【SQL 学习】排序问题之order by与索引排序-白红宇

SQL> conn yang/yang as sysdba

已连接。

SQL> create table t as select object_id id ,object_name name

2 from dba_objects ;

表已创建。

SQL> set autot traceonly

一次普通的全表扫描，没有排序的！

SQL> select id ,name from t;

已选择68372行。

执行计划

----------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 64794 | 4998K| 91 (2)| 00:00:02 |

| 1 |

TABLE ACCESS FULL| T | 64794 | 4998K| 91 (2)| 00:00:02 |

--------------------------------------------------------------------------

Note

-----

- dynamic sampling used for this statement

统计信息

----------------------------------------------------------

68 recursive calls

0 db block gets

4943 consistent gets

325 physical reads

0 redo size

2936793 bytes sent via SQL*Net to client

50554 bytes received via SQL*Net from client

4560 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

68372 rows processed

--根据id 排序！注意执行计划里面的TempSpc 是临时空间,大小11M

SQL> select id ,name from t order by id;

已选择68372行。

执行计划

----------------------------------------------------------

Plan hash value: 961378228

----------------------------------------------------------------------------------

TempSpc| Cost (%CPU)| Time |

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 64794 | 4998K| | 1283 (1)| 00:00:16 |

| 1 |

SORT ORDER BY | | 64794 | 4998K|

11M| 1283 (1)| 00:00:16 |

| 2 | TABLE ACCESS FULL| T | 64794 | 4998K| | 91 (2)| 00:00:02 |

-----------------------------------------------------------------------------------

Note

-----

- dynamic sampling used for this statement

统计信息

----------------------------------------------------------

4 recursive calls

0 db block gets

394 consistent gets

0 physical reads

0 redo size

2663362 bytes sent via SQL*Net to client

50554 bytes received via SQL*Net from client

4560 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

68372 rows processed

--在表的 id 字段建立索引，并进行信息统计。

SQL> create index idx_id on t(id) ;

索引已创建。

SQL> exec dbms_stats.gather_table_stats(user ,'T',cascade => true);

PL/SQL 过程已成功完成。

SQL> select id ,name from t order by id;

已选择68372行。

执行计划

----------------------------------------------------------

Plan hash value: 961378228

-----------------------------------------------------------------------------------

TempSpc| Cost (%CPU)| Time |

-----------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 68372 | 1936K| | 638 (1)| 00:00:08 |

| 1 |

SORT ORDER BY | | 68372 | 1936K|

5384K| 638 (1)| 00:00:08 |

| 2 | TABLE ACCESS FULL| T | 68372 | 1936K| | 91 (2)| 00:00:02 |

-----------------------------------------------------------------------------------

统计信息

----------------------------------------------------------

151 recursive calls

0 db block gets

348 consistent gets

0 physical reads

0 redo size

2663362 bytes sent via SQL*Net to client

50554 bytes received via SQL*Net from client

4560 SQL*Net roundtrips to/from client

5 sorts (memory) --没有使用索引比全表扫描多了四此排序。

0 sorts (disk)

68372 rows processed

--使用索引。执行计划中没有tempspac

SQL> select id ,name from t where id <1200

order by id;

--加上了order by

已选择1133行。

执行计划

----------------------------------------------------------

Plan hash value: 827754323

--------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1167 | 33843 | 10 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T | 1167 | 33843 | 10 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | IDX_ID | 1167 | | 4 (0)| 00:00:01 |

--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("ID"<1200)

统计信息

----------------------------------------------------------

1 recursive calls

0 db block gets

160 consistent gets

0 physical reads

0 redo size

37872 bytes sent via SQL*Net to client

1241 bytes received via SQL*Net from client

77 SQL*Net roundtrips to/from client

0 sorts (memory) --这里可以看出没有排序！

0 sorts (disk)

1133 rows processed

小结：

如果数据直接从索引获取，也是有序的，此时加order by,cbo不会执行sort 排序动作的。即，加上order by对性能也不会有什么影响！

其实这里还是有疑问的

1 根据id 排序走全表扫描和建立了索引后信息统计上有差别，前者比后者少了3个sort 操作！而后者的TempSpc比全表扫描少了将近一半！

2 关于TempSpc 的理解如果是临时表空间，就用到了磁盘排序了，而执行上面没有显示disk sort！

对这两个问题问个为什么？