Re: Why Index full scan path not considered

1. Delete following an index fast full scan was a path that became available some time in 12c, see: https://jonathanlewis.wordpress.com/2014/06/19/delete-costs/
2. You stats say you must be doing something like the site mentioned in the article. Your table reports 14,000 blocks and no rows, your index report blevel=2 and no rows and (because of the way Oracle calculates the leaf block count) Oracle doesn't know how many allocated blocks there are in the index, but I'd guess there might be a couple of hundred.

You could set (and lock) some index stats to give (particularly the leaf_blocks) to let Oracle know the index space is physically quite large, even though it is empty. Or you could find a moment to move the table online while there's no data in it so that it shrinks and gets zero stats and takes zero space. The former might be the safer option.

(I think there's a fix-control you can set (or maybe a parameter) to tell Oracle to use the segment size to cost the index fast full scan, and that would be another valid workaround.)

Regards
Jonathan Lewis

On Thu, 9 Jun 2022 at 20:24, Pap <oracle.developer35_at_gmail.com> wrote:

> Hello Listers, We have a delete query with a difference in plan after we
> moved to 19C. It runs hundreds of thousands of times in a day. It was using
> unique scan path on 11.2 and was running faster i.e. ~.0001 seconds per
> execution but when we moved to 19C it's now going for an index fast full
> scan path and taking ~.05 seconds/execution.
>
> We fetched the 10053 trace for both OFE 19.1 and OFE 11.2. What we see is
> , in 11.2 the best cost comes to be 2.000464 i.e the index unique scan
> path. An optimizer evaluated table scan vs index unique scan path but
> there was no index FFS path cost evaluation. In the 19c trace we are
> seeing the new "index FFS" access path evaluation and that cost endup as
> 2.000000 and thus chosen as the best path in 19c.
>
> So my question is,
>
> 1)If the index ffs path was not possible for delete statements and was
> introduced in 19c?
>
> 2)To handle such a scenario if hinting the query is the only option?
>
> DELETE FROM TAB1 WHERE CMP_ID = :b1 AND EP_DT = to_date(:b2,'DD-MON-YYYY');
>
> Here an unique index TAB1_PK exists on column cmp_id, EP_DT.
>
> 11G 10053 trace:-
> https://gist.github.com/oracle9999/ba46c45d4c227a7ed7f56be21c26ccfc
>
> 19C 10053 trace:-
> https://gist.github.com/oracle9999/9edb233ced8bd5c6b908f5c7c7a1dc70
>
>
>

--
http://www.freelists.org/webpage/oracle-l