Indexes
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 28

Thread: Indexes

  1. #1
    Join Date
    Jun 2001
    Posts
    316
    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

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    can you get the explain plan and paste it here?
    And table structure, number of cllumns, what columnd is/are indexed etc

  3. #3
    Join Date
    Jun 2001
    Posts
    316
    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

  4. #4
    Join Date
    Jun 2001
    Posts
    316
    name is indexed.....

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    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]

  6. #6
    Join Date
    Jun 2001
    Posts
    316
    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....

  7. #7
    Join Date
    Oct 2000
    Location
    Germany
    Posts
    1,185
    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.
    David Knight
    OCP DBA 8i, 9i, 10g

  8. #8
    Join Date
    Jun 2001
    Posts
    316
    i did that..
    but still it does the complete table scan and takes the same amt of time...

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    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

  10. #10
    Join Date
    May 2001
    Location
    Cape Town, South Africa
    Posts
    52
    Try analyzing the table and index and using hints

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width