Home » SQL & PL/SQL » SQL & PL/SQL » Database link not working for count(1) but works for count(*) (10.2.0.4.0 on RHEL4)
Database link not working for count(1) but works for count(*) [message #321254] Mon, 19 May 2008 11:57 Go to next message
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
Senior Member
Hi,

I am using oracle 10.2.0.4.0 on RHEL

I have 2 databases : db1 and db2

I have user1 on both the databases (with dba privileges)

I have created database link from db1 to db2

create public database link conn_db2.myco.com connect to user1 identified by user1_pwd using 'db2';

I can execute
Select count(*) from process_log@conn_db2.myco.com

But while I execute
Select count(1) from process_log@conn_db2.myco.com;

I get an error

"ora-01008: not all variables are bound
ora-02063: preceding line from conn_db2"

What could be the reason?

Thanks and Regards,
OraKaran

Report message to a moderator

Re: Database link not working for count(1) but works for count(*) [message #321260 is a reply to message #321254] Mon, 19 May 2008 12:10 Go to previous messageGo to next message
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
count(1) is a bug you must NEVER use it, ALWAYS use count(*).

Regards
Michel

Report message to a moderator

Re: Database link not working for count(1) but works for count(*) [message #321261 is a reply to message #321260] Mon, 19 May 2008 12:13 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Why is count(1) a bug?
Sure, it doesn't differ in any way from count(*) and using it shows that one doesn't know that, but it is not a bug.
Nothing happens (as far as I know) when you do use count(1) or count('myfingers') instead of count(*)

Apparently things seem to go wrong over database-links. Is this related to the use of the "1"? Any more known issues with it?

[Updated on: Mon, 19 May 2008 12:14]

Report message to a moderator

Re: Database link not working for count(1) but works for count(*) [message #321263 is a reply to message #321254] Mon, 19 May 2008 12:24 Go to previous messageGo to next message
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
Senior Member
Thanks for quick help!!

Frank,
It's only COUNT(1) which is giving problem, rest things are fine

Thanks and Regards,
OraKaran

Report message to a moderator

Re: Database link not working for count(1) but works for count(*) [message #321267 is a reply to message #321261] Mon, 19 May 2008 12:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Count(1) is a bug because:
1/ logically, it is meaningless (you want to count rows not "1"), as you said why not another value?
2/ it is internally converted to count(*) but as it leads to more internal code it is opened to more Oracle bugs as it seems for this one

If you don't want to use count(*) (I don't know any reason), try to use count('OraKaran'), it has some chances to work.

Regards
Michel

Report message to a moderator

Re: Database link not working for count(1) but works for count(*) [message #321276 is a reply to message #321254] Mon, 19 May 2008 13:16 Go to previous messageGo to next message
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
Senior Member
Many Thanks Michel/ Frank !!

As you have suggested it seems i headed a Bug and shall stick to count(*).

Regards,
OraKaran

Report message to a moderator

Re: Database link not working for count(1) but works for count(*) [message #321400 is a reply to message #321267] Tue, 20 May 2008 03:05 Go to previous messageGo to next message
me_arindam
Messages: 26
Registered: March 2008
Location: India
Junior Member
Hi Michel,

does count(1) consider the primary key of the table when use like:

select count(1) from t1.

As there is always an index in primary key column so it would be pretty much faster than count(*).

Could you please explain if it is wrong.

Thanks,
Arindam

Report message to a moderator

Re: Database link not working for count(1) but works for count(*) [message #321404 is a reply to message #321400] Tue, 20 May 2008 03:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
does count(1) consider the primary key of the table when use like:

No more, no less than count(*).

Regards
Michel

Report message to a moderator

Re: Database link not working for count(1) but works for count(*) [message #321509 is a reply to message #321404] Tue, 20 May 2008 09:30 Go to previous messageGo to next message
me_arindam
Messages: 26
Registered: March 2008
Location: India
Junior Member
Thank You..

Report message to a moderator

Re: Database link not working for count(1) but works for count(*) [message #321537 is a reply to message #321254] Tue, 20 May 2008 11:59 Go to previous messageGo to next message
drewsmith70
Messages: 22
Registered: April 2008
Location: New Hampshire
Junior Member
I just tried count(1) over a db link on 10.2, and it seems to work OK. Why is it a bug? I agree that you should always use count(*) or count(indexed_column), but I think count(1) is internally converted to count(*) anyway (it just means "count rows with non-null value 1" - doesn't mean anything to me).

But as of Oracle 11, here's something that should convince you to use count(*) vs. count(1):

asmith@DEV> create table t1 (c1 number);

Table created.

asmith@DEV> create view v1 as
  2  select count(1) count from t1;

View created.

asmith@DEV> create view v2 as 
  2  select count(*) count from t1;

View created.

asmith@DEV> select object_name, object_type, status
  2  from user_objects
  3  where (object_name in ('V1','V2'))
  4  and object_type = 'VIEW';

OBJECT_NAME
----------------------------
OBJECT_TYPE         STATUS
------------------- -------
V1
VIEW                VALID

V2
VIEW                VALID


asmith@DEV> alter table t1 add (c2 number);

Table altered.

asmith@DEV> select object_name, object_type, status
  2  from user_objects
  3  where (object_name in ('V1','V2'))
  4  and object_type = 'VIEW';

OBJECT_NAME
----------------------------
OBJECT_TYPE         STATUS
------------------- -------
V1
VIEW                INVALID

V2
VIEW                VALID


A view using count(*) on a table will remain valid when columns are added to the table.

See:
http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/dependencies.htm#insertedID4

Report message to a moderator

Re: Database link not working for count(1) but works for count(*) [message #321540 is a reply to message #321537] Tue, 20 May 2008 12:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
I just tried count(1) over a db link on 10.2, and it seems to work OK. Why is it a bug?

It is a logical/code bug (not a real bug) and you just prove it with your example and as the internal bug over db link in earlier version also proves it.
This a bug in the same way as "WHEN OTHERS THEN NULL" is a bug something that you must not do in correct programming.

Regards
Michel

Report message to a moderator

Re: Database link not working for count(1) but works for count(*) [message #321562 is a reply to message #321540] Tue, 20 May 2008 13:50 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
I'd never consider count(1) to be any kind of "bug" unless Oracle says it is. AskTom doesn't seem to think so:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1156159920245

I'd never reject someone's code during a review beacuse they used count(1). From my perspective it's a style issue. Personally I always use count(*).

Regarding the view becoming invalid above: In general, invalid views will always automatically recompile when you access them, so if you can select from the invalid view without getting an error, I wouldn't call it an error just because it behaves differently to another example. Interesting though - I haven't seen that test case before.

Most of the metalink hits regarding ora-01008 I saw are related to cursor sharing. I found nothing related specifically to count(1). The cursor sharing would tie in with Michel's comment about internally re-writing as count(*).

Report message to a moderator

Re: Database link not working for count(1) but works for count(*) [message #321673 is a reply to message #321562] Wed, 21 May 2008 00:34 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member
Even i personally prefer to user count(*), but i cannot get why the count(1) is considered as bug by Michel, though it is converted by oracle internally to count(*)? His openion is important for me..

Interesting testcase by Mr. Smith.
Any other such testcase, anyone came to face where count(*) and count(1) behave differenty?
This is important to know for us because may of the developer used to use count(1) instade of count(*).

Report message to a moderator

Re: Database link not working for count(1) but works for count(*) [message #321677 is a reply to message #321673] Wed, 21 May 2008 00:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
vithalani_dipali wrote on Wed, 21 May 2008 07:34
Even i personally prefer to user count(*), but i cannot get why the count(1) is considered as bug by Michel, though it is converted by oracle internally to count(*)?

Just my opinion, don't care but the 2 points raised here (OP's error and Drew's invalidation, and there surely are other cases) should lead everyone to use the correct count(*).

Regards
Michel

Report message to a moderator

Re: Database link not working for count(1) but works for count(*) [message #322251 is a reply to message #321677] Thu, 22 May 2008 22:59 Go to previous message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member
You are right Mr. cadot.

Report message to a moderator

Previous Topic: list of tables containing no data
Next Topic: Global Temp Tables and PL/SQL
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri May 16 09:57:37 CDT 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.