SQL见下,没用分页时,在TOAD里执行计划都有用到索引,条件字段也都有索引。
1.当ROWNUM <= 20时,也是用到索引查询的,但一旦ROWNUM <= 40时,表payout_detail就报全表扫描了。
2.试着把内层的order by移出一层,无论怎么改都会用索引。怀疑是数据的问题,因在测试库上都没这问题,生产库频繁出现这问题,请大侠们帮分析下这问题?SELECT *
FROM (SELECT temp.*, ROWNUM num
FROM (select temp.wtid,
temp.wbid,
temp.PAYOUT_NUMBER,
temp.customer_number,
temp.processed_time,
temp.biz_type_code,
temp.general_biz_num,
temp.assigned_to,
pd.payout_notice_num,
p.party_name
from payout pt
join (select wt.id as wtid,
wb.id as wbid,
wb.PAYOUT_NUMBER as PAYOUT_NUMBER,
wb.customer_number as customer_number,
wt.processed_time as processed_time,
wb.biz_type_code,
wb.biz_event_number as general_biz_num,
wt.assigned_to
from task_inst wt
join task_info wb on wb.bp_task_id = wt.bp_task_id
where wb.biz_type_code = '7'
and wt.assigned_to = '442000025'
and wt.processed_time <
to_date('2012-11-26 23:59:59',
'yyyy-MM-dd HH24:mi:ss')
and wt.processed_time >=
to_date('2011-05-04 08:00:00',
'yyyy-MM-dd HH24:mi:ss')
order by wt.id, wb.id) temp on pt.payout_app_form_num =
temp.payout_number
join party p on pt.customer_num = p.customer_num
join payout_detail pd on pt.payout_app_form_num =
pd.payout_app_form_num) temp
where ROWNUM <= 20) WHERE num > 0
1.当ROWNUM <= 20时,也是用到索引查询的,但一旦ROWNUM <= 40时,表payout_detail就报全表扫描了。
2.试着把内层的order by移出一层,无论怎么改都会用索引。怀疑是数据的问题,因在测试库上都没这问题,生产库频繁出现这问题,请大侠们帮分析下这问题?SELECT *
FROM (SELECT temp.*, ROWNUM num
FROM (select temp.wtid,
temp.wbid,
temp.PAYOUT_NUMBER,
temp.customer_number,
temp.processed_time,
temp.biz_type_code,
temp.general_biz_num,
temp.assigned_to,
pd.payout_notice_num,
p.party_name
from payout pt
join (select wt.id as wtid,
wb.id as wbid,
wb.PAYOUT_NUMBER as PAYOUT_NUMBER,
wb.customer_number as customer_number,
wt.processed_time as processed_time,
wb.biz_type_code,
wb.biz_event_number as general_biz_num,
wt.assigned_to
from task_inst wt
join task_info wb on wb.bp_task_id = wt.bp_task_id
where wb.biz_type_code = '7'
and wt.assigned_to = '442000025'
and wt.processed_time <
to_date('2012-11-26 23:59:59',
'yyyy-MM-dd HH24:mi:ss')
and wt.processed_time >=
to_date('2011-05-04 08:00:00',
'yyyy-MM-dd HH24:mi:ss')
order by wt.id, wb.id) temp on pt.payout_app_form_num =
temp.payout_number
join party p on pt.customer_num = p.customer_num
join payout_detail pd on pt.payout_app_form_num =
pd.payout_app_form_num) temp
where ROWNUM <= 20) WHERE num > 0
dbms_stats.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'表名');
dbms_stats.GATHER_INDEX_STATS(OWNNAME=>USER,INDNAME=>'索引名');