asked on

Oracle Select !

Hi Expert,

Can anybody please through light on below query! How does it's work internally?

select table_name,
to_number(extractvalue(dbms_xmlgen.getXMLtype('select /*+ PARALLEL(8) */ count(*) cnt from "&&SCHEMA_NAME".'||table_name),'/ROWSET/ROW/CNT'))
rows_in_table from dba_TABLES
where owner='&SCHEMA_NAME';

thanks

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Geert G

and again ... why do you put /*+ parallel */ in every query ???

what is the point of doing that on dba_tables ???

Alex [***Alex140181***]

what is the point of doing that on dba_tables ???

Apart from "good looking"?! Nothing (in that case)!

johnsone

The parallel hint isn't on DBA_TABLES, it is on the count of the table in the sub-query. You are trying to get a count of the rows in every table in a schema.

The real question is how exact does that have to be? Stats should get you pretty close with:

SELECT table_name, 
       num_rows 
FROM   dba_tables 
WHERE  owner = '&SCHEMA_NAME';

Open in new window

Alex [***Alex140181***]

Stats should get you pretty close with

It depends: take staging or temp tables for instance -> some of those may get truncated at some point in processing. Stats will show then, let's say 125345 rows, whereas the table is empty!!
Sure, in many/most cases, you can rely on stats, but you should keep in mind that this can be very vague in some cases!

johnsone

True temp tables would always have no rows in a count. You can only see temp tables that are part of your session.

I wouldn't think you would care about the counts in staging tables. They are transient. The number of rows in the table really isn't meaningful.