Results 1 to 5 of 5

Thread: analyzing table

  1. #1
    Join Date
    Aug 2003
    Posts
    123

    Unanswered: analyzing table

    what is the use of analyzing the table, how my query will be faster by analyzing table.


    --Jaggu

  2. #2
    Join Date
    Oct 2003
    Posts
    26

    Re: analyzing table

    Hi,

    Analyzing your table "could" speed up your query only if your DB is on the Cost Based Optimizer mode.

    Basically analyzing a table generates some vital stats about the data in the tables like number of rows, number of unique values in a column etc.
    The CBO uses the above generated information for coming up with several plans & uses the plan with the least cost for the query.

    For more information, read the Oracle docs.

    HTH
    Arvind

  3. #3
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Because then Oracle can use its cost based optimizer to decide on the execution plan using statistics based on the data you have in your tables. By statistics it actually calculates things like number of rows, data distribution in the columns etc. The advantage is obviuosly that it can look at your query and decide how selective your criteria will be and thus come up with a better faster execution plan.

    Obviously if your data changes then you should reanalyze. Their are stored procedures to help you analyze a schema or whole databases. Put them in an oracle job and it will do it on a regular basis.

    Alan

  4. #4
    Join Date
    Aug 2003
    Posts
    123
    Alan,
    Could u pls. explain me the following scenario, Lets consider the table T1
    1. T1 is not analyzed
    2. T1 is analyzed quite some time back
    3. T1 is analyzed as of now

    If my query call for the table T1, what will happen for each of the above scenario.

    Thanks in advantage

  5. #5
    Join Date
    Oct 2003
    Posts
    87
    Originally posted by jaggu
    Alan,
    Could u pls. explain me the following scenario, Lets consider the table T1
    1. T1 is not analyzed
    You force the CBO to use built-in defaults or revert to RBO rules that can, occassionly yield decent access paths but usually don't.

    Originally posted by jaggu
    2. T1 is analyzed quite some time back
    CBO uses old stats that may no longer represent the current nature of the data possibly resulting in poor access paths.

    Originally posted by jaggu
    3. T1 is analyzed as of now
    This usually yields the best access paths; however, sometimes a DBA is in a unique position to know info about the data that aren't tracked by Oracle stats -- in this case "hints" can be used to influence CBO decisions.

    Finally, running stats should be "event based". Changes in volume (deletes or inserts), skew, and partitioning are some typical reasons. There a examples in the Oracle manuals to help you write scripts that can detect these changes and cause stats to be executed on the appropriate objects.
    Oracle - DB2 - MS Access -

Posting Permissions

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