Sorting

[Ian]

Hi,
can someone tell me why this join perform sort(saw it in access plan). Is
there a book that explains db2 query tuning, especially this kind of stuff.

SELECT
C.*,
O.*
FROM
crm.CUSTOMER C, crm.ORGANISATION O
WHERE
C.ID = O.ID
Thanks,
Ian

[Rhino]

"Ian" <ia***************@yahoo.com> wrote in message
news:cr***************************@40tude.net...

Hi,
can someone tell me why this join perform sort(saw it in access plan). Is
there a book that explains db2 query tuning, especially this kind of stuff.
SELECT
C.*,
O.*
FROM
crm.CUSTOMER C, crm.ORGANISATION O
WHERE
C.ID = O.ID

A join can require a sort of one or both tables depending on what indexes
are available.

You haven't specified your version of DB2 or your platform so I can't point
you to a specific URL that will answer your question about query tuning.
However, you probably want the Administration Guide for your
version/platform. All of the DB2 manuals are available online at the IBM
website, http://www.ibm.com.

Rhino

[Ian]

On Tue, 31 Aug 2004 08:31:58 -0400, Rhino wrote:

"Ian" <ia***************@yahoo.com> wrote in message
news:cr***************************@40tude.net...

Hi,
can someone tell me why this join perform sort(saw it in access plan). Is
there a book that explains db2 query tuning, especially this kind of

stuff.

SELECT
C.*,
O.*
FROM
crm.CUSTOMER C, crm.ORGANISATION O
WHERE
C.ID = O.ID

A join can require a sort of one or both tables depending on what indexes
are available.

You haven't specified your version of DB2 or your platform so I can't point
you to a specific URL that will answer your question about query tuning.
However, you probably want the Administration Guide for your
version/platform. All of the DB2 manuals are available online at the IBM
website, http://www.ibm.com.

Rhino

I have indexes on both ID's. Is there a way to avoid this sort?

I've already downloaded DB2 manuals for v7.2, but haven't found this sort
of info.

Thanks.

[AK]

Ian,
you might want to google on "merge join"

[Rhino]

"Ian" <ia***************@yahoo.com> wrote in message
news:1k******************************@40tude.net.. .

On Tue, 31 Aug 2004 08:31:58 -0400, Rhino wrote:

"Ian" <ia***************@yahoo.com> wrote in message
news:cr***************************@40tude.net...

Hi,
can someone tell me why this join perform sort(saw it in access plan). Is there a book that explains db2 query tuning, especially this kind of

stuff.

SELECT
C.*,
O.*
FROM
crm.CUSTOMER C, crm.ORGANISATION O
WHERE
C.ID = O.ID

A join can require a sort of one or both tables depending on what indexes are available.

You haven't specified your version of DB2 or your platform so I can't point you to a specific URL that will answer your question about query tuning.
However, you probably want the Administration Guide for your
version/platform. All of the DB2 manuals are available online at the IBM
website, http://www.ibm.com.

Rhino

I have indexes on both ID's. Is there a way to avoid this sort?

I've already downloaded DB2 manuals for v7.2, but haven't found this sort
of info.

I assume you mean DB2 V7.2 for Windows/Linux/Unix. I have the same manuals
and found a discussion that might answer your question via the search engine
in the IBM DB2/Information application. I searched on "join concepts" and
found several hits. The one that might answer your questions is in the
Administration Guide in a page entitled "Data Access Concepts and
Optimization". Near the very top of that page, is a internal link for "Join
Concepts". If you click on it, you go much further down that same long page
and come to a discussion of the three DB2 Join types: nested loop, merge
join and hash join. This may help you. Or not.

It isn't clear from your note whether you are getting one sort or two so I
don't know if you are getting a merge join or a hash join. That would be a
useful thing to know if you can determine that information. Do you know how
to use any of the forms of Explain? Any form of Explain should tell you
which type of join you're doing. Explain is described in detail in the
Administration Guide, Chapter 26.

I suggest that you do an Explain first to determine exactly which kind of
join you are getting. Then read the Join Concepts section for some insight
into how that type of join works and when/why the Optimizer chooses it. If
that doesn't answer your question, please post again with at least the join
type and maybe someone here can help you further.

Rhino

[AK]

>

I have indexes on both ID's. Is there a way to avoid this sort?

OPTIMIZE FOR FIRST xx ROWS clause might get rid of the sort.
However, comparing real execution costs might indicate that the plan
involving sort is actually better.

Another common way to avoid sorts is to reorganize the child table
(and maybe also the parent one) on the FK / PK indexes

[Ian]

On Tue, 31 Aug 2004 13:12:48 -0400, Rhino wrote:

"Ian" <ia***************@yahoo.com> wrote in message
news:1k******************************@40tude.net.. .

On Tue, 31 Aug 2004 08:31:58 -0400, Rhino wrote:

"Ian" <ia***************@yahoo.com> wrote in message
news:cr***************************@40tude.net...
Hi,
can someone tell me why this join perform sort(saw it in access plan). Is there a book that explains db2 query tuning, especially this kind of
stuff.

SELECT
C.*,
O.*
FROM
crm.CUSTOMER C, crm.ORGANISATION O
WHERE
C.ID = O.ID
A join can require a sort of one or both tables depending on what indexes are available.

I have indexes on both ID's. Is there a way to avoid this sort?

I've already downloaded DB2 manuals for v7.2, but haven't found this sort
of info.

I assume you mean DB2 V7.2 for Windows/Linux/Unix. I have the same manuals
and found a discussion that might answer your question via the search engine
in the IBM DB2/Information application. I searched on "join concepts" and

Rhino, AK thank you both. I run dynexpln and found that query is using
merge join. I also reorganized both tables but sort is stil here.

I will read those chapters you suggested and try to solve this.
This is output from dynexpln:

------------------------------------------------------
Estimated Cost = 128906
Estimated Cardinality = 159397

Access Table Name = CRM.ORGANISATION ID = 2,97
| #Columns = 7
| Relation Scan
| | Prefetch: Eligible
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Insert Into Sorted Temp Table ID = t1
| | #Columns = 7
| | #Sort Key Columns = 1
| | | Key 1: ID (Ascending)
| | Sortheap Allocation Parameters:
| | | #Rows = 159397
| | | Row Width = 68
| | Piped
Sorted Temp Table Completion ID = t1
Access Temp Table ID = t1
| #Columns = 7
| Relation Scan
| | Prefetch: Eligible
Merge Join
| Early Out: Single Match Per Outer Row
| Access Table Name = CRM.CUSTOMER ID = 2,74
| | #Columns = 17
| | Index Scan: Name = CRM.SQL040720111132250 ID = 1
| | | Index Columns:
| | | | 1: ID (Ascending)
| | | #Key Columns = 0
| | | | Start Key: Beginning of Index
| | | | Stop Key: End of Index
| | | Data Prefetch: Eligible 0
| | | Index Prefetch: None
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
Return Data to Application
| #Columns = 24
---------------------------------------------------------
Thanks, Ian