Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2002
    Posts
    11

    Unanswered: Index vs. Full table Scan

    Hello:

    We run a Test and Prod instance that were set-up identical
    and are loaded with the same data. The problem is a query
    that processes differently in the two environments. An index
    was set-up in both environments to support the query. In one
    enviroment it works as intended. In the other environment
    the query still invokes a Full Table Scan and performance
    is poor. This seems to be a consistent problem with other tables
    and indexes as well. Any ideas?

    Thanks,

  2. #2
    Join Date
    Nov 2002
    Location
    Columbus, OH
    Posts
    9

    Re: Index vs. Full table Scan

    1. Check if the compute statistics was run.
    2. If it was then sometimes based on statistics even if there are indexes Oracle will do full tablescans which will be slower. Drop the statistics.
    3. It the statistics were not run then, compute the statistics.

  3. #3
    Join Date
    Sep 2002
    Posts
    11
    Yes, we did run an analyze on both the tables and indexes
    separately this past Sunday. How do I 'Drop' the statistics?

  4. #4
    Join Date
    Nov 2002
    Location
    Columbus, OH
    Posts
    9
    analyze table "table_name" delete statistics

  5. #5
    Join Date
    Sep 2002
    Posts
    11
    Thanks .. I'll give it a try

  6. #6
    Join Date
    Sep 2002
    Posts
    11
    SQL> alter table fleet_unit_options delete statistics;
    alter table fleet_unit_options delete statistics
    *
    ERROR at line 1:
    ORA-01735: invalid ALTER TABLE option


    Using the same user that created the statistics?!?

    Aso tried 'Drop' statistics.

  7. #7
    Join Date
    Sep 2002
    Posts
    11
    I was able to delete the statistics using the OEM. (dbms.stats).
    Unfortunately the problem persists. I am re-computing statistics
    now. Any other possibilities ?

  8. #8
    Join Date
    Nov 2002
    Location
    Columbus, OH
    Posts
    9
    Sorry, It was analyze table "table_name" delete statistics

  9. #9
    Join Date
    Nov 2002
    Location
    Columbus, OH
    Posts
    9
    The other option is to edit the query and put a rule hint.
    That will make the SQL use and Index , if there is one.

Posting Permissions

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