AW: local index

From: <>
Date: Tue, 26 May 2020 14:14:30 +0200 (CEST)
Message-ID: <>


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.  

Ahmed Fikri      

Betreff: Re: local index
Datum: 2020-05-26T13:43:38+0200
Von: "Lothar Flatz" <> An: "" <>      


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.



Am 26.05.2020 um 12:32 schrieb <> :

    Thanks a lot Sir for this explanation!      

    Regarding "the human eye", I wonder when Oracle will have one.      

    Thanks and regards
    Ahmed Fikri


    Betreff: Re: local index
    Datum: 2020-05-26T11:34:19+0200
    Von: "Jonathan Lewis" <>

    An: "" <>
<> <>


    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!)      

    Jonathan Lewis


    On Tue, May 26, 2020 at 7:59 AM
<> <
<> > 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
      However using plsql:
      v_partitions sys.odcivarchar2list := sys.odcivarchar2list('part_1',
      l_count NUMBER;
      l_total_count NUMBER;
      v_loc_id NUMBER;
        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;

Received on Tue May 26 2020 - 14:14:30 CEST

Original text of this message