DB2 SELECT COUNT(*) problem

Bing Wu,

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

Bing Wu,

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

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.
>>
>

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.
>>
>

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.
>>
>

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,

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" a ?rit dans le message de
> news:bqnkr9$qa4$1@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,

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" a ?rit dans le message de
> news:bqnkr9$qa4$1@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,

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" a ?rit dans le message de
> news:bqnkr9$qa4$1@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
>>>
>>
>
>