Home » SQL & PL/SQL » SQL & PL/SQL » Count difference
Count difference [message #258742] Mon, 13 August 2007 08:57 Go to next message
Aju
Messages: 94
Registered: October 2004
Member
select COUNT(*) RCOUNT from TRANSACTION Partition(TRAN_200612)
WHERE CLIENT_ID IN ('AJU', 'MICHEL'); -- 29622346


select COUNT(posting_date) RCOUNT from ecw.TRANSACTION Partition(TRAN_200612)
WHERE CLIENT_ID IN ('AJU', 'MICHEL') -- 29626867

Can anyone please explain the reason for the difference between the above 2 counts.

Thanks

[Edit: remove confidential data]

[Updated on: Tue, 14 August 2007 05:04] by Moderator

Report message to a moderator

Re: Count difference [message #258745 is a reply to message #258742] Mon, 13 August 2007 09:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Some posting_date are null.

Regards
Michel
Re: Count difference [message #258854 is a reply to message #258745] Mon, 13 August 2007 11:59 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Also I hope you are executing both the queries in the same schema

[Updated on: Mon, 13 August 2007 12:00]

Report message to a moderator

Re: Count difference [message #258857 is a reply to message #258742] Mon, 13 August 2007 12:03 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
COUNT

By
Vamsi
Re: Count difference [message #259042 is a reply to message #258742] Tue, 14 August 2007 04:54 Go to previous messageGo to next message
Aju
Messages: 94
Registered: October 2004
Member
Yes both are executing for the same schema.
Re: Count difference [message #259045 is a reply to message #259042] Tue, 14 August 2007 04:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you check what I said?

Regards
Michel
Re: Count difference [message #259047 is a reply to message #258742] Tue, 14 August 2007 05:04 Go to previous messageGo to next message
Aju
Messages: 94
Registered: October 2004
Member
select * from ecw.TRANSACTION Partition(TRAN_200612)
WHERE CLIENT_ID IN ('AJU', 'MICHEL') AND POSTING_DATE IS NULL

returns no records. But even though the posting_date is null why would be the count(*) give less result than count(posting_date). Can you please explain.

Thank

[Edit: remove confidential data]

[Updated on: Tue, 14 August 2007 05:08] by Moderator

Report message to a moderator

Re: Count difference [message #259049 is a reply to message #259047] Tue, 14 August 2007 05:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Count(field) only counts not null "field".
Count(*) counts rows.

Post result of:

select count(*), count(posting_date), count(case when posting_date is null then 1 end)
from ...

Regards
Michel
Re: Count difference [message #259057 is a reply to message #259049] Tue, 14 August 2007 05:29 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Michel,
You are right. But Aju's question is that why count("field") is giving bigger value than count(*). 29626867 > 29622346
If at all field is null for some rows, count("field") should return smaller value than count(*).

Aju,
By the way can you please check the following too?
select count(*) from ecw.TRANSACTION;
select count(*) from TRANSACTION;
Seems in one of your queries, you have used another schema object.
By
Vamsi
Re: Count difference [message #259065 is a reply to message #259047] Tue, 14 August 2007 05:57 Go to previous messageGo to next message
Aju
Messages: 94
Registered: October 2004
Member
The result is as below
select count(*), count(posting_date), count(case when posting_date is null then 1 end)
from ecw.TRANSACTION Partition(TRAN_200612)
WHERE CLIENT_ID IN ('JCPCON', 'JCPBRC')

Count (*) 29626867
29626867

count(posting_date)
29626867

count(case when posting_date is null then 1 end)
0

But why do the count(*) return less result. Should not it return the same.


Thank you
Re: Count difference [message #259068 is a reply to message #259065] Tue, 14 August 2007 06:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't see any difference between 29626867 and 29626867.

In your first test, I think someone committed his work between the 2 queries.

Regards
Michel
Re: Count difference [message #259079 is a reply to message #259065] Tue, 14 August 2007 06:23 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Mad How about my queries?

By
Vamsi
Re: Count difference [message #259147 is a reply to message #259079] Tue, 14 August 2007 09:16 Go to previous messageGo to next message
Aju
Messages: 94
Registered: October 2004
Member
Both queries returns the same result.

Intersting; when I fire the count qry separately it gives the count as

select COUNT(*) from ecw.TRANSACTION Partition(TRAN_200612)
WHERE CLIENT_ID IN ('JCPCON', 'JCPBRC') --29622346
but when firing the qry as below

select count(*), count(posting_date), count(case when posting_date is null then 1 end)
from ecw.TRANSACTION Partition(TRAN_200612)
WHERE CLIENT_ID IN ('JCPCON', 'JCPBRC')
it gives the result as

Guys please explain. Is it a bug with Oracle....

Thanks for your time
Re: Count difference [message #259170 is a reply to message #259147] Tue, 14 August 2007 10:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL*Plus and copy and paste the execution.
I don't understand what you have.

Regards
Michel
Re: Count difference [message #259182 is a reply to message #259170] Tue, 14 August 2007 10:37 Go to previous messageGo to next message
Aju
Messages: 94
Registered: October 2004
Member
SQL> select COUNT(*) from ecw.TRANSACTION Partition(TRAN_200612)
2 WHERE CLIENT_ID IN ('JCPCON', 'JCPBRC')
3 /

COUNT(*)
----------
29622346

SQL> select count(*), count(posting_date), count(case when posting_date is null then 1 end)
2 from ecw.TRANSACTION Partition(TRAN_200612)
3 WHERE CLIENT_ID IN ('JCPCON', 'JCPBRC')
4 /

COUNT(*) COUNT(POSTING_DATE) COUNT(CASEWHENPOSTING_DATEISNULLTHEN1END)
---------- ------------------- -----------------------------------------
29626867 29626867 0
Re: Count difference [message #259185 is a reply to message #259182] Tue, 14 August 2007 10:44 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
How about the explain plans?

By
Vamsi
Re: Count difference [message #259204 is a reply to message #259182] Tue, 14 August 2007 11:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow How to format your posts
Make sure that lines of code do not exceed 80 or 100 characters when you format.

Execute:
set autotrace on explain statistics
and the same queries

Regards
Michel
Re: Count difference [message #259605 is a reply to message #259204] Thu, 16 August 2007 01:49 Go to previous messageGo to next message
Aju
Messages: 94
Registered: October 2004
Member
Sorry for the delay as we need to contact DBAs for the explain plan.....

SQL> explain plan for select COUNT(*) 
from ecw.TRANSACTION 
     Partition(TRAN_200612) 
WHERE CLIENT_ID IN ('JCPCON', 'JCPBRC'); 
    ----Count 29622346

Explained.
12:12:47 PM◄Plan Table
----------------------------------------------------------------------------------------------------------------------
| Operation | Name | Rows | Bytes| Cost | TQ |IN-OUT| PQ Distrib | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
| SELECT STATEMENT | | 1 | 8 | 32 | | | | | |
| SORT AGGREGATE | | 1 | 8 | | | | | | |
| SORT AGGREGATE | | 1 | 8 | | 2,00 | P->S | QC (RANDOM)| | |
| PARTITION HASH ALL | | | | | 2,00 | PCWP | | 1 | 16 |
| INLIST ITERATOR | | | | | 2,00 | PCWP | | | |
| BITMAP CONVERSION COUN| | | | | 2,00 | PCWP | | | |
| BITMAP INDEX SINGLE V|TRANS_CLIENT_ID_BID | | | | 2,00 | PCWP | | 1009 | 1024 |
----------------------------------------------------------------------------------------------------------------------


SQL> explain plan for 
select count(*), count(posting_date), 
count(case when posting_date is null then 1 end) 
from ecw.TRANSACTION Partition(TRAN_200612) 
WHERE CLIENT_ID IN ('JCPCON', 'JCPBRC');


---Output Count(*)29626867 Count(Posting_Date) 29626867 Count(case)0

Explained.
12:14:27 PM◄Plan Table
----------------------------------------------------------------------------------------------------------------------
| Operation | Name | Rows | Bytes| Cost | TQ |IN-OUT| PQ Distrib | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
| SELECT STATEMENT | | 1 | 16 | 20322 | | | | | |
| SORT AGGREGATE | | 1 | 16 | | | | | | |
| SORT AGGREGATE | | 1 | 16 | | 5,00 | P->S | QC (RANDOM)| | |
| PARTITION HASH ALL | | | | | 5,00 | PCWP | | 1 | 16 |
| TABLE ACCESS FULL |TRANSACTION | 10M| 153M| 20322 | 5,00 | PCWP | | 1009 | 1024 |
----------------------------------------------------------------------------------------------------------------------

[Updated on: Thu, 16 August 2007 02:05] by Moderator

Report message to a moderator

Re: Count difference [message #259609 is a reply to message #259605] Thu, 16 August 2007 02:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Execute EXACTLY what I posted.
FORMAT THE WHOLE OUTPUT.
Count the number of characters and format for no more than 80 characters per line.
Is this so difficult to follow what we ask?

Regards
Michel

Re: Count difference [message #259634 is a reply to message #259609] Thu, 16 August 2007 02:42 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
In my opinion, it is either a corruption in the index (TRANS_CLIENT_ID_BID) or a bug in the BITMAP CONVERSION COUNT algorithm.

Try the following queries:

This one will tell us whether there is a problem with the INLIST ITERATOR. It checks the two clients independently. If this produces the wrong result, the problem is not with the INLIST ITERATOR.
select COUNT(*) subtot
from ecw.TRANSACTION Partition(TRAN_200612) 
WHERE CLIENT_ID = 'JCPCON';

select COUNT(*) 
from ecw.TRANSACTION Partition(TRAN_200612) 
WHERE CLIENT_ID = 'JCPBRC';


Add the results together. This one checks whether it is restricted to just partition queries. It will count the entire table. If they don't match, the problem is not retricted to partition queries. I've set it up as a UNION ALL to ensure that both queries access the same image of possibly changing data.
select COUNT(*), NULL
from ecw.TRANSACTION 
WHERE CLIENT_ID IN ('JCPCON', 'JCPBRC')
UNION ALL
select count(*), count(posting_date) 
from ecw.TRANSACTION 
WHERE CLIENT_ID IN ('JCPCON', 'JCPBRC');


If both of these fail, you can try rebuilding all 16 index hash sub-partitions for TRANS_CLIENT_ID_BID(TRAN_200612).

If the rebuild doesn't fix the problem, then we're looking at an Oracle bug. You'll have to raise a SAR with Oracle or check Metalink. Things to look for:
- Bitmap Conversion Count
- Inlist
- Sub-partitioned

Ross Leishman
Re: Count difference [message #260142 is a reply to message #259634] Fri, 17 August 2007 10:46 Go to previous messageGo to next message
Aju
Messages: 94
Registered: October 2004
Member
The DBA has agreed to rebild the bitmap index over this
weekend. Hope it sloves the problem. I will suggest them to rebuild the hash partition if it does not help

Can anyone please guide where am I wrong. I tried to query on
the user Aju but unable to retrive any documents. As I cannot
open yahoo mail from office, I am late in getting the link from
my personal mail id.

SQL> select COUNT(*) subtotJCPCON
  2  from ecw.TRANSACTION Partition(TRAN_200612) 
  3  WHERE CLIENT_ID = 'JCPCON';

    SUBTOTJCPCON
----------
  29611208

SQL> 
SQL> select COUNT(*) subtotJCPBRC
  2  from ecw.TRANSACTION Partition(TRAN_200612) 
  3  WHERE CLIENT_ID = 'JCPBRC';

  SUBTOTJCPBRC
----------
     11138

SQL> SELECT  29611208+11138 from dual;

29611208+11138
--------------
      29622346

SQL>


Which is lesser than the actual count(29626867)

I tried to execute the query given by Ross Leishman, it is getting execute for around 1 hour. Once I get the result, I will post on the forum.

select COUNT(*), NULL
from ecw.TRANSACTION 
WHERE CLIENT_ID IN ('JCPCON', 'JCPBRC')
UNION ALL
select count(*), count(posting_date) 
from ecw.TRANSACTION 
WHERE CLIENT_ID IN ('JCPCON', 'JCPBRC');



Michel we do not have the privilage to execute the Explain plan
on the DB, not sure where am I missing the 80 chars limit.

Are you looking for the output in the format below

SQL> select COUNT(*)
  2   from ecw.TRANSACTION 
  3        Partition(TRAN_200612) 
  4   WHERE CLIENT_ID IN ('JCPCON');

  COUNT(*)
----------
  29611208

ERROR:
ORA-01031: insufficient privileges


SP2-0612: Error generating AUTOTRACE EXPLAIN report

Statistics
----------------------------------------------------------
         20  recursive calls
          3  db block gets
        788  consistent gets
          1  physical reads
        820  redo size
        198  bytes sent via SQL*Net to client
        254  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         16  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>  select COUNT(Posting_date)
  2  from ecw.TRANSACTION 
  3       Partition(TRAN_200612) 
  4  WHERE CLIENT_ID IN ('JCPCON');

COUNT(POSTING_DATE)
-------------------
           29611208

ERROR:
ORA-01031: insufficient privileges


SP2-0612: Error generating AUTOTRACE EXPLAIN report

Statistics
----------------------------------------------------------
         20  recursive calls
          3  db block gets
     305595  consistent gets
     303326  physical reads
        812  redo size
        209  bytes sent via SQL*Net to client
        254  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         16  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> 





Thanks
Re: Count difference [message #260153 is a reply to message #260142] Fri, 17 August 2007 10:58 Go to previous messageGo to next message
Aju
Messages: 94
Registered: October 2004
Member
Here is the result of the entire table count

SQL> select COUNT(*), NULL
  2  from ecw.TRANSACTION 
  3  WHERE CLIENT_ID IN ('JCPCON', 'JCPBRC')
  4  UNION ALL
  5  select count(*), count(posting_date) 
  6  from ecw.TRANSACTION 
  7  WHERE CLIENT_ID IN ('JCPCON', 'JCPBRC');

  COUNT(*)       NULL
---------- ----------
1625391507
1625391507 1625391507
Re: Count difference [message #260158 is a reply to message #260142] Fri, 17 August 2007 11:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As you don't have the execution plan (error 1031) then it is almost useless.
But if we look at the "consistent get" we see that one should go through an index and the other one no. You likely have a corrupted index.

If you can, connect through user ecw and do the same thing or enable sql trace and use tkprof to analyze it.

Regards
Michel
Re: Count difference [message #261383 is a reply to message #260158] Wed, 22 August 2007 07:55 Go to previous messageGo to next message
Aju
Messages: 94
Registered: October 2004
Member
We are yet to rebild the index. As soon as it is done I will post the result.

Thanks for all your hints
Re: Count difference [message #261387 is a reply to message #261383] Wed, 22 August 2007 08:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ok, thanks for the feedback, we're waiting for your new inputs.

Regards
Michel
Re: Count difference [message #265169 is a reply to message #258742] Wed, 05 September 2007 10:22 Go to previous messageGo to next message
Aju
Messages: 94
Registered: October 2004
Member
Thanks for all your support. After rebuilding the index, the count matches.
Re: Count difference [message #265170 is a reply to message #265169] Wed, 05 September 2007 10:23 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the feedback.

Regards
Michel
Previous Topic: ORACLE PL/SQL QUERIES
Next Topic: DBMS_SCHEDULER job does not run
Goto Forum:
  


Current Time: Sat Dec 10 12:45:08 CST 2016

Total time taken to generate the page: 0.10522 seconds