Skip to main content

Search

Welcome to Tibero GTS!

Example of Query Using INDEX Sorting

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 C3 column of the T1 table, a Full Scan is performed followed by a Sort operation to retrieve the MAX value.

  • 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_DESC hint 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 MAX value.

  • 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_DESC hint 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.

 

Related to