[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Complex SQL Query

Posted on 2007-10-18
10
Medium Priority
?
803 Views
Last Modified: 2013-12-19
Guys any Scope for Optimization!!

SELECT  H1.USERNUM,
        H1.HANDLE AS USERNAME,
        H1.FIRSTNAME AS FIRST_NAME,
        H1.MAIDENNAME AS MAIDEN_NAME,
        H1.LASTNAME AS LAST_NAME,
        H1.EMAIL,
        H1.SCHOOL_ID,
        H1.GRADYEAR AS GRAD_YEAR,
       H1.GENDER,
        H1.CITY,
       H1.STATE,
        H1.DATE_CREATED AS DATE_OF_REG,
        H1.LAST_VISIT AS DATE_OF_LAST_VISIT,
        H1.BIRTHDAY AS DATE_OF_BIRTH,
        H1.DATING AS IS_DATING,
        HEP.ACTIVITYWATCH_FREQ,
        USS.IS_SUBSCRIBED,
        USS.WLFY_COUNT AS WSFM_COUNT,
        USS.WLFY_COUNT_THIS_WEEK AS WSFM_COUNT_LAST_WEEK,
        USS.MSG_COUNT,
        HAW.SCHOOL_ID AS WATCHED_SCHOOL_ID,
        HAW.STARTYEAR AS WATCHED_START_YEAR,
        HAW.ENDYEAR AS WATCHED_END_YEAR,
        (SELECT NTP.THEN_PHOTO_ID FROM ALBUM.NOW_THEN_PHOTO NTP WHERE NTP.USERNUM = H1.USERNUM) AS THEN_PHOTO_ID,
        (SELECT NTP.NOW_PHOTO_ID FROM ALBUM.NOW_THEN_PHOTO NTP WHERE NTP.USERNUM = H1.USERNUM) AS NOW_PHOTO_ID,        
       (SELECT COUNT (DISTINCT VPT.VIEWER_USERNUMBER)  
           FROM VIEW_PROFILE_TRACKING VPT, HSA H2
           WHERE VPT.VIEWER_USERNUMBER = H2.USERNUM
             AND H2.DELETED = 0
             AND VPT.VIEW_DATE BETWEEN TRUNC(sysdate)-7 AND ?
             AND VPT.VIEWEE_USERNUMBER <> VPT.VIEWER_USERNUMBER
            AND VPT.VIEWEE_USERNUMBER = H1.USERNUM) AS WVMP_COUNT_LAST_WEEK,        
        (SELECT COUNT (DISTINCT VPT.VIEWER_USERNUMBER)  
           FROM VIEW_PROFILE_TRACKING VPT, HSA H2
           WHERE VPT.VIEWER_USERNUMBER = H2.USERNUM
             AND H2.DELETED = 0
             AND VPT.VIEW_DATE BETWEEN TRUNC(?)-365 AND ?
             AND VPT.VIEWEE_USERNUMBER <> VPT.VIEWER_USERNUMBER
             AND VPT.VIEWEE_USERNUMBER = H1.USERNUM) AS WVMP_COUNT_PAST_YEAR,  
                   
        (SELECT P.PROFILE_UPDATED FROM PROFILE.PROFILE P WHERE P.USERNUM = H1.USERNUM) AS LAST_PROFILE_MODIFY_DATE,
       
        (SELECT COUNT(F.PROFILE_ID) FROM PROFILE.FAVORITES F WHERE F.PROFILE_ID = (SELECT ID FROM PROFILE.PROFILE WHERE USERNUM = H1
.USERNUM)) AS NUM_OF_INTERESTS,
        (SELECT MAX(SEARCH_DATE) FROM PEOPLE_SEARCH_HISTORY PSH WHERE PSH.USERNUM = H1.USERNUM AND PSH.DELETED = 0) AS LAST_PEOPLE_S
EARCH_DATE,
        (SELECT COUNT(SEARCH_ID) FROM PEOPLE_SEARCH_HISTORY PSH WHERE PSH.USERNUM = H1.USERNUM AND PSH.DELETED = 0) AS SAVED_SEARCHE
S_COUNT,
        (SELECT LAST_ENTRY_DATE FROM BLOG.BLOG B WHERE B.USERNUM = H1.USERNUM) AS LAST_BLOG_ENTRY_DATE,
        (SELECT MAX(P.CREATE_DATE) FROM  ALBUM.PHOTO_ALBUM PA, ALBUM.PHOTO P WHERE PA.USERNUM = H1.USERNUM AND P.PHOTO_ALBUM_ID = PA
.PHOTO_ALBUM_ID) AS LAST_PHOTO_UPLOAD_DATE,
        (SELECT COUNT(P.PHOTO_ID) FROM  ALBUM.PHOTO_ALBUM PA, ALBUM.PHOTO P WHERE PA.USERNUM = H1.USERNUM AND P.PHOTO_ALBUM_ID = PA.
PHOTO_ALBUM_ID) AS NUM_OF_PHOTOS,
        (SELECT COUNT(ABE.ID) FROM ADDRESSBOOK.ADDRESSBOOK AB, ADDRESSBOOK.ADDRESSBOOK_ENTRY ABE WHERE AB.USERNUM = H1.USERNUM AND A
BE.ADDRESSBOOK_ID = AB.ID AND NVL(ABE.USERNUM, 0) <> 25657194) AS NUM_OF_CONTACTS,        
        (SELECT COUNT(HSAAW.USERNUM) FROM HSAALUMNIWATCH HSAAW WHERE HSAAW.SCHOOL_ID = H1.SCHOOL_ID AND HSAAW.STARTYEAR <= H1.GRADYE
AR+2 AND HSAAW.ENDYEAR >= H1.GRADYEAR-2 AND HSAAW.DATE_CREATED BETWEEN TRUNC(?)-7 AND ?) AS NEW_STUDENTS_OF_PRIMARY_SCHOOL,
        (SELECT REU.REUNIONDATE from RC_HSAREUNIONS REU WHERE REU.GROUPID = H1.SCHOOL_ID AND REU.GROUPYEAR = H1.GRADYEAR) AS REUNION
_DATE
FROM HSA H1
        INNER JOIN HSA_EMAIL_PROFILE HEP ON HEP.USERNUM = H1.USERNUM
        LEFT OUTER JOIN HSAALUMNIWATCH HAW ON HAW.USERNUM = H1.USERNUM
        LEFT OUTER JOIN USER_SEARCH_STATS USS ON USS.USERNUM = H1.USERNUM        
WHERE H1.USERNUM BETWEEN 0 AND 2000
        AND H1.DELETED = 0    
        AND HEP.HARDBOUNCE = 0
        AND HEP.ACTIVITYWATCH_FREQ > 0
        AND HEP.VALIDATION_STATUS IN (1,2)
0
Comment
Question by:anandmahajan
8 Comments
 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 672 total points
ID: 20106138
Yes.
1. Eliminate the outer joins, if possible
2. Eliminate the "select distinct..." (try to use just "select..." instead)
3. Make sure that the "where" clauses provide indexed values that are quite distinctive.
0
 
LVL 4

Expert Comment

by:jindalankush
ID: 20107312
most  better way to use stored procedure
0
 
LVL 27

Expert Comment

by:MikeToole
ID: 20108427
Don't query the same table twice with the same criteria.
E.g.
<       (SELECT NTP.THEN_PHOTO_ID FROM ALBUM.NOW_THEN_PHOTO NTP WHERE NTP.USERNUM = H1.USERNUM) AS THEN_PHOTO_ID,
        (SELECT NTP.NOW_PHOTO_ID FROM ALBUM.NOW_THEN_PHOTO NTP WHERE NTP.USERNUM = H1.USERNUM) AS NOW_PHOTO_ID,         >

Is better handled as a join:
      Left Join (SELECT THEN_PHOTO_ID,  NOW_PHOTO_ID FROM ALBUM.NOW_THEN_PHOTO NTP) on NTP.USERNUM = H1.USERNUM)


0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 20110302
To jindalankush:

No, I disagree!  Stored procedures do not have a "magic" way to execute inefficient SQL statements efficiently!  If an SQL statement is inefficient, it will be inefficient whether that is run in SQL*Plus, TOAD,
some other program or utility or put into a stored procedure.


To MikeToole:

 I agree that two separate selects from the same table with the same "where" clause should be avoided (and/or combined) if possible.  But, if the only alternative is an outer join, that may not be a performance improvement.
0
 
LVL 27

Assisted Solution

by:MikeToole
MikeToole earned 664 total points
ID: 20111435
The Left Join should perform better than two separate Selects - it halves the work that Oracle has to do. The optimiser should have an easier task to figure out the best method of doing the join than it would with inline Select statements.


0
 
LVL 11

Assisted Solution

by:yuching
yuching earned 664 total points
ID: 20168308
i agree with MikeToole, Left join is better than 2 separate Selects and also left join only join once in the query but if select it in subquery, it will requery the table again
Eg
SELECT NTP.THEN_PHOTO_ID FROM ALBUM.NOW_THEN_PHOTO NTP WHERE NTP.USERNUM = H1.USERNUM

Below is my suggestion, hope it's help

SELECT  H1.USERNUM,   H1.HANDLE AS USERNAME,  H1.FIRSTNAME AS FIRST_NAME,  
        H1.MAIDENNAME AS MAIDEN_NAME,  H1.LASTNAME AS LAST_NAME,
        H1.EMAIL,  H1.SCHOOL_ID, H1.GRADYEAR AS GRAD_YEAR, H1.GENDER,
        H1.CITY, H1.STATE,  H1.DATE_CREATED AS DATE_OF_REG, H1.LAST_VISIT AS DATE_OF_LAST_VISIT,
        H1.BIRTHDAY AS DATE_OF_BIRTH, H1.DATING AS IS_DATING,
        HEP.ACTIVITYWATCH_FREQ,  USS.IS_SUBSCRIBED, USS.WLFY_COUNT AS WSFM_COUNT,
        USS.WLFY_COUNT_THIS_WEEK AS WSFM_COUNT_LAST_WEEK, USS.MSG_COUNT,  
        HAW.SCHOOL_ID AS WATCHED_SCHOOL_ID,  HAW.STARTYEAR AS WATCHED_START_YEAR,
        HAW.ENDYEAR AS WATCHED_END_YEAR,
       
        ---- PART 1 DONT USE SUB QUERY IN 2 SELECT - Combine it into LEFT OUTER JOIN part
       -- (SELECT NTP.THEN_PHOTO_ID FROM ALBUM.NOW_THEN_PHOTO NTP
       --WHERE NTP.USERNUM = H1.USERNUM) AS THEN_PHOTO_ID,
       -- (SELECT NTP.NOW_PHOTO_ID FROM ALBUM.NOW_THEN_PHOTO NTP
       --WHERE NTP.USERNUM = H1.USERNUM) AS NOW_PHOTO_ID,        
       NTP.THEN_PHOTO_ID AS THEN_PHOTO_ID,
       NTP.NOW_PHOTO_ID AS NOW_PHOTO_ID,
       
       -- PART 2 - Combine also..Requery VIEW_PROFILE_TRACKING twice,
       -- notice that current count distinct VIEWER_USERNUMBER always return 1
       -- because it join on VIEWER_USERNUMBER
       -- I think the query here is to get number of views perform by the user
       
      --  (SELECT COUNT (DISTINCT VPT.VIEWER_USERNUMBER)  
      --     FROM VIEW_PROFILE_TRACKING VPT, HSA H2
      --     WHERE VPT.VIEWER_USERNUMBER = H2.USERNUM
       --      AND H2.DELETED = 0   AND VPT.VIEW_DATE BETWEEN TRUNC(sysdate)-7 AND ?
       --      AND VPT.VIEWEE_USERNUMBER <> VPT.VIEWER_USERNUMBER
       --     AND VPT.VIEWEE_USERNUMBER = H1.USERNUM) AS WVMP_COUNT_LAST_WEEK,        
      SUM  (CASE WHEN VPT.VIEW_DATE BETWEEN TRUNC(sysdate)-7 AND ? THEN  1 ELSE 0 END)  
      AS WVMP_COUNT_LAST_WEEK,
           
       -- (SELECT COUNT (DISTINCT VPT.VIEWER_USERNUMBER)  
       --    FROM VIEW_PROFILE_TRACKING VPT, HSA H2
       --    WHERE VPT.VIEWER_USERNUMBER = H2.USERNUM
       --      AND H2.DELETED = 0
       --      AND VPT.VIEW_DATE BETWEEN TRUNC(?)-365 AND ?
       --      AND VPT.VIEWEE_USERNUMBER <> VPT.VIEWER_USERNUMBER
       --      AND VPT.VIEWEE_USERNUMBER = H1.USERNUM) AS WVMP_COUNT_PAST_YEAR,  
       SUM  (CASE WHEN VPT.VIEW_DATE BETWEEN TRUNC(?)-365 AND ? THEN  1 ELSE 0 END)  
       AS WVMP_COUNT_PAST_YEAR,
       
       -- PART 3            
       -- (SELECT P.PROFILE_UPDATED FROM PROFILE.PROFILE P
       -- WHERE P.USERNUM = H1.USERNUM) AS LAST_PROFILE_MODIFY_DATE,
        P.PROFILE_UPDATED AS LAST_PROFILE_MODIFY_DATE,
       
        -- PART 4
       -- (SELECT COUNT(F.PROFILE_ID) FROM PROFILE.FAVORITES F WHERE
       -- F.PROFILE_ID = (SELECT ID FROM PROFILE.PROFILE WHERE USERNUM = H1.USERNUM))
       -- AS NUM_OF_INTERESTS,
        F.NUM_OF_INTERESTS,
       
        --PART 5
       -- (SELECT MAX(SEARCH_DATE) FROM PEOPLE_SEARCH_HISTORY PSH
       -- WHERE PSH.USERNUM = H1.USERNUM
       -- AND PSH.DELETED = 0) AS LAST_PEOPLE_SEARCH_DATE,
        --(SELECT COUNT(SEARCH_ID) FROM PEOPLE_SEARCH_HISTORY PSH
        -- WHERE PSH.USERNUM = H1.USERNUM
        -- AND PSH.DELETED = 0) AS SAVED_SEARCHES_COUNT,
       PSH.LAST_PEOPLE_SEARCH_DATE,
       PSH.SAVED_SEARCHES_COUNT,
       
       -- PART 6
       -- (SELECT LAST_ENTRY_DATE FROM BLOG.BLOG B
       --WHERE B.USERNUM = H1.USERNUM) AS LAST_BLOG_ENTRY_DATE,
        B.LAST_ENTRY_DATE AS LAST_BLOG_ENTRY_DATE,
       
        --PART 7
        --(SELECT MAX(P.CREATE_DATE) FROM  ALBUM.PHOTO_ALBUM PA, ALBUM.PHOTO P
        -- WHERE PA.USERNUM = H1.USERNUM AND P.PHOTO_ALBUM_ID = PA.PHOTO_ALBUM_ID)
        -- AS LAST_PHOTO_UPLOAD_DATE,
        --(SELECT COUNT(P.PHOTO_ID) FROM  ALBUM.PHOTO_ALBUM PA, ALBUM.PHOTO P
        -- WHERE PA.USERNUM = H1.USERNUM AND P.PHOTO_ALBUM_ID = PA.PHOTO_ALBUM_ID)
        --AS NUM_OF_PHOTOS,
        PAP.LAST_PHOTO_UPLOAD_DATE,
        PAP.NUM_OF_PHOTOS,
       
        --PART 8
       --(SELECT COUNT(ABE.ID) FROM ADDRESSBOOK.ADDRESSBOOK AB,
       -- ADDRESSBOOK.ADDRESSBOOK_ENTRY ABE
       -- WHERE AB.USERNUM = H1.USERNUM AND ABE.ADDRESSBOOK_ID = AB.ID
       -- AND NVL(ABE.USERNUM, 0) <> 25657194) AS NUM_OF_CONTACTS,        
       AB.NUM_OF_CONTACTS,
       
       -- PART 9 - dont join again with HSAALUMNIWATCH, it's already in left outer join part
       -- (SELECT COUNT(HSAAW.USERNUM) FROM HSAALUMNIWATCH HSAAW
       -- WHERE HSAAW.SCHOOL_ID = H1.SCHOOL_ID AND HSAAW.STARTYEAR <= H1.GRADYEAR+2
       -- AND HSAAW.ENDYEAR >= H1.GRADYEAR-2
       -- AND HSAAW.DATE_CREATED BETWEEN TRUNC(?)-7 AND ?)
       -- AS NEW_STUDENTS_OF_PRIMARY_SCHOOL,
       SUM(CASE WHEN HAW.SCHOOL_ID = H1.SCHOOL_ID
        AND  HAW.STARTYEAR <= H1.GRADYEAR+2
       AND HAW.ENDYEAR >= H1.GRADYEAR-2
       AND HAW.DATE_CREATED BETWEEN TRUNC(?)-7 AND ? THEN 1 ELSE 0 END)
       AS NEW_STUDENTS_OF_PRIMARY_SCHOOL,
       
       --PART 10
       -- (SELECT REU.REUNIONDATE from RC_HSAREUNIONS REU WHERE REU.GROUPID = H1.SCHOOL_ID
       --AND REU.GROUPYEAR = H1.GRADYEAR) AS REUNION_DATE
        REU.REUNIONDATE AS REUNION_DATE
       
FROM HSA H1
        INNER JOIN HSA_EMAIL_PROFILE HEP ON HEP.USERNUM = H1.USERNUM
        LEFT OUTER JOIN HSAALUMNIWATCH HAW ON HAW.USERNUM = H1.USERNUM
        LEFT OUTER JOIN USER_SEARCH_STATS USS ON USS.USERNUM = H1.USERNUM        
       
        -- PART 1
        LEFT OUTER JOIN ALBUM.NOW_THEN_PHOTO NTP  ON NTP.USERNUM = H1.USERNUM
       
        -- PART 2
        LEFT OUTER JOIN  VIEW_PROFILE_TRACKING VPT ON VPT.VIEWER_USERNUMBER = H2.USERNUM
             AND H2.DELETED = 0 AND VPT.VIEWEE_USERNUMBER <> VPT.VIEWER_USERNUMBER
             AND VPT.VIEWEE_USERNUMBER = H1.USERNUM
       
        --PART 3
        LEFT OUTER JOIN  PROFILE.PROFILE P ON P.USERNUM = H1.USERNUM
       
        -- PART 4
        LEFT OUTER JOIN
             (SELECT PROFILE_ID, COUNT(*) AS  NUM_OF_INTERESTS
              FROM PROFILE.FAVORITES
               GROUP BY PROFILE_ID) F  ON F.PROFILE_ID = P.ID
       
       -- PART 5
       LEFT OUTER JOIN
            (SELECT USERNUM, MAX(SEARCH_DATE) AS LAST_PEOPLE_SEARCH_DATE,
            COUNT(SEARCH_ID) AS SAVED_SEARCHES_COUNT
              FROM  PEOPLE_SEARCH_HISTORY
              WHERE DELETED = 0
              GROUP BY  USERNUM) PSH ON  PSH.USERNUM = H1.USERNUM    
     
      -- PART 6
      LEFT OUTER JOIN BLOG.BLOG B ON B.USERNUM = H1.USERNUM
     
      -- PART 7
      LEFT OUTER JOIN
           (SELECT USERNUM,   MAX(P.CREATE_DATE) AS LAST_PHOTO_UPLOAD_DATE,
           COUNT(P.PHOTO_ID) AS NUM_OF_PHOTOS
            FROM  ALBUM.PHOTO_ALBUM PA, ALBUM.PHOTO P  
            WHERE P.PHOTO_ALBUM_ID = PA.PHOTO_ALBUM_ID
            GROUP BY USERNUM) PAP ON PAP.USERNUM = H1.USERNUM
   
    -- PART 8
     LEFT OUTER JOIN
            (SELECT AB.USERNUM, COUNT(ABE.ID) AS NUM_OF_CONTACTS
            FROM ADDRESSBOOK.ADDRESSBOOK AB, ADDRESSBOOK.ADDRESSBOOK_ENTRY ABE
            WHERE ABE.ADDRESSBOOK_ID = AB.ID AND NVL(ABE.USERNUM, 0) <> 25657194
             GROUP BY AB.USERNUM) AB ON  AB.USERNUM = H1.USERNUM    
   
    --PART 10
    LEFT OUTER JOIN    RC_HSAREUNIONS REU ON  REU.GROUPID = H1.SCHOOL_ID AND REU.GROUPYEAR = H1.GRADYEAR
         
WHERE H1.USERNUM BETWEEN 0 AND 2000
        AND H1.DELETED = 0    
        AND HEP.HARDBOUNCE = 0
        AND HEP.ACTIVITYWATCH_FREQ > 0
        AND HEP.VALIDATION_STATUS IN (1,2)
0
 
LVL 1

Author Comment

by:anandmahajan
ID: 20187369
Thanks ...lets me try it out!
0
 
LVL 1

Expert Comment

by:Computer101
ID: 20999846
Forced accept.

Computer101
EE Admin
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

873 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