Indexes and Indexed-Organized tables

Indexes and Indexed-Organized tables

Bitmap Indexes (BI)
ALTER TABLE statement no longer invalidates a BI.
New ALTER TABLE statement RECORDS_PER_BLOCK clause that restricts the quantity of rows that can be stored in each dB block.
MINIMIZE RECORDS_PER_BLOCK – Calculates the max number of records in any of the current dB blocks and sets that as a max that can be inserted into any new blocks. Improves query performance.
Can not specify if bitmap index already exists on the specified table.
Can not specify for an Index-organized table or nested table
Can not specify on an empty table.
NOMINIMIZE RECORDS_PER_BLOCK – disables minimize. DEFAULT

Reverse-Key Indexes
Reverses the bytes of each index column.
Helps to improve performance in Oracle Parallel Server (OPS) when index changes are centered on a small set of leaf blocks.
Insertions will be better distributed across all leafs keys in an index instead of clustered together, when index values are sequential.
Use when inserts are in ascending values and deletes are occurring to the lower values.
May help an OLTP in OPS in some cases.

Function Based Indexes
An index created on functions or expressions based upon one or more columns of the tables being accessed. These expressions may be arithmetic, built-ins or user-defined.
Use when different NLS collating sequences are present.
Pre-calculates the values of the function or expression and stores it in an index.
Can be a B-tree or bitmapped index.
Expression can not contain aggregate functions, nested table, REF or LOB columns or if an object-type contains a nested table, REF or LOB.
Must use cost-based optimizer and collect statistics for a functional index to be used.
Requires QUERY REWRITE or GLOBAL QUERY REWRITE privileges to create.
To enable function based indexes your must issue
ALTER SESSION SET QUERY_REWRITE_ENABLE = TRUE
And
ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED

Descending Indexes
Means that Indexes are generated in descending order instead of ascending order. Ascending order is the default.
Once ASC or DESC is specified, the database character set determines the coalition sequence.
Use to reduce query-sorting requirements when multiple queries require various column-ordering sequences.
Descending indexes are treated by Oracle as function-based indexes, don’t need QUERY REWRITE or GLOBAL QUERY REWRITE privileges to create.
Won’t be used until index and table are analyzed and stats are produced.
Can’t specify ASC or DESC for a domain index.
Can’t specify DESC for a bitmapped or reverse index.

Online Index Rebuild
Can perform online index builds on partitioned, nonpartitioned, and index-organized tables with b-tree indexes, using either of the following syntax:
ALTER INDEX index_name REBUILD ONLINE;
CREATE INDEX index_name ON table_name (col1...coln) ONLINE;
During ONLINE index rebuild a DML SS-locks exist that disallow any DDL tasks.
Can perform DML. However, this is not recommended, especially if a large percent of rows are involved.
Can not perform any parallel DML during an online index build.
Usually, twice the amount of current index storage is required for an online rebuild.
Indexes that can not be rebuilt on-line include cluster type, bitmap type and secondary indexes created on IOT’s.
Index rebuild has 3 phases:
prepare phase – short locks placed on the table
build phase – journal table populated
merge phase – unlocked journal rows deleted

Compute Index Statistics
Can compute index statistics is various ways.
ALTER INDEX index_name REBUILD COMPUTE STATISTICS ;
On a partitioned index this statement rebuilds index and gathers only index stats.
On a non-partitioned index gathers table, column and index stats.
CREATE INDEX index_name ON table_name (col1...coln) COMPUTE STATISTICS;
On a non-partitioned index the stats generated by the above command are stored in the data dictionary.
On a partitioned index this statement creates index and gathers only index stats.
On a non-partitioned index creates index and gathers table, column and index stats.
ANALYZE INDEX index_name COMPUTE STATISTICS ;
DBMS_DDL.ANALYZE_OBJECT(‘INDEX’ , ‘SCHEMA’, ‘INDEX_NAME’, ‘METHOD’);
The statistics generated on an index that is a concatenated key refer only to the first column.

Index-Organized Table (IOT)
An IOT is a table that stores its data in only an index. Instead of having data and index storage structures, it contains only an index structure that contains both the index value and the data values.
Fast key-based access for queries using exact match and range searches based upon the primary key in applications such as Information Retrieval, Spatial data and OLAP applications
IOT’s can be reorganized with the ALTER TABLE statement MOVE clause.
Reduced storage requirements, key columns are not duplicated in an index and a table.
CREATE TABLE command is used to create an IOT and ORGANIZATION INDEX clause defines it as an IOT.
Example: CREATE TABLE iot_table
( f1 char(20),
f2 NUMBER,
f3 NUMBER,
CONSTRAINT pk_iot_table PRIMARY KEY (f1, f2))
ORGANIZATION INDEX
TABLESPACE iot_tablespace
PCTTHRESHOLD 20
OVERFLOW TABLESPACE iot_overflow_tablespace ;
must specify a primary key on an IOT using a column constraint (single column) or table constraint clause (multicolumn).
The ORGANIZATION INDEX qualifier defines the table as an IOT.
optional row overflow specification
PCTTHRESHOLD – amount of space, expressed as a percent, to save in each index block for an IOT row.
Must be big enough to hold the primary key.
Can not specify on individual partitions of a partitioned IOT.
OVERFLOW TABLESPACE – tablespace location to store portions of rows that exceed the PCTTHRESHOLD value.
Can not use ROWID column datatype on an IOT.
No unique constraints or LONG datatype columns.
If partitioned, can not contain LOB or varray type columns.
If non-partitioned can contain nested table column types.
reorganize with the ALTER TABLE command MOVE clause
Query the DBA_TABLES view and evaluate the IOT_NAME and IOT_TYPE columns to view information about IOT’s. These fields are new to this table to support IOT’s.

Logical Rowid’s
A logical ROWID is a logical row identifier within an IOT that is based upon the IOT’s primary key.
Is used to create secondary indexes on IOT’s.
Can be accessed with the ROWID pseudocolumn. Returns the primary key value and a “physical guess”. The physical guess is created or updated when the secondary index is created or rebuilt. Contains the block location of the row in the IOT.
Logical ROWID value will remain static as long as the primary key value it is based upon does not change.
Fastest way to get to a row in an IOT, even if it takes more than one block access.
Logical ROWID’s can not be used to see how a table has been organized.
Logical ROWID’s can be stored in a UROWID datatype column.