LIKE Operator

Database
Enthusiast

LIKE Operator

Hello ,

 im trying to execute a query which is written like  below

select

product_id ,product_name ,Location ,country ,coalesce('0'||ph_num) as Contact_num

from  product  prd

inner join  contact_details  cd  on prd.ph_num like  cd.phone_num ||'%'  ;

this query is taking time in executing also it is consuming all the system resources ,so the other users are unable to execute the queries.

please suggest me how can we resolve it

5 REPLIES
Enthusiast

Re: LIKE Operator

Any one please suggest me it urgent please
WAQ
Enthusiast

Re: LIKE Operator

I think thats probably because of the resulting product join. Please see the explain plan for more detail.

Enthusiast

Re: LIKE Operator

yes i know that it will end with product join ,i need a suggestion how to avoid these product join for this query any suggestions please
Enthusiast

Re: LIKE Operator

Consider a SINGLE TABLE JOIN INDEX of

CREATE JOIN INDEX jidx_p AS
SELECT
product_id
,product_name
,Location
,country
,coalesce('0'||ph_num) as Contact_num
FROM product
PRIMARY INDEX jidx_p_pi (Contact_num);

COLLECT STATISTICS ON jidx_p INDEX jidx_p_pi;

However I think most DBS's hate joining on varchars... you'd be well advised to find some way to make it join on an integer.

Enthusiast

Re: LIKE Operator

Create  table wooosh

as

(

sel ..'cols from contact details' , phone_num || '%' as phone_num

from contact details

) primary index (phone_num ) with data ;

select

product_id ,product_name ,Location ,country ,coalesce('0'||ph_num) as Contact_num

from  product  prd

inner join  woosh  cd  on prd.ph_num =phone_NUM;

Having written above query ,I started wondering your use of %.If it is meant to be wildcard character the query might not give proper result.You might want to introduce quotes also in concatenation in that case. (ofcourse above query wuold be almost obsolete then)