Indexes

[helpme]

Hi,
I have a table with xactly 391900 rows in it...
normally my web page make a search for a column thats a varchar2(80) and is indexed.
When i make a select statment in this table ..it takes more or less 10-15 secs to give the result...
Is this a normal behavior that indexes for longer columns would take some time to give results os is it like my index is gone corrupt....

p.s it takes the same time in sql plus too

thanx

[pando]

can you get the explain plan and paste it here?
And table structure, number of cllumns, what columnd is/are indexed etc

[helpme]

operation ---table access
option--- full


does this mean that it doesnot use the index????

cost is 8857

i use the query:

SELECT * FROM table_nameWHERE UPPER(name) LIKE '%name%'

does it not use the index if we use any ora defined functions like upper?

if so..is there a way to make it use the index?

thanx

[helpme]

name is indexed.....

[pando]

I am not very expert with tuning SQL but I think if you use LIKE then most probably you are forcing a Full Table Scan since Oracle has to check every single row to meet your like condition, you may consider use of Intermedia, used for text search

[Edited by pando on 08-14-2001 at 06:44 AM]

[helpme]

so is it not a good idea to add indexes for a varchar column?

So is there any possible way to get the result faster with it?

thanx again....

[dknight]

Another possible way to speed performance is to put everything into your database in upper case and then use the UPPER operator on your search criterion.

Good luck.

[helpme]

i did that..
but still it does the complete table scan and takes the same amt of time...

[pando]

I dont mean it is not a good idea, the bad idea is your query, if instead of like you use = most probably Oracle will do an index scan

But if you really want to use LIKE operator you have to use Intermedia

[truma1]

Try analyzing the table and index and using hints