At the same time, the efficiency of querying the maximum and minimum values sometimes needs to query the maximum and minimum values of some data at the same time. How can we get the results as quickly as possible? We know that the index has already sorted the data, so the index efficiency is the highest. Let's look at an example: Create a date index in a table with nearly 2 million data, the maximum and minimum values of the date must be retrieved simultaneously: [SQL] SQL> select COUNT (*) from USER_OBJECTS_TMP t; COUNT (*) ---------- 1933312 CREATE INDEX user_objects_create_dt ON user_objects_tmp (created) TABLESPACE tbs_lubinsu; SQL> desc user_objects_tmp Name Type Nullable Default Comments -------------- ------------- -------- ------- -------- OBJECT_NAME VARCHAR2 (128) Y SUBO BJECT_NAME VARCHAR2 (30) Y OBJECT_ID number y DATA_OBJECT_ID number y OBJECT_TYPE VARCHAR2 (19) y created date y LAST_DDL_TIME date y timestamp VARCHAR2 (19) y status VARCHAR2 (7) y temporary VARCHAR2 (1) y generated VARCHAR2 (1) y secondary VARCHAR2 (1) Y; -- creates an index in the CREATED field. If you use the MAX and MIN functions to obtain the maximum and minimum values, as shown in the following figure: -- time consumed: Elapsed: 00:00:00. 54. From the execution plan, we can see that the modified statement does not take the index, because no predicate is specified here. [SQL] SQL> set timing on SQL> set autotrace traceonly SQL> set linesize 200 SQL> SELECT MIN (created), MAX (created) AS MIN FROM user_objects_tmp; Elapsed: 00:00:00. 54 Execution Plan -------------------------------------------------------- Plan hash value: 3066201300 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | percent | 0 | select statement | 1 | 9 | 1718 (4) | 00:00:21 | 1 | sort aggregate | 1 | 9 | 2 | table access full | USER_OBJECTS_TMP | 1600K | 13M | 1718 (4) | 00:00:21 | notice Note ------dynamic sampling used This statement Statistics limit 5 recursive call0 db block gets 8639 consistent gets 659 physical reads 0 redo size 481 bytes sent via SQL * Net to client 385 bytes received via SQL * Net from client 2 SQL * Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed -- if the predicate is specified, the result is as follows, which does not improve the efficiency. -- because the index fu with the maximum max min value specified at the same time cannot be indexed Ll scan (MIN/MAX) SCAN, -- the read method for index fast full scan is: Starting from the segment header, read contains bitmap blocks, ROOT blocks, all BRANCH blocks, LEAF blocks, -- The read sequence is determined by the physical storage location, and multiple reads are taken. Each time DB_FILE_MULTIBLOCK_READ_COUNT is read. -- When querying the total number of records in a table, the index fast full scan based on the primary key is the most effective, but it is not the most suitable here. -- Index full scan: first locates the root node of the INDEX tree, branches, and then read the leaf node, which is read in the order of two-way linked list of the leaf node, -- this reading method is sequential and sorted, so we want to use this method: [SQL] SQL> SELECT MIN (created), MAX (created) as min from user_objects_tmp a WHERE. created is not null; Elapsed: 00:00:00. 62 Execution Plan -------------------------------------------------------- Plan hash value: 3784617757 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | minute | 0 | select statement | 1 | 9 | 1661 (1) | 00:00:20 | 1 | sort aggregate | 1 | 9 | * 2 | index fast full scan | USER_OBJECTS_CREATE_DT | 1600K | 13M | 1661 (1) | 00:00:20 | identified by operation id: ----------------------------------------------------------------- 2-filter ("". "CREATED" is not null) note ------dynamic sampling used for this statement Statistics limit 5 recursive cballs 0 db block gets 8647 consistent gets 267 physical reads 0 redo size 481 bytes sent via SQL * Net to client 385 bytes encoded ed SQL * Net from client 2 SQL * Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed -- since it cannot be put together for query, we will split the two functions: -- let them go through the full table scan of the index respectively: -- execution time: Elapsed: 00:00:00. 10 [SQL] SQL> select min, MAX 2 FROM (select min (created) as min from user_objects_tmp) a, 3 (select max (created) as max from user_objects_tmp) B; elapsed: 00:00:00. 10 Execution Plan -------------------------------------------------------- Plan hash value: 4210122108 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | minute | 0 | select statement | 1 | 18 | 3437 (4) | 00:00:42 | 1 | nested loops | 1 | 18 | 3437 (4) | 00:00:42 | 2 | VIEW | 1 | 9 | 1718 (4) | 00:00:21 | 3 | sort aggregate | 1 | 9 | 4 | index full scan (MIN/MAX) | USER_OBJECTS_CREATE_DT | 1600K | 13M | 5 | VIEW | 1 | 9 | 1718 (4) | 00:00:21 | 6 | sort aggregate | 1 | 9 | 7 | index full scan (MIN/MAX) | USER_OBJECTS_CREATE_DT | 1600K | 13M | notice Note ------dynamic sampling used for this statement Statistics limit 195 recursive CILS 0 db block gets 159 consistent gets 72 physical reads 0 redo size 472 bystes via SQL * Net to client 385 bytes encoded ed via SQL * Net from client 2 SQL * Net roundtrips to/from client 5 sorts (memory) 0 sorts (disk) 1 rows processed -- visible, the efficiency is the highest select min, max from (select min (created) as min from user_objects_tmp), (select max (created) as max from user_objects_tmp) B;