Re: NUM_ROWS in DBA_TABLES has no values

bennettj_at_news-server (Joseph Bennett) wrote:
>
> Karl Penney (aa651_at_ccn.cs.dal.ca) wrote:
> : I was trying to write a query to find the number of rows in some
> : tables using the NUM_ROWS field on the dictionary table DBA_TABLES
> : but all the values are blank. I can't use count(*) on the tables
> : themselves because I need to join the results to some other dictionary
> : tables to get other info. Why is the NUM_ROWS field blank and how can
> : I get is populated with real data.
>
> Unfortunately, these are filled when you use the ANALYZE command. If you
> don't do the ANALYZE command, then this information is not available to
> you (or for that matter, to the Cost-Based-Optimizer).
>
> : --
> : Karl Penney |~~~~~~| _/^\_ |~~~~~~|
> : DMR Group, Halifax, NS | | _/\_\ /_/\_ | |
> : aa651_at_cfn.cs.dal.ca | | >___________< | |
> : 74203.673_at_compuserve.com |______| | |______|
>
> Have a great day....
> --
>
> +------------+
> /| +--------+ |
> //| | //| | Joseph P. Bennett
> //|| |_____///| | Senior Consultant
> //_|| |_____// | | Perfect Order Software Solutions
> ||_|| |____ ||_| | bennettj_at_ugly.microserve.net
> || || | || |
> || /+-----| ||---+ "There are a lot of dumb people with powerful tools."
> ||// _____| ||/ / Jonah Seiger, 1994
> | / /_______| /
> |/__________| /
>

Since what you want to do is use the row count in some other queries, and have to avoid a join (cause of the group by issue), you may be able to use the new syntax available in 7.1 (this won't work in 7.0).

Try something like:  

  1 select table_name, row_cnt, col_cnt, idx_cnt   2 from
  3 user_tables,

  4    (select count(*) row_cnt from emp ),
  5    (select count(*) col_cnt from user_tab_columns where table_name='EMP'),
  6    (select count(*) idx_cnt from user_indexes where table_name='EMP')

TABLE_NAME                        ROW_CNT    COL_CNT    IDX_CNT
------------------------------ ---------- ---------- ----------
EMP                                    15          8          1
 

Where you would normally have tables, you can have a query. A lot like building a view on the fly. Alternatively, you could build views in 7.0 for each table you wanted a count of (eg: create view emp_cnt as select 'EMP' table_name, count(*) row_count from emp).

Also, bear in mind that
- Analyze must be run periodically to get the row count refreshed - The row count in dba_tables will become stale (not accurate) right after anyone inserts/deletes from the table. - If optimizer_mode is set to true, you will start using the cost based optimizer on any query that uses that analyzed table. This will be bad if other tables in the query have not been analyzed.

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government Received on Mon May 29 1995 - 00:00:00 CEST