Home » SQL & PL/SQL » SQL & PL/SQL » Interesting SQL problem (Oracle 10g)
Interesting SQL problem [message #357204] Tue, 04 November 2008 05:00 Go to next message
tanmoy1048
Messages: 133
Registered: June 2007
Senior Member
SELECT count(*) FROM V$LOGMNR_CONTENTS 
WHERE seg_type=2 and seg_owner IN ('SADAT') and operation in ('LOB_WRITE');



This return only 2. But


SELECT count(sql_redo) FROM V$LOGMNR_CONTENTS 
WHERE seg_type=2 and seg_owner IN ('SADAT') and operation in ('LOB_WRITE');


This returns 10. whats the problem?

[Updated on: Tue, 04 November 2008 05:15] by Moderator

Report message to a moderator

Re: Interesting SQL problem [message #357207 is a reply to message #357204] Tue, 04 November 2008 05:10 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It isn't interesting, it is just the way it is:
SQL> create table test (redo number, owner varchar2(20));

Table created.

SQL> insert all
  2    into test values (1   , 'sadat')
  3    into test values (null, 'little')
  4    into test values (null, 'foot')
  5    into test values (2   , 'tanmoy')
  6    into test values (3   , '1048')
  7  select * From dual;

5 rows created.

SQL> select count(*) from test;

  COUNT(*)
----------
         5         --> 5 records in there ...

SQL> select count(redo) from test;

COUNT(REDO)
-----------
          3        --> ... but only 3 of them contain value in the "REDO" column

SQL>
Re: Interesting SQL problem [message #357209 is a reply to message #357204] Tue, 04 November 2008 05:14 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@tanmoy1048,

I hope the following example clears things for you:

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Nov 4 16:48:48 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options


SQL> SELECT COUNT (*)
  2     FROM 
  3     (SELECT 'A' col_1 FROM DUAL
  4        UNION ALL
  5      SELECT 'B' FROM DUAL
  6        UNION ALL
  7      SELECT NULL FROM DUAL
  8        UNION ALL
  9      SELECT NULL FROM DUAL
 10        UNION ALL
 11      SELECT 'C' FROM DUAL);

  COUNT(*)
----------
         5

SQL> SELECT COUNT (col_1)
  2    FROM 
  3    (SELECT 'A' col_1 FROM DUAL
  4       UNION ALL
  5     SELECT 'B' FROM DUAL
  6       UNION ALL
  7     SELECT NULL FROM DUAL
  8       UNION ALL
  9     SELECT NULL FROM DUAL
 10       UNION ALL
 11     SELECT 'C' FROM DUAL);

COUNT(COL_1)
------------
           3


Difference is how count function deals with NULL values.

But I am confused with your output. http://img2.mysmiley.net/imgs/smile/confused/confused0031.gif In your case COUNT(*) yeilds less result than COUNT(COLUMN_NAME). I am curious now...

Hope this helps,
{**Added: Too Slow.... Seems Littlefoot beat me to it http://img2.mysmiley.net/imgs/smile/angel/sadangel.gif]
Regards,
Jo

[Updated on: Tue, 04 November 2008 05:18]

Report message to a moderator

Re: Interesting SQL problem [message #357210 is a reply to message #357204] Tue, 04 November 2008 05:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Keep your lines in 80 characters.

Nothing interesting in this SQL "problem", just a lack of reading the documentation from you.

Regards
Michel
Re: Interesting SQL problem [message #357234 is a reply to message #357210] Tue, 04 November 2008 06:41 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Am I the only one who sees that the original post has the number of counts reversed, opposed to the examples??
count(*) gives 2, count(sql_redo) gives 10.

Edit: Sorry Jo, just read the trailer of your post.

[Updated on: Tue, 04 November 2008 06:42]

Report message to a moderator

Re: Interesting SQL problem [message #357252 is a reply to message #357234] Tue, 04 November 2008 07:54 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
Frank,
We can wait for OP to prove it. http://img2.mysmiley.net/imgs/smile/innocent/innocent0002.gif

@tanmoy1048,
Can you post your SQL*Plus session running the commands posted by you?

Regards,
Jo
Re: Interesting SQL problem [message #357321 is a reply to message #357204] Tue, 04 November 2008 21:33 Go to previous messageGo to next message
tanmoy1048
Messages: 133
Registered: June 2007
Senior Member
SQL> exec sys.DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/oradata2/archive/arch1_1335_1_664408221.dbf', OPTIONS => sys.DBMS_LOGMNR.NEW);

EXECUTE sys.DBMS_LOGMNR.START_LOGMNR(OPTIONS => sys.DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + sys.DBMS_LOGMNR.NO_ROWID_IN_STMT);
PL/SQL procedure successfully completed.

SQL> SQL>

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> SELECT count(*) FROM V$LOGMNR_CONTENTS
WHERE seg_type=2 and seg_owner IN ('SADAT') and operation in ('LOB_WRITE');  2

  COUNT(*)
----------
         2

SQL> SELECT count(sql_redo) FROM V$LOGMNR_CONTENTS
WHERE seg_type=2 and seg_owner IN ('SADAT') and operation in ('LOB_WRITE');  2

COUNT(SQL_REDO)
---------------
             10


thanx frank..
Re: Interesting SQL problem [message #357325 is a reply to message #357210] Tue, 04 November 2008 22:50 Go to previous messageGo to next message
tanmoy1048
Messages: 133
Registered: June 2007
Senior Member
I am sorry Michel. I didnt understand your comment.
Re: Interesting SQL problem [message #357329 is a reply to message #357325] Tue, 04 November 2008 22:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your title is "Interesting SQL problem", I don't find anything interesting in the fact you don't know how COUNT works.

Regards
Michel
Re: Interesting SQL problem [message #357337 is a reply to message #357204] Wed, 05 November 2008 00:09 Go to previous messageGo to next message
tanmoy1048
Messages: 133
Registered: June 2007
Senior Member
I confess that I have lack of knowledge but would u please explain why this is happening .
Re: Interesting SQL problem [message #357341 is a reply to message #357337] Wed, 05 November 2008 00:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What about others replied? ...and requested.

Regards
Michel
Re: Interesting SQL problem [message #357344 is a reply to message #357341] Wed, 05 November 2008 00:29 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
@Michel

The problem he is facing that count(*) yeilds less rows compared to count(column_name). If it had been otherway around then it would have been understandable.

@tanmoy1048

Can you post explain plan for the both the queries?

Re: Interesting SQL problem [message #357348 is a reply to message #357344] Wed, 05 November 2008 00:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
bonker,

He did not prove what he stated and this has already been requested several times. Most likely it is a typo and this was the other ones (and I) assumed.

This is why "copy and paste" should be ALWAYS done and not interpretation posted.

Regards
Michel
Re: Interesting SQL problem [message #357349 is a reply to message #357204] Wed, 05 November 2008 00:38 Go to previous messageGo to next message
tanmoy1048
Messages: 133
Registered: June 2007
Senior Member
SELECT count(*) FROM V$LOGMNR_CONTENTS WHERE seg_type=2 and seg_owner IN ('SADAT');
        11

Execution Plan
----------------------------------------------------------
Plan hash value: 1325233743
---------------------------------------------------------------------------------------
| Id  | Operation         | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                   |     1 |    57 |     0   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |                   |     1 |    57 |            |          |
|*  2 |   FIXED TABLE FULL| X$LOGMNR_CONTENTS |     1 |    57 |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("SEG_TYPE"=2 AND "SEG_OWNER"='SADAT' AND "ROW_TYPE"=0 AND
              "INST_ID"=USERENV('INSTANCE'))

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        515  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> SELECT count(sql_redo) FROM V$LOGMNR_CONTENTS WHERE seg_type=2 and seg_owner IN ('SADAT');
             19
Execution Plan
----------------------------------------------------------
Plan hash value: 1325233743

---------------------------------------------------------------------------------------
| Id  | Operation         | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                   |     1 |  2059 |     0   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |                   |     1 |  2059 |            |          |
|*  2 |   FIXED TABLE FULL| X$LOGMNR_CONTENTS |     1 |  2059 |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("SEG_TYPE"=2 AND "SEG_OWNER"='SADAT' AND "ROW_TYPE"=0 AND
              "INST_ID"=USERENV('INSTANCE'))

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        522  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
Re: Interesting SQL problem [message #357351 is a reply to message #357204] Wed, 05 November 2008 00:40 Go to previous messageGo to next message
tanmoy1048
Messages: 133
Registered: June 2007
Senior Member
First I posted only the query as I thought it may be a known problem to you. Then I posted the whole session and now I have posted the explain plan.
Re: Interesting SQL problem [message #357354 is a reply to message #357351] Wed, 05 November 2008 00:45 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
V$/X$ are special, you can't assume anything about them.
You now have to open a SR to get an answer.

You can also "select *" and see what is the correct answer.

Regards
Michel
Previous Topic: Display calendar between two dates
Next Topic: Zip a File From PL/SQL
Goto Forum:
  


Current Time: Sun Dec 11 06:23:59 CST 2016

Total time taken to generate the page: 0.08210 seconds