Document Type | Technical Information
Category | Tuning
Applicable Product Version | Tibero 7.2.4
Document Number | TTUTI039
Overview
- INDEX RANGE SCAN vertically searches from the index root block to the leaf block and then scans only the necessary range of the leaf blocks.
- It is the most common access method for B*Tree indexes.
- Minimizing the index scan range and minimizing the number of accesses to the table are important.
- The leading column of the index must be used in the condition clause to enable index range scan.
- The result set generated through the index range scan process is sorted in the order of the index columns. Therefore, sort order by operations can be omitted or min/max values can be extracted quickly.
Method
1. Create Test Object and Data
To test performance according to the INDEX scan method, first execute the following statements.
-- Create test table
SET ECHO ON
DROP TABLE T1;
CREATE TABLE "T1"
( "C1" NUMBER,
"C2" NUMBER,
"C3" NUMBER,
"C4" NUMBER,
"C5" NUMBER,
"C6" NUMBER,
"C7" NUMBER,
"C8" NUMBER,
"C9" NUMBER,
"C10" NUMBER
);
-- Insert 10 million rows
INSERT /*+append*/ INTO T1
SELECT 1 C1
, LEVEL C2
, LEVEL C3
, LEVEL C4
, LEVEL C5
, LEVEL C6
, LEVEL C7
, LEVEL C8
, LEVEL C9
, LEVEL C10
FROM DUAL CONNECT BY LEVEL<=10000000;
COMMIT;
-- Create index
CREATE INDEX IDX_T1_C1C2 ON T1(C1,C2);
2. Querying Data Without Index
Check how much performance degradation occurs when performing a FULL SCAN of the table without using an index.
-- Disable index usage
SQL> ALTER INDEX IDX_T1_C1C2 INVISIBLE;
Index 'IDX_T1_C1C2' altered.
-- Query the count of rows matching the condition on table T1, takes about 1 second
SQL> SET TIMING ON
SQL> SET AUTOT ON
SQL> SELECT
COUNT(*)
FROM T1
WHERE C1 = 1
AND C2 >= 10 AND C2 <= 20
;
COUNT(*)
----------
11
1 row selected.
Total elapsed time 00:00:01.016124
SQL ID: ahh910anj0fhy
Child number: 1940
Plan hash value: 1845450593
Execution Plan
--------------------------------------------------------------------------------
1 COLUMN PROJECTION (Cost:35685, %%CPU:0, Rows:1)
2 SORT AGGR (Cost:35685, %%CPU:0, Rows:1)
3 TABLE ACCESS (FULL): T1 (Cost:35685, %%CPU:0, Rows:1)
Predicate Information
--------------------------------------------------------------------------------
3 - filter: ("T1"."C2" <= 20) AND ("T1"."C1" = 1) AND ("T1"."C2" >= 10) (0.000 * 1.000 * 1.000)
Note
--------------------------------------------------------------------------------
3 - dynamic sampling used for this table (16 blocks)
NAME VALUE
------------------------------ ----------
db block gets 795
consistent gets 85067
physical reads 77214
redo size 0
sorts (disk) 0
sorts (memory) 0
rows processed 1
Note
The query took about 1 second.
The number of block requests in read consistency mode (
consistent gets) is confirmed as 85,067.Physical reads are confirmed as 77,214. (
physical readsoccur when a full scan of theT1table segment reads all blocks and some are not found in the buffer cache.)
3. Using Fast Full Index Scan (INDEX FAST FULL SCAN)
Use the INDEX_FFS hint to instruct using the specified index to perform a Fast Full Index Scan and check the execution plan.
SQL> ALTER INDEX IDX_T1_C1C2 VISIBLE;
Index 'IDX_T1_C1C2' altered.
SQL> SELECT /*+ INDEX_FFS(T1 IDX_T1_C1C2)*/
COUNT(*)
FROM T1
WHERE C1 = 1
AND C2 >= 10 AND C2 <= 20
;
COUNT(*)
----------
11
1 row selected.
Total elapsed time 00:00:00.847795
SQL ID: f8sybg63t6xdz
Child number: 1941
Plan hash value: 862723915
Execution Plan
--------------------------------------------------------------------------------
1 COLUMN PROJECTION (Cost:11922, %%CPU:1, Rows:1)
2 SORT AGGR (Cost:11922, %%CPU:1, Rows:1)
3 FILTER (Cost:11922, %%CPU:1, Rows:1)
4 INDEX (FAST FULL SCAN): IDX_T1_C1C2 (Cost:11693, %%CPU:0, Rows:8196822)
Predicate Information
--------------------------------------------------------------------------------
3 - filter: ("T1"."C2" <= 20) AND ("T1"."C1" = 1) AND ("T1"."C2" >= 10) (0.000 * 1.000 * 1.000)
Note
--------------------------------------------------------------------------------
4 - dynamic sampling used for this table (16 blocks)
NAME VALUE
------------------------------ ----------
db block gets 104
consistent gets 28094
physical reads 0
redo size 0
sorts (disk) 0
sorts (memory) 0
rows processed 1
Note
INDEX_FFShint execution took about 0.85 seconds.The number of block requests in read consistency mode (
consistent gets) is confirmed as 28,094.Physical reads are confirmed as 0.
4. Using Range Index Scan (INDEX RANGE SCAN)
Use the INDEX_RS hint to instruct using the specified index to perform a Range Index Scan and check the execution plan.
SQL> SELECT /*+ INDEX_RS(T1 IDX_T1_C1C2)*/
COUNT(*)
FROM T1
WHERE C1 = 1
AND C2 >= 10 AND C2 <= 20
/
COUNT(*)
----------
11
1 row selected.
Total elapsed time 00:00:00.000384
SQL ID: 6ma4cuhmjbtnr
Child number: 1942
Plan hash value: 2030295845
Execution Plan
--------------------------------------------------------------------------------
1 COLUMN PROJECTION (Cost:3, %%CPU:0, Rows:1)
2 SORT AGGR (Cost:3, %%CPU:0, Rows:1)
3 INDEX (RANGE SCAN): IDX_T1_C1C2 (Cost:3, %%CPU:0, Rows:1)
Predicate Information
--------------------------------------------------------------------------------
3 - access: ("T1"."C1" = 1) AND ("T1"."C2" >= 10) AND ("T1"."C2" <= 20) (1.000 * 1.000 * 0.000)
Note
--------------------------------------------------------------------------------
3 - dynamic sampling used for this table (16 blocks)
NAME VALUE
------------------------------ ----------
db block gets 0
consistent gets 3
physical reads 0
redo size 0
sorts (disk) 0
sorts (memory) 0
rows processed 1
Note
INDEX_RShint execution took about 0.00038 seconds.The number of block requests in read consistency mode (
consistent gets) is confirmed as 3.Physical reads are confirmed as 0.