CBO doesn't recognize local partitioned index?

[Cookies]

Oracle 9.2.0.4 on Linux

For some reason my CBO won't recognize and use the local partitioned index after I analyze the table and indexes.

It looks like something is wrong with the CBO because it doesn't think
it costs anything. Anyone have a clue why this would be?

Obviously it is much faster using the local partitioned index.

Forcing the local index with hint:

SELECT /* getQueryString */ /*+ index (mi temp_mi3)*/
MIN(mi.end_dt) AS end_dt,SUM(mi.usage_nbr*4) AS usage_nbr,
COUNT(*) AS sample_count
FROM USAGE_METER_INTERVAL mi WHERE
mi.org_id = 'TC'
AND mi.end_dt >= SYSDATE-1 AND mi.end_dt <= SYSDATE+1
AND EXISTS (
SELECT NULL FROM ACCOUNT ad WHERE
ad.org_id = mi.org_id AND
ad.svc_type_cd = mi.svc_type_cd AND
ad.cust_id = mi.cust_id AND
ad.prem_seq_nbr = mi.prem_seq_nbr AND
ad.created_dt <= mi.end_dt) GROUP BY mi.end_dt

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.02       0.04          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        9      0.80       0.82         68        804          0         107
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       11      0.82       0.87         68        804          0         107

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 64

Rows     Row Source Operation
-------  ---------------------------------------------------
    107  SORT GROUP BY (cr=804 r=68 w=0 time=824403 us)
  26640   FILTER  (cr=804 r=68 w=0 time=653615 us)
  26640    HASH JOIN SEMI (cr=804 r=68 w=0 time=598453 us)
  26640     PARTITION RANGE ITERATOR PARTITION: KEY KEY (cr=782 r=55 w=0 time=305653 us)
  26640      TABLE ACCESS BY LOCAL INDEX ROWID USAGE_METER_INTERVAL PARTITION: KEY KEY (cr=782 r=55 w=0 time=253800 us)
  26640       INDEX RANGE SCAN TEMP_MI3 PARTITION: KEY KEY (cr=58 r=55 w=0 time=133882 us)(object id 34539)
   2500     INDEX FAST FULL SCAN ACCOUNT_CRTD_DT_IX (cr=22 r=13 w=0 time=29115 us)(object id 34116) 

Same query without the hint. Notice the lack of any cost for the full scan:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.01       1.05          0          8          0           0
Fetch        9      0.32       8.59         13         22          0         107
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       11      0.33       9.64         13         30          0         107

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 64

Rows     Row Source Operation
-------  ---------------------------------------------------
    107  SORT GROUP BY (cr=22 r=13 w=0 time=8593925 us)
  26750   FILTER  (cr=22 r=13 w=0 time=8408538 us)
      0    HASH JOIN SEMI (cr=0 r=0 w=0 time=0 us)
      0     PARTITION RANGE ITERATOR PARTITION: KEY KEY (cr=0 r=0 w=0 time=0 us)
      0      TABLE ACCESS FULL USAGE_METER_INTERVAL PARTITION: KEY KEY (cr=0 r=0 w=0 time=0us)
      0     INDEX FAST FULL SCAN ACCOUNT_CRTD_DT_IX (cr=0 r=0 w=0 time=0 us)(object id 34116) 

[pando]

It runs less costly without index, CBO costs on that not on time... And it's full scanning partitions not table

[Cookies]

yes, full scan on the partition(s).

So, basically I could force it with the hint if I want a faster result (poorer performance) or I could just go with the CBO.

Those are my only choices?

I tried re-writing the query, but this tested to be the most efficient form. Basically the query needs to check that the account created date is earlier than the intervals being selected.

[slimdave]

Are the indexes analyzed at the partition level as well as the global level?

[Cookies]

Originally posted by slimdave
Are the indexes analyzed at the partition level as well as the global level?

I analyzed the table using:

declare
begin

    dbms_stats.gather_table_stats (
         ownname          => 'TEST_CASE',
         tabname          => 'USAGE_METER_INTERVAL',
         cascade          => TRUE,
         degree           => 3,
         granularity      => 'PARTITION'
    );
end; 
/ 

I think something is wrong with either the analyzer, the CBO or the
CBO parameter settings.

some related parameters:

NAME                                 TYPE        VALUE
==================================== =========== ========
db_file_multiblock_read_count        integer     16
optimizer_dynamic_sampling           integer     1
optimizer_features_enable            string      9.2.0
optimizer_index_caching              integer     25
optimizer_index_cost_adj             integer     90
optimizer_max_permutations           integer     2000
optimizer_mode                       string      CHOOSE 

[pando]

try analyze with global stats however I dont think that is gonna help anyways, the less costly query is what CBO will choose, in this case the one not using the index

[Cookies]

I made a few changes to:

ALTER SESSION SET optimizer_index_caching=80

ALTER SESSION SET optimizer_index_cost_adj=5


Success:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.02       0.02          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        9      0.74       0.98         89        862          0         111
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       11      0.76       1.00         89        862          0         111

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 64

Rows     Row Source Operation
-------  ---------------------------------------------------
    111  SORT GROUP BY (cr=862 r=89 w=0 time=981925 us)
  26698   FILTER  (cr=862 r=89 w=0 time=809891 us)
  26698    HASH JOIN SEMI (cr=862 r=89 w=0 time=754257 us)
  26698     PARTITION RANGE ITERATOR PARTITION: KEY KEY (cr=840 r=89 w=0 time=435083 us)
  26698      TABLE ACCESS BY LOCAL INDEX ROWID USAGE_METER_INTERVAL PARTITION: KEY KEY (cr=840 r=89 w=0 time=362400 us)
  26698       INDEX RANGE SCAN TEMP_MI3 PARTITION: KEY KEY (cr=91 r=88 w=0 time=220469 us)(object id 34539)
   2500     INDEX FAST FULL SCAN ACCOUNT_CRTD_DT_IX (cr=22 r=0 w=0 time=9676 us)(object id 34116) 

[engiri]

what was the previous values of these parameters?

[abhaysk]

Read his previous post