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