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

-indexing and sorting

Drop table t purge

Create table t as select * from dba_objects

Set autotrace traceonly

-- oracle is smart enough not to be foolish enough to sort here, do query conversion, and ignore this sort.

Select count (*) from t order by object_id

-the following statement describes the sort

Set autotrace traceonly

Set linesize 1000

Drop table t purge

Create table t as select * from dba_objects

-- the following statement has no index and order by, so sorting is bound to occur.

Select * from t where object_id > 2 order by object_id

Carry out the plan

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

| | 0 | SELECT STATEMENT | | 92407 | 18m | | 4454 (1) | 00:00:54 |

| | 1 | SORT ORDER BY | | 92407 | 18m | 21m | 4454 (1) | 00:00:54 |

| | * 2 | TABLE ACCESS FULL | T | 92407 | 18m | | 294 (2) | 00:00:04 |

Statistical information

0 recursive calls

0 db block gets

1047 consistent gets

0 physical reads

0 redo size

3513923 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)

73117 rows processed

-after adding an index, it is possible for Oracle to take advantage of the orderly nature of the index to avoid sorting, as shown below:

Create index idx_t_object_id on t (object_id)

Set autotrace traceonly

Select * from t where object_id > 2 order by object_id

Carry out the plan

-

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

-

| | 0 | SELECT STATEMENT | | 92407 | 18m | 1302 (1) | 00:00:16 |

| | 1 | TABLE ACCESS BY INDEX ROWID | T | 92407 | 18m | 1302 (1) | 00:00:16 |

| | * 2 | INDEX RANGE SCAN | IDX_T_OBJECT_ID | 92407 | | 177 (1) | 00:00:03 |

-

Statistical information

0 recursive calls

0 db block gets

10952 consistent gets

0 physical reads

0 redo size

8115221 bytes sent via SQL*Net to client

54029 bytes received via SQL*Net from client

4876 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

73117 rows processed

In the following case, Oracle must not hesitate to choose to use the index, because the return table has been cancelled!

Select object_id from t where object_id > 2 order by object_id

Carry out the plan

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

| | 0 | SELECT STATEMENT | | 92407 | 1173K | 177K | 00:00:03 |

| | * 1 | INDEX RANGE SCAN | IDX_T_OBJECT_ID | 92407 | 1173K | 177K (1) | 00:00:03 |

Statistical information

0 recursive calls

0 db block gets

5027 consistent gets

0 physical reads

0 redo size

1062289 bytes sent via SQL*Net to client

54029 bytes received via SQL*Net from client

4876 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

73117 rows processed

In addition, Oracle will not need an index if it is the following statement.

Select object_id from t where object_id > 2

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.