Re: Need help turning OUTER JOIN query into UNION query

God, that query was ugly.

Tom
www.oraclepower.com

"Andy" <enzoweb_at_hotmail.com> wrote in message news:8d4033cd.0201291457.65a7c4c6_at_posting.google.com...
> We have a loooooong running query, and after some investigation have
> determined that since it is using an outer join it is doing full table
> scans. According to some posts here, using UNION instead will cause
> the query to use the indexes:
>
> SELECT
> T4.LOGIN,
> T10.LAST_UPD,
> T7.END_DT,
> T9.NAME,
> T7.START_DT,
> T5.ROW_ID,
> T8.ADDR,
> T10.CREATED_BY,
> T10.X_PR_PAR_EMP_ID,
> T2.NAME,
> T10.CONFLICT_ID,
> T2.OU_ID,
> T10.POSTN_TYPE_CD,
> T10.PR_TERR_ID,
> T10.X_CSIP_TE_TIME,
> T10.CREATED,
> T10.PAR_POSTN_ID,
> T4.FST_NAME,
> T10.ROW_ID,
> T10.X_CHANNEL_CODE,
> T1.END_DT,
> T4.JOB_TITLE,
> T5.NAME,
> T10.BU_ID,
> T10.OU_ID,
> T4.X_REP_ID,
> T10.PR_POSTN_ADDR_ID,
> T10.NAME,
> T2.PR_EMP_ID,
> T8.COUNTRY,
> T10.MODIFICATION_NUM,
> T2.NAME,
> T4.LAST_NAME,
> T10.PR_EMP_ID,
> T4.ROW_ID,
> T3.X_REP_ID,
> T6.NAME,
> T8.CITY,
> T1.START_DT,
> T10.X_GROUP_CODE,
> T10.X_CSIP_USER_UPDATE_TIME,
> T10.LAST_UPD_BY,
> T8.ZIPCODE,
> T1.ROW_ID,
> T8.STATE,
> T8.ROW_ID,
> T10.COMPENSATABLE_FLG,
> T7.ROW_ID,
> T7.MODIFICATION_NUM,
> T7.CREATED_BY,
> T7.LAST_UPD_BY,
> T7.CREATED,
> T7.LAST_UPD,
> T7.CONFLICT_ID,
> T7.EMP_ID,
> T7.POSITION_ID,
> T7.POSITION_ID,
> T7.EMP_ID,
> T7.POSTN_BU_ID,
> T7.POSTN_CMPNSTBL_FLG
> FROM
> SIEBEL.S_EMP_POSTN T1,
> SIEBEL.S_POSTN T2,
> SIEBEL.S_EMPLOYEE T3,
> SIEBEL.S_EMPLOYEE T4,
> SIEBEL.S_ASGN_GRP T5,
> SIEBEL.S_ORG_INT T6,
> SIEBEL.S_EMP_POSTN T7,
> SIEBEL.S_ADDR_ORG T8,
> SIEBEL.S_ORG_INT T9,
> SIEBEL.S_POSTN T10
> WHERE
> T10.BU_ID = T6.ROW_ID (+) AND
> T10.PAR_POSTN_ID = T2.ROW_ID (+) AND
> T10.PR_EMP_ID = T3.ROW_ID (+) AND
> T10.OU_ID = T9.ROW_ID AND
> T10.PR_POSTN_ADDR_ID = T8.ROW_ID (+) AND
> T10.PR_TERR_ID = T5.ROW_ID (+) AND
> T10.PR_EMP_ID = T4.ROW_ID (+) AND
> T10.PR_EMP_ID = T1.EMP_ID (+) AND T10.ROW_ID = T1.POSITION_ID
> (+) AND T7.POSITION_ID = T10.ROW_ID AND
> (T7.EMP_ID = '1+2L+1930')
> ORDER BY
> T10.NAME;
>
> Are there any SQL gurus out there who can re-write this into a
> statement that uses UNION to get it to use indexes?
>
> TIA,
>
> Andy
>
Received on Tue Jan 29 2002 - 19:19:16 CST