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.
Background
I have put this page together because, quite frankly, it is important when learning to use hints to have demos that reliably reproduce a specified behavior and to my horror,
though not my amazement, the examples in the Oracle docs going backward as far as I could check all include examples that do not work.
So on this page I have extracted the relevant descriptive text from the Oracle docs and with attribution, from the work of Richard Foote, Jonathan Lewis, and a few others to help you build the demos and understand why the work.
Demo Tables not included in the general library table build can be found [here]
conn uwclass/uwclass@pdbdev
CREATE TABLE t (testcol NUMBER);
INSERT INTO t (testcol) VALUES (1);
INSERT INTO t (testcol) VALUES (2);
INSERT INTO t (testcol) VALUES (3);
INSERT INTO t (testcol) VALUES (4);
INSERT INTO t (testcol) VALUES (5);
COMMIT;
Index Scan Demos
Fast Full Scan
A fast full scan is a full index scan in which the database reads all index blocks in no particular order. The database accesses the data in the index itself, without accessing the table.
This is the optimal access method when all of the information required to answer a query is contained in indexed columns.
Thus we will start the demo by first finding indexed columns and then building a query that uses only those columns. And, to make the demo as reliable as possible only those where the column is the first column in the index.
conn uwclass/uwclass@pdbdev
col column_name format a30
SELECT table_name, index_name, column_position, column_name
FROM user_ind_columns
WHERE column_position = 1
AND table_name = 'AIRPLANES'
ORDER BY 1,2,3;
A full index scan eliminates a sort operation, because the data is ordered by the index key. It reads all index blocks singly. The database uses a full scan in any of the following situations:
* An ORDER BY clause that meets the following requirements is present in the query:
o All of the columns in the ORDER BY clause must be in the index
o The order of the columns in the ORDER BY clause must match the order of the leading index columns
The ORDER BY clause can contain all of the columns in the index or a subset of the columns in the index.
* The query requires a sort merge join. The database can perform a full index scan instead of doing a full table scan followed by a sort when the query meets the following requirements:
o All of the columns referenced in the query must be in the index.
o The order of the columns referenced in the query must match the order of the leading index columns.
The query can contain all of the columns in the index or a subset of the columns in the index.
* A GROUP BY clause is present in the query, and the columns in the GROUP BY clause are present in the index. The columns do not need to be in the same order in the index and the GROUP BY clause.
The GROUP BY clause can contain all of the columns in the index or a subset of the columns in the index.
According to the Oracle docs (as of 30-Nov-2009)
The optimizer uses a range scan when it finds one or more leading columns of an index specified in conditions, such as the following: * col1 = :b1
* col1 < :b1
* col1 > :b1
* AND combination of the preceding conditions for leading columns in the index
and this just isn't correct without some clarification.
A range scan is one in which the index is scanned for a range of values and the easiest way to force this behavior is with constructs such as BETWEEN and BOOLEAN operators such as < and >.
conn uwclass/uwclass@pdbdev
-- example with equals ("=")
SELECT *
FROM t
WHERE testcol = 2;
CREATE INDEX nui_t ON t(testcol);
EXPLAIN PLAN FOR
SELECT *
FROM t
WHERE testcol = 2;
SELECT * FROM TABLE(dbms_xplan.display);
------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 |
|* 1 | INDEX RANGE SCAN| NUI_T | 1 | 13 |
------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("TESTCOL"=2)
DROP INDEX nui_t;
CREATE UNIQUE INDEX nui_t ON t(testcol);
EXPLAIN PLAN FOR
SELECT *
FROM t
WHERE testcol = 2;
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("TESTCOL"=2)
/* What the Oracle docs don't say and yet is obviously important is that if the index is a unique index Oracle will preferentially choose a more efficient unique scan if it can. */
-- examples with greater-than and less-than
DROP INDEX nui_t;
CREATE INDEX nui_t ON t(testcol);
EXPLAIN PLAN FOR
SELECT *
FROM t
WHERE testcol < 2;
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("TESTCOL"<2)
Note
-----
-- dynamic sampling used for this statement
/* with the Boolean greater-than and less-than operators it does not matter whether the index is unique or non-unique */
Range Scan Descending
An index range scan descending is identical to an index range scan, except that the data is returned in descending order. Indexes, by default, are stored in ascending order.
Usually, the database uses this scan when ordering data in a descending order to return the most recent data first, or when seeking a value less than a specified value.
The optimizer uses index range scan descending when an an index can satisfy an order by descending clause.
conn uwclass/uwclass@pdbdev
SELECT *
FROM t;
-- if it exists
DROP INDEX nui_t;
CREATE INDEX di_t
ON t(testcol DESC);
EXPLAIN PLAN FOR
SELECT *
FROM t
WHERE testcol BETWEEN 3 AND 4;
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access(SYS_OP_DESCEND("TESTCOL")>=HEXTORAW('3EFAFF') AND
SYS_OP_DESCEND("TESTCOL")<=HEXTORAW('3EFBFF') )
filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("TESTCOL"))>=3 AND
SYS_OP_UNDESCEND(SYS_OP_DESCEND("TESTCOL"))<=4)
Note
-----
- dynamic sampling used for this statement
/* That wasn't it so we can forget about that. It a DESCENDING SCAN has nothing to do with the use of a descending index. Now lets look at what it is. */
DROP INDEX di_t;
CREATE INDEX nui_t
ON t(testcol);
EXPLAIN PLAN FOR
SELECT *
FROM t
WHERE testcol BETWEEN 3 AND 4
ORDER BY testcol DESC;
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("TESTCOL">=3 AND "TESTCOL"<=4)
Note
-----
- dynamic sampling used for this statement
-- It is, as you can now see a reverse read on an ascending index.
Skip Scan
The following description is from the 11gR2 docs:
An index skip scan uses logical subindexes of a composite index. The database "skips" through a single index as if it were searching separate indexes.
Skip scanning is beneficial if there are few distinct values in the leading column of a composite index and many distinct values in the nonleading key of the index.
The database may choose an index skip scan when the leading column of the composite index is not specified in a query predicate. For example, assume that you run the following query for a customer in the sh.customers table:
The following demo was written and posted by Richard Foote and is recreated here for your convenience. A few minor changes have been made to avoid using keywords and to clarify the demo:
Though I should expect the very mention of David Bowie should be sufficient for everyone to know this is Richard's.
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("CODE"=42)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2635 consistent gets
0 physical reads
0 redo size
531 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
-- However, search on the CODE column only and the index can not be used.
-- Whereas the leading column is very selective, a CODE value of 42 could
-- potentially be referenced within any of the index leaf blocks
-- Let's now re-create the index but with the columns reversed.
DROP INDEX ix_ziggy_stuff_rid_code;
CREATE INDEX ix_ziggy_stuff_code_rid
ON ziggy_stuff(code, rid);
SELECT *
FROM ziggy_stuff
WHERE rid = 42 AND code = 42;
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows| Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1| 13 | 4 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID | ZIGGY_STUFF | 1| 13 | 4 (0)|
|* 2 | INDEX RANGE SCAN | IX_ZIGGY_STUFF_CODE_RID| 1| | 3 (0)|
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CODE"=42 AND "RID"=42)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
2 physical reads
0 redo size
535 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
-- again as expected, the index is used when both columns are searched
SELECT *
FROM ziggy_stuff
WHERE code = 42;
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows| Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1| 13 | 4 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID | ZIGGY_STUFF | 1| 13 | 4 (0)|
|* 2 | INDEX RANGE SCAN | IX_ZIGGY_STUFF_CODE_RID| 1| | 3 (0)|
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CODE"=42)
Statistics
-------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
535 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
/* When searching on just the CODE column for the value 42, with the histogram in place, the CBO estimates there's only the one row and so can use the index effectively */
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("RID"=42)
filter("RID"=42)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
19 consistent gets
10 physical reads
0 redo size
603 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
/* When searching on just RID column, the CBO knows there are only 6 distinct CODE values. The CBO can effectively probe the index in 6 different locations and retrieve all the necessary rows.
At 19 consistent gets though, it's not as good as the 7 consistent gets with the previous index. However, it's not too bad and much better than the approx 2635 consistent gets required for a full table scan.
Perhaps the new index will suffice, making the overheads associated having a second index unnecessary ... */
SELECT /*+ NO_INDEX_SS(ziggy_stuff ix_ziggy_stuff_code_rid) */ *
FROM ziggy_stuff
WHERE rid = 42;
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RID"=42)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2636 consistent gets
0 physical reads
0 redo size
581 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
-- Sure looks that way: The skip scan is a good compromise.
Unique Scan
From the Oracle 11gR2 docs:
In contrast to an index range scan, an index unique scan must have either 0 or 1 rowid associated with an index key. The database performs a unique scan when a predicate references all of the columns in a UNIQUE index key using an equality operator.
An index unique scan stops processing as soon as it finds the first record because no second record is possible.
In other words the index must be created as a unique index either using the CREATE UNIQUE INDEX syntax or as a byproduct of the default creation (not deferrable) of a primary key or unique constraint.
CREATE INDEX nui_t on t(testcol);
SELECT COUNT(*)
FROM t
WHERE testcol = 2;
EXPLAIN PLAN FOR
SELECT COUNT(*)
FROM t
WHERE testcol = 2;
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TESTCOL"=2)
-- other ways of producing a UNIQUE SCAN
WHERE testcol IN (2)
WHERE testcol IN (2,3)
WHERE testcol IN (SELECT COUNT(*) FROM user_tables WHERE table_name = 'T')
WHERE testcol BETWEEN 3 AND 3 -- a range scan results if the values are not identical
