1 / 30

All About Indexes:

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.

baker-lynch
Télécharger la présentation

All About Indexes:

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. All About Indexes: What to Index, When, and Why Mark J. Bobak OOUG, 17-Jan-2013

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

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

  4. Summary/Outline Index Structure B-Tree Bitmap Indexing Strategies Bitmap B-Tree Common Indexing Myths Review Q/A

  5. B-Tree Index Structure Root Branch Leaf

  6. B-Tree Index Leaf Block Key ROWID AAAUCOAACAANZNDAAA AAAUCOAACAANZNDAAG AAAUCOAACAANZNDAAF AAAUCOAACAANZNDAAH AAAUCOAACAANZNDAAD AAAUCOAACAANZNDAAK AAAUCOAACAANZNDAAB AAAUCOAACAANZNDAAJ AAAUCOAACAANZNDAAC AAAUCOAACAANZNDAAI AAAUCOAACAANZNDAAE AAAUCOAACAANZNDAAL Adams Adams Barker Barker Carter Carter Dodge Dodge Franklin Franklin Jones Jones

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

  8. Bitmap Index Structure Root Branch Leaf

  9. Bitmap Index Leaf Block Key Start ROWID End ROWID Bitmap 100000100000 000001010000 000100000010 010000000100 001000001000 000010000001 AAAUCOAACAANZNDAAA AAAUCOAACAANZNDAAA AAAUCOAACAANZNDAAA AAAUCOAACAANZNDAAA AAAUCOAACAANZNDAAA AAAUCOAACAANZNDAAA AAAUCOAACAANZNDAAL AAAUCOAACAANZNDAAL AAAUCOAACAANZNDAAL AAAUCOAACAANZNDAAL AAAUCOAACAANZNDAAL AAAUCOAACAANZNDAAL Adams Barker Carter Dodge Franklin Jones

  10. 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?

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

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

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

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

  15. Avoiding SORT Operations (cont.) NAME DATATYPE NULL? ------------ ------------ ------------ DOC_ID NUMBER(15) NOT NULL THREAD_ID NUMBER(1) NOT NULL STATUS VARCHAR2(10) NOT NULL STATUS_DATE DATE NOT NULL PRIORITY NUMBER(3) 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;

  16. 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 | • ------------------------------------------------------------------------------------------------

  17. Avoiding SORT Operations (cont.) NAME DATATYPE NULL? ------------ ------------ ------------ DOC_ID NUMBER(15) NOT NULL THREAD_ID NUMBER(1) NOT NULL STATUS VARCHAR2(10) NOT NULL STATUS_DATE DATE NOT NULL PRIORITY NUMBER(3) 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;

  18. 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 | • ------------------------------------------------------------------------------------

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

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

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

  22. 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;

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

  24. Virtual Columns NAME DATATYPE NULL? ------------ ------------ ------------ N NUMBER N_EQUALS_ONE NUMBER Create index FBI on my_tab(n_equals_one); select * from my_tab where n_equals_one = 1;

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

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

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

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

  29. Q/A Questions? Comments? Criticisms/Complaints?

  30. My Contact Info Mark J. Bobak mark@bobak.net http://markjbobak.wordpress.com/ Thanks for your time!

More Related