Oracle Indexes
Version 21c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
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
Data Dictionary Objects
ALL_INDEXES DBA_IND_COLUMNS INDSUBPART$
ALL_IND_COLUMNS DBA_IND_EXPRESSIONS IND_ONLINE$
ALL_IND_EXPRESSIONS ICOL$ IND_STATS$
CDB_INDEXES IND$ USER_INDEXES
CDB_IND_COLUMNS INDPART$ USER_IND_COLUMNS
CDB_IND_EXPRESSIONS INDEX_HISTOGRAMS USER_IND_EXPRESSIONS
COL$ INDEX_STATS V$OBJECT_USAGE
DBA_INDEXES    
System Privileges
ALTER ANY INDEX CREATE ANY INDEX DROP ANY INDEX
Related initialization parameters
optimizer_index_caching optimizer_use_invisible_indexes _disable_function_based_index
optimizer_index_cost_adj skip_unusable_indexes  
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
  1. Indexes and tables should be in separate tablespaces for performance reasons. This is pure unadulterated rubbish without foundation in fact.
  2. 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 pay attention to 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@pdbdev

SELECT tablespace_name
FROM user_tablespaces;

conn sys@pdbdev 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@pdbdev

SELECT tablespace_name
FROM user_tablespaces;
Create Table For Index Demos conn uwclass/uwclass@pdbdev

CREATE TABLE index_demo (
person_id NUMBER(10),
gender    VARCHAR2(1),
state     VARCHAR2(2),
textcol   VARCHAR2(2000))
TABLESPACE data_lrg;
Load Demo Table 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)
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@pdbdev

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)
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)
TABLESPACE uwdata
NOSORT;
NOSORT with ASSM tablespaces conn sys@pdbdev 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@pdbdev

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)
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)
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)
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';
 
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@pdbdev

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@pdbdev

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)
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.

If the function is a user created function it must be DETERMINISTIC.
-- FBI Demo Table and Data
CREATE 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@pdbdev

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@pdbdev

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@pdbdev

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 t1 (
col1 VARCHAR2(10) NOT NULL,
col2 NUMBER(5) NOT NULL);

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

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

INSERT INTO t1 (col1, col2) VALUES ('N', 1);
Range Uniqueness Enforcement conn uwclass/uwclass@pdbdev

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 AUTHID DEFINER 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;
 
Indexing NULLs
Including NULL in an index

In theory Oracle doesn't index NULLs but that is not only not true ... the technique demonstrated here is currently in use by Oracle in its own fully supported applications.
conn uwclass/uwclass@pdbdev

CREATE TABLE ixnulls (
col1 NUMBER,
col2 VARCHAR2(10),
col3 VARCHAR2(10));

BEGIN
  FOR i IN 1 .. 100000 LOOP
  INSERT INTO ixnulls
  (col1, col2)
  VALUES
  (i, dbms_crypto.randombytes(5));
  END LOOP;
  COMMIT;
END;
/

SELECT *
FROM ixnulls
WHERE rownum < 6;

 COL1 COL2       COL3
----- ---------- ----------
    1 01AA485457
    2 8253F07F13
    3 A17F3CB628
    4 0D56BDF4A9
    5 B065EB11A0


-- the integer values highlighted in the CREATE INDEX statements tell the database to
-- create and index what is essentially a virtual column and include NULLs in the index


CREATE INDEX ixnulls1 ON ixnulls(col1);
CREATE INDEX ixnulls2 ON ixnulls(col1, 1);
CREATE INDEX ixnulls3 ON ixnulls(col1, 1, col2);
CREATE INDEX ixnulls4 ON ixnulls(col1, 1, col2, col3, 2);

SELECT index_name, index_type
FROM dba_indexes
WHERE table_name = 'IXNULLS'
ORDER BY 1;

INDEX_NAME  INDEX_TYPE
----------- ----------------------
IXNULLS1    NORMAL
IXNULLS2    FUNCTION-BASED NORMAL
IXNULLS3    FUNCTION-BASED NORMAL
IXNULLS4    FUNCTION-BASED NORMAL


-- the following output may seem misleading, and it is, but the issue is the
-- column naming convention in the view


SELECT index_name, column_name, column_position
FROM dba_ind_columns
WHERE table_name = 'IXNULLS'
ORDER BY 1;

INDEX_NAME COLUMN_NAME   COLUMN_POSITION
----------- ------------ ---------------
IXNULLS1    COL1                       1
IXNULLS2    COL1                       1
IXNULLS2    SYS_NC00004$               2
IXNULLS3    SYS_NC00004$               2
IXNULLS3    COL1                       1
IXNULLS3    COL2                       3
IXNULLS4    COL1                       1
IXNULLS4    SYS_NC00004$               2
IXNULLS4    COL2                       3
IXNULLS4    COL3                       4
IXNULLS4    SYS_NC00005$               5


-- the table only has 3 columns so how can the COLUMN_POSITION by 4 or 5?
-- POSITION is the position in the index ... not the table as seen below

SELECT object_id, object_name
2 FROM dba_objects
3 WHERE object_name LIKE 'IXNULLS%'
4 AND object_type = 'INDEX'
5 ORDER BY 1;

 OBJECT_ID OBJECT_NAME
---------- ------------
    157821 IXNULLS1
    157822 IXNULLS2
    157823 IXNULLS3
    157824 IXNULLS4


SELECT obj#, col#, pos#
FROM icol$
WHERE obj# BETWEEN 157821 AND 157824;

   OBJ#  COL#  POS#
------- ----- -----
 157821     1     1
 157822     1     1
 157822     0     2
 157823     1     1
 157823     0     2
 157823     2     3
 157824     1     1
 157824     0     2
 157824     2     3
 157824     3     4
 157824     0     5


-- note the positions in the CREATE INDEX statement orrespond wiith COL# which is
-- the column number in the table


exec dbms_stats.gather_table_stats(USER, 'IXNULLS', CASCADE=>TRUE);

PL/SQL procedure successfully completed.

SELECT segment_name, segment_type, blocks, bytes
FROM dba_segments
WHERE segment_name LIKE 'IXNULLS%'
ORDER BY 2,1;

SEGMENT_NAME  SEGMENT_TYPE   BLOCKS    BYTES
------------- ------------- ------- --------
IXNULLS1      INDEX             256  2097152
IXNULLS2      INDEX             384  3145728
IXNULLS3      INDEX             512  4194304
IXNULLS4      INDEX             512  4194304
IXNULLS       TABLE             384  3145728


-- Time to pay attention to the number of 8K blocks allocated: Look at indexes 3 and 4
-- and remember that COL3 is all NULLs. Let's change that


UPDATE ixnulls
SET col3 = 'A'
WHERE MOD(col1,2) = 0;

50000 rows updated.

COMMIT;

Commit complete.

-- 50% of the rows have had COL3 updated to a single byte ... essentially 50K

exec dbms_stats.gather_table_stats(USER, 'IXNULLS', CASCADE=>TRUE);

PL/SQL procedure successfully completed.

SELECT segment_name, segment_type, blocks, bytes
FROM dba_segments
WHERE segment_name LIKE 'IXNULLS%'
ORDER BY 2,1;

SEGMENT_NAME  SEGMENT_TYPE   BLOCKS    BYTES
------------- ------------- ------- --------
IXNULLS1      INDEX             256  2097152
IXNULLS2      INDEX             384  3145728
IXNULLS3      INDEX             512  4194304
IXNULLS4      INDEX            1024  8388608
IXNULLS       TABLE             384  3145728


-- the index is now double the size and almost triple the size of the table
-- this is not to say don't use this technique, Oracle Corp. does, but rather,
-- use it with care
Including NULL in an index

SYS_OP_MAP_NONNULL is covered on the undocumented Oracle page of the library

An undocumented solution to indexing NULLs can be found on the Undocumented Oracle link at the bottom of this page
conn uwclass/uwclass@pdbdev

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

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

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

SELECT COUNT(*) FROM t2;

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

CREATE INDEX ix_t2
ON t2 (col2);

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

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

SELECT * FROM TABLE(dbms_xplan.display);
------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------
|  0 | SELECT STATEMENT  |      | 8184 |   159K|    85   (0)| 00:00:01 |
|* 1 |  TABLE ACCESS FULL| T2   | 8184 |   159K|    85   (0)| 00:00:01 |
------------------------------------------------------------------------
-- note the number of null rows is actually 9090

CREATE INDEX ix_t2_mapnn
ON t2 (sys_op_map_nonnull(col2));

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

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

SELECT * FROM TABLE(dbms_xplan.display);
-------------------------------------------------------------------------------------------------
| Id | Operation                           | Name        | Rows | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                    |             |  903 | 29799 |     4   (0)| 00:00:01 |
|  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T2          |  903 | 29799 |     4   (0)| 00:00:01 |
|* 2 |   INDEX RANGE SCAN                  | IX_T2_MAPNN |  361 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
-- note again that the number of null rows is actually 9090.

/* Clearly the cost with SYS_OP_MAP_NONNULL is substantially lower but, too, the explain plan numbers don';t match reality, so create your own demo with a far larger number of rows and test for timing and/or run a trace to see what is really going on. Then decide whether to use this trick. */
Another solution for indexing NULLs conn sys@pdbdev as sysdba

GRANT EXECUTE ON dbms_crypto TO uwclass;

conn uwclass/uwclass@pdbdev

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;

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

CREATE INDEX ix_t
ON t(completed);

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

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


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';

SELECT * FROM TABLE(dbms_xplan.display);
------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------
|  0 | SELECT STATEMENT  |      |    1 |     2 |     1   (0)| 00:00:01 |
|  1 |  SORT AGGREGATE   |      |    1 |     2 |            |          |
|* 2 |   INDEX RANGE SCAN| IX_T |   10 |    20 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------
Including NULL in an index

Another method suggested by Richard Foote and David A. W. Johnson
conn uwclass/uwclass@pdbdev

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

-- create an index in which the leading column is the one with nulls
CREATE INDEX ix_t3_itc
ON t3 (col2, col1)
TABLESPACE uwdata;

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

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

SELECT * FROM TABLE(dbms_xplan.display);

-- compare this cost of the plans above
-----------------------------------------------------------------------------
| Id | Operation        | Name      | Rows | Bytes | Cost  (%CPU)| Time     |
-----------------------------------------------------------------------------
|  0 | SELECT STATEMENT |           |    1 |    20 |     0    (0)|          |
|* 1 |  FILTER          |           |      |       |             |          |
|  2 |   INDEX FULL SCAN| IX_T3_ITC |    1 |    20 |     1    (0)| 00:00:01 |
-----------------------------------------------------------------------------

/* SYS_OP_MAP_NONNULL may be undocumented ... but it can sometimes work 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. */
 
IndexType
Note: These indexes require a previously created INDEXTYPE object.
IndexType index based on the CTXSYS contains operator CREATE INDEX <[schema_name.]index_name>
ON <[schema_name.]table_name>(<column_name_list>)
INDEXTYPE IS <[schema_name.]indextype_object_name>;
conn uwclass/uwclass@pdbdev

CREATE TABLE ctxdemo (
col1 NUMBER,
col2 VARCHAR2(100));

ALTER TABLE ctxdemo
ADD CONSTRAINT pk_ctxdemo
PRIMARY KEY (col1);

INSERT INTO ctxdemo VALUES (1,';Oracle A B C';);
COMMIT;

set autot trace exp

SELECT col1
FROM ctxdemo
WHERE contains(col2, ';A or C';) > 0;
SELECT col1
*
ERROR at line 1:
ORA-20000: Oracle Text error:
DRG-10599: column is not indexed


CREATE INDEX ctxix_ctxdemo_col2
ON ctxdemo(col2)
INDEXTYPE IS ctxsys.context;

SELECT col1
FROM ctxdemo
WHERE contains(col2, ';A or C';) > 0;

SELECT col1
FROM ctxdemo
WHERE contains(col2, ';r';) > 0;
 
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)
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;

Table created.

CREATE INDEX vix_virtual_table_name
ON virtual(table_name)
NOSEGMENT;

Index created.

SELECT segment_name
FROM user_segments
WHERE segment_name = 'VIX_VIRTUAL_TABLE_NAME';

no rows selected

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

no rows selected

desc virtual

 Name            Null?     Type
---------------  --------  --------------
TABLE_NAME       NOT NULL  VARCHAR2(128)
TABLESPACE_NAME            VARCHAR2(30)


col column_name format a20

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

COLUMN_NAME          COLUMN_POSITION
-------------------- ---------------
TABLE_NAME                         1


SELECT object_name
FROM user_objects
WHERE object_name = 'VIX_VIRTUAL_TABLE_NAME';

OBJECT_NAME
------------------------------
VIX_VIRTUAL_TABLE_NAME


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

PL/SQL procedure successfully completed.

set autotrace traceonly

SELECT table_name
FROM virtual
WHERE table_name = 'SERVERS';

Execution Plan
----------------------------
Plan hash value: 1255570001
-----------------------------------------------------------------
| Id | Operation         | Name    | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------
|  0 | SELECT STATEMENT  |         |     1 |    18 |     5   (0)|
|* 1 |  TABLE ACCESS FULL| VIRTUAL |     1 |    18 |     5   (0)|
-----------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("TABLE_NAME"='SERVERS')

Statistics
----------------------------------------------------
1 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
557 bytes sent via SQL*Net to client
421 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed


ALTER SESSION SET "_use_nosegment_indexes" = TRUE;

Session altered.

SELECT table_name
FROM virtual
WHERE table_name = 'SERVERS';

Execution Plan
----------------------------
Plan hash value: 4146180004

------------------------------------------------------------------------------
| Id | Operation        | Name                  | Rows | Bytes | Cost (%CPU) |
------------------------------------------------------------------------------
|  0 | SELECT STATEMENT |                       |    1 |    18 |     1   (0) |
|* 1 |  INDEX RANGE SCAN| VIX_VIRTUAL_TABLE_NAME|    1 |    18 |     1   (0) |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("TABLE_NAME"='SERVERS')

Statistics
---------------------------------------------------
0 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
557 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed


set autotrace off

ALTER INDEX vix_virtual_table_name COALESCE;
ALTER INDEX vix_virtual_table_name COALESCE
*
ERROR at line 1:
ORA-08114: can not alter a fake index
 
XML Indexes
Create XML Index CREATE INDEX <index_name>
ON <table_name>
(<column_name>, [<column_name>])
INDEXTYPE IS xdb.xmlindex;
CREATE TABLE xml_index_demo (
clobcol CLOB);

CREATE INDEX ix_xml
ON xml_index_demo(clobcol)
INDEXTYPE IS xdb.xmlindex;

CREATE INDEX ix_xml
*
ERROR at line 1:
ORA-29958: fatal error occurred in the execution of ODCIINDEXCREATE routine
ORA-30963: The indexed column is not of XMLType.


DROP TABLE xml_index_demo PURGE;

CREATE TABLE xml_index_demo (
xmlcol XMLTYPE);

CREATE INDEX ix_xml
ON xml_index_demo(xmlcol)
INDEXTYPE IS xdb.xmlindex;

SELECT index_name, index_type
FROM user_indexes
WHERE table_name = 'XML_INDEX_DEMO';

INDEX_NAME                INDEX_TYPE
------------------------- ----------------------
IX_XML                    FUNCTION-BASED DOMAIN
SYS_IL0000157829C00002$$  LOB
 
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 [CLEANUP];
ALTER INDEX ix_index_demo_gender_state COALESCE;
Alter Index Partitioned Table ALTER INDEX <index_name> COALESCE [CLEANUP];
conn uwclass/uwclass@pdbdev

CREATE TABLE range_part (
prof_history_id NUMBER(10),
person_id       NUMBER(10) NOT NULL,
organization_id NUMBER(10) NOT NULL,
record_date     DATE NOT NULL)
PARTITION BY RANGE (record_date) (
PARTITION yr11 VALUES LESS THAN (TO_DATE('01-JAN-2012','DD-MON-YYYY')),
PARTITION yr12 VALUES LESS THAN (TO_DATE('01-JAN-2013','DD-MON-YYYY')),
PARTITION yr13 VALUES LESS THAN (TO_DATE('01-JAN-2014','DD-MON-YYYY')),
PARTITION yr99 VALUES LESS THAN (MAXVALUE));

CREATE INDEX ix_perid ON range_part(person_id);

ALTER INDEX ix_perid COALESCE CLEANUP;
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

Note: Unless you've no requirement for recovery never make an index NOLOGGING.
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 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;

Related Topics
Advanced Index Compression
Analyze
Autotrace
Clustering Factor
Compressed Indexes
Clusters and Hash Indexes
DBMS_AUTO_INDEX
DBMS_I_INDEX_UTL
DBMS_INDEX_UTL
DBMS_INDEXING
DBMS_PCLXUTIL
DBMS_SPACE.CREATE_INDEX_COST
DBMS_STATS
Hints
Index Organized Table
Nested Tables
NVL2 Function
Object Tables
Partitioning
SYS_OP_LBID
SYS_OP_MAP_NONNULL
XIMETADATA_PKG
What's New In 21c
What's New In 23c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx