| index_stats not populated [message #132277] |
Thu, 11 August 2005 13:08  |
mjschwenger
Messages: 137
Registered: July 2005
Location: US
|
Senior Member |
|
|
I have created a couple of new indexes using the same code which run for the older indexes. Compute statistics per table, dbms_stats - for all index_columns/cascade.
The new indexes have no in fo in index_stats DD view, the old - do.
In DBA_Indexes they all have shown analysed with the correct date.
How is this view populated and why the new indexes do not have entries there.
Thanks a lot,mj
|
|
|
|
| Re: index_stats not populated [message #132432 is a reply to message #132277] |
Fri, 12 August 2005 13:15  |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
In a situation like this, it might be best for you to post your entire session of creating table/index and populating it and gathering statistics and selecting from the view in question. That way we can spot what is going on more easily.
Also, if you are talking about the index_stats view, I believe you need to analyze the index and validate structure to populate it.
|
|
|
|
| Re: index_stats not populated [message #132620 is a reply to message #132432] |
Mon, 15 August 2005 04:34 |
alliejane
Messages: 59
Registered: July 2005
Location: Glasgow
|
Member |
|
|
The view INDEX_STATS is only populated after you run an analyze index name validate structure, and the information is only held in your PGA (if you log out you no longer see the statistics).
These stats are created for your own benefit, and have no influence on the Optimizer when it's generating an explain plan.
As long as you have an up-to-date last_analyzed_date on the dba_indexes view then the "real" stats generation worked.
Not sure how you could look into INDEX_STATS and see the statistics from the old indexes but not the new indexes, you should only see stats from the last index you analyzed (that's why you can look in the index_stats view without the where clause specifying the index name..)
SQL> analyze index hr.emp_emp_id_pk validate structure;
Index analyzed.
SQL> select name, blocks from index_stats;
NAME BLOCKS
------------------------------ ----------
EMP_EMP_ID_PK 8
SQL> analyze index hr.emp_email_uk validate structure;
Index analyzed.
SQL> select name, blocks from index_stats;
NAME BLOCKS
------------------------------ ----------
EMP_EMAIL_UK 8
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
