Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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

What does ANALYZE do?

From: Helen rwulfjeq <lannyue_at_yahoo.com>
Date: Thu, 09 Aug 2001 16:51:13 -0700
Message-ID: <F001.00365DFB.20010809101606@fatcity.com>


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. HelenDo You Yahoo!?
Make international calls for as low as $.04/minute with Yahoo! Messengerhttp://phonecard.yahoo.com/ Received on Thu Aug 09 2001 - 18:51:13 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US