Oracle Indexes

Oracle Indexes Version 11.2.0.3

General Information

Notes: Index types specifically related to clusters, index organized tables, nested tables, and partitioned tables are handled in the specific library sections dealing with those object types.

Do not rebuild indexes without using the link, page bottom, to the undocumented SYS_OP_LBID function, reading the comments, and working with the "Working Production Code" demo.

Index Types

B*Tree	Descending	Reverse
Bitmap	Function Based	Star
Bitmap Join	Invisible	XML Index
Compressed	No-Segment

Data Dictionary Objects

ALL_INDEXES	DBA_IND_EXPRESSIONS	IND_ONLINE$
ALL_IND_COLUMNS	IND$	IND_STATS$
ALL_IND_EXPRESSIONS	INDPART$	USER_INDEXES
COL$	INDEX_HISTOGRAMS	USER_IND_COLUMNS
DBA_INDEXES	INDEX_STATS	USER_IND_EXPRESSIONS
DBA_IND_COLUMNS	INDSUBPART$	V$OBJECT_USAGE

Security Model: System Privileges

create index
create any index

Related initialization parameters

optimizer_index_caching
optimizer_index_cost_adj
optimizer_use_invisible_indexes
skip_unusable_indexes
_disable_function_based_index

Index Usage Notes

This unedited explanation, one of the most lucid I have seen, was posted by Richard Foote at c.d.o.server on 20 January, 2005.

"hastenthunder" wrote in message news:56uHd.2452$Ny6.4229@mencken.net.nih.gov...

>> Hello,
>>
>> I've read many documentations online stating to only create an index if
>> queries against this table frequently retrieve less than 15% of the rows.
>> However, if the query returns, say, 40% of the rows, wouldn't indexing the
>> column still help by cutting the work by roughly half?
>>
>>
>> hastenthunder
>>

A much *simplified* example on how I teach this stuff...

Let's say we have a table that has 10,000,000 rows which are stored in 1,000,000 data blocks meaning we have approximately 10 rows per block on average.

Let's say we have an index on this table that has 100,000 leaf blocks meaning we have on average approximately 100 leaf entries per leaf block the index has 3 levels.

Let's also say we have an "effective" multi-block read capability of 10 blocks per I/O (meaning Oracle will read 10 "consecutive" blocks at a time on average during a full table scan multi-block read).

Finally, let's say we're interested in accessing *just* 10% of the data (or 1,000,000 of the total 10,000,000 rows). Will Oracle use the index or won't it ? Hopefully, I've picked an easy set of numbers to help illustrate the answer ...

Firstly, to calculate the "cost" of using the index access path.

We need to read the root block + a branch block in order to get to the first leaf block of interest. That's 2 logical I/Os (LIOs). We then need to read approximately 10% of the leaf blocks in order to get our 1,000,000 leaf entries required to directly access our 1,000,000 rows of interest, that's 10% of the 100,000 leaf blocks = 10,000 leaf blocks. Because we're reading an index via a range scan and because the leaf blocks are not (necessarily) physically co-related, Oracle must read each leaf block via a single I/O. So that's 10,000 LIOs. So, just to read the index alone, we require 2 + 10,000 = 10,002 LIOs.

Note by default, Oracle assumes the above "cost" to be physical I/Os (PIOs). Now assuming this index is heavily accessed, a good number of these index blocks may already be cached in memory. The optimizer_index_caching parameter can be used to adjust the above cost by suggesting that x% are actually already cached and so are "cheaper" to access. To keep things simple, we'll assume the default value of 0% or that no index blocks are actually likely to be cached (generally not a wise assumption but let's keep the arithmetic simple).

To access the corresponding table blocks, again Oracle can only perform these reads via a single block read as each index entry points to a table block that contains it's specific table row. Now we're after 1,000,000 rows which means we require 1,000,000 LIOs in order to access the required rows. Question is, how many *different* table blocks do we need to access? Well, this is entirely dependent on the Clustering Factor (CF) of the index, or how closely aligned are the corresponding rows in the table in relation to the order of the index (which must be in the order of the index values). In the "best" possible case, all the required rows are all ordered and grouped together in the same "collection" of table blocks meaning we only have to
access 10% of the 1,000,000 table blocks or 100,000 table blocks in a roughly *consecutively* manner.

However, as is more common, if the required rows are randomly and evenly distributed among the table blocks, then on average we need to read 1 row (10%) from *each and every table block*. Note in your case of wanting to access 40% of the data, we might depending on a poor CF need to visit on average *each and every* data block *4 times*. This is the key point (no pun intended).

The greater the number of differing blocks we access, then the less likely we will find the block in memory from it being previously read and the more likely that the block will need to be read from disk (PIO). Oracle considers this and uses the CF in it's costing calculations. Assuming a randomly distributed set of required rows, note we will need to visit *all* the table blocks on average because on average we are interested in 1 in 10 of the rows that each block contains (yes, some blocks may not actually be visited and some may be visited a number of times but with such volume of blocks, it conceivably might be a significant duration between reads to the same block meaning it could easily have been aged and be physically re-read anyways).

The point though is that it's 1,000,000 LIOs regardless, of which a very significant number *could* be *actual distinct* (or differing) blocks. So that's 10,002 for the index + 1,000,000 for the table = 1,010,002 LIOs to read *just* 10% of the data via an index.

Now to calculate the "cost" of a FTS. A FTS has a number of advantages over an index access path. Firstly, because we read each block "consecutively" (kinda) Oracle can investigate the appropriate selectiveness of each row within the block ensuring that each table block is read just *once* (special blocks such as extent maps withstanding). Secondly, again because each block is read consecutively, Oracle can perform a multi-block read and read multiple blocks within the one LIO. This is based on factors such as db_file_multiblock_read_count, system statistics, OS I/O characteristics, the caching characteristics of the table and the "fudge-factor" that the
Oracle CBO applies in it's calculations.

For simplicity (and to keep the numbers really simple), assuming an effective multi-block read of 10, we can read the entire table in approximately 1,000,000 table blocks / 10 = 100,000 LIOs. Note that although these are larger and potentially more "costly" I/Os than the single block I/Os used by the index, Oracle assumes by default that the actual cost of each type of I/O to be the same. The optimizer_index_cost_adj parameter can be used to more accurately estimate (if necessary) the relative cost of
a single block I/O to that of a FTS multi-block I/O. Again for simplicity, we'll assume the default of 100 meaning that the cost of a single block I/O is 100% (or the same) as a FTS I/O.

So, we now have our two comparative costings. The index access has a rough cost of 1,010,002 and the FTS has a rough cost of just 100,000. The FTS wins hands down.... Note for 40% of the data, the relative costs would have been roughly 4,040,002 vs. 100,000. Even more hands down ...

The break-even point can now be calculated based on the above criteria, some of which include:

the selectivity of the query
number of leaf blocks
average number of leaf entries per leaf block
height of index
caching characteristics of index
clustering factor of index
number of table blocks (below HWM)
average number of rows per block
effective (or calculated) multi-block read
caching characteristics of the table (which can influence the effective multi-block read)
relative cost of a single block I/O vs. a multi-block I/O
amount of row migration / row chaining (although the CBO is not so good with this)
parallelism (potentially a major factor)

So your assumption that reading 40% of rows would cut the work by roughly half is not correct. In the example above, it would actually cost about 40 times as much. In my long-winded manner, I hope this makes some kind of sense and goes some way to explaining why.

One final piece of advice. Ignore any writings or suggestions that there is a magical break even point is x% (where x could be 2% or 10% or 50% or whatever). Hopefully the above will hint that there is *no* such percentage as it all depends on too many factors. I can easily give you an example where an index is most efficient when reading 0% of data and I can easily give you an example where an index is most efficient when reading *100%* of data (and *any* value in between). When one understands how the CBO functions, one understands why such so-called rules of thumb are a nonsense.

Cheers

Richard Foote

Mythology

Indexes and tables should be in separate tablespaces for performance reasons. This is pure unadulterated rubbish without foundation in fact.
Indexes need to be rebuilt frequently. There is no questions that in some situations index space usage and performance can be improved by a rebuild. It is not true that this is as it must be. Read the advise on this subject written by Richard Foote and Jonathan Lewis and follow their advise. Ignore everything written on the subject by non-Oak Table Members. If you think you may need to rebuild indexes go to the SYS_OP_LBID page in the library and get the code for my rebuild tool.

Indexes Demo Preparation

Create Tablespace For Index Demos

conn uwclass/uwclass

SELECT tablespace_name
FROM user_tablespaces;

conn / as sysdba

CREATE TABLESPACE data_lrg
DATAFILE 'c:\temp\inddemo1.dbf'
SIZE 250M AUTOEXTEND ON
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M
SEGMENT SPACE MANAGEMENT MANUAL;

SELECT tablespace_name
FROM user_tablespaces;

ALTER USER uwclass QUOTA UNLIMITED ON data_lrg;

conn uwclass/uwclass

SELECT tablespace_name
FROM user_tablespaces;

Create Table For Index Demos

conn uwclass/uwclass

CREATE TABLE index_demo (
person_id NUMBER(10),
gender    VARCHAR2(1),
state     VARCHAR2(2),
textcol   VARCHAR2(2000))
TABLESPACE data_lrg;

Demo Table and Data

DECLARE
g index_demo.gender%TYPE := 'F';
BEGIN
FOR i IN 1 .. 50000 LOOP
    INSERT INTO index_demo
    (person_id, gender, state, textcol)
    VALUES
    (i, g, 'WA', RPAD('x', 1799, 'x'));

    IF g = 'F' THEN
      g := 'M';
    ELSE
      g := 'F';
    END IF;
  END LOOP;
  COMMIT;

  UPDATE index_demo
  SET state = 'OR'
  WHERE person_id LIKE '%1';

  UPDATE index_demo
  SET state = 'CA'
  WHERE person_id LIKE '%2';

  UPDATE index_demo
  SET state = 'ID'
  WHERE person_id LIKE '%3';

  UPDATE index_demo
  SET state = 'NY'
  WHERE person_id LIKE '%4';

UPDATE index_demo
SET state = 'MA'
WHERE person_id LIKE '%5';

UPDATE index_demo
SET state = 'MN'
WHERE person_id LIKE '%6';

UPDATE index_demo
SET state = 'VA'
WHERE person_id LIKE '%7';

UPDATE index_demo
SET state = 'NC'
WHERE person_id LIKE '%8';

UPDATE index_demo
SET state = 'MI'
WHERE person_id like '%9';

  COMMIT;
END;
/

B*Tree Indexes

Single Column Non-unique

CREATE INDEX <index_name>
ON <table_name> (<column_name>)
PCTFREE <integer>
TABLESPACE <tablespace_name>;

CREATE INDEX ix_index_demo_person_id
ON index_demo(person_id)
PCTFREE 0
TABLESPACE uwdata;

SELECT index_name, index_type, uniqueness, num_rows
FROM user_indexes;

Sort / Nosort

By default, Oracle sorts indexes in ascending order when it creates the index. You can specify NOSORT to indicate to Oracle that the rows are already stored in the database in ascending order, so that Oracle does not have to sort the rows when creating the index. If the rows of the indexed column or columns are not stored in ascending order, Oracle returns an error. For greatest savings of sort time and space, use this clause immediately after the initial load of rows into a table. If you specify neither of these keywords, SORT is the default.

CREATE INDEX <index_name>
ON <table_name> (<column_name>)
PCTFREE <integer>
TABLESPACE <tablespace_name>
NOSORT;

conn uwclass/uwclass

CREATE TABLE sort_demo
AS SELECT table_name
FROM user_tables
ORDER BY num_rows;

SELECT *
FROM sort_demo;

-- this will fail
CREATE INDEX ix_failure
ON sort_demo (table_name)
PCTFREE 0
TABLESPACE uwdata
NOSORT;

DROP TABLE sort_demo PURGE;

CREATE TABLE sort_demo
AS SELECT table_name
FROM user_tables
ORDER BY table_name;

SELECT *
FROM sort_demo;

-- this will succeed
CREATE INDEX ix_success
ON sort_demo (table_name)
PCTFREE 0
TABLESPACE uwdata
NOSORT;

NOSORT with ASSM tablespaces

conn / as sysdba

CREATE TABLESPACE ssmm DATAFILE 'c:/temp/a01.dbf' size 10M
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT MANUAL;

CREATE TABLESPACE ssma DATAFILE 'c:/temp/b01.dbf' size 10M
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

ALTER USER uwclass QUOTA UNLIMITED ON ssmm;
ALTER USER uwclass QUOTA UNLIMITED ON ssma;

conn uwclass/uwclass

CREATE TABLE t_manual
TABLESPACE ssmm AS
SELECT *
FROM dba_objects_ae
WHERE 1=2;

CREATE TABLE t_auto
TABLESPACE ssma AS
SELECT *
FROM dba_objects_ae
WHERE 1=2;

INSERT INTO t_manual SELECT * FROM dba_objects_ae ORDER BY 2;

INSERT INTO t_auto SELECT * FROM dba_objects_ae ORDER BY 2;

CREATE INDEX ix_manual on t_manual (object_name) NOSORT;

CREATE INDEX ix_auto on t_auto (object_name) NOSORT;

TRUNCATE TABLE t_auto;

INSERT /*+ APPEND */ INTO t_auto SELECT * FROM dba_objects_ae ORDER BY 2;

CREATE INDEX ix_auto on t_auto (object_name) NOSORT;

DROP INDEX ix_manual;
DROP INDEX ix_auto;

TRUNCATE TABLE t_manual;
TRUNCATE TABLE t_auto;

CREATE INDEX ix_manual on t_manual (object_name);
CREATE INDEX ix_auto on t_auto (object_name);

INSERT INTO t_manual SELECT * FROM dba_objects_ae ORDER BY 2;
INSERT INTO t_auto SELECT * FROM dba_objects_ae ORDER BY 2;

exec dbms_stats.gather_schema_stats(USER, CASCADE=>TRUE);

SELECT i.index_name, i.clustering_factor, s.blocks
FROM user_indexes i, user_segments s
WHERE i.index_name = s.segment_name;

Single Column Compute Statistics

CREATE INDEX <index_name>
ON <table_name> (<column_name>)
PCTFREE <integer>
TABLESPACE <tablespace_name>
COMPUTE STATISTICS;

CREATE INDEX ix_index_demo_person_id
ON index_demo(person_id)
PCTFREE 0
TABLESPACE uwdata
COMPUTE STATISTICS;

SELECT index_name, index_type, uniqueness, num_rows
FROM user_indexes;

Multiple Column Non-unique

CREATE INDEX <index_name>
ON <table_name> (<column_name>, <column_name>, ....)
PCTFREE <integer>
TABLESPACE <tablespace_name>;

CREATE INDEX ix_index_demo_gender_state
ON index_demo(person_id, state)
PCTFREE 0
TABLESPACE uwdata;

SELECT index_name, index_type, uniqueness
FROM user_indexes;

SELECT table_name, index_name, column_name, column_position
FROM user_ind_columns
ORDER BY table_name, index_name;

Parallel Index

CREATE INDEX <index_name>
ON <table_name> (<column_name_list>)
PCTFREE 0
PARALLEL (DEGREE <integer>)
TABLESPACE <tablespace_name>;

CREATE INDEX pix_index_demo_gender_state
ON index_demo(person_id, state)
PCTFREE 0
PARALLEL (DEGREE 4)
TABLESPACE uwdata;

SELECT index_name, index_type, degree
FROM user_indexes;

Create Unique Index

CREATE UNIQUE INDEX <index_name>
ON <table_name> (<column_name>)
PCTFREE <integer>
TABLESPACE <tablespace_name>;

Unique constraints are always preferable to unique indexes.

CREATE UNIQUE INDEX uix_index_demo_person_id
ON index_demo(person_id)
PCTFREE 0
TABLESPACE uwdata;

SELECT index_name, index_type, uniqueness
FROM user_indexes;

Now that you know how to build these: Don't!

Create Unusable Index

CREATE INDEX <index_name>
ON <table_name> (<column_name>)
PCTFREE <integer>
TABLESPACE <tablespace_name>
UNUSABLE;

Unique constraints are always preferable to unique indexes.

CREATE INDEX uix_index_demo_person_id
ON index_demo(person_id)
PCTFREE 0
TABLESPACE uwdata
UNUSABLE;

SELECT index_name, status
FROM user_indexes;

ALTER INDEX uix_index_demo_person_id REBUILD;

SELECT index_name, status
FROM user_indexes;

SELECT index_name, status
FROM user_indexes;

New in 11gR2 Unusable Indexes become Segmentless

SELECT table_name, index_name
FROM user_indexes;

SELECT segment_type, tablespace_name, bytes, blocks
FROM user_segments
WHERE segment_name = 'IX_PROGRAM_ID';

ALTER INDEX ix_program_id UNUSABLE;

SELECT segment_type, tablespace_name, bytes, blocks
FROM user_segments
WHERE segment_name = 'IX_PROGRAM_ID';

ALTER INDEX ix_program_id REBUILD;

SELECT segment_type, tablespace_name, bytes, blocks
FROM user_segments
WHERE segment_name = 'IX_PROGRAM_ID';

TABLESPACE LOGGING

TABLESPACE <tablespace_name> <LOGGING | NOLOGGING>

conn uwclass/uwclass

CREATE INDEX ix_latitude
ON servers(latitude)
TABLESPACE uwdata LOGGING;

Bitmap Indexes

Note: These are primarily intended for read-only data warehouse/decision support systems.

Create Bitmap Index

CREATE BITMAP INDEX <index_name>
ON <table_name> (<column_name>)
PCTFREE <integer>
TABLESPACE <tablespace_name>;

CREATE INDEX ix_gender
ON index_demo (gender)
PCTFREE 5
TABLESPACE uwdata;

exec dbms_stats.gather_index_stats('UWCLASS', 'IX_GENDER');

SELECT index_name, index_type, blevel, leaf_blocks
FROM user_indexes;

DROP INDEX ix_gender;

CREATE BITMAP INDEX bix_gender
ON index_demo (gender)
PCTFREE 5
TABLESPACE uwdata;

exec dbms_stats.gather_index_stats('UWCLASS', 'BIX_GENDER');

SELECT index_name, index_type, blevel, leaf_blocks
FROM user_indexes;

Bitmap Join Indexes (aka Star Index)

Create Bitmap Join Index

Thanks to Marla Weston of Camosun College, Victoria BC for corrections.

CREATE BITMAP JOIN INDEX <index_name>
ON <table_name> (<table_name.column_name>)
FROM <table_name, table_name>
WHERE <join_condition>

CREATE TABLE facts (
prod_id VARCHAR2(10),
amount NUMBER(10,2))
PARTITION BY RANGE (prod_id) (
PARTITION p1 VALUES LESS THAN ('M'),
PARTITION p2 VALUES LESS THAN (MAXVALUE));

CREATE TABLE products (
prod_id VARCHAR2(15),
prod_name VARCHAR2(30));

ALTER TABLE products
ADD CONSTRAINT pk_product
PRIMARY KEY (prod_id)
USING INDEX
PCTFREE 0;

CREATE BITMAP INDEX bjix_fact_prod
ON facts (products.prod_name)
FROM facts, products
WHERE facts.prod_id = products.prod_id
LOCAL;

SELECT index_name, index_type
FROM user_indexes
ORDER BY 2;

set linesize 121
col inner_table_name format a20
col inner_table_column format a20
col outer_table_name format a20
col outer_table_column format a20

SELECT index_name, inner_table_name, inner_table_column, outer_table_name, outer_table_column
FROM user_join_ind_columns;

CREATE TABLE facts_new AS
SELECT * FROM facts
WHERE 0 = 1;

CREATE BITMAP INDEX bjix_facts_new_prod
ON facts_new(products.prod_name)
FROM facts_new, products
WHERE facts_new.prod_id = products.prod_id;

col index_name format a20
col inner_table_name format a20
col inner_table_column format a20
col outer_table_name format a20
col outer_table_column format a20

SELECT index_name, inner_table_name, inner_table_column,
outer_table_name, outer_table_column
FROM user_join_ind_columns;

Bitmap Join Index Demo

conn scott/tiger

CREATE TABLE emp2 AS
SELECT * FROM emp;

CREATE TABLE dept2 AS
SELECT * FROM dept;

ALTER TABLE dept2
ADD CONSTRAINT pk_dept2
PRIMARY KEY(deptno);

CREATE BITMAP INDEX bix_emp
ON emp2(d.dname)
FROM emp2 e, dept2 d
WHERE e.deptno = d.deptno;

SELECT index_name, index_type
FROM user_indexes;

-- fake up some data for the CBO
exec dbms_stats.set_table_stats(USER, 'EMP', numrows => 1000000, numblks => 300000);

exec dbms_stats.set_table_stats(USER, 'DEPT', numrows => 100000, numblks => 30000);

exec dbms_stats.set_table_stats(USER, 'EMP2', numrows=>1000000, numblks => 300000);

exec dbms_stats.set_table_stats(USER, 'DEPT2', numrows=>100000, numblks => 30000);

set autotrace on

SELECT COUNT(*)
FROM emp e, dept d
WHERE e.deptno = d.deptno
and d.dname = 'SALES';

SELECT COUNT(*)
FROM emp2 e, dept2 d
WHERE e.deptno = d.deptno
and d.dname = 'SALES';

set autotrace off

Bitmap Join index with more than two tables

conn sh/sh

CREATE TABLE sales2 AS SELECT * FROM sales;
CREATE TABLE channels2 AS select * FROMchannels;
CREATE TABLE products2 AS select * FROM products;

ALTER TABLE channels2
ADD CONSTRAINT pk_channel22
PRIMARY KEY (channel_id);

ALTER TABLE products2
ADD CONSTRAINT pk_products2
PRIMARY KEY (prod_id);

desc sales2
desc channels2
desc products2

SELECT /*+ index(s bdi_sales2) */ c.channel_desc, p.prod_name, SUM(s.quantity_sold)
FROM sales2 s, channels2 c, products2 p
WHERE s.channel_id = c.channel_id
AND s.prod_id = p.prod_id
AND s.channel_id = 2
AND s.prod_id = 120
GROUP BY c.channel_desc, p.prod_name;

EXPLAIN PLAN FOR
SELECT /*+ index(s bdi_sales2) */ c.channel_desc, p.prod_name, SUM(s.quantity_sold)
FROM sales2 s, channels2 c, products2 p
WHERE s.channel_id = c.channel_id
AND s.prod_id = p.prod_id
AND s.channel_id = 2
AND s.prod_id = 120
GROUP BY c.channel_desc, p.prod_name;

SELECT * FROM TABLE(dbms_xplan.display);

CREATE BITMAP INDEX bdi_sales2
ON sales2 (s.prod_id, s.channel_id)
FROM sales2 s, channels2 c, products2 p
WHERE s.channel_id = c.channel_id
AND s.prod_id = p.prod_id;

EXPLAIN PLAN FOR
SELECT /*+ index(s bdi_sales2) */ c.channel_desc, p.prod_name, SUM(s.quantity_sold)
FROM sales2 s, channels2 c, products2 p
WHERE s.channel_id = c.channel_id
AND s.prod_id = p.prod_id
AND s.channel_id = 2
AND s.prod_id = 120
GROUP BY c.channel_desc, p.prod_name;

SELECT * FROM TABLE(dbms_xplan.display);

Descending

Note: See sys_op_descend under Undocumented Oracle

Related Init Parameters

Oracle treats descending indexes as if they were function-based indexes. You do not need the QUERY REWRITE or GLOBAL QUERY REWRITE privileges to create them, as you do with other function-based indexes. However, as with other function-based indexes, Oracle does not use descending indexes until you first analyze the index and the table on which the index is defined. Oracle ignores DESC if index is bitmapped.

Create Descending Index

CREATE INDEX <index_name>
ON <table_name>
(<column_name>, [<column_name>] DESC)
PCTFREE <integer>
TABLESPACE <tablespace_name>;

CREATE INDEX ix_demo_gender_state_desc
ON index_demo(person_id, state DESC)
PCTFREE 0
TABLESPACE uwdata;

SELECT index_name, index_type
FROM user_indexes;

Function Based Indexes

Related Init Parameters

To create a function-based index (FBI) in your own schema on your own table you must have the QUERY REWRITE system privilege. To create the index in another schema or on another schema's table, you must have the GLOBAL QUERY REWRITE privilege.

In either case, the table owner must also have the EXECUTE object privilege on the function(s) used in the creation of the FBI. In addition, in order for Oracle to use FBI's in queries, the QUERY_REWRITE_ENABLED parameter must be set to TRUE, and the QUERY_REWRITE_INTEGRITY parameter must be set to TRUSTED.

-- FBI Demo Table and DataCREATE TABLE emp (
empno    NUMBER(4,0),
ename    VARCHAR2(10),
job      VARCHAR2(9),
mgr      NUMBER(4,0),
hiredate DATE,
sal      NUMBER(7,2),
comm     NUMBER(7,2),
deptno   NUMBER(2,0));

INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,TO_DATE('17-DEC-05'),8000,NULL,20);
INSERT INTO emp VALUES (7499, 'ALLEN', 'SALESMAN',7698, TO_DATE('20-FEB-98'),16000,300,30);
INSERT INTO emp VALUES (7521, 'WARD', 'SALESMAN',7698,TO_DATE('22-FEB-96'),12500,500,30);
INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,TO_DATE('02-APR-95'),29750,NULL,20);
INSERT INTO emp VALUES (7654, 'MARTIN', 'SALESMAN',7698, TO_DATE('28-SEP-92'),12500,1400,30);
INSERT INTO emp VALUES (7698,'MORGAN','MANAGER',7839,TO_DATE('01-MAY-03'),28500,NULL,30);
INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,TO_DATE('09-JUN-91'),24500,NULL,10);
INSERT INTO emp VALUES (7788, 'SCOTT', 'ANALYST',7566,TO_DATE('19-APR-97'),30000,NULL,20);
INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL, TO_DATE('17-NOV-91'),50000,NULL,10);
INSERT INTO emp VALUES (7844,'TURNER', 'SALESMAN',7698, TO_DATE('08-SEP-91'),15000,0,30);
INSERT INTO emp VALUES (7876,'ADAMS', 'CLERK',7788,TO_DATE('23-MAY-97'),1100,0,20);
INSERT INTO emp VALUES (7900,'JAMES', 'CLERK',7698,TO_DATE('03-DEC-91'),9500,NULL,30);
INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,TO_DATE('03-DEC-91'),30000,NULL,20);
INSERT INTO emp VALUES (7934,'MILLER', 'CLERK', 7782,TO_DATE('23-JAN-92'),13000,NULL,10);
COMMIT;

Index based on calculation using two columns

CREATE INDEX <index_name>
ON <table_name> <function_or_calculation)
PCTFREE <integer>
TABLESPACE <tablespace_name>;

conn scott/tiger

SELECT COUNT(*)
FROM emp;

exec dbms_stats.gather_schema_stats('UWCLASS', CASCADE=>TRUE);

set autotrace traceonly

SELECT ename
FROM emp
WHERE (sal * comm) < 300000;

set autotrace off

CREATE INDEX fbi_emp_sal_x_comm
ON emp (sal * comm);

SELECT index_name, index_type, funcidx_status, status
FROM user_indexes;

col column_expression format a20

SELECT table_name, index_name, column_expression
FROM user_ind_expressions;

exec dbms_stats.gather_schema_stats('UWCLASS', CASCADE=>TRUE);

set autotrace traceonly

SELECT ename
FROM emp
WHERE (sal * comm) < 300000;

set autotrace off

Avoiding full table scans when records are being filtered by a function

conn sh/sh

set autotrace traceonly explain

SELECT cust_first_name
FROM customers
WHERE substr(cust_last_name,1,1) = 'L';

CREATE INDEX ix_customers_cust_ln
ON customers(cust_last_name);

SELECT cust_first_name
FROM customers
WHERE substr(cust_last_name,1,1) = 'L';

CREATE INDEX fbi_customers_cust_ln_init
ON customers (SUBSTR(cust_last_name,1,1));

SELECT cust_first_name
FROM customers
WHERE substr(cust_last_name,1,1) = 'L';

DROP INDEX fbi_customers_cust_ln_init;
DROP INDEX ix_customers_cust_ln;

set autotrace off

Avoiding indexing of values that won't be searched

This demo based on a presentation by Tom Kyte for the Victoria Oracle Users Group

conn uwclass/uwclass

CREATE TABLE fbidemo AS
SELECT object_name, object_type, temporary
FROM all_objects;

CREATE INDEX ix_fbidemo
ON fbidemo (temporary)
PCTFREE 0;

CREATE INDEX fbi_fbidemo
ON fbidemo (DECODE(temporary, 'Y', 'Y', NULL));

set linesize 121

SELECT index_type, leaf_blocks, avg_leaf_blocks_per_key, avg_data_blocks_per_key, blevel
FROM user_indexes
WHERE table_name = 'FBIDEMO';

SELECT index_name, column_expression
FROM user_ind_expressions
WHERE column_expression IS NOT NULL;

-- =====================
DROP INDEX ix_fbidemo;
DROP INDEX fbi_fbidemo;

set autotrace traceonly

exec dbms_stats.gather_table_stats('UWCLASS', 'FBIDEMO', CASCADE=>TRUE);

SELECT object_name, object_type
FROM fbidemo
WHERE temporary = 'Y';

CREATE INDEX ix_fbidemo
ON fbidemo (temporary)
PCTFREE 0;

exec dbms_stats.gather_table_stats('UWCLASS', 'FBIDEMO', CASCADE=>TRUE);

SELECT object_name, object_type
FROM fbidemo
WHERE temporary = 'Y';

DROP INDEX ix_fbidemo;

CREATE INDEX fbi_fbidemo
ON fbidemo (DECODE(temporary, 'Y', 'Y', NULL));

exec dbms_stats.gather_table_stats('UWCLASS', 'FBIDEMO', CASCADE=>TRUE);

SELECT object_name, object_type
FROM fbidemo
WHERE (DECODE(temporary, 'Y', 'Y', NULL)) = 'Y';

set autotrace off

Enforcing data integrity

This demo based on a presentation by& Tom Kyte

CREATE TABLE t (
col1 VARCHAR2(10) NOT NULL,
col2 NUMBER(5) NOT NULL);

CREATE UNIQUE INDEX ix_t
ON t (CASE WHEN col1='N' THEN col2 ELSE NULL END);

INSERT INTO t (col1, col2) VALUES ('Y', 1);
INSERT INTO t (col1, col2) VALUES ('Y', 2);
INSERT INTO t (col1, col2) VALUES ('Y', 1);
INSERT INTO t (col1, col2) VALUES ('N', 1);
INSERT INTO t (col1, col2) VALUES ('N', 2);
INSERT INTO t (col1, col2) VALUES ('N', 1);

Including NULL in an index

SYS_OP_MAP_NONNULL is covered on the undocumented Oracle page of the library

conn uwclass/uwclass

CREATE TABLE t (
col1 VARCHAR2(10) NOT NULL,
col2 NUMBER(5));

DECLARE
x INTEGER;
BEGIN
FOR i IN 1..99999 LOOP
    IF mod(i,11) = 0 THEN
      x := NULL;
    ELSE
      x := i;
    END IF;

    INSERT INTO t
    (col1, col2)
    VALUES
    ('XXXXXXXXXX', x);
END LOOP;
COMMIT;
END;
/

SELECT COUNT(*) FROM t;

SELECT COUNT(*) FROM t WHERE col2 IS NULL;

CREATE INDEX ix_t
ON t (col2);

EXPLAIN PLAN FOR
SELECT *
FROM t
WHERE col2 IS NULL;

SELECT * FROM TABLE(dbms_xplan.display);

CREATE INDEX ix_t_mapnn
ON t (sys_op_map_nonnull(col2));

exec dbms_stats.gather_index_stats(USER, 'IX_T_MAPNN');

EXPLAIN PLAN FOR
SELECT *
FROM t
WHERE sys_op_map_nonnull(col2) = sys_op_map_nonnull(NULL);

SELECT * FROM TABLE(dbms_xplan.display);

Another solution for indexing NULLs

conn uwclass/uwclass

CREATE TABLE t (
rid      NUMBER,
completed DATE);

BEGIN
FOR i IN 1..1000 LOOP
    IF mod(i, 2) = 0 THEN
    INSERT INTO t
      (rid, completed)
      VALUES
      (i, SYSDATE + TO_NUMBER(SUBSTR(dbms_crypto.randomInteger,1,4)));
    ELSE
    INSERT INTO t t (rid) VALUES (i);
    END IF;
END LOOP;
COMMIT;
END;
/

EXPLAIN PLAN FOR
SELECT COUNT(*)
FROM t
WHERE completed IS NULL;

---------------------------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     9 |     3   (0)| 00:01:36 |
|   1 | SORT AGGREGATE    |      |     1 |     9 |            |          |
|* 2 |   TABLE ACCESS FULL| T    |   500 | 4500 |     3   (0)| 00:01:36 |
---------------------------------------------------------------------------

CREATE INDEX ix_t
ON t(completed);

EXPLAIN PLAN FOR
SELECT COUNT(*)
FROM t
WHERE completed IS NULL;

---------------------------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes | Cost (%CPU)| Time    |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     9 |     3   (0)| 00:01:36 |
|   1 | SORT AGGREGATE    |      |     1 |     9 |            |          |
|* 2 |   TABLE ACCESS FULL| T    |   500 | 4500 |     3   (0)| 00:01:36 |
---------------------------------------------------------------------------

DROP INDEX ix_t;
CREATE INDEX ix_t
ON t(NVL2(completed, NULL, 'X'));

EXPLAIN PLAN FOR
SELECT COUNT(*)
FROM t
WHERE NVL2(completed, NULL, 'X') = 'X';

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2 |     1   (0)| 00:00:32 |
|   1 | SORT AGGREGATE   |      |     1 |     2 |            |          |
|* 2 | INDEX RANGE SCAN| IX_T |    10 |    20 |     1   (0)| 00:00:32 |
--------------------------------------------------------------------------

Including NULL in an index

Another method suggested by Richard Foote and David A. W. Johnson

conn uwclass/uwclass

-- create an index in which the leading column is the one with nulls
CREATE INDEX ix_t_itc
ON t (col2, col1);

exec dbms_stats.gather_index_stats(USER, 'IX_T_ITC');

EXPLAIN PLAN FOR
SELECT *
FROM t
WHERE col2 IS NULL;

SELECT * FROM TABLE(dbms_xplan.display);

-- compare the cost of the two methods first using SYS_OP_MAP_NONNULL
--------------------------------------------------------------------
| Id | Operation                  | Name       | Rows | Cost (%CPU)|
--------------------------------------------------------------------
| 0 | SELECT STATEMENT           |            | 1095 |    4    (0)|
| 1 | TABLE ACCESS BY INDEX ROWID|          T | 1095 |    4    (0)|
|* 2 | INDEX RANGE SCAN           | IX_T_MAPNN | 438 |    1    (0)|
---------------------------------------------------------------------
-- then using the two-column index solution
------------------------------------------------------------------
| Id | Operation                  |   Name   | Rows | Cost (%CPU)|
------------------------------------------------------------------
| 0 | SELECT STATEMENT           |          | 9939 |    21    (0)|
|* 1 | INDEX RANGE SCAN           | IX_T_ITC | 9939 |    21    (0)|
------------------------------------------------------------------
/* SYS_OP_MAP_NONNULL may be undocumented ... but it sure works well in situations where you can write the WHERE clause. It should be noted that in both cases the number of rows estimated by the optimizer is incorrect. */

Range Uniqueness Enforcement

conn uwclass/uwclass

CREATE TABLE t (rid NUMBER(5), testcol DATE);

ALTER TABLE t
ADD CONSTRAINT pk_t
PRIMARY KEY (rid, testcol);

CREATE OR REPLACE FUNCTION ftest(rid NUMBER, bdate DATE, edate DATE)
RETURN DATE DETERMINISTIC IS
x DATE;
BEGIN
-- decomposes the range and inserts one record per increment
FOR d IN 0 .. (TRUNC(edate)-TRUNC(bdate)) LOOP
    x := TRUNC(bdate+d);
    INSERT INTO t
    (rid, testcol)
    VALUES
    (rid, x);
END LOOP;
-- returns NULL so nothing is ever indexed
RETURN NULL;
END ftest;
/

CREATE TABLE test (
rid   NUMBER(5),
bdate DATE,
edate DATE);

CREATE INDEX fbi_t
ON test (ftest(rid, bdate, edate));

INSERT INTO test VALUES (1, SYSDATE, SYSDATE);
INSERT INTO test VALUES (2, SYSDATE, SYSDATE+1);
INSERT INTO test VALUES (3, SYSDATE-10, SYSDATE+10);
INSERT INTO test VALUES (3, SYSDATE, SYSDATE);
UPDATE test SET rid = 3 WHERE rid = 1;

Invisible Indexes

Create Invisible Index

CREATE INDEX <index_name>
ON <table_name> (<column_name>)
PCTFREE <integer>
TABLESPACE <tablespace_name>
INVISIBLE;

CREATE TABLE visib AS
SELECT table_name, tablespace_name
FROM all_tables;

CREATE INDEX ix_visib
ON visib(table_name);

CREATE TABLE invis AS
SELECT table_name, tablespace_name
FROM all_tables;

CREATE INDEX ix_invis
ON invis(table_name)
INVISIBLE;

SELECT index_name, table_name, visibility
FROM user_indexes
WHERE index_name LIKE '%VIS%';

EXPLAIN PLAN FOR
SELECT table_name
FROM visib
WHERE table_name = 'SERVERS';

SELECT * FROM TABLE(dbms_xplan.display);

EXPLAIN PLAN FOR
SELECT table_name
FROM invis
WHERE table_name = 'SERVERS';

SELECT * FROM TABLE(dbms_xplan.display);

ALTER SESSION SET "optimizer_use_invisible_indexes" = TRUE;

EXPLAIN PLAN FOR
SELECT table_name
FROM invis
WHERE table_name = 'SERVERS';

SELECT * FROM TABLE(dbms_xplan.display);

Reverse Key Indexes

Warning

Reverse key indexes can have a disastrous effect - made worse by the fact that they can look like a good idea for weeks or months, and then suddenly cause a massive I/O problem. Reverse key indexes need to be 100% buffered to be efficient - the same is not necessarily true of indexes which have not been reversed, so reversing an index may result in much more physical I/O appearing in your system.

~ Jonathan Lewis

Create Reverse Key Index

Note: An index can not be both REVERSE and DESCENDING

CREATE INDEX <index_name>
ON <table_name> (<column_name>)
PCTFREE <integer>
TABLESPACE <tablespace_name>
REVERSE;

CREATE INDEX rix_index_demo_person_id
ON index_demo(person_id)
PCTFREE 0
TABLESPACE uwdata
REVERSE;

SELECT index_name, index_type
FROM user_indexes;

Virtual / NoSegment

A virtual index is a non-physical (no-segments) index useful for evaluating whether the optimizer will benefit from index creation prior to creating a physical index. These are not officially supported by Oracle but are used, extensively, by the OEM Grid Control.

Create No Segment Index

CREATE INDEX <index_name>
ON <table_name> (<column_name>, [<column_name>]) NOSEGMENT;

CREATE TABLE virtual AS
SELECT table_name, tablespace_name
FROM all_tables;

CREATE INDEX vix_virtual_table_name
ON virtual(table_name)
NOSEGMENT;

SELECT segment_name
FROM user_segments
WHERE segment_name = 'VIX_VIRTUAL_TABLE_NAME';

SELECT index_name, index_type
FROM user_indexes
WHERE index_name = 'VIX_VIRTUAL_TABLE_NAME';

desc virtual

col column_name format a20

SELECT column_name, column_position
FROM user_ind_columns
WHERE index_name = 'VIX_VIRTUAL_TABLE_NAME';

SELECT object_name
FROM user_objects
WHERE object_name = 'VIX_VIRTUAL_TABLE_NAME';

exec dbms_stats.gather_table_stats('UWCLASS', 'VIRTUAL', CASCADE=>TRUE);

set autotrace traceonly

SELECT table_name
FROM virtual
WHERE table_name = 'SERVERS';

alter session set "_use_nosegment_indexes" = TRUE;

SELECT table_name
FROM virtual
WHERE table_name = 'SERVERS';

set autotrace off

ALTER INDEX vix_virtual_table_name COALESCE;

Local And Global Indexes

See the link at page bottom to Partitioning.

Alter Index

Alter Index Monitor Usage

ALTER INDEX <index_name> MONITORING USAGE;

ALTER INDEX ix_index_demo_gender_state MONITORING USAGE;

exec dbms_stats.gather_index_stats(OWNNAME=>'UWCLASS', INDNAME=>'IX_INDEX_DEMO_GENDER_STATE');

SELECT COUNT(*)
FROM index_demo
WHERE gender = 'M';

SELECT *
FROM v$object_usage;

ALTER INDEX ix_index_demo_gender_state NOMONITORING USAGE;

Alter Index Rename

ALTER INDEX <index_name> RENAME TO <new_name>;

SELECT index_name
FROM user_indexes;

ALTER INDEX bix_gender RENAME TO ixb_gender;

SELECT index_name
FROM user_indexes;

ALTER INDEX ixb_gender RENAME TO bix_gender;

Alter Index Coalesce

ALTER INDEX <index_name> COALESCE;

ALTER INDEX ix_index_demo_gender_state COALESCE;

Alter Index Rebuild

ALTER INDEX <index_name> REBUILD [ONLINE];

ALTER INDEX ix_index_demo_gender_state REBUILD ONLINE;

Alter Index Rebuild and Change Tablespace

ALTER INDEX <index_name>
REBUILD TABLESPACE <tablspace_name>;

SELECT index_name, tablespace_name
FROM user_indexes;

ALTER INDEX ix_index_demo_gender_state
REBUILD TABLESPACE uwdata ONLINE;

SELECT index_name, tablespace_name
FROM user_indexes;

Alter Index Allocate Extent

ALTER INDEX <index_name> ALLOCATE EXTENT;

SELECT SUM(bytes), SUM(blocks)
FROM user_extents
WHERE segment_name = 'IX_INDEX_DEMO_GENDER_STATE';

ALTER INDEX ix_index_demo_gender_state ALLOCATE EXTENT;

SELECT SUM(bytes), SUM(blocks)
FROM user_extents
WHERE segment_name = 'IX_INDEX_DEMO_GENDER_STATE';

ALTER INDEX ix_index_demo_gender_state ALLOCATE EXTENT;

SELECT SUM(bytes), SUM(blocks)
FROM user_extents
WHERE segment_name = 'IX_INDEX_DEMO_GENDER_STATE';

ALTER INDEX ix_index_demo_gender_state ALLOCATE EXTENT;

SELECT SUM(bytes), SUM(blocks)
FROM user_extents
WHERE segment_name = 'IX_INDEX_DEMO_GENDER_STATE';

ALTER INDEX ix_index_demo_gender_state ALLOCATE EXTENT;
ALTER INDEX ix_index_demo_gender_state ALLOCATE EXTENT;
ALTER INDEX ix_index_demo_gender_state ALLOCATE EXTENT;

SELECT SUM(bytes), SUM(blocks)
FROM user_extents
WHERE segment_name = 'IX_INDEX_DEMO_GENDER_STATE';

Alter Index Deallocate Unused

ALTER INDEX <index_name> DEALLOCATE UNUSED;

SELECT SUM(bytes), SUM(blocks)
FROM user_extents
WHERE segment_name = 'IX_INDEX_DEMO_GENDER_STATE';

ALTER INDEX ix_index_demo_gender_state DEALLOCATE UNUSED;

Alter Index Deallocate Unused

ALTER INDEX <index_name> DEALLOCATE UNUSED KEEP <integer> <K|M>;

SELECT SUM(bytes), SUM(blocks)
FROM user_extents
WHERE segment_name = 'IX_INDEX_DEMO_GENDER_STATE';

ALTER INDEX ix_index_demo_gender_state
DEALLOCATE UNUSED KEEP 5M;

Alter Index Logging

ALTER INDEX <index_name> <LOGGING | NOLOGGING>;

SELECT index_name, logging
FROM user_indexes;

ALTER INDEX bix_gender NOLOGGING;

SELECT index_name, logging
FROM user_indexes;

ALTER INDEX bix_gender LOGGING;

SELECT index_name, logging
FROM user_indexes;

Alter Index Parallel

ALTER INDEX <index_name> PARALLEL <integer>;

SELECT index_name, degree
FROM user_indexes;

ALTER INDEX bix_gender PARALLEL 2;

SELECT index_name, degree
FROM user_indexes;

Alter Index Disable (applies only to Function Based Indexes

ALTER INDEX <index_name> DISABLE;

ALTER INDEX fbi_emp_sal_x_comm DISABLE;

Alter Index Enable (applies only to Function Based Indexes)

ALTER INDEX <index_name> ENABLE;

ALTER INDEX fbi_emp_sal_x_comm ENABLE;

Alter Index Usable / Unusable

ALTER INDEX <index_name> UNUSABLE;

SELECT index_name, status
FROM user_indexes;

ALTER INDEX bix_gender UNUSABLE;

SELECT index_name, status
FROM user_indexes;

ALTER INDEX bix_gender REBUILD;

SELECT index_name, status
FROM user_indexes;

Alter Index Reverse

ALTER INDEX <index_name> REBUILD REVERSE;

CREATE INDEX ix_index_demo_person_id
ON index_demo (person_id);

SELECT index_name, index_type
FROM user_indexes;

ALTER INDEX ix_index_demo_person_id REBUILD REVERSE;

SELECT index_name, index_type
FROM user_indexes;

Alter Index Update Block Reference

See Index Organized Tables

Drop Index

DROP INDEX <index_name>;

DROP INDEX ix_index_demo_gender_state;

Index Block Dump

Dumping an index tree including branch block headers, leaf block headers, and leaf block contents

col object_name format a30

SELECT object_name, object_id
FROM user_objects;

ALTER SESSION SET EVENTS 'immediate trace name treedump level 54220';

Alternative index dump

ORADEBUG DUMP TREEDUMP 54220;

Index Related Queries

Analyze Index

set linesize 121
col avg_leaf_blocks_per_key format 999
col avg_leaf_blocks_per_key head leafs_key
col avg_data_blocks_per_key format 999
col avg_data_blocks_per_key head data_key

SELECT index_name,blevel,distinct_keys,
avg_leaf_blocks_per_key,avg_data_blocks_per_key
FROM user_indexes;

Show all indexes and their columns and column positions

set verify off
col index_owner format a20
col column_name format a20
col tablespace_name format a20
break on table_name skip 1;

SELECT c.index_owner, i.index_name,
DECODE(i.uniqueness, 'UNIQUE', 'YES', 'NO') UNIQUENESS,
c.column_name, c.column_position, i.tablespace_name
FROM dba_ind_columns c, dba_indexes i
WHERE i.index_name = c.index_name
AND i.table_owner = c.table_owner
ORDER BY c.index_owner, i.index_name, c.column_position;

Index Stats History

desc sys.wri$_optstat_ind_history

SELECT obj#, COUNT(*)
FROM sys.wri$_optstat_ind_history
GROUP BY obj#
HAVING COUNT(*) > 1
ORDER BY 2;