Re: Assessment question on indexes

From: Anurag Varma <avoracle_at_gmail.com>
Date: 10 Feb 2005 12:33:50 -0800
Message-ID: <1108067630.928463.110090@l41g2000cwc.googlegroups.com>

ok .. agreed that A would not care about null values (I didn't register the distinct clause being there).
However, still I think 9i optimizer will not use the index if the columns are nullable. The OCP question was version based and hence, bug or not .. that's how it behaves.

Here is an example:

ORA92> desc inventories
Name
Null? Type

-------------------------------------------- PRODUCT_ID NUMBER WAREHOUSE_ID NUMBER C NUMBER D NUMBER

ORA92> select column_name, column_position from user_ind_columns where table_name = 'INVENTORIES';

COLUMN_NAME                    COLUMN_POSITION


------------------------------ ---------------

PRODUCT_ID                                   1
WAREHOUSE_ID                                 2

ORA92> set autotrace traceonly exp
ORA92> exec
dbms_stats.gather_table_stats(null,'INVENTORIES',cascade=>true);

PL/SQL procedure successfully completed.

ORA92> select count(distinct WAREHOUSE_ID) from INVENTORIES;

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=2) 1 0 SORT (GROUP BY)
2 1 TABLE ACCESS (FULL) OF 'INVENTORIES' (Cost=2 Card=2 Bytes=4)

ORA92> select /*+ index(INVENTORIES) */ count(distinct WAREHOUSE_ID) from INVENTORIES;

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=2) 1 0 SORT (GROUP BY)
2 1 TABLE ACCESS (FULL) OF 'INVENTORIES' (Cost=2 Card=2 Bytes=4)

ORA92> set autotrace off
ORA92> select index_name from user_indexes where table_name = 'INVENTORIES'; INDEX_NAME

I_IDX ORA92> set autotrace traceonly exp
ORA92> select /*+ index_ss(INVENTORIES I_IDX) */ count(distinct WAREHOUSE_ID) from INVENTORIES;

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=2) 1 0 SORT (GROUP BY)
2 1 TABLE ACCESS (FULL) OF 'INVENTORIES' (Cost=2 Card=2 Bytes=4)

ORA92> alter session set optimizer_index_cost_adj=1;

Session altered.

ORA92> alter session set optimizer_index_caching=100;

Session altered.

ORA92> select /*+ index_ss(INVENTORIES I_IDX) */ count(distinct WAREHOUSE_ID) from INVENTORIES;

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=2) 1 0 SORT (GROUP BY)
2 1 TABLE ACCESS (FULL) OF 'INVENTORIES' (Cost=2 Card=2 Bytes=4)

However, not all questions in OCP are like that .. but the some questions that are like that .. should
be more clear in what they say. Received on Thu Feb 10 2005 - 14:33:50 CST