sql1select MAIN_DOMAIN, ALEXA_RANKING, site_name, SITE_TYPE, CONTACT
from (select MAIN_DOMAIN,
ALEXA_RANKING,
site_name,
SITE_TYPE,
CONTACT,
spare_num,
row_number() over(partition by MAIN_DOMAIN order by MAIN_DOMAIN) rn
from site_alexa)
where rn = 1
and spare_num = 0
and ALEXA_RANKING >= '0'
and ALEXA_RANKING <= '1000000'
order by ALEXA_RANKING desc
sql2
select distinct MAIN_DOMAIN, ALEXA_RANKING, site_name, SITE_TYPE, CONTACT
from site_alexa
where 1 = 1
and spare_num = 0
and ALEXA_RANKING >= '0'
and ALEXA_RANKING <= '1000000'
order by ALEXA_RANKING desc
总记录:526130
索引:
DOMAIN 域名
MAIN_DOMAIN 主域名
我一直以为分析函数 row_number()会比distinct快,但这一两个sql ,distinct更快1秒多
sql1 要3秒多,sql21秒多
from (select MAIN_DOMAIN,
ALEXA_RANKING,
site_name,
SITE_TYPE,
CONTACT,
spare_num,
row_number() over(partition by MAIN_DOMAIN order by MAIN_DOMAIN) rn
from site_alexa)
where rn = 1
and spare_num = 0
and ALEXA_RANKING >= '0'
and ALEXA_RANKING <= '1000000'
order by ALEXA_RANKING desc
sql2
select distinct MAIN_DOMAIN, ALEXA_RANKING, site_name, SITE_TYPE, CONTACT
from site_alexa
where 1 = 1
and spare_num = 0
and ALEXA_RANKING >= '0'
and ALEXA_RANKING <= '1000000'
order by ALEXA_RANKING desc
总记录:526130
索引:
DOMAIN 域名
MAIN_DOMAIN 主域名
我一直以为分析函数 row_number()会比distinct快,但这一两个sql ,distinct更快1秒多
sql1 要3秒多,sql21秒多
sql1用了个inline view。
SORT ORDER BY
WINDOW NOSORT
VIEW
WINDOW SORT PUSHED RANK
TABLE ACCESS FULL sql2执行计划
SELECT STATEMENT, GOAL = CHOOSE Optimizer=CHOOSE
SORT UNIQUE
TABLE ACCESS FULL
SORT UNIQUE
TABLE ACCESS FULL
alter system flush buffer_cache;
alter system flush shared_pool;清除缓存之后再执行sql2统计看看
Oracle Sql 语句性能调优
怎么用sql语句查share_pool的sql?
2.大家讨论来讨论去,不知道注意到没有,这两条sql并不等价,那么执行快慢讨论还有什么意义呢
是的,再加上这点,没有统计信息,也就基于Rule叻
怎么收集统计信息,能给个deom吗
找到个例子
但没找到用法
我自己这下试的,不成功!
选执行这语句:
begin
dbms_stats.gather_table_stats(ownname => kailiao,
tabname => 'spd_domain',
method_opt => 'for all indexed columns',
cascade => TRUE);
end;再执行:select MAIN_DOMAIN, ALEXA_RANKING, site_name, SITE_TYPE, CONTACT
from (select MAIN_DOMAIN,
ALEXA_RANKING,
site_name,
SITE_TYPE,
CONTACT,
spare_num,
row_number() over(partition by MAIN_DOMAIN order by MAIN_DOMAIN) rn
from site_alexa)
where rn = 1
and spare_num = 0
and ALEXA_RANKING >= '0'
and ALEXA_RANKING <= '1000000'
order by ALEXA_RANKING desc
最后执行:SELECT endpoint_number, endpoint_value, ENDPOINT_ACTUAL_VALUE
FROM (SELECT endpoint_number, endpoint_value, ENDPOINT_ACTUAL_VALUE
FROM USER_HISTOGRAMS
WHERE table_name = 'spd_domain'
and column_name = 'kailiao'
ORDER BY endpoint_number)
WHERE rownum <= 20;
但查询出来的结果没有记录我想查上面的查询sql语句 的统计信息, 各位 前辈要怎么用
and ALEXA_RANKING <= '1000000'字符串的<= >=是很费时间的,楼主这里为什么不转换成数字呢?