SELECT COL1, COL2, COL3 FROM ADDRESSINFO 
WHERE 
(COL4 like '%1%' OR COL5 like '%1%') 
AND (COL6 like '%STEADING%' OR COL7 like '%STEADING%' OR COL8 like '%STEADING%') 
AND (COL9 LIKE '%DURHAM%' OR COL10 LIKE '%DURHAM%');

Total number of records in ADDRESSINFO table  = 2500000
Time taken to retrieve the data   = 23 Sec

Total number of records in ADDRESSINFO table  = 30000000
Time taken to retrieve the data   = 15 mins

CREATE INDEX IDX_COL4 ON ADDRESSINFO
(CASE  WHEN "COL4" LIKE '%1%' THEN 1 END );

CREATE INDEX IDX_COL6 ON ADDRESSINFO
(CASE  WHEN "COL6" LIKE '%STEADING%' THEN 1 END );

SELECT COL1, COL2, COL3 FROM ADDRESSINFO
WHERE
case when COL6 like '%STEADING%' then 1 end is not null

SELECT COL1, COL2, COL3 FROM ADDRESSINFO
WHERE
(
case when COL6 like '%STEADING%' then 1 end is not null  
OR 
case when COL7 like '%STEADING%' then 1 end is not null 
)

  1* select last_name, job_id from employees where last_name like 'T%' or job_id like '%MAN%'
SQL> /

LAST_NAME		  JOB_ID
------------------------- ----------
Cambrault		  SA_MAN
Errazuriz		  SA_MAN
Fripp			  ST_MAN
Hartstein		  MK_MAN
Kaufling		  ST_MAN
Mourgos 		  ST_MAN
Partners		  SA_MAN
Raphaely		  PU_MAN
Russell 		  SA_MAN
Taylor			  SA_REP
Taylor			  SH_CLERK

LAST_NAME		  JOB_ID
------------------------- ----------
Tobias			  PU_CLERK
Tucker			  SA_REP
Tuvault 		  SA_REP
Vollman 		  ST_MAN
Weiss			  ST_MAN
Zlotkey 		  SA_MAN

17 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1604305746

--------------------------------------------------------------------------------
-----------

| Id  | Operation	       | Name		  | Rows  | Bytes | Cost (%CPU)|
 Time	  |

--------------------------------------------------------------------------------
-----------

|   0 | SELECT STATEMENT       |		  |    10 |   170 |	3  (34)|
 00:00:01 |

|*  1 |  VIEW		       | index$_join$_001 |    10 |   170 |	3  (34)|
 00:00:01 |

|*  2 |   HASH JOIN	       |		  |	  |	  |	       |
	  |

|   3 |    INDEX FAST FULL SCAN| EMP_JOB_IX	  |    10 |   170 |	1   (0)|
 00:00:01 |

|   4 |    INDEX FAST FULL SCAN| EMP_NAME_IX	  |    10 |   170 |	1   (0)|
 00:00:01 |

--------------------------------------------------------------------------------
-----------


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("JOB_ID" LIKE '%MAN%' OR "LAST_NAME" LIKE 'T%')
   2 - access(ROWID=ROWID)

SELECT COL1, COL2, COL3 FROM ADDRESSINFO
WHERE
(
(case when COL6 like '%STEADING%' then 1 end) = 1
OR 
(case when COL7 like '%STEADING%' then 1 end) = 1
)

SELECT COL1, COL2, COL3 FROM ADDRESSINFO
WHERE case when COL6 like '%STEADING%' then 1 end is not null  
UNION 
SELECT COL1, COL2, COL3 FROM ADDRESSINFO
WHERE case when COL7 like '%STEADING%' then 1 end is not null 

  SQL> SELECT COL1, COL2, COL3 FROM ADDRESSINFO
  2  WHERE
  3  (
  4  (CASE WHEN COL6 LIKE '%STEADING%' THEN 1 END IS NOT NULL)
  5  OR
  6  (CASE WHEN COL7 LIKE '%STEADING%' THEN 1 END IS NOT NULL)
  7* );
COL1			COL2			COL3 
------------------------------------------------------
DH8 6GA                BK8 61A			LM612
DI8 6LA                BB8 62A			LM613
DP8 6PA                BL8 63A			LM614
3 rows selected.

Elapsed: 00:00:24.48

SQL> SELECT COL1, COL2, COL3 FROM ADDRESSINFO
2  WHERE
3  CASE WHEN COL6 LIKE '%STEADING%' THEN 1 END IS NOT NULL
4  UNION
5  SELECT COL1, COL2, COL3 FROM ADDRESSINFO
6  WHERE
7  CASE WHEN COL7 LIKE '%STEADING%' THEN 1 END IS NOT NULL ;
COL1			COL2			COL3 
------------------------------------------------------
DH8 6GA                BK8 61A			LM612
DI8 6LA                BB8 62A			LM613
DP8 6PA                BL8 63A			LM614

3 rows selected.
Elapsed: 00:00:00.85

Rows		Plan
------		------------------
    253040
SELECT STATEMENT
    253040
 TABLE ACCESS FULL ADDRESSINFO

Rows		Plan
------		------------------
    259528
SELECT STATEMENT

    259528
 SORT UNIQUE

  UNION-ALL

    129764
   TABLE ACCESS BY INDEX ROWID ADDRESSINFO

       108
    INDEX FULL SCAN IDX_COL6

    129764
   TABLE ACCESS BY INDEX ROWID ADDRESSINFO

       182
    INDEX FULL SCAN IDX_COL7

SQL> explain plan for select * from dual;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3543395131

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

8 rows selected.

SQL> 

PLAN_TABLE_OUTPUT
Plan hash value: 2047210256
-----------------------------------------------------------------------------------------
| Id  | Operation         | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                     |   253K|  3706K| 49798   (1)| 00:09:58 |
|*  1 |  TABLE ACCESS FULL| ADDRESSINFO         |   253K|  3706K| 49798   (1)| 00:09:58 |
-----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(CASE  WHEN "COL6" LIKE '%STEADING%' THEN 1 END  IS NOT NULL OR 
              CASE  WHEN "COL7" LIKE '%STEADING%' THEN 1 END  IS NOT NULL)

PLAN_TABLE_OUTPUT
Plan hash value: 2438991125
 
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                     |   259K|  3041K|       |  1357  (52)| 00:00:17 |
|   1 |  SORT UNIQUE                  |                     |   259K|  3041K|     9M|  1357  (52)| 00:00:17 |
|   2 |   UNION-ALL                   |                     |       |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| ADDRESSINFO         |   129K|  1520K|       |    69   (0)| 00:00:01 |
|*  4 |     INDEX FULL SCAN           | IDX_COL6            |   108 |       |       |     1   (0)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID| ADDRESSINFO	    |   129K|  1520K|       |    96   (0)| 00:00:02 |
|*  6 |     INDEX FULL SCAN           | IDX_COL7            |   182 |       |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - filter(CASE  WHEN "COL6" LIKE '%STEADING%' THEN 1 END  IS NOT NULL)
   6 - filter(CASE  WHEN "COL7" LIKE '%STEADING%' THEN 1 END  IS NOT NULL)

CREATE INDEX IDX_COL4 ON ADDRESSINFO
(CASE  WHEN "COL4" LIKE '%1%' THEN 1 END )

CREATE INDEX IDX_COL5 ON ADDRESSINFO
(CASE  WHEN "COL5" LIKE '%1%' THEN 1 END )

 /*+ index(col6 IDX_COL6) index(col7 IDX_COL7) */ 

SCOTT@orcl_11gR2> CREATE TABLE addressinfo
  2    (col1   NUMBER,
  3  	col2   NUMBER,
  4  	col3   NUMBER,
  5  	col4   VARCHAR2 (10),
  6  	col5   VARCHAR2 (18),
  7  	col6   VARCHAR2 (12),
  8  	col7   VARCHAR2 (12),
  9  	col8   VARCHAR2 (12),
 10  	col9   VARCHAR2 (10),
 11  	col10  VARCHAR2 (10))
 12  /

Table created.

SCOTT@orcl_11gR2> INSERT INTO addressinfo VALUES
  2  (1, 2, 3,
  3  '1', '1',
  4  'A STEADING B', 'C STEADING D', 'E STEADING F',
  5  'G DURHAM H', 'I DURHAM J')
  6  /

1 row created.

SCOTT@orcl_11gR2> INSERT INTO addressinfo VALUES
  2  (4, 5, 6,
  3  '1', '2',
  4  'A STEADING B', 'SOMEBODY', 'NOBODY',
  5  'G DURHAM H', 'WHOMEVER')
  6  /

1 row created.

SCOTT@orcl_11gR2> INSERT INTO addressinfo VALUES
  2  (7, 8, 9,
  3  '2', '1',
  4  'SOMEBODY', 'B STEADING A', 'NOBODY',
  5  'WHOMEVER', 'H DURHAM B')
  6  /

1 row created.

SCOTT@orcl_11gR2> INSERT INTO addressinfo VALUES
  2  (10, 11, 12,
  3  '2', '2',
  4  'SOMEBODY', 'ANYBODY', 'NOBODY',
  5  'WHOMEVER', 'WHATEVER')
  6  /

1 row created.

SCOTT@orcl_11gR2> INSERT INTO addressinfo
  2  SELECT object_id, null, null,
  3  	    SUBSTR (object_name, 1, 10), object_type, status,
  4  	    SUBSTR (owner, 1, 10), null, null, null
  5  FROM   all_objects
  6  /

72702 rows created.

SCOTT@orcl_11gR2> BEGIN
  2    CTX_DDL.CREATE_PREFERENCE
  3  	 ('address_datastore',
  4  	  'MULTI_COLUMN_DATASTORE');
  5    CTX_DDL.SET_ATTRIBUTE
  6  	 ('address_datastore',
  7  	  'COLUMNS',
  8  	  'col4, col5, col6, col7, col8, col9, col10');
  9    CTX_DDL.CREATE_SECTION_GROUP
 10  	 ('address_sg',
 11  	  'basic_section_group');
 12    CTX_DDL.ADD_FIELD_SECTION ('address_sg', 'col4',  'col4',  TRUE);
 13    CTX_DDL.ADD_FIELD_SECTION ('address_sg', 'col5',  'col5',  TRUE);
 14    CTX_DDL.ADD_FIELD_SECTION ('address_sg', 'col6',  'col6',  TRUE);
 15    CTX_DDL.ADD_FIELD_SECTION ('address_sg', 'col7',  'col7',  TRUE);
 16    CTX_DDL.ADD_FIELD_SECTION ('address_sg', 'col8',  'col8',  TRUE);
 17    CTX_DDL.ADD_FIELD_SECTION ('address_sg', 'col9',  'col9',  TRUE);
 18    CTX_DDL.ADD_FIELD_SECTION ('address_sg', 'col10', 'col10', TRUE);
 19  END;
 20  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> ALTER TABLE addressinfo ADD (search_cols  VARCHAR2 (1))
  2  /

Table altered.

SCOTT@orcl_11gR2> CREATE INDEX address_idx
  2  ON addressinfo (search_cols)
  3  INDEXTYPE IS CTXSYS.CONTEXT
  4  PARAMETERS
  5    ('datastore	address_datastore
  6  	 section group	address_sg')
  7  /

Index created.

SCOTT@orcl_11gR2> SET AUTOTRACE ON EXPLAIN
SCOTT@orcl_11gR2> SELECT col1, col2, col3
  2  FROM   addressinfo
  3  WHERE  CONTAINS
  4  	      (search_cols,
  5  	       '(1 WITHIN col4 OR 1 WITHIN col5) AND
  6  		(steading WITHIN col6 OR steading WITHIN col7 OR steading WITHIN col8) AND
  7  		(durham WITHIN col9 OR durham WITHIN col10)') > 0
  8  /

      COL1       COL2       COL3
---------- ---------- ----------
         1          2          3
         4          5          6
         7          8          9

3 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2524500015

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |    32 |  1696 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ADDRESSINFO |    32 |  1696 |     4   (0)| 00:00:01 |
|*  2 |   DOMAIN INDEX              | ADDRESS_IDX |       |       |     4   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CTXSYS"."CONTAINS"("SEARCH_COLS",'(1 WITHIN col4 OR 1 WITHIN col5)
              AND            (steading WITHIN col6 OR steading WITHIN col7 OR steading WITHIN
              col8) AND            (durham WITHIN col9 OR durham WITHIN col10)')>0)

Note
-----
   - dynamic sampling used for this statement (level=2)

SCOTT@orcl_11gR2>

Test_DB> SELECT col1,col2,col3,col4,col5,col6,col7,col8,col9,col10
  2      FROM   addressinfo
  3      WHERE  CONTAINS
  4             (search_cols,
  5             '(1 WITHIN col4 OR 1 WITHIN col5) AND
  6             (steading WITHIN col6 OR steading WITHIN col7 OR steading WITHIN col8) AND
  7             (durham WITHIN col9 OR durham WITHIN col10)') > 0
  8  /
no rows selected
Elapsed: 00:00:00.01

Test_DB>SELECT col1,col2,col3,col4,col5,col6,col7,col8,col9,col10
  2  FROM   addressinfo
  3  WHERE
  4  (col4 like '%1%' OR col5 like '%1%')
  5  AND (col6 like '%STEADING%' OR col7 like '%STEADING%' OR col8 like '%STEADING%')
  6  AND (col9 LIKE '%DURHAM%' OR col10 LIKE '%DURHAM%');

COL1	COL2			COL3	 COL4	COL5	COL6		COL7		COl8	COl9	COl10
----	----			----	 ----	----	----		----		----	----	----
Pst	olptsgb20		SP1 0IK	 	1	UNNAMED 	THE STEADINGS			DURHAM
Pst	ossv7676545720		KY2 8BG		1	UNNAMED 	THE STEADINGS			DURHAM
Pst	gpgb75674567465		JH4 6TR		1	UNNAMED 	THE STEADINGS			DURHAM
Pst	lmg4545293168920	LO9 3PI		1	UNNAMED 	THE STEADINGS			DURHAM

4 rows selected.
Elapsed: 00:00:23.23