In oracle9i we know that we can use the Jump Index Scan (index Skip Scan). However, there are some limitations to the use of jumping index scans.
From Oracle's documentation we can find this:
Index Skip Scans
Index skip scans improve index scans by Nonprefix columns.
Often, scanning index blocks is faster than scanning table data blocks.
Skip scanning lets a composite index is split logically into smaller subindexes.
In skip scanning, the initial column of the composite index isn't specified in query.
In the other words, it is skipped.
The number of logical subindexes is determined from the number of distinct values in the initial column.
Skip scanning is advantageous if there are few distinct values in the leading column of the composite
Index and many distinct values in the Nonleading key of the index.
You can also say that the optimizer determines whether to use skip Scan based on the number of unique values in the leading column in the index (the first column indexed).
Let's do a test first:
sql> CREATE TABLE Test as
2 SELECT rownum a,rownum-1 B, ROWNUM-2 c,rownum-3 d,rownum-4 E
3 from All_objects
4/
Sql> SELECT DISTINCT COUNT (a) from test;
COUNT (A)
----------
28251
Table has been created.
Sql>
Sql> CREATE INDEX test_idx on test (A,B,C)
2/
The index has been created.
sql> ANALYZE TABLE Test COMPUTE STATISTICS
2 for TABLE
3 for all INDEXES
4 for all INDEXED COLUMNS
5/
The table is parsed.
Sql> SET autotrace Traceonly explain
Sql> SELECT * FROM Test WHERE b = 99
2/
--This is where the CBO chooses a full table scan.
--we went on to do another test:
sql> drop table test;
The table has been discarded.
sql> CREATE TABLE Test
2 AS
3 SELECT DECODE (MOD (rownum,2), 0, ' 1 ', ' 2 ') A,
4 ROWNUM-1 B,
5 ROWNUM-2 C,
6 ROWNUM-3 D,
7 ROWNUM-4 E
8 from All_objects
9/
Table has been created.
Sql> set Autotrace off
Sql> SELECT distinct a from test;
A
--
1
2
--A column has only two unique values
Sql> CREATE INDEX test_idx on test (A,B,C)
2/
The index has been created.
sql> ANALYZE TABLE Test COMPUTE STATISTICS
2 for TABLE
3 for all INDEXES
4 for all INDEXED COLUMNS
5/
The table is parsed.
Sql> Set Autotrace traceonly explain
Sql> SELECT * FROM Test WHERE b = 99
2/
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT optimizer=choose (cost=4 card=1 bytes=24)
1 0 TABLE ACCESS (by INDEX ROWID) of ' TEST ' (cost=4 card=1 bytes=24)
2 1 INDEX (SKIP SCAN) of ' Test_idx ' (non-unique) (cost=3 card=1)
The Oracle Optimizer (here refers to the CBO) can apply the index SKIP scans to queries at least a few conditions:
The 1 optimizer considers it appropriate.
The number of unique values for the leading columns in the 2 index satisfies certain conditions.
3 optimizer to know the value distribution of leading columns (through analysis/statistics)
4 Appropriate SQL statements
......
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.