Difference of rows in SELECT COUNT(*) and SELECT *

Database
Teradata Employee

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
7 REPLIES
Enthusiast

Re: Difference of rows in SELECT COUNT(*) and SELECT *

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

Enthusiast

Re: Difference of rows in SELECT COUNT(*) and SELECT *

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,

Enthusiast

Re: Difference of rows in SELECT COUNT(*) and SELECT *

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

Enthusiast

Re: Difference of rows in SELECT COUNT(*) and SELECT *

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

Senior Supporter

Re: Difference of rows in SELECT COUNT(*) and SELECT *

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.

Enthusiast

Re: Difference of rows in SELECT COUNT(*) and SELECT *

Clear,

thank you Ulrich,

Pierre

Enthusiast

Re: Difference of rows in SELECT COUNT(*) and SELECT *

My guess is soft ri as well.