Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Regarding effeciency of joins

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
rajesh_m

New User


Joined: 15 Apr 2008
Posts: 39
Location: India

PostPosted: Thu May 15, 2008 3:30 pm    Post subject: Regarding effeciency of joins
Reply with quote

select a.col1,a.col2,b.col1 from tab1 a,tab2 b where a.colx=b.coly;

In the above query colx is less in count than coly. Then this is efficent or not that is my doubt.

which is efficient a.colx=b.coly or b.coly=a.colx

Any help is appreciatable.
Back to top
View user's profile Send private message

stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Thu May 15, 2008 6:38 pm    Post subject:
Reply with quote

Why don't you run an explain statement and check the results?
Back to top
View user's profile Send private message
rajesh_m

New User


Joined: 15 Apr 2008
Posts: 39
Location: India

PostPosted: Thu May 15, 2008 6:47 pm    Post subject:
Reply with quote

Steve,
stodolas wrote:
Why don't you run an explain statement and check the results?


Can you tell me how to do that. Thanks in advance.
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Thu May 15, 2008 6:49 pm    Post subject:
Reply with quote

Check the manuals linked from the top of the page. They can describe the syntax of EXPLAIN and what the results mean better than I can.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6970
Location: porcelain throne

PostPosted: Thu May 15, 2008 7:08 pm    Post subject:
Reply with quote

there is usually a chapter in every programming/sql guide on explain, how to set up your explain table and how to code the sql 'FOR EXPLAIN OF'
Back to top
View user's profile Send private message
Suresh Ponnusamy

Active User


Joined: 22 Feb 2008
Posts: 107
Location: New York

PostPosted: Wed May 28, 2008 7:38 pm    Post subject:
Reply with quote

Hi Rajesh,

Actually we do not need to worry about the order in which to specify the columns.

In DB2 8, DB2 Optimizer itself will calculate the time taken to execute the query and rewrites the query to access the shortest access path.

And also only, it matters that the difference in no. of records between colx and coly is huge. Say colx has records in 100 and coly has records in million. If the difference is very minimal, then it does not matter.

Usually Db2 starts executing the query from Right to Left.

So if we specify the column which has less no. of records on the right side would give better performance provided both columns are properly indexed.

Hope this helps.
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Wed May 28, 2008 7:56 pm    Post subject:
Reply with quote

<thread-jack>

Quote:
Usually Db2 starts executing the query from Right to Left.


Is a misleading statement. DB2 more correctly evaluates queries that need to be satisfied to evaluate other conditions. It may need to satisfy sub-queries that are need to evaluate WHERE clauses. Given the following, it is doing an inside->out evaluation.

Code:

SELECT  A.TICKET_ID                                 
        ,A.TICKET_CATEGORY
        ,A.LAST_UPDATE_USER_ID
        ,(SELECT MAX(AT.ARCHIVE_TIME) FROM TABOWN.ARCHIVE_T AT)
        ,A.TICKET_STATUS
        ,A.CONTACT_PHONE_NUM
        ,DATE(A.CLOSED_TS) AS CLOSED_DT           
        ,DATE(A.OPENED_TS)                               
        ,A.CLOSED_RSN
FROM TABOWN.TICKET_T A                           
JOIN (SELECT Y.TICKET_ID   , Y.FIRST_EFF_TS             
      FROM TABOWN.TICKET_T Y                     
      JOIN (SELECT Z.TICKET_ID,                     
                   MIN(Z.CLOSED_TS) AS minEFFDATE 
            FROM TABOWN.TICKET_T Z               
            WHERE Z.TICKET_STATUS = 'C'           
            GROUP BY TICKET_ID) MT                   
            ON Y.TICKET_ID = MT.TICKET_ID       
            AND Y.CLOSED_TS = MT.minEFFDATE) B     
      ON A.TICKET_ID = B.TICKET_ID                 
      AND A.CLOSED_TS = B.CLOSED_TS             
WHERE DATE(A.CLOSED_TS) >= (CURRENT date - 45 DAYS)
    WITH UR 


</thread-jack>
Back to top
View user's profile Send private message
Suresh Ponnusamy

Active User


Joined: 22 Feb 2008
Posts: 107
Location: New York

PostPosted: Wed May 28, 2008 8:09 pm    Post subject:
Reply with quote

Yes Steve. That is true.

Just to reformat my statement

If a query is not having any sub query and it is simple JOIN between two tables then it will start from right to left.

If it has sub query in a JOIN, then the Sub query will get executed first that too from right to left.

Kindly let me know in case of any concerns.
Back to top
View user's profile Send private message
rajesh_m

New User


Joined: 15 Apr 2008
Posts: 39
Location: India

PostPosted: Fri May 30, 2008 5:55 pm    Post subject:
Reply with quote

Thanks Suresh and steve for your valuable inputs.

Still i am not clear ... plz summarize.
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Fri May 30, 2008 6:47 pm    Post subject:
Reply with quote

Run the explains on the queries and check the actual real world results for yourself.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Need help in Joins rajendra kalepu JCL & VSAM 9 Wed Oct 30, 2013 10:11 pm
No new posts Sort Joins umanaga DFSORT/ICETOOL 7 Fri May 25, 2012 4:56 pm
No new posts joins four types..can we use all in DB2 kumar119119 DB2 2 Mon Jun 28, 2010 11:18 pm
This topic is locked: you cannot edit posts or make replies. Difference between Corrrelated Subque... Sudeshna Sarkar DB2 2 Sat May 15, 2010 3:18 pm
No new posts For Update Of in Cursor with Joins RAJA-RAJA DB2 1 Mon Apr 12, 2010 6:05 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us