|
View previous topic :: View next topic
|
| Author |
Message |
rajesh_m
New User
Joined: 15 Apr 2008
Posts: 39
Location: India
|
|
|
|
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 |
|
 |
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin
|
|
|
|
| Why don't you run an explain statement and check the results? |
|
| Back to top |
|
|
rajesh_m
New User
Joined: 15 Apr 2008
Posts: 39
Location: India
|
|
|
|
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 |
|
|
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin
|
|
|
|
| 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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| 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 |
|
|
Suresh Ponnusamy
Active User
Joined: 22 Feb 2008
Posts: 107
Location: New York
|
|
|
|
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 |
|
|
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin
|
|
|
|
<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 |
|
|
Suresh Ponnusamy
Active User
Joined: 22 Feb 2008
Posts: 107
Location: New York
|
|
|
|
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 |
|
|
rajesh_m
New User
Joined: 15 Apr 2008
Posts: 39
Location: India
|
|
|
|
Thanks Suresh and steve for your valuable inputs.
Still i am not clear ... plz summarize. |
|
| Back to top |
|
|
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin
|
|
|
|
| Run the explains on the queries and check the actual real world results for yourself. |
|
| Back to top |
|
|
|
|
