AW: local index

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 <mailto: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>
<mailto:jlewisoracle_at_gmail.com>

    An: "ahmed.fikri_at_t-online.de" <mailto:ahmed.fikri_at_t-online.de>
<ahmed.fikri_at_t-online.de> <mailto: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
<mailto:ahmed.fikri_at_t-online.de> <ahmed.fikri_at_t-online.de
<mailto: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