Re: local index

I imagine you’d also have to access the table if you used a bind variable for the partition key filter.

You could just prefix the local indexes so that they can always be used as the only filter for predicates on these columns. It would be very compressible so it won’t use a lot of extra space.

Otherwise, you could tell the optimizer to use or expansion on your in list so that it produces a bunch of union all’ed queries for each particular value in your list. Although there’s every possibility it won’t recognise the existence of your special case.

Thanks,
Andrew

On Tue, 26 May 2020 at 13:15, ahmed.fikri_at_t-online.de < ahmed.fikri_at_t-online.de> wrote:

>
>
> on the contrary, I admire oracle technologies, but "as a humble lateral
> thinker" (if I may call me like that), I guess that oracle needs a leap
> into the future. And that was just an appeal.
>
>
>
> Regards
>
> Ahmed Fikri
>
>
>
>
>
>
>
> -----Original-Nachricht-----
>
> Betreff: Re: local index
>
> Datum: 2020-05-26T13:43:38+0200
>
> Von: "Lothar Flatz" <l.flatz_at_bluewin.ch>
>
> An: "oracle-l_at_freelists.org" <oracle-l_at_freelists.org>
>
>
>
>
>
>
> Ahmed,
>
> Well, "human eye", I believe you are underestimating the task of writing
> an optimizer.
> I also believe there will be eventually a KI doing the optimization.
>
> Regards
>
> Lothar
>
> Am 26.05.2020 um 12:32 schrieb ahmed.fikri_at_t-online.de:
>
> Thanks a lot Sir for this explanation!
>
>
>
> Regarding "the human eye", I wonder when Oracle will have one.
>
>
>
> Thanks and regards
>
> Ahmed Fikri
>
>
>
>
>
>
>
> -----Original-Nachricht-----
>
> Betreff: Re: local index
>
> Datum: 2020-05-26T11:34:19+0200
>
> Von: "Jonathan Lewis" <jlewisoracle_at_gmail.com> <jlewisoracle_at_gmail.com>
>
> An: "ahmed.fikri_at_t-online.de" <ahmed.fikri_at_t-online.de>
> <ahmed.fikri_at_t-online.de> <ahmed.fikri_at_t-online.de>
>
>
>
>
>
>
>
> 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