select
col_hash, column1||decode(column2,null,'',' '||column2)||decode(a.column3,null,'',' '||a.column3)||decode(column4,null,'',' '||column4)||
decode(column5,null,'',' '||column5)||decode(column6,null,'',' '||column6)||decode(column7,null,'',' '||column7)||
decode(column8,null,'',' '||column8)||decode(column9,null,'',' '||column9) column_total, a.TIMESTAMP, ACTIVE
from
new_table a
where 
(
(upper(column1||' '||column2||' '||a.column3||' '||column4||' '||column5||' '||column6||' '||column7||' '||column8||' '||column9) like upper('%3932%jeram%')) or
(upper(column1||' '||column2||' '||a.column3||' '||column4||' '||column5||' '||column6||' '||column7||' '||column8||' '||column9) like upper('%137%11400%')) or
(upper(column1||' '||column2||' '||a.column3||' '||column4||' '||column5||' '||column6||' '||column7||' '||column8||' '||column9) like upper('%3932%jeram%')) or
(upper(column1||' '||column2||' '||a.column3||' '||column4||' '||column5||' '||column6||' '||column7||' '||column8||' '||column9) like upper('%2684%17200%')) or
(upper(column1||' '||column2||' '||a.column3||' '||column4||' '||column5||' '||column6||' '||column7||' '||column8||' '||column9) like upper('%2116%jenjarom%')) or
(upper(column1||' '||column2||' '||a.column3||' '||column4||' '||column5||' '||column6||' '||column7||' '||column8||' '||column9) like upper('%11%KENANGA 17%')) or
(upper(column1||' '||column2||' '||a.column3||' '||column4||' '||column5||' '||column6||' '||column7||' '||column8||' '||column9) like upper('%45%Lengkok Perajurit%')) or
(upper(column1||' '||column2||' '||a.column3||' '||column4||' '||column5||' '||column6||' '||column7||' '||column8||' '||column9) like upper('%8%4%D%KAMPUNG TASIK TAMBAHAN%')) or
(upper(column1||' '||column2||' '||a.column3||' '||column4||' '||column5||' '||column6||' '||column7||' '||column8||' '||column9) like upper('4%jebat 33%skudai%JOHOR%'))
)
and active='Y' and nvl(col_hash,'ZZZ') not in (
'1396964261', '1844018095', '2185626809', '2242239952', '2384319983', '2490635265', '26009072', '2737860531', '2812037192',
'3330281279', '3764421409', '4053948423', '4093097088', '4171476067', '455130727', '73891903', '934007476', '964534197', '3643395552',
'560615978', '4277989715', '4006992506', '2200381667', '522855221', '3706781957', '1714768764', '83277617', '863548372', '702209962'
)
;

CREATE TABLE newnew (
  RESOURCE_INST_ID NUMBER(9,0)    NOT NULL,
  column1          VARCHAR2(40)   NULL,
  column2          VARCHAR2(40)   NULL,
  column3          VARCHAR2(30)   NULL,
  column4          VARCHAR2(100)  NULL,
  column5          VARCHAR2(123)  NULL,
  column6          VARCHAR2(60)   NULL,
  column7          VARCHAR2(10)   NULL,
  column8          VARCHAR2(60)   NULL,
  column9          VARCHAR2(40)   NULL,
  MAIN_col         VARCHAR2(100)  NOT NULL,
  TIMESTAMP        DATE           DEFAULT sysdate NULL,
  ACTIVE           CHAR(1)        NULL,
  col_HASH         VARCHAR2(50)   NOT NULL,
  FDC_ID           VARCHAR2(50)   NULL
)
/

CREATE INDEX newnew_IDX
  ON newnew (
    col_HASH
  )
/

CREATE INDEX newnew_IDX2
  ON newnew (
    FDC_ID
  )
/




ALTER TABLE newnew
  ADD CONSTRAINT newnew_PK PRIMARY KEY (
    RESOURCE_INST_ID,
    MAIN_col,
    col_HASH
  )
/

CREATE INDEX new_table _IDX3
  ON new_table (
    column1||' '||column2||' '||column3||' '||column4||' '||column5||' '||column6||' '||column7||' '||column8||' '||column9
  )
/

orclz>
orclz> create index loci on dept(loc) indextype is ctxsys.context;

Index created.

orclz> set autot on exp
orclz>
orclz> select * from dept where contains(loc,'YORK') > 0;

         DEPTNO DNAME          LOC
--------------- -------------- -------------
             10 ACCOUNTING     NEW YORK


Execution Plan
----------------------------------------------------------
Plan hash value: 3272822835

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

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

   2 - access("CTXSYS"."CONTAINS"("LOC",'YORK')>0)

orclz>

SQL*Plus: Release 11.2.0.1.0 Production