Don't rely on 9i Skip Scan Indexing

From: grjohnson <Johnsog123_at_hotmail.com>
Date: 20 Oct 2002 21:17:43 -0700
Message-ID: <32b8a689.0210202017.1400c57e@posting.google.com>

G'day,

Skip Scanning of Indexes is a new feature of Oracle 9i. In previous releases of Oracle, if a query failed to utilise an index prefix i.e. the leading column of the composite index, the CBO would perform a Full table Scan (FTS) and would cause the index not to be used.

Sorry Howard Rogers, but I'm going to quote you here, in Oracle9i Index Skip Scanning is "an intrinsic function of the cost-based optimizer... It will only be of significant benefit where the leading column of the index is of fairly low cardinality, and whether the optimizer ever uses it depends entirely on the nature of the SQL statments thrown at the database."

I thought I'd do a bit of testing regarding this.

I took a table and created a composite index. The table had only 66 rows. Small enough to test on, and the primary key index had 6 distinct keys. I figure this is lowish cardiantly for this table. Results are as follows.

1). First I analyzed the table.  

SQL> ANALYZE TABLE CR_FILE COMPUTE STATISTICS; Table analyzed.

2. Performed query prior to adding the index.

SQL> SELECT photo_id, file_name, file_name_old FROM cr_file
WHERE user_id = 152
AND status = 'PENDING CONFIRMATION'
AND file_type = 'R';

no rows selected

Execution Plan

---

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=1 Bytes=80) 1 0 TABLE ACCESS (FULL) OF 'CR_FILE' (Cost=6 Card=1 Bytes=80)

NB: Full table scan

3. Create the composite index on the three colums queried

SQL> CREATE INDEX NKCR_FILE_1 ON CR_FILE (user_id, status, file_type) tablespace NPD_INDEX_SML;

Index created.

4. Analyze table again

SQL> ANALYZE TABLE CR_FILE COMPUTE STATISTICS; Table analyzed.

5. Perform same query as above to make sure index utilised

SQL> SELECT photo_id, file_name, file_name_old FROM cr_file
WHERE user_id = 152
AND status = 'PENDING CONFIRMATION'
AND file_type = 'R';

no rows selected

Execution Plan

---

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=80) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'CR_FILE' (Cost=2 Card=1 Bytes=80)
2 1 INDEX (RANGE SCAN) OF 'NKCR_FILE_1' (NON-UNIQUE) (Cost=1 Card=1)

NB: Prove the CBO determine cost cheaper to do INDEX RANGE SCAN than FTS. 6. Commented out 2nd restriction in WHERE clause. Index should be used again.

SQL> SELECT photo_id, file_name, file_name_old FROM cr_file
WHERE user_id = 152
--status = 'PENDING CONFIRMATION'
AND file_type = 'R';

no rows selected

Execution Plan

---

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=2 Bytes=144) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'CR_FILE' (Cost=3 Card=2 Bytes=144)
2 1 INDEX (RANGE SCAN) OF 'NKCR_FILE_1' (NON-UNIQUE) (Cost=1 Card=4)

7. Comment out the 1st WHERE clause to see if CBO used skip-scanning.

SQL> SELECT photo_id, file_name, file_name_old FROM cr_file
WHERE -- user_id = 152
status = 'PENDING CONFIRMATION'
AND file_type = 'R';

no rows selected

Execution Plan

---

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=6 Bytes=462) 1 0 TABLE ACCESS (FULL) OF 'CR_FILE' (Cost=6 Card=6 Bytes=462)

NB: Full table scan

So, what does that prove? It's proves to me that STILL, as every other ORACLE release, to be sure your queries use a INDEX, you'd have to create an index on each column. Because as Howard says "whether the optimizer ever uses it depends entirely on the nature of the SQL statments", therefore the DBA has absolutely no control over the INDEX usage. The only example I have found from Metalink of INDEX SKIP-SCANNING actually working, is when Oracle use a specific hint for the query to use the index.

I just thought this was interesting. What is other people's experience of using this Oracle "feature"?

Thanks,

Greg Johnson
Oracle Certified Professional
OCP 8i DBA / OCP 9i DBA
Dialog Information Technology
Brisbane, Queensland, Australia Received on Sun Oct 20 2002 - 23:17:43 CDT