Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> here again - What does ANALYZE do ??

here again - What does ANALYZE do ??

From: Helen rwulfjeq <>
Date: Thu, 09 Aug 2001 17:22:28 -0700
Message-ID: <>

Not seen the message I posted, here I'm again: 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 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. HelenDo You Yahoo!?
Make international calls for as low as $.04/minute with Yahoo! Messenger Received on Thu Aug 09 2001 - 19:22:28 CDT

Original text of this message