Oracle数据库中，千万级数据量，筛选条件在一个字段里面，求大神指点SQL怎么啊！一千万的数据，匹配某一个字段中以逗号分开的多个值，如何写sql提高查询效率

Question

需求：
根据用户从机构表中查询出多个机构号
再从数据表中查询组合号码相同的，并且关联机构号字段中至少有一个与查出的机构号中任意一个匹配的数据
机构表：
表名      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个小时都没执行完

Answer 1

in效率低 可以换下 minus和exists。有索引的字段上不要用函数和加表达式

Answer 2

不只是in的问题！
机构号是传入的，已知的多个机构号，这个怎么用minus或exists啊？
目前关键在于，用已知的多个机构号去匹配字段里面的多个机构号，这个如何匹配？我之前去拆分了数据，但是这个效率特别低，1000条数据拆分的时间多很长，这个要怎么解决？

Answer 3

两个表的 SYSGUID 这个字段有关联吗？

Answer 4

传入一个编码，这个编码与DATA_CODE字段的后32位进行匹配，得到DEPT_NOS （这一步以什么方式进行，全表扫描匹配）
再通过DEPT_NOS得到机构信息？
没看懂

Answer 5

两个表的 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，应该用什么替代呢？

Answer 6

这这个是设计的问题，要调整出一个独立的关系表。太消费 CPU 和 IO.

Answer 7

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

Answer 8

--创建索引
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;

Answer 9

--代码修复，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;
以上。

Answer 10

如果不增加关系表，一定要用单表的话，建议 字符串域段值规整为  ",0012,0043,0035,"    即前后都有 逗号， 然后    like 查找  '%,0043,%'     只能是整表遍历， 但千万别在整表遍历的基础上增加函数分析，会更慢。