DB2 SELECT COUNT(*) problem

[Bing Wu]

Hi,

Has anyone got problem with this on large table. It seems it take ages to return the result ( half an hour :-( ).

SELECT COUNT(*) FROM COORDINATE

A strange thing is the tempory space (System Managed) keeps growing during the query:

14346747904 Dec 2 16:22 SQL00002.TDA

It's almost as large as the current table (17GB). Any comments?

Many thanks,

Bing

[Wolfgang Riedel]

Bing Wu wrote:

Hi,

Has anyone got problem with this on large table. It seems it take ages to return the result ( half an hour :-( ).

SELECT COUNT(*) FROM COORDINATE

A strange thing is the tempory space (System Managed) keeps growing during the query:

14346747904 Dec 2 16:22 SQL00002.TDA

It's almost as large as the current table (17GB). Any comments?

Many thanks,

Bing

try:

select count(1) from ...

and see.

hth.
Wolfgang

[Bing Wu]

Many thanks for the hint. It seems still taking long time.

Bing
Wolfgang Riedel wrote:

try:

select count(1) from ...

and see.

hth.
Wolfgang

[MCPHEAL]

>Has anyone got problem with this on large table. It seems it take ages to

return the result ( half an hour :-( ).

SELECT COUNT(*) FROM COORDINATE

have you ran explain ? maybe an index would help

[Jing Li]

Is there any index for the table?
Maybe you should reorg the TS and Indexspace to optimize it.
Form the result count, it's a huge table, if it's not well orgnized, it'll
need a long time to get a result of course.

"Bing Wu" <bi**@biop.ox.ac.uk> wrote in message
news:bq**********@news.ox.ac.uk...

Hi,

Has anyone got problem with this on large table. It seems it take ages to return the result ( half an hour :-( ).
SELECT COUNT(*) FROM COORDINATE

A strange thing is the tempory space (System Managed) keeps growing during the query:
14346747904 Dec 2 16:22 SQL00002.TDA

It's almost as large as the current table (17GB). Any comments?

Many thanks,

Bing

[miro flasza]

Bing Wu wrote:

Hi,

Has anyone got problem with this on large table. It seems it take ages
to return the result ( half an hour :-( ).

SELECT COUNT(*) FROM COORDINATE

A strange thing is the tempory space (System Managed) keeps growing
during the query:

14346747904 Dec 2 16:22 SQL00002.TDA

It's almost as large as the current table (17GB). Any comments?

Many thanks,

Bing

Are you certain that COORDINATE is a table and not a view? Normally
a simple query like this should not require any temp space. As someone
already mentioned in this thread, get an explain for the query. Once
you do, post it here, maybe someone will be able to help you then.

Regards,
Miro

[Bing Wu]

Yes, it's a table with over a billion rows. I have run the SQL explain and please find the result below.

Thanks again,

Bing
Package Name = "DB2ADMIN"."DYNEXPLN" Version = ""

Prep Date = 2003/12/04
Prep Time = 15:36:33

Bind Timestamp = 2003-12-04-15.36.33.960521

Isolation Level = Cursor Stability
Blocking = Block Unambiguous Cursors
Query Optimization Class = 7

Partition Parallel = No
Intra-Partition Parallel = Yes (Bind Degree = ANY)

SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "DB2ADMIN"

-------------------- SECTION ---------------------------------------
Section = 1
SQL Statement:
DECLARE C1 CURSOR
FOR
SELECT COUNT(*)
FROM COORDINATE

Intra-Partition Parallelism Degree = 2

Section Code Page = 819

Estimated Cost = 8889258.000000
Estimated Cardinality = 1.000000

Process Using 2 Subagents
| Access Table Name = DB2ADMIN.COORDINATE ID = 4,4
| | #Columns = 0
| | Parallel Scan
| | Relation Scan
| | | Prefetch: Eligible
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
| | Sargable Predicate(s)
| | | Partial Predicate Aggregation
| | | | Column Function(s)
| Partial Aggregation Completion
| | Column Function(s)
| Insert Into Asynchronous Local Table Queue ID = q1
Access Local Table Queue ID = q1 #Columns = 1
Final Aggregation
| Column Function(s)
Return Data to Application
| #Columns = 1

End of section
miro flasza wrote:

Are you certain that COORDINATE is a table and not a view? Normally
a simple query like this should not require any temp space. As someone
already mentioned in this thread, get an explain for the query. Once
you do, post it here, maybe someone will be able to help you then.

Regards,
Miro

[Bing Wu]

Yes, it's indexed and organised. I'll try the reorg and see if it improves.

Thanks,

Bing

Jing Li wrote:

Is there any index for the table?
Maybe you should reorg the TS and Indexspace to optimize it.
Form the result count, it's a huge table, if it's not well orgnized, it'll
need a long time to get a result of course.

[AK]

Bing Wu,

do you have any indexes on the table at all?
Have you run reorgchk?

[Blair Adamache]

Likely unrelated, but what happens if you do a select count_big(*)
against the table? Have you done a runstats on the table? Runstats on
its indexes? Can you define a unique index on it (sorry if you'd heard
these sugggestions before)?

Bing Wu wrote:

Yes, it's indexed and organised. I'll try the reorg and see if it improves.

Thanks,

Bing

Jing Li wrote:

Is there any index for the table?
Maybe you should reorg the TS and Indexspace to optimize it.
Form the result count, it's a huge table, if it's not well orgnized,
it'll
need a long time to get a result of course.

[Jean-Marc Blaise]

Bing, is the access plan with a default query set to 1 the same - relation
scan and not index scan ?

Jean-Marc

"Bing Wu" <bi**@biop.ox.ac.uk> a écrit dans le message de
news:bq**********@news.ox.ac.uk...

Yes, it's a table with over a billion rows. I have run the SQL explain and please find the result below.
Thanks again,

Bing
Package Name = "DB2ADMIN"."DYNEXPLN" Version = ""

Prep Date = 2003/12/04
Prep Time = 15:36:33

Bind Timestamp = 2003-12-04-15.36.33.960521

Isolation Level = Cursor Stability
Blocking = Block Unambiguous Cursors
Query Optimization Class = 7

Partition Parallel = No
Intra-Partition Parallel = Yes (Bind Degree = ANY)

SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "DB2ADMIN"
-------------------- SECTION ---------------------------------------
Section = 1
SQL Statement:
DECLARE C1 CURSOR
FOR
SELECT COUNT(*)
FROM COORDINATE

Intra-Partition Parallelism Degree = 2

Section Code Page = 819

Estimated Cost = 8889258.000000
Estimated Cardinality = 1.000000

Process Using 2 Subagents
| Access Table Name = DB2ADMIN.COORDINATE ID = 4,4
| | #Columns = 0
| | Parallel Scan
| | Relation Scan
| | | Prefetch: Eligible
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
| | Sargable Predicate(s)
| | | Partial Predicate Aggregation
| | | | Column Function(s)
| Partial Aggregation Completion
| | Column Function(s)
| Insert Into Asynchronous Local Table Queue ID = q1
Access Local Table Queue ID = q1 #Columns = 1
Final Aggregation
| Column Function(s)
Return Data to Application
| #Columns = 1

End of section
miro flasza wrote:

Are you certain that COORDINATE is a table and not a view? Normally
a simple query like this should not require any temp space. As someone
already mentioned in this thread, get an explain for the query. Once
you do, post it here, maybe someone will be able to help you then.

Regards,
Miro

[Bing Wu]

Jean-Marc,

Do you mean: SET CURRENT QUERY OPTIMIZATION 1? The current value is 7 at the mo.

Thanks,

Bing

Jean-Marc Blaise wrote:

Bing, is the access plan with a default query set to 1 the same - relation
scan and not index scan ?

Jean-Marc

"Bing Wu" <bi**@biop.ox.ac.uk> a ?rit dans le message de
news:bq**********@news.ox.ac.uk...

Yes, it's a table with over a billion rows. I have run the SQL explain and

please find the result below.

Thanks again,

Bing
Package Name = "DB2ADMIN"."DYNEXPLN" Version = ""

Prep Date = 2003/12/04
Prep Time = 15:36:33

Bind Timestamp = 2003-12-04-15.36.33.960521

Isolation Level = Cursor Stability
Blocking = Block Unambiguous Cursors
Query Optimization Class = 7

Partition Parallel = No
Intra-Partition Parallel = Yes (Bind Degree = ANY)

SQL Path = "SYSIBM", "SYSFUN", "SYSPROC",

"DB2ADMIN"

-------------------- SECTION ---------------------------------------
Section = 1
SQL Statement:
DECLARE C1 CURSOR
FOR
SELECT COUNT(*)
FROM COORDINATE

Intra-Partition Parallelism Degree = 2

Section Code Page = 819

Estimated Cost = 8889258.000000
Estimated Cardinality = 1.000000

Process Using 2 Subagents
| Access Table Name = DB2ADMIN.COORDINATE ID = 4,4
| | #Columns = 0
| | Parallel Scan
| | Relation Scan
| | | Prefetch: Eligible
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
| | Sargable Predicate(s)
| | | Partial Predicate Aggregation
| | | | Column Function(s)
| Partial Aggregation Completion
| | Column Function(s)
| Insert Into Asynchronous Local Table Queue ID = q1
Access Local Table Queue ID = q1 #Columns = 1
Final Aggregation
| Column Function(s)
Return Data to Application
| #Columns = 1

End of section
miro flasza wrote:

Are you certain that COORDINATE is a table and not a view? Normally
a simple query like this should not require any temp space. As someone
already mentioned in this thread, get an explain for the query. Once
you do, post it here, maybe someone will be able to help you then.

Regards,
Miro

[Jean-Marc Blaise]

No, I'm speaking about SET CURRENT DEGREE = '1' , to inhibate the intra
parallelism and see what's the explain gives. You can either set the
max_querydegree parameter in the DB CFG to 1 and change it back, online.

Cheers,

Jean-Marc
"Bing Wu" <bi**@biop.ox.ac.uk> a écrit dans le message de
news:bq**********@news.ox.ac.uk...

Jean-Marc,

Do you mean: SET CURRENT QUERY OPTIMIZATION 1? The current value is 7 at the mo.
Thanks,

Bing

Jean-Marc Blaise wrote:

Bing, is the access plan with a default query set to 1 the same - relation scan and not index scan ?

Jean-Marc

"Bing Wu" <bi**@biop.ox.ac.uk> a ?rit dans le message de
news:bq**********@news.ox.ac.uk...

Yes, it's a table with over a billion rows. I have run the SQL explain

and
please find the result below.

Thanks again,

Bing
Package Name = "DB2ADMIN"."DYNEXPLN" Version = ""

Prep Date = 2003/12/04
Prep Time = 15:36:33

Bind Timestamp = 2003-12-04-15.36.33.960521

Isolation Level = Cursor Stability
Blocking = Block Unambiguous Cursors
Query Optimization Class = 7

Partition Parallel = No
Intra-Partition Parallel = Yes (Bind Degree = ANY)

SQL Path = "SYSIBM", "SYSFUN", "SYSPROC",

"DB2ADMIN"

-------------------- SECTION ---------------------------------------
Section = 1
SQL Statement:
DECLARE C1 CURSOR
FOR
SELECT COUNT(*)
FROM COORDINATE

Intra-Partition Parallelism Degree = 2

Section Code Page = 819

Estimated Cost = 8889258.000000
Estimated Cardinality = 1.000000

Process Using 2 Subagents
| Access Table Name = DB2ADMIN.COORDINATE ID = 4,4
| | #Columns = 0
| | Parallel Scan
| | Relation Scan
| | | Prefetch: Eligible
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
| | Sargable Predicate(s)
| | | Partial Predicate Aggregation
| | | | Column Function(s)
| Partial Aggregation Completion
| | Column Function(s)
| Insert Into Asynchronous Local Table Queue ID = q1
Access Local Table Queue ID = q1 #Columns = 1
Final Aggregation
| Column Function(s)
Return Data to Application
| #Columns = 1

End of section
miro flasza wrote:

Are you certain that COORDINATE is a table and not a view? Normally
a simple query like this should not require any temp space. As someone
already mentioned in this thread, get an explain for the query. Once
you do, post it here, maybe someone will be able to help you then.

Regards,
Miro