Home » RDBMS Server » Performance Tuning » Optimized Query with LIKE Operator (merged)  () 1 Vote
Optimized Query with LIKE Operator (merged) [message #379685] Wed, 07 January 2009 11:38 Go to next message
shankhan
Messages: 28
Registered: September 2008
Location: Pakistan
Junior Member
Dear Guys,

I have a table which contains thousands (apx 15k) of country codes like:

Country_Code Country Name
93 Afghanistan (LL)
937 Afghanistan (Mobile)
9370 Afghanistan (Mobile)
....
....

Now I have written a function to which I pass a phone number and it tells me the exact corresponding country code. The SQL statement that achieves this is given below:

SELECT code_country
FROM (SELECT country_code
FROM country_codes
WHERE phone_num LIKE country_code || '%'
ORDER BY LENGTH(country_code) DESC)
WHERE ROWNUM = 1

Since I am using the LIKE operator it always performs a FULL TABLE scan. Now I have to call this function on thousands of records for which it takes lots of time.

Is there any other best way to get the country code information from the table or we can optimze the above query.

I would really appriciate your help in this regard.


Re: Optimized Query with LIKE Operator (merged) [message #379690 is a reply to message #379685] Wed, 07 January 2009 12:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous topic:
Michel Cadot wrote on Tue, 23 December 2008 08:32
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel

And don't multipost your question.

Re: Optimized Query with LIKE Operator (merged) [message #379694 is a reply to message #379690] Wed, 07 January 2009 12:40 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Oracle can (not will) use an index if you only wildcard the end of your search string.

create table country_codes (country_code varchar2(10));

create index country_codes_ix1 on country_codes (country_code);

insert into country_codes (select to_char(object_id) from dba_objects);

commit

exec dbms_stats.gather_table_stats( user, 'COUNTRY_CODES');

--FTS
SELECT country_code
FROM country_codes
WHERE country_code LIKE '%20774%'

Plan
SELECT STATEMENT  ALL_ROWS Cost: 23  Bytes: 11,385  Cardinality: 2,277  	
	1 TABLE ACCESS FULL TABLE SCOTT.COUNTRY_CODES Cost: 23  Bytes: 11,385  Cardinality: 2,277  


-- index scan
SELECT country_code
FROM country_codes
WHERE country_code LIKE '20774%'

Plan
SELECT STATEMENT  ALL_ROWS Cost: 2  Bytes: 5  Cardinality: 1  	
	1 INDEX RANGE SCAN INDEX SCOTT.COUNTRY_CODES_IX1 Cost: 2  Bytes: 5  Cardinality: 1  

Re: Optimized Query with LIKE Operator (merged) [message #379704 is a reply to message #379685] Wed, 07 January 2009 13:20 Go to previous messageGo to next message
shankhan
Messages: 28
Registered: September 2008
Location: Pakistan
Junior Member
Sorry for All the trouble.
But it is not using index for the following query.

SELECT country_code
FROM country_codes
WHERE '20774' LIKE country_code || '%'
Re: Optimized Query with LIKE Operator (merged) [message #379707 is a reply to message #379685] Wed, 07 January 2009 13:24 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>But it is not using index for the following query.
I am not surprised by this.

>SELECT country_code FROM country_codes WHERE '20774' LIKE country_code || '%'

What EXACTLY do you hope/expect to be done with your WHERE clause?

How many rows are returned by this SELECT statement?
Re: Optimized Query with LIKE Operator (merged) [message #379711 is a reply to message #379685] Wed, 07 January 2009 13:39 Go to previous messageGo to next message
shankhan
Messages: 28
Registered: September 2008
Location: Pakistan
Junior Member
The Output I have already mentioned at the start of my question: i.e; I will be giving a phone number and the output will be a country code.

On average this query returns about 4 rows out of 10K Unique Country Codes table.

Like if I have country codes as given below:

91
912
913
914
915
....


And I write a query as given below:

SELECT country_code
FROM (SELECT country_code 
FROM country_codes
WHERE '9123434345' LIKE country_code || '%' 
ORDER BY LENGTH(country_code) DESC)
WHERE ROWNUM = 1


Then this query will be fetching two rows out of which I will get only one row with greater country code length as per my business logic.

[Updated on: Wed, 07 January 2009 13:50]

Report message to a moderator

Re: Optimized Query with LIKE Operator (merged) [message #379728 is a reply to message #379711] Wed, 07 January 2009 15:43 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9099
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_11g> CREATE TABLE country_codes
  2    (country_code  VARCHAR2(15))
  3  /

Table created.

SCOTT@orcl_11g> INSERT ALL
  2  INTO country_codes VALUES (91)
  3  INTO country_codes VALUES (912)
  4  SELECT * FROM DUAL
  5  /

2 rows created.

SCOTT@orcl_11g> INSERT INTO country_codes
  2  SELECT object_id FROM all_objects
  3  /

68724 rows created.

SCOTT@orcl_11g> CREATE INDEX test_idx ON country_codes (country_code)
  2  /

Index created.

SCOTT@orcl_11g> EXEC DBMS_STATS.GATHER_TABLE_STATS (USER, 'COUNTRY_CODES')

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> VARIABLE searchstring VARCHAR2(15)
SCOTT@orcl_11g> EXEC :searchstring := '9123434345'

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> SET TIMING ON
SCOTT@orcl_11g> SET AUTOTRACE ON
SCOTT@orcl_11g> SELECT country_code
  2  FROM   (SELECT country_code
  3  	     FROM   country_codes,
  4  		    (SELECT  SUBSTR (:searchstring, 1, LEVEL) codestring,
  5  			     LEVEL lvl
  6  		     FROM    DUAL
  7  		     CONNECT BY LEVEL <= LENGTH (:searchstring))
  8  	     WHERE  country_code = codestring
  9  	     ORDER  BY lvl DESC)
 10  WHERE  ROWNUM = 1
 11  /

COUNTRY_CODE
---------------
9123

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1804782531

----------------------------------------------------------------------------------------------
| Id  | Operation                         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |          |     1 |     9 |     4  (25)| 00:00:01 |
|*  1 |  COUNT STOPKEY                    |          |       |       |            |          |
|   2 |   VIEW                            |          |     1 |     9 |     4  (25)| 00:00:01 |
|*  3 |    SORT ORDER BY STOPKEY          |          |     1 |    36 |     4  (25)| 00:00:01 |
|   4 |     NESTED LOOPS                  |          |     1 |    36 |     3   (0)| 00:00:01 |
|   5 |      VIEW                         |          |     1 |    31 |     2   (0)| 00:00:01 |
|*  6 |       CONNECT BY WITHOUT FILTERING|          |       |       |            |          |
|   7 |        FAST DUAL                  |          |     1 |       |     2   (0)| 00:00:01 |
|*  8 |      INDEX RANGE SCAN             | TEST_IDX |     1 |     5 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM=1)
   3 - filter(ROWNUM=1)
   6 - filter(LEVEL<=LENGTH(:SEARCHSTRING))
   8 - access("COUNTRY_CODE"="CODESTRING")


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          9  consistent gets
          0  physical reads
          0  redo size
        424  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

SCOTT@orcl_11g> SET AUTOTRACE OFF
SCOTT@orcl_11g> SET TIMING OFF

Re: Optimized Query with LIKE Operator (merged) [message #379752 is a reply to message #379728] Wed, 07 January 2009 19:47 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Nice solution to a tricky problem, Barbara.

Can you move the CONNECT BY inline view so that it becomes an IN sub-query? That would make the syntax a bit more intuitive. Something like:

SELECT country_code
FROM   (
    SELECT country_code
    FROM   country_codes
    WHERE country_code IN (
        SELECT  SUBSTR (:searchstring, 1, LEVEL) codestring
        FROM    DUAL
        CONNECT BY LEVEL <= LENGTH (:searchstring)
    )
    ORDER  BY length(country_code) DESC
)
WHERE  ROWNUM = 1
/


Ross Leishman
icon7.gif  Re: Optimized Query with LIKE Operator (merged) [message #379940 is a reply to message #379685] Thu, 08 January 2009 09:54 Go to previous message
shankhan
Messages: 28
Registered: September 2008
Location: Pakistan
Junior Member
Thanks A Lot,

You guys are really outstanding and orafaq is a great problem solving and learning platform.
Previous Topic: Pga hit ratio
Next Topic: cpu usage during backup
Goto Forum:
  


Current Time: Mon Nov 04 11:04:30 CST 2024