Why index is used? It should not be

[cheguvera]

Hi,

I am on Oracle 9.2 on HP Unix and getting more confused about the optimizer.

Please have a look at following steps I performed,

-- ----- Created empty table ---------
SQL> create table r1 as select * from all_objects where 1=2 ;

Table created.

SQL> select count(*) from r1 ;

COUNT(*)
----------
0

SQL> create index r1_ind1 on r1 (object_id) ;

Index created.

-- ----- I am collecting the stats when the table is empty -------

SQL> exec dbms_stats.gather_table_stats('USER1','R1');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_index_stats('USER1','R1_IND1');

PL/SQL procedure successfully completed.

-- ---- Putting some data -----------
SQL> insert into r1 select * from all_objects ;

43107 rows created.

SQL> commit ;

Commit complete.

-- --- data in the table and stats do not match ---

SQL> set autotrace traceonly explain
SQL> select * from r1 where object_id=311098;

Execution Plan
----------------------------------------------------------
0
SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=128)

1 0
TABLE ACCESS (BY INDEX ROWID) OF 'R1' (Cost=1 Card=1 Bytes=128)

2 1
INDEX (RANGE SCAN) OF 'R1_IND1' (NON-UNIQUE) (Cost=1 Card=1)


SQL> set autotrace off
SQL> l
1* select num_rows from user_tables where table_name='R1'
SQL> /

NUM_ROWS
----------
0

I was expecting that Oracle will go for full table scan, but it used index.
Also, look at the cardinality in the plan, "Card=1", still the index is used. How very confusing ???

Can anybody help ?

Regards

 

[dbtoo2001]

It's not just about numbers, it's also about access methods, paths, block reads, and 'rules':

From the manual you will find that you wrote a statement which could use an index, so it did. Just because statistics aren't updated does not mean the optimizer will be required to use full table scans.

Writing Statements That Use Indexes
Even after you create an index, the optimizer cannot use an access path that uses the index simply because the index exists. The optimizer can choose such an access path for a SQL statement only if it contains a construct that makes the access path available. To allow the CBO the option of using an index access path, ensure that the statement contains a construct that makes such an access path available.

Did you review the performance guide?

Optimizer Index Determination Criteria
The optimizer uses the following criteria when determining which index to use:

Number of rows in the index (cardinality).
Number of distinct keys. These define the selectivity of the index.
Level or height of the index. This indicates how deeply the data probe must search in order to find the data.
Number of leaf blocks in the index. This is the number of I/Os needed to find the desired rows of data.
Clustering factor (CF). This is the collocation amount of the index block relative to data blocks. The higher the CF, the less likely the optimizer is to select this index.
Average leaf blocks for each key (ALFBKEY). Average number of leaf blocks in which each distinct value in the index appears, rounded to the nearest integer. For indexes that enforce UNIQUE and PRIMARY KEY constraints, this value is always one.
Determining if You Have Chosen the Right Index
Use the following notes to assist you in deciding whether you have chosen an appropriate index for a table, data, and query:

DISTINCT
Consider index ap_invoices_n3, having two distinct keys. The resulting selectivity based on index ap_invoices_n3 is poor, and the optimizer is not likely to use this index. Using this index fetches 50% of the data in the table. In this case, a full table scan is cheaper than using index ap_invoices_n3.

Index Cost Tie
The optimizer uses alphabetic determination. If the optimizer determines that the selectivity, cost, and cardinality of two finalist indexes is the same, then it looks at the names of the indexes and chooses the name that begins with the lower alphabetic letter or number.

 

[cheguvera]

Thanks dbtoo2001,

I would like to totally agree with you, but if we see the query about the same index,

SQL> select INDEX_NAME, UNIQUENESS, BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS,
2 AVG_LEAF_BLOCKS_PER_KEY,CLUSTERING_FACTOR, NUM_ROWS, SAMPLE_SIZE,
3 STATUS, DEGREE, USER_STATS, DURATION,PCT_DIRECT_ACCESS, JOIN_INDEX
4 from user_indexes where index_name='R1_IND1';

INDEX_NAME UNIQUENES BLEVEL LEAF_BLOCKS DISTINCT_KEYS
------------------------------ --------- ---------- ----------- -------------
AVG_LEAF_BLOCKS_PER_KEY CLUSTERING_FACTOR NUM_ROWS SAMPLE_SIZE STATUS
----------------------- ----------------- ---------- ----------- --------
DEGREE USE DURATION PCT_DIRECT_ACCESS
---------------------------------------- --- --------------- -----------------
JOI
---
R1_IND1 NONUNIQUE 0 0 0
0 0 0 0 VALID
1 NO
NO


SQL> set autotrace traceonly explain
SQL> select * from r1 where object_id=311098;

Execution Plan
----------------------------------------------------------
0
SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=128)

1 0
TABLE ACCESS (BY INDEX ROWID) OF 'R1' (Cost=1 Card=1 Bytes=128)

2 1
INDEX (RANGE SCAN) OF 'R1_IND1' (NON-UNIQUE) (Cost=1 Card=1)


If we see all the information(stats) about this index in the data dictionary, its zero.
I know that, its not just about the count. Actually, I am trying to understand here that, which factor from the statistics prompted Oracle to use the index.
Its true that, just having the index is not enough. But all information Oracle need to make this decision, comes from the statistics which we have to collect.
If there is no stats, DYNAMIC SAMPLING will be used. But if there is some stats (old or new), dynamic sampling will not be used.
Also, in the plan we can see the cardinality as 1, means there are not many distinct values.
And we are using COST based optimizer, as shown by the plan, Optimizer=CHOOSE , there should be no question of RULES.
Any decision Oracle makes is on the basis of the stats.
Is there anything, which affects more than stats in CBO?

Regards