|
|
| Re: like operator [message #7178 is a reply to message #7176] |
Mon, 26 May 2003 17:42   |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
|
It might - depends on use of bind variables, statistics, optimizer mode, and the actual value searched for (position of the %).
|
|
|
|
| Re: like operator [message #7376 is a reply to message #7178] |
Mon, 09 June 2003 15:43 |
kiran
Messages: 503
Registered: July 2000
|
Senior Member |
|
|
Thanks Todd,
I have a query like this
Select inv_cust_no_720,inv_no_720,inv_finder_no_720
from invoice
Where inv_paid_flag_720 = 'O' and
(
INV_NO_720 LIKE ' 530874' or
INV_NO_720 LIKE ' 530874%' or
INV_NO_720 LIKE ' 530874%' or
INV_NO_720 LIKE ' 530874%' or
INV_NO_720 LIKE ' 530874%' or
INV_NO_720 LIKE ' 530874%' or
INV_NO_720 LIKE ' 530874%' or
INV_NO_720 LIKE ' 530874%' or
INV_NO_720 LIKE '530874%')
(Slow)
This query is taking almost a minute to execute , but if comment out the second like it executes fast and i get results within few seconds like this
Select inv_cust_no_720,inv_no_720,inv_finder_no_720
from invoice
Where inv_paid_flag_720 = 'O' and
(
INV_NO_720 LIKE ' 530874' or
--INV_NO_720 LIKE ' 530874%' or
INV_NO_720 LIKE ' 530874%' or
INV_NO_720 LIKE ' 530874%' or
INV_NO_720 LIKE ' 530874%' or
INV_NO_720 LIKE ' 530874%' or
INV_NO_720 LIKE ' 530874%' or
INV_NO_720 LIKE ' 530874%' or
inv_no_720 LIKE '530874%')
(Very fast )
why it behaves like this ? , INV_NO_720 is VARCHAR2(14)
and indexed and it is not a primary key.
one more interesting thing is... it takes more time in production than testbase eventhough testbase has more invoices with paid_flag as 'O' ,
does this select statement making use of index ? if not how can we ensure it makes use of index ?
Thanks
Kiran
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
