geek sync | the universe of oracle indexing

33
Topics Click to edit Master text styles Second level Third level Fourth level Fifth level The Universe of Oracle Indexing October 25 th , 2017

Upload: idera-software

Post on 22-Jan-2018

70 views

Category:

Software


3 download

TRANSCRIPT

Topics

Click to edit Master text styles• Second level

• Third level− Fourth level

• Fifth level

The Universe of Oracle Indexing

October 25th, 2017

Topics

Click to edit Master text styles• Second level

• Third level− Fourth level

• Fifth level

Abstract 2

The Oracle database offers many different types of indexing strategies and thus index types.

Today's Oracle DBA needs to be aware of all such indexing options and when each might make

sense to apply.

Join IDERA and Bert Scalzo as he explores Oracle Indexing. This session will cover every

common Oracle index type, including: b-tree, bitmap, bitmap join, reverse order, function based,

invisible, partitioned, global prefix, local prefix and index organized tables (IOT's). It's very likely

that you are only taking advantage of a small subset of these index types. This is a special Geek

Sync you will not want to miss!

Topics

Click to edit Master text styles• Second level

• Third level− Fourth level

• Fifth level

Presenter 3

Topics

Click to edit Master text styles• Second level

• Third level− Fourth level

• Fifth level

Agenda

Indexing and partitioning

What is an index?

Many indexing options

How indexes are used

3

1

2

4

4

Topics

Click to edit Master text styles• Second level

• Third level− Fourth level

• Fifth level

What is an index (conceptually)

Traditional definition:

• Wikipedia: A database index is a data structure that improves the speed of data retrieval

operations on a database table at the cost of additional writes and extra storage space to

maintain the index data structure.

• Oracle Docs: An index is an optional structure, associated with a table or table cluster, that can

sometimes speed data access. Indexes are schema objects that are logically and physically

independent of the data in the objects with which they are associated. Thus, you can drop or

create an index without physically affecting the indexed table.

5

Topics

Click to edit Master text styles• Second level

• Third level− Fourth level

• Fifth level

What is an index (commonly)

B-tree index:

• Wikipedia: In computer science, a B-tree is a self-balancing tree data structure that keeps data

sorted and allows searches, sequential access, insertions, and deletions in logarithmic time. The

B-tree is a generalization of a binary search tree in that a node can have more than two children.

• Oracle Docs: B-trees, short for balanced trees, are the most common type of database index. A

B-tree index is an ordered list of values divided into ranges. By associating a key with a row or

range of rows, B-trees provide excellent retrieval performance for a wide range of queries,

including exact match and range searches.

6

Topics

Click to edit Master text styles• Second level

• Third level− Fourth level

• Fifth level

What is an index (commonly) 7

Fetch row where

col1 = 222

col1 col2 col3 col4

222 X Y Z

TABLE

Topics

Click to edit Master text styles• Second level

• Third level− Fourth level

• Fifth level

But that is not all … 8

Topics

Click to edit Master text styles• Second level

• Third level− Fourth level

• Fifth level

B-tree index options 9

• Unique or not unique

• Ascending or descending

• Single column or multi column (composite)

• Reverse Key (can help with some RAC leaf block contention issues)

• Reverse key index is a type of B-tree index that physically reverses the bytes of each

index key while keeping the column order. For example, if the index key is 20, and if the

two bytes stored for this key in hexadecimal are C1,15 in a standard B-tree index, then a

reverse key index stores the bytes as 15,C1.

• Compressed (repeated key values removed)

• Prefix compression (also known as key compression) to compress portions of the

primary key column values in a B-tree index or an index-organized table. Prefix

compression can greatly reduce the space consumed by the index.

• Starting with Oracle 12c advanced index compression improves on traditional prefix

compression for indexes on heap-organized tables. Unlike prefix compression, which

uses fixed duplicate key elimination for every block, advanced compression uses

adaptive duplicate key elimination on a per-block basis.

Topics

Click to edit Master text styles• Second level

• Third level− Fourth level

• Fifth level

Bitmap indexes 10

• Database stores a bitmap for each index key.

• A mapping function converts each bit in the bitmap to a rowid.

• In a conventional B-tree index, one index entry points to a single row. In a bitmap index, each

index key stores pointers to multiple rows.

• Bitmap indexes are primarily designed for data warehousing or environments in which queries

reference many columns in an ad hoc fashion.

• Situations that may call for a bitmap index include:

• The indexed columns have low cardinality, that is, the number of distinct values is small

compared to the number of table rows.

• The indexed table is either read-only or not subject to significant modification by DML

statements.

Topics

Click to edit Master text styles• Second level

• Third level− Fourth level

• Fifth level

How bitmap indexes work

Fetch row where

color = green

Mapping

Function

11

Topics

Click to edit Master text styles• Second level

• Third level− Fourth level

• Fifth level

Bitmap join indexes 12

• A bitmap join index is a bitmap index

for the join of two or more tables. For

each value in a table column, the

index stores the rowid of the

corresponding row in the indexed

table. In contrast, a standard bitmap

index is created on a single table.

• A bitmap join index is an efficient

means of reducing the volume of data

that must be joined by performing

restrictions in advance.

• Redbrick database (Ralph Kimball)

pioneered this and called it “Star Join”

Topics

Click to edit Master text styles• Second level

• Third level− Fourth level

• Fifth level

How bitmap join indexes work 13

Fetch row where

Fact.col1 = X

Dim1.col2 = Y

Dim2.col3 = Z

Index stores

rowid for each

table in the join

Topics

Click to edit Master text styles• Second level

• Third level− Fourth level

• Fifth level

Function based indexes 14

• Function-based indexes are efficient for evaluating statements that contain functions in their

WHERE clauses.

• The database only uses the function-based index when the function is included in a query.

• When the database processes INSERT and UPDATE statements, however, it must still evaluate

the function to process the statement.

• A function-based index is also useful for indexing only specific rows in a table. For example, the

cust_valid column in the sh.customers table has either I or A as a value. To index only the A

rows, you could write a function that returns a null value for any rows other than the A rows.

Topics

Click to edit Master text styles• Second level

• Third level− Fourth level

• Fifth level

Index organized table (IOT) 15

• An index-organized table is a table stored in a variation of a B-tree index structure.

• Rows are stored in an index defined on the primary key for the table.

• Each index entry in the B-tree also stores the non-key column values.

• You can also specify a separate segment as a row overflow area.

• Thus, the index is the data, and the data is the index.

• If a row overflow area is specified, then the database can divide a row in an index-organized

table into the following parts:

• The index entry: This part contains column values for all the primary key columns, a

physical rowid that points to the overflow part of the row, and optionally a few of the non-

key columns. This part is stored in the index segment.

• The overflow part: This part contains column values for the remaining non-key columns.

This part is stored in the overflow storage area segment.

Topics

Click to edit Master text styles• Second level

• Third level− Fourth level

• Fifth level

How IOTs work

Branch nodes

Leaf nodes

Overflow area

16

Topics

Click to edit Master text styles• Second level

• Third level− Fourth level

• Fifth level

IOT secondary indexes 17

• A secondary index is an index on an

index-organized table. In a sense, it is an

index on an index.

• The secondary index is an independent

schema object and is stored separately

from the index-organized table.

• A secondary index on an index-organized

table can be a bitmap index.

Topics

Click to edit Master text styles• Second level

• Third level− Fourth level

• Fifth level

Invisible indexes 18

• An invisible index is maintained by DML operations, but is not used by default by the optimizer.

• Making an index invisible is an alternative to making it unusable or dropping it.

• Invisible indexes are especially useful for testing the removal of an index before dropping it or

using indexes temporarily without affecting the overall application.

Topics

Click to edit Master text styles• Second level

• Third level− Fourth level

• Fifth level

How invisible indexes work 19

Topics

Click to edit Master text styles• Second level

• Third level− Fourth level

• Fifth level

Confused yet? There’s more! 20

Topics

Click to edit Master text styles• Second level

• Third level− Fourth level

• Fifth level

Partitioning objects 21

• Partitioning enables you to decompose very large tables and indexes into smaller and more

manageable pieces called partitions.

• Each partition is an independent object with its own name and optionally its own storage

characteristics.

Topics

Click to edit Master text styles• Second level

• Third level− Fourth level

• Fifth level

Horizontal partitioning 22

Topics

Click to edit Master text styles• Second level

• Third level− Fourth level

• Fifth level

Vertical partitioning 23

Topics

Click to edit Master text styles• Second level

• Third level− Fourth level

• Fifth level

Partitioning benefits 24

• Increased availability

• The unavailability of a partition does not entail the unavailability of the object. The query

optimizer automatically removes unreferenced partitions from the query plan so queries

are not affected when the partitions are unavailable.

• Easier administration of schema objects

• A partitioned object has pieces that can be managed either collectively or individually.

DDL statements can manipulate partitions rather than entire tables or indexes. Thus, you

can break up resource-intensive tasks such as rebuilding an index or table. For

example, you can move one table partition at a time. If a problem occurs, then only the

partition move must be redone, not the table move. Also, dropping a partition avoids

executing numerous DELETE statements.

• Reduced contention for shared resources in OLTP systems

• In some OLTP systems, partitions can decrease contention for a shared resource. For

example, DML is distributed over many segments rather than one segment.

• Enhanced query performance in data warehouses

• In a data warehouse, partitioning can speed processing of ad hoc queries. For example,

a sales table containing a million rows can be partitioned by quarter.

Topics

Click to edit Master text styles• Second level

• Third level− Fourth level

• Fifth level

Basic partitioning 25

Topics

Click to edit Master text styles• Second level

• Third level− Fourth level

• Fifth level

Extended partitioning 26

Topics

Click to edit Master text styles• Second level

• Third level− Fourth level

• Fifth level

Indexing under partitioning 27

• Local

• (1) Local partitioned index: the index is partitioned on the same columns, with the same

number of partitions and the same partition bounds as its table.

• (2) Local prefixed index: the partition keys are on the leading edge of the index

definition.

• (3) Local nonprefixed index: the partition keys are not on the leading edge of the

indexed column list and need not be in the list at all.

• Global

• (4) Global partitioned index: that is partitioned independently of the underlying table on

which it is created.

Topics

Click to edit Master text styles• Second level

• Third level− Fourth level

• Fifth level

It’s very complicated 28

Topics

Click to edit Master text styles• Second level

• Third level− Fourth level

• Fifth level

Use indexes judiciously 29

Topics

Click to edit Master text styles• Second level

• Third level− Fourth level

• Fifth level

How indexes are used 30

• B-tree

• Index Unique Scan

• Index Range Scan

• Index Full can

• Index Fast Full Scan

• Index Skip Scan

• Index Join Scan

• Bitmap

• Bitmap Index Single Value

• Bitmap Index Range Scan

• Bitmap Merge

• Bitmap Index Range Scan

Too complex to explain

today, look for a future

webcast on query tuning

– indexes vs. exec plans

Oracle® Database SQL Tuning

Guide

12c Release 1 (12.1) – Chapter 8

Topics

Click to edit Master text styles• Second level

• Third level− Fourth level

• Fifth level

Index usage summary 31

Topics

Click to edit Master text styles• Second level

• Third level− Fourth level

• Fifth level

Right Choice Greatly Matters 32

Thank you!

• Bert Scalzo

[email protected]

– community.idera.com/members/bscalzo/blogs

• http://community.idera.com

33