CREATE TABLE s (x VARCHAR2(30) PRIMARY KEY) ORGANIZATION INDEX;
INSERT INTO s VALUES ('x');
INSERT INTO s VALUES ('y');
INSERT INTO s VALUES ('z');
COMMIT;
EXEC DBMS_STATS.GATHER_TABLE_STATS(null, 's');
然后构造长查询以逐列检查:
SELECT * FROM large_table
WHERE col1 IN (SELECT x FROM s)
OR col2 IN (SELECT x FROM s)
...
OR colx IN (SELECT x FROM s);
如果列数很大,我会使用SQL来帮助编写查询:
SELECT 'OR '||column_name||' IN (SELECT x FROM s)' AS line
FROM user_tab_columns
WHERE table_name = 'large_table';
0
CREATE TABLE large_table
(
col1 VARCHAR2(100)
, col2 VARCHAR2(100)
, col3 VARCHAR2(100)
, col4 VARCHAR2(100)
, col5 VARCHAR2(100)
);
INSERT INTO large_table VALUES ('aa', 'bb', 'cc', 'dd', 'ee');
INSERT INTO large_table VALUES ('ax', 'bx', 'cx', 'dx', 'ex');
INSERT INTO large_table VALUES ('ay', 'by', 'cy', 'dd', 'ee');
CREATE TABLE values_to_search
(
s_value VARCHAR2(30)
);
INSERT INTO values_to_search VALUES ('aa');
INSERT INTO values_to_search VALUES ('bb');
INSERT INTO values_to_search VALUES ('cc');
INSERT INTO values_to_search VALUES ('dd');
INSERT INTO values_to_search VALUES ('ee');
SELECT CASE WHEN EXISTS(SELECT 1 FROM values_to_search s WHERE INSTR(l.col1, s.s_value) > 0 ) THEN 1 ELSE NULL END AS s1
, CASE WHEN EXISTS(SELECT 1 FROM values_to_search s WHERE INSTR(l.col2, s.s_value) > 0 ) THEN 1 ELSE NULL END AS s2
, CASE WHEN EXISTS(SELECT 1 FROM values_to_search s WHERE INSTR(l.col3, s.s_value) > 0 ) THEN 1 ELSE NULL END AS s3
, CASE WHEN EXISTS(SELECT 1 FROM values_to_search s WHERE INSTR(l.col4, s.s_value) > 0 ) THEN 1 ELSE NULL END AS s4
, CASE WHEN EXISTS(SELECT 1 FROM values_to_search s WHERE INSTR(l.col5, s.s_value) > 0 ) THEN 1 ELSE NULL END AS s5
FROM large_table l;
/*
1 1 1 1 1
1 1
*/
CREATE TABLE search_result
(
col1 VARCHAR2(100)
, col2 VARCHAR2(100)
, col3 VARCHAR2(100)
, col4 VARCHAR2(100)
, col5 VARCHAR2(100)
);
INSERT INTO search_result
SELECT /*+ PARALLEL */ *
FROM large_table l
WHERE COALESCE
(
CASE WHEN EXISTS(SELECT 1 FROM values_to_search s WHERE INSTR(l.col1, s.s_value) > 0 ) THEN 1 ELSE NULL END
, CASE WHEN EXISTS(SELECT 1 FROM values_to_search s WHERE INSTR(l.col2, s.s_value) > 0 ) THEN 1 ELSE NULL END
, CASE WHEN EXISTS(SELECT 1 FROM values_to_search s WHERE INSTR(l.col3, s.s_value) > 0 ) THEN 1 ELSE NULL END
, CASE WHEN EXISTS(SELECT 1 FROM values_to_search s WHERE INSTR(l.col4, s.s_value) > 0 ) THEN 1 ELSE NULL END
, CASE WHEN EXISTS(SELECT 1 FROM values_to_search s WHERE INSTR(l.col5, s.s_value) > 0 ) THEN 1 ELSE NULL END
) IS NOT NULL
;
-- 2 rows inserted.
/*
aa bb cc dd ee
ay by cy dd ee
*/
2 回答
我创建了一个表来保存搜索值
然后构造长查询以逐列检查:
如果列数很大,我会使用SQL来帮助编写查询:
根据我们提供的金额信息,没有错误的答案 .
添加一些where条件 . 希望您创建了一些索引 - 这是一个很好的列 .
不要扫描整个表 - 使用日期范围,即:日复一日或逐月 .