1 / 14

B-Tree Index

B-Tree Index. B-Tree indexes are used to avoid large sorting operations. For example, a SQL query requiring 10,000 rows to be presented in sorted order will often use a B-Tree index to avoid the very large sort required to deliver the data to the end user. . B-Tree Example.

javen
Download Presentation

B-Tree Index

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. B-Tree Index • B-Tree indexes are used to avoid large sorting operations. For example, a SQL query requiring 10,000 rows to be presented in sorted order will often use a B-Tree index to avoid the very large sort required to deliver the data to the end user.

  2. B-Tree Example An Oracle B-Tree Index Source (http://www.dba-oracle.com)

  3. B-Tree Example Continued • Then look for page/row with number “99” • Then scan for actual entry in index • Read pointer • Move to table • Read data

  4. About B-Tree Indexes • Root and Branch blocks = approx 2% of index (small) • In frequent hit situations, both blocks loaded in Data Buffer all the time • Then only 2 I/O may be required: • One to read leaf block • One to read the table • In practice, read in index and in table may require reading several blocks • e.g: Several similar names • Index spans two (or more) leaf blocks • Each record is in a different data block

  5. Creating and Using Indexes • Important for live access, but even more for querying with multiple tables • Value matching is costly process in RDB • No pointers • Connection purely on comparison basis only • One row with all other rows • If link between 2 huge tables, performance is low • All RDBs use some form of indexing • Some complexity involved as index can reduce physical I/O while increasing logical I/O (i.e. CPU time)

  6. B-Tree Indexing • Creating an index means creating a table with X+1 columns • X = number of columns in index • Rowid (added field) [table block + row] • Index is then copied into consecutive blocks • PCTFREE function leaves space for growth of data (but high value will generate many leaf blocks) • Pointer is added to previous and next leaf blocks in header of block

  7. B-Tree Indexing • Then branch layer is built: • If index > one block • Collect all first entries + block address of each leaf block • Write down into the first level branch block (packed) • If branch block is full, initiates second level of branch blocks etc…. • Room is saved in branch blocks: • No forward and backward pointer in branch blocks • Entries are “trimmed” to the bare minimum

  8. Syntax • CREATE INDEX name ON table name (field1, field2 …) PCTFREE 80; • Oracle has many utility programmes to assess the performance of indexes – use INDEX_STATS • Practical problem: is it easy to create a new index for a large table? NO!

  9. Index_Stats SQL> analyze index T1X validate structure; SQL> select lf_blks, br_blks, del_lf_rows, pct_used from index_stats; LF_BLKS BR_BLKS DEL_LF_ROWS PCT_USED ---------- ---------- ----------- ---------- 66 1 0 88

  10. PCTFREE PCTFREE • This parameter is used to specify how much space should be left in the block for updates. • If the PCTFREE for a block is 30, then Oracle keeps on adding new rows to the block until the block is 70 % full. • It leaves the 30 % for future updates.

  11. PCTUSED PCTUSED • Oracle stops INSERTING new Rows in a table when the space usage reaches the PCTFREE limit. • Now consider - When should Oracle start inserting new rows in the Block ? • PCTUSED parameter is taken into consideration. Suppose you have specified PCTUSED as 40 %. and PCTFREE as 20 %. Oracle will keep on inserting new rows till the space is 80 % used. It will reserve the remaining 20% for future updates.

  12. When do we Start Adding? • When do we start adding new rows again to the block? • Oracle will check the space in the Block and the PCTUSED parameter. When the space falls below 40 %, Oracle will start adding new rows to the block.

  13. Updating Indexes • Index entries are NEVER changed • Marked as deleted and re-inserted • Space made available cannot be used until after index is re-built • Inserts that don’t fit split the block (rarely 50/50!) • If a blocks becomes empty, it is marked as free, but is never removed • Also, blocks never merge automatically

  14. Some Problems • Some situations cannot be addressed with indexes • e.g. In a FIFO processing situation (e.g. a queue), indexes will prove counterproductive • Index may grow to out of proportion even with small error rate (unsuccessful processing of data) • Every time a transaction is added or processed (deleted) the index must change

More Related