Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2004
    Location
    Lucknow
    Posts
    6

    Unanswered: index not working

    Hello all,

    I had my database crash few days back. after which I reinstalled oracle server and took import from the dump. now my query is not using the indexes. i have recreated indexes many times. still it is not using them while the query is same and data is same and indexes are there as before.

    pls suggest me the possible reasons ASAP. its urgent!!!!

    regards,
    Anvi

  2. #2
    Join Date
    Jan 2004
    Location
    India
    Posts
    191
    Hi,

    1. Are the tables are analyzed (After importing).?
    2. Oracle Optimiser make's decesion for choosing index or to make full table scan.
    3. Are quries were using indexe's in the previous database?

    Thanks,
    Pagnint
    (No need to search web before posting new question)

  3. #3
    Join Date
    Feb 2004
    Location
    Lucknow
    Posts
    6
    Yes, indexes were working in previous database. should i analyze all tables? if yes, then how?

  4. #4
    Join Date
    Jan 2004
    Location
    India
    Posts
    191
    Hi,

    analyze table table_name compute statistics;


    Thanks,
    Pagnint
    (No need to search web before posting new question)

  5. #5
    Join Date
    Feb 2004
    Location
    Lucknow
    Posts
    6
    what should i do after i have analyzed tables? what should i do with the stats?

  6. #6
    Join Date
    Jan 2004
    Location
    India
    Posts
    191
    see the explain plan for the query.
    Pagnint
    (No need to search web before posting new question)

  7. #7
    Join Date
    Feb 2004
    Location
    Lucknow
    Posts
    6
    this is not making the index work. the explain plan is still doing table access full and query cost has increased marginally now.

  8. #8
    Join Date
    Jan 2004
    Location
    India
    Posts
    191
    Hi,

    Full table scan is not a bad thing about query cost it does not have to do any thing with performance its only algorithm. Optimiser always choose a better plan.Does the query is taking long time to execute.?
    Can you post a copy of explain plan .

    Thanks,
    Pagnint
    (No need to search web before posting new question)

  9. #9
    Join Date
    Apr 2004
    Posts
    9

    /*HINTS */

    It could be possoble that it is more efficient to do a full table scan for the query. If you are using toad you can force the trace to use rule based queries.

    Also look up hints at oracle these can be used to force a query into using an index a stop a full table scan, over riding the oracle optimisation.

    Regards
    David Edwards

  10. #10
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    You could try to analyze the table for the indexed columns ... also analyze the indexes ...

    Ex: analyze table tablename estimate statistics sample 20 percent for all indexed columns;

    analyze index indexname estimate statistics sample 20 percent;

    HTH
    Gregg

  11. #11
    Join Date
    Jul 2003
    Posts
    2,296
    did you analyze the tables and the indexes?

    use this:
    PHP Code:
    declare
    begin
      dbms_stats
    .gather_schema_stats
              
    ownname          => 'USERNAME',
                
    cascade          => TRUE,
                
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE );
    end;

    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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