?
Solved

Query Review

Posted on 2011-03-01
7
Medium Priority
?
199 Views
Last Modified: 2012-05-11
Do w have better query than this

   SELECT a.top_id,   COUNT (b.po_id) published_replies, MAX (b.date_posted) new_rep_date FROM    tb1 a   LEFT OUTER JOIN    tb2 b   ON a.top_id = b.top_id AND b.workflow = 'A'  GROUP BY a.top_id;

Execution Plan
----------------------------------------------------------
Plan hash value: 3224356369

--------------------------------------------------------------------------------
---------------

| Id  | Operation                      | Name         | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------
---------------

|   0 | SELECT STATEMENT               |              |     4 |    96 |     5  (20)| 00:00:01 |

|   1 |  SORT GROUP BY NOSORT          |              |     4 |    96 |     5  (20)| 00:00:01 |

|   2 |   MERGE JOIN OUTER             |              |   105 |  2520 |     5  (20)| 00:00:01 |

|   3 |    INDEX FULL SCAN             | PK_TOP     |   105 |   420 |     1  (0)| 00:00:01 |

|*  4 |    SORT JOIN                   |              |     5 |   100 |     4  (25)| 00:00:01 |

|   5 |     TABLE ACCESS BY INDEX ROWID| tb2        |     5 |   100 |      3 (0)| 00:00:01 |

|*  6 |      INDEX RANGE SCAN          | IDX_WORKFLOW |     5 |       |     1 (0)| 00:00:01 |

--------------------------------------------------------------------------------
---------------
0
Comment
Question by:vadicherla
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 18

Expert Comment

by:sventhan
ID: 35008970
This would be better

 SELECT a.top_id,   COUNT (b.po_id) published_replies, MAX (b.date_posted) new_rep_date FROM    tb1 a   Inner JOIN    tb2 b   ON a.top_id = b.top_id AND b.workflow = 'A'  GROUP BY a.top_id;

or if you really need Left Outer Join then I do not see anything wrong with your SQL.
0
 
LVL 12

Expert Comment

by:Paul_Harris_Fusion
ID: 35009027
it is a pretty simple query so the options for refactoring are pretty limited.

Assuming you need the outer join,  I think your approach is probably pretty optimal.  

If you could live without the outer join,  then you could live without the join at all but I guess this is unlikely.
0
 
LVL 12

Expert Comment

by:Paul_Harris_Fusion
ID: 35009049
if you do not need the outer join then:

Select top_id, count(po_id) as published_replies,  MAX(date_posted) as new_rep_date
from tb1
where b.workflow = 'A
group by top_id
;
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:vadicherla
ID: 35009051
inner join is excluding  the records which  does not have value(0)
0
 
LVL 12

Assisted Solution

by:Paul_Harris_Fusion
Paul_Harris_Fusion earned 400 total points
ID: 35009054
Sorry typo:

Select top_id, count(po_id) as published_replies,  MAX(date_posted) as new_rep_date
from tb2
where b.workflow = 'A
group by top_id
;
0
 
LVL 12

Expert Comment

by:Paul_Harris_Fusion
ID: 35009432
Yes - leaving out the inner join would have exactly the result you describe.    I think yoiu already have the best approach to the query.

If performance is an issue, your only other approach would be to experiment with indexing the columns you are filtering on in the join statement.
0
 
LVL 1

Accepted Solution

by:
sunil_rangineni earned 1600 total points
ID: 35015923
Looking at your query, i feel using the concept of corelated subqueries would give better performnace it this instance.
------------------------------
select a.top_id,
       (select count(po_id) from tb2 where top_id = tb1.top_id and workflow = 'A') as cnt_poid,
       (select MAX (date_posted) from tb2 where top_id = tb1.top_id and workflow = 'A') as max_date
from tb1;

Indexing of top_id in both the tables will fasten the query.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

718 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question