Select statement is causing performance issue [message #379856] |
Thu, 08 January 2009 02:53 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ind9
Messages: 65 Registered: January 2009
|
Member |
|
|
Hi,
I am facing a performance issue and the details are as given below.
CREATE INDEX Scott.IDX_Last ON Scott.Emp
(SUBSTR(Scott.Pkg.Fn_Match(Last_Name,40),1,40));
/
CREATE INDEX Scott.IDX_First ON Scott.Emp
(SUBSTR(Scott.Pkg.Fn_Match(First_Name,40),1,40))
/
I have a function based index for Last_Name and First_Name. Like shown above.
Select *
From Emp
WHere (SUBSTR (Pkg.Fn_Match (Last_Name, 40), 1, 40) Like 'In%'
OR
SUBSTR(Pkg.Fn_Match (First_Name,40), 1, 40) Like 'In%')
/
no rows selected
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=124)
1 0 TABLE ACCESS (FULL) OF 'EMP' (Cost=3 Card=1
Bytes=124)
This query is been using in the production environment. It's causing performance problems.
When i run this query it is going for full table scan and it is taking more time to execute.
Please tell me why it is not using index.
What should i do to make this query to use index? Its very urgent.
Thanks
|
|
|
|
Re: Select statement is causing performance issue [message #379859 is a reply to message #379858] |
Thu, 08 January 2009 03:06 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ind9
Messages: 65 Registered: January 2009
|
Member |
|
|
Hi Michel,
Thanks for your response.
SUBSTR (Pkg.Fn_Match (Last_Name, 40), 1, 40) Like '&Last_Nm'
Input of the user might exceed 40 characters also that is why we used substr
And we have gather statistics. Is there any way can i make the above query to use index instead of going for full table scan.
Thanks
|
|
|
|
|
|
Re: Select statement is causing performance issue [message #379876 is a reply to message #379869] |
Thu, 08 January 2009 03:59 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ind9
Messages: 65 Registered: January 2009
|
Member |
|
|
Sorry i am not getting any directions to solve this issue.
My concern is the above specified query has to use function based index which is created.
It is not using index and its going for full table scan.
I hope i am clear with the problem.
Thanks.
|
|
|
Re: Select statement is causing performance issue [message #379882 is a reply to message #379876] |
Thu, 08 January 2009 04:23 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68676 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
You are clear with the problem.
You don't understand what I mean.
Plan depends on value.
Each time you change the value plan may change.
There is no reason Oracle will choose an index if it thinks it is faster with a full scan.
Why doyou think it should use one (or both) of the indexes?
Regards
Michel
[Updated on: Thu, 08 January 2009 04:24] Report message to a moderator
|
|
|
Re: Select statement is causing performance issue [message #379909 is a reply to message #379882] |
Thu, 08 January 2009 06:10 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ind9
Messages: 65 Registered: January 2009
|
Member |
|
|
Hi,
Is there any way can i re-write this query.
Select *
From Emp
WHere (SUBSTR (Pkg.Fn_Match (Last_Name, 40), 1, 40) Like '&input'
OR
SUBSTR(Pkg.Fn_Match (First_Name,40), 1, 40) Like '&input')
/
no rows selected
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=124)
1 0 TABLE ACCESS (FULL) OF 'EMP' (Cost=3 Card=1
Bytes=124)
This query is been using in the production environment and if we execute this it is taking more than half hour.
The requirement is if we give the input value as say 'PO%' then
the above query has to retrieve all the last name starting with 'PO' and first_name starting with 'PO'.
Is there any way where i can rewrite this query. Kindly help
Thanks
|
|
|
|
|
|
|
|
Re: Select statement is causing performance issue [message #380041 is a reply to message #380019] |
Thu, 08 January 2009 21:33 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ind9
Messages: 65 Registered: January 2009
|
Member |
|
|
CREATE INDEX Scott.IDX_Last ON Scott.Emp
(SUBSTR(Scott.Pkg.Fn_Match(Last_Name,40),1,40));
CREATE INDEX Scott.IDX_First ON Scott.Emp
(SUBSTR(Scott.Pkg.Fn_Match(First_Name,40),1,40))
/
The reason is indexes were created by using substr. Thats why even in the queries we are using substr
|
|
|
|
Re: Select statement is causing performance issue [message #380104 is a reply to message #380020] |
Fri, 09 January 2009 03:10 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
ind9 wrote on Fri, 09 January 2009 02:43 | Unfortunately we wont get execution plans from production environment.
This execution plan is related to development environment. But query is same.
Thanks
|
That's what I thought.
How many rows have you got in emp on your test machine and how many have you got in prod?
I'm guessing there is a very large difference between the two counts.
It helps, when doing performance tuning, to have a dev/test machine with similar data volumes to production.
I very much doubt there is anything you can do to make oracle use the indexes in your dev environment because you don't have enough data to make indexes worth it's while.
|
|
|