Search every field of particular table for a list of values in Oracle

Question

Is it possible to search every field of particular table for a list of values in Oracle.The requirement is to extract all details from table that matches against the list in any column of the table.The table holds huge amount of data and I require to extract the data for a period of 6 months

You may start from here: [Search All Fields In All Tables For A Specific Value (Oracle)](http://stackoverflow.com/q/208493/319875) — Florin Ghita, Jun 21 '13 at 07:52
How many columns do you want to search? How long ist the list of values? What is the data type (numbers, char, dates?) — wolφi, Jun 21 '13 at 08:04
hi wol,character & varchar datatype and klist is around 120 values — Sam, Jun 21 '13 at 08:07
Which SQL client are you using? Some have built-in features for that. — , Jun 21 '13 at 09:07

score 0 · Answer 1 · answered Jun 21 '13 at 08:41

I'd create a table to hold the search values

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');

and then construct the long query to check column by column:

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);

If the number of columns is huge, I'd use SQL to help writing the query:

SELECT 'OR '||column_name||' IN (SELECT x FROM s)' AS line
  FROM user_tab_columns 
 WHERE table_name = 'large_table';

@a_horse_with_no_name: Ah, come on, don't pick on me. It works. The table name is not case sensitive in DBMS_STATS (except for "mixedCaseTableNames", of course). — wolφi, Jun 21 '13 at 09:43

score 0 · Answer 2 · answered Jun 21 '13 at 09:04

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
*/

With the amount information that we have there is no wrong answer.

Add some where condition. Hopefully you have some index created - this is good column to use.

Do not scan whole table - use date ranges and do it i.e.: day by day or month by month.

Search every field of particular table for a list of values in Oracle

2 Answers2