sql1 select distinct MAIN_DOMAIN,
DOMAIN,
ALEXA_RANKING,
site_name,
SITE_TYPE,
CONTACT
from site_alexa
where 1 = 1
and ALEXA_RANKING >= 0
and ALEXA_RANKING <= 1313465
and spare_num =-1
order by ALEXA_RANKING descsql 1执行计划: SELECT STATEMENT, GOAL = CHOOSE Optimizer=CHOOSE
SORT UNIQUE
TABLE ACCESS BY INDEX ROWID
INDEX RANGE SCAN 要2.3或2.4秒的时间
sql2
select distinct MAIN_DOMAIN,
DOMAIN,
ALEXA_RANKING,
site_name,
SITE_TYPE,
CONTACT
from site_alexa
where 1 = 1
and ALEXA_RANKING >= '0'
and ALEXA_RANKING <= '8000000'
and spare_num = '0'
order by ALEXA_RANKING desc
sql2执行计划:
SELECT STATEMENT, GOAL = CHOOSE Optimizer=CHOOSE
SORT UNIQUE
TABLE ACCESS BY INDEX ROWID
INDEX RANGE SCAN
要4秒多的时间
sql3
select distinct MAIN_DOMAIN,
DOMAIN,
ALEXA_RANKING,
site_name,
SITE_TYPE,
CONTACT
from site_alexa
where 1 = 1
and ALEXA_RANKING >=to_number( '0')
and ALEXA_RANKING <= to_number('8000000')
and spare_num = to_number('0')
order by ALEXA_RANKING descsql3执行计划:SELECT STATEMENT, GOAL = CHOOSE Optimizer=CHOOSE
SORT UNIQUE
TABLE ACCESS BY INDEX ROWID
INDEX RANGE SCAN 要5秒多
表才526130条记录,按理不应该这么慢啊,大家帮忙分析下表索引 :ALEXA_RANKING,DOMAIN,MAIN_DOMAIN
因为 spare_num 只有0跟-1,我就没建索引了where 的条件中值,程序传给我的是String类型,且没办法改,而我表里的类型是number
还有一点不明白:ALEXA_RANKING >= '0'
ALEXA_RANKING 是number类型可以与VARCHAR2(200)比较大小,这是不是只比对应的ascii值 啊????? 为什么会用distict 是因为我不会它,速度更慢,我本地测试划这样的
请看 http://topic.csdn.net/u/20090511/18/38ead906-4221-4111-8617-3612472d889a.html
DOMAIN,
ALEXA_RANKING,
site_name,
SITE_TYPE,
CONTACT
from site_alexa
where 1 = 1
and ALEXA_RANKING >= 0
and ALEXA_RANKING <= 1313465
and spare_num =-1
order by ALEXA_RANKING descsql 1执行计划: SELECT STATEMENT, GOAL = CHOOSE Optimizer=CHOOSE
SORT UNIQUE
TABLE ACCESS BY INDEX ROWID
INDEX RANGE SCAN 要2.3或2.4秒的时间
sql2
select distinct MAIN_DOMAIN,
DOMAIN,
ALEXA_RANKING,
site_name,
SITE_TYPE,
CONTACT
from site_alexa
where 1 = 1
and ALEXA_RANKING >= '0'
and ALEXA_RANKING <= '8000000'
and spare_num = '0'
order by ALEXA_RANKING desc
sql2执行计划:
SELECT STATEMENT, GOAL = CHOOSE Optimizer=CHOOSE
SORT UNIQUE
TABLE ACCESS BY INDEX ROWID
INDEX RANGE SCAN
要4秒多的时间
sql3
select distinct MAIN_DOMAIN,
DOMAIN,
ALEXA_RANKING,
site_name,
SITE_TYPE,
CONTACT
from site_alexa
where 1 = 1
and ALEXA_RANKING >=to_number( '0')
and ALEXA_RANKING <= to_number('8000000')
and spare_num = to_number('0')
order by ALEXA_RANKING descsql3执行计划:SELECT STATEMENT, GOAL = CHOOSE Optimizer=CHOOSE
SORT UNIQUE
TABLE ACCESS BY INDEX ROWID
INDEX RANGE SCAN 要5秒多
表才526130条记录,按理不应该这么慢啊,大家帮忙分析下表索引 :ALEXA_RANKING,DOMAIN,MAIN_DOMAIN
因为 spare_num 只有0跟-1,我就没建索引了where 的条件中值,程序传给我的是String类型,且没办法改,而我表里的类型是number
还有一点不明白:ALEXA_RANKING >= '0'
ALEXA_RANKING 是number类型可以与VARCHAR2(200)比较大小,这是不是只比对应的ascii值 啊????? 为什么会用distict 是因为我不会它,速度更慢,我本地测试划这样的
请看 http://topic.csdn.net/u/20090511/18/38ead906-4221-4111-8617-3612472d889a.html
2.and ALEXA_RANKING >=to_number( '0')
and ALEXA_RANKING <= to_number('8000000')
改成 ALEXA_RANKING between (to_number( '0'),to_number('8000000')
DOMAIN,
ALEXA_RANKING,
site_name,
SITE_TYPE,
CONTACT
from site_alexa
where 1 = 1
and ALEXA_RANKING >= 0
and ALEXA_RANKING <= 1313465
and spare_num =-1
order by ALEXA_RANKING desc
试试
select distinct MAIN_DOMAIN,
DOMAIN,
ALEXA_RANKING,
site_name,
SITE_TYPE,
CONTACT
from site_alexa
where (ALEXA_RANKING >= 0
and ALEXA_RANKING <= 1313465)
and spare_num =-1
order by ALEXA_RANKING desc另外,distinct 的结果是已经排好序的,考虑用一下,不要再 order by 了。
为什么and 放在一起,就快这么多
spare_num可以建个位图索引如果你要取的数据量占总的数据量的占比比较高的话,那索引基本没用
另外你有distinct也是影响效率的
你的sql把spare_num=0就慢了,是因为spare_num=-1的很少,才会这么快
呵呵,
如果建位图索引的话,我还要查询修改,会不会导到锁很多记录啊(在网上说看到位的)
2 ALEXA_RANKING为索引字段,程序中采用绑定变量传参;
3 spare_num字段中值为0在表中所分布记录多的话,不采用绑定变量,如果分布少,可采用绑定变量,同理spare_num 值为-1也如此;
4 去掉1=1;
5 从执行计划中可推断出字段ALEXA_RANKING为唯一索引字段,即此值在表中不会有相同记录,故不会有重复记录存在,去掉distinct,distinct对性能也会有影响
更改后:
select MAIN_DOMAIN,
DOMAIN,
ALEXA_RANKING,
site_name,
SITE_TYPE,
CONTACT
from site_alexa
where ALEXA_RANKING >= :num1
and ALEXA_RANKING <= :num2
and spare_num = -1
order by ALEXA_RANKING desc
and ALEXA_RANKING <= '1000000'字符串的<= >=是很费时间的,楼主这里为什么不转换成数字呢?
sql3比sql2慢得比较没有道理,理论上讲应该比sql2快的。