Home » SQL & PL/SQL » SQL & PL/SQL » Analyze Table (Oracle 9i)
Analyze Table [message #346967] Wed, 10 September 2008 04:31 Go to next message
pointers
Messages: 410
Registered: May 2008
Senior Member
Hi,

What will
analyze table <tablename> compute statistics
do..?
and when should we use this command?
I tried in google but dint get a proper understandable link.

Regards,
Pointers.
Re: Analyze Table [message #346969 is a reply to message #346967] Wed, 10 September 2008 04:38 Go to previous messageGo to next message
dwarak.k
Messages: 61
Registered: June 2008
Location: Hyderabad
Member
This is used to collect and manage table statistics.

But oracle advises to use DBMS_STATS to collect statistics.

look here
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/general.htm#sthref1861
Re: Analyze Table [message #347023 is a reply to message #346967] Wed, 10 September 2008 07:54 Go to previous messageGo to next message
rakeshforum
Messages: 5
Registered: September 2008
Location: india
Junior Member
hi,

oracle 9i two types of optimizer is there
1. Rule Based Optimizer (RBO).
2. Cost-Based optimizer (CBO).

by default oracle 9i is RBO . we change RBO to CBO mode then
at that time we use this query.

Thanks
Re: Analyze Table [message #347031 is a reply to message #347023] Wed, 10 September 2008 08:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
by default oracle 9i is RBO

This is wrong.
OPTIMIZER_MODE
Quote:
Default value choose
The optimizer chooses between a cost-based approach and a rule-based approach based on whether statistics are available.


Regards
Michel
Re: Analyze Table [message #347157 is a reply to message #346967] Wed, 10 September 2008 22:02 Go to previous messageGo to next message
beginoracle
Messages: 4
Registered: September 2008
Junior Member

sql> analyze table<table_name> compute/estimate statistics;

This is done when you want to analyze the table and the analyzed results are placed in plan_table.

This is done when there is a large amount of changes going on in a table. It basically give the statistics like no.of rows,cost and stuff like that. The recent version of oracle is based on CBO(cost based) because it is only optimization method which makes use of the stats by analyzing the table.


The earlier version of oracles prior to oracle 7 uses the rule based optimization method. Not anymore, even Hints only works with CBO.

Hope this answers every question.

Thanx..
Re: Analyze Table [message #347159 is a reply to message #346967] Wed, 10 September 2008 22:37 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>sql> analyze table<table_name> compute/estimate statistics;
Above was valid for 8i, but has been replaced DBMS_STATS


>This is done when you want to analyze the table and the analyzed results are placed in plan_table.

Please post reproducible proof that statement above is correct; which I contend is blatantly incorrect.

[Updated on: Wed, 10 September 2008 22:37] by Moderator

Report message to a moderator

Re: Analyze Table [message #347172 is a reply to message #347159] Wed, 10 September 2008 23:21 Go to previous messageGo to next message
beginoracle
Messages: 4
Registered: September 2008
Junior Member

Hi,

Thank you for correcting.

For example:
EXPLAIN PLAN FOR
SELECT cust_name FROM customers;

This explains the plan into the PLAN_TABLE table. You can then select the execution plan from PLAN_TABLE. This is useful if you do not have any other plans in PLAN_TABLE, or if you only want to look at the last statement.


Regards,
Re: Analyze Table [message #347174 is a reply to message #346967] Wed, 10 September 2008 23:29 Go to previous message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>This is done when you want to analyze the table and the analyzed results are placed in plan_table.

>This explains the plan into the PLAN_TABLE table.

But this is NOT what you said above.

ANALYZE <> EXPLAIN

Which is correct? You are not consistent in your answers.
Previous Topic: CHECK constraint not working ?
Next Topic: ORA-01008 error for cursor
Goto Forum:
  


Current Time: Sun Dec 04 22:40:58 CST 2016

Total time taken to generate the page: 0.05562 seconds