1- 在数据查询时面对大批量数据,如何关联查询。这个对查询效率影响很大。这里有两种方法提高查询效率。更新大批量数据时通过rowid查找
在单表查询时
可以增加并行度
select /*+ parallel(tcustomerinfo 8) */
c_custno
from tcustomerinfo ti
多表查询
select /*+ all_rows leading(hm) use_hash(h) no_merge(h) */
h.rowid ,hm.d_opendate
from (
select c_custno, d_opendate
from(
select /*+ parallel(TACCOINFO 8) */
c_custno,
d_opendate,
row_number() over(partition by c_custno order by d_opendate desc nulls last) rn
from TACCOINFO
where c_custtype = ''1'' )
where rn=1
) hm ,TORGEXINFO h
where hm.c_custno =h.c_custno order by h.rowid
2- 大数据量更新时选择rowid查找更新,这样可以直接找到物理地址。形式如下
declare
type refcursor is ref cursor;
v_cursor refcursor;
type array_rowid is table of rowid;
type array_value is table of varchar2(20);
a_rowid array_rowid := array_rowid();
a_value array_value := array_value();
begin
open v_cursor for 'select h.rowid,hm.d_lastconfirmdate from (
select c_custno,to_char(to_date(d_cdate),''yyyy-mm-dd'') d_lastconfirmdate from(
select /*+ parallel(tconfirm 8) */
c_custno,d_cdate,row_number() over(partition by c_custno order by d_cdate desc nulls last) rn from tconfirm
where c_businflag in (''02'', ''39'')
and c_custtype = ''1''
)
where rn=1) hm ,tperexp h where hm.c_custno =h.c_custno order by h.rowid';
loop
fetch v_cursor bulk collect into a_rowid,a_value limit 5000;
if a_rowid.count() > 0 then
forall i in a_rowid.first .. a_rowid.last
update tperexp set d_lastconfirmdate =a_value(i) where rowid = a_rowid(i);
end if;
exit when v_cursor%notfound;
end loop;
close v_cursor;
end;
/
3- 查询最大值如下查询方式要比直接使用max效率高
select c_custno, d_opendate
from(
select /*+ parallel(TACCOINFO 8) */
c_custno,
d_opendate,
row_number() over(partition by c_custno order by d_opendate desc nulls last) rn
from TACCOINFO
where c_custtype = ''1'' )
where rn=1