Home » SQL & PL/SQL » SQL & PL/SQL » Query with LIKE too SLOW...
icon6.gif  Query with LIKE too SLOW... [message #682791] Fri, 20 November 2020 04:11 Go to next message
aimy
Messages: 213
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
  )
/
Re: Query with LIKE too SLOW... [message #682792 is a reply to message #682791] Fri, 20 November 2020 04:30 Go to previous messageGo to next message
John Watson
Messages: 8431
Registered: January 2010
Location: Global Village
Senior Member
You might manage to make that query indexable if you concatenate the columns into one virtual column and index that with a Text index,
https://docs.oracle.com/en/database/oracle/oracle-database/19/ccapp/index.html
Re: Query with LIKE too SLOW... [message #682793 is a reply to message #682792] Fri, 20 November 2020 04:34 Go to previous messageGo to next message
aimy
Messages: 213
Registered: June 2006
Senior Member
Thanks John.

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

[Updated on: Fri, 20 November 2020 04:35]

Report message to a moderator

Re: Query with LIKE too SLOW... [message #682794 is a reply to message #682793] Fri, 20 November 2020 04:36 Go to previous messageGo to next message
John Watson
Messages: 8431
Registered: January 2010
Location: Global Village
Senior Member
Are you trolling? Being deliberately stupid in an attempt to make people angry? Or is not deliberate?
Re: Query with LIKE too SLOW... [message #682795 is a reply to message #682794] Fri, 20 November 2020 04:39 Go to previous messageGo to next message
aimy
Messages: 213
Registered: June 2006
Senior Member
sorry Sir. I didn't mean too.

I just visited the link you provided and this is something new for me.

Sorry again.

At first I really thought that's the way you meant by indexing those columns.

Thanks again for your assistance Smile

[Updated on: Fri, 20 November 2020 05:31]

Report message to a moderator

Re: Query with LIKE too SLOW... [message #682796 is a reply to message #682795] Fri, 20 November 2020 05:37 Go to previous messageGo to next message
Michel Cadot
Messages: 67572
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can indeed try using this function-based index but as you compare with patterns starting '%' I doubt Oracle will choose the index over a table scan above all when you have other conditions.

In addition, the use of NVL condition "nvl(col_hash,'ZZZ') not in ..." will prevent Oracle to use an index on col_hash unless you index the expression (which does not mean Oracle will necessarily choose this index, it depends on carnality and if histograms are there).

Re: Query with LIKE too SLOW... [message #682798 is a reply to message #682795] Fri, 20 November 2020 06:14 Go to previous messageGo to next message
John Watson
Messages: 8431
Registered: January 2010
Location: Global Village
Senior Member
aimy wrote on Fri, 20 November 2020 10:39
sorry Sir. I didn't mean too.

I just visited the link you provided and this is something new for me.

Sorry again.

At first I really thought that's the way you meant by indexing those columns.

Thanks again for your assistance Smile
A very simple example how Text indexes work to give the effect of indexing your %...% filters:
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>
Re: Query with LIKE too SLOW... [message #682802 is a reply to message #682798] Fri, 20 November 2020 09:14 Go to previous messageGo to next message
aimy
Messages: 213
Registered: June 2006
Senior Member
Thank you so much.

Unfortunately, I guess this feature is not supported with my Oracle version?

SQL*Plus: Release 11.2.0.1.0 Production
Re: Query with LIKE too SLOW... [message #682804 is a reply to message #682802] Fri, 20 November 2020 09:58 Go to previous messageGo to next message
John Watson
Messages: 8431
Registered: January 2010
Location: Global Village
Senior Member
It dates back to release 8.
Re: Query with LIKE too SLOW... [message #682805 is a reply to message #682804] Fri, 20 November 2020 10:16 Go to previous message
Michel Cadot
Messages: 67572
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

... but you have to install the option (which was named Oracle Text in this version).

[Updated on: Fri, 20 November 2020 10:18]

Report message to a moderator

Previous Topic: UPDATE query with three table join (3 merged)
Next Topic: Need SQL Logic For Comparing Data Between Two Tabless
Goto Forum:
  


Current Time: Fri Dec 04 15:49:09 CST 2020