All About Indexes:

What to Index, When, and Why Mark J. Bobak OOUG, 17-Jan-2013

Who Am I?

 Senior Oracle DBA at ProQuest Company in Ann Arbor, MI  Member of the OakTable since 2002  Oracle ACE since 2010  Regular Presenter at Local, National, and International Conferences

Disclaimer

 At least one statement in this presentation is in error, but it may be this one.

Summary/Outline

 Index Structure   B-Tree Bitmap  Indexing Strategies   Bitmap B-Tree Common Indexing Myths  Review  Q/A

B-Tree Index Structure

Root Branch Leaf

B-Tree Index Leaf Block

Key Adams Adams Barker Barker Carter Carter Dodge Dodge Franklin Franklin Jones Jones ROWID AAAUCOAACAANZNDAAA AAAUCOAACAANZNDAAG AAAUCOAACAANZNDAAF AAAUCOAACAANZNDAAH AAAUCOAACAANZNDAAD AAAUCOAACAANZNDAAK AAAUCOAACAANZNDAAB AAAUCOAACAANZNDAAJ AAAUCOAACAANZNDAAC AAAUCOAACAANZNDAAI AAAUCOAACAANZNDAAE AAAUCOAACAANZNDAAL

Clustering Factor

 The clustering factor of an index is an indicator of the order of the data in the table, relative to the index key.

 The clustering factor will range from the number of blocks in the table (best case) to the number of rows in the table (worst case).

 Only relevant when you have index range scan followed by TABLE ACCESS BY ROWID operation.

Bitmap Index Structure

Root Branch Leaf

Bitmap Index Leaf Block

Key Adams Barker Carter Dodge Franklin Jones Start ROWID AAAUCOAACAANZNDAAA AAAUCOAACAANZNDAAA AAAUCOAACAANZNDAAA AAAUCOAACAANZNDAAA AAAUCOAACAANZNDAAA AAAUCOAACAANZNDAAA End ROWID AAAUCOAACAANZNDAAL AAAUCOAACAANZNDAAL AAAUCOAACAANZNDAAL AAAUCOAACAANZNDAAL AAAUCOAACAANZNDAAL AAAUCOAACAANZNDAAL Bitmap 100000100000 000001010000 000100000010 010000000100 001000001000 000010000001

Indexing Strategies - Bitmap

 When To Use a Bitmap Index?

  Enterprise Edition only!

NO DML – Just Don't Do It!

Extremely high cost of maintenance “Low Selectivity” Columns    But, what's “low selectivity”?

The standard example is GENDER – (M/F) What about higher selectivities? 10s, 100s, 1000s, even 100,000s of distinct values?

Indexing Strategies – Bitmap (cont.)

 Counting   Bitmap indexes are very efficient for COUNT(*) operations Combining   Bitmap indexes excel when you have one bitmap index per column, and the where clause predicates specify multiple columns that each have their own bitmap index This allows for BITMAP AND/BITMAP OR and BITMAP MERGE operations  Never for columns where data is unique

Indexing Strategies – Bitmap (cont.)

 To improve efficiency of bitmap indexes, minimize records per block before creating indexes.

 This operation will scan the table, determine the maximum number of rows in any block of the table, and set that as the maximum limit of rows allowed in any block.

 This called the Hakan Factor and is stored in SYS.TAB$ in the SPARE1 column.

 Beware of ORA-14642/ORA-14643

Indexing Strategies – B-Tree

 Typical Index in use is B-Tree  Excellent for use in OLTP-style environments  Indexes may be used to avoid SORT operations in some cases  Function-based Indexes were introduced in 8i (really expression based indexes)  Reduce Index Size    Compression FBI may also be used to index a subset of data Starting with 11g, Virtual Columns make for a convenient way to “hide” the function.

Avoiding SORT Operations

 A carefully constructed index may allow for avoiding a SORT operation  Example:    IN_PROCESS Table Several 100s of millions of rows Data must be processed in PRIORITY Order

Avoiding SORT Operations (cont.)

NAME ----------- DOC_ID THREAD_ID STATUS STATUS_DATE PRIORITY DATATYPE ----------- NUMBER(15) NUMBER(1) VARCHAR2(10) DATE NUMBER(3) NULL?

----------- NOT NULL NOT NULL NOT NULL NOT NULL NOT NULL create index my_perf_index on in_process(thread_id,status); select doc_id from (select doc_id from IN_PROCESS where THREAD_ID = :b1 and STATUS = :b2 order by PRIORITY,STATUS_DATE) where rownum < 101;

Avoiding SORT Operations (cont.)

----------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 2 (50)| 00:00:01 | |* 1 | COUNT STOPKEY | | | | | | | 2 | VIEW | | 1 | 13 | 2 (50)| 00:00:01 | |* 3 | SORT ORDER BY STOPKEY | | 1 | 70 | 2 (50)| 00:00:01 | | 4 | TABLE ACCESS BY INDEX ROWID| IN_PROCESS | 1 | 70 | 1 (0)| 00:00:01 | |* 5 | INDEX RANGE SCAN | MY_PERF_INDEX | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------

Avoiding SORT Operations (cont.)

NAME ----------- DOC_ID THREAD_ID STATUS STATUS_DATE PRIORITY DATATYPE ----------- NUMBER(15) NUMBER(1) VARCHAR2(10) DATE NUMBER(3) NULL?

----------- NOT NULL NOT NULL NOT NULL NOT NULL NOT NULL Create index my_new_perf_indx on in_process(thread_id,status,priority,status_date,doc_id); select doc_id from (select doc_id from IN_PROCESS where THREAD_ID = :b1 and STATUS = :b2 order by THREAD_ID, STATUS,PRIORITY,STATUS_DATE) where rownum < 101;

Avoiding SORT Operations (cont.)

----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 | |* 1 | COUNT STOPKEY | | | | | | | 2 | VIEW | | 1 | 13 | 1 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN| MY_PERF_INDEX | 1 | 70 | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------

Function-based Indexes

 FBI Was Introduced in 8i  Allows For Creating an Index on the output of a Function of a Column  Function

must

be deterministic  You can lie to Oracle, but it's a bad idea  Typical usage: create index FBI on my_tab(upper(surname));

Index Compression

 Repeated Key Values in Leaf Blocks may be Compressed  Non-unique indexes   Multi-column unique indexes alter index my_ind compress n; where n is the compression prefix, i.e., the number of leading columns subject to compression –

Using FBI to Index Subset of Table

 Function-based Indexes May be used to Index a Subset of Dataset  Can Reduce the Index Size Substantially  Makes the Index

much

more cache-friendly, can be a big performance boost, in some cases  Typical example: create index FBI on my_tab(case when n = 1 then n else null end);

Using FBI to Index Subset of Table, p2

 Problem is writing the correct query, with the correctly matching expression, so that the optimizer will take advantage of the index  The more complex the function/expression, the more cumbersome to integrate in SQL statement.

Example: select * from my_tab where (case when n=1 then n else null end) = 1;

Virtual Columns

  Virtual Columns – One of My Favorite New Features in 11g Virtual Columns allow you to “hide”

ugly

functions and expressions, and allow for much cleaner SQL statements Example: alter table my_tab add(n_equals_one generated always as (case when n=1 then n else null end);

Virtual Columns

NAME ----------- N N_EQUALS_ONE DATATYPE ----------- NUMBER NUMBER NULL?

----------- Create index FBI on my_tab(n_equals_one); select * from my_tab where n_equals_one = 1;

Common Indexing Myths

 Small Tables Don't Need to Be Indexed  Rebuild Your Index To Improve Clustering Factor  Rebuild Index when BLEVEL > x, where x = 4, 5, 6, etc

Myth #1

Small Tables Don't Need To Be Indexed It's been often stated that “small” tables need not be indexed, as a full table scan will be more efficient.

Really? Is that true?

It can easily be demonstrated that even indexing a single row table can provide benefit.

Myth #2

Rebuild Your Index to Improve Clustering Factor Clustering factor is an indicator of the order of the data in the

table

, relative to the index key column(s). Since rebuilding an index can

NEVER

change the table data order, rebuilding the index will

NEVER

modify the clustering factor.

Myth #3

Rebuild Index when BLEVEL > x, where x = 4, 5, 6, etc The BLEVEL is largely dependent on block size, key length, and the number of non-null key values.

Unless there has been a very large deletion, rebuilding an index is not likely to reduce the BLEVEL at all.

Q/A

 Questions?

 Comments?

 Criticisms/Complaints?

My Contact Info

Mark J. Bobak [email protected]

http://markjbobak.wordpress.com/ Thanks for your time!