[20181219]不能使用USE_CONCAT優化例子.txt

--//連結http://www.itpub.net/thread-2107240-2-1.html,http://www.itpub.net/thread-2107231-2-1.html的討論.

SQL_ID 6qsvy74cbrm6x, child number 0

-------------------------------------

SELECT /*+ gather_plan_statistics USE_CONCAT */ * FROM ljapay

WHERE incomeno IN (SELECT contno FROM

lccont WHERE prtno = '1300000000617430')

OR otherno = '1300000000617430'

Plan hash value: 2966270370

------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | | | 549 (100)| | 0 |00:00:00.33 | 233K|

|* 1 | FILTER | | 1 | | | | | 0 |00:00:00.33 | 233K|

| 2 | TABLE ACCESS FULL | LJAPAY | 1 | 67404 | 13M| 549 (1)| 00:00:07 | 67404 |00:00:00.04 | 2015 |

|* 3 | TABLE ACCESS BY INDEX ROWID| LCCONT | 67376 | 1 | 37 | 3 (0)| 00:00:01 | 0 |00:00:00.25 | 231K|

|* 4 | INDEX UNIQUE SCAN | PK_LCCONT | 67376 | 1 | | 2 (0)| 00:00:01 | 63370 |00:00:00.12 | 168K|

------------------------------------------------------------------------------------------------------------------------------------

--//加入USE_CONCAT並沒有用,走的是filter,更要命的是id=3,4的starts=67376,大部分邏輯讀都在這裡.

--//作者執行的是修改,我換成了select語句.

--//我自己寫一個測試例子驗證看看.

1.環境:

SCOTT@book> @ ver1

PORT_STRING VERSION BANNER

------------------------------ -------------- --------------------------------------------------------------------------------

x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

create table t1 as select rownum id1 ,rownum id2 ,lpad('x',100,'x') name from dual connect by level<=6000;

create table t2 as select rownum id1 ,rownum id2 ,lpad('x',100,'x') name from dual connect by level<=6000;

create index i_t1_id1 on t1(id1);

create index i_t1_id2 on t1(id2);

create index i_t2_id1 on t2(id1);

--//分析略.

2.測試:

SCOTT@book> alter session set statistics_level = all;

Session altered.

SCOTT@book> select * from t1 where t1.id1 in (select t2.id1 from t2 where t2.id1=11 ) or (t1.id2=10 );

ID1 ID2 NAME

---------- ---------- ----------------------------------------------------------------------------------------------------

10 10 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

11 11 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

SCOTT@book> @ dpc '' ''

PLAN_TABLE_OUTPUT

-------------------------------------

SQL_ID gz5pqkg6svm7k, child number 0

-------------------------------------

select * from t1 where t1.id1 in (select t2.id1 from t2 where

t2.id1=11 ) or (t1.id2=10 )

Plan hash value: 1962644737

-------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | | | 30 (100)| | 2 |00:00:00.01 | 102 |

|* 1 | FILTER | | 1 | | | | | 2 |00:00:00.01 | 102 |

| 2 | TABLE ACCESS FULL| T1 | 1 | 6000 | 638K| 30 (0)| 00:00:01 | 6000 |00:00:00.01 | 100 |

|* 3 | FILTER | | 5999 | | | | | 1 |00:00:00.01 | 2 |

|* 4 | INDEX RANGE SCAN| I_T2_ID1 | 1 | 1 | 4 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 |

-------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

1 - SEL$1

2 - SEL$1 / T1@SEL$1

3 - SEL$2

4 - SEL$2 / T2@SEL$2

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter(("T1"."ID2"=10 OR IS NOT NULL))

3 - filter(11=:B1)

4 - access("T2"."ID1"=:B1)

32 rows selected.

--//我的版本與作者看到的不同,仔細看id=4的starts就明白了,實際上這裡應該迴圈5999次.

--//我的版本11.2.0.4(沒有打任何補丁),而他使用版本是11.2.0.4.180717.

--//也就是我使用的版本最後的buffers計算錯誤.

--//加入提示:

select /*+ USE_CONCAT */ * from t1 where t1.id1 in (select t2.id1 from t2 where t2.id1=11 ) or (t1.id2=10 );

--//執行效果一樣.使用如下提示:

select /*+ USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(1)) */ * from t1 where t1.id1 in (select t2.id1 from t2 where t2.id1=11 ) or (t1.id2=10 );

--//執行計劃如下:

SCOTT@book> @ dpc '' ''

PLAN_TABLE_OUTPUT

-------------------------------------

SQL_ID 9m6dksysdc87a, child number 0

-------------------------------------

select /*+ USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(1)) */ * from t1 where

t1.id1 in (select t2.id1 from t2 where t2.id1=11 ) or (t1.id2=10 )

Plan hash value: 1154250921

-----------------------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | | | 33 (100)| | 2 |00:00:00.01 | 105 |

| 1 | CONCATENATION | | 1 | | | | | 2 |00:00:00.01 | 105 |

| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 1 | 109 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 4 |

|* 3 | INDEX RANGE SCAN | I_T1_ID2 | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |

|* 4 | FILTER | | 1 | | | | | 1 |00:00:00.01 | 101 |

|* 5 | TABLE ACCESS FULL | T1 | 1 | 5999 | 638K| 30 (0)| 00:00:01 | 5999 |00:00:00.01 | 99 |

|* 6 | FILTER | | 5999 | | | | | 1 |00:00:00.01 | 2 |

|* 7 | INDEX RANGE SCAN | I_T2_ID1 | 1 | 1 | 4 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 |

-----------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

1 - SEL$1

2 - SEL$1_1 / T1@SEL$1

3 - SEL$1_1 / T1@SEL$1

5 - SEL$1_2 / T1@SEL$1_2

6 - SEL$2

7 - SEL$2 / T2@SEL$2

Predicate Information (identified by operation id):

---------------------------------------------------

3 - access("T1"."ID2"=10)

4 - filter( IS NOT NULL)

5 - filter(LNNVL("T1"."ID2"=10))

6 - filter(11=:B1)

7 - access("T2"."ID1"=:B1)

--//提示起作用.但是id=4,走的是filter,一樣效率低下.改寫如下:

select /*+ USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(1)) */ * from t1 where t1.id1 in (select /*+ unnest */ t2.id1 from t2 where t2.id1=11 ) or (t1.id2=10 );

--//也是一樣unnest沒用.看了開發版的連結:http://www.itpub.net/thread-2107231-1-1.html,改用OR_EXPANSION問題依舊.

select /*+ OR_EXPAND */ * from t1 where t1.id1 in (select /*+ unnest */ t2.id1 from t2 where t2.id1=11 ) or (t1.id2=10 );

--//這樣使用union all:

select * from t1 where t1.id1 in (select /*+ unnest */ t2.id1 from t2 where t2.id1=11 ) and lnnvl(t1.id2=10)

union all

select * from t1 where (t1.id2=10 );

--//或者使用union:

select * from t1 where t1.id1 in (select /*+ unnest */ t2.id1 from t2 where t2.id1=11 )

union

select * from t1 where (t1.id2=10 );

--//感覺使用union all 效果更好.貼出使用union all的執行計劃:

SQL_ID 3619bvczapunb, child number 0

-------------------------------------

select * from t1 where t1.id1 in (select /*+ unnest */ t2.id1 from t2

where t2.id1=11 ) and lnnvl(t1.id2=10) union all select * from t1 where

(t1.id2=10 )

Plan hash value: 3919113390

------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | | | 5 (100)| | 2 |00:00:00.01 | 9 |

| 1 | UNION-ALL | | 1 | | | | | 2 |00:00:00.01 | 9 |

| 2 | NESTED LOOPS SEMI | | 1 | 1 | 113 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 6 |

|* 3 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 1 | 109 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 4 |

|* 4 | INDEX RANGE SCAN | I_T1_ID1 | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |

|* 5 | INDEX RANGE SCAN | I_T2_ID1 | 1 | 1 | 4 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 |

| 6 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 1 | 109 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |

|* 7 | INDEX RANGE SCAN | I_T1_ID2 | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 |

------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

1 - SET$1

2 - SEL$5DA710D3

3 - SEL$5DA710D3 / T1@SEL$1

4 - SEL$5DA710D3 / T1@SEL$1

5 - SEL$5DA710D3 / T2@SEL$2

6 - SEL$3 / T1@SEL$3

7 - SEL$3 / T1@SEL$3

Predicate Information (identified by operation id):

---------------------------------------------------

3 - filter(LNNVL("T1"."ID2"=10))

4 - access("T1"."ID1"=11)

5 - access("T2"."ID1"=11)

filter("T1"."ID1"="T2"."ID1")

7 - access("T1"."ID2"=10)

Column Projection Information (identified by operation id):

-----------------------------------------------------------

1 - STRDEF[22], STRDEF[22], STRDEF[100]

2 - "T1"."ID1"[NUMBER,22], "T1"."ID2"[NUMBER,22], "T1"."NAME"[VARCHAR2,100]

3 - "T1"."ID1"[NUMBER,22], "T1"."ID2"[NUMBER,22], "T1"."NAME"[VARCHAR2,100]

4 - "T1".ROWID[ROWID,10], "T1"."ID1"[NUMBER,22]

6 - "T1"."ID1"[NUMBER,22], "T1"."ID2"[NUMBER,22], "T1"."NAME"[VARCHAR2,100]

7 - "T1".ROWID[ROWID,10], "T1"."ID2"[NUMBER,22]

3.繼續:

--//不過對方執行的是修改操作,使用union all或者union,不能基於結果集合的修改.

--//會報錯誤 ORA-01732: data manipulation operation not legal on this view.

--//關於結果集修改看連結:http://blog.itpub.net/267265/viewspace-2139049/

--// 修改條件使用主鍵或者rowid就ok了.貼出使用rowid的改寫:

update t1 set name='zzz' where

rowid in

(

select rowid from t1 where t1.id1 in (select /*+ unnest */ t2.id1 from t2 where t2.id1=11 ) and lnnvl(t1.id2=10)

union all

select rowid from t1 where (t1.id2=10 )

);

SCOTT@book> @ dpc '' ''

PLAN_TABLE_OUTPUT

-------------------------------------

SQL_ID g6608508whaar, child number 0

-------------------------------------

update t1 set name='zzz' where rowid in ( select rowid from t1 where

t1.id1 in (select /*+ unnest */ t2.id1 from t2 where t2.id1=11 ) and

lnnvl(t1.id2=10) union all select rowid from t1 where (t1.id2=10 ) )

Plan hash value: 3348292872

-------------------------------------------------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------------------------------------------------------------------

| 0 | UPDATE STATEMENT | | 1 | | | 6 (100)| | 0 |00:00:00.01 | 10 | | | |

| 1 | UPDATE | T1 | 1 | | | | | 0 |00:00:00.01 | 10 | | | |

| 2 | NESTED LOOPS | | 1 | 120 | 14520 | 6 (0)| 00:00:01 | 2 |00:00:00.01 | 8 | | | |

| 3 | VIEW | VW_NSO_1 | 1 | 2 | 24 | 4 (0)| 00:00:01 | 2 |00:00:00.01 | 7 | | | |

| 4 | SORT UNIQUE | | 1 | 2 | 40 | 4 (0)| 00:00:01 | 2 |00:00:00.01 | 7 | 73728 | 73728 | |

| 5 | UNION-ALL | | 1 | | | | | 2 |00:00:00.01 | 7 | | | |

| 6 | NESTED LOOPS SEMI | | 1 | 1 | 24 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 5 | | | |

|* 7 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 1 | 20 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | | | |

|* 8 | INDEX RANGE SCAN | I_T1_ID1 | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | | | |

|* 9 | INDEX RANGE SCAN | I_T2_ID1 | 1 | 1 | 4 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | | | |

|* 10 | INDEX RANGE SCAN | I_T1_ID2 | 1 | 1 | 16 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | | | |

| 11 | TABLE ACCESS BY USER ROWID | T1 | 2 | 60 | 6540 | 1 (0)| 00:00:01 | 2 |00:00:00.01 | 1 | | | |

-------------------------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

1 - SEL$9C09E64D

3 - SET$FCA7A018 / VW_NSO_1@SEL$9C09E64D

4 - SET$FCA7A018

6 - SEL$5DA710D3

7 - SEL$5DA710D3 / T1@SEL$1

8 - SEL$5DA710D3 / T1@SEL$1

9 - SEL$5DA710D3 / T2@SEL$2

10 - SEL$3 / T1@SEL$3

11 - SEL$9C09E64D / T1@UPD$1

Predicate Information (identified by operation id):

---------------------------------------------------

7 - filter(LNNVL("T1"."ID2"=10))

8 - access("T1"."ID1"=11)

9 - access("T2"."ID1"=11)

filter("T1"."ID1"="T2"."ID1")

10 - access("T1"."ID2"=10)

--//id=4 SORT UNIQUE,依舊需要去除重複的記錄.另外連結也提供了使用merge的例子,我個人很少使用它.貼出對方改寫的merge的例子:

MERGE INTO ljapay t1

USING (SELECT a.payno, a.currency

FROM ljapay a JOIN lccont b ON a.incomeno = b.contno

WHERE b.prtno = 'Q99180000000110'

UNION

SELECT a.payno, a.currency

FROM ljapay a

WHERE otherno = 'Q99180000000110') t2

ON (t1.payno = t2.payno AND t1.currency = t2.currency)

WHEN MATCHED

THEN

UPDATE SET

t1.Operstate = '0'

,t1.modifydate = DATE '2018-12-17'

,t1.modifytime = '16:20:44'

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2285693/，如需轉載，請註明出處，否則將追究法律責任。

[20181219]不能使用USE_CONCAT優化例子.txt

相關文章