Using Skip Scan Indexes Oracle Tips by Burleson Consulting

The main strength of the Oracle9i skip scan index capability is that you scan the index, not the table, saving a full table scan. Let’s take a look at an example:

SQL> desc emp5

 Name                                      Null?      Type
 ----------------------------------------- --------   ----------------
 EMPNO                                                NUMBER(15)
 ENAME                                                VARCHAR2(10)
 JOB                                                  VARCHAR2(9)
 MGR                                                  NUMBER(4)
 HIREDATE                                             DATE
 SAL                                                  NUMBER(7,2)
 COMM                                                 NUMBER(7,2)
 DEPTNO                                               NUMBER(2)

SQL> create index skip1 on emp5(job,empno);

Index created.  

The index skip1 will be a two-level B-tree index.  The first level will contain the job value; the second will contain the empno values associated with the jobs. First a select with the normal index:

SQL> select count(*)

  1  from emp5
  2* where empno=7900;

Elapsed: 00:00:00.12 (Result is a single row…not displayed)

Execution Plan

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=5)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (RANGE SCAN) OF 'EMP5I_EMPNO' (NON-UNIQUE)

Statistics

3  consistent gets
0  physical reads

Now, let’s see what the execution time is without the index:

SQL> select /*+ no_index(emp5 emp5i_empno) */ count(*)

  1  from emp5
  2* where empno=7900;

Elapsed: 00:00:03.13 (Result is a single row…not displayed)

Execution Plan
-------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=5)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (FAST FULL SCAN) OF 'SKIP1' (NON-UNIQUE)

Statistics
-----------------------
6826  consistent gets
6819  physical reads

Now let’s force the use of the skip scan index:

SQL> select /*+ index(emp5 skip1) */ count(*)

  1  from emp5
  2* where empno=7900;

Elapsed: 00:00:00.56

Execution Plan
-------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=1 Bytes=5)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (SKIP SCAN) OF 'SKIP1' (NON-UNIQUE)

Statistics
-------------------------
21  consistent gets
17  physical reads

As you can see a significant performance improvement!

Estimating the Size of an Index

To estimate the size of an index for the storage clause, use the following procedure:

1.    Calculate the required database block header size (BHS).

            BHS = fixed header + variable transaction header

where:
            fixed header = 113
            variable transaction header = 24  *INITRANS

2.    Calculate available data space (ADS).
            ADS = ((Blocksize - BHS)  * (PCTFREE/100))

3.    Calculate the average data length (ADL) per row.

Note: Step 3 is the same as step 3 in the table-sizing section in Chapter 5. However, size only for those columns in the index.

4.    Calculate the average row length (ARL).
            bytes/entry = entry header + ROWID length + F + V + D

where:
   entry header = 2
            ROWID = 6

F = Total length of bytes of all columns that store 127 bytes or fewer—one header byte per column.

V = Total length of bytes of all columns that store more than 127 bytes—two header bytes per column.

For UNIQUE indexes, the entry header is 0.

5.    Calculate number of blocks for index.

            # of Blocks = 1.05 * (((# of NOT NULL rows) / (ADS) / ARL))

The 1.05 factor allows for branch space and is an empirically (SWAG) derived value.

6.    Calculate the number of bytes required.
            Size in bytes = BLOCKSIZE ? number of blocks

If the table on which the index is based exists, the script in Source 6.3 in the next section is used to estimate the size of the index that is generated from a given list of columns. The Remote DBA enters the table name, the table owner name, and a list of columns; the procedure does the rest. A spreadsheet to calculate index size based on 8i sizing formulas is included in the MISC directory on the Wiley Web site. An example of how this spreadsheet looks is shown in Figure 6.2.

Figure 6.2 Example Oracle9i index-sizing spreadsheet.

Analyzing Indexes

For Oracle8, Oracle8i, and Oracle9i, the ANALYZE command can be used to get the average index size from an example index. The format of this command follows. Other statistics are also generated, but these will be covered in Chapter 9.

ANALYZE INDEX [schema.]index_name
[PARTITION(partition)]
[SUBPARTITION(subpartition)]
COMPUTE STATISTICS|ESTIMATE STATISTICS [SAMPLE n ROWS|PERCENT]
[VALIDATE STRUCTURE [CASCADE]]

where:

COMPUTE STATISTICS. Calculates statistics based on all rows.

ESTIMATE STATISTICS. Calculates an estimate of the statistics based on n ROWS or PERCENT of rows in table.

VALIDATE STRUCTURE. Validates that the table and its indexes are consistent and not corrupted.

CASCADE. For clusters, validates all tables and indexes in cluster; for tables, validates all indexes.

DELETE STATISTICS. Removes current statistics.

The results appear in the INDEX_STATS  view, and some of the statistics also appear in the *_INDEXES series of views (Remote DBA, USER, and ALL). One thing to remember is that, unlike the Remote DBA_TABLES view, only one row at a time is saved in the INDEX_STATS view, the row for the last index analyzed.

The script in Source 6.3 estimates index sizes.

SOURCE 6.3 Script to calculate index space requirements for a proposed index.

rem  ******************************************************
rem  NAME:  IN_ES_SZ.sql    
rem  HISTORY:
rem  Date           Who                    What
rem  --------       -------------------    ---------------
rem  01/20/93       Michael Brouillette    Creation
rem  09/22/01       Michael Ault           Upgraded to 9i
rem  FUNCTION:  Compute the space used by an entry for an
rem   existing index.
rem  NOTES:  Currently requires Remote DBA.
rem  INPUTS:
rem         tname  = Name of table.
rem         towner = Name of owner of table.
rem         clist  = List of columns enclosed in quotes.
rem                  i.e., 'ename', 'empno'
rem         cfile  = Name of output SQL Script file
rem  ******************************************************
COLUMN name      NEW_VALUE     db NOPRINT
COLUMN dum1      NOPRINT
COLUMN isize     FORMAT 99,999.99
COLUMN rcount    FORMAT 999,999,999 NEWLINE
ACCEPT tname  PROMPT 'Enter table name: '
ACCEPT towner PROMPT 'Enter table owner name: '
ACCEPT clist  PROMPT 'Enter column list: '
ACCEPT cfile  PROMPT 'Enter name for output SQL file: '
SET HEADING OFF VERIFY OFF TERMOUT OFF PAGES 0 EMBEDDED ON
SET feedback OFF SQLCASE UPPER TRIMSPOOL ON SQLBL OFF
SET NEWPAGE 3
SELECT name FROM v$database;
SPOOL rep_out/&db/&cfile..sql
SELECT -1 dum1,
       'SELECT ''Proposed Index on table ''||'
FROM dual
UNION
SELECT 0,
       '''&towner..&tname'||' has '',COUNT(*) rcount,
       '' entries of '', (' 
FROM dual
UNION
SELECT column_id,
      'SUM(NVL(vsize('||column_name||'),0)) + 1 +'
FROM Remote DBA_tab_columns
WHERE table_name = '&tname'
   AND owner = '&towner'
   AND column_name in (upper(&clist))
   AND column_id <> (SELECT MAX(column_id)
                     FROM Remote DBA_tab_columns
                      WHERE table_name = UPPER('&tname')
                        AND owner = UPPER('&towner')
                        AND column_name IN (upper(&clist)))
UNION
SELECT column_id,
      'SUM(NVL(VSIZE('||column_name||'),0)) + 1)'
 FROM Remote DBA_tab_columns
 WHERE table_name = upper('&tname')
   AND owner = upper('&towner') AND column_name IN (upper(&clist))
   AND column_id = (SELECT MAX(column_id)
                    FROM Remote DBA_tab_columns
                     WHERE table_name = upper('&tname') 
                      AND owner = upper('&towner')
                      AND column_name IN (upper(&clist)))
UNION
SELECT 997, '/ COUNT(*) + 11 isize, '' bytes each.'''
FROM dual
UNION
SELECT 999,
       'FROM &towner..&tname.;'  FROM dual;
SPOOL OFF
SET TERMOUT ON feedback 15 PAGESIZE 20 SQLCASE MIXED
SET NEWPAGE 1
START rep_out/&db/&cfile
CLEAR COLUMNS

See Code Depot for Full Scripts

---

This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters". You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

Expert Remote DBA BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call BC Remote DBA today.