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

Instructor's Notes

(If applicable)

Index key compression removes redundant leading-edge key values from each leaf block. So instead of:
United States of America Rhode Island Narragansett
United States of America Rhode Island Newport
United States of America Rhode Island Providence
United States of America Rhode Island South Kingstown
Oracle would store:
United States of America Rhode Island Narragansett Newport Providence South Kingstown
The reduction in size can be dramatic. The query of USER_SEGMENTS shown above shows that, given an index on the same volume of data, the index size was reduced by 38% (1664 blocks to 640 blocks).
The price you will pay for this is a modest increase in CPU time to access and update the index and possibly more contention. Always test in your environment.
Optional Mini-Workshop
Review and run the supplied script INDEX_KEY_COMPRESSION.SQL. What is the difference in index size?

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

SQL> create index t2_idx
2 on t2 (owner, object_name, object_type)
3 compress nologging compute statistics;

Index created.

SQL> select segment_name, bytes, blocks
2 from user_segments
3 where segment_name in('T1_IDX', 'T2_IDX');

SEGMENT_NAME BYTES BLOCKS
------------------------- ---------- ----------
T1_IDX 13631488 1664
T2_IDX 5242880 640

Optional Mini-Workshop

Instructor's Notes

(If applicable)

Oracle allows one or more index keys to be sorted (and stored) in descending sequence. This makes it possible to use the index for queries that request the data in different sequences, for example:
select * from sales
ORDER BY region, sales desc, state;

NORTHEAST 1000000 MASSACHUSETTS
NORTHEAST 580000 VERMONT
NORTHEAST 460000 RHODE ISLAND
WEST 2000000 CALIFORNIA
WEST 840000 NEVADA

Note that a descending key is not necessary in this situation:
select * from sales
ORDER BY region desc, sales desc;
Oracle will simply read the index backwards.

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

create unique index t_idx on t (c1,c2,c3);

create unique index t_idx on t
(c1, c2 desc, c3);

select * from t
order by c1, c2 desc, c3

Instructor's Notes

(If applicable)

DELETE statements leave unused space in index leaf block. This space can be reused by INSERT operations. However, unless you are inserting rows with column value that will reuse the space, that space becomes unused “holes” in the index. (Remember the index sort sequence – will the index entry be inserted into an existing leaf block? If you are using a sequence, the value will always be increasing and be inserted on the right side of the index.) The index coalesce operation can fix this – compact the index to eliminate the “holes”.
In this example, I delete more than half of the rows in the “T1” table. (Note that there are better ways to remove a large number of rows, e.g. partition the table and drop or truncate the partition; I have used a DELETE to force a situation where coalesce will be helpful.)
Next, the ALTER INDEX VALIDATE STRUCTURE statement is used to populate the INDEX_STATS view. This view has statistics about the health of the index. (Refer to the Oracle9i Database Reference for details on this view.)
We see that – after the DELETE – there are 1515 leaf blocks used and that only 44% of the space allocated to the index is being used. The number of deleted leaf rows in the index exceeds 30%. This is about the limit – we should consider a coalesce operation.
Notes continue on the next page…

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

SQL> delete from t1 where rownum <= 150000;

150000 rows deleted.

SQL> analyze index t1_idx validate structure;

Index analyzed.

SQL> select lf_blks, pct_used, del_lf_rows/lf
2 from index_Stats;

LF_BLKS PCT_USED RATIO
---------- ---------- ----------
1515 44 .312774642

SQL> alter index t1_idx coalesce;

Index altered.

SQL> analyze index t1_idx validate structure;

Index analyzed.

SQL> select lf_blks, pct_used, del_lf_rows
2 from index_Stats;

LF_BLKS PCT_USED RATIO
---------- ---------- ----------
602 72 0

Instructor's Notes

(If applicable)

After the ALTER INDEX COALESCE operation there are only 602 leaf blocks, and the percent used has increased to 72%.
Supplemental Notes
Oracle also provides an ALTER INDEX REBUILD operation. This completely rebuilds the index (not just merge leaf blocks). Rebuild may help bitmap indexes, which suffer fragmentation, but is not often helpful with B-tree indexes. For an informative discussion on this, visit http://asktom.oracle.com and search on "coalesce vs. rebuild“.
However, ALTER INDEX REBUILD does provide more capability than the coalesce operation, like the ability to move and compress an index:
SQL> alter index t1_idx rebuild tablespace indx compress;

Index altered.

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

Instructor's Notes

(If applicable)

Which columns should be indexed? Well, certainly primary key and foreign key columns should be indexed. Indexing foreign keys is important for performance and scalability (for your convenience, the following notes are repeated in the Constraints lesson):

The index on the foreign key column can significantly improve join performance. Remember that most joins use “pk_column = fk_column” in the WHERE clause.
DELETE CASCADE will, if the column is left unindexed, scan the child table for each row deleted in the parent.
The scope of lock can increase from row to table if rows in the parent table are deleted or updated and the foreign key column is left unindexed.

To find unindexed foreign keys in an existing database, use Tom Kyte’s script at http://osi.oracle.com /~tkyte/unindex/index.html.
If you are experiencing unacceptable response time, also consider indexing columns used in the WHERE clause or ORDER BY clause. Many factors impact the decision to add an index. These include:

Notes continue on the next page…

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

Always test all applications!
See next slide for code to determine selectivity

Instructor's Notes

(If applicable)

Selectivity. How many rows will be returned if a condition is used on the column? If less than 20% of the total number of rows in the table, it is a candidate for an index. 20% is a general rule-of-thumb. You will see other percentages recommended by various sources. Always test with full volume of data!
Size of the table. If the table is very small (only occupies a few blocks), additional indexes probably will not help. This is probably not the cause of your response time problem.
Datatype and size of the column. Some datatypes cannot be indexed, e.g. LONG. VARCHAR2 datatypes are expanded to the maximum length for the index keys, so big VARCHAR2 columns create large indexes. Try to index only small columns, if possible.
DML activity on the table. Before adding an index to help the performance of the query in question, consider what other transactions usually occur against the table. Adding an index in this case may speed up the single query but may also hurt DML type transactions. It could even degrade the performance of other queries. Test, test, test.

When considering adding an index, check all of the factors – not just one. And, of course, always test all applications with a full volume of data.

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

Instructor's Notes

(If applicable)

The selectivity of a column can be thought of as the percentage of total rows that will be returned if we apply a condition to the column.
For example, what percentage of rows will be returned if I query the "T" table with “WHERE c1 = 1”. From the report shown above, we can see that roughly .003% of the rows will be returned. This is excellent selectivity and the cost-based optimizer would be likely to choose an index, if the size of the table were sufficient to warrant it.
This code is available in the supplied script COLUMN_SELECTIVITY.SQL.
Supplemental Notes
Columns with poor selectivity are not good choices for B-tree indexes – in fact the optimizer will often ignore it! However, bitmap indexes might be a useful structure in this case.

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

Update statistics first

SQL> exec dbms_stats.gather_table_stats –
> (user, 't', cascade=>true)

PL/SQL procedure successfully completed.

SQL> select column_name,
2 ( ut.num_rows / utc.num_distinct)
3 / ut.num_rows * 100
4 AS selectivity
5 from user_tables ut, user_tab_columns utc
6 where ut.table_name = utc.table_name
7 and ut.table_name = 'T'
8 /

COLUMN_NAME SELECTIVITY
------------------------------ -----------
C1 .003432887
C2 .052966102
Percentage of rows that will be returned for a single value

Instructor's Notes

(If applicable)

You have learned a lot about b-tree indexes, but there is more:

How big is the index? How much will it grow? You should be able to create a test index on full volume of data (if you cannot, you are probably not doing sufficient testing), query DBA_SEGMENTS to see the size. This will give you a good idea if you should place the index in a system-managed LMT or a UNIFORM LMT. If you do not know how fast and how much it will grow, then you will almost certainly want to create the index in a system-managed LMT.
The CREATE INDEX statement supports parallel create. Refer to the SQL Reference for more information.
B-tree indexes are just one of the index types supported. Don’t forget to research the other types.

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

Instructor's Notes

(If applicable)

You have learned that b-tree indexes can reduce I/O counts, eliminate sorts and generally improve the performance and scalability of applications. Testing is critical, so become comfortable with tools such as SQL*Plus AUTOTRACE. Improperly used, indexes can actually hurt performance, so testing is critical.
You also saw that Oracle supports composite indexes. These can be useful when more than one column is frequently used in the WHERE clause.
Oracle provides many good features, including:

Compression
NOLOGGING
COMPUTE STATISTICS
NOSORT

Become familiar with the proper use of these features.

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

Instructor's Notes

(If applicable)

Composite Index Workshop
1. Run the supplied script “BIGTABLE.SQL”. Describe the BIG table to become familiar with the makeup of the table.
2. Use AUTOTRACE to trace the following query
- SQL> select * from big where object_type = 'TABLE'
- 2 and status = 'INVALID';
1. What is the access path used?
2. Create an index on the two columns used in the query. Which column should be first?
3. Re-trace the query? What was the access path used?

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