Re: Don't rely on 9i Skip Scan Indexing

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

Straight cut-paste out of 9.2 W2K

SQL> create table blah ( x number, y number, z char(50));

Table created.

SQL> insert into blah
  2 select 12, rownum, 'x'
  3 from sys.source$
  4 where rownum < 200000;

132609 rows created.

SQL> commit;

Commit complete.

SQL> create index blah_ix on blah ( x,y) nologging;

Index created.

SQL> analyze table blah compute statistics;

Table analyzed.

SQL> set autotrace traceonly explain
SQL> select * from blah
  2 where y = 23456;

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=56)    1 0 TABLE ACCESS (BY INDEX ROWID) OF 'BLAH' (Cost=4 Card=1 Bytes=56)

   2 1 INDEX (SKIP SCAN) OF 'BLAH_IX' (NON-UNIQUE) (Cost=3 Card=1)

so the optimizer DOES know about it...Now its just a matter of determining the break even point in terms of leading column cardinality

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."