All Forums Database
Adeel Chaudhry 773 posts Joined 04/08
03 Aug 2011
Difference of rows in SELECT COUNT(*) and SELECT *

Hi,

I am facing a weird problem of which can not really figure out what could be the reason of that. Consider following SELECT statement:

SELECT
Col1, Col2, Col3
FROM
View1
QUALIFY ROW_NUMBER() OVER(PARTITION BY Col4 ORDER BY Col5) = 1

We get 644,539 rows in return.

If we take a COUNT(*) as below:

SELECT COUNT(*) FROM
(
SELECT
Col1, Col2, Col3
FROM
View1
QUALIFY ROW_NUMBER() OVER(PARTITION BY Col4 ORDER BY Col5) = 1
) A

It gives us count to be 158,446

If we do SELECT * as below:

SELECT * FROM
(
SELECT
Col1, Col2, Col3
FROM
View1
QUALIFY ROW_NUMBER() OVER(PARTITION BY Col4 ORDER BY Col5) = 1
) A

It gives us count of return rows to be 644,539 same as the first case.

In case we remove the list if columns from the inner select both counts matches to 158,446, SQL as below:

SELECT COUNT(*) FROM
(
SELECT
*
FROM
View1
QUALIFY ROW_NUMBER() OVER(PARTITION BY Col4 ORDER BY Col5) = 1
) A

SELECT * FROM
(
SELECT
*
FROM
View1
QUALIFY ROW_NUMBER() OVER(PARTITION BY Col4 ORDER BY Col5) = 1
) A

Can anyone explain the causes of this behavior?

Regards, MAC

-- If you are stuck at something .... consider it an opportunity to think anew.
AbeK 24 posts Joined 08/09
26 Sep 2011

If you can attach the explain, it might be helpful. Also, check if you have any Soft Referential integrity constraints on the objects invovled.

26 Sep 2011

Hi All,

this is the first time i am posting my question in Teradata Forum, Please for give my ignorance if it is incorrect site.

I have to create a view , I would like to know which one is better

AS LOCKING ROW IN ACCESS or

LOCKING TABLE tablename FOR ACCESS, I read documentation but not giving fair idea.

 

Thanks,

27 Sep 2011

LOCKING ROW FOR ACCESS would be a better option ...

LUCAS 56 posts Joined 06/09
18 Jan 2012

Hi,

I have the same interrogation: LOCKING ROW FOR ACCESS is said to be the best ... but why ?

Doc is not so much clear about that:

"Locking an entire base table across all AMPS is undesirable, and the use of LOCKING ROW here prevents the need to lock an entire base table across all AMPs."

So LOCKING TABLE is costly and inappropriate ?

But you can find this :

"The LOCKING ROW modifier cannot be used to lock multiple row hashes. If LOCKING ROW FOR ACCESS is specified with multiple row hashes, the declaration implicitly converts to LOCKING TABLE FOR ACCESS."

The conversion from "Row" to "table" should be costly too, when needed.

So it depends ...

Thanks for comments !

Pierre

ulrich 816 posts Joined 09/09
18 Jan 2012

Hi,

lock row for access has the benefit that the DB detects which need to be done.

If you access only one PI value it will place a row lock for access.

If you access the whole table it will place a table lock for access.

Where if you specify a lock table for access it will be always a table lock even if you select only one PI.

So you have the possibility of an upside if you use lock row for access without an penalty.

 

 

 

 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

LUCAS 56 posts Joined 06/09
18 Jan 2012

Clear,

thank you Ulrich,

Pierre

JimmyLee 13 posts Joined 06/10
18 Jan 2012

My guess is soft ri as well.

"To not give your best is to sacrifice the gift" -- Steve Prefontaine

You must sign in to leave a comment.