需求：
根據用戶從機構表中查詢出多個機構號
再從資料表中查詢組合號碼相同的，并且關聯機構號欄位中至少有一個與查出的機構號中任意一個匹配的資料
機構表：
表名 t_dept
ID_DEPT（主鍵，使用SYSGUID） DEPT_NO（機構號） DEPT_NAME（機構名） PERSON（用戶）
SYSGUID（） 0012 aaa MYUSER
SYSGUID（） 0043 bbb MYUSER
SYSGUID（） 1708 ccc MYUSER
資料表：
表名 t_data
ID_DATA（） DATA_CODE（資料編碼，由兩個SYSGUID拼出）DEPT_NOS（關聯機構號，存放多個機構號，用“，”分隔） ……（其他資料展示欄位）
SYSGUID（） SYSGUID（）_SYSGUID（） 0012,0043,1708,1654,9812
SYSGUID（） SYSGUID（）_SYSGUID（） 1708,1654,9813
SYSGUID（） SYSGUID（）_SYSGUID（） 1708,0012,1654,9814,0043
SYSGUID（） SYSGUID（）_SYSGUID（） 0012,0043,1654,9815
SYSGUID（） SYSGUID（）_SYSGUID（） 0043,1708,1654,9816,0012
注：資料表的資料有一千萬條
傳入一個編碼，這個編碼與DATA_CODE欄位的后32位進行匹配
機構號可以是傳入的已經查出來的一個集合（我之前使用in（機構號，機構號....）但是效率太低了）
只要這個集合中的任意一個機構號，與資料表中DEPT_NOS欄位里的使用逗號分隔的多個機構號中的任意一個匹配就可以查出這條資料
求大神指教怎么寫這個查詢SQL ！？！
我的SQL就不貼出來了，效率太低，執行2個小時都沒執行完
uj5u.com熱心網友回復：
in效率低 可以換下 minus和exists。有索引的欄位上不要用函式和加運算式uj5u.com熱心網友回復：
不只是in的問題！機構號是傳入的，已知的多個機構號，這個怎么用minus或exists啊？
目前關鍵在于，用已知的多個機構號去匹配欄位里面的多個機構號，這個如何匹配？我之前去拆分了資料，但是這個效率特別低，1000條資料拆分的時間多很長，這個要怎么解決？
uj5u.com熱心網友回復：
兩個表的 SYSGUID 這個欄位有關聯嗎？uj5u.com熱心網友回復：
傳入一個編碼，這個編碼與DATA_CODE欄位的后32位進行匹配，得到DEPT_NOS （這一步以什么方式進行，全表掃描匹配）再通過DEPT_NOS得到機構資訊？
沒看懂
uj5u.com熱心網友回復：
兩個表的 SYSGUID 這個欄位是沒有關聯的，是自己生成的傳入一個編碼，這個編碼與DATA_CODE欄位的后32位進行匹配，這是匹配的過濾條件之一
另一個條件就是要在DEPT_NOS中的多個機構號中匹配
我寫了一個sql，資料量在100萬的時候，執行熟讀是90秒，查看了執行計劃會有全表掃描，大家幫忙看看啊
select 需要查詢的欄位 from t_data b
where exists（
select * from (
select * from t_data a
where substr(DATA_CODE,34,32) = substr(傳入的編碼,34,32)
)c,table(split_mine(DEPT_NOS,','))
where column_value in(傳入的機構號集合) and b.ID_DATA = c.ID_DATA
）
其中split_mine是我自己寫的拆分函式
紅色字的執行計劃是沒有全表掃描的，但是全部執行即便沒有in還是會全表掃描
這個要怎么優化？
另外，匹配條件中的機構號是傳入的確定值，這個不用in，應該用什么替代呢？
uj5u.com熱心網友回復：
這這個是設計的問題，要調整出一個獨立的關系表。太消費 CPU 和 IO.
uj5u.com熱心網友回復：
select 需要查詢的欄位 from t_data b,(select distinct ID_DATA from (
select * from t_data a
where substr(DATA_CODE,34,32) = substr(傳入的編碼,34,32)
)c,table(split_mine(DEPT_NOS,','))
where column_value in(傳入的機構號集合)
) c
where b.ID_DATA = c.ID_DATA
uj5u.com熱心網友回復：
--創建索引create index i_t_data_01 on t_data(substr(DATA_CODE,34,32)) online;
嘗試以下sql：
WITH t_sql AS
(SELECT rtrim(xmlagg(xmlparse(content 'select * from t_data a where substr(DATA_CODE,34,32)=substr(''$傳入的編碼$'',34,32) and instr(a.dept_nos,''' || a.value || ''')>=1 union ' wellformed))
.getclobval(), 'union ') sqlt
FROM TABLE(split_mine('$傳入的機構號集合$', ',')) a)
SELECT b.*
FROM (SELECT dbms_xmlgen.getxmltype(sqlt) xml_data
FROM t_sql) a,
xmltable('$data/ROWSET/ROW' passing xml_data AS "data" columns id_data
VARCHAR2(500), data_code VARCHAR2(500), dept_nos VARCHAR2(500)) b;
uj5u.com熱心網友回復：
--代碼修復，value改為column_value：WITH t_sql AS
(SELECT rtrim(xmlagg(xmlparse(content 'select * from t_data a where substr(DATA_CODE,34,32)=substr(''$傳入的編碼$'',34,32) and instr(a.dept_nos,''' || a.column_value || ''')>=1 union ' wellformed))
.getclobval(), 'union ') sqlt
FROM TABLE(split_mine('$傳入的機構號集合$', ',')) a)
SELECT b.*
FROM (SELECT dbms_xmlgen.getxmltype(sqlt) xml_data
FROM t_sql) a,
xmltable('$data/ROWSET/ROW' passing xml_data AS "data" columns id_data
VARCHAR2(500), data_code VARCHAR2(500), dept_nos VARCHAR2(500)) b;
以上。
uj5u.com熱心網友回復：
如果不增加關系表，一定要用單表的話，建議 字串域段值規整為 ",0012,0043,0035," 即前后都有 逗號， 然后 like 查找 '%,0043,%' 只能是整表遍歷， 但千萬別在整表遍歷的基礎上增加函式分析，會更慢。轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/60638.html
標籤：開發