AW: local index

I cannot make changes to this very very huge table.  

Concerning prefixing the local index, I have tried in the past to see what would bring this on the primary key for this table (another column). The results were very good, but the storage space was too high + approx. 50% (the partkey = 'yyyymmdd_(+number)'), despite the compression. Maybe I did something wrong.  

For my use case (checking in advance for the GUI whether the returned rows exceed a certain limit) I am completely satisfied with the solution with PL / SQL, it seems to work wonderfully (response time constantly < 3 sec)  

I was just wondering that Oracle somehow couldn't see something like that, so I posted the question.  

Thanks and regards
Ahmed Fikri        

-----Original-Nachricht-----
Betreff: Re: local index
Datum: 2020-05-26T14:39:41+0200
Von: "Andy Sayer" <andysayer_at_gmail.com> An: "ahmed.fikri_at_t-online.de" <ahmed.fikri_at_t-online.de>      

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 <mailto:ahmed.fikri_at_t-online.de> <ahmed.fikri_at_t-online.de <mailto: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 <mailto:l.flatz_at_bluewin.ch> >   An: "oracle-l_at_freelists.org <mailto:oracle-l_at_freelists.org> " <   oracle-l_at_freelists.org <mailto: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