AW: local index

From: <ahmed.fikri_at_t-online.de>
Date: Tue, 26 May 2020 12:32:05 +0200 (CEST)
Message-ID: <1590489125645.1612401.31f4170c8a3a9e2551467a2d6fd83dfefd837e69_at_spica.telekom.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> An: "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
<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

Received on Tue May 26 2020 - 12:32:05 CEST