ORACLE 分类查询 正则 REGEXP_SUBSTR intersect交集

建表语句如下：

CREATE TABLE"TEST_SETTING" (
"ID" VARCHAR2(10 BYTE) NULL ,
"LX" VARCHAR2(200 BYTE) NULL 
);
comment on table TEST_JG is'设置表';
comment on column TEST_JG.ID is'编号';
comment on column TEST_JG.LX is '类型';
INSERT INTO"TEST_SETTING" VALUES ('35001', '0x0005');
INSERT INTO"TEST_SETTING" VALUES ('35002', '0x0001,0x0003,0x0004,0x0005');
INSERT INTO"TEST_SETTING" VALUES ('35003', '0x0004');
INSERT INTO"TEST_SETTING" VALUES ('35004', '0x0004,0x0005');
INSERT INTO"TEST_SETTING" VALUES ('35005', '0x0002,0x0004,0x0005');

CREATE TABLE "TEST_JG" (
"ID" VARCHAR2(10 BYTE) NULL ,
"LX" VARCHAR2(200 BYTE) NULL 
);
comment on table TEST_JG is'结果表';
comment on column TEST_JG.ID is'编号';
comment on column TEST_JG.LX is '类型';
INSERT INTO "TEST_JG" VALUES ('35001', '0x0003,0x0005');
INSERT INTO "TEST_JG" VALUES ('35003', '0x0001,0x0003,');
INSERT INTO "TEST_JG" VALUES ('35004', '0x0004,');
INSERT INTO "TEST_JG" VALUES ('35005', '0x0002,0x0005');

最后的语句为：

select DISTINCT id  from (
select * from 
(
  SELECT DISTINCT * from (
  SELECT  A.ID, REGEXP_SUBSTR (A.LX,'[^,]+', 1, LEVEL, 'i' ) AS STR   FROM TEST_SETTING A  CONNECT BY LEVEL <= LENGTH (A.LX) - LENGTH ( REGEXP_REPLACE( A.LX,',','')) + 1
  )  ORDER BY ID ASC
 )a 
intersect 
select * from (
  SELECT DISTINCT * from (
  SELECT  A.ID, REGEXP_SUBSTR (A.LX,'[^,]+', 1, LEVEL, 'i' ) AS STR   FROM TEST_JG A  CONNECT BY LEVEL <= LENGTH (A.LX) - LENGTH ( REGEXP_REPLACE( A.LX,',','')) + 1 
 )  ORDER BY ID ASC 
)b

)

功能是实现了。

不过数据量多一点的话效率比较低。

不知道各位有还有什么好的实现方法，请多多指教。

谢谢