| num_rows on user_indexes vs table_indexes [message #574166] |
Mon, 07 January 2013 09:50  |
 |
tom_watson
Messages: 8
Registered: January 2013
|
Junior Member |
|
|
Hi,
Our stats are collected at the schema level as follows:
EXEC DBMS_STATS.gather_schema_stats('schema', estimate_percent => 15, degree => 8 )
However, when I compare the num_rows in user_indexes to the num_rows in user_tables, for some indexes I am getting zero rows but over 100k rows in the table.
I can see from the last_analyzed col on user_indexes that stats were gathered within seconds of the stats being collected on the tables.
Does anyone have any ideas as to this behaviour?
Thanks,
Tom.
|
|
|
|
|
|
|
|
| Re: num_rows on user_indexes vs table_indexes [message #574172 is a reply to message #574168] |
Mon, 07 January 2013 10:28  |
|
tom_watson
Messages: 8
Registered: January 2013
|
Junior Member |
|
|
Hi,
Please find below the statement I've written along with my results. I have deliberately kept my table and index names hidden.
select
--a.table_name,
a.num_rows "Tab count",
a.last_analyzed "Tab Stats Gathered",
--b.index_name,
b.num_rows "Index count",
b.last_analyzed "Index Stats Gathered",
round(b.num_rows * 100 / a.num_rows) "Index pct of tab count"
from user_tables a, user_indexes b
where a.table_name = b.table_name
order by b.num_rows * 100 / a.num_rows
;
Tab count Tab Stats Gathered Index count Index Stats Gathered Index pct of tab count
---------------------- ------------------------- ---------------------- ------------------------- ----------------------
9 07-JAN-13 05:10:30 0 07-JAN-13 05:10:30 0
967 07-JAN-13 04:56:25 0 07-JAN-13 04:56:25 0
122573 07-JAN-13 04:45:17 0 07-JAN-13 04:45:21 0
122573 07-JAN-13 04:45:17 0 07-JAN-13 04:45:21 0
122573 07-JAN-13 04:45:17 0 07-JAN-13 04:45:21 0
7039 07-JAN-13 04:33:28 0 07-JAN-13 04:33:28 0
616447 07-JAN-13 04:34:46 60055 07-JAN-13 04:34:47 10
The last col displays the num_rows in user_indexes as a % of num_rows in user_tables. All tables = 0 except for the last where the num_rows in user_indexes is 10% of that in user_tables.
Thanks,
Tom.
[Updated on: Mon, 07 January 2013 10:31] Report message to a moderator |
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
