Index Series 11-- ordering of Index characteristics and Optimization of stored values max
Index optimization of MAX/MIN
Drop table t purge
Create table t as select * from dba_objects
Update t set object_id=rownum
Alter table t add constraint pk_object_id primary key (OBJECT_ID)
Set autotrace on
Set linesize 1000
Select max (object_id) from t
Carry out the plan
-
| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
-
| | 0 | SELECT STATEMENT | | 1 | 13 | 2 (0) | 00:00:01 |
| | 1 | SORT AGGREGATE | | 1 | 13 |
| | 2 | INDEX FULL SCAN (MIN/MAX) | PK_OBJECT_ID | 1 | 13 | 2 (0) | 00:00:01 |
-
Statistical information
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
431 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
The minimum teacher's experiment does not need to show the results of the implementation plan, which must be the same as the maximum implementation plan!
Select min (object_id) from t
-- if the index is not used as follows, see how the execution plan is different, and see the difference between cost and logical read!
Select / * + full (t) * / max (object_id) from t
Carry out the plan
| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
| | 0 | SELECT STATEMENT | | 1 | 13 | 292 (1) | 00:00:04 |
| | 1 | SORT AGGREGATE | | 1 | 13 |
| | 2 | TABLE ACCESS FULL | T | 92407 | 1173K | 292 (1) | 00:00:04 |
Statistical information
0 recursive calls
0 db block gets
1047 consistent gets
0 physical reads
0 redo size
431 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
-in addition, the following experiments can be done to see if the performance difference is significant with the increase in the number of records with an index.
Set autotrace off
Drop table t_max purge
Create table t_max as select * from dba_objects
Insert into t_max select * from t_max
Insert into t_max select * from t_max
Insert into t_max select * from t_max
Insert into t_max select * from t_max
Insert into t_max select * from t_max
Select count (*) from t_max
Create index idx_t_max_obj on t_max (object_id)
Set autotrace on
Select max (object_id) from t_max
Carry out the plan
-
| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
-
| | 0 | SELECT STATEMENT | | 1 | 13 | 3 (0) | 00:00:01 |
| | 1 | SORT AGGREGATE | | 1 | 13 |
| | 2 | INDEX FULL SCAN (MIN/MAX) | IDX_T_MAX_OBJ | 1 | 13 | 3 (0) | 00:00:01 |
-
Statistical information
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
431 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
/ *
If object_id is allowed to be empty, will it adopt the INDEX FULL SCAN (MIN/MAX) efficient algorithm after adding an index?
Of course I will! What are you afraid of when you take the maximum and minimum?
, /
Drop table t purge
Create table t as select * from dba_objects
Create index idx_object_id on t (object_id)
Set autotrace on
Set linesize 1000
Select max (object_id) from t
