Document Type | Technical Information
Category | Tuning
Applicable Product Version | Tibero 7.2.4
Document Number | TTUTI038
Overview
This is an example that utilizes the characteristic that an index maintains the sorted state of values to extract Min and Max values.
Method
1. Create Test Object and Data
To perform the example that omits sort operations according to INDEX sorting, first execute the following statements.
-- Create test table DROP TABLE T1; CREATE TABLE "T1" ( "C1" NUMBER, "C2" NUMBER, "C3" NUMBER, "C4" VARCHAR2(10) ); -- Generate 20 million rows INSERT /*+ append */ INTO T1 SELECT 1 , 1 , 1 , 'TIBERO' FROM DUAL CONNECT BY LEVEL<=20000000; COMMIT; -- Create index CREATE INDEX IDX_T1_C1C2 ON T1(C1,C2); -- Gather statistics for T1 table EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'TIBERO', TABNAME=>'T1');
2. Execution Without Index
Check the execution plan when finding the maximum value without using an index.
SQL> SET TIMING ON
SQL> ALTER SESSION SET GATHER_SQL_PLAN_STAT=Y;
Session altered.
SQL> SELECT MAX(C3) FROM T1 WHERE C3 > 0 AND C1 = 1;
MAX(C3)
----------
1
1 row selected.
Total elapsed time 00:00:06.869296
SQL> set linesize 180
SQL> set pagesize 0
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL BUFGETS'));
SQL ID : fa94ybmrnh3yy
CHILD NUMBER : 501
HASH VALUE : 4014477278
PLAN HASH VALUE: 1804130916
EXECUTIONS : 2
FETCHES : 2
LOADED AT : 2025/12/08 16:15:26
TOT ELAPSED TIME: 00:00:14.1772
AVG ELAPSED TIME: 00:00:07.0886
TOT BUFFER GETS: 119498
AVG BUFFER GETS: 59749
-----------------------------------------------------------------------------------------------------------------
| ID | Operation | Name | Cost (%CPU) | Cards | Rows | Elaps. Time | BUFGETS | Starts |
-----------------------------------------------------------------------------------------------------------------
| 1 | COLUMN PROJECTION | |26335 (6.44)| 1 | 1 |00:00:00.0000 | 0 | 1 |
| 2 | SORT AGGR | |26335 (6.44)| 1 | 1 |00:00:00.4022 | 0 | 1 |
| 3 | TABLE ACCESS (FULL) | T1 |25588 (3.71)| 19987446 | 20000000 |00:00:06.4635 | 59749 | 1 |
-----------------------------------------------------------------------------------------------------------------
LAST ELAPSED TIME: 00:00:06.8657
LAST BUFFER GET: 59749
Predicate Information
-----------------------------------------------------------------------------------------------------------------
3 - filter: ("T1"."C3" > 0) AND ("T1"."C1" = 1) (1.000 * 1.000)
Note
Since there is no index on the
C3column of theT1table, a Full Scan is performed followed by a Sort operation to retrieve theMAXvalue.The number of block requests in read consistency mode (
consistent gets) is confirmed to be 59,749.
3. Using INDEX_DESC
Check the execution plan when using the INDEX_DESC hint to instruct the index scan to be in descending order to find the maximum value. Through the example below, you can see that it finds the maximum value of the IDX_T1_C1C2 index's C2 column by scanning the index in reverse order of its sorted state (currently sorted from smaller to larger values) performing a backward scan (scanning from larger values).
SQL>
SELECT /*+ INDEX_DESC(T1 IDX_T1_C1C2)*/
MAX(C2)
FROM T1
WHERE C1 = 1
AND C2 > 0
/
MAX(C2)
----------
1
1 row selected.
Total elapsed time 00:00:00.000233
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL BUFGETS'));
SQL ID : 4k1ak2xnmj3td
CHILD NUMBER : 507
HASH VALUE : 1765314349
PLAN HASH VALUE: 472743196
EXECUTIONS : 3
FETCHES : 3
LOADED AT : 2025/12/08 16:22:39
TOT ELAPSED TIME: 00:00:00.0007
AVG ELAPSED TIME: 00:00:00.0002
TOT BUFFER GETS: 18
AVG BUFFER GETS: 6
------------------------------------------------------------------------------------------------------------------------------------
| ID | Operation | Name | Cost (%CPU) | Cards | Rows | Elaps. Time | BUFGETS | Starts |
------------------------------------------------------------------------------------------------------------------------------------
| 1 | COLUMN PROJECTION | |48126 (.75)| 1 | 1 |00:00:00.0000 | 0 | 1 |
| 2 | SORT AGGR | |48126 (.75)| 1 | 1 |00:00:00.0000 | 0 | 1 |
| 3 | COUNT (STOP NODE) (STOP LIMIT 2) | |48126 (.75)| 1 | 1 |00:00:00.0000 | 0 | 1 |
| 4 | INDEX (RANGE SCAN) DESCENDING | IDX_T1_C1C2 |47764 (0)| 19987446 | 263 |00:00:00.0000 | 6 | 1 |
------------------------------------------------------------------------------------------------------------------------------------
LAST ELAPSED TIME: 00:00:00.0000
LAST BUFFER GET: 6
Predicate Information
------------------------------------------------------------------------------------------------------------------------------------
3 - filter: ("T1"."C2" IS NOT NULL) (1.000)
4 - access: ("T1"."C1" = 1) AND ("T1"."C2" > 0) (1.000 * 1.000)
Note
It reads fewer blocks than a table Full Scan and no sorting operation occurs, so the load is low.
INDEX_DESChint was applied, and the execution took about 0.0002 seconds.The number of block requests in read consistency mode (
consistent gets) is confirmed to be 6.
4. Using INDEX_ASC
Check the execution plan when using the INDEX_ASC hint to instruct the index scan to be in ascending order to find the maximum value. Through the example below, you can see that it applies the IDX_T1_C1C2 index scan hint and performs a forward scan (scanning from smaller values) according to the index sorted state (currently sorted from smaller to larger values).
SQL>
SELECT /*+ INDEX_ASC(T1 IDX_T1_C1C2)*/
MAX(C2)
FROM T1
WHERE C1 = 1
AND C2 > 0
/
MAX(C2)
----------
1
1 row selected.
Total elapsed time 00:00:00.474051
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL BUFGETS'));
SQL ID : b5kz6gzxqam8p
CHILD NUMBER : 510
HASH VALUE : 4217720085
PLAN HASH VALUE: 3306013127
EXECUTIONS : 3
FETCHES : 3
LOADED AT : 2025/12/08 16:30:53
TOT ELAPSED TIME: 00:00:12.6722
AVG ELAPSED TIME: 00:00:04.2241
TOT BUFFER GETS: 143901
AVG BUFFER GETS: 47967
-----------------------------------------------------------------------------------------------------------------------
| ID | Operation | Name | Cost (%CPU) | Cards | Rows | Elaps. Time | BUFGETS | Starts |
-----------------------------------------------------------------------------------------------------------------------
| 1 | COLUMN PROJECTION | |48510 (1.54)| 1 | 1 |00:00:00.0000 | 0 | 1 |
| 2 | SORT AGGR | |48510 (1.54)| 1 | 1 |00:00:00.3712 | 0 | 1 |
| 3 | INDEX (RANGE SCAN) | IDX_T1_C1C2 |47764 (0)| 19987446 | 20000000 |00:00:00.0959 | 47967 | 1 |
-----------------------------------------------------------------------------------------------------------------------
LAST ELAPSED TIME: 00:00:00.4671
LAST BUFFER GET: 47967
Predicate Information
-----------------------------------------------------------------------------------------------------------------------
3 - access: ("T1"."C1" = 1) AND ("T1"."C2" > 0) (1.000 * 1.000)
Note
After performing an Index Scan to find values satisfying the condition, a sorting operation is performed to retrieve the
MAXvalue.The number of block requests in read consistency mode (
consistent gets) is confirmed to be 47,967.
5. Using INDEX_DESC with ORDER BY Clause
When finding the maximum value of the C2 column in the T1 table, unlike example 3, check the execution plan when not using the MAX function but using the ORDER BY clause. It uses the sorting of the IDX_T1_C1C2 index to find the maximum value of the C2 column and performs a backward scan (scanning from larger values) on the index sorted state (currently sorted from smaller to larger values).
SQL>
SELECT C2
FROM ( SELECT /*+ INDEX_DESC(T1 IDX_T1_C1C2)*/
C2
FROM T1
WHERE C1 = 1
AND C2 > 0
ORDER BY C1 DESC, C2 DESC
)
WHERE ROWNUM <= 1
/
C2
----------
1
1 row selected.
Total elapsed time 00:00:00.000222
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL BUFGETS'));
SQL ID : bs671x3mca6xs
CHILD NUMBER : 511
HASH VALUE : 3871677368
PLAN HASH VALUE: 872264694
EXECUTIONS : 3
FETCHES : 3
LOADED AT : 2025/12/08 16:39:45
TOT ELAPSED TIME: 00:00:00.0290
AVG ELAPSED TIME: 00:00:00.0097
TOT BUFFER GETS: 18
AVG BUFFER GETS: 6
----------------------------------------------------------------------------------------------------------------------------------
| ID | Operation | Name | Cost (%CPU) | Cards | Rows | Elaps. Time | BUFGETS | Starts |
----------------------------------------------------------------------------------------------------------------------------------
| 1 | COUNT (STOP NODE) (STOP LIMIT 2) | |47764 (0)| 1 | 1 |00:00:00.0000 | 0 | 1 |
| 2 | INDEX (RANGE SCAN) DESCENDING | IDX_T1_C1C2 |47764 (0)| 19987446 | 263 |00:00:00.0000 | 6 | 1 |
----------------------------------------------------------------------------------------------------------------------------------
LAST ELAPSED TIME: 00:00:00.0000
LAST BUFFER GET: 6
Predicate Information
----------------------------------------------------------------------------------------------------------------------------------
2 - access: ("T1"."C1" = 1) AND ("T1"."C2" > 0) (1.000 * 1.000)
Note
It reads fewer blocks than a table Full Scan and no sorting operation occurs, so the load is low.
INDEX_DESChint was applied, and the execution took about 0.0002 seconds.The number of block requests in read consistency mode (
consistent gets) is confirmed to be 6.