select tall.*,tcount.t_count from (
select * from (select st.*,rownum rnum from (
--查找的原始语句
select t.*,l.lc,f.ftime,f.ctime from bus_records t left join kys.bus_records_lc l on l.sname=t.sst_name and l.zname=t.tst_name left join kys.whole_bus_fc f on f.bs_code=t.bus_code where carr_code='9017' and drive_date='20101111' order by IS_DO desc
) st) where rnum <=50 and rnum >0
) tall,(
--获取总行数的原始语句
select count(*) t_count from bus_records where carr_code='9017' and drive_date='20101111'
) tcount
select tall.*,tcount.t_count from (
select * from (select st.*,rownum rnum from (
--查找的原始语句
select t.*,l.lc,f.ftime,f.ctime from bus_records t left join kys.bus_records_lc l on l.sname=t.sst_name and l.zname=t.tst_name left join kys.whole_bus_fc f on f.bs_code=t.bus_code where drive_date='20101111' order by IS_DO desc
) st) where rnum <=50 and rnum >0
) tall,(
--获取总行数的原始语句
select count(*) t_count from bus_records where drive_date='20101111'
) tcount第一条与第二条中间就多了一个 carr_code='9017' ,但第二天速度很快,第一条却要10几秒,但是carr_code我是建了索引的,怎么会这么慢呢?
select * from (select st.*,rownum rnum from (
--查找的原始语句
select t.*,l.lc,f.ftime,f.ctime from bus_records t left join kys.bus_records_lc l on l.sname=t.sst_name and l.zname=t.tst_name left join kys.whole_bus_fc f on f.bs_code=t.bus_code where carr_code='9017' and drive_date='20101111' order by IS_DO desc
) st) where rnum <=50 and rnum >0
) tall,(
--获取总行数的原始语句
select count(*) t_count from bus_records where carr_code='9017' and drive_date='20101111'
) tcount
select tall.*,tcount.t_count from (
select * from (select st.*,rownum rnum from (
--查找的原始语句
select t.*,l.lc,f.ftime,f.ctime from bus_records t left join kys.bus_records_lc l on l.sname=t.sst_name and l.zname=t.tst_name left join kys.whole_bus_fc f on f.bs_code=t.bus_code where drive_date='20101111' order by IS_DO desc
) st) where rnum <=50 and rnum >0
) tall,(
--获取总行数的原始语句
select count(*) t_count from bus_records where drive_date='20101111'
) tcount第一条与第二条中间就多了一个 carr_code='9017' ,但第二天速度很快,第一条却要10几秒,但是carr_code我是建了索引的,怎么会这么慢呢?
解决方案 »
- sql语句优化讨论,主要针对oracle、mysql
- plsql developer连接不上windows 7 下安装的oracle11g
- 求助 ORA-12154: TNS:could not resolve the connect identifier specified
- 如何创建一个触发器来校验数据的连贯性?
- 在线高分求等答案
- Oracle备份与恢复案例
- 说有关于oracle开发的分析函数的汇总资料!想学习下!
- 急~~~ Oracle数据库的同步问题。
- 8i中的这个函数是什么?
- oracle里面能否识别001|002|003|.....这种类型呢?
- oracle 驱动寻找
- 请教SQL----时间可以固定一个时间段
-- 没有用条件(carr_code)的时候,查找估计是用全表扫描啦!-- 查看执行计划:
music@SZTYORA> set autotrace on;
music@SZTYORA> select count(logid) sums
2 from musicloguser_detail t1
3 WHERE t1.mn = 'sztysearchmusic' AND t1.k='lst' AND t1.v1 is not null
4 AND t1.timed>=trunc(sysdate-10)
5 AND t1.timed<trunc(sysdate)
6 and exists (select 1 from tj_hll_testmobilelist t2 where t2.mobile=t1.e1); SUMS
----------
264
执行计划
----------------------------------------------------------
Plan hash value: 497835998---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 60 | 31898 (3)| 00:06:23 | | |
| 1 | SORT AGGREGATE | | 1 | 60 | | | | |
|* 2 | FILTER | | | | | | | |
|* 3 | HASH JOIN RIGHT SEMI | | 47 | 2820 | 31898 (3)| 00:06:23 | | |
| 4 | TABLE ACCESS FULL | TJ_HLL_TESTMOBILELIST | 63 | 1701 | 3 (0)| 00:00:01 | | |
| 5 | PARTITION RANGE ITERATOR| | 3767 | 121K| 31895 (3)| 00:06:23 | KEY | KEY |
|* 6 | TABLE ACCESS FULL | MUSICLOGUSER_DETAIL | 3767 | 121K| 31895 (3)| 00:06:23 | KEY | KEY |
---------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 2 - filter(TRUNC(SYSDATE@!-10)<TRUNC(SYSDATE@!))
3 - access("T2"."MOBILE"="T1"."E1")
6 - filter("T1"."V1" IS NOT NULL AND "T1"."K"='lst' AND "T1"."MN"='sztysearchmusic' AND
"T1"."TIMED">=TRUNC(SYSDATE@!-10) AND "T1"."TIMED"<TRUNC(SYSDATE@!))Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processedmusic@SZTYORA>
也许第一个正因为用了carr_code索引,反倒慢了。
而第二个用了小表的索引。另外drive_date、IS_DO有没有索引啊?