Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.