Home » RDBMS Server » Performance Tuning » Cost Issue while using row_number()
Cost Issue while using row_number() [message #640887] Thu, 06 August 2015 06:48 Go to next message
AJAYRAM
Messages: 4
Registered: August 2015
Location: HYDERABAD
Junior Member
Hi,

->I need to eliminate duplicate rows from the below query.If i use distinct operator i am getting select cost as 33,475.
->So i went for row_number analytical function it is solving my problem to extent but not totally,it is giving around 20000.
->If i remove this analytical function or distinct operator then cost is 475.

Can you people help me to overcome this problem..


SELECT
ROWNUM ROWNM
,TSGOV.DEPT_OBS DEPT_OBS
,TSGOV.RES_MGR_ID RES_MGR_ID
,TSGOV.RES_MGR_NAME RES_MGR_NAME
,TSGOV.MGR_ID MGR_ID
,TSGOV.RES_ID RES_ID
,TSGOV.RES_NAME RES_NAME
,TSGOV.EMP_TYPE EMP_TYPE
,TSGOV.CONTRACTOR CONTRACTOR
,TSGOV.PRSTART PRSTART
,TSGOV.PRFINISH PRFINISH
,TSGOV.STATUS STATUS
,TSGOV.STATUS P_STATUS
,TSGOV.PRTIMEPERIOD PRTIMEPERIOD
,TSGOV.TPID TPID
,TSGOV.PERSON_TYPE PERSON_TYPE
FROM
(SELECT DEPT_OBS
,RES_MGR_ID
,RES_MGR_NAME
,MGR_ID
,RES_ID
,SRM_ID
,RES_NAME
,EMP_TYPE
,PERSON_TYPE
,CONTRACTOR
,to_date(PRSTART) PRSTART
,to_date(PRFINISH) PRFINISH
,STATUS
,TPID
,TO_CHAR(PRSTART,'dd-Mon-yyyy')||' - '||TO_CHAR(PRFINISH,'dd-Mon-yyyy') PRTIMEPERIOD
,ROW_NUMBER() OVER (PARTITION BY RES_ID ,RES_NAME,to_date(PRSTART),to_date(PRFINISH),STATUS
ORDER BY RES_ID ,RES_NAME,to_date(PRSTART),to_date(PRFINISH),STATUS) AS RN
FROM (SELECT DEPT_OBS
,RES_MGR_ID
,RES_MGR_NAME
,SRM_ID
,MGR_ID
,RES_ID
,RES_NAME
,EMP_TYPE
,PERSON_TYPE
,(CASE WHEN EMP_TYPE='Contractor' THEN CONTRACTOR ELSE '' END) CONTRACTOR
,PRSTART
,PRFINISH
,TPID
,(CASE WHEN NVL(TS.PRSTATUS,9)=9 THEN 'Not Created'
WHEN (NVL(TS.PRSTATUS,9)=0) THEN 'Not Submitted'
WHEN NVL(TS.PRSTATUS,9) in (3,4) AND ( NVL(ATS.CREATED_DATE,TO_DATE('01/01/99','MM/DD/YY')) NOT BETWEEN PRSTART AND PRFINISH) THEN 'Not Attested'
ELSE 'OTHER'
END) STATUS
FROM (SELECT OBS.PATH DEPT_OBS
,MGR.UNIQUE_NAME RES_MGR_ID
,MGR.FULL_NAME RES_MGR_NAME
,MGR.USER_ID MGR_ID
,S.UNIQUE_NAME RES_ID
,S.FULL_NAME RES_NAME
,S.ID SRM_ID
,S.PERSON_TYPE PERSON_TYPE
,(SELECT NAME FROM CMN_LOOKUPS_V WHERE LOOKUP_TYPE='SRM_RESOURCE_TYPE' AND LANGUAGE_CODE='en' AND ID=S.PERSON_TYPE) EMP_TYPE
,(SELECT NAME FROM CMN_LOOKUPS_V WHERE LOOKUP_TYPE='TD_CONTRACTOR_CLASS' AND LANGUAGE_CODE='en' AND LOOKUP_CODE=OCR.TD_CONTRACTOR_CLASS) CONTRACTOR
--,PTP.PRSTART
,TO_DATE(PTP.PRSTART ) PRSTART
,TO_DATE(PTP.PRFINISH )-1 PRFINISH
,PTP.PRID TPID
,S.ID RESID

FROM
PRTIMEPERIOD PTP
,SRM_RESOURCES S
,ODF_CA_RESOURCE OCR
,SRM_RESOURCES MGR
,PRJ_RESOURCES PR
,(SELECT
RES_POA.RECORD_ID RECORD_ID,RES_UNITS.PATH PATH
FROM
(SELECT ID UNIT_ID,TYPE_ID TPID,SYS_CONNECT_BY_PATH(NAME,'/') PATH FROM PRJ_OBS_UNITS
START WITH PARENT_ID IS NULL CONNECT BY PRIOR ID=PARENT_ID) RES_UNITS
JOIN PRJ_OBS_TYPES RES_POT ON RES_UNITS.TPID = RES_POT.ID AND LOWER(RES_POT.UNIQUE_NAME) = 'td_department'
JOIN PRJ_OBS_ASSOCIATIONS RES_POA ON RES_UNITS.UNIT_ID = RES_POA.UNIT_ID AND RES_POA.TABLE_NAME = 'SRM_RESOURCES'
JOIN OBS_UNITS_FLAT_BY_MODE RES_FLAT ON RES_FLAT.LINKED_UNIT_ID = RES_UNITS.UNIT_ID
WHERE
RES_FLAT.UNIT_MODE = 'OBS_UNIT_AND_CHILDREN'
--AND (RES_FLAT.UNIT_ID = :PARAM_OBS)
AND (:param_obs IS NULL OR :param_obs = RES_FLAT.UNIT_ID)
) OBS
WHERE
MGR.USER_ID=S.MANAGER_ID
--AND S.IS_ACTIVE=1
AND PR.PRID=S.ID
AND PR.PRISOPEN=1
AND PRTRACKMODE=2
AND OCR.ID=S.ID
AND OBS.RECORD_ID=S.ID
AND (:param_p_res_status IS NULL OR :param_p_res_status = S.IS_ACTIVE)
) RES
LEFT OUTER JOIN
(SELECT PTS.PRSTATUS,S.ID,PTP.PRID FROM PRTIMEPERIOD PTP
,PRTIMESHEET PTS
,SRM_RESOURCES S
WHERE PTS.PRRESOURCEID=S.ID
AND PTS.PRTIMEPERIODID=PTP.PRID
AND PTS.PRSTATUS !=5
) TS
ON RES.TPID=PRID
AND RES.RESID=TS.ID
LEFT OUTER JOIN
(SELECT ATT.CREATED_DATE,(SELECT USER_ID FROM SRM_RESOURCES WHERE ID= TD_RES_MGR) TD_RES_MGR , MUL.ID
FROM ODF_CA_TD_RM_ATTEST ATT
,(SELECT MUL.PK_ID,S.ID FROM ODF_MULTI_VALUED_LOOKUPS MUL ,SRM_RESOURCES S
WHERE MUL.ATTRIBUTE='td_res_name'
AND MUL.VALUE=S.ID) MUL
WHERE MUL.PK_ID=ATT.ID) ATS
ON RES.RESID=ATS.ID
--AND RES.MGR_ID=ATS.TD_RES_MGR
)
WHERE STATUS !='OTHER'
AND (MGR_ID = MGR_ID)
AND (TPID = TPID)
)TSGOV
WHERE TSGOV.RN=1
AND
1=1;

Thanks,
AJAY.
Re: Cost Issue while using row_number() [message #640890 is a reply to message #640887] Thu, 06 August 2015 08:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68618
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals.

For any performances question, please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

[Updated on: Thu, 06 August 2015 08:28]

Report message to a moderator

Re: Cost Issue while using row_number() [message #640892 is a reply to message #640890] Thu, 06 August 2015 08:49 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.dpriver.com/pp/sqlformat.htm?ref=g_wangz
above used to format code.
Please do so yourself in the future.

SELECT ROWNUM             ROWNM, 
       TSGOV.dept_obs     DEPT_OBS, 
       TSGOV.res_mgr_id   RES_MGR_ID, 
       TSGOV.res_mgr_name RES_MGR_NAME, 
       TSGOV.mgr_id       MGR_ID, 
       TSGOV.res_id       RES_ID, 
       TSGOV.res_name     RES_NAME, 
       TSGOV.emp_type     EMP_TYPE, 
       TSGOV.contractor   CONTRACTOR, 
       TSGOV.prstart      PRSTART, 
       TSGOV.prfinish     PRFINISH, 
       TSGOV.status       STATUS, 
       TSGOV.status       P_STATUS, 
       TSGOV.prtimeperiod PRTIMEPERIOD, 
       TSGOV.tpid         TPID, 
       TSGOV.person_type  PERSON_TYPE 
FROM   (SELECT dept_obs, 
               res_mgr_id, 
               res_mgr_name, 
               mgr_id, 
               res_id, 
               srm_id, 
               res_name, 
               emp_type, 
               person_type, 
               contractor, 
               To_date(prstart) 
                      PRSTART, 
               To_date(prfinish) 
                      PRFINISH, 
               status, 
               tpid, 
               To_char(prstart, 'dd-Mon-yyyy') 
               ||' - ' 
               ||To_char(prfinish, 'dd-Mon-yyyy') 
                      PRTIMEPERIOD, 
               Row_number() 
                 over ( 
                   PARTITION BY res_id, res_name, To_date(prstart), To_date( 
                 prfinish), 
                 status 
                   ORDER BY res_id, res_name, To_date(prstart), To_date(prfinish 
                 ), 
                 status) AS 
               RN 
        FROM   (SELECT dept_obs, 
                       res_mgr_id, 
                       res_mgr_name, 
                       srm_id, 
                       mgr_id, 
                       res_id, 
                       res_name, 
                       emp_type, 
                       person_type, 
                       ( CASE 
                           WHEN emp_type = 'Contractor' THEN contractor 
                           ELSE '' 
                         END ) CONTRACTOR, 
                       prstart, 
                       prfinish, 
                       tpid, 
                       ( CASE 
                           WHEN Nvl(TS.prstatus, 9) = 9 THEN 'Not Created' 
                           WHEN ( Nvl(TS.prstatus, 9) = 0 ) THEN 'Not Submitted' 
                           WHEN Nvl(TS.prstatus, 9) IN ( 3, 4 ) 
                                AND ( Nvl(ATS.created_date, 
                                      To_date('01/01/99', 'MM/DD/YY')) 
                                      NOT 
                                      BETWEEN 
                                          prstart AND prfinish ) THEN 
                           'Not Attested' 
                           ELSE 'OTHER' 
                         END ) STATUS 
                FROM   (SELECT 
               OBS.path 
               DEPT_OBS, 
               MGR.unique_name 
               RES_MGR_ID, 
               MGR.full_name 
               RES_MGR_NAME, 
               MGR.user_id 
               MGR_ID, 
               S.unique_name 
               RES_ID, 
               S.full_name 
               RES_NAME, 
               S.id 
               SRM_ID, 
               S.person_type 
               PERSON_TYPE, 
               (SELECT name 
                FROM   cmn_lookups_v 
                WHERE  lookup_type = 'SRM_RESOURCE_TYPE' 
                       AND language_code = 'en' 
                       AND id = S.person_type) 
               EMP_TYPE, 
               (SELECT name 
                FROM   cmn_lookups_v 
                WHERE  lookup_type = 'TD_CONTRACTOR_CLASS' 
                       AND language_code = 'en' 
                       AND lookup_code = OCR.td_contractor_class) 
               CONTRACTOR 
               --,PTP.PRSTART  
               , 
               To_date(PTP.prstart) 
               PRSTART, 
               To_date(PTP.prfinish) - 1 
               PRFINISH, 
               PTP.prid                                           TPID, 
               S.id                                               RESID 
                        FROM   prtimeperiod PTP, 
                               srm_resources S, 
                               odf_ca_resource OCR, 
                               srm_resources MGR, 
                               prj_resources PR, 
                               (SELECT RES_POA.record_id RECORD_ID, 
                                       RES_UNITS.path    PATH 
                                FROM   (SELECT id 
                                               UNIT_ID, 
                                               type_id 
                                               TPID, 
                                               Sys_connect_by_path(name, '/') 
                                               PATH 
                                        FROM   prj_obs_units 
                                        START WITH parent_id IS NULL 
                                        CONNECT BY PRIOR id = parent_id) 
                                       RES_UNITS 
                                       join prj_obs_types RES_POT 
                                         ON RES_UNITS.tpid = RES_POT.id 
                                            AND Lower(RES_POT.unique_name) = 
                                                'td_department' 
                                       join prj_obs_associations RES_POA 
                                         ON RES_UNITS.unit_id = RES_POA.unit_id 
                                            AND RES_POA.table_name = 
                                                'SRM_RESOURCES' 
                                       join obs_units_flat_by_mode RES_FLAT 
                                         ON RES_FLAT.linked_unit_id = 
                                            RES_UNITS.unit_id 
                                WHERE  RES_FLAT.unit_mode = 
                                       'OBS_UNIT_AND_CHILDREN' 
                                       --AND (RES_FLAT.UNIT_ID = :PARAM_OBS) 
                                       AND ( :param_obs IS NULL 
                                              OR :param_obs = RES_FLAT.unit_id ) 
                               ) OBS 
                        WHERE  MGR.user_id = S.manager_id 
                               --AND S.IS_ACTIVE=1 
                               AND PR.prid = S.id 
                               AND PR.prisopen = 1 
                               AND prtrackmode = 2 
                               AND OCR.id = S.id 
                               AND OBS.record_id = S.id 
                               AND ( :param_p_res_status IS NULL 
                                      OR :param_p_res_status = S.is_active )) 
                       RES 
                       left outer join (SELECT PTS.prstatus, 
                                               S.id, 
                                               PTP.prid 
                                        FROM   prtimeperiod PTP, 
                                               prtimesheet PTS, 
                                               srm_resources S 
                                        WHERE  PTS.prresourceid = S.id 
                                               AND PTS.prtimeperiodid = PTP.prid 
                                               AND PTS.prstatus != 5) TS 
                                    ON RES.tpid = prid 
                                       AND RES.resid = TS.id 
                       left outer join (SELECT ATT.created_date, 
                                               (SELECT user_id 
                                                FROM   srm_resources 
                                                WHERE  id = td_res_mgr) 
                                               TD_RES_MGR, 
                                               MUL.id 
                                        FROM   odf_ca_td_rm_attest ATT, 
                                               (SELECT MUL.pk_id, 
                                                       S.id 
                                                FROM   odf_multi_valued_lookups 
                                                       MUL, 
                                                       srm_resources S 
                                                WHERE 
                                       MUL.attribute = 'td_res_name' 
                                       AND MUL.value = S.id) MUL 
                                        WHERE  MUL.pk_id = ATT.id) ATS 
                                    ON RES.resid = ATS.id 
               --AND RES.MGR_ID=ATS.TD_RES_MGR 
               ) 
        WHERE  status != 'OTHER' 
               AND ( mgr_id = mgr_id ) 
               AND ( tpid = tpid ))TSGOV 
WHERE  TSGOV.rn = 1 
       AND 1 = 1; 
Re: Cost Issue while using row_number() [message #640893 is a reply to message #640892] Thu, 06 August 2015 08:59 Go to previous messageGo to next message
gazzag
Messages: 1116
Registered: November 2010
Location: Bedwas, UK
Senior Member
The last line is meaningless:
AND 1 = 1; 
Re: Cost Issue while using row_number() [message #640894 is a reply to message #640892] Thu, 06 August 2015 09:05 Go to previous messageGo to next message
cookiemonster
Messages: 13915
Registered: September 2008
Location: Rainy Manchester
Senior Member
Are you sure that using this:
               Row_number() 
                 over ( 
                   PARTITION BY res_id, res_name, To_date(prstart), To_date( 
                 prfinish), 
                 status 
                   ORDER BY res_id, res_name, To_date(prstart), To_date(prfinish 
                 ), 
                 status) AS 
               RN 

will give the same results as distinct?

Also this:
To_date(prstart), 

Is never a good idea. I assume prstart is a date and you want the to get rid of the time part, in which case this is the correct way:
trunc(prestart)


Your approach relies on no-one ever changing the sessions nls_date_format.
Re: Cost Issue while using row_number() [message #640932 is a reply to message #640893] Fri, 07 August 2015 00:32 Go to previous messageGo to next message
AJAYRAM
Messages: 4
Registered: August 2015
Location: HYDERABAD
Junior Member
Hi gazzag,

It is NSQL syntax.I think no problem with it.

Thanks,
Ajay.
Re: Cost Issue while using row_number() [message #640933 is a reply to message #640894] Fri, 07 August 2015 01:08 Go to previous messageGo to next message
AJAYRAM
Messages: 4
Registered: August 2015
Location: HYDERABAD
Junior Member
Hi cookiemonster,

Yes it will give the same result set as distinct.In where clause we are filtering rn=1 so that we will get only unique rows.

but cost is not up to the mark,can you suggest me any alternative for that.


Thanks,
Ajay.
Re: Cost Issue while using row_number() [message #640942 is a reply to message #640933] Fri, 07 August 2015 03:11 Go to previous messageGo to next message
cookiemonster
Messages: 13915
Registered: September 2008
Location: Rainy Manchester
Senior Member
row_number over (partition by 5 columns) doesn't usually give the same results as distinct over 15 columns.
Generally the most efficient way to avoid distinct/row_number restrictions is have a where clause that excludes all the rows you aren't actually interested in, but since we know nothing about your tables/data we aren't in a position to tell you how to do that.

Also cost is a bit arbitrary. While queries with lower costs usually run faster it isn't always true. So I'd actually run both against real data and see how long they actually take.
Re: Cost Issue while using row_number() [message #640943 is a reply to message #640942] Fri, 07 August 2015 03:12 Go to previous messageGo to next message
cookiemonster
Messages: 13915
Registered: September 2008
Location: Rainy Manchester
Senior Member
And you really need to stop doing to_date on dates.
Re: Cost Issue while using row_number() [message #640957 is a reply to message #640943] Fri, 07 August 2015 04:37 Go to previous messageGo to next message
AJAYRAM
Messages: 4
Registered: August 2015
Location: HYDERABAD
Junior Member
Hi cookiemonster,

Query is running around 8 seconds and showing data it's fine.

but client is not ok with the select cost it's showing.

so can you suggest me some ways/tips to reduce SELECT COST.That would help me a lot.


Thanks,
Ajay.
Re: Cost Issue while using row_number() [message #640960 is a reply to message #640942] Fri, 07 August 2015 04:46 Go to previous messageGo to next message
cookiemonster
Messages: 13915
Registered: September 2008
Location: Rainy Manchester
Senior Member
cookiemonster wrote on Fri, 07 August 2015 09:11
have a where clause that excludes all the rows you aren't actually interested in, but since we know nothing about your tables/data we aren't in a position to tell you how to do that.


Re: Cost Issue while using row_number() [message #640961 is a reply to message #640960] Fri, 07 August 2015 04:48 Go to previous message
cookiemonster
Messages: 13915
Registered: September 2008
Location: Rainy Manchester
Senior Member
Past that, read the link at the end of Michel's post above.
Previous Topic: A way to identify the top sql other than AWR
Next Topic: Help to tune the query
Goto Forum:
  


Current Time: Tue Mar 19 03:44:51 CDT 2024