RE: What does ANALYZE do?

A1:
The counts in USER_TABLES are only updated by an analyze.  Do not worry that a transaction is not updating them
 
A2:
Analyze gathers statistics that the optimizer needs including the count of rows and the data distribution of indexed columns.  If there are only 10 rows in a table, a full scan is probably faster than using an index. A full table scan is also probably faster than an index on gender.  The optimizer determines execution plans based on these statistics, so if they change significantly analyze needs to be run again.  In your case the statistics were initially from before the data load, so the optimizer probably grossly underestimated the row counts for the tables and decided to ignore the indexes.  After the analysis, it had more accurate information and was able to create a faster execution.
<SPAN

class=126164518-09082001> 
<SPAN

class=126164518-09082001>A3:  Check the documentation on explain plan.  It allows you to see the full execution path selected by the optimizer.  Indexes need to rebuilt when they get skewed.  Analyzing will populate columns in user_indexes that can be used to determine how skewed the index is.
Brian Norrell <FONT face=Arial
size=2>Manager, MPI Development <FONT color=#ff0000 face=Arial size=2>QuadraMed 511 E John Carpenter Frwy, Su 500 Irving, TX 75062 <FONT
face=Arial size=2>(972) 831-6600
<FONT face=Tahoma

size=2>-----Original Message-----From: Helen rwulfjeq [mailto:lannyue_at_YAHOO.COM]Sent: Thursday, August 09, 2001 1:16 PMTo: Multiple recipients of list ORACLE-LSubject: What does ANALYZE do?
Hello, DBAs:
I'm doing data migration (say from 3.1 database application to 4.1) using SQL script on Oracle 8i. Here are something I can't understand. After I insert all the data from old database, the last step I need to build a new intermediate table-A (with the PK on several tables) which was not exist in 3.1 DB. The single select count (*) from these several joint tables (the same query to build this intermediate table-A) will take about 10 hours (as I calculated). This is unacceptable for sure. Then I find out that the row number returns from "select count(*) from TABLEx" is totally different from the NUM_ROWS from USER_TABLES. NUM_ROWS will not change, only reflects the row numbers before I run the migration script (like only 2 records). For sure, I did COMMIT after each insert.
Then I did "Analyze table ... compute statistics". The Data Dictionary got updated this time. And the query returns within a minute. Q1. Why the COMMIT does not update the Data Dictionary? Do/should I care about this?
Q2. What function/role does analyze has here? Help on the speed? Should I use it? If so, should I delete the analyze after use it?     My another thought is about table indexes. If I ever used them or did they broke when the query runs. But Q3. How can I find out (when I query tables) if the table's indexes are being used or not? When should I rebuild the indexes? Thanks in advance for your clarification. Helen

Do You Yahoo!?Make international calls for as low as $.04/minute with Yahoo! Messenger<A
href="http://phonecard.yahoo.com/?.refer=mailtagline">http://phonecard.yahoo.com/ Received on Thu Aug 09 2001 - 19:07:42 CDT