分页查询遇到的奇怪问题

leopard353 2008-12-02 10:33:55
本人某表数据300万条，分页语句如下:
select * from ( select * from FES.FES_CONTACTHISTORY this_ where
this_.PHONE1='111' //全表都为'111'
and
this_.PARTY_TYPE='6' //在0～9内随机生成
and
this_.VECTOR_ID='28' //在0～100内随机生成
and
this_.VERSION_ID='11' //在0～100内随机生成
and
this_.CREATEDBY IN //在0～120内随机生成
('1' ,'2' ,'3' ,'4' ,'5' ,'6' ,'7' ,'8' ,'9' ,'10' ,'11' ,'12' ,'13' ,'14' ,'15' ,'16' ,'17' ,'18' ,'19' ,'20' ,'21' ,'22' ,'23' ,'24' ,'25' ,'26' ,'27' ,'28' ,'29' ,'30' ,'31' ,'32' ,'33' ,'34' ,'35' ,'36' ,'37' ,'38' ,'39' ,'40' ,'41' ,'42' ,'43' ,'44' ,'45' ,'46' ,'47' ,'48' ,'49' ,'50' ,'51' ,'52' ,'53' ,'54' ,'55' ,'56' ,'57' ,'58' ,'59' ,'60' ,'61' ,'62' ,'63')
and this_.STARTTIME>=to_date
(
'2008-11-01,00:00:00', 'YYYY-MM-DD,HH24:MI:SS'
) and this_.STARTTIME<=to_date
(
'2008-12-04,23:59:59', 'YYYY-MM-DD,HH24:MI:SS'
)order by this_.STARTTIME DESC
)where rownum <= 200
以上出现的各个字段都已经建立索引。
奇怪之处：
当VECTOR_ID='28'，搜索时间大概70秒，所中数据14条
当VECTOR_ID='24'，搜索时间为2秒以内，所中数据16条
用其他某些字符进行搜索，时间要么大概2秒，要么就是大概70秒。而且所中数据量相差只在30条以内。
有无高人明白是怎么回事？？？
...全文
280 56 打赏 收藏 转发到动态 举报
写回复
用AI写文章
56 条回复
切换为时间正序
请发表友善的回复…
发表回复
無名VF 2008-12-06
  • 打赏
  • 举报
 回复
[Quote=引用 43 楼 vc555 的回复:]
引用 39 楼 codearts 的回复:
我认为应该扫描starttime, 因为他是以这个字段order by分页，后面加的2个version_id、vector_id只是辅助，并且我认为LZ表中的数据在ver…

我主要是看LZ前面贴的执行计划，LZ本来每个字段都有索引，但是执行计划中并没用到starttime索引，
而是出现了BITMAP CONVERSION TO ROWIDS，并且是在 version_id、vector_id上。所以据此判断要在version_id、vector_id上联合索引。
LZ这个表如果经常…
[/Quote]
...
yonghengdizhen 2008-12-05
  • 打赏
  • 举报
 回复
自动统计信息提供给Oracle的优化器作为计算执行计划成本的依据.
leopard353 2008-12-05
  • 打赏
  • 举报
 回复
大概知道点方向了。现在就是再也还原不了出现70秒的情况了。有谁能解释下自动统计信息有什么作用的？
leopard353 2008-12-04
  • 打赏
  • 举报
 回复

10:59:35 SQL> select * from ( select * from FES.FES_CONTACTHISTORY this_ where
11:01:32 2 this_.PHONE1='111'
11:01:32 3 and
11:01:32 4 this_.PARTY_TYPE='5'
11:01:32 5 and
11:01:32 6 this_.VECTOR_ID='9'
11:01:32 7 and
11:01:32 8 this_.VERSION_ID='23'
11:01:32 9 and
11:01:32 10 this_.CREATEDBY IN ('1' ,'2' ,'3' ,'4' ,'5' ,'6' ,'7' ,'8' ,'9' ,'10' ,'11' ,'12' ,'
13' ,'14' ,'15' ,'16' ,'17' ,'18' ,'19' ,'20' ,'21' ,'22' ,'23' ,'24' ,'25' ,'26' ,'27' ,'28' ,'29'
,'30' ,'31' ,'32' ,'33' ,'34' ,'35' ,'36' ,'37' ,'38' ,'39' ,'40' ,'41' ,'42' ,'43' ,'44' ,'45' ,'46
' ,'47' ,'48' ,'49' ,'50' ,'51' ,'52' ,'53' ,'54' ,'55' ,'56' ,'57' ,'58' ,'59' ,'60' ,'61' ,'62' ,'
63')
11:01:32 11 and
11:01:32 12 this_.STARTTIME>=to_date
11:01:32 13 (
11:01:32 14 '2008-11-05,03:39:55', 'YYYY-MM-DD,HH24:MI:SS'
11:01:32 15 ) and this_.STARTTIME<=to_date
11:01:32 16 (
11:01:32 17 '2008-12-05,23:17:17', 'YYYY-MM-DD,HH24:MI:SS'
11:01:32 18 )order by this_.STARTTIME DESC
11:01:32 19 )where rownum <= 200 ;

已选择12行。


执行计划
----------------------------------------------------------
Plan hash value: 2300518666

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

| Id | Operation | Name | Rows | Bytes
| Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | 14 | 150
K| 368 (2)| 00:00:05 |

|* 1 | COUNT STOPKEY | | |
| | |

| 2 | VIEW | | 14 | 150
K| 368 (2)| 00:00:05 |

|* 3 | SORT ORDER BY STOPKEY | | 14 | 14630
| 368 (2)| 00:00:05 |

|* 4 | TABLE ACCESS BY INDEX ROWID | FES_CONTACTHISTORY | 14 | 14630
| 367 (2)| 00:00:05 |

| 5 | BITMAP CONVERSION TO ROWIDS | | |
| | |

| 6 | BITMAP AND | | |
| | |

| 7 | BITMAP CONVERSION FROM ROWIDS| | |
| | |

|* 8 | INDEX RANGE SCAN | CH_VECID | 25721 |
| 91 (2)| 00:00:02 |

| 9 | BITMAP CONVERSION FROM ROWIDS| | |
| | |

|* 10 | INDEX RANGE SCAN | CH_VERSIONID | 25721 |
| 116 (1)| 00:00:02 |

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


Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM<=200)
3 - filter(ROWNUM<=200)
4 - filter("THIS_"."PARTY_TYPE"='5' AND "THIS_"."STARTTIME">=TIMESTAMP'2008-1
1-05 03:39:55'

AND ("THIS_"."CREATEDBY"='1' OR "THIS_"."CREATEDBY"='10' OR "THIS_
"."CREATEDBY"='11' OR

"THIS_"."CREATEDBY"='12' OR "THIS_"."CREATEDBY"='13' OR "THIS_"."C
REATEDBY"='14' OR

"THIS_"."CREATEDBY"='15' OR "THIS_"."CREATEDBY"='16' OR "THIS_"."C
REATEDBY"='17' OR

"THIS_"."CREATEDBY"='18' OR "THIS_"."CREATEDBY"='19' OR "THIS_"."C
REATEDBY"='2' OR

"THIS_"."CREATEDBY"='20' OR "THIS_"."CREATEDBY"='21' OR "THIS_"."C
REATEDBY"='22' OR

"THIS_"."CREATEDBY"='23' OR "THIS_"."CREATEDBY"='24' OR "THIS_"."C
REATEDBY"='25' OR

"THIS_"."CREATEDBY"='26' OR "THIS_"."CREATEDBY"='27' OR "THIS_"."C
REATEDBY"='28' OR

"THIS_"."CREATEDBY"='29' OR "THIS_"."CREATEDBY"='3' OR "THIS_"."CR
EATEDBY"='30' OR

"THIS_"."CREATEDBY"='31' OR "THIS_"."CREATEDBY"='32' OR "THIS_"."C
REATEDBY"='33' OR

"THIS_"."CREATEDBY"='34' OR "THIS_"."CREATEDBY"='35' OR "THIS_"."C
REATEDBY"='36' OR

"THIS_"."CREATEDBY"='37' OR "THIS_"."CREATEDBY"='38' OR "THIS_"."C
REATEDBY"='39' OR

"THIS_"."CREATEDBY"='4' OR "THIS_"."CREATEDBY"='40' OR "THIS_"."CR
EATEDBY"='41' OR

"THIS_"."CREATEDBY"='42' OR "THIS_"."CREATEDBY"='43' OR "THIS_"."C
REATEDBY"='44' OR

"THIS_"."CREATEDBY"='45' OR "THIS_"."CREATEDBY"='46' OR "THIS_"."C
REATEDBY"='47' OR

"THIS_"."CREATEDBY"='48' OR "THIS_"."CREATEDBY"='49' OR "THIS_"."C
REATEDBY"='5' OR

"THIS_"."CREATEDBY"='50' OR "THIS_"."CREATEDBY"='51' OR "THIS_"."C
REATEDBY"='52' OR

"THIS_"."CREATEDBY"='53' OR "THIS_"."CREATEDBY"='54' OR "THIS_"."C
REATEDBY"='55' OR

"THIS_"."CREATEDBY"='56' OR "THIS_"."CREATEDBY"='57' OR "THIS_"."C
REATEDBY"='58' OR

"THIS_"."CREATEDBY"='59' OR "THIS_"."CREATEDBY"='6' OR "THIS_"."CR
EATEDBY"='60' OR

"THIS_"."CREATEDBY"='61' OR "THIS_"."CREATEDBY"='62' OR "THIS_"."C
REATEDBY"='63' OR

"THIS_"."CREATEDBY"='7' OR "THIS_"."CREATEDBY"='8' OR "THIS_"."CRE
ATEDBY"='9') AND

"THIS_"."PHONE1"='111' AND "THIS_"."STARTTIME"<=TIMESTAMP'2008-12-
05 23:17:17')

8 - access("THIS_"."VECTOR_ID"='9')
10 - access("THIS_"."VERSION_ID"='23')


统计信息
----------------------------------------------------------
468 recursive calls
0 db block gets
592 consistent gets
452 physical reads
0 redo size
15028 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
12 rows processed
前两个是用了联合索引的，这个没有用联合索引的。从统计信息上看到底那个查询性能要好呢？
leopard353 2008-12-04
  • 打赏
  • 举报
 回复

09:39:38 SQL> select * from ( select /*+ index(this_, idx_test_123) */ * from FES.FES_CONTACTHISTORY
this_ where
09:41:17 2 this_.PHONE1='111'
09:41:17 3 and
09:41:17 4 this_.PARTY_TYPE='6'
09:41:17 5 and
09:41:17 6 this_.VECTOR_ID='58'
09:41:17 7 and
09:41:17 8 this_.VERSION_ID='74'
09:41:17 9 and
09:41:17 10 this_.CREATEDBY IN ('1' ,'2' ,'3' ,'4' ,'5' ,'6' ,'7' ,'8' ,'9' ,'10' ,'11' ,'12' ,'
13' ,'14' ,'15' ,'16' ,'17' ,'18' ,'19' ,'20' ,'21' ,'22' ,'23' ,'24' ,'25' ,'26' ,'27' ,'28' ,'29'
,'30' ,'31' ,'32' ,'33' ,'34' ,'35' ,'36' ,'37' ,'38' ,'39' ,'40' ,'41' ,'42' ,'43' ,'44' ,'45' ,'46
' ,'47' ,'48' ,'49' ,'50' ,'51' ,'52' ,'53' ,'54' ,'55' ,'56' ,'57' ,'58' ,'59' ,'60' ,'61' ,'62' ,'
63','64')
09:41:17 11 and
09:41:17 12 this_.STARTTIME>=to_date
09:41:17 13 (
09:41:17 14 '2008-11-05,01:50:30', 'YYYY-MM-DD,HH24:MI:SS'
09:41:17 15 ) and this_.STARTTIME<=to_date
09:41:17 16 (
09:41:17 17 '2008-12-05,23:59:50', 'YYYY-MM-DD,HH24:MI:SS'
09:41:17 18 )order by this_.STARTTIME DESC
09:41:17 19 )where rownum <= 200 ;

已选择8行。


执行计划
----------------------------------------------------------
Plan hash value: 1645311555

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

| Id | Operation | Name | Rows | Bytes | Co
st (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | 13 | 139K| 10
963 (1)| 00:02:12 |

|* 1 | COUNT STOPKEY | | | |
| |

| 2 | VIEW | | 13 | 139K| 10
963 (1)| 00:02:12 |

|* 3 | SORT ORDER BY STOPKEY | | 13 | 13585 | 10
963 (1)| 00:02:12 |

|* 4 | TABLE ACCESS BY INDEX ROWID| FES_CONTACTHISTORY | 13 | 13585 | 10
962 (1)| 00:02:12 |

|* 5 | INDEX RANGE SCAN | IDX_TEST_123 | 208 | | 10
753 (1)| 00:02:10 |

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


Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM<=200)
3 - filter(ROWNUM<=200)
4 - filter("THIS_"."PARTY_TYPE"='6' AND ("THIS_"."CREATEDBY"='1' OR
"THIS_"."CREATEDBY"='10' OR "THIS_"."CREATEDBY"='11' OR "THIS_"."C
REATEDBY"='12' OR

"THIS_"."CREATEDBY"='13' OR "THIS_"."CREATEDBY"='14' OR "THIS_"."C
REATEDBY"='15' OR

"THIS_"."CREATEDBY"='16' OR "THIS_"."CREATEDBY"='17' OR "THIS_"."C
REATEDBY"='18' OR

"THIS_"."CREATEDBY"='19' OR "THIS_"."CREATEDBY"='2' OR "THIS_"."CR
EATEDBY"='20' OR

"THIS_"."CREATEDBY"='21' OR "THIS_"."CREATEDBY"='22' OR "THIS_"."C
REATEDBY"='23' OR

"THIS_"."CREATEDBY"='24' OR "THIS_"."CREATEDBY"='25' OR "THIS_"."C
REATEDBY"='26' OR

"THIS_"."CREATEDBY"='27' OR "THIS_"."CREATEDBY"='28' OR "THIS_"."C
REATEDBY"='29' OR

"THIS_"."CREATEDBY"='3' OR "THIS_"."CREATEDBY"='30' OR "THIS_"."CR
EATEDBY"='31' OR

"THIS_"."CREATEDBY"='32' OR "THIS_"."CREATEDBY"='33' OR "THIS_"."C
REATEDBY"='34' OR

"THIS_"."CREATEDBY"='35' OR "THIS_"."CREATEDBY"='36' OR "THIS_"."C
REATEDBY"='37' OR

"THIS_"."CREATEDBY"='38' OR "THIS_"."CREATEDBY"='39' OR "THIS_"."C
REATEDBY"='4' OR

"THIS_"."CREATEDBY"='40' OR "THIS_"."CREATEDBY"='41' OR "THIS_"."C
REATEDBY"='42' OR

"THIS_"."CREATEDBY"='43' OR "THIS_"."CREATEDBY"='44' OR "THIS_"."C
REATEDBY"='45' OR

"THIS_"."CREATEDBY"='46' OR "THIS_"."CREATEDBY"='47' OR "THIS_"."C
REATEDBY"='48' OR

"THIS_"."CREATEDBY"='49' OR "THIS_"."CREATEDBY"='5' OR "THIS_"."CR
EATEDBY"='50' OR

"THIS_"."CREATEDBY"='51' OR "THIS_"."CREATEDBY"='52' OR "THIS_"."C
REATEDBY"='53' OR

"THIS_"."CREATEDBY"='54' OR "THIS_"."CREATEDBY"='55' OR "THIS_"."C
REATEDBY"='56' OR

"THIS_"."CREATEDBY"='57' OR "THIS_"."CREATEDBY"='58' OR "THIS_"."C
REATEDBY"='59' OR

"THIS_"."CREATEDBY"='6' OR "THIS_"."CREATEDBY"='60' OR "THIS_"."CR
EATEDBY"='61' OR

"THIS_"."CREATEDBY"='62' OR "THIS_"."CREATEDBY"='63' OR "THIS_"."C
REATEDBY"='64' OR

"THIS_"."CREATEDBY"='7' OR "THIS_"."CREATEDBY"='8' OR "THIS_"."CRE
ATEDBY"='9') AND

"THIS_"."PHONE1"='111')
5 - access("THIS_"."STARTTIME">=TIMESTAMP'2008-11-05 01:50:30' AND
"THIS_"."VECTOR_ID"='58' AND "THIS_"."VERSION_ID"='74' AND
"THIS_"."STARTTIME"<=TIMESTAMP'2008-12-05 23:59:50')
filter("THIS_"."VECTOR_ID"='58' AND "THIS_"."VERSION_ID"='74')


统计信息
----------------------------------------------------------
525 recursive calls
0 db block gets
10983 consistent gets
183 physical reads
0 redo size
14133 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
8 rows processed
leopard353 2008-12-04
  • 打赏
  • 举报
 回复

09:33:56 SQL> select * from ( select /*+ index(this_, idx_test_123) */ * from FES.FES_CONTACTHISTORY
this_ where
09:36:24 2 this_.PHONE1='111'
09:36:24 3 and
09:36:24 4 this_.PARTY_TYPE='8'
09:36:24 5 and
09:36:24 6 this_.VECTOR_ID='28'
09:36:24 7 and
09:36:24 8 this_.VERSION_ID='38'
09:36:24 9 and
09:36:24 10 this_.CREATEDBY IN ('1' ,'2' ,'3' ,'4' ,'5' ,'6' ,'7' ,'8' ,'9' ,'10' ,'11' ,'12' ,'
13' ,'14' ,'15' ,'16' ,'17' ,'18' ,'19' ,'20' ,'21' ,'22' ,'23' ,'24' ,'25' ,'26' ,'27' ,'28' ,'29'
,'30' ,'31' ,'32' ,'33' ,'34' ,'35' ,'36' ,'37' ,'38' ,'39' ,'40' ,'41' ,'42' ,'43' ,'44' ,'45' ,'46
' ,'47' ,'48' ,'49' ,'50' ,'51' ,'52' ,'53' ,'54' ,'55' ,'56' ,'57' ,'58' ,'59' ,'60' ,'61' ,'62' ,'
63','64')
09:36:25 11 and
09:36:25 12 this_.STARTTIME>=to_date
09:36:25 13 (
09:36:25 14 '2008-11-05,06:50:30', 'YYYY-MM-DD,HH24:MI:SS'
09:36:25 15 ) and this_.STARTTIME<=to_date
09:36:25 16 (
09:36:25 17 '2008-12-05,22:59:59', 'YYYY-MM-DD,HH24:MI:SS'
09:36:25 18 )order by this_.STARTTIME DESC
09:36:25 19 )where rownum <= 200 ;

已选择9行。


执行计划
----------------------------------------------------------
Plan hash value: 1645311555

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

| Id | Operation | Name | Rows | Bytes | Co
st (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | 14 | 150K| 10
917 (1)| 00:02:12 |

|* 1 | COUNT STOPKEY | | | |
| |

| 2 | VIEW | | 14 | 150K| 10
917 (1)| 00:02:12 |

|* 3 | SORT ORDER BY STOPKEY | | 14 | 14630 | 10
917 (1)| 00:02:12 |

|* 4 | TABLE ACCESS BY INDEX ROWID| FES_CONTACTHISTORY | 14 | 14630 | 10
916 (1)| 00:02:11 |

|* 5 | INDEX RANGE SCAN | IDX_TEST_123 | 247 | | 10
669 (1)| 00:02:09 |

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


Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM<=200)
3 - filter(ROWNUM<=200)
4 - filter("THIS_"."PARTY_TYPE"='8' AND ("THIS_"."CREATEDBY"='1' OR
"THIS_"."CREATEDBY"='10' OR "THIS_"."CREATEDBY"='11' OR "THIS_"."C
REATEDBY"='12' OR

"THIS_"."CREATEDBY"='13' OR "THIS_"."CREATEDBY"='14' OR "THIS_"."C
REATEDBY"='15' OR

"THIS_"."CREATEDBY"='16' OR "THIS_"."CREATEDBY"='17' OR "THIS_"."C
REATEDBY"='18' OR

"THIS_"."CREATEDBY"='19' OR "THIS_"."CREATEDBY"='2' OR "THIS_"."CR
EATEDBY"='20' OR

"THIS_"."CREATEDBY"='21' OR "THIS_"."CREATEDBY"='22' OR "THIS_"."C
REATEDBY"='23' OR

"THIS_"."CREATEDBY"='24' OR "THIS_"."CREATEDBY"='25' OR "THIS_"."C
REATEDBY"='26' OR

"THIS_"."CREATEDBY"='27' OR "THIS_"."CREATEDBY"='28' OR "THIS_"."C
REATEDBY"='29' OR

"THIS_"."CREATEDBY"='3' OR "THIS_"."CREATEDBY"='30' OR "THIS_"."CR
EATEDBY"='31' OR

"THIS_"."CREATEDBY"='32' OR "THIS_"."CREATEDBY"='33' OR "THIS_"."C
REATEDBY"='34' OR

"THIS_"."CREATEDBY"='35' OR "THIS_"."CREATEDBY"='36' OR "THIS_"."C
REATEDBY"='37' OR

"THIS_"."CREATEDBY"='38' OR "THIS_"."CREATEDBY"='39' OR "THIS_"."C
REATEDBY"='4' OR

"THIS_"."CREATEDBY"='40' OR "THIS_"."CREATEDBY"='41' OR "THIS_"."C
REATEDBY"='42' OR

"THIS_"."CREATEDBY"='43' OR "THIS_"."CREATEDBY"='44' OR "THIS_"."C
REATEDBY"='45' OR

"THIS_"."CREATEDBY"='46' OR "THIS_"."CREATEDBY"='47' OR "THIS_"."C
REATEDBY"='48' OR

"THIS_"."CREATEDBY"='49' OR "THIS_"."CREATEDBY"='5' OR "THIS_"."CR
EATEDBY"='50' OR

"THIS_"."CREATEDBY"='51' OR "THIS_"."CREATEDBY"='52' OR "THIS_"."C
REATEDBY"='53' OR

"THIS_"."CREATEDBY"='54' OR "THIS_"."CREATEDBY"='55' OR "THIS_"."C
REATEDBY"='56' OR

"THIS_"."CREATEDBY"='57' OR "THIS_"."CREATEDBY"='58' OR "THIS_"."C
REATEDBY"='59' OR

"THIS_"."CREATEDBY"='6' OR "THIS_"."CREATEDBY"='60' OR "THIS_"."CR
EATEDBY"='61' OR

"THIS_"."CREATEDBY"='62' OR "THIS_"."CREATEDBY"='63' OR "THIS_"."C
REATEDBY"='64' OR

"THIS_"."CREATEDBY"='7' OR "THIS_"."CREATEDBY"='8' OR "THIS_"."CRE
ATEDBY"='9') AND

"THIS_"."PHONE1"='111')
5 - access("THIS_"."STARTTIME">=TIMESTAMP'2008-11-05 06:50:30' AND
"THIS_"."VECTOR_ID"='28' AND "THIS_"."VERSION_ID"='38' AND
"THIS_"."STARTTIME"<=TIMESTAMP'2008-12-05 22:59:59')
filter("THIS_"."VECTOR_ID"='28' AND "THIS_"."VERSION_ID"='38')


统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
10733 consistent gets
129 physical reads
0 redo size
14365 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
9 rows processed
codearts 2008-12-04
  • 打赏
  • 举报
 回复
592 consistent gets
这个值小是表示这个执行计划好。

你都用同样的参数试试，别改参数啊。

hyee 2008-12-03
  • 打赏
  • 举报
 回复
在starttime建立索引，假设索引名为IX_XX_STARTTIME，如果是分区表，最好加上Local属性,相关SQL的部分改为：

SELECT *
FROM (SELECT /*+index(this_ IX_XX_STARTTIME)*/ *
FROM FES.FES_CONTACTHISTORY this_
......

作用是强制使用IX_XX_STARTTIME这个索引。当然，将该索引改为在 starttime,version_id,vector_id上建复合索引效果更好，主要看你这个索引的使用频率值不值得这么做。
codearts 2008-12-03
  • 打赏
  • 举报
 回复
[Quote=引用 32 楼 codearts 的回复:]
楼主每个字段建索引，不用把这些单个索引都删除，建一个组合索引


SQL codecreate index idx_test_123 on FES.FES_CONTACTHISTORY(STARTTIME, VECTOR_ID, VERSION_ID);

begin dbms_stats.gather_table_stats('FES', 'FES_CONTACTHISTORY', cascade=>true) end;

--执行上面的sql之后，再看看下面的执行计划

select * from ( select /*+ index(this_, idx_test_123) */ ...--这个是你的sql,加了hint
[/Quote]

为什么不试这个呢？
codearts 2008-12-03
  • 打赏
  • 举报
 回复
你没收集统计信息呀，你才看看，CBO认为表中只有81行的记录

10g自动统计，是由job来自动统计的，这有个时间点的
leopard353 2008-12-03
  • 打赏
  • 举报
 回复
Oracle 10G以上有自动统计信息的功能，到底有什么功能呢。如果我不手动统计信息。是不是优化器会依靠自动统计信息？
leopard353 2008-12-03
  • 打赏
  • 举报
 回复
偶而出现70秒的现象会不会是统计信息过期或者什么的导致优化器优化不能取得最好的执行计划呢？？？？
leopard353 2008-12-03
  • 打赏
  • 举报
 回复
各位大侠。到底该怎么建索引呢。刚刚我试了下。将全部索引删了，只留下starttime索引。居然执行全表扫描

18:30:32 SQL> select * from ( select * from FES.FES_CONTACTHISTORY this_ where
18:33:27 2 this_.PARTY_ID='V0000001'
18:33:27 3 and
18:33:27 4 this_.STARTTIME>=to_date
18:33:27 5 (
18:33:27 6 '2008-11-01,00:00:00', 'YYYY-MM-DD,HH24:MI:SS'
18:33:27 7 ) and this_.STARTTIME<=to_date
18:33:27 8 (
18:33:27 9 '2008-12-04,23:59:59', 'YYYY-MM-DD,HH24:MI:SS'
18:33:27 10 )order by this_.STARTTIME DESC
18:33:27 11 )where rownum <= 200 ;

未选定行


执行计划
----------------------------------------------------------
Plan hash value: 1483632479

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

| Id | Operation | Name | Rows | Bytes | Cost (%CP
U)| Time |

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

| 0 | SELECT STATEMENT | | 81 | 871K| 95720 (
3)| 00:19:09 |

|* 1 | COUNT STOPKEY | | | |
| |

| 2 | VIEW | | 81 | 871K| 95720 (
3)| 00:19:09 |

|* 3 | SORT ORDER BY STOPKEY| | 81 | 90477 | 95720 (
3)| 00:19:09 |

|* 4 | TABLE ACCESS FULL | FES_CONTACTHISTORY | 81 | 90477 | 95719 (
3)| 00:19:09 |

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


Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM<=200)
3 - filter(ROWNUM<=200)
4 - filter("THIS_"."PARTY_ID"='V0000001' AND
"THIS_"."STARTTIME">=TIMESTAMP'2008-11-01 00:00:00' AND
"THIS_"."STARTTIME"<=TIMESTAMP'2008-12-04 23:59:59')


统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
427921 consistent gets
403991 physical reads
0 redo size
11367 bytes sent via SQL*Net to client
374 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
0 rows processed
codearts 2008-12-03
  • 打赏
  • 举报
 回复
[Quote=引用 42 楼 leopard353 的回复:]
并且我认为LZ表中的数据在version_id,vector_id这2个字段上的分布是不均匀－》”分布不均匀“是什么意思呢？？？
[/Quote]

均匀，比方说吧， this_.VECTOR_ID='28' //在0～100内随机生成
vector_id这个字段，假设有100条记录，由于值是1..100, 那么平均下来值为1的记录有1条，值为2的记录有1条，值为3的记录有1条。。。。

现在不均匀，就是说是值为1的记录有90条，值为2的记录有9条，值有3的记录有1条，值为4的记录没有了。

4楼说的： 可能和列数据分布直方图有关,用字面常量进行这类查询,获得的执行计划不一定一样.
也就是这个意思。




vc555 2008-12-03
  • 打赏
  • 举报
 回复
[Quote=引用 39 楼 codearts 的回复:]
我认为应该扫描starttime, 因为他是以这个字段order by分页，后面加的2个version_id、vector_id只是辅助，并且我认为LZ表中的数据在ver…
[/Quote]
我主要是看LZ前面贴的执行计划，LZ本来每个字段都有索引，但是执行计划中并没用到starttime索引，
而是出现了BITMAP CONVERSION TO ROWIDS，并且是在 version_id、vector_id上。所以据此判断要在version_id、vector_id上联合索引。
LZ这个表如果经常更新数据，那么starttime便宜得更厉害。
leopard353 2008-12-03
  • 打赏
  • 举报
 回复
并且我认为LZ表中的数据在version_id,vector_id这2个字段上的分布是不均匀－》”分布不均匀“是什么意思呢？？？
leopard353 2008-12-03
  • 打赏
  • 举报
 回复
你也看见我之前贴的执行计划了。有时候它并不慢。我想找到真正的问题所在。现在马上试你所说的
codearts 2008-12-03
  • 打赏
  • 举报
 回复
主要是看这个：
28551 consistent gets

这个数值越小起好。

LZ为什么不试试的说的方法，并且把我的方法的执行计划帖出来？
codearts 2008-12-03
  • 打赏
  • 举报
 回复
[Quote=引用 36 楼 vc555 的回复:]
具体根据数据分布不同和你的查询条件的不同，如果建联合索引会有不确定影响的。

就你上面贴的最后那个执行计划来看，如果在(VECTOR_ID,VERSION_ID)上建索引，可能会降低逻辑读。
但是在starttime,version_id,vector_id这三个列上建联合索引，就不好说了。
[/Quote]

我认为应该扫描starttime, 因为他是以这个字段order by分页，后面加的2个version_id、vector_id只是辅助，并且我认为LZ表中的数据在version_id,vector_id这2个字段上的分布是不均匀的。
leopard353 2008-12-03
  • 打赏
  • 举报
 回复

14:41:58 SQL> select * from ( select * from FES.FES_CONTACTHISTORY this_ where
15:16:53 2 this_.PHONE1='111'
15:16:53 3 and
15:16:53 4 this_.PARTY_TYPE='6'
15:16:53 5 and
15:16:53 6 this_.VECTOR_ID='11'
15:16:53 7 and
15:16:53 8 this_.VERSION_ID='44'
15:16:53 9 and
15:16:53 10 this_.CREATEDBY IN ('1' ,'2' ,'3' ,'4' ,'5' ,'6' ,'7' ,'8' ,'9' ,'10' ,'11' ,'12' ,'
13' ,'14' ,'15' ,'16' ,'17' ,'18' ,'19' ,'20' ,'21' ,'22' ,'23' ,'24' ,'25' ,'26' ,'27' ,'28' ,'29'
,'30' ,'31' ,'32' ,'33' ,'34' ,'35' ,'36' ,'37' ,'38' ,'39' ,'40' ,'41' ,'42' ,'43' ,'44' ,'45' ,'46
' ,'47' ,'48' ,'49' ,'50' ,'51' ,'52' ,'53' ,'54' ,'55' ,'56' ,'57' ,'58' ,'59' ,'60' ,'61' ,'62' ,'
63')
15:16:54 11 and this_.STARTTIME>=to_date
15:16:54 12 (
15:16:54 13 '2008-11-01,00:00:00', 'YYYY-MM-DD,HH24:MI:SS'
15:16:54 14 ) and this_.STARTTIME<=to_date
15:16:54 15 (
15:16:54 16 '2008-12-04,23:59:59', 'YYYY-MM-DD,HH24:MI:SS'
15:16:54 17 )order by this_.STARTTIME DESC
15:16:54 18 )where rownum <= 200 ;

已选择7行。


执行计划
----------------------------------------------------------
Plan hash value: 584285649

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

| Id | Operation | Name | Rows | Bytes | Co
st (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | 1 | 11018 |
55 (2)| 00:00:01 |

|* 1 | COUNT STOPKEY | | | |
| |

| 2 | VIEW | | 1 | 11018 |
55 (2)| 00:00:01 |

|* 3 | SORT ORDER BY STOPKEY | | 1 | 11018 |
55 (2)| 00:00:01 |

|* 4 | TABLE ACCESS BY INDEX ROWID| FES_CONTACTHISTORY | 1 | 11018 |
54 (0)| 00:00:01 |

|* 5 | INDEX RANGE SCAN | CH_VECID | 15837 | |
50 (0)| 00:00:01 |

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


Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM<=200)
3 - filter(ROWNUM<=200)
4 - filter("THIS_"."PHONE1"='111' AND "THIS_"."PARTY_TYPE"='6' AND
"THIS_"."VERSION_ID"='44' AND ("THIS_"."CREATEDBY"='1' OR "THIS_".
"CREATEDBY"='10' OR

"THIS_"."CREATEDBY"='11' OR "THIS_"."CREATEDBY"='12' OR "THIS_"."C
REATEDBY"='13' OR

"THIS_"."CREATEDBY"='14' OR "THIS_"."CREATEDBY"='15' OR "THIS_"."C
REATEDBY"='16' OR

"THIS_"."CREATEDBY"='17' OR "THIS_"."CREATEDBY"='18' OR "THIS_"."C
REATEDBY"='19' OR

"THIS_"."CREATEDBY"='2' OR "THIS_"."CREATEDBY"='20' OR "THIS_"."CR
EATEDBY"='21' OR

"THIS_"."CREATEDBY"='22' OR "THIS_"."CREATEDBY"='23' OR "THIS_"."C
REATEDBY"='24' OR

"THIS_"."CREATEDBY"='25' OR "THIS_"."CREATEDBY"='26' OR "THIS_"."C
REATEDBY"='27' OR

"THIS_"."CREATEDBY"='28' OR "THIS_"."CREATEDBY"='29' OR "THIS_"."C
REATEDBY"='3' OR

"THIS_"."CREATEDBY"='30' OR "THIS_"."CREATEDBY"='31' OR "THIS_"."C
REATEDBY"='32' OR

"THIS_"."CREATEDBY"='33' OR "THIS_"."CREATEDBY"='34' OR "THIS_"."C
REATEDBY"='35' OR

"THIS_"."CREATEDBY"='36' OR "THIS_"."CREATEDBY"='37' OR "THIS_"."C
REATEDBY"='38' OR

"THIS_"."CREATEDBY"='39' OR "THIS_"."CREATEDBY"='4' OR "THIS_"."CR
EATEDBY"='40' OR

"THIS_"."CREATEDBY"='41' OR "THIS_"."CREATEDBY"='42' OR "THIS_"."C
REATEDBY"='43' OR

"THIS_"."CREATEDBY"='44' OR "THIS_"."CREATEDBY"='45' OR "THIS_"."C
REATEDBY"='46' OR

"THIS_"."CREATEDBY"='47' OR "THIS_"."CREATEDBY"='48' OR "THIS_"."C
REATEDBY"='49' OR

"THIS_"."CREATEDBY"='5' OR "THIS_"."CREATEDBY"='50' OR "THIS_"."CR
EATEDBY"='51' OR

"THIS_"."CREATEDBY"='52' OR "THIS_"."CREATEDBY"='53' OR "THIS_"."C
REATEDBY"='54' OR

"THIS_"."CREATEDBY"='55' OR "THIS_"."CREATEDBY"='56' OR "THIS_"."C
REATEDBY"='57' OR

"THIS_"."CREATEDBY"='58' OR "THIS_"."CREATEDBY"='59' OR "THIS_"."C
REATEDBY"='6' OR

"THIS_"."CREATEDBY"='60' OR "THIS_"."CREATEDBY"='61' OR "THIS_"."C
REATEDBY"='62' OR

"THIS_"."CREATEDBY"='63' OR "THIS_"."CREATEDBY"='7' OR "THIS_"."CR
EATEDBY"='8' OR

"THIS_"."CREATEDBY"='9') AND "THIS_"."STARTTIME">=TIMESTAMP'2008-1
1-01 00:00:00' AND

"THIS_"."STARTTIME"<=TIMESTAMP'2008-12-04 23:59:59')
5 - access("THIS_"."VECTOR_ID"='11')

Note
-----
- dynamic sampling used for this statement


统计信息
----------------------------------------------------------
3705 recursive calls
0 db block gets
28551 consistent gets
27026 physical reads
0 redo size
13912 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
7 rows processed
这是刚刚查询出来的执行计划。能否帮忙具体分析下？？非常地谢谢vc555
加载更多回复（36）
oracle实现一对多数据分页查询筛选示例代码
今天项目测试运行的时候，遇到了一个奇怪的问题，这个问题说起来按sql语法的话是没有错误的 但是呢按照我们的业务来做区分就有些逻辑上的错误了， 下面请听我慢慢道来，在数据库中有两个数据， 先来看下第一次sql是...
asp.net知识库
分页存储过程：排序反转分页法 优化后的通用分页存储过程 sql语句 一些Select检索高级用法 SQL server 2005中新增的排序函数及应用 根据基本表结构及其数据生成 INSERT ... 的 SQL 简便的MS SQL 数据库 表内容 脚本 ...
BAT公司万亿海量数据分页秒级查询落地方案实现
该课程系统最终是一个可用的分页秒级查询落地实现项目，包含解决方案以及实现，商业价值极高。大家可以根据自己企业的特定需求，稍加改造就可以用到自己企业的项目中去。   开发环境概述 开发工具：IDEA...
开始了!「Start!」-crx插件
一个新的开始页面，用于所有您的开始分页需要。 尝试开始！ 2-已发布新版本：...
自己动手写操作系统(含源代码).part2
书中讲解了大量在开发操作系统中需注意的细节问题，这些细节不仅能使读者更深刻地认识操作系统的核心原理，而且使整个开发过程少走弯路。本书分上下两篇，共11章。其中每一章都以前一章的工作成果为基础，实现一项新...
基础和管理

17,377

社区成员

95,128

社区内容

发帖
与我相关
我的任务
社区描述
Oracle 基础和管理
社区管理员
  • 基础和管理社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章