Oracle Database Discussions

when executing the below query it takes 5 mins to return 2 million rows .How ro optimize the query


select count(*) from (
SELECT
S_ORG_EXT.ROW_ID,
S_ORG_EXT.CREATED,
'Y',
'Y',
S_ORG_EXT.NAME,
-- NULL,
'Y',
S_ORG_EXT.BASE_CURCY_CD,
S_ORG_EXT.CMPT_FLG,
S_ORG_EXT.LOC,
S_ORG_EXT.OU_TYPE_CD,
S_ORG_EXT.X_OPTY_ACCOUNT_SEGMENT,
S_ORG_EXT.PR_ADDR_ID,
NULL,
S_ORG_EXT.X_SAC_FLG,
NULL,
NULL,
NULL,
S_ORG_EXT_X.ATTRIB_27,
-- NULL,
S_ORG_EXT.PAR_OU_ID,
S_ORG_EXT.PAR_OU_ID,
S_ORG_EXT.CUST_STAT_CD, S_ORG_EXT.DOM_ULT_DUNS_NUM, S_ORG_EXT.DUNS_NUM, S_ORG_EXT.FRGHT_TERMS_CD, S_ORG_EXT.GLBLULT_DUNS_NUM, S_ORG_EXT.HIST_SLS_CURCY_CD, S_ORG_EXT.HIST_SLS_EXCH_DT, S_ORG_EXT.HIST_SLS_VOL,
S_ORG_EXT.MAIN_PH_NUM, S_ORG_EXT.PAR_DUNS_NUM, S_ORG_EXT.PTNTL_SLS_EXCH_DT, S_ORG_EXT.PTNTL_SLS_VOL, S_ORG_EXT.CURR_PRI_LST_ID, S_ORG_EXT.PTNTL_SLS_CURCY_CD, S_ADDR_ORG.ADDR,
S_ADDR_ORG.CITY,
S_ADDR_ORG.COUNTRY,
S_ADDR_ORG.STATE,
S_ADDR_ORG.ZIPCODE,
NULL,
NULL,
NULL,
NULL,
ADDRORG.ADDR ORG_ADDR,
ADDRORG.CITY ORG_CITY,
ADDRORG.COUNTRY ORG_COUNTRY,
ADDRORG.STATE ORG_STATE,
ADDRORG.ZIPCODE ORG_ZIPCODE,
NULL,
S_ORG_EXT.INTEGRATION_ID,
S_ORG_EXT.OU_NUM,
NULL,
NULL,
S_ORG_EXT.DIVISION,
NULL,
S_ORG_EXT.X_ACCOUNT_CHANNEL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
S_ORG_EXT.X_OPTY_FRANCHISE_NAME,
S_ORG_EXT.PROSPECT_FLG,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
S_ORG_EXT.X_OPTY_SEGMENT,
NULL,
S_ORG_EXT.BU_ID,
S_ORG_EXT.PR_POSTN_ID,
S_ORG_EXT.DIVN_CD,
S_ORG_EXT.REGION,
S_ORG_EXT_X.ATTRIB_37,
S_ORG_EXT_X.ATTRIB_38,
S_ORG_EXT.X_OU_SUBTYPE_CD,
S_ORG_EXT.X_CUST_STATUS_DATE
FROM
S_ORG_EXT_X,
S_ORG_EXT ,
S_ADDR_ORG,
S_ADDR_ORG ADDRORG,
S_ETL_I_IMAGE IMG,
S_ETL_SRC_TABLE ISRC
WHERE
S_ORG_EXT.ROW_ID = IMG.ROW_ID
AND
IMG.TABLE_WID = ISRC.ROW_WID
AND
ISRC.TABLE_NAME = 'S_ORG_EXT'
AND S_ORG_EXT_X.ROW_ID(+) = S_ORG_EXT.ROW_ID
AND S_ORG_EXT.PR_ADDR_ID = S_ADDR_ORG.ROW_ID(+)
AND S_ORG_EXT.PR_ADDR_ID = ADDRORG.ROW_ID(+))








Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=66197 Card=1 Byt
es=82)

1 0 SORT (AGGREGATE)
2 1 SORT* (AGGREGATE) :Q301007
3 2 MERGE JOIN* (OUTER) (Cost=66197 Card=1666645 Bytes=136 :Q301007
664890)

4 3 MERGE JOIN* (OUTER) (Cost=51559 Card=1666645 Bytes=1 :Q301007
19998440)

5 4 SORT* (JOIN) (Cost=46841 Card=1666645 Bytes=103331 :Q301007
990)

6 5 MERGE JOIN* (OUTER) (Cost=38131 Card=1666645 Byt :Q301004
es=103331990)

7 6 SORT* (JOIN) (Cost=35084 Card=1666645 Bytes=86 :Q301004
665540)

8 7 MERGE JOIN* (Cost=27589 Card=1666645 Bytes=8 :Q301002
6665540)

9 8 SORT* (JOIN) (Cost=5427 Card=1666645 Bytes :Q301002
=48332705)

10 9 NESTED LOOPS* (Cost=797 Card=1666645 Byt :Q301000
es=48332705)

11 10 TABLE ACCESS (BY INDEX ROWID) OF 'S_ET
L_SRC_TABLE' (Cost=1 Card=1 Bytes=15)

12 11 INDEX (UNIQUE SCAN) OF 'S_ETL_SRC_TA
BLE_P1' (UNIQUE)

13 10 INDEX (FAST FULL SCAN) OF 'S_ETL_I_IMA
GE_U1' (UNIQUE) (Cost=796 Card=3333289 Bytes=46666046)

14 8 SORT* (JOIN) (Cost=22162 Card=5466787 Byte :Q301002
s=125736101)

15 14 INDEX* (FAST FULL SCAN) OF 'S_ORG_EXT_MX :Q301001
1' (NON-UNIQUE) (Cost=2146 Card=5466787 Bytes=125736101)

16 6 SORT* (JOIN) (Cost=3047 Card=2187813 Bytes=218 :Q301004
78130)

17 16 INDEX* (FAST FULL SCAN) OF 'S_ORG_EXT_X_MX1' :Q301003
(NON-UNIQUE) (Cost=4 Card=2187813 Bytes=21878130)

18 4 SORT* (JOIN) (Cost=4718 Card=3391400 Bytes=3391400 :Q301007
0)

19 18 INDEX* (FAST FULL SCAN) OF 'S_ADDR_ORG_MX1' (NON :Q301005
-UNIQUE) (Cost=4 Card=3391400 Bytes=33914000)

20 3 SORT* (JOIN) (Cost=4718 Card=3391400 Bytes=33914000) :Q301007
21 20 INDEX* (FAST FULL SCAN) OF 'S_ADDR_ORG_MX1' (NON-U :Q301006
NIQUE) (Cost=4 Card=3391400 Bytes=33914000)



2 PARALLEL_TO_SERIAL SELECT /*+ PIV_SSF */ SYS_OP_MSR(COUNT(*)) F
ROM (SELECT /*+ ORDERED NO_EXPAND US

3 PARALLEL_COMBINED_WITH_PARENT
4 PARALLEL_COMBINED_WITH_PARENT
5 PARALLEL_COMBINED_WITH_PARENT
6 PARALLEL_TO_PARALLEL SELECT /*+ ORDERED NO_EXPAND USE_MERGE(A2) *
/ A1.C2 C0,A1.C0 C1,A2.C0 C2,A1.C1 C

7 PARALLEL_COMBINED_WITH_PARENT
8 PARALLEL_TO_PARALLEL SELECT /*+ ORDERED NO_EXPAND USE_MERGE(A2) *
/ A2.C0 C0,A1.C0 C1,A2.C1 C2,A1.C1 C

9 PARALLEL_COMBINED_WITH_PARENT
10 PARALLEL_FROM_SERIAL
14 PARALLEL_COMBINED_WITH_PARENT
15 PARALLEL_TO_PARALLEL SELECT /*+ Q301001 */ A1."ROW_ID" C0,A1."PR_
ADDR_ID" C1 FROM :I."SIEBEL"."S_ORG_

16 PARALLEL_COMBINED_WITH_PARENT
17 PARALLEL_TO_PARALLEL SELECT /*+ Q301003 */ A1."ROW_ID" C0 FROM :I
."SIEBEL"."S_ORG_EXT_X"."S_ORG_EXT_X

18 PARALLEL_COMBINED_WITH_PARENT
19 PARALLEL_TO_PARALLEL SELECT /*+ Q301005 */ A1."ROW_ID" C0 FROM :I
."SIEBEL"."S_ADDR_ORG"."S_ADDR_ORG_M

20 PARALLEL_COMBINED_WITH_PARENT
21 PARALLEL_TO_PARALLEL SELECT /*+ Q301006 */ A1."ROW_ID" C0 FROM :I
."SIEBEL"."S_ADDR_ORG"."S_ADDR_ORG_M