1 / 28

Boris Gurov Support Analyst Oracle Bulgaria

All you need to know about Oracle Indexes. Boris Gurov Support Analyst Oracle Bulgaria. What is a table?. A table is a collection of data blocks Each data block physically stores several rows Each row is composed of several fields containing data. Data Blocks. row 1 fields row 2 fields

miracle
Download Presentation

Boris Gurov Support Analyst Oracle Bulgaria

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 you need to know about Oracle Indexes Boris GurovSupport Analyst Oracle Bulgaria

  2. What is a table? • A table is a collection of data blocks • Each data block physically stores several rows • Each row is composed of several fields containing data Data Blocks row 1 fields row 2 fields . . row m fields row m+1 fields . . row n fields

  3. How can data in a tablebe accessed? • Data in a table can be accessed by reading the table’s data pages sequentially looking for the required data. • Can we speed this up?

  4. The Concept of an Index • We want a cross-reference of the table to enable us to access the data rows quickly • We choose a key - the data fields that we normally use to locate a data row • We store a list of key values and for each key value we write down the physical location of the data row

  5. Key1 Key2 …….. rowid Rowid ……... What does an index look like? row 1 fields row 2 fields ……

  6. What does an index look like? Key1 Key2 …….. Keym Keym …… rowid Rowid ……... rowid Rowid ….. row n fields …. row 1 fields row 2 fields …… row m fields row m+1 fields ……..

  7. What does an index look like? Keym ……Keyn Key1 Key2 …….. Key1 Keym …….. Rowid ….. Rowid rowid Rowid ……... ……... row n fields …. row 1 fields row 2 fields …… row m fields row m+1 fields ……..

  8. What does an index look like? Key1 Key …….. Branch Blocks Key1 Keym …….. …… Key Key1 Key2 …….. rowid Rowid ……... Keym Keyn ……. rowid rowid ……. Key rowid Leaf blocks …...…. row 1 fields row 2 fields …… row m fields row m+1 fields …….. row n fields …. Table

  9. Creating a Balanced Tree Index • An Oracle index has a balanced tree (B*-tree) structure • In a balanced tree, the shortest path from the root block to a leaf block is either equal to the longest path or to the longest path minus 1 • This guarantees that no matter what data we are looking for, navigating the index will use the same amount of I/O operations

  10. Clustering Factor (CF) • The CF is a number that indicates the extent to which the “SORT” sequences of the table and index match • The CF ranges between the number of blocks containing data and the number of rows in the table • The CF represents the number of I/O operations performed on the table when the entire index is scanned

  11. Index Clustering -Low Clustering Factor R L L L L Data Data Data Data Data Data Row 1 Row 2 Row 3 Row 4 Row 5 Row 6 Row 7 Row 8 Row 9 Row10 Row 11 Row 12 Row 13 Row 14 Row 15 Row 16 Row sequence: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16 Page sequence: 1, -, -, 2, -, -, 3, -, -, 4, -, -, 5, -, -, 6 Low Clustering Factor: 6

  12. Index Clustering -Low Clustering Factor R L L L L Data Data Data Data Data Data Row 1 Row 4 Row 7 Row 10 Row 13 Row 16 Row 2 Row 8 Row 5 Row 11 Row 9 Row 15 Row 3 Row 14 Row 12 Row 6 Row sequence: 1, 4, 7, 10, 13, 16, 2, 8, 5, 11, 9, 15, 3, 14, 12, 6 Page sequence: 1, 2, 3, 4, 5, 6, 1, 3, 2, 4, 3, 5, 1, 5, 4, 2 High Clustering Factor: 16

  13. Adjusting the Cost in Oracle 8i • In Oracle 8i, the total cost of the index is adjusted using the following formula:Adjusted cost = cost * OPTIMIZER_INDEX_COST_ADJ / 100 • This adjustment bypasses the Oracle assumption of a low buffer hit ratio used in the data access calculation

  14. Data in Oracle’s Data Dictionary • When the Analyze command is used to collect statistics, the following data on indexes is collected: • Index tree depth • Number of leaf blocks • Number of distinct values • Clustering factor • Average number of leaf blocks per index value • Average number of data blocks per index value

  15. More Data • More data on indexes can be collected using the Validate Index command • The data collected is stored in a table called INDEX_STATS. • Table INDEX_STATS is not part of the data dictionary and contains only one row with data on the last index for which the command was executed

  16. Table INDEX_STATS contains the following useful data: • NAME: Name of the index for which the Validate Index command was executed • HEIGHT: Number of levels + 1 • BLOCKS: Total number of index blocks • LF_ROWS: Number of rows in the leaf pages • LF_BLKS: Total number of leaf pages • BR_ROWS: Total number of rows in non-leaf blocks • BR_BLKS: Total number of non-leaf blocks

  17. LF_ROWS_LEN: Total length of all leaf rows • BR_ROWS_LEN: Total length of all non-leaf rows • DEL_LF_ROWS: Number of logically deleted rows • DEL_LF_ROWS_LEN: Total length of all deleted rows • DISTINCT_KEYS: Number of distinct keys in the index • MOST_REPEATED_KEY: Most repeated key • ROWS_PER_KEY: Average number of rows per key

  18. More Data in the Data Dictionary • Table Statistics • Number of rows • Number of currently used data blocks • Column Statistics • Number of distinct values • Minimum value • Maximum value • Histogram information - provide improved selectivity information in cases where data distribution is not uniform

  19. The Cost Based Optimizer (CBO) • Uses knowledge and assumptions on data distribution and access patterns to select an access path to data • Introduces the concept of a “hint”. Hints can force the use of specific access paths • Optimizes queries for best throughput, unless instructed otherwise • Selects an index ONLY if the price of using the index is lower than the price of a full table scan

  20. Filtering Factor (FF) Predicate Estimated Filter Factor = 1/(number of distinct values) IN (values in IN)/(number of distinct values) > (highest value - requested value) (highest value - lowest value) < (requested value - lowest value) (highest value - lowest value) BETWEEN (requested high - requested low) (highest value - lowest value) Logical Operators Estimated Filter Factor expr1 AND expr2ff1 * ff2 expr1 OR expr2 (ff1 + ff2) - (ff1 * ff2)

  21. Unique Index Scan Number of blocks read = index height + 1 (to scan the index)+ 1 (to access the table data) A C

  22. A B Range Index Scan Number of blocks read = index height + 1 + leaves * FFM+ CF * FFSM CF - index clustering factor FFM - filter factor of matching predicates FFSM - filter factor of all screening and matching predicates C

  23. B Full Index Scan Number of blocks read = leaves + CF * FFSM CF - index clustering factor FFSM - filter factor of all screening and matching predicates C

  24. Fast Full Index Scan Number of blocks read = IBLK / DBF + CF * FFSM IBLK - number of index blocks DBF - DB_FILE_MULTIBLOCK_READ_COUNT CF - index clustering factor FFSM - filter factor of all screening and matching predicates

  25. Full Table Scan Number of blocks read = BLK / DBF BLK - number of dirty table blocks DBF - DB_FILE_MULTIBLOCK_READ_COUNT init.ora parameter

  26. Summary • An index improves performance ONLY if used correctly • It is your responsibility to determine whether the optimizer selected the best access path to the required data and to correct the optimizer’s decision when you believe it failed to select the best access path

More Related