Re: local index

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:
>
> 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