Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Don't rely on 9i Skip Scan Indexing

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

From: Howard J. Rogers <howardjr2000_at_yahoo.com.au>
Date: Mon, 21 Oct 2002 16:43:10 +1000
Message-ID: <6eNs9.58148$g9.166256@newsfeeds.bigpond.com>

"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."

Which bit of the above don't you like?! "it's an intrinsic function of the CBO" means you don't have a SKIP_SCAN=TRUE parameter to set. "leading column is low cardinality" is true, to the extent that if you have a high cardinality column as the leading edge, you are most unlikely to end up skipping anything very much.

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

Excellent news.

> I took a table and created a composite index. The table had only 66
> rows.

Extremely bad news. If you're going to get skip scanning happening, you need an index with plenty of leaf nodes so that there's actually something worth skipping! 66 rows probably creates an index with 4 blocks tops (give or take). Not much point in skipping anything there (and yes, I know the example I gave in the book used an index of 8 leaf nodes, but that was just to get things explained in theory). Try your tests with 6600 rows and report back. Your failure to induce the CBO to start skipping is entirely a product of this one aspect of your test.

I can't remember exactly, and it's been a few months, but I could have sworn my examples in the Beginning Programming book asks you to create a table as a select from dba_objects -which means that it would have had about 5500 rows as an absolute minimum.

Regards
HJR
>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 - 01:43:10 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US