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

Optimization of SUM/AVG

Drop table t purge

Create table t as select * from dba_objects

Create index idx1_object_id on t (object_id)

Set autotrace on

Set linesize 1000

Set timing on

Select sum (object_id) from t

Carry out the plan

-

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

-

| | 0 | SELECT STATEMENT | | 1 | 13 | 49 (0) | 00:00:01 |

| | 1 | SORT AGGREGATE | | 1 | 13 |

| | 2 | INDEX FAST FULL SCAN | IDX1_OBJECT_ID | 92407 | 1173K | 49 (0) | 00:00:01 |

-

Statistical information

0 recursive calls

0 db block gets

170 consistent gets

0 physical reads

0 redo size

432 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

Compare the cost of not taking the index, and realize the importance of this index

Select / * + full (t) * / sum (object_id) from t

SUM (OBJECT_ID)

-

2732093100

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

432 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

-- something similar, such as AVG, is the same as SUM, as follows:

Select avg (object_id) from t

AVG (OBJECT_ID)

-

37365.5338

Carry out the plan

-

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

-

| | 0 | SELECT STATEMENT | | 1 | 13 | 49 (0) | 00:00:01 |

| | 1 | SORT AGGREGATE | | 1 | 13 |

| | 2 | INDEX FAST FULL SCAN | IDX1_OBJECT_ID | 92407 | 1173K | 49 (0) | 00:00:01 |

-

Statistical information

0 recursive calls

0 db block gets

170 consistent gets

0 physical reads

0 redo size

448 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

I don't know if you have noticed, but the experiment here has already told us that whether the OBJECT_ID column is empty or not does not affect the results of aggregations such as SUM/AVG.

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.