分页语句的深入研究

来源：互联网发布：js array push index 编辑：程序博客网时间：2024/05/21 09:54
select *  from (select                            a.cert_type,               a.cert_no,               a.cust_name,               e.oper_no,               to_char(c.vou_start_no + instr(c.vou_status, '0') - 1,                       'fm00000000') as vou,               e.bran_code          from comr_cifbinfo   a,               comc_box        b,               comr_clerk_vou  c,               auto_comc_clerk e         where a.cert_type = '1'           and substr(rtrim(a.cert_no), 18, 1) is not null           and a.cert_no not in               ('330721197211217006X', '1302011981101070315')           and a.valid_flag = '0'           and not exists         (select  * from comr_cifacctno ta where ta.cust_no = a.cust_no)           and c.vou_status like '%0%'           and b.bran_code = e.bran_code           and c.bran_code = e.bran_code           and a.bank_corp_code = c.bank_corp_code           and b.box_no = c.box_no           and b.oper_no = e.oper_no           and c.vou_status like '%0%'           and c.vou_type = '11'           and c.sub_vou_type = '0'           and e.post_no in ('12002', '12402')           and e.flag = '0'         order by vou asc) where rownum < 2;之前的SQL 由于有坑爹的c.vou_status like '%0%' 条件,导致最终分页SQL无法彻底优化，这次先把like搞掉SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID  gv9chqfu3770y, child number 0-------------------------------------select *   from (select                          a.cert_type,     a.cert_no,                a.cust_name,                e.oper_no,             to_char(c.vou_start_no + instr(c.vou_status, '0') - 1,                   'fm00000000') as vou,                e.bran_code      from comr_cifbinfo   a,                comc_box        b,       comr_clerk_vou  c,                auto_comc_clerk ewhere a.cert_type = '1'            and substr(rtrim(a.cert_no), 18, 1)is not null            and a.cert_no not in('330721197211217006X', '1302011981101070315')            anda.valid_flag = '0'            and not exists          (select  * fromcomr_cifacctno ta where ta.cust_no = a.cust_no)         /*   andc.vou_status like '%0%'*/            and b.bran_code = e.bran_code      and c.bran_code = e.bran_code            and a.bank_corp_code =c.bank_corp_code            and b.box_no = c.box_no            andb.oper_no = e.oper_no          /*  aPlan hash value: 2236318696-------------------------------------------------------------------------------------------------------------------| Id  | Operation                         | Name                | Starts | E-Rows | A-Rows |   A-Time   | Buffers |-------------------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT                  |                     |      1 |        |      1 |00:00:00.28 |   12288 ||*  1 |  COUNT STOPKEY                    |                     |      1 |        |      1 |00:00:00.28 |   12288 ||   2 |   VIEW                            |                     |      1 |      1 |      1 |00:00:00.28 |   12288 ||   3 |    NESTED LOOPS ANTI              |                     |      1 |      1 |      1 |00:00:00.28 |   12288 ||   4 |     NESTED LOOPS                  |                     |      1 |    103 |   3843 |00:00:00.26 |    7317 ||   5 |      NESTED LOOPS                 |                     |      1 |      1 |      1 |00:00:00.01 |     406 ||   6 |       NESTED LOOPS                |                     |      1 |    179 |    198 |00:00:00.01 |      89 ||   7 |        TABLE ACCESS BY INDEX ROWID| COMR_CLERK_VOU      |      1 |   2487 |      6 |00:00:00.01 |       9 ||*  8 |         INDEX RANGE SCAN          | IDX_COMR_CLERK_VOU  |      1 |     15 |      6 |00:00:00.01 |       3 ||*  9 |        TABLE ACCESS FULL          | COMC_BOX            |      6 |     12 |    198 |00:00:00.01 |      80 ||* 10 |       TABLE ACCESS BY INDEX ROWID | AUTO_COMC_CLERK     |    198 |      1 |      1 |00:00:00.01 |     317 ||* 11 |        INDEX UNIQUE SCAN          | AUTO_COMC_CLERK_INX |    198 |      1 |    162 |00:00:00.01 |     202 ||* 12 |      TABLE ACCESS BY INDEX ROWID  | COMR_CIFBINFO       |      1 |    100 |   3843 |00:00:00.26 |    6911 ||* 13 |       INDEX RANGE SCAN            | CIFB_IDX2           |      1 |    101 |   3843 |00:00:00.25 |    3138 ||* 14 |     INDEX RANGE SCAN              | CIFACCTNO_IDX2      |   3843 |   2110K|   3842 |00:00:00.01 |    4971 |-------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter(ROWNUM<2)   8 - access("C"."VOU_TYPE"=11 AND "C"."SUB_VOU_TYPE"=0)   9 - filter("B"."BOX_NO"="C"."BOX_NO")  10 - filter((INTERNAL_FUNCTION("E"."POST_NO") AND "E"."FLAG"='0' AND "B"."BRAN_CODE"="E"."BRAN_CODE" AND              "C"."BRAN_CODE"="E"."BRAN_CODE"))  11 - access("B"."OPER_NO"="E"."OPER_NO")  12 - filter("A"."VALID_FLAG"='0')  13 - access("A"."CERT_TYPE"='1' AND "A"."BANK_CORP_CODE"="C"."BANK_CORP_CODE")       filter((SUBSTR(RTRIM("A"."CERT_NO"),18,1) IS NOT NULL AND "A"."CERT_NO"<>'330721197211217006X' AND              "A"."CERT_NO"<>'1302011981101070315' AND "A"."BANK_CORP_CODE"="C"."BANK_CORP_CODE"))  14 - access("TA"."CUST_NO"="A"."CUST_NO")55 rows selected.create index IDX_COMR_CLERK_VOU on COMR_CLERK_VOU (VOU_TYPE, SUB_VOU_TYPE, TO_CHAR(VOU_START_NO+INSTR(VOU_STATUS,'0')-1,'fm00000000'))|*  8 |         INDEX RANGE SCAN          | IDX_COMR_CLERK_VOU  |      1 |     15 |      6 |00:00:00.01 |       3 |此时扫描了6条记录停止
0 0