查询语句如下:
select count(1) from tb where ( (fq='80' and nid=35 or nid=-1) or (fq='80' and nid=34 or nid=-1) or (fq='80' and nid=31 or nid=-1) or (fq='80' and nid=33 or nid=-1) or (fq='80' and nid=37 or nid=-1) or (fq='80' and nid=36 or nid=-1) or (fq='80' and nid=32 or nid=-1) ) and (tm between to_date('2011-7-7 0:00:00 ','yyyy-mm-dd hh24:mi:ss') and to_date('2011-8-7 17:19:36','yyyy-mm-dd hh24:mi:ss') and dcid in (2) );
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 24 | 6277 (2)| 00:01:21 |
| 1 | SORT AGGREGATE | | 1 | 24 | | |
|* 2 | INDEX SKIP SCAN| IDX_1 | 1003K| 22M| 6748 (2)| 00:01:21 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TM">=TO_DATE('2011-07-07 00:00:00', 'yyyy-mm-dd
hh24:mi:ss') AND "DCID"=2 AND "TM"<=TO_DATE('2011-09-07
17:19:36', 'yyyy-mm-dd hh24:mi:ss'))
filter("NID"=2 AND ("FQ"=U'80' AND
"NID"=31 OR "FQ"=U'80' AND "NID"=32 OR
"FQ"=U'80' AND "NID"=33 OR "FQ"=U'80' AND
"NID"=34 OR "FQ"=U'80' AND "NID"=35 OR
"FQ"=U'80' AND "NID"=36 OR "FQ"=U'80' AND
"NID"=37 OR "NID"=(-1)))
------------------------------------------------------
1074 recursive calls
0 db block gets
30164 consistent gets
29924 physical reads
0 redo size
411 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
31 sorts (memory)
0 sorts (disk)
1 rows processed
此表数据量为400多万,索引为TB(TM, DCID, NID, FQ, NLE, NTYPEID, CE)
以上查询出的数据量为140万,查询时间用了30多秒
===============如下对另外一个表,结构基本上一致的表,差几个字段================
select count(*) from tb where ((SD=1 and TID=2) or
(SID=2 and TID=2)) and tm between to_date('2011-11-17 0:00:00 ','yyyy-mm-dd hh24:mi:ss')
and to_date('2011-12-17 9:56:48','yyyy-mm-dd hh24:mi:ss') and rid in (3,11,4,12,2,9,1,10,5,13,6,14,7,15,8,16)
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 4705 (6)| 00:00:57 | | |
| 1 | SORT AGGREGATE | | 1 | 17 | | | | |
| 2 | PARTITION RANGE SINGLE| | 1260K| 20M| 4705 (6)| 00:00:57 | 1 | 1 |
|* 3 | INDEX FAST FULL SCAN | PART_IDX | 1260K| 20M| 4705 (6)| 00:00:57 | 1 | 1
3 - filter("TM">=TO_DATE('2011-11-17 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
"TM"<=TO_DATE('2011-12-17 09:56:48', 'yyyy-mm-dd hh24:mi:ss') AND ("SID"=1 AND
"TID"=2 OR "SID"=2 AND "TID"=2) AND ("RID"=1 OR
"RID"=2 OR "RID"=3 OR "RID"=4 OR "RID"=5 OR
"RID"=6 OR "RID"=7 OR "RID"=8 OR "RID"=9 OR
"RID"=10 OR "RID"=11 OR "RID"=12 OR "RID"=13 OR
"RID"=14 OR "RID"=15 OR "RID"=16))
统计信息
----------------------------------------------------------
3077 recursive calls
0 db block gets
20942 consistent gets
20384 physical reads
0 redo size
410 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
56 sorts (memory)
0 sorts (disk)
1 rows processed
此表为分区表,数据量为3000万,但这个查询位于一个分区内,此分区数据量为500万左右,
以上查询查出的数据为130万左右,用时只有 3s 左右。
=====我看了看物理读也差不太多啊,处理的rows和bytes也没差太多,为什么查询用时却差这么多。上面用时长的好个查询已经走了INDEX SKIP SCAN,所以速度是不是这样最快了,期间改变索引,使用这个查询使用Index Fast Full Scan速度也很慢,是不是因为这个索引列数太多,导致索引扫描慢。
而且我看了看这两个索引的bytes和blocks也相差不是很多。目前这个索引的选择性不太高,尤其是有几列的选择性很低,但无论如何,走了全索引扫描,为什么会扫描这么慢,我怎么能知道时间消耗在什么地方了呢
请高人帮忙看下吧