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

-UNION needs to be sorted.

Drop table t1 purge

Create table T1 as select * from dba_objects where object_id is not null

Alter table t1 modify OBJECT_ID not null

Drop table t2 purge

Create table T2 as select * from dba_objects where object_id is not null

Alter table t2 modify OBJECT_ID not null

Set linesize 1000

Set autotrace traceonly

Select object_id from t1

Union

Select object_id from t2

Carry out the plan

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

| | 0 | SELECT STATEMENT | | 136k | 1732K | | 1241 (55) | 00:00:15 |

| | 1 | SORT UNIQUE | | 136k | 1732K | 2705K | 1241 (55) | 00:00:15 |

| | 2 | UNION-ALL |

| | 3 | TABLE ACCESS FULL | T1 | 57994 | 736K | | 292 (1) | 00:00:04 |

| | 4 | TABLE ACCESS FULL | T2 | 78456 | 996k | | 292 (1) | 00:00:04 |

Statistical information

0 recursive calls

0 db block gets

2094 consistent gets

0 physical reads

0 redo size

1062305 bytes sent via SQL*Net to client

54029 bytes received via SQL*Net from client

4876 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

73120 rows processed

-- found that the index cannot eliminate UNION sorting (INDEX FAST FULL SCAN)

Create index idx_t1_object_id on T1 (object_id)

Create index idx_t2_object_id on T2 (object_id)

Set autotrace traceonly

Set linesize 1000

Select object_id from t1

Union

Select object_id from t2

Carry out the plan

-

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

-

| | 0 | SELECT STATEMENT | | 136k | 1732K | | 755 (57) | 00:00:10 |

| | 1 | SORT UNIQUE | | 136k | 1732K | 2705K | 755 (57) | 00:00:10 |

| | 2 | UNION-ALL |

| | 3 | INDEX FAST FULL SCAN | IDX_T1_OBJECT_ID | 57994 | 736K | | 49 (0) | 00:00:01 |

| | 4 | INDEX FAST FULL SCAN | IDX_T2_OBJECT_ID | 78456 | 996k | | 49 (0) | 00:00:01 |

-

Statistical information

0 recursive calls

0 db block gets

340 consistent gets

0 physical reads

0 redo size

1062305 bytes sent via SQL*Net to client

54029 bytes received via SQL*Net from client

4876 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

73120 rows processed

-- INDEX FULL SCAN's index still cannot eliminate UNION sorting

Select / * + index (T1) * / object_id from T1

Union

Select / * + index (T2) * / object_id from T2

Carry out the plan

-

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

-

| | 0 | SELECT STATEMENT | | 136k | 1732K | | 1010 (56) | 00:00:13 |

| | 1 | SORT UNIQUE | | 136k | 1732K | 2705K | 1010 (56) | 00:00:13 |

| | 2 | UNION-ALL |

| | 3 | INDEX FULL SCAN | IDX_T1_OBJECT_ID | 57994 | 736K | | 177K (1) | 00:00:03 |

| | 4 | INDEX FULL SCAN | IDX_T2_OBJECT_ID | 78456 | 996k | | 177K (1) | 00:00:03 |

-

Statistical information

0 recursive calls

0 db block gets

326 consistent gets

0 physical reads

0 redo size

1062305 bytes sent via SQL*Net to client

54029 bytes received via SQL*Net from client

4876 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

73120 rows processed

Conclusion: the index can not eliminate UNION sorting. Generally speaking, it is necessary to determine the necessity when using UNION, and only UNION ALL is needed when the data will not be duplicated.

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.