Performance problems of querying the maximum and minimum values of a column in a single Oracle table

In Oracle 10 Gb, there is a single-Table query SQL statement, which does not have a where clause, but simply calculates the maximum and minimum values of a column at the same time.
According to our understanding, it should undergo a full index scan, but it does. The data size of a single table is a little large, and the composition is a little complicated. There are many LOB fields, many indexes, and 13 lob indexes. The performance is much worse, and the operation in milliseconds is expected to be minutes. In other databases of the same version, if there are few indexes, full index scanning will be performed, but the performance is not good, and consistent reading during query is also great.

The SQL statement is as follows: select max (updateid), min (updateid) from dbcenter. TABLE_NAME;
Simple, And the updateid column has a unique index. The index has also been analyzed, but the performance is poor now, and the entire table scan is fatal.


First, use set autotrace trace exp stat to get the actual execution plan.
SQL> set timing on
SQL> set autotrace trace exp stat
SQL> set linesize 300

Bytes -------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes -------------------------------------------------------------------------------------
| 0 | select statement | 1 | 7 | 373 K (1) | 01:14:42 |
| 1 | sort aggregate | 1 | 7 |
| 2 | table access full | TABLE_NAME | 8665K | 57M | 373 K (1) | 01:14:42 |
Bytes -------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
1 recursive cballs
0 db block gets
1700621 consistent gets
1506260 physical reads
0 redo size
602 bytes sent via SQL * Net to client
492 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>

The result shows that the full table scan is performed. From the statistical value, it is also the real full table scan, from the first scan to the tail, there is no way, the value of this field in the table is not sorted, not all scan results do not know the maximum and minimum values.

Obviously, this is not the optimal result. In my opinion, the most ideal thing is to use the index of the updateid column. One index can be quickly scanned.

I guess there will be more indexes and I don't know how to choose. In the select clause, the index is not automatically selected?

However, I have no effect on using hint, And the optimizer still does not choose to use this index.


Select/* + index_ffs (TABLE_NAME IDX55021287) */MAX (updateid), MIN (updateid) from dbcenter. TABLE_NAME;

Elapsed: 00:03:28. 77

Execution Plan
----------------------------------------------------------


Bytes -------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes -------------------------------------------------------------------------------------
| 0 | select statement | 1 | 7 | 373 K (1) | 01:14:42 |
| 1 | sort aggregate | 1 | 7 |
| 2 | table access full | TABLE_NAME | 8665K | 57M | 373 K (1) | 01:14:42 |
Bytes -------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive cballs
0 db block gets
1701902 consistent gets
1497285 physical reads
0 redo size
602 bytes sent via SQL * Net to client
492 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

However, if you only query max or min, the index is used.

Select MIN (updateid) from dbcenter. TABLE_NAME;

Execution Plan
----------------------------------------------------------
Plan hash value: 3935799349

Bytes ------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes ------------------------------------------------------------------------------------------
| 0 | select statement | 1 | 7 | 373 K (1) | 01:14:42 |
| 1 | sort aggregate | 1 | 7 |
| 2 | index full scan (MIN/MAX) | IDX55021287 | 8665K | 57M |
Bytes ------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
0 recursive cballs
0 db block gets
3 consistent gets
0 physical reads
0 redo size
524 bytes sent via SQL * Net to client
492 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

Good performance, consistent read only 3. This result is also very understandable. An index is a unique index and has been sorted. To obtain a maximum value, you must scan the data blocks at the beginning or end of the index.


Therefore, you need to analyze the process of generating the SQL Execution Plan. I use the event 10053 trace name context forever and level 1 methods to complete this operation.

Alter system flush shared_pool;
Alter session set "_ optimizer_search_limit" = 15;
Oradebug setmypid;
Oradebug event 10053 trace name context forever, level 1;
Explain plan for select max (updateid), min (updateid) from dbcenter. TABLE_NAME;

***************************************
SINGLE TABLE ACCESS PATH
-----------------------------------------
BEGIN Single Table Cardinality Estimation
-----------------------------------------
Table: TABLE_NAME Alias: TABLE_NAME
Card: Original: 8663996 Rounded: 8663996 Computed: 8663996.00 Non Adjusted: 8663996.00
-----------------------------------------
END Single Table Cardinality Estimation
-----------------------------------------
Access Path: TableScan
Cost: 373495.00 Resp: 373495.00 Degree: 0
Cost_io: 372211.00 Cost_cpu: 18442053762
Resp_io: 372211.00 resp_cpus: 18442053762
* ******* Begin index join costing ********
* ***** Trying bitmap/domain indexes ******
Access Path: index (FullScan)
Index: IDX242025
Resc_io: 25019.00 resc_cpu: 1911171307
Ix_sel: 1 ix_sel_with_filters: 1
Cost: 2515.21 Resp: 2515.21 Degree: 0
Access Path: index (FullScan)
Index: IDX94341804
Resc_io: 31023.00 resc_cpu: 1953914433
Ix_sel: 1 ix_sel_with_filters: 1
Cost: 3115.90 Resp: 3115.90 Degree: 0
Access Path: index (FullScan)
Index: PK_TABLE_NAME
Resc_io: 25217.00 resc_cpu: 1912567352
Ix_sel: 1 ix_sel_with_filters: 1
Cost: 2535.02 Resp: 2535.02 Degree: 0
Access Path: index (FullScan)
Index: IDX242025
Resc_io: 25019.00 resc_cpu: 1911171307
Ix_sel: 1 ix_sel_with_filters: 1
Cost: 2515.21 Resp: 2515.21 Degree: 0
* ***** Finished trying bitmap/domain indexes ******
* ******* End index join costing ********
Best: AccessPath: TableScan
Cost: 373495.00 Degree: 1 Resp: 373495.00 Card: 8663996.00 Bytes: 0
***************************************

As a result, the optimizer did not calculate the IDX55021287 index during the index join costing operation.

Even if I use alter session set "_ optimizer_search_limit" = 15; upgrading the limit value from 5 to 15 does not work. Perhaps the number of indexes introduced during the index join costing operation is not controlled by this parameter.

The query operation with the maximum and minimum values should not be completed in SQL step by step. Apparently, oracle's query rewriting is less intelligent than separating it. Not even at 11G. I tested it.