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

The performance problem of querying the maximum and minimum values of a column in a single table is that in oracle 10 Gb, there is an SQL statement for querying a single table. It 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; is 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 onSQL> set autotrace trace exp statSQL> set linesize 300 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | percent | 0 | select statement | 1 | 7 | 373 K (1) | 01:14:42 | 1 | sort aggregate | 1 | 7 | 2 | T Able access full | TABLE_NAME | 8665K | 57M | 373 K (1) | 01:14:42 | Statistics limit 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 received 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 -------------------------------------------------------- Operation | 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 | Statistics ---------------------------------------------------------- 1 recursive cballs 0 db block gets 1701902 consistent gets 149 7285 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 | history Statistics limit 0 recursive CILS 0 db block gets 3 consistent gets 0 physical reads 0 redo size 524 bytes sent 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 has good performance and only 3 consistent reads. 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 encrypted Access Path: TableScan Cost: 373495.00 Resp: 373495.00 Degree: 0 Cost_io: 372211.00 Cost_cpu: 18442053762 Resp_io: 372211.00 Resp_c Pu: 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 kernel: 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: 3 115.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 I Ndex join costing ******** Best: AccessPath: TableScan Cost: 373495.00 Degree: 1 Resp: 373495.00 Card: 8663996.00 Bytes: 0 *************************************** from the results, during the index join costing operation, the optimizer does not calculate the IDX55021287 index. 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.