Home » SQL & PL/SQL » SQL & PL/SQL » Taking Sum of Similar Tables (Oracle 11g)
Taking Sum of Similar Tables [message #662271] Sun, 23 April 2017 07:22 Go to next message
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 #662272 is a reply to message #662271] Sun, 23 April 2017 07:33 Go to previous messageGo to next message
BlackSwan
Messages: 26270
Registered: January 2009
Location: SoCal
Senior Member
which metric measures best?

I believe that PL/SQL needs to be used.
Re: Taking Sum of Similar Tables [message #662273 is a reply to message #662271] Sun, 23 April 2017 07:40 Go to previous messageGo to next message
John Watson
Messages: 7659
Registered: January 2010
Location: Global Village
Senior Member
I would use a compound query, for example
jw122pdb>
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 #662274 is a reply to message #662273] Sun, 23 April 2017 07:58 Go to previous messageGo to next message
BlackSwan
Messages: 26270
Registered: January 2009
Location: SoCal
Senior Member
how 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?
Re: Taking Sum of Similar Tables [message #662275 is a reply to message #662271] Sun, 23 April 2017 08:29 Go to previous messageGo to next message
Michel Cadot
Messages: 65942
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Did you solve your previous problem?
If so, how and what was the root of the problem?

Re: Taking Sum of Similar Tables [message #662277 is a reply to message #662274] Sun, 23 April 2017 08:45 Go to previous messageGo to next message
John Watson
Messages: 7659
Registered: January 2010
Location: Global Village
Senior Member
BlackSwan wrote on Sun, 23 April 2017 13:58
how 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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2812
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2812
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 #662281 is a reply to message #662280] Sun, 23 April 2017 09:18 Go to previous messageGo to next message
gobi21
Messages: 6
Registered: January 2016
Location: Singapore
Junior Member
Thanks. I will check the query tom. I assume count(pk) will be better instead count(*) as we will fast in query and not using much DB resource.

Re: Taking Sum of Similar Tables [message #662282 is a reply to message #662281] Sun, 23 April 2017 09:25 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2812
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.
Re: Taking Sum of Similar Tables [message #662289 is a reply to message #662282] Sun, 23 April 2017 14:24 Go to previous messageGo to next message
saipradyumn
Messages: 361
Registered: October 2011
Location: Hyderabad
Senior Member

Hi
I think we can consider the sum(NUM_ROW) in User_tables.
But we need to make that before executing the query need to collect gathering statistics for entire schema .

Thanks
Saipradyumn
Re: Taking Sum of Similar Tables [message #662290 is a reply to message #662289] Sun, 23 April 2017 14:52 Go to previous messageGo to next message
Michel Cadot
Messages: 65942
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Which will not guarantee to give you the correct result (without speaking this is a way OP first rejected).
Why did you envisage this way?

[Updated on: Sun, 23 April 2017 14:52]

Report message to a moderator

Re: Taking Sum of Similar Tables [message #662307 is a reply to message #662290] Mon, 24 April 2017 07:45 Go to previous message
gobi21
Messages: 6
Registered: January 2016
Location: Singapore
Junior Member
@ Solomon Yakobson. Thanks for the Query. It worked like a charm.

Cheers
Previous Topic: Pivot result using "Pivot" keyword
Next Topic: ORA-00054 RESOURCE BUSY AND ACQUIRE WITH NOWAIT
Goto Forum:
  


Current Time: Mon Nov 12 21:32:30 CST 2018