DBA Tips Archive for Oracle

  


[an error occurred while processing this directive]

No Title

[an error occurred while processing this directive]

by Michael New, MichaelNew@earthlink.net, Gradation LLC

Contents

Introduction

In Oracle releases before Oracle9i, the Cost Based Optimizer (CBO) would only use a composite index (sometimes referred to as a concatenated index) if you referenced the index prefix column in the WHERE clause of the SQL statement. If the SQL statement omitted the index prefix column of the composite index, the CBO would not use the index.

One solution would be to simply create another index on the non-prefixed index columns in order for Oracle to use an index for faster access to the data. This method, however, may add considerable overhead when Oracle had to update several indexes during DML operations. A new feature included with Oracle9i called Skip Scan Index Access allows the Oracle CBO to use a composite index even when the index prefix column in a SQL statement has been omitted. Using this new feature, you will not have to create an additional index to provide faster access to your data. This article provides a brief introduction to skip scan index access and how it can improve the response time of your queries.

Oracle9i Support for Skip Scan Index Access

Oracle will attempt to use the new skip scan index access feature and scan a composite index when the SQL does not contain the index prefix column. This new feature can be used for the default B-tree index, however, you cannot use the skip scan index access technology for bitmap indexes, function-bases indexes, domain indexes, and for reverse key keys. Oracle9i does, however, supporting the new skip scan index access feature on clustered indexes, descending indexes, and using the CONNECT BY clause of the SQL statement.

How Skip Scan Index Access Works

Now let's see how the skip scan technology works. Oracle will use the skip scan algorithm to determine the domain of distinct values for the index prefix column and then iterate through each distinct value in this domain. For each distinct value, Oracle will perform a regular index scan on that portion of the index. To put it another way, Oracle will treat the composite index search as a number of small sub-indexes to retrieve the ROWIDs - there will be one sub-index search performed for each distinct value within the index prefix column. The new skip scan index access feature will function as if you issued a sequence of SQL statements having each statement specifying a single value for the index prefix column.

Skip scan index access is not a silver bullet. While it is faster than performing a full table scan (FTS), it is not faster than a full index scan (FIS). The real benefits and efficiencies for using scan index access increase when the domain of distinct values for the prefix index column decreases. Basically, when the domain of distinct values for the prefix index column, there are fewer sub-indexes Oracle will have to utilize. When determining whether to use the skip scan index access algorithm over a full table scan (or other possible data access paths), the Oracle CBO will use the data distribution statistics of the prefix index column. Keep in mind that the skip scan index access feature can only be used with the Oracle cost based optimizer.

Example

The following simple example should provide an insight to how the skip scan index access feature works. In this example, we will create a composite index. When the skip scan index access algorithm is going to be used, it will split the composite index into logically smaller indexes. The number of logical sub-indexes will depend on the cardinality of the prefix index column. Let's take a look at an example.


SQL> create table test1(a number, b number); Table created. SQL> begin 2 for i in 1 .. 1000 3 loop 4 insert into test1 values(mod(i, 10), 100); 5 end loop; 6 end; 7 / PL/SQL procedure successfully completed. SQL> create index test1_i on test1(a, b); Index created. SQL> exec dbms_stats.gather_table_stats(ownname => 'SYSTEM', tabname => 'test1', cascade => true); PL/SQL procedure successfully completed. SQL> explain plan for select /*+ index_ss(test1 test1_i) */ a, b from test1 where b = 101; Explained. SQL> start ?\rdbms\admin\utlxpls.sql PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- -------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | -------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 5 | 11 | |* 1 | INDEX SKIP SCAN | TEST1_I | 1 | 5 | 11 | -------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- 1 - access("TEST1"."B"=101) filter("TEST1"."B"=101) Note: cpu costing is off 15 rows selected.

About the Author

Jeffrey Hunter is an Oracle Certified Professional, Java Development Certified Professional, Author, and an Oracle ACE. Jeff currently works as a Senior Database Administrator for The DBA Zone, Inc. located in Pittsburgh, Pennsylvania. His work includes advanced performance tuning, Java and PL/SQL programming, developing high availability solutions, capacity planning, database security, and physical / logical database design in a UNIX / Linux server environment. Jeff's other interests include mathematical encryption theory, tutoring advanced mathematics, programming language processors (compilers and interpreters) in Java and C, LDAP, writing web-based database administration tools, and of course Linux. He has been a Sr. Database Administrator and Software Engineer for over 20 years and maintains his own website site at: http://www.iDevelopment.info. Jeff graduated from Stanislaus State University in Turlock, California, with a Bachelor's degree in Computer Science and Mathematics.



Copyright (c) 1998-2017 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Tuesday, 17-Jan-2012 10:23:38 EST
Page Count: 14837