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.
Helen
Do You Yahoo!?
Make international calls for as low as $.04/minute with Yahoo! Messenger
http://phonecard.yahoo.com/
