DB2 SELECT COUNT(*) problem

**Wolfgang Riedel** · Nov 12 '05, 05:58 AM

Re: DB2 SELECT COUNT(*) problem

Bing Wu wrote:[color=blue]
>
> 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[/color]

try:

select count(1) from ...

and see.

hth.
Wolfgang

**Bing Wu** · Nov 12 '05, 05:58 AM

Re: DB2 SELECT COUNT(*) problem

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

Bing

Wolfgang Riedel wrote:[color=blue]
>
> try:
>
> select count(1) from ...
>
> and see.
>
> hth.
> Wolfgang[/color]

**MCPHEAL** · Nov 12 '05, 05:58 AM

Re: DB2 SELECT COUNT(*) problem

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

have you ran explain ? maybe an index would help

**Jing Li** · Nov 12 '05, 05:58 AM

Re: DB2 SELECT COUNT(*) problem

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" <bing@biop.ox.a c.uk> wrote in message
news:bqiesa$msf $1@news.ox.ac.u k...[color=blue]
> Hi,
>
> Has anyone got problem with this on large table. It seems it take ages to[/color]
return the result ( half an hour :-( ).[color=blue]
>
> SELECT COUNT(*) FROM COORDINATE
>
> A strange thing is the tempory space (System Managed) keeps growing during[/color]
the query:[color=blue]
>
> 14346747904 Dec 2 16:22 SQL00002.TDA
>
> It's almost as large as the current table (17GB). Any comments?
>
> Many thanks,
>
> Bing
>[/color]

**miro flasza** · Nov 12 '05, 05:58 AM

Re: DB2 SELECT COUNT(*) problem

Bing Wu wrote:[color=blue]
> 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
>[/color]

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** · Nov 12 '05, 05:59 AM

Re: DB2 SELECT COUNT(*) problem

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"."DYN EXPLN" 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.COORDI NATE 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:[color=blue]
>
> 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
>[/color]

**Bing Wu** · Nov 12 '05, 05:59 AM

Re: DB2 SELECT COUNT(*) problem

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

Thanks,

Bing

Jing Li wrote:[color=blue]
> 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.
>[/color]

AK · Nov 12 '05, 05:59 AM

Re: DB2 SELECT COUNT(*) problem

Bing Wu,

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

**Blair Adamache** · Nov 12 '05, 05:59 AM

Re: DB2 SELECT COUNT(*) problem

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:
[color=blue]
> Yes, it's indexed and organised. I'll try the reorg and see if it improves.
>
> Thanks,
>
> Bing
>
> Jing Li wrote:
>[color=green]
>> 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.
>>[/color]
>[/color]

**Jean-Marc Blaise** · Nov 12 '05, 05:59 AM

Re: DB2 SELECT COUNT(*) problem

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

Jean-Marc

"Bing Wu" <bing@biop.ox.a c.uk> a écrit dans le message de
news:bqnkr9$qa4 $1@news.ox.ac.u k...[color=blue]
> Yes, it's a table with over a billion rows. I have run the SQL explain and[/color]
please find the result below.[color=blue]
>
> Thanks again,
>
> Bing
>
>
> Package Name = "DB2ADMIN"."DYN EXPLN" 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",[/color]
"DB2ADMIN"[color=blue]
>
> -------------------- 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.COORDI NATE 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:[color=green]
> >
> > 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
> >[/color]
>[/color]

**Bing Wu** · Nov 12 '05, 06:00 AM

Re: DB2 SELECT COUNT(*) problem

Jean-Marc,

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

Thanks,

Bing

Jean-Marc Blaise wrote:[color=blue]
> Bing, is the access plan with a default query set to 1 the same - relation
> scan and not index scan ?
>
> Jean-Marc
>
> "Bing Wu" <bing@biop.ox.a c.uk> a ?rit dans le message de
> news:bqnkr9$qa4 $1@news.ox.ac.u k...
>[color=green]
>>Yes, it's a table with over a billion rows. I have run the SQL explain and[/color]
>
> please find the result below.
>[color=green]
>>Thanks again,
>>
>>Bing
>>
>>
>>Package Name = "DB2ADMIN"."DYN EXPLN" 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",[/color]
>
> "DB2ADMIN"
>[color=green]
>>-------------------- 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.COORDI NATE 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:
>>[color=darkred]
>>>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
>>>[/color]
>>[/color]
>
>[/color]

**Jean-Marc Blaise** · Nov 12 '05, 06:00 AM

Re: DB2 SELECT COUNT(*) problem

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" <bing@biop.ox.a c.uk> a écrit dans le message de
news:bqq1i4$ol1 $1@news.ox.ac.u k...[color=blue]
> Jean-Marc,
>
> Do you mean: SET CURRENT QUERY OPTIMIZATION 1? The current value is 7 at[/color]
the mo.[color=blue]
>
> Thanks,
>
> Bing
>
> Jean-Marc Blaise wrote:[color=green]
> > Bing, is the access plan with a default query set to 1 the same -[/color][/color]
relation[color=blue][color=green]
> > scan and not index scan ?
> >
> > Jean-Marc
> >
> > "Bing Wu" <bing@biop.ox.a c.uk> a ?rit dans le message de
> > news:bqnkr9$qa4 $1@news.ox.ac.u k...
> >[color=darkred]
> >>Yes, it's a table with over a billion rows. I have run the SQL explain[/color][/color][/color]
and[color=blue][color=green]
> >
> > please find the result below.
> >[color=darkred]
> >>Thanks again,
> >>
> >>Bing
> >>
> >>
> >>Package Name = "DB2ADMIN"."DYN EXPLN" 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",[/color]
> >
> > "DB2ADMIN"
> >[color=darkred]
> >>-------------------- 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.COORDI NATE 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
> >>>
> >>[/color]
> >
> >[/color]
>[/color]

DB2 SELECT COUNT(*) problem

DB2 SELECT COUNT(*) problem

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment