66 rows is kind of small. The full table scan for 66 rows is probably
faster than any other method.
Jim
"grjohnson" <Johnsog123_at_hotmail.com> wrote in message
news:32b8a689.0210202017.1400c57e_at_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 Mon Oct 21 2002 - 00:32:34 CDT
