oracle 分页:
-- 第一种 select * from (select aed.*, row_number() over(order by aed.created_date) rw from alarm_event ae, alarm_event_detail aed where ae.id = aed.id) where rw between 10 and 15; -- 第二种 select * from (select t.*, rownum rw from (select aed.* from alarm_event ae, alarm_event_detail aed where ae.id = aed.id order by aed.created_date) t where rownum < 16) where rw >= 10;
with 关键字 (解决子句中多次引用相同的查询块,或者解决多层嵌套查询时)下面中也有使用分析函数解决分组问题。
with t1 as (select ai.accountid, lb.businesssum, lb.customerid, lb.businesstype, lb.loanterm, bp.bar_code_no, count(lb.putoutno) over(partition by ai.accountid, lb.businesssum, lb.customerid, lb.businesstype, lb.loanterm, bp.bar_code_no) ct, row_number() over(partition by ai.accountid, lb.businesssum, lb.customerid, lb.businesstype, lb.loanterm, bp.bar_code_no order by lb.customerid) rw from cfss.loan_balance lb left join cfss.account_info ai on lb.putoutno = ai.objectid and ai.accountserialno = '1' left join cfss.business_putout bp on bp.serialno = lb.putoutserialno where lb.putoutdate >= '2018/06/28' and lb.businesstype = '1702-SB-01'), t2 as (select lb.customerid, lb.normalbalance, lb.overduebalance from cfss.loan_balance lb, t1 where lb.customerid = t1.customerid and t1.ct > 1 and rw = 1 and lb.businesstype = '1702-SB-01'), t3 as (select ci.certtype, ci.certid, sum(normalbalance) + sum(overduebalance) as balance from t2, cfss.customer_info ci where t2.customerid = ci.customerid group by ci.certtype, ci.certid) select t3.*, bc.creditsum, bc.customerid from cfss.business_credit bc, t3 where bc.certtype = t3.certtype and bc.certid = t3.certid and balance > bc.creditsum;
开始 搜集统计信息。(搜集完成统计信息,可以使执行计划更优)
begin
dbms_stats.gather_table_stats(ownname => 'CFSS',
TABNAME => 'BUSINESS_PUTOUT',
cascade => true);
end;
分析和开窗函数
使用绑定变量 VS 不使用绑定变量
https://blog.csdn.net/Alen_Liu_SZ/article/details/80527834
1 不使用绑定变量
1)创建表并测试
-
SQL> set timing on
-
SQL> create table t1( id int);
-
-
Table created.
-
-
Elapsed: 00:00:01.18
-
SQL> begin
-
2 for i in 1 .. 100000
-
3 loop
-
4 execute immediate 'insert into t1 values('||i|| ')';
-
5 end loop;
-
6 commit;
-
7 end;
-
8 /
-
-
PL/SQL procedure successfully completed.
-
-
Elapsed: 00:01:34.06
2)查看硬解析次数以及执行次数
-
SQL> set linesize 200
-
SQL> col sql_text for a40
-
SQL> col sql_id for a15
-
SQL> select sql_text,sql_id,executions,parse_calls from v$ sql
-
2 where sql_text like 'insert into t1 values%'
-
3 and rownum <= 10;
-
-
SQL_TEXT SQL_ID EXECUTIONS PARSE_CALLS
-
---------------------------------------- --------------- ---------- -----------
-
insert into t1 values( 99739) 4hwu069b7w058 1 1
-
insert into t1 values( 99795) c3wc2p1y440n6 1 1
-
insert into t1 values( 99600) ggxrmk462s138 1 1
-
insert into t1 values( 99610) 8m3jrkgshh1bj 1 1
-
insert into t1 values( 99857) a8wn2bw9cw1ck 1 1
-
insert into t1 values( 99809) 87uzu2cggw1hq 1 1
-
insert into t1 values( 99714) bddx0bx62n1qz 1 1
-
insert into t1 values( 99559) cbgyw5tudc1yq 1 1
-
insert into t1 values( 99745) 2xngw3w9b829n 1 1
-
insert into t1 values( 99826) 7s29ajyy3h2nh 1 1
-
-
10 rows selected.
-
-
Elapsed: 00: 00: 00.01
-
SQL> select count(*) from v$ sql
-
2 where sql_text like 'insert into t1 values%';
-
-
COUNT(*)
-
----------
-
461
-
-
Elapsed: 00:00:00.15
-
SQL>
思考:为何只有461次?
shared_pool大小有限,无法保存每条解析过的sql,会通过LRU算法踢出冷块。
2 使用绑定变量
1)清空缓存,测试
-
SQL> alter system flush shared_pool;
-
-
System altered.
-
-
Elapsed: 00:00:00.07
-
SQL> alter system flush buffer_cache;
-
-
System altered.
-
-
Elapsed: 00:00:00.46
-
SQL> begin
-
2 for i in 1 .. 100000
-
3 loop
-
4 execute immediate 'insert into t1 values (:X)' using i;
-
5 end loop;
-
6 commit;
-
7 end;
-
8 /
-
-
PL/SQL procedure successfully completed.
-
-
Elapsed: 00:00:12.06
2)查看硬解析次数以及执行次数
-
SQL> select sql_text,sql_id,executions,parse_calls from v$ sql
-
2 where sql_text like 'insert into t1 values%';
-
-
SQL_TEXT SQL_ID EXECUTIONS PARSE_CALLS
-
---------------------------------------- --------------- ---------- -----------
-
insert into t1 values (:X) d1f3fv8rt9j8t 100000 1
-
-
Elapsed: 00: 00: 00.11
-