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.
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 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;
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';
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;
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));
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);
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;
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;
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>;
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;
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.
-- 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;
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;
/
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;
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. */
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;
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;
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';
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';
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;
