SkillBuilders | Oracle 9i Administration

Instructor's Notes

(If applicable)

Lesson 14: Indexes
This lesson covers B-tree indexes. We will learn what they are, what makes them work (organization) and many other things that will let you put them to good use, including:

Turning off redo logging when creating to increase performance of CREATE TABLE.
Collecting statistics with DBMS_STATS.
Making intelligent decisions on what columns should be indexed.
When and how to create multi-column (composite) indexes.
Using index compression.
When and where to create indexes.

However, as much as indexes are often a critical part of SQL and application tuning, this is not an exhaustive lesson on SQL and application tuning. Though we will scratch the surface of some tuning issues, you should follow up with more study on tuning. I recommend the following sources:

SkillBuilders’ SQL Tuning for Developers and DBAs course
Effective Oracle by Design, by Tom Kyte ISBN 0-07-223065-7

Other Text:
(Examples or comments displayed on slide, if any).

This is not an exhaustive lesson on tuning

Instructor's Notes

(If applicable)

Indexes are an optional object that is stored within a disk segment. They are created with CREATE INDEX statement (they can also be created as a result of PRIMARY KEY and UNIQUE constraints; more on this in the constraints lesson). Indexes contain pointers (in the form of ROWIDs) to table data. This reduces I/O by eliminating the need to sequentially scan a table’s blocks when searching for a row (sequential scan of table blocks is called a “full table scan”).
Indexes are actually optional, so why should we create them? Some answers:

Without indexes, Oracle would be forced to perform sequential scans of table blocks (full table scan) for all query and DML operations. Scalability and performance of your applications would be severely limited.
Indexes can lower I/O counts of query and DML operations. (We will illustrate exactly how later in this lesson.)
Oracle builds indexes to help it enforce unique (and primary key) constraints. We will explore this too.

Notes continue on the next page…

Other Text:
(Examples or comments displayed on slide, if any).

Properly used, indexes help. They can hurt; always test!

Instructor's Notes

(If applicable)

Indexes entries are always kept in sorted order. Therefore, by reading the rows in the order of the index entries, Oracle can eliminate the need for post-processing sort operations.

Indexes don’t fix all performance problems and can actually degrade performance if used improperly. Always test all applications with any new index you create.

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

Oracle supports several index structures. The B-tree is the default structure – and what this lesson will focus on. Here’s a quick mention of the other types:

A bitmap index uses bits to reveal the value in a column. Each distinct value in the column creates an index entry; the entry contains a bitmap – a series of bits that “point” to table rows. The bits go through a conversion process a execution time (BITMAP to ROWID conversion). This index is useful when there are very few distinct values in a column and when the column data rarely changes. These indexes are often used in read-mostly or read-only data warehouse environments.
The bitmap join index is similar to a bitmap index but is actually a pre-join of columns from multiple tables. This reduces or eliminates the need for the join operation at query time. These indexes are often used in read-mostly or read-only data warehouse environments.
A reverse key index stores the index key in reverse – so key “12345” is stored as “54321”. This helps avoid index hot spots in Real Application Cluster (formerly called Oracle Parallel Server) environments. Reverse key indexes only support equality queries, e.g. WHERE key = 12345; they do not support range scans, e.g. WHERE key > 12345;

Notes continue on the next page…

Other Text:
(Examples or comments displayed on slide, if any).

This lesson covers B-Tree indexes
Tuning course covers other indexes

Instructor's Notes

(If applicable)

A function-based index is an index created with an SQL function. This would support a query that uses that function, e.g. WHERE upper(name) = ‘PEGGY’.
A domain index is a user-defined index. It is part of the “extensible indexing” feature provided by Oracle. Oracle provides some domain indexes, e.g. the text index “indextype is ctxsys.context”. This provides the ability to query text columns with the CONTAINS operator:select * from t where contains( col, ‘string’) > 0. Refer to the Oracle9i Database Performance Tuning Guide and Reference or Tom Kyte’s Effective Oracle by Design text for more information on domain indexes.

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

The B-Tree index has a structure conceptually similar to the illustration above.
Assume that this is a unique index on the “NAME” column of hypothetical table “T”.
We see that there is:

A root block at the top of the structure. The root block is the entry point into the index (i.e. the first block read) for most types of index scans. The root block contains links (to lower-level child blocks) and index keys. The key is some portion of the indexed value used to make branching decisions.
“n” levels of branch blocks. A branch block contains links (to lower-level child blocks) and index keys. In this illustration, there are two levels of branch blocks (the root block is considered a branch block), so the total number of levels in this index is 3. The number of levels depends on the total number of index values, the length of the index entries and the block size.
A leaf node. Leaf node blocks contain every indexed value along with a ROWID, which is the address of the row in the table. Unique indexes have one ROWID for each value; non-unique indexes have potentially many ROWID’s for each value.

Notes continue on the next slide…

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

So, if a query such as this:
select * from t where name = ‘KEVIN’;
where executed, and the optimizer chose to use the index, Oracle would do the following:
Read the root node and perform the following comparisons of index-key to query-value:
‘Ja’ >= ‘Ke’
‘Pe’ >= ‘Ke’
Since the second condition is TRUE, and key ‘Pe’ is greater than value ‘Ke’, Oracle reads the child block identified by the link before the key ‘Pe’.
The same comparisons are performed using the keys from the child block:
‘Ke’ >= ‘Ke’
‘Na’ >= ‘Ke’
Since the first comparison is TRUE, and ‘Ke’ is equal to ‘Ke’, Oracle reads the leaf block identified by the link after the key ‘Ke’.
When the leaf block is read, the unique index scan changes the comparison to equality, essentially searching the leaf block for the value specified in the WHERE clause condition (‘Kevin’ in this example). If a match is found, Oracle uses the ROWID in the index to read the block from the table. If no match is found Oracle returns “row not found”.
For more information on index structure and how they are used, refer to Chapter 10 of the Oracle9i Concepts manual and Chapter 1 of the Oracle9i Database Performance Tuning Guide and Reference.

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

The goal of CBO is quite straightforward: find the lowest cost plan to solve the query (i.e. return the rows). Given a query such as
select * from t where name = ‘KEVIN’;
and assuming a unique index exists on the NAME column, Oracle would have two obvious paths to the data:
1. Full table scan. This is a sequential read of the blocks in the table. On average, Oracle would read ½ the blocks in the table, 279 blocks in my sample table.
2. Unique index scan. A unique index scan reads the index blocks to access the table (as previously described). Given a 3-level index, four logical I/O’s (accesses to a block in the buffer cache) and a maximum of four physical I/O’s would be required to retrieve the row.

Other Text:
(Examples or comments displayed on slide, if any).

SQL> select blocks from user_tables
2 where table_name = 'T';

BLOCKS
----------
558

SQL> select blevel from user_indexes
2 where index_name = 'T_NAME';

BLEVEL
----------
2

BLEVEL is relative to 0

Instructor's Notes

(If applicable)

Use the SQL*Plus AUTOTRACE tool to externalize the access path decision made by CBO and show I/O statistics. You will need to have a “plan table” to successfully execute SET AUTOTRACE ON; create a plan table with the supplied script UTLXPLAN.SQL.
The statistics shown above (an excerpt) reveal that 4 physical I/O’s (“physical reads”) were required to execute the query (obviously I have used a contrived NAME value; this was just to get the statistics I wanted for this example). “Consistent gets” are logical I/O’s; logical I/O’s are required to access the block in the buffer cache. The server process, in the execution phase of the query, will do the following:

I/O 1 retrieves the root node block
I/O 2 retrieves a branch node block
I/O 3 retrieves a leaf block
I/O 4 retrieves the row from the table data block using the ROWID

The performance should be adequate and consistent, regardless of the value specified in the query’s WHERE clause.
See the script UNIQUE_INDEX_SCAN.SQL for a working demonstration of AUTOTRACE and unique index scan.

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

There are many essential points you should be aware of for a complete understanding of B-tree indexes:

The number of levels in the index is not fixed, and is determined by key length, block size and number of rows indexed. As new rows are inserted, index block splitting can occur, sometimes changing the number of levels in the index.
The index is always kept balanced, i.e. all leaf blocks are at exactly the same level/depth. Thus, performance is consistent and hopefully adequate no matter what value is used in the search.
Indexes are automatically updated by DML operations, which adds to the overhead of DML operations. Indexes – like table rows – are locked, which can cause blocking (transactions waiting for other transactions to complete).
B-tree indexes do not contain entries for NULLs. This leads to the facts that

A table with a unique index on a nullable column can have multiple rows with NULL in that column.

Notes continue on the next page…

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

“WHERE column IS NULL” is not an indexable condition when using b-tree indexes.
A very large table could have a very small index if the index is built on a nullable column and most rows “contain” null in the column. This can actually work to your advantage if a common search is on some value in the column (as opposed to searching for NULL).

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

Though there are many optional parameters, the CREATE INDEX statement can be quite simple. Required parameters and clauses are:

Index name – 1 to 30 alpha-numeric characters. Must be unique within the schema. Must start with an alphabetic character. “T_TABLE_NAME” is the index name specified in the first example shown above.
Table name – “t” in these examples.
Column name(s) – One or more comma-separated columns in the table.

Optional parameters include:

TABLESPACE – Specify the name of the tablespace you want to create the index in. Some DBAs prefer to keep indexes in a separate tablespace (sometimes on a separate disk) from the tables. This is not a performance decision; performance will not degrade if the index is on the same disk as the table. (The Oracle Concepts Guide is incorrect on this. It states “Furthermore, you can improve performance of queries that use an index by storing an index and its table in different tablespaces located on different disk drives, because Oracle can retrieve both index and table data in parallel.” Visit http://www.niall.litchfield.dial.pipex.com/OracleMyths.zip for a discussion of this and other myths.).

Notes continue on the next page…

Other Text:
(Examples or comments displayed on slide, if any).

SQL> create table t
2 tablespace users as
3 select table_name, num_rows from user_tables;

Table created.

SQL> create unique index t_table_name
2 on t (table_name) nologging nosort
3 compute statistics tablespace indx;

Index created.
Collect stats for CBO when creating; efficient!

SQL> create index t_num_rows on
2 t (num_rows) nologging compute statistics
3 tablespace indx;

Index created.

Instructor's Notes

(If applicable)

TABLESPACE (continued) – Separating indexes from tables is a management and a “feel good” issue. For example, perhaps you’d like to backup the tablespaces on a different schedule.
NOLOGGING – Suppress redo logging. See the next page for more information.
NOSORT – Use this parameter to inform Oracle that the data in the column is already in sorted order. Oracle skips the costly sort step when creating the index. Use when you have just loaded the table in sorted order. An error is returned if the data is not in sorted order.
COMPUTE STATISTICS – Gather and store index statistics (e.g. BLEVEL) while creating the index. This is significantly more efficient than gathering statistics after the CREATE with DBMS_STATS. Index statistics are required by the cost based optimizer (CBO).
UNIQUE – This index will accept only unique values. Oracle returns the following error if an attempt is made to insert a duplicate value:
SQL> insert into t (table_name) values ('EMP');
insert into t (table_name) values ('EMP')
*
ERROR at line 1:
ORA-00001: unique constraint (DAVE.T_TABLE_NAME) violated

Refer to the supplied script CREATE_BTREE_INDEXES.SQL for a working version of this code.

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

The NOLOGGING option allows us to bypass logging for the index creation. (Note that NOLOGGING replaces the V7.2 UNRECOVERABLE option.)
NOLOGGING can cut the time to create the index in half.
Since an index created with the NOLOGGING option cannot be recovered, be sure to execute the CREATE INDEX statement(s) after performing a database recovery. You will learn how to recover objects later in the Introduction to RMAN lesson.
Optional Mini-Workshop
Review and execute the supplied script LOGGING.SQL.

Other Text:
(Examples or comments displayed on slide, if any).

Optional Mini-Workshop.

Instructor's Notes

(If applicable)

Statistics on an object are needed by the cost-based optimizer in order to make decisions on execution plans. Statistics can be generated with the ANALYZE command or with the DBMS_STATS package. The ANALYZE command has been deprecated with Oracle9i. Oracle recommends using the DBMS_STATS package to gather statistics.
The example above uses GATHER_INDEX_STATS with the following parameters:

OWNNAME – Owner (schema) of the index. I have specified the function USER which resolves into the username of the current session.
INDNAME – Index name.
ESTIMATE_PERCENT – What percent of the index should be read to generate statistics. Very large indexes can take a long time to analyze. I have specified the AUTO_SAMPLE_SIZE option which asks Oracle to figure it out- I.e. let Oracle determine how much data should be read to collect valid statistics. The default is to read the entire index.
DEGREE – The degree of parallelism. Increasing parallelism on multi-CPU machines can have a dramatic positive affect on performance of statistic collection. DEFAULT_DEGREE looks at initialization parameters. Though not specifically documented, it is a good bet it looks at parameters PARALLEL_AUTOMATIC_TUNING and PARALLEL_ADAPTIVE_MULTI_ USER.

Other Text:
(Examples or comments displayed on slide, if any).

SQL> exec dbms_stats.gather_index_stats(user, 't_c2', -
> estimate_percent=>dbms_Stats.auto_sample_size, -
> degree=>dbms_Stats.default_degree)

PL/SQL procedure successfully completed.

Instructor's Notes

(If applicable)

Indexes Workshop
1. Review and execute the supplied script SETUP_INDEX_WORKSHOP .SQL. Take note of the elapsed time required to run the PL/SQL program.
2. Create an index on table "T" with the goal of reducing the elapsed time required to run the PL/SQL program.
3. Rerun the PL/SQL program that is coded in the supplied script SETUP_INDEX_WORKSHOP.SQL (do not rerun the full script because it drops the table and thus also drops the index) we just created.

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

A composite index is an index created on more than one column of a single table. This is useful if the columns are often or always used together in the WHERE clause. Adding a column to the index can also increase the selectivity of the index, increasing the likelihood that CBO will choose to use the index.
The SQL*Plus AUTOTRACE command can be used to determine if CBO chose to use the index:
SQL> set autotrace on
SQL> select * from t where c1 = 'x' and c1 = 1;
. . .
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=3
2 1 INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE)

The execution plan shows that Oracle used the index “T_IDX” to execute the query.

Other Text:
(Examples or comments displayed on slide, if any).

SQL> create unique index t_idx on t (c1,c2) nologging compute stat

Index created.

SQL> select *
2 from t
3 where c1 = 288
4 and c2 = 2;

C1 C2 C3
---------- ---------- ---------
288 2 test

Instructor's Notes

(If applicable)

When creating a composite index, you will need to decide which column to put first. In Oracle9i, the primary consideration is the selectivity of the columns (how many distinct values are in the column). You can easily calculate selectivity with the COUNT function:
SQL> select count(distinct owner), count(distinct object_name) from big;

Generally, in Oracle9i, the least selective column(s) first will provide the most benefit. This is because of a new access path called “skip scan”*. Index key compression, if used, also benefits from putting the least selective column first. (Compression is discussed next in this lesson.) Given the selectivity of the OWNER and OBJECT_NAME columns in my “big” table, I would create:
SQL> create index big_idx1 on big (owner, object_name) nologging compute statistics;

Index created.
Notes continue on the next page…

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

Despite these general recommendations, always test in your environment. Review the supplied file TEST_COMPOSITE_INDEX.SQL for an example of comparing different sequences of index keys.
*Skip Scan Access Path
In releases prior to Oracle9i, the leading the column of the index had to be referenced in a WHERE clause in order for the optimizer to consider using the index. This caused DBAs to build multiple indexes to support such behavior. In Oracle9i a new feature called “skip scan” allows the optimizer to still consider the index even if the leading column is not referenced in the WHERE clause.

Other Text:
(Examples or comments displayed on slide, if any).