using indexes [message #23334] |
Mon, 02 December 2002 23:15 |
Vishwanath
Messages: 4 Registered: April 2002
|
Junior Member |
|
|
I have a data of about 15 million records in the database. I have indexed the table on fields say X and Y. Both X and Y are VARCHAR2(32).Now my query is as follows:
select * from table1 where X like '+9876754' and Y like '+98%'. This query does not return even after 30 minutes.
Whereas, If i give a query like :
select * from table1 where Y like '+9876754' and X like '+98%'. This results in an immediate output with 1 record.
One thing about the data is, There are plenty of records where X is like '+9876754' and ALL the columns of Y start with '+98'.
Thanks in advance,
Vish
|
|
|
Re: using indexes [message #23335 is a reply to message #23334] |
Mon, 02 December 2002 23:40 |
Todd Barry
Messages: 4819 Registered: August 2001
|
Senior Member |
|
|
What are the execution plans of each of these queries? Also, for the parts of your query that do not contain a percent sign (%), you should use = instead of the LIKE operator.
Post the plans for each query and then we can see what the optimizer is doing. 15M rows is really not that big of a table. Even a full-table scan shouldn't take that long...
|
|
|