Use with as to optimize SQL again

A buddy in Shanghai asked me that I had not finished running an SQL statement for four hours. I was so sorry, I was asked to optimize it. I really admire him. If I ran for a few minutes, I wouldn't be able to stand it anymore.

The SQL statement and execution plan are as follows:

-- SQL id: 1qbbw3th4x8yc SELECT "VOUCHER ". FID "ID", "ENTRIES ". FID "ENTRIES. ID "," ENTRIES ". FEntryDC "ENTRIES. ENTRYDC "," ACCOUNT ". FID "ACCOUNT. ID "," ENTRIES ". FCurrencyID "CURRENCY. ID "," PERIOD ". FNumber "PERIOD. NUMBER "," ENTRIES ". FSeq "ENTRIES. SEQ "," ENTRIES ". FLocalExchangeRate "LOCALEXCHANGERATE", "ENTRIES ". FReportingExchangeRate "REPORTINGEXCHANGERATE", "ENTRIES ". FMeasureUnitID "ENTRYMEASUREUNIT. ID "," ASSISTRECORDS ". FID "ASSISTRECORDS. ID "," ASSISTRECORDS ". FSeq "ASSISTRECORDS. SEQ ", case when (" ACCOUNT ". fcaa is null) AND ("ACCOUNT ". fhasUserProperty <> 1) THEN "ENTRIES ". FOriginalAmount ELSE "ASSISTRECORDS ". FOriginalAmount END "ASSISTRECORDS. ORIGINALAMOUNT ", case when (" ACCOUNT ". fcaa is null) AND ("ACCOUNT ". fhasUserProperty <> 1) THEN "ENTRIES ". FLocalAmount ELSE "ASSISTRECORDS ". FLocalAmount END "ASSISTRECORDS. LOCALAMOUNT ", case when (" ACCOUNT ". fcaa is null) AND ("ACCOUNT ". fhasUserProperty <> 1) THEN "ENTRIES ". FReportingAmount ELSE "ASSISTRECORDS ". FReportingAmount END "ASSISTRECORDS. REPORTINGAMOUNT ", case when (" ACCOUNT ". fcaa is null) AND ("ACCOUNT ". fhasUserProperty <> 1) THEN "ENTRIES ". FQuantity ELSE "ASSISTRECORDS ". FQuantity END "ASSISTRECORDS. QUANTITY ", case when (" ACCOUNT ". fcaa is null) AND ("ACCOUNT ". fhasUserProperty <> 1) THEN "ENTRIES ". FStandardQuantity ELSE "ASSISTRECORDS ". FStandardQuantity END "ASSISTRECORDS. STANDARDQTY ", case when (" ACCOUNT ". fcaa is null) AND ("ACCOUNT ". fhasUserProperty <> 1) THEN "ENTRIES ". FPrice ELSE "ASSISTRECORDS ". FPrice END "ASSISTRECORDS. PRICE ", case when (" ACCOUNT ". fcaa is null) then null else "ASSISTRECORDS ". FAssGrpID END "ASSGRP. ID "FROM T_GL_Voucher" VOUCHER "left outer join T_BD_Period" PERIOD "ON" VOUCHER ". FPeriodID = "PERIOD ". fid inner join T_GL_VoucherEntry "ENTRIES" ON "VOUCHER ". FID = "ENTRIES ". FBillID inner join T_BD_AccountView "ACCOUNT" ON "ENTRIES ". FAccountID = "ACCOUNT ". fid left outer join T_GL_VoucherAssistRecord "ASSISTRECORDS" ON "ENTRIES ". FID = "ASSISTRECORDS ". FEntryID WHERE "VOUCHER ". fid in (SELECT "VOUCHER ". FID "ID" FROM T_GL_Voucher "VOUCHER" inner join T_GL_VoucherEntry "ENTRIES" ON "VOUCHER ". FID = "ENTRIES ". FBillID inner join T_BD_AccountView "ACCOUNT" ON "ENTRIES ". FAccountID = "ACCOUNT ". fid inner join t_bd_accountview pav on (INSTR ("ACCOUNT ". flongnumber, pav. flongnumber) = 1 AND pav. faccounttableid = "ACCOUNT ". faccounttableid) AND pav. fcompanyid = "ACCOUNT ". fcompanyid) WHERE ("VOUCHER ". FCompanyID IN ('fssf82rrskexm3kkn1d0tmznrtq = ') AND ("VOUCHER ". FBizStatus IN (5) AND ("VOUCHER ". FPeriodID IN ('+ wQxkBFVRiKnV7OniceMDoI4jEw =') AND "ENTRIES ". FCurrencyID = 'dfd38d11-00fd-1000-e000-1ebdc0a8100dDEB58FDC ') AND (pav. fid in ('vpikexlrxiymb41vsvvzj2pmcy = ') order by "ID" ASC, "ENTRIES. SEQ "ASC," ASSISTRECORDS. SEQ "ASC -- execution plan scheduler | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | scheduler | 0 | select statement | 13 | 5733 | 486 (1) | 1 | sort order by | 13 | 5733 | 486 (1) | 2 | VIEW | VM_NWVW_2 | 13 | 5733 | 486 (1) | 3 | hash unique | 13 | 11115 | 486 (1) | 4 | nested loops outer | 13 | 11115 | 485 (1) | 5 | nested loops | 9 | 6606 | 471 (1) | 6 | nested loops | 9 | 6057 | 467 (1) | 7 | merge join outer | 1 | 473 | 459 (1) | 8 | hash join | 1 | 427 | 458 (1) | 9 | nested loops | 10 | nested loops | 258 | 83850 | 390 (0) | 11 | nested loops | 6 | 1332 | 3 (0) | 12 | table access by index rowid | T_BD_ACCOUNTVIEW | 1 | 111 | 2 (0) | 13 | index unique scan | PK_BD_ACCOUNTVIEW | 1 | 1 (0) | 14 | index range scan | IX_BD_ACTCOMLNUM | 6 | 666 | 1 (0) | 15 | index range scan | IX_GL_VCHAACCT | 489 | 1 (0) | 16 | table access by index rowid | T_GL_VOUCHERENTRY | 42 | 4326 | 65 (0) | 17 | index range scan | IX_GL_VCH_11 | 7536 | 750K | 68 (0) | 18 | buffer sort | 1 | 46 | 391 (0) | 19 | index range scan | IX_PERIOD_ENC | 1 | 46 | 1 (0) | 20 | table access by index rowid | T_GL_VOUCHERENTRY | 17 | 3400 | 8 (0) | 21 | index range scan | IX_GL_VCHENTRYFQ1 | 17 | 1 (0) | 22 | table access by index rowid | T_BD_ACCOUNTVIEW | 1 | 61 | 1 (0) | 23 | index unique scan | PK_BD_ACCOUNTVIEW | 1 | 1 (0) | 24 | table access by index rowid | T_GL_VOUCHERASSISTRECORD | 1 | 121 | 2 (0) | 25 | index range scan | IX_GL_VCHASSREC_11 | 2 | 1 (0) | notice Note ------'Plan _ table' is old version

I rely on plan_table is old version... I usually ignore this kind of execution plan, but this buddy has a good relationship. Forget it.

Then I will not optimize the execution plan and analyze the SQL statement optimization directly. This SQL statement is quite simple. There are several tables following from, where has an in statement, and finally an order by statement.

Ask him how many records are returned after in

17:11:46
Oh
How long does the where in run?
Shanghai-coffee fans 17:12:40
Soon
23 s 16880 rows

The sizes of other tables are as follows:

If you see this, you will know how to optimize it. If you have been running for four hours before, I will optimize it to 1 minute.

If in returns only 16880 data records, you can use in as the driving table to drive T_GL_Voucher.

The idea is the same as this blog http://blog.csdn.net/robinson1988/article/details/10551467, I rely on a sb in the comment

Because in is a little complicated and hint is not easy to use, I am lazy and it is rare to do it. I directly use the following SQL Optimization

With x as (SELECT/* + materialize */"VOUCHER ". FID "ID" FROM T_GL_Voucher "VOUCHER" inner join T_GL_VoucherEntry "ENTRIES" ON "VOUCHER ". FID = "ENTRIES ". FBillID inner join T_BD_AccountView "ACCOUNT" ON "ENTRIES ". FAccountID = "ACCOUNT ". fid inner join t_bd_accountview pav on (INSTR ("ACCOUNT ". flongnumber, pav. flongnumber) = 1 AND pav. faccounttableid = "ACCOUNT ". faccounttableid) AND pav. fcompanyid = "ACCOUNT ". fcompanyid) WHERE ("VOUCHER ". FCompanyID IN ('fssf82rrskexm3kkn1d0tmznrtq = ') AND ("VOUCHER ". FBizStatus IN (5) AND ("VOUCHER ". FPeriodID IN ('+ wQxkBFVRiKnV7OniceMDoI4jEw =') AND "ENTRIES ". FCurrencyID = 'dfd38d11-00fd-1000-e000-1ebdc0a8100dDEB58FDC ') AND (pav. fid in ('vpikexlrxiymb41vsvvzj2pmcy = ') SELECT "VOUCHER ". FID "ID", "ENTRIES ". FID "ENTRIES. ID "," ENTRIES ". FEntryDC "ENTRIES. ENTRYDC "," ACCOUNT ". FID "ACCOUNT. ID "," ENTRIES ". FCurrencyID "CURRENCY. ID "," PERIOD ". FNumber "PERIOD. NUMBER "," ENTRIES ". FSeq "ENTRIES. SEQ "," ENTRIES ". FLocalExchangeRate "LOCALEXCHANGERATE", "ENTRIES ". FReportingExchangeRate "REPORTINGEXCHANGERATE", "ENTRIES ". FMeasureUnitID "ENTRYMEASUREUNIT. ID "," ASSISTRECORDS ". FID "ASSISTRECORDS. ID "," ASSISTRECORDS ". FSeq "ASSISTRECORDS. SEQ ", case when (" ACCOUNT ". fcaa is null) AND ("ACCOUNT ". fhasUserProperty <> 1) THEN "ENTRIES ". FOriginalAmount ELSE "ASSISTRECORDS ". FOriginalAmount END "ASSISTRECORDS. ORIGINALAMOUNT ", case when (" ACCOUNT ". fcaa is null) AND ("ACCOUNT ". fhasUserProperty <> 1) THEN "ENTRIES ". FLocalAmount ELSE "ASSISTRECORDS ". FLocalAmount END "ASSISTRECORDS. LOCALAMOUNT ", case when (" ACCOUNT ". fcaa is null) AND ("ACCOUNT ". fhasUserProperty <> 1) THEN "ENTRIES ". FReportingAmount ELSE "ASSISTRECORDS ". FReportingAmount END "ASSISTRECORDS. REPORTINGAMOUNT ", case when (" ACCOUNT ". fcaa is null) AND ("ACCOUNT ". fhasUserProperty <> 1) THEN "ENTRIES ". FQuantity ELSE "ASSISTRECORDS ". FQuantity END "ASSISTRECORDS. QUANTITY ", case when (" ACCOUNT ". fcaa is null) AND ("ACCOUNT ". fhasUserProperty <> 1) THEN "ENTRIES ". FStandardQuantity ELSE "ASSISTRECORDS ". FStandardQuantity END "ASSISTRECORDS. STANDARDQTY ", case when (" ACCOUNT ". fcaa is null) AND ("ACCOUNT ". fhasUserProperty <> 1) THEN "ENTRIES ". FPrice ELSE "ASSISTRECORDS ". FPrice END "ASSISTRECORDS. PRICE ", case when (" ACCOUNT ". fcaa is null) then null else "ASSISTRECORDS ". FAssGrpID END "ASSGRP. ID "FROM T_GL_Voucher" VOUCHER "left outer join T_BD_Period" PERIOD "ON" VOUCHER ". FPeriodID = "PERIOD ". fid inner join T_GL_VoucherEntry "ENTRIES" ON "VOUCHER ". FID = "ENTRIES ". FBillID inner join T_BD_AccountView "ACCOUNT" ON "ENTRIES ". FAccountID = "ACCOUNT ". fid left outer join T_GL_VoucherAssistRecord "ASSISTRECORDS" ON "ENTRIES ". FID = "ASSISTRECORDS ". FEntryID WHERE "VOUCHER ". fid in (select id from x) order by "ID" ASC, "ENTRIES. SEQ "ASC," ASSISTRECORDS. SEQ "ASC --- ======= execution plan scheduler | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | STATEMENT | 0 | select statement | 24 | 11208 | 506 (1) | 1 | temp table transformation | 2 | load as select | SYS_TEMP_0FD9D6853_1AD5C99D | 3 | hash join | 1 | 415 | 458 (1) | 4 | nested loops | 5 | nested loops | 258 | 83850 | 390 (0) | 6 | nested loops | 6 | 1332 | 3 (0) | 7 | table access by index rowid | T_BD_ACCOUNTVIEW | 1 | 111 | 2 (0) | 8 | index unique scan | PK_BD_ACCOUNTVIEW | 1 | 1 (0) | 9 | index range scan | IX_BD_ACTCOMLNUM | 6 | 666 | 1 (0) | 10 | index range scan | IX_GL_VCHAACCT | 489 | 1 (0) | 11 | table access by index rowid | T_GL_VOUCHERENTRY | 42 | 4326 | 65 (0) | 12 | index range scan | IX_GL_VCH_11 | 7536 | 662K | 68 (0) | 13 | sort order by | 24 | 11208 | 48 (5) | 14 | nested loops outer | 24 | 11208 | 47 (3) | 15 | nested loops | 17 | 6086 | 21 (5) | 16 | nested loops | 17 | 5253 | 13 (8) | 17 | nested loops outer | 1 | 121 | 5 (20) | 18 | nested loops | 1 | 87 | 4 (25) | 19 | VIEW | VW_NSO_1 | 1 | 29 | 2 (0) | 20 | hash unique | 1 | 24 | 21 | VIEW | 1 | 24 | 2 (0) | 22 | table access full | SYS_TEMP_0FD9D6853_1AD5C99D | 1 | 29 | 2 (0) | 23 | index range scan | IX_GL_VCH_FIDCMPNUM | 1 | 58 | 1 (0) | 24 | index range scan | IX_PERIOD_ENC | 1 | 34 | 1 (0) | 25 | table access by index rowid | T_GL_VOUCHERENTRY | 17 | 3196 | 8 (0) | 26 | index range scan | IX_GL_VCHENTRYFQ1 | 17 | 1 (0) | 27 | table access by index rowid | T_BD_ACCOUNTVIEW | 1 | 49 | 1 (0) | 28 | index unique scan | PK_BD_ACCOUNTVIEW | 1 | 1 (0) | 29 | table access by index rowid | T_GL_VOUCHERASSISTRECORD | 1 | 109 | 2 (0) | 30 | index range scan | IX_GL_VCHASSREC_11 | 2 | 1 (0) | average ------------------------------------------------------------------------------------------------------

Last

I want to optimize SQL statements by adding qq 692162374 or group 

Zookeeper