【求助】【急】关于Oracle10g里的Order by速度慢的问题
表结构：
CREATE TABLE TB_RID_TRACE_2013
(
ID VARCHAR2(50) NOT NULL CONSTRAINT PK_TB_RID_TRACE_2013 PRIMARY KEY,
SESSION_ID VARCHAR2(50),
LOGIN_AT DATE,
LOGOUT_AT DATE,
ID_TYPE VARCHAR2(32),
ID_CODE VARCHAR2(100),
ID_NAME VARCHAR2(100),
AREA_CODE VARCHAR2(15),
SERVICE_CODE VARCHAR2(32),
SERVICE_WAN_IP VARCHAR2(32),
SERVICE_LAN_IP VARCHAR2(32),
SERVICE_MAC VARCHAR2(32),
HOST_NAME VARCHAR2(100)
)
PARTITION BY RANGE (LOGIN_AT)
(
PARTITION TB_RID_TRACE_2013_PART_01 VALUES LESS THAN (TO_DATE('2013-02-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
PARTITION TB_RID_TRACE_2013_PART_02 VALUES LESS THAN (TO_DATE('2013-03-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
PARTITION TB_RID_TRACE_2013_PART_03 VALUES LESS THAN (TO_DATE('2013-04-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
PARTITION TB_RID_TRACE_2013_PART_04 VALUES LESS THAN (TO_DATE('2013-05-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
PARTITION TB_RID_TRACE_2013_PART_05 VALUES LESS THAN (TO_DATE('2013-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
PARTITION TB_RID_TRACE_2013_PART_06 VALUES LESS THAN (TO_DATE('2013-07-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
PARTITION TB_RID_TRACE_2013_PART_07 VALUES LESS THAN (TO_DATE('2013-08-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
PARTITION TB_RID_TRACE_2013_PART_08 VALUES LESS THAN (TO_DATE('2013-09-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
PARTITION TB_RID_TRACE_2013_PART_09 VALUES LESS THAN (TO_DATE('2013-10-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
PARTITION TB_RID_TRACE_2013_PART_10 VALUES LESS THAN (TO_DATE('2013-11-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
PARTITION TB_RID_TRACE_2013_PART_11 VALUES LESS THAN (TO_DATE('2013-12-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
PARTITION TB_RID_TRACE_2013_PART_12 VALUES LESS THAN (TO_DATE('2014-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
)
NOLOGGING;
该表上创建的索引：
CREATE INDEX IDX_2013_ID_CODE ON TB_RID_TRACE_2013 (ID_CODE DESC);
CREATE INDEX IDX_2013_LOGIN_AT ON TB_RID_TRACE_2013 (LOGIN_AT DESC);
CREATE INDEX IDX_2013_LOGOUT_AT ON TB_RID_TRACE_2013 (LOGOUT_AT DESC);
CREATE INDEX IDX_2013_SERVICE_CODE ON TB_RID_TRACE_2013 (SERVICE_CODE DESC);
目前表里有18000000条数据，系统里是使用ExtJS分页显示列表。问题是执行普通SQL速度很快。但是如果SQL里包含Order By后速度就变得爆慢。以下是我执行的SQL和时间以及执行计划的内容：
SQL1,执行时间：0.172秒100条数据
SELECT * FROM TB_RID_TRACE_2013 T WHERE T.LOGIN_AT BETWEEN TO_DATE('2013-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE('2013-06-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS');
SQL1执行计划：
执行计划
----------------------
Plan hash value: 3349134993
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1555K| 255M| 19128 (2)| 00:03:50 | | |
| 1 | PARTITION RANGE SINGLE| | 1555K| 255M| 19128 (2)| 00:03:50 | 6 | 6 |
|* 2 | TABLE ACCESS FULL | TB_RID_TRACE_2013 | 1555K| 255M| 19128 (2)| 00:03:50 | 6 | 6 |
------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T"."LOGIN_AT"<=TO_DATE('2013-06-15 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
SQL2,执行时间：14.789秒100条数据
SELECT T.* FROM TB_RID_TRACE_2013 T WHERE T.LOGIN_AT BETWEEN TO_DATE('2013-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE('2013-06-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS') ORDER BY T.LOGIN_AT;
SQL2执行计划：
执行计划
----------------------
Plan hash value: 2801453287
--------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------
| 0 | SELECT STATEMENT | | 1555K| 255M| | 78265 (1)| 00:15:40 | | |
| 1 | PARTITION RANGE SINGLE| | 1555K| 255M| | 78265 (1)| 00:15:40 | 6 | 6 |
| 2 | SORT ORDER BY | | 1555K| 255M| 607M| 78265 (1)| 00:15:40 | | |
|* 3 | TABLE ACCESS FULL | TB_RID_TRACE_2013 | 1555K| 255M| | 19128 (2)| 00:03:50 | 6 | 6 |
--------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T"."LOGIN_AT"<=TO_DATE('2013-06-15 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
SQL3,执行时间：24.492秒100条数据
SELECT * FROM (SELECT ROWNUM RN, T.* FROM TB_RID_TRACE_2013 T WHERE T.LOGIN_AT BETWEEN TO_DATE('2013-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE('2013-06-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS') ORDER BY T.LOGIN_AT DESC) T2 WHERE T2.RN BETWEEN 0 AND 100;
SQL3执行计划：
执行计划
----------------------
Plan hash value: 283752969
-----------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------
| 0 | SELECT STATEMENT | | 1555K| 504M| | 78265 (1)| 00:15:40 | | |
|* 1 | VIEW | | 1555K| 504M| | 78265 (1)| 00:15:40 | | |
| 2 | SORT ORDER BY | | 1555K| 255M| 607M| 78265 (1)| 00:15:40 | | |
| 3 | COUNT | | | | | | | | |
| 4 | PARTITION RANGE SINGLE| | 1555K| 255M| | 19128 (2)| 00:03:50 | 6 | 6 |
|* 5 | TABLE ACCESS FULL | TB_RID_TRACE_2013 | 1555K| 255M| | 19128 (2)| 00:03:50 | 6 | 6 |
-----------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T2"."RN"<=100 AND "T2"."RN">=0)
5 - filter("T"."LOGIN_AT"<=TO_DATE('2013-06-15 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) Oracle10g Order?by 速度慢 优化 索引
[解决办法]
TO_DATE('2013-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE('2013-06-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS');
为条件返回多少条记录？从查询计划上看返回1555K
真实有那么多吗？看看是否要收集一些统计信息？
[解决办法]
从执行计划大体可以得出：
1）5 - filter("T"."LOGIN_AT"<=TO_DATE('2013-06-15 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
==> 存在隐式类型转换！太槽糕了
2） 2
[解决办法]
TABLE ACCESS FULL
[解决办法]
TB_RID_TRACE_2013
==> CBO选择全表扫、索引在这里不起作用
3）既然分区了、干嘛还选择全局索引？
4）1 sorts (disk)
==> 都跑到磁盘上来排序了？pga里面sort_area多大？
5）索引是我后来追加的，当时表里已经有这么多数据了，我也REBUILD了啊！
==> 表和索引的统计信息重新收集了没？
