Skip to Main Content
  • Questions
  • get row count from all the tables from different schemas and store in materialized view

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Rupal.

Asked: January 26, 2017 - 10:21 am UTC

Last updated: January 26, 2017 - 2:24 pm UTC

Version: 11g

Viewed 1000+ times

You Asked

Daily activity is to fetch row count from all the tables in different schemas using below query.
But issue is its taking too much time [around 50 mins to fetch 50000 rows].

SELECT
b.source_name,
a.table_name,
alh.A_ETL_LOAD_SET_KEY,
to_number(
EXTRACTVALUE(
XMLTYPE(
DBMS_XMLGEN.getxml('select count(a_etl_load_set_fk) LOADROWS' ||
' from '||b.source_name||'.'||a.table_name ||
' where a_etl_load_set_fk >= '||alh.A_ETL_LOAD_SET_KEY
)
),'/ROWSET/ROW/LOADROWS'
)
)number_rows_loaded,
ALH.LOAD_START,
ALH.LOAD_END,
ALH.LOAD_SUCCESSFUL
FROM ct_ods.load_frequency a inner join ct_ods.source b
ON a.source_id = b.source_id
cross join CT_ODS.A_LOAD_HISTORY alh
where alh.A_ETL_LOAD_SET_KEY >= (select min(A_ETL_LOAD_SET_KEY) from CT_ODS.A_LOAD_HISTORY where load_start>=add_months(sysdate,-12))
order by 1,2,3

Row count is required from the beginning of the table which is around 10 months.
I am thinking to save data per month into materialized view and then at the end cmobine all of them together into another view to get the complete statistics.

I am getting error while creating materialized view. Below is the query and error message.
CREATE MATERIALIZED VIEW JAINRUP.ODS_STATS_VW (owner,table_name,a_etl_load_set_key,loadcount,load_start,load_end,load_success)
TABLESPACE TS_DATA
BUILD IMMEDIATE
REFRESH FORCE ON DEMAND
WITH PRIMARY KEY
AS
SELECT b.source_name,a.table_name,alh.A_ETL_LOAD_SET_KEY,
to_number(EXTRACTVALUE(XMLTYPE(DBMS_XMLGEN.getxml('select count(a_etl_load_set_fk) LOADROWS' ||
' from '||b.source_name||'.'||a.table_name ||
' where a_etl_load_set_fk >= '||alh.A_ETL_LOAD_SET_KEY)
),'/ROWSET/ROW/LOADROWS'
)
)number_rows_loaded,
ALH.LOAD_START, ALH.LOAD_END, ALH.LOAD_SUCCESSFUL
FROM ct_ods.load_frequency a inner join ct_ods.source b
ON a.source_id = b.source_id
cross join CT_ODS.A_LOAD_HISTORY alh
where alh.A_ETL_LOAD_SET_KEY >= (select min(A_ETL_LOAD_SET_KEY) from CT_ODS.A_LOAD_HISTORY where load_start>=add_months(sysdate,-1))
order by 1,2,3;

Error:
to_number(EXTRACTVALUE(XMLTYPE(DBMS_XMLGEN.getxml('select count(a_etl_load_set_f
k) LOADROWS' ||
*
ERROR at line 8:
ORA-00942: table or view does not exist

Is there any restriction on using XML function inside materialized view query?
If there is a better way to achieve it please suggest.


and Chris said...

Why do you need the row counts for all the tables?

Without a bit more background it's hard to suggest alternatives... Though you can get an estimate from the table stats:

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1660875645686

When you create the MV Oracle still has to execute the query. So if it's taking 50+ minutes to execute, it'll take at least that long to create the MV. This will only help you if you're going to run the query several times.

To use dbms_xmlgen you need select privileges on the tables you're querying. Check you've got this on all the tables you're querying!

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

DBMS_XMLGEN

More on PL/SQL routine DBMS_XMLGEN here