| Taking Sum of Similar Tables [message #662271] |
Sun, 23 April 2017 07:22  |
 |
gobi21
Messages: 6
Registered: January 2016
Location: Singapore
|
Junior Member |
|
|
Hi,
I need to take the count from multiple tables(100+ Tables) and Sum up the same into a single value.
For example: Tables starting with TA.
I want the actual counts and hence dont want to use DBA_TABLES/ALL_TABLES.
Please suggest me the best way to do this.
Thanks
|
|
|
|
|
|
| Re: Taking Sum of Similar Tables [message #662273 is a reply to message #662271] |
Sun, 23 April 2017 07:40  |
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
I would use a compound query, for examplejw122pdb>
jw122pdb> select sum(cnt) from
2 (select count(*) cnt from emp
3 union
4 select count(*) from dept);
SUM(CNT)
----------
18
jw122pdb> |
|
|
|
|
|
|
|
| Re: Taking Sum of Similar Tables [message #662277 is a reply to message #662274] |
Sun, 23 April 2017 08:45 |
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
BlackSwan wrote on Sun, 23 April 2017 13:58how to know which tables need to be counted?
Realize the "final" value will likely be incorrect due to row changes that occur while the COUNT operation is underway? I wouldn't class that as a problem, it is just the usual issue of understanding isolation levels. What is a real problem I have noticed with my solution is that duplicate values will be removed. If OP chooses to implement it that way, he will have to adjust accordingly. But since he hasn't bothered to reply yet, I'm won't bother to post the real solution.
|
|
|
|
| Re: Taking Sum of Similar Tables [message #662278 is a reply to message #662271] |
Sun, 23 April 2017 08:49 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Something like:
SELECT SUM(
XMLCAST(
XMLQUERY(
'/ROWSET/ROW/CNT'
PASSING DBMS_XMLGEN.GETXMLTYPE(
'SELECT COUNT(*) CNT
FROM "' || OWNER || '". "' || TABLE_NAME || '"'
)
RETURNING CONTENT
)
AS NUMBER
)
) TOTAL_ROW_COUNT
FROM DBA_TABLES T
WHERE OWNER = '&SCHEMA_NAME'
AND TABLE_NAME LIKE 'TA%'
AND TABLE_NAME NOT LIKE 'MLOG$\_%' ESCAPE '\' -- exclude materialized view logs
AND NVL(IOT_TYPE,'NOT_IOT') NOT IN ('IOT_OVERFLOW','IOT_MAPPING') -- exclude IOT overflow and mapping
AND TEMPORARY = 'N' -- exclude temporary tables
AND SECONDARY = 'N' -- exclude Oracle Text index tables and other "non-tables"
AND TABLE_NAME NOT IN (
SELECT E.TABLE_NAME
FROM DBA_EXTERNAL_TABLES E
WHERE E.OWNER = T.OWNER
) -- exclude external tables
/
SY.
[Updated on: Sun, 23 April 2017 08:56] Report message to a moderator |
|
|
|
| Re: Taking Sum of Similar Tables [message #662279 is a reply to message #662278] |
Sun, 23 April 2017 09:03 |
|
gobi21
Messages: 6
Registered: January 2016
Location: Singapore
|
Junior Member |
|
|
Thanks for the responses. They query will be run once during the off-business hours and hence DB update may not be happening during that time.
@John Watson.. Compounding query may not be possible as i we have only few starting characters of the table. The table will go on increase with some initial static characters.
For Example: TAXGLK000001,TAXGLK000002...
@Soloman Yakobson.. Thanks for your query. It seems to the one i am looking for. I am connected to DB in home network. Will check tom and respond back. Really thanks for the quick responses.
Just one thing. we are already filtering table_name like TA%, Then why we need all other filter conditions after that.?.
Cheers.
[Updated on: Sun, 23 April 2017 09:03] Report message to a moderator |
|
|
|
| Re: Taking Sum of Similar Tables [message #662280 is a reply to message #662279] |
Sun, 23 April 2017 09:15 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Yes, I updated my post and added LIKE 'TA%" last minute since I missed that requirement originally. So you can remove:
AND TABLE_NAME NOT LIKE 'MLOG$\_%' ESCAPE '\' -- exclude materialized view logs
predicate. All predicates have comments explaining why they were added. E.g. you can have IOT table for which Oracle creates overflow & mapoping tables which are auxiliary and aren't user tables. Same way most likely you don't want to count rows in external table or in GTT even if name starts with TA.
SY.
|
|
|
|
|
|
| Re: Taking Sum of Similar Tables [message #662282 is a reply to message #662281] |
Sun, 23 April 2017 09:25 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
You assume wrong.
SQL> explain plan for select count(*) from emp;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
Plan hash value: 2937609675
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| PK_EMP | 14 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------
9 rows selected.
SQL> explain plan for select count(empno) from emp;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
Plan hash value: 2937609675
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| PK_EMP | 14 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------
9 rows selected.
SQL>
SY.
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
