[20181031]lob欄位與布隆過濾.txt

--//今天8月份遇到的問題,連結:[20180828]exadata--豆腐渣系統的保護神.txt=>http://blog.itpub.net/267265/viewspace-2213256/

1.環境:

SYS@xxxx1> @ &r/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

SELECT XXXXXX_YYY.EMR_BL03.*, XXXXXX_YYY.EMR_BL_BL01.BLMC

FROM XXXXXX_YYY.EMR_BL03

LEFT JOIN XXXXXX_YYY.EMR_BL_BL01

ON XXXXXX_YYY.EMR_BL03.BLBH = XXXXXX_YYY.EMR_BL_BL01.BLBH

WHERE XXXXXX_YYY.EMR_BL_BL01.BRBH = '00366441';

--//執行計劃,選擇布隆過濾,當時awr顯示需要54.27秒,IO佔49.51秒.

Plan hash value: 40434530

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

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

| 0 | SELECT STATEMENT | | | | 215K(100)| |

| 1 | HASH JOIN | | 19 | 27645 | 215K (1)| 00:43:02 |

| 2 | JOIN FILTER CREATE | :BF0000 | 19 | 817 | 16 (0)| 00:00:01 |

| 3 | TABLE ACCESS BY INDEX ROWID| EMR_BL_BL01 | 19 | 817 | 16 (0)| 00:00:01 |

| 4 | INDEX RANGE SCAN | I_EMR_BL_BL01_BRBH_CJSJ | 19 | | 3 (0)| 00:00:01 |

| 5 | JOIN FILTER USE | :BF0000 | 3968K| 5343M| 215K (1)| 00:43:01 |

| 6 | TABLE ACCESS STORAGE FULL | EMR_BL03 | 3968K| 5343M| 215K (1)| 00:43:01 |

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

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

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

1 - SEL$C8875FE2

3 - SEL$C8875FE2 / EMR_BL_BL01@SEL$1

4 - SEL$C8875FE2 / EMR_BL_BL01@SEL$1

6 - SEL$C8875FE2 / EMR_BL03@SEL$2

zzzzz> @ &r/desc XXXXXX_YYY.EMR_BL03

Name Null? Type

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

WDBH NOT NULL NUMBER(18)

ZYMZ NOT NULL NUMBER(2)

BLBH NOT NULL NUMBER(18)

WDLX NOT NULL NUMBER(4)

WDNR BLOB

zzzzz> select segment_name,bytes/1024/1024/1024 Gb from DBA_SEGMENTS where segment_name='EMR_BL03';

SEGMENT_NAME GB

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

EMR_BL03 12.2724609

zzzzz> select segment_name,bytes/1024/1024/1024 gb from dba_segments where segment_name in

(select segment_name from DBA_LOBS where table_name='EMR_BL03');

SEGMENT_NAME GB

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

SYS_LOB0000087717C00005$$ 102.436523

--//以上內容是當時的記錄.

--//我以前大概測試過我們現在使用的exadata,select /*+ full(a) */ count(*) from big_table a;IO最大吞吐量大約2.5GB/s.

--//(102.436523+12.2724609)/2.5 = 45.88359356,這樣非常接近.

--//主要原因:索引建立不合適,EMR_BL03存在索引IDX_EMR_BL03_BLBH.欄位包括ZYMZ, BLBH, WDLX.而且執行計劃沒有index skip scan.

--//當時遇到的困惑:

1.建立索引不合適,但是為什麼沒有選擇index skip scan.這個探究放棄.

2.即使選擇布隆過濾,選擇全表掃描EMR_BL03,讀取大量的lob資訊不應該.如果選擇hash join呢,會出現怎樣的情況呢?

2.測試:

--//今天主要探究第2個問題:

--//查詢不包括BLOB欄位,因為正確的索引已經建立.要繼續探究只能加入提示/*+ full(EMR_BL03) */.

xxxx> set timing on

xxxx> alter session set statistics_level=all;

Session altered.

--//採用bloom 過濾,查詢不包括lob欄位.

SELECT /*+ full(EMR_BL03) */ XXXXXX_YYY.EMR_BL03.WDBH

,XXXXXX_YYY.EMR_BL03.ZYMZ

,XXXXXX_YYY.EMR_BL03.BLBH

,XXXXXX_YYY.EMR_BL03.WDLX

,XXXXXX_YYY.EMR_BL_BL01.BLMC

FROM XXXXXX_YYY.EMR_BL03

LEFT JOIN XXXXXX_YYY.EMR_BL_BL01

ON XXXXXX_YYY.EMR_BL03.BLBH = XXXXXX_YYY.EMR_BL_BL01.BLBH

WHERE XXXXXX_YYY.EMR_BL_BL01.BRBH = '00366441';

Elapsed: 00:00:06.32

--//注:查詢不包含lob欄位.執行需要7秒!!執行計劃如下:

Plan hash value: 40434530

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

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

| 0 | SELECT STATEMENT | | 1 | | | 435K(100)| | 57 |00:00:05.23 | 1655K| 1655K| | | |

|* 1 | HASH JOIN | | 1 | 19 | 1159 | 435K (1)| 01:27:07 | 57 |00:00:05.23 | 1655K| 1655K| 2226K| 2226K| 1129K (0)|

| 2 | JOIN FILTER CREATE | :BF0000 | 1 | 19 | 817 | 16 (0)| 00:00:01 | 56 |00:00:00.01 | 44 | 0 | | | |

| 3 | TABLE ACCESS BY INDEX ROWID| EMR_BL_BL01 | 1 | 19 | 817 | 16 (0)| 00:00:01 | 56 |00:00:00.01 | 44 | 0 | | | |

|* 4 | INDEX RANGE SCAN | I_EMR_BL_BL01_BRBH_CJSJ | 1 | 19 | | 3 (0)| 00:00:01 | 56 |00:00:00.01 | 3 | 0 | 1025K| 1025K| |

| 5 | JOIN FILTER USE | :BF0000 | 1 | 8118K| 139M| 435K (1)| 01:27:06 | 7220 |00:00:05.23 | 1655K| 1655K| | | |

|* 6 | TABLE ACCESS STORAGE FULL | EMR_BL03 | 1 | 8118K| 139M| 435K (1)| 01:27:06 | 7220 |00:00:05.22 | 1655K| 1655K| 1025K| 1025K| 3085K (0)|

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

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

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

1 - SEL$C8875FE2

3 - SEL$C8875FE2 / EMR_BL_BL01@SEL$1

4 - SEL$C8875FE2 / EMR_BL_BL01@SEL$1

6 - SEL$C8875FE2 / EMR_BL03@SEL$2

Predicate Information (identified by operation id):

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

1 - access("EMR_BL03"."BLBH"="EMR_BL_BL01"."BLBH")

4 - access("EMR_BL_BL01"."BRBH"='00366441')

6 - storage(SYS_OP_BLOOM_FILTER(:BF0000,"EMR_BL03"."BLBH"))

filter(SYS_OP_BLOOM_FILTER(:BF0000,"EMR_BL03"."BLBH"))

--//buffers=1655K.

--//做10046跟蹤:

Elapsed times include waiting on following events:

Event waited on Times Max. Wait Total Waited

---------------------------------------- Waited ---------- ------------

SQL*Net message to client 2 0.00 0.00

enq: KO - fast object checkpoint 3 0.04 0.04

reliable message 1 0.00 0.00

cell smart table scan 3678 0.08 3.38

SQL*Net message from client 2 4.40 4.40

********************************************************************************

--//可以發現enq: KO - fast object checkpoint,這是採用smart scan,必須先把髒塊寫盤,執行時間主要消耗在cell smart table scan,SQL*Net message from client.

3.測試2:

--//採用bloom 過濾,查詢包括lob欄位.

SELECT /*+ full(EMR_BL03) */ XXXXXX_YYY.EMR_BL03.*, XXXXXX_YYY.EMR_BL_BL01.BLMC

FROM XXXXXX_YYY.EMR_BL03

LEFT JOIN XXXXXX_YYY.EMR_BL_BL01

ON XXXXXX_YYY.EMR_BL03.BLBH = XXXXXX_YYY.EMR_BL_BL01.BLBH

WHERE XXXXXX_YYY.EMR_BL_BL01.BRBH = '00366441';

Elapsed: 00:00:46.77

--//執行需要47,與我前面看到基本一致.

--//執行計劃如下:

Plan hash value: 40434530

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

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

| 0 | SELECT STATEMENT | | 1 | | | 435K(100)| | 57 |00:00:44.96 | 1655K| 1655K| | | |

|* 1 | HASH JOIN | | 1 | 19 | 27284 | 435K (1)| 01:27:07 | 57 |00:00:44.96 | 1655K| 1655K| 2211K| 2211K| 1123K (0)|

| 2 | JOIN FILTER CREATE | :BF0000 | 1 | 19 | 817 | 16 (0)| 00:00:01 | 56 |00:00:00.01 | 44 | 0 | | | |

| 3 | TABLE ACCESS BY INDEX ROWID| EMR_BL_BL01 | 1 | 19 | 817 | 16 (0)| 00:00:01 | 56 |00:00:00.01 | 44 | 0 | | | |

|* 4 | INDEX RANGE SCAN | I_EMR_BL_BL01_BRBH_CJSJ | 1 | 19 | | 3 (0)| 00:00:01 | 56 |00:00:00.01 | 3 | 0 | 1025K| 1025K| |

| 5 | JOIN FILTER USE | :BF0000 | 1 | 8118K| 10G| 435K (1)| 01:27:06 | 7220 |00:00:44.95 | 1655K| 1655K| | | |

|* 6 | TABLE ACCESS STORAGE FULL | EMR_BL03 | 1 | 8118K| 10G| 435K (1)| 01:27:06 | 7220 |00:00:44.95 | 1655K| 1655K| 1025K| 1025K| |

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

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

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

1 - SEL$C8875FE2

3 - SEL$C8875FE2 / EMR_BL_BL01@SEL$1

4 - SEL$C8875FE2 / EMR_BL_BL01@SEL$1

6 - SEL$C8875FE2 / EMR_BL03@SEL$2

Predicate Information (identified by operation id):

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

1 - access("EMR_BL03"."BLBH"="EMR_BL_BL01"."BLBH")

4 - access("EMR_BL_BL01"."BRBH"='00366441')

6 - storage(SYS_OP_BLOOM_FILTER(:BF0000,"EMR_BL03"."BLBH"))

filter(SYS_OP_BLOOM_FILTER(:BF0000,"EMR_BL03"."BLBH"))

--//buffers=1655K,大約1655*1024*8192/1024/1024/1024 = 12.9296875G,看來我以前的判斷有誤.並不會大量讀取lob資訊.

--//為什麼需要46秒呢?

xxxx> select * from dba_objects where owner='XXXXXX_YYY' and object_name='EMR_BL03';

OWNER OBJECT_NAME SUBOBJECT_ OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE EDITION_NAME

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

XXXXXX_YYY EMR_BL03 87717 87717 TABLE 2014-10-28 18:54:49 2018-08-30 09:53:17 2014-10-28:18:54:49 VALID N N N 1

xxxx> select owner,column_name,segment_name,index_name from dba_lobs where owner='XXXXXX_YYY' and table_name='EMR_BL03';

OWNER COLUMN_NAME SEGMENT_NAME INDEX_NAME

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

XXXXXX_YYY WDNR SYS_LOB0000087717C00005$$ SYS_IL0000087717C00005$$

xxxx> select * from dba_objects where owner='XXXXXX_YYY' and object_name in ('SYS_LOB0000087717C00005$$','SYS_IL0000087717C00005$$');

OWNER OBJECT_NAME SUBOBJECT_ OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE EDITION_NAME

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

XXXXXX_YYY SYS_IL0000087717C00005$$ 87719 87719 INDEX 2014-10-28 18:54:49 2014-10-28 18:54:49 2014-10-28:18:54:49 VALID N Y N 4

XXXXXX_YYY SYS_LOB0000087717C00005$$ 87718 87718 LOB 2014-10-28 18:54:49 2014-10-28 18:54:49 2014-10-28:18:54:49 VALID N Y N 8

--//可以確定OBJ# = 87717,87718,87719

# grep "direct path read" /u01/app/oracle/diag/rdbms/xxxx/xxxx1/trace/xxxx1_ora_74497.trc | grep obj#=87717|wc

7099 106485 950291

# grep "direct path read" /u01/app/oracle/diag/rdbms/xxxx/xxxx1/trace/xxxx1_ora_74497.trc | grep obj#=87718|wc

23 345 2730

# grep "direct path read" /u01/app/oracle/diag/rdbms/xxxx/xxxx1/trace/xxxx1_ora_74497.trc | grep obj#=87719|wc

0 0 0

SELECT /*+ full(EMR_BL03) */ XXXXXX_YYY.EMR_BL03.*, XXXXXX_YYY.EMR_BL_BL01.BLMC

FROM XXXXXX_YYY.EMR_BL03

LEFT JOIN XXXXXX_YYY.EMR_BL_BL01

ON XXXXXX_YYY.EMR_BL03.BLBH = XXXXXX_YYY.EMR_BL_BL01.BLBH

WHERE XXXXXX_YYY.EMR_BL_BL01.BRBH = '00366441'

call count cpu elapsed disk query current rows

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

Parse 1 0.00 0.00 0 0 0 0

Execute 1 0.00 0.00 0 0 0 0

Fetch 58 4.95 37.06 1655745 1655859 0 57

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

total 60 4.95 37.06 1655745 1655859 0 57

Misses in library cache during parse: 0

Optimizer mode: ALL_ROWS

Parsing user id: SYS

Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation

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

57 57 57 HASH JOIN (cr=1655859 pr=1655745 pw=0 time=37062551 us cost=435533 size=27284 card=19)

56 56 56 JOIN FILTER CREATE :BF0000 (cr=44 pr=0 pw=0 time=253 us cost=16 size=817 card=19)

56 56 56 TABLE ACCESS BY INDEX ROWID EMR_BL_BL01 (cr=44 pr=0 pw=0 time=226 us cost=16 size=817 card=19)

56 56 56 INDEX RANGE SCAN I_EMR_BL_BL01_BRBH_CJSJ (cr=3 pr=0 pw=0 time=35 us cost=3 size=0 card=19)(object id 88921)

7220 7220 7220 JOIN FILTER USE :BF0000 (cr=1655815 pr=1655745 pw=0 time=37051920 us cost=435495 size=11308964632 card=8118424)

7220 7220 7220 TABLE ACCESS STORAGE FULL EMR_BL03 (cr=1655815 pr=1655745 pw=0 time=37049166 us cost=435495 size=11308964632 card=8118424)

Elapsed times include waiting on following events:

Event waited on Times Max. Wait Total Waited

---------------------------------------- Waited ---------- ------------

library cache lock 1 0.00 0.00

library cache pin 1 0.00 0.00

SQL*Net message to client 59 0.00 0.00

SQL*Net message from client 59 36.30 36.32

enq: KO - fast object checkpoint 3 0.01 0.01

reliable message 1 0.00 0.00

direct path read 7099 0.27 32.60

********************************************************************************

--//這裡並沒有包含lob的direct path read.

********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows

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

Parse 2 0.00 0.00 0 0 0 0

Execute 2 0.00 0.00 0 0 0 0

Fetch 58 4.95 37.06 1655745 1655859 0 57

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

total 62 4.95 37.06 1655745 1655859 0 57

Misses in library cache during parse: 0

Elapsed times include waiting on following events:

Event waited on Times Max. Wait Total Waited

---------------------------------------- Waited ---------- ------------

SQL*Net message to client 174 0.00 0.00

SQL*Net message from client 174 36.30 40.62

library cache lock 1 0.00 0.00

library cache pin 1 0.00 0.00

enq: KO - fast object checkpoint 3 0.01 0.01

reliable message 1 0.00 0.00

direct path read 7122 0.27 32.76

gc cr block 2-way 1 0.00 0.00

SQL*Net more data to client 5 0.00 0.00

--//也就是從某種意思講直接路徑讀導致exadata採用塊傳輸模式,沒有充分發揮exadata smart scan的作用.

4.測試3:

--//關閉布隆過濾.查詢不包括lob欄位:

SELECT /*+ OPT_PARAM('_bloom_filter_enabled' 'false') full(EMR_BL03) */ XXXXXX_YYY.EMR_BL03.WDBH

,XXXXXX_YYY.EMR_BL03.ZYMZ

,XXXXXX_YYY.EMR_BL03.BLBH

,XXXXXX_YYY.EMR_BL03.WDLX

,XXXXXX_YYY.EMR_BL_BL01.BLMC

FROM XXXXXX_YYY.EMR_BL03

LEFT JOIN XXXXXX_YYY.EMR_BL_BL01

ON XXXXXX_YYY.EMR_BL03.BLBH = XXXXXX_YYY.EMR_BL_BL01.BLBH

WHERE XXXXXX_YYY.EMR_BL_BL01.BRBH = '00366441';

Elapsed: 00:00:03.99

--//執行時間4秒.有點點詫異的是比方法1快一點點.

Plan hash value: 1372458871

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

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

| 0 | SELECT STATEMENT | | 1 | | | 435K(100)| | 57 |00:00:03.98 | 1655K| 1655K| | | |

|* 1 | HASH JOIN | | 1 | 19 | 1159 | 435K (1)| 01:27:07 | 57 |00:00:03.98 | 1655K| 1655K| 2226K| 2226K| 1284K (0)|

| 2 | TABLE ACCESS BY INDEX ROWID| EMR_BL_BL01 | 1 | 19 | 817 | 16 (0)| 00:00:01 | 56 |00:00:00.01 | 44 | 0 | | | |

|* 3 | INDEX RANGE SCAN | I_EMR_BL_BL01_BRBH_CJSJ | 1 | 19 | | 3 (0)| 00:00:01 | 56 |00:00:00.01 | 3 | 0 | 1025K| 1025K| |

| 4 | TABLE ACCESS STORAGE FULL | EMR_BL03 | 1 | 8118K| 139M| 435K (1)| 01:27:06 | 8397K|00:00:02.50 | 1655K| 1655K| 1025K| 1025K| 3085K (0)|

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

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

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

1 - SEL$C8875FE2

2 - SEL$C8875FE2 / EMR_BL_BL01@SEL$1

3 - SEL$C8875FE2 / EMR_BL_BL01@SEL$1

4 - SEL$C8875FE2 / EMR_BL03@SEL$2

Predicate Information (identified by operation id):

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

1 - access("EMR_BL03"."BLBH"="EMR_BL_BL01"."BLBH")

3 - access("EMR_BL_BL01"."BRBH"='00366441')

5.測試4:

--//關閉布隆過濾.查詢包括lob欄位:

SELECT /*+ OPT_PARAM('_bloom_filter_enabled' 'false') full(EMR_BL03) */ XXXXXX_YYY.EMR_BL03.*, XXXXXX_YYY.EMR_BL_BL01.BLMC

FROM XXXXXX_YYY.EMR_BL03

LEFT JOIN XXXXXX_YYY.EMR_BL_BL01

ON XXXXXX_YYY.EMR_BL03.BLBH = XXXXXX_YYY.EMR_BL_BL01.BLBH

WHERE XXXXXX_YYY.EMR_BL_BL01.BRBH = '00366441';

Elapsed: 00:00:35.65

--//執行計劃如下:

Plan hash value: 1372458871

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

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

| 0 | SELECT STATEMENT | | 1 | | | 435K(100)| | 57 |00:00:33.94 | 1655K| 1655K| | | |

|* 1 | HASH JOIN | | 1 | 19 | 27284 | 435K (1)| 01:27:07 | 57 |00:00:33.94 | 1655K| 1655K| 2211K| 2211K| 1270K (0)|

| 2 | TABLE ACCESS BY INDEX ROWID| EMR_BL_BL01 | 1 | 19 | 817 | 16 (0)| 00:00:01 | 56 |00:00:00.01 | 44 | 0 | | | |

|* 3 | INDEX RANGE SCAN | I_EMR_BL_BL01_BRBH_CJSJ | 1 | 19 | | 3 (0)| 00:00:01 | 56 |00:00:00.01 | 3 | 0 | 1025K| 1025K| |

| 4 | TABLE ACCESS STORAGE FULL | EMR_BL03 | 1 | 8118K| 10G| 435K (1)| 01:27:06 | 8397K|00:00:32.16 | 1655K| 1655K| 1025K| 1025K| |

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

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

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

1 - SEL$C8875FE2

2 - SEL$C8875FE2 / EMR_BL_BL01@SEL$1

3 - SEL$C8875FE2 / EMR_BL_BL01@SEL$1

4 - SEL$C8875FE2 / EMR_BL03@SEL$2

Predicate Information (identified by operation id):

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

1 - access("EMR_BL03"."BLBH"="EMR_BL_BL01"."BLBH")

3 - access("EMR_BL_BL01"."BRBH"='00366441')

--//同類比較也比第2種方法快.

--//按照道理採用布隆過濾在exadata的儲存層完成應該更快一些,也許是全表掃描的原因.

6.總結:

--//看來我以前分析有誤,我以前一直以為oracle這種方法要掃描全部lob段,實際上不是.主要問題是採用direct path read沒有smart scan快.

--//實際上exadata要充分使用smart scan,採用direct path read是前提,規避行連結或者行遷移也是關鍵因素.使用lob相當於一部分資料出現行連結.

--//導致執行計劃中等待事件direct path read,這樣許多工作無法交給儲存層完成,轉移到了服務端,也就是快傳輸模式.無法充分發揮exadata的作用與優勢.

--//方法1,方法3就是沒有讀取lob欄位,可以充分發揮smart scan的最佳化,執行時間一個7秒,1個4秒.還有一點點就是採用布隆過濾的有點慢,我記得exadata書中講布隆計算在儲存層實現的.

--//也許這個全表掃描真的很消耗儲存層資源.

--//另外有點意外的是lob直接路徑讀僅僅抓到1次.

xxxx> SELECT sql_id FROM V$ACTIVE_SESSION_HISTORY WHERE event = 'direct path read' AND sample_time >= TRUNC (SYSDATE) AND current_obj# = 87718;

SQL_ID

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

26zqdq622vt0m

xxxx> @ &r/sqlid 26zqdq622vt0m

SQL_ID SQLTEXT

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

26zqdq622vt0m SELECT /*+ full(EMR_BL03) */ XXXXXX_YYY.EMR_BL03.*, XXXXXX_YYY.EMR_BL_BL01.BLMC

FROM XXXXXX_YYY.EMR_BL03

LEFT JOIN XXXXXX_YYY.EMR_BL_BL01

ON XXXXXX_YYY.EMR_BL03.BLBH = XXXXXX_YYY.EMR_BL_BL01.BLBH

WHERE XXXXXX_YYY.EMR_BL_BL01.BRBH = '00366441'

[20181031]lob欄位與布隆過濾.txt

相關文章