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）创建表并测试
  1.  
    SQL> set timing on
  2.  
    SQL> create table t1(id int);
  3.  
     
  4.  
    Table created.
  5.  
     
  6.  
    Elapsed: 00:00:01.18
  7.  
    SQL> begin
  8.  
    2 for i in 1 .. 100000
  9.  
    3 loop
  10.  
    4 execute immediate 'insert into t1 values('||i||')';
  11.  
    5 end loop;
  12.  
    6 commit;
  13.  
    7 end;
  14.  
    8 /
  15.  
     
  16.  
    PL/SQL procedure successfully completed.
  17.  
     
  18.  
    Elapsed: 00:01:34.06
2）查看硬解析次数以及执行次数
  1.  
    SQL> set linesize 200
  2.  
    SQL> col sql_text for a40
  3.  
    SQL> col sql_id for a15
  4.  
    SQL> select sql_text,sql_id,executions,parse_calls from v$sql
  5.  
    2 where sql_text like 'insert into t1 values%'
  6.  
    3 and rownum <= 10;
  7.  
     
  8.  
    SQL_TEXT SQL_ID EXECUTIONS PARSE_CALLS
  9.  
    ---------------------------------------- --------------- ---------- -----------
  10.  
    insert into t1 values(99739) 4hwu069b7w058 1 1
  11.  
    insert into t1 values(99795) c3wc2p1y440n6 1 1
  12.  
    insert into t1 values(99600) ggxrmk462s138 1 1
  13.  
    insert into t1 values(99610) 8m3jrkgshh1bj 1 1
  14.  
    insert into t1 values(99857) a8wn2bw9cw1ck 1 1
  15.  
    insert into t1 values(99809) 87uzu2cggw1hq 1 1
  16.  
    insert into t1 values(99714) bddx0bx62n1qz 1 1
  17.  
    insert into t1 values(99559) cbgyw5tudc1yq 1 1
  18.  
    insert into t1 values(99745) 2xngw3w9b829n 1 1
  19.  
    insert into t1 values(99826) 7s29ajyy3h2nh 1 1
  20.  
     
  21.  
    10 rows selected.
  22.  
     
  23.  
    Elapsed: 00:00:00.01
  24.  
    SQL> select count(*) from v$sql
  25.  
    2 where sql_text like 'insert into t1 values%';
  26.  
     
  27.  
    COUNT(*)
  28.  
    ----------
  29.  
    461
  30.  
     
  31.  
    Elapsed: 00:00:00.15
  32.  
    SQL>
思考：为何只有461次？
shared_pool大小有限，无法保存每条解析过的sql，会通过LRU算法踢出冷块。
 
2 使用绑定变量
 
1）清空缓存，测试
  1.  
    SQL> alter system flush shared_pool;
  2.  
     
  3.  
    System altered.
  4.  
     
  5.  
    Elapsed: 00:00:00.07
  6.  
    SQL> alter system flush buffer_cache;
  7.  
     
  8.  
    System altered.
  9.  
     
  10.  
    Elapsed: 00:00:00.46
  11.  
    SQL> begin
  12.  
    2 for i in 1 .. 100000
  13.  
    3 loop
  14.  
    4 execute immediate 'insert into t1 values (:X)' using i;
  15.  
    5 end loop;
  16.  
    6 commit;
  17.  
    7 end;
  18.  
    8 /
  19.  
     
  20.  
    PL/SQL procedure successfully completed.
  21.  
     
  22.  
    Elapsed: 00:00:12.06
2）查看硬解析次数以及执行次数
  1.  
    SQL> select sql_text,sql_id,executions,parse_calls from v$sql
  2.  
    2 where sql_text like 'insert into t1 values%';
  3.  
     
  4.  
    SQL_TEXT SQL_ID EXECUTIONS PARSE_CALLS
  5.  
    ---------------------------------------- --------------- ---------- -----------
  6.  
    insert into t1 values (:X) d1f3fv8rt9j8t 100000 1
  7.  
     
  8.  
    Elapsed: 00:00:00.11

  9.