Analyze table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Analyze table

  1. #1
    Join Date
    Sep 2000
    Posts
    384
    Hi all,
    i have a simple query that returns records based on the values passed and the values passed are the primary key columns. when i get the explain plan for this query it gives a full table scan.after i delete the statistics of thta particular table then it gives aunique scan,
    i didn't understant the concept of analyzing the tables and the way they are related to the optimization of queries(i mean unique scan and full table scan when we get the explain plans)
    thanks a lot,
    Radhakrishnan.M

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    What is your optimizer mode? If it was a cost based optimization, then it would mean that it would have been cheaper to do the full table scan, rather than unique scan based on the statistics. Did you also analyze your pk index. If not analyze that and try the same querry.

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    Join Date
    Sep 2000
    Posts
    384
    my optimizer is a cost base doptimizer...
    i have analyzed the index and tried but it still give the full table scan
    Radhakrishnan.M

  4. #4
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    TRY setting the OPTIMIZER_MODE=FIRST_ROWS and if this doesn't bring out any improvement then it means that cost of scanning the entire table is less than that of using the index.

    If you really want the index scan to look more effective for the optimizer then you can set the OPTIMIZER_INDEX_COST_ADJ to any value less than 100, that would tell the OPTIMIZER that using index would be more efficient when comparing the full table scans...

    Hope this would help you.

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  5. #5
    Join Date
    Sep 2001
    Posts
    112
    Rebuild the index and then analyze.

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