Oracle Select !

MIHIR KAR used Ask the Experts™
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';

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018
It selects a count from a table using dynamic SQL:
select /*+ PARALLEL(8) */ count(*) cnt from "&&SCHEMA_NAME".'||table_name

It creates an XML resultset by looing through DBA_TABLES using the dbms_xmlgen.

It then extracts the count from the XML with extractvalue.

and returns the table_name and the row count for a specific schema.

Break it down and experiment.

I'm guessing you already know:
select count(*) from some_table;

To play with the xml generate:
select dbms_xmlgen.getXMLtype('select sysdate from dual') from dual;

Then you can experiment with the extractvalue.
Geert GOracle dba
Top Expert 2009

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

what is the point of doing that on dba_tables ???
what is the point of doing that on dba_tables ???
Apart from "good looking"?! Nothing (in that case)!
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

johnsoneSenior Oracle DBA

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, 
FROM   dba_tables 
WHERE  owner = '&SCHEMA_NAME'; 

Open in new window

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!
johnsoneSenior Oracle DBA

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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial