Re: SELECT Statement help required

 Tim Witort wrote:

>Jon Winchester wrote:
>>
>> James Petts wrote:
>> >
>> > I'm trying to write a SELECT statement which will return the
>> > number of rows in each of a set of tables selected by owner
>> > from DBA_TABLES.
>>
>> I use this SQL to select the number of rows from USER_TABLES :
>>
>> column table_name format A16
>> column num_rows format 999,999,999 heading 'Rows' ;
>
>That assumes that the NUM_ROWS column is being populated.
>None of the dozen or so Oracle instances I have encountered
>have had this column populated.

You can use the ANALYZE command to populate the data dictionary tables. To get statistics for all tables in a schema, use the ANALYZE_SCHEMA procedure in one of the built-in packages (DBMS_UTILITY?) . Before considering doing this, however, understand that once the statistics are in place, Oracle will (if the optimizer mode initialization parameter is not set to RULE), Oracle will start to use those stats to determine the execution path for queries; that is, cost-based optimization. This should usually improve query performance, but, in my limited experience, I have seen it result in a serious performance drag on long, complex queries and had to delete the statistics. Received on Thu Jul 24 1997 - 00:00:00 CDT