Home » SQL & PL/SQL » SQL & PL/SQL » Query with LIKE too SLOW...
Query with LIKE too SLOW... [message #682791] |
Fri, 20 November 2020 04:11 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
aimy
Messages: 225 Registered: June 2006
|
Senior Member |
|
|
Hi there.
What could I do for below query to return faster results..
I have delete some of the likes.. Actual like was 5 times more than this.
Thank you so much.
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 and indexes
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
)
/
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Sep 07 20:15:25 CDT 2024
|