AW: local index

From: <>
Date: Tue, 26 May 2020 15:13:45 +0200 (CEST)
Message-ID: <>

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        

Betreff: Re: local index
Datum: 2020-05-26T14:39:41+0200
Von: "Andy Sayer" <> An: "" <>      

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.  


On Tue, 26 May 2020 at 13:15, <> < <> > 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.    

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

Received on Tue May 26 2020 - 15:13:45 CEST

Original text of this message