Re: local index

You're thinking of your special case where it's "obvious" to the human eye that the path you want is valid.
The optimizer has to find a path that gives the right answer in all cases.

Say I have one partition which hold ('part_2','part_2a','part_2b') - then clearly the optimizer has to visit the table to determine which rows hold 'part_2'.
Now imagine I have 10,000 partitions some like the above, some with one value each, and then write a query with an INLIST of 250 items.

To do what you want - if it were legal - the optimizer would first have to check which of the 10,000 partitions each value in the list belonged to, and check whether or not the partition was a single-value only partition (and, to be as clever as possible, it could separate the input list into the values that could do an index-only access and those that had to visit the table).

The problem of picking the path you want because you know that you have one value per partition is not a trivial exercise. (And that's when it's a single table query!)

Regards
Jonathan Lewis

On Tue, May 26, 2020 at 7:59 AM ahmed.fikri_at_t-online.de < ahmed.fikri_at_t-online.de> wrote:

> In my real case the table has billions of records and it takes lot of time
> to execute this one
>
> select count(*) from table_a where loc_id = some_id and part_key in (e.g.
> 50 partitions)
>
>
>
> for me"is not clear why this step TABLE ACCESS BY LOCAL INDEX ROWID
> BATCHED.
>
>
>
> However using plsql:
>
>
>
> DECLARE
> v_partitions sys.odcivarchar2list := sys.odcivarchar2list('part_1',
> ....,'part_50');
> l_count NUMBER;
> l_total_count NUMBER;
> v_loc_id NUMBER;
> BEGIN
> FOR i IN 1..v_partitions.count LOOP
> EXECUTE IMMEDIATE 'select /*+ index(t IDX_TBL_A_LOC_ID ) */ count(*)
> from tbl_a t where part_key = :1 and loc_id = :2 ' INTO l_count
> USING v_partitions(i), v_loc_id;
> l_total_count := l_total_count + l_count;
> END LOOP;
> dbms_output.put_line(l_total_count);
> END;
>
>
>
>

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