【求助】【急】关于Oracle10g里的Order by速度慢的问题

落魄祥子 2013-07-02 07:31:21
表结构:
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'))


跪求各位大神指定迷津。。。
...全文
347 点赞 收藏 1
写回复
1 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
sych888 2013-07-02
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 真实有那么多吗?看看是否要收集一些统计信息?
回复
相关推荐
发帖
Oracle
创建于2007-09-28

1.6w+

社区成员

Oracle开发相关技术讨论
申请成为版主
帖子事件
创建了帖子
2013-07-02 07:31
社区公告
暂无公告