DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Fast Full Scan

  1. #1
    Join Date
    Dec 2002
    Posts
    32

    Fast Full Scan

    Hi
    I have a table for which the primary key are columns a,b,c,d. I created an index for columns b and c to faster my query which uses only these two columns in where clause. But the query is not using the index that i created for it. Instead it uses the FAST FULL SCAN of the primary key and it becomes very slow. How can rewrite the query so that it uses the index that i created for it?

    Thanks
    Sajeev

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    did you analyze the index

  3. #3
    Join Date
    Dec 2002
    Posts
    32
    Ys i analyzed the entire schema..
    Sajeev

  4. #4
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    try using HINT
    Rajeev Suri

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    How selective are the columns? What % of rows is your query returning from the table? What columns are in the select clause?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Personally, I find the whole situation problematic.

    First, you should not have a 4-part PK, IMHO. Single-field surrogate PKs are the way to go.

    Second, why would you possibly need to define a left-leading sub-part of a unique index???? The only thing I can think of is that the fourth column is actually a large string. This is another big no-no.

    So, you've got a poorly-designed table with a 4-part key and the 4th part is a big string. So, using the index with that string is causing performance problems. No kidding? But to fix those problems, you want to introduce a completely redundant index?

    Fixing your table would be a better solution.

    But to address the problem at hand, how about you provide a little info about the table, the indexes and the query/plan so we can see if there's something to be done?

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  7. #7
    Join Date
    Dec 2002
    Location
    India
    Posts
    34
    Hi,


    I have faced with same type of problem..
    if you want to use the indexes created on the table
    case 1

    SELECT /* INDEX_COMBINE(table_name index1 index2) */ it uses bit map access path for the table..
    the cost would be more in the explain plan

    case 2:

    when u remove the hint its going to use full table scan.. just run the explain plan for it. the cost would be less

    the ouput i was fetching was 16% of the rows .. so it has to use the indexes created on the table..

    but the cost based optimizer uses the path which has less cost..

    Note: The Tables should be analyzed

    Thanks
    Sridhar

  8. #8
    Join Date
    Mar 2002
    Posts
    534
    Originally posted by ksridhar
    the ouput i was fetching was 16% of the rows .. so it has to use the indexes created on the table..
    Why do you mean that with 16% it would have to use the index? With 16% the probability that your data are spread over all or most data blocks of that table is pretty high. Which means it would have any way to read all the data blocks.

    So my question is: how did you check that the use of indexes is better than a fts? Which was the AVG_ROW_LEN of that table?
    Last edited by mike9; 05-15-2003 at 04:05 AM.

  9. #9
    Join Date
    Dec 2002
    Location
    India
    Posts
    34
    how can i check whether my data is spread across many blocks??

    I had run the queries using the hints and without using hints..
    the query was fast when i use hints.

    Properties of the table

    The Avg_Row_Len for the table is 114
    No of rows : 1394000
    Blocks : 33905
    empty_blocks : 159
    Avg_space :254

  10. #10
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Do you have any indexes? If you do look clustering factor, if it's almost as big as number of rows then your blocks are spread randomly, if it is as big as number of blocks then your blocks are nicely packed

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