Home » RDBMS Server » Performance Tuning » what to do about this one !!!
| what to do about this one !!! [message #190343] |
Wed, 30 August 2006 05:31  |
seemit
Messages: 7
Registered: July 2006
|
Junior Member |
|
|
Hey all,
I am given a query which generates 175 K records in 45 minutes. I need to get it down to <10 minutes. I am not supposed to change the query and yet performance gain is required ( but we can forget this "NO CHANGE" right now, so lets change however we want). I can do a 'diff' after spooling the 2 results on Unix, in case the results differ.
I am told to use HINTS...
Here is the scenario..
SELECT 40 columns, sum(some_col) from 6 tables where 6 joins AND some_col='A' AND a condition with IN
group by 40 columns
UNION ALL --same query repeats
SELECT 40 columns, sum(some_col) from 6 tables where 6 joins AND some_col!='A' AND a condition with IN
group by 40 columns -- notice != 'A'
HAVING
sum(some_col)>=some_int value --this sum is same as query before union
Help me with this one !! for one, my team man is trying INDEXing. something else you guys could suggest?
|
|
|
|
|
|
| Re: what to do about this one !!! [message #190353 is a reply to message #190345] |
Wed, 30 August 2006 05:58  |
seemit
Messages: 7
Registered: July 2006
|
Junior Member |
|
|
okay here you go..
QUERY ===>
SELECT
DIM_CLIENT.NM_CMU_4, DIM_CLIENT.NM_CMU_3, DIM_CLIENT.NM_CMU_2, DIM_CLIENT.NM_CMU_1,
DIM_CLIENT.FRANCHISE_L1_1_NM, FACT_EMPLOYEE_ORDER.FEED_LEADING_SOURCE_SYSTEM_ID,
DIM_SOURCE_SYSTEM.NM_LEADING_SOURCE_SYSTEM, FACT_EMPLOYEE_ORDER.ARTEMIS_SOURCE_SYSTEM_ID,
DIM_DATE.NUM_YEAR, DIM_DATE.NM_ABBR_MONTH,
DIM_TEAM.TEAM_LEVEL_6_NM, DIM_TEAM.TEAM_LEVEL_5_NM, DIM_TEAM.TEAM_LEVEL_4_NM, DIM_TEAM.TEAM_LEVEL_3_NM, DIM_TEAM.TEAM_LEVEL_2_NM, DIM_TEAM.TEAM_LEVEL_1_NM,
DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_4_NM, DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_3_NM, DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_2_NM, DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_1_NM,
DIM_TRADING_DESK.NM_OU_ABBR, DIM_TRADING_DESK.ID_OU, DIM_TRADING_DESK.NM_LE, DIM_TRADING_DESK.ID_LE,
DIM_TRADING_DESK.NM_LEVEL_9, DIM_TRADING_DESK.NM_LEVEL_8, DIM_TRADING_DESK.NM_LEVEL_7, DIM_TRADING_DESK.NM_LEVEL_6, DIM_TRADING_DESK.NM_LEVEL_5,
DIM_TRADING_DESK.SUMM_PT_LEVEL_9, DIM_TRADING_DESK.SUMM_PT_LEVEL_8, DIM_TRADING_DESK.SUMM_PT_LEVEL_7, DIM_TRADING_DESK.SUMM_PT_LEVEL_6, DIM_TRADING_DESK.SUMM_PT_LEVEL_5,
DIM_DATE.DT_DATE, DIM_EMPLOYEE_ROLE.NM_EMPLOYEE_ROLE, DIM_TEAM.TEAM_LEVEL_0_NM, DIM_CLIENT.SECTOR_L1_1_NM,
sum(FACT_EMPLOYEE_ORDER.AM_NET_CVA_DELTA) NET_CVA, sum(FACT_EMPLOYEE_ORDER.AM_NET_CVA_ASGN_DELTA) A_NET_CVA,
sum(FACT_EMPLOYEE_ORDER.AM_NET_CVA_X_ROLE_DELTA) X_NET_CVA, sum(FACT_EMPLOYEE_ORDER.AM_NET_CVA_ROLE_DELTA) F_NET_CVA,
DIM_CLIENT.SPN_ID
FROM
FACT_EMPLOYEE_ORDER,
DIM_CLIENT,
DIM_TEAM,
DIM_EMPLOYEE_ROLE,
DIM_PRODUCT_HIERARCHY,
DIM_SOURCE_SYSTEM,
DIM_DATE,
DIM_FINANCIAL_HIERARCHY DIM_TRADING_DESK
WHERE
( FACT_EMPLOYEE_ORDER.ID_SOURCE_SYSTEM=DIM_SOURCE_SYSTEM.ID_SOURCE_SYSTEM )
AND ( FACT_EMPLOYEE_ORDER.ID_CLIENT=DIM_CLIENT.ID_CLIENT )
AND ( FACT_EMPLOYEE_ORDER.ID_EMPLOYEE_ROLE=DIM_EMPLOYEE_ROLE.ID_EMPLOYEE_ROLE )
AND ( FACT_EMPLOYEE_ORDER.ID_LE_OU_TRADING_DESK=DIM_TRADING_DESK.ID_LE_OU )
AND ( DIM_PRODUCT_HIERARCHY.ID_INSTRUMENT_PRODUCT=FACT_EMPLOYEE_ORDER.ID_INSTRUMENT_PRODUCT )
AND ( DIM_TEAM.ID_TEAM=FACT_EMPLOYEE_ORDER.ID_TEAM )
AND ( FACT_EMPLOYEE_ORDER.ID_REPORTING_DATE=DIM_DATE.ID_DATE )
AND (IND_TO_BE_DELETED = 'N' AND IND_IS_REPORTABLE = 'Y' )
AND (
DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_2_NM IN ('EQ OTC DERIV', 'OTHER CR EXO/HYB', 'SYNTH ETIC CDO - INV', 'FX SPOT', 'SOFTS',
'STR ALTERN INVEST', 'PRECIOUS METALS', 'FX SWAP', 'GAS', 'IRD SWAPS', 'IRD EXO/HYB', 'FX EXOTIC OPTIONS', 'POWER',
'TRANCHE', 'FX HYBRID OPTIONS', 'EQ SECURIT DERIV', 'EMISSIONS', 'FX OPTIONS', 'FX FORWARD', 'OIL', 'IRD OPTIONS',
'FTD', 'BASE METALS', 'SYNTHETIC CDO - MGR', 'COAL')
AND ( DIM_DATE.YEAR_MONTH BETWEEN 200601 AND 200607)
AND ( DIM_EMPLOYEE_ROLE.NM_EMPLOYEE_ROLE = 'SALES' )
AND DIM_CLIENT.SECTOR_L1_3_NM = 'CORPORATES'
)
GROUP BY
DIM_CLIENT.NM_CMU_4, DIM_CLIENT.NM_CMU_3, DIM_CLIENT.NM_CMU_2, DIM_CLIENT.NM_CMU_1,
DIM_CLIENT.FRANCHISE_L1_1_NM, FACT_EMPLOYEE_ORDER.FEED_LEADING_SOURCE_SYSTEM_ID,
DIM_SOURCE_SYSTEM.NM_LEADING_SOURCE_SYSTEM, FACT_EMPLOYEE_ORDER.ARTEMIS_SOURCE_SYSTEM_ID,
DIM_DATE.NUM_YEAR, DIM_DATE.NM_ABBR_MONTH,
DIM_TEAM.TEAM_LEVEL_6_NM, DIM_TEAM.TEAM_LEVEL_5_NM, DIM_TEAM.TEAM_LEVEL_4_NM, DIM_TEAM.TEAM_LEVEL_3_NM, DIM_TEAM.TEAM_LEVEL_2_NM, DIM_TEAM.TEAM_LEVEL_1_NM,
DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_4_NM, DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_3_NM, DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_2_NM, DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_1_NM,
DIM_TRADING_DESK.NM_OU_ABBR, DIM_TRADING_DESK.ID_OU, DIM_TRADING_DESK.NM_LE, DIM_TRADING_DESK.ID_LE,
DIM_TRADING_DESK.NM_LEVEL_9, DIM_TRADING_DESK.NM_LEVEL_8, DIM_TRADING_DESK.NM_LEVEL_7, DIM_TRADING_DESK.NM_LEVEL_6, DIM_TRADING_DESK.NM_LEVEL_5,
DIM_TRADING_DESK.SUMM_PT_LEVEL_9, DIM_TRADING_DESK.SUMM_PT_LEVEL_8, DIM_TRADING_DESK.SUMM_PT_LEVEL_7, DIM_TRADING_DESK.SUMM_PT_LEVEL_6, DIM_TRADING_DESK.SUMM_PT_LEVEL_5,
DIM_DATE.DT_DATE, DIM_EMPLOYEE_ROLE.NM_EMPLOYEE_ROLE, DIM_TEAM.TEAM_LEVEL_0_NM, DIM_CLIENT.SECTOR_L1_1_NM,
DIM_CLIENT.SPN_ID
UNION ALL
SELECT
DIM_CLIENT.NM_CMU_4, DIM_CLIENT.NM_CMU_3, DIM_CLIENT.NM_CMU_2, DIM_CLIENT.NM_CMU_1,
DIM_CLIENT.FRANCHISE_L1_1_NM, FACT_EMPLOYEE_ORDER.FEED_LEADING_SOURCE_SYSTEM_ID,
DIM_SOURCE_SYSTEM.NM_LEADING_SOURCE_SYSTEM, FACT_EMPLOYEE_ORDER.ARTEMIS_SOURCE_SYSTEM_ID,
DIM_DATE.NUM_YEAR, DIM_DATE.NM_ABBR_MONTH,
DIM_TEAM.TEAM_LEVEL_6_NM, DIM_TEAM.TEAM_LEVEL_5_NM, DIM_TEAM.TEAM_LEVEL_4_NM, DIM_TEAM.TEAM_LEVEL_3_NM, DIM_TEAM.TEAM_LEVEL_2_NM, DIM_TEAM.TEAM_LEVEL_1_NM,
DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_4_NM, DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_3_NM, DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_2_NM, DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_1_NM,
DIM_TRADING_DESK.NM_OU_ABBR, DIM_TRADING_DESK.ID_OU, DIM_TRADING_DESK.NM_LE, DIM_TRADING_DESK.ID_LE,
DIM_TRADING_DESK.NM_LEVEL_9, DIM_TRADING_DESK.NM_LEVEL_8, DIM_TRADING_DESK.NM_LEVEL_7, DIM_TRADING_DESK.NM_LEVEL_6, DIM_TRADING_DESK.NM_LEVEL_5,
DIM_TRADING_DESK.SUMM_PT_LEVEL_9, DIM_TRADING_DESK.SUMM_PT_LEVEL_8, DIM_TRADING_DESK.SUMM_PT_LEVEL_7, DIM_TRADING_DESK.SUMM_PT_LEVEL_6, DIM_TRADING_DESK.SUMM_PT_LEVEL_5,
DIM_DATE.DT_DATE, DIM_EMPLOYEE_ROLE.NM_EMPLOYEE_ROLE, DIM_TEAM.TEAM_LEVEL_0_NM, DIM_CLIENT.SECTOR_L1_1_NM,
sum(FACT_EMPLOYEE_ORDER.AM_NET_CVA_DELTA), sum(FACT_EMPLOYEE_ORDER.AM_NET_CVA_ASGN_DELTA),
sum(FACT_EMPLOYEE_ORDER.AM_NET_CVA_X_ROLE_DELTA), sum(FACT_EMPLOYEE_ORDER.AM_NET_CVA_ROLE_DELTA),
DIM_CLIENT.SPN_ID
FROM
FACT_EMPLOYEE_ORDER,
DIM_CLIENT,
DIM_TEAM,
DIM_EMPLOYEE_ROLE,
DIM_PRODUCT_HIERARCHY,
DIM_SOURCE_SYSTEM,
DIM_DATE,
DIM_FINANCIAL_HIERARCHY DIM_TRADING_DESK
WHERE
( FACT_EMPLOYEE_ORDER.ID_SOURCE_SYSTEM=DIM_SOURCE_SYSTEM.ID_SOURCE_SYSTEM )
AND ( FACT_EMPLOYEE_ORDER.ID_CLIENT=DIM_CLIENT.ID_CLIENT )
AND ( FACT_EMPLOYEE_ORDER.ID_EMPLOYEE_ROLE=DIM_EMPLOYEE_ROLE.ID_EMPLOYEE_ROLE )
AND ( FACT_EMPLOYEE_ORDER.ID_LE_OU_TRADING_DESK=DIM_TRADING_DESK.ID_LE_OU )
AND ( DIM_PRODUCT_HIERARCHY.ID_INSTRUMENT_PRODUCT=FACT_EMPLOYEE_ORDER.ID_INSTRUMENT_PRODUCT )
AND ( DIM_TEAM.ID_TEAM=FACT_EMPLOYEE_ORDER.ID_TEAM )
AND ( FACT_EMPLOYEE_ORDER.ID_REPORTING_DATE=DIM_DATE.ID_DATE )
AND (IND_TO_BE_DELETED = 'N' AND IND_IS_REPORTABLE = 'Y')
AND (
DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_2_NM IN ('EQ OTC DERIV', 'OTHER CR EXO/HYB', 'SYNTH ETIC CDO - INV', 'FX SPOT', 'SOFTS',
'STR ALTERN INVEST', 'PRECIOUS METALS', 'FX SWAP', 'GAS', 'IRD SWAPS', 'IRD EXO/HYB', 'FX EXOTIC OPTIONS', 'POWER',
'TRANCHE', 'FX HYBRID OPTIONS', 'EQ SECURIT DERIV', 'EMISSIONS', 'FX OPTIONS', 'FX FORWARD', 'OIL', 'IRD OPTIONS',
'FTD', 'BASE METALS', 'SYNTHETIC CDO - MGR', 'COAL')
AND ( DIM_DATE.YEAR_MONTH BETWEEN 200601 AND 200607)
AND ( DIM_EMPLOYEE_ROLE.NM_EMPLOYEE_ROLE = 'SALES' )
AND DIM_CLIENT.SECTOR_L1_3_NM != 'CORPORATES'
)
GROUP BY
DIM_CLIENT.NM_CMU_4, DIM_CLIENT.NM_CMU_3, DIM_CLIENT.NM_CMU_2, DIM_CLIENT.NM_CMU_1,
DIM_CLIENT.FRANCHISE_L1_1_NM, FACT_EMPLOYEE_ORDER.FEED_LEADING_SOURCE_SYSTEM_ID,
DIM_SOURCE_SYSTEM.NM_LEADING_SOURCE_SYSTEM, FACT_EMPLOYEE_ORDER.ARTEMIS_SOURCE_SYSTEM_ID,
DIM_DATE.NUM_YEAR, DIM_DATE.NM_ABBR_MONTH,
DIM_TEAM.TEAM_LEVEL_6_NM, DIM_TEAM.TEAM_LEVEL_5_NM, DIM_TEAM.TEAM_LEVEL_4_NM, DIM_TEAM.TEAM_LEVEL_3_NM, DIM_TEAM.TEAM_LEVEL_2_NM, DIM_TEAM.TEAM_LEVEL_1_NM,
DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_4_NM, DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_3_NM, DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_2_NM, DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_1_NM,
DIM_TRADING_DESK.NM_OU_ABBR, DIM_TRADING_DESK.ID_OU, DIM_TRADING_DESK.NM_LE, DIM_TRADING_DESK.ID_LE,
DIM_TRADING_DESK.NM_LEVEL_9, DIM_TRADING_DESK.NM_LEVEL_8, DIM_TRADING_DESK.NM_LEVEL_7, DIM_TRADING_DESK.NM_LEVEL_6, DIM_TRADING_DESK.NM_LEVEL_5,
DIM_TRADING_DESK.SUMM_PT_LEVEL_9, DIM_TRADING_DESK.SUMM_PT_LEVEL_8, DIM_TRADING_DESK.SUMM_PT_LEVEL_7, DIM_TRADING_DESK.SUMM_PT_LEVEL_6, DIM_TRADING_DESK.SUMM_PT_LEVEL_5,
DIM_DATE.DT_DATE, DIM_EMPLOYEE_ROLE.NM_EMPLOYEE_ROLE, DIM_TEAM.TEAM_LEVEL_0_NM, DIM_CLIENT.SECTOR_L1_1_NM,
DIM_CLIENT.SPN_ID
HAVING
sum(FACT_EMPLOYEE_ORDER.AM_NET_CVA_DELTA) >= 250000;
EXPLAIN PLAN ==> has 22 FULL TABLE ACCESSES copy it to textpad <if you have it >
Plan hash value: 2635515769
------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------
| 0 | SELECT STATEMENT | | 1830 | 892K| 48942 (65)| 00:11:26 | | | | | |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 | 48785 | 4383K| 2213 (1)| 00:00:31 | | | Q1,01 | P->S | QC (RAND) |
| 4 | LOAD AS SELECT | | | | | | | | Q1,01 | PCWP | |
| 5 | PX RECEIVE | | 48785 | 4383K| 2213 (1)| 00:00:31 | | | Q1,01 | PCWP | |
| 6 | PX SEND ROUND-ROBIN | :TQ10000 | 48785 | 4383K| 2213 (1)| 00:00:31 | | | Q1,00 | P->P | RND-ROBIN |
| 7 | PX BLOCK ITERATOR | | 48785 | 4383K| 2213 (1)| 00:00:31 | | | Q1,00 | PCWC | |
|* 8 | TABLE ACCESS FULL | DIM_CLIENT | 48785 | 4383K| 2213 (1)| 00:00:31 | | | Q1,00 | PCWP | |
| 9 | PX COORDINATOR | | | | | | | | | | |
| 10 | PX SEND QC (RANDOM) | :TQ20018 | | | | | | | Q2,18 | P->S | QC (RAND) |
| 11 | BUFFER SORT | | 1830 | 892K| 48942 (65)| 00:11:26 | | | Q2,18 | PCWP | |
| 12 | UNION-ALL | | | | | | | | Q2,18 | PCWP | |
| 13 | SORT GROUP BY | | 298 | 136K| 15289 (2)| 00:03:35 | | | Q2,18 | PCWP | |
| 14 | PX RECEIVE | | 298 | 136K| 15288 (2)| 00:03:35 | | | Q2,18 | PCWP | |
| 15 | PX SEND HASH | :TQ20016 | 298 | 136K| 15288 (2)| 00:03:35 | | | Q2,16 | P->P | HASH |
|* 16 | HASH JOIN | | 298 | 136K| 15288 (2)| 00:03:35 | | | Q2,16 | PCWP | |
| 17 | PX RECEIVE | | 298 | 106K| 15009 (2)| 00:03:31 | | | Q2,16 | PCWP | |
| 18 | PX SEND BROADCAST | :TQ20014 | 298 | 106K| 15009 (2)| 00:03:31 | | | Q2,14 | P->P | BROADCAST |
|* 19 | HASH JOIN | | 298 | 106K| 15009 (2)| 00:03:31 | | | Q2,14 | PCWP | |
| 20 | PX RECEIVE | | 298 | 84632 | 14984 (2)| 00:03:30 | | | Q2,14 | PCWP | |
| 21 | PX SEND BROADCAST | :TQ20012 | 298 | 84632 | 14984 (2)| 00:03:30 | | | Q2,12 | P->P | BROADCAST |
|* 22 | HASH JOIN | | 298 | 84632 | 14984 (2)| 00:03:30 | | | Q2,12 | PCWP | |
| 23 | PX RECEIVE | | 298 | 51554 | 14956 (2)| 00:03:30 | | | Q2,12 | PCWP | |
| 24 | PX SEND BROADCAST | :TQ20010 | 298 | 51554 | 14956 (2)| 00:03:30 | | | Q2,10 | P->P | BROADCAST |
|* 25 | HASH JOIN | | 298 | 51554 | 14956 (2)| 00:03:30 | | | Q2,10 | PCWP | |
| 26 | PX RECEIVE | | 298 | 48276 | 14950 (2)| 00:03:30 | | | Q2,10 | PCWP | |
| 27 | PX SEND BROADCAST | :TQ20007 | 298 | 48276 | 14950 (2)| 00:03:30 | | | Q2,07 | P->P | BROADCAST |
|* 28 | HASH JOIN BUFFERED | | 298 | 48276 | 14950 (2)| 00:03:30 | | | Q2,07 | PCWP | |
| 29 | PX JOIN FILTER CREATE | :BF0000 | 298 | 34270 | 14944 (2)| 00:03:30 | | | Q2,07 | PCWP | |
| 30 | PX RECEIVE | | 298 | 34270 | 14944 (2)| 00:03:30 | | | Q2,07 | PCWP | |
| 31 | PX SEND HASH | :TQ20002 | 298 | 34270 | 14944 (2)| 00:03:30 | | | Q2,02 | P->P | HASH |
|* 32 | HASH JOIN | | 298 | 34270 | 14944 (2)| 00:03:30 | | | Q2,02 | PCWP | |
| 33 | PX RECEIVE | | 1 | 11 | 6 (0)| 00:00:01 | | | Q2,02 | PCWP | |
| 34 | PX SEND BROADCAST | :TQ20000 | 1 | 11 | 6 (0)| 00:00:01 | | | Q2,00 | P->P | BROADCAST |
| 35 | PX BLOCK ITERATOR | | 1 | 11 | 6 (0)| 00:00:01 | | | Q2,00 | PCWC | |
|* 36 | TABLE ACCESS FULL | DIM_EMPLOYEE_ROLE | 1 | 11 | 6 (0)| 00:00:01 | | | Q2,00 | PCWP | |
|* 37 | HASH JOIN | | 895 | 93080 | 14938 (2)| 00:03:30 | | | Q2,02 | PCWP | |
| 38 | PX RECEIVE | | 120 | 3120 | 11 (0)| 00:00:01 | | | Q2,02 | PCWP | |
| 39 | PX SEND BROADCAST | :TQ20001 | 120 | 3120 | 11 (0)| 00:00:01 | | | Q2,01 | P->P | BROADCAST |
| 40 | PX BLOCK ITERATOR | | 120 | 3120 | 11 (0)| 00:00:01 | | | Q2,01 | PCWC | |
|* 41 | TABLE ACCESS FULL | DIM_DATE | 120 | 3120 | 11 (0)| 00:00:01 | | | Q2,01 | PCWP | |
| 42 | PX PARTITION RANGE ALL | | 6179 | 470K| 14926 (2)| 00:03:29 | 1 | 26 | Q2,02 | PCWC | |
|* 43 | TABLE ACCESS BY LOCAL INDEX ROWID| FACT_EMPLOYEE_ORDER | 6179 | 470K| 14926 (2)| 00:03:29 | 1 | 26 | Q2,02 | PCWP | |
| 44 | BITMAP CONVERSION TO ROWIDS | | | | | | | | Q2,02 | PCWP | |
| 45 | BITMAP AND | | | | | | | | Q2,02 | PCWP | |
| 46 | BITMAP MERGE | | | | | | | | Q2,02 | PCWP | |
| 47 | BITMAP KEY ITERATION | | | | | | | | Q2,02 | PCWP | |
| 48 | BUFFER SORT | | | | | | | | Q2,02 | PCWP | |
|* 49 | TABLE ACCESS FULL | DIM_EMPLOYEE_ROLE | 1 | 11 | 6 (0)| 00:00:01 | | | Q2,02 | PCWP | |
|* 50 | BITMAP INDEX RANGE SCAN | XIF35FACT_EMPLOYEE_ORDER | | | | | 1 | 26 | Q2,02 | PCWP | |
| 51 | BITMAP MERGE | | | | | | | | Q2,02 | PCWP | |
| 52 | BITMAP KEY ITERATION | | | | | | | | Q2,02 | PCWP | |
| 53 | BUFFER SORT | | | | | | | | Q2,02 | PCWP | |
|* 54 | TABLE ACCESS FULL | DIM_PRODUCT_HIERARCHY | 802 | 37694 | 6 (0)| 00:00:01 | | | Q2,02 | PCWP | |
|* 55 | BITMAP INDEX RANGE SCAN | XIF26FACT_EMPLOYEE_ORDER | | | | | 1 | 26 | Q2,02 | PCWP | |
| 56 | BITMAP MERGE | | | | | | | | Q2,02 | PCWP | |
| 57 | BITMAP KEY ITERATION | | | | | | | | Q2,02 | PCWP | |
| 58 | BUFFER SORT | | | | | | | | Q2,02 | PCWP | |
|* 59 | TABLE ACCESS FULL | DIM_DATE | 120 | 3120 | 11 (0)| 00:00:01 | | | Q2,02 | PCWP | |
| 60 | BITMAP CONVERSION FROM ROWID| | | | | | | | Q2,02 | PCWP | |
|* 61 | INDEX RANGE SCAN | XIF13FACT_EMPLOYEE_ORDER | | | 265 (1)| 00:00:04 | 1 | 26 | Q2,02 | PCWP | |
| 62 | BITMAP MERGE | | | | | | | | Q2,02 | PCWP | |
| 63 | BITMAP KEY ITERATION | | | | | | | | Q2,02 | PCWP | |
| 64 | BUFFER SORT | | | | | | | | Q2,02 | PCWP | |
| 65 | TABLE ACCESS FULL | SYS_TEMP_0FD9D663D_4091BC55 | 1 | 13 | 2 (0)| 00:00:01 | | | Q2,02 | PCWP | |
|* 66 | BITMAP INDEX RANGE SCAN | XIF34FACT_EMPLOYEE_ORDER | | | | | 1 | 26 | Q2,02 | PCWP | |
| 67 | PX RECEIVE | | 802 | 37694 | 6 (0)| 00:00:01 | | | Q2,07 | PCWP | |
| 68 | PX SEND HASH | :TQ20003 | 802 | 37694 | 6 (0)| 00:00:01 | | | Q2,03 | P->P | HASH |
| 69 | PX JOIN FILTER USE | :BF0000 | 802 | 37694 | 6 (0)| 00:00:01 | | | Q2,03 | PCWP | |
| 70 | PX BLOCK ITERATOR | | 802 | 37694 | 6 (0)| 00:00:01 | | | Q2,03 | PCWC | |
|* 71 | TABLE ACCESS FULL | DIM_PRODUCT_HIERARCHY | 802 | 37694 | 6 (0)| 00:00:01 | | | Q2,03 | PCWP | |
| 72 | PX BLOCK ITERATOR | | 13200 | 141K| 6 (0)| 00:00:01 | | | Q2,10 | PCWC | |
| 73 | TABLE ACCESS FULL | DIM_SOURCE_SYSTEM | 13200 | 141K| 6 (0)| 00:00:01 | | | Q2,10 | PCWP | |
| 74 | PX BLOCK ITERATOR | | 20810 | 2255K| 27 (0)| 00:00:01 | | | Q2,12 | PCWC | |
| 75 | TABLE ACCESS FULL | DIM_TEAM | 20810 | 2255K| 27 (0)| 00:00:01 | | | Q2,12 | PCWP | |
| 76 | PX BLOCK ITERATOR | | 48785 | 3906K| 25 (0)| 00:00:01 | | | Q2,14 | PCWC | |
| 77 | TABLE ACCESS FULL | SYS_TEMP_0FD9D663D_4091BC55 | 48785 | 3906K| 25 (0)| 00:00:01 | | | Q2,14 | PCWP | |
| 78 | PX BLOCK ITERATOR | | 130K| 12M| 278 (2)| 00:00:04 | | | Q2,16 | PCWC | |
| 79 | TABLE ACCESS FULL | DIM_FINANCIAL_HIERARCHY | 130K| 12M| 278 (2)| 00:00:04 | | | Q2,16 | PCWP | |
|* 80 | FILTER | | | | | | | | Q2,18 | PCWC | |
| 81 | SORT GROUP BY | | 1532 | 755K| 31440 (1)| 00:07:21 | | | Q2,18 | PCWP | |
| 82 | PX RECEIVE | | 3451 | 1701K| 31439 (1)| 00:07:21 | | | Q2,18 | PCWP | |
| 83 | PX SEND HASH | :TQ20017 | 3451 | 1701K| 31439 (1)| 00:07:21 | | | Q2,17 | P->P | HASH |
|* 84 | HASH JOIN | | 3451 | 1701K| 31439 (1)| 00:07:21 | | | Q2,17 | PCWP | |
| 85 | PX RECEIVE | | 3451 | 1391K| 29225 (1)| 00:06:50 | | | Q2,17 | PCWP | |
| 86 | PX SEND BROADCAST | :TQ20015 | 3451 | 1391K| 29225 (1)| 00:06:50 | | | Q2,15 | P->P | BROADCAST |
|* 87 | HASH JOIN | | 3451 | 1391K| 29225 (1)| 00:06:50 | | | Q2,15 | PCWP | |
| 88 | PX RECEIVE | | 3451 | 1044K| 28946 (1)| 00:06:46 | | | Q2,15 | PCWP | |
| 89 | PX SEND BROADCAST | :TQ20013 | 3451 | 1044K| 28946 (1)| 00:06:46 | | | Q2,13 | P->P | BROADCAST |
|* 90 | HASH JOIN | | 3451 | 1044K| 28946 (1)| 00:06:46 | | | Q2,13 | PCWP | |
| 91 | PX BLOCK ITERATOR | | 20810 | 2255K| 27 (0)| 00:00:01 | | | Q2,13 | PCWC | |
| 92 | TABLE ACCESS FULL | DIM_TEAM | 20810 | 2255K| 27 (0)| 00:00:01 | | | Q2,13 | PCWP | |
| 93 | BUFFER SORT | | | | | | | | Q2,13 | PCWC | |
| 94 | PX RECEIVE | | 3451 | 670K| 28918 (1)| 00:06:45 | | | Q2,13 | PCWP | |
| 95 | PX SEND BROADCAST | :TQ20011 | 3451 | 670K| 28918 (1)| 00:06:45 | | | Q2,11 | P->P | BROADCAST |
|* 96 | HASH JOIN BUFFERED | | 3451 | 670K| 28918 (1)| 00:06:45 | | | Q2,11 | PCWP | |
| 97 | PX JOIN FILTER CREATE | :BF0001 | 13200 | 141K| 6 (0)| 00:00:01 | | | Q2,11 | PCWP | |
| 98 | PX RECEIVE | | 13200 | 141K| 6 (0)| 00:00:01 | | | Q2,11 | PCWP | |
| 99 | PX SEND HASH | :TQ20008 | 13200 | 141K| 6 (0)| 00:00:01 | | | Q2,08 | P->P | HASH |
| 100 | PX BLOCK ITERATOR | | 13200 | 141K| 6 (0)| 00:00:01 | | | Q2,08 | PCWC | |
| 101 | TABLE ACCESS FULL | DIM_SOURCE_SYSTEM | 13200 | 141K| 6 (0)| 00:00:01 | | | Q2,08 | PCWP | |
| 102 | PX RECEIVE | | 3451 | 633K| 28912 (1)| 00:06:45 | | | Q2,11 | PCWP | |
| 103 | PX SEND HASH | :TQ20009 | 3451 | 633K| 28912 (1)| 00:06:45 | | | Q2,09 | P->P | HASH |
| 104 | PX JOIN FILTER USE | :BF0001 | 3451 | 633K| 28912 (1)| 00:06:45 | | | Q2,09 | PCWP | |
|*105 | HASH JOIN | | 3451 | 633K| 28912 (1)| 00:06:45 | | | Q2,09 | PCWP | |
| 106 | PX RECEIVE | | 802 | 37694 | 6 (0)| 00:00:01 | | | Q2,09 | PCWP | |
| 107 | PX SEND BROADCAST | :TQ20004 | 802 | 37694 | 6 (0)| 00:00:01 | | | Q2,04 | P->P | BROADCAST |
| 108 | PX BLOCK ITERATOR | | 802 | 37694 | 6 (0)| 00:00:01 | | | Q2,04 | PCWC | |
|*109 | TABLE ACCESS FULL | DIM_PRODUCT_HIERARCHY | 802 | 37694 | 6 (0)| 00:00:01 | | | Q2,04 | PCWP | |
|*110 | HASH JOIN | | 3452 | 475K| 28906 (1)| 00:06:45 | | | Q2,09 | PCWP | |
| 111 | PX RECEIVE | | 120 | 3120 | 11 (0)| 00:00:01 | | | Q2,09 | PCWP | |
| 112 | PX SEND BROADCAST | :TQ20005 | 120 | 3120 | 11 (0)| 00:00:01 | | | Q2,05 | P->P | BROADCAST |
| 113 | PX BLOCK ITERATOR | | 120 | 3120 | 11 (0)| 00:00:01 | | | Q2,05 | PCWC | |
|*114 | TABLE ACCESS FULL | DIM_DATE | 120 | 3120 | 11 (0)| 00:00:01 | | | Q2,05 | PCWP | |
|*115 | HASH JOIN | | 23843 | 2677K| 28894 (1)| 00:06:45 | | | Q2,09 | PCWP | |
| 116 | PX RECEIVE | | 1 | 11 | 6 (0)| 00:00:01 | | | Q2,09 | PCWP | |
| 117 | PX SEND BROADCAST | :TQ20006 | 1 | 11 | 6 (0)| 00:00:01 | | | Q2,06 | P->P | BROADCAST |
| 118 | PX BLOCK ITERATOR | | 1 | 11 | 6 (0)| 00:00:01 | | | Q2,06 | PCWC | |
|*119 | TABLE ACCESS FULL | DIM_EMPLOYEE_ROLE | 1 | 11 | 6 (0)| 00:00:01 | | | Q2,06 | PCWP | |
| 120 | PX PARTITION RANGE ALL | | 71528 | 7264K| 28888 (1)| 00:06:45 | 1 | 26 | Q2,09 | PCWC | |
|*121 | TABLE ACCESS BY LOCAL INDEX ROWID| FACT_EMPLOYEE_ORDER | 71528 | 7264K| 28888 (1)| 00:06:45 | 1 | 26 | Q2,09 | PCWP | |
| 122 | BITMAP CONVERSION TO ROWIDS | | | | | | | | Q2,09 | PCWP | |
| 123 | BITMAP AND | | | | | | | | Q2,09 | PCWP | |
| 124 | BITMAP MERGE | | | | | | | | Q2,09 | PCWP | |
| 125 | BITMAP KEY ITERATION | | | | | | | | Q2,09 | PCWP | |
| 126 | BUFFER SORT | | | | | | | | Q2,09 | PCWP | |
|*127 | TABLE ACCESS FULL | DIM_EMPLOYEE_ROLE | 1 | 11 | 6 (0)| 00:00:01 | | | Q2,09 | PCWP | |
|*128 | BITMAP INDEX RANGE SCAN | XIF35FACT_EMPLOYEE_ORDER | | | | | 1 | 26 | Q2,09 | PCWP | |
| 129 | BITMAP MERGE | | | | | | | | Q2,09 | PCWP | |
| 130 | BITMAP KEY ITERATION | | | | | | | | Q2,09 | PCWP | |
| 131 | BUFFER SORT | | | | | | | | Q2,09 | PCWP | |
|*132 | TABLE ACCESS FULL | DIM_PRODUCT_HIERARCHY | 802 | 37694 | 6 (0)| 00:00:01 | | | Q2,09 | PCWP | |
|*133 | BITMAP INDEX RANGE SCAN | XIF26FACT_EMPLOYEE_ORDER | | | | | 1 | 26 | Q2,09 | PCWP | |
| 134 | BITMAP MERGE | | | | | | | | Q2,09 | PCWP | |
| 135 | BITMAP KEY ITERATION | | | | | | | | Q2,09 | PCWP | |
| 136 | BUFFER SORT | | | | | | | | Q2,09 | PCWP | |
|*137 | TABLE ACCESS FULL | DIM_DATE | 120 | 3120 | 11 (0)| 00:00:01 | | | Q2,09 | PCWP | |
| 138 | BITMAP CONVERSION FROM ROWID| | | | | | | | Q2,09 | PCWP | |
|*139 | INDEX RANGE SCAN | XIF13FACT_EMPLOYEE_ORDER | 92814 | | 265 (1)| 00:00:04 | 1 | 26 | Q2,09 | PCWP | |
| 140 | PX BLOCK ITERATOR | | 130K| 12M| 278 (2)| 00:00:04 | | | Q2,15 | PCWC | |
| 141 | TABLE ACCESS FULL | DIM_FINANCIAL_HIERARCHY | 130K| 12M| 278 (2)| 00:00:04 | | | Q2,15 | PCWP | |
| 142 | PX BLOCK ITERATOR | | 632K| 55M| 2213 (1)| 00:00:31 | | | Q2,17 | PCWC | |
|*143 | TABLE ACCESS FULL | DIM_CLIENT | 632K| 55M| 2213 (1)| 00:00:31 | | | Q2,17 | PCWP | |
------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------
|
|
|
|
| Re: what to do about this one !!! [message #190370 is a reply to message #190353] |
Wed, 30 August 2006 06:57 |
wagnerch
Messages: 58
Registered: July 2006
|
Member |
|
|
How about reposting it inside [code][/code] tags as suggested. The code tag preserves the formatting, and makes it easy to read. Explain plans should be posted with their original formatting, as it clearly outlines driving tables and join orders.
You are making it difficult for anyone to want to help.
|
|
|
|
| Re: what to do about this one !!! [message #190371 is a reply to message #190370] |
Wed, 30 August 2006 07:06 |
seemit
Messages: 7
Registered: July 2006
|
Junior Member |
|
|
okay my bad
here is the query
SELECT
DIM_CLIENT.NM_CMU_4, DIM_CLIENT.NM_CMU_3, DIM_CLIENT.NM_CMU_2, DIM_CLIENT.NM_CMU_1,
DIM_CLIENT.FRANCHISE_L1_1_NM, FACT_EMPLOYEE_ORDER.FEED_LEADING_SOURCE_SYSTEM_ID,
DIM_SOURCE_SYSTEM.NM_LEADING_SOURCE_SYSTEM, FACT_EMPLOYEE_ORDER.ARTEMIS_SOURCE_SYSTEM_ID,
DIM_DATE.NUM_YEAR, DIM_DATE.NM_ABBR_MONTH,
DIM_TEAM.TEAM_LEVEL_6_NM, DIM_TEAM.TEAM_LEVEL_5_NM, DIM_TEAM.TEAM_LEVEL_4_NM, DIM_TEAM.TEAM_LEVEL_3_NM, DIM_TEAM.TEAM_LEVEL_2_NM, DIM_TEAM.TEAM_LEVEL_1_NM,
DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_4_NM, DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_3_NM, DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_2_NM, DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_1_NM,
DIM_TRADING_DESK.NM_OU_ABBR, DIM_TRADING_DESK.ID_OU, DIM_TRADING_DESK.NM_LE, DIM_TRADING_DESK.ID_LE,
DIM_TRADING_DESK.NM_LEVEL_9, DIM_TRADING_DESK.NM_LEVEL_8, DIM_TRADING_DESK.NM_LEVEL_7, DIM_TRADING_DESK.NM_LEVEL_6, DIM_TRADING_DESK.NM_LEVEL_5,
DIM_TRADING_DESK.SUMM_PT_LEVEL_9, DIM_TRADING_DESK.SUMM_PT_LEVEL_8, DIM_TRADING_DESK.SUMM_PT_LEVEL_7, DIM_TRADING_DESK.SUMM_PT_LEVEL_6, DIM_TRADING_DESK.SUMM_PT_LEVEL_5,
DIM_DATE.DT_DATE, DIM_EMPLOYEE_ROLE.NM_EMPLOYEE_ROLE, DIM_TEAM.TEAM_LEVEL_0_NM, DIM_CLIENT.SECTOR_L1_1_NM,
sum(FACT_EMPLOYEE_ORDER.AM_NET_CVA_DELTA) NET_CVA, sum(FACT_EMPLOYEE_ORDER.AM_NET_CVA_ASGN_DELTA) A_NET_CVA,
sum(FACT_EMPLOYEE_ORDER.AM_NET_CVA_X_ROLE_DELTA) X_NET_CVA, sum(FACT_EMPLOYEE_ORDER.AM_NET_CVA_ROLE_DELTA) F_NET_CVA,
DIM_CLIENT.SPN_ID
FROM
FACT_EMPLOYEE_ORDER,
DIM_CLIENT,
DIM_TEAM,
DIM_EMPLOYEE_ROLE,
DIM_PRODUCT_HIERARCHY,
DIM_SOURCE_SYSTEM,
DIM_DATE,
DIM_FINANCIAL_HIERARCHY DIM_TRADING_DESK
WHERE
( FACT_EMPLOYEE_ORDER.ID_SOURCE_SYSTEM=DIM_SOURCE_SYSTEM.ID_SOURCE_SYSTEM )
AND ( FACT_EMPLOYEE_ORDER.ID_CLIENT=DIM_CLIENT.ID_CLIENT )
AND ( FACT_EMPLOYEE_ORDER.ID_EMPLOYEE_ROLE=DIM_EMPLOYEE_ROLE.ID_EMPLOYEE_ROLE )
AND ( FACT_EMPLOYEE_ORDER.ID_LE_OU_TRADING_DESK=DIM_TRADING_DESK.ID_LE_OU )
AND ( DIM_PRODUCT_HIERARCHY.ID_INSTRUMENT_PRODUCT=FACT_EMPLOYEE_ORDER.ID_INSTRUMENT_PRODUCT )
AND ( DIM_TEAM.ID_TEAM=FACT_EMPLOYEE_ORDER.ID_TEAM )
AND ( FACT_EMPLOYEE_ORDER.ID_REPORTING_DATE=DIM_DATE.ID_DATE )
AND (IND_TO_BE_DELETED = 'N' AND IND_IS_REPORTABLE = 'Y' )
AND (
DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_2_NM IN ('EQ OTC DERIV', 'OTHER CR EXO/HYB', 'SYNTH ETIC CDO - INV', 'FX SPOT', 'SOFTS',
'STR ALTERN INVEST', 'PRECIOUS METALS', 'FX SWAP', 'GAS', 'IRD SWAPS', 'IRD EXO/HYB', 'FX EXOTIC OPTIONS', 'POWER',
'TRANCHE', 'FX HYBRID OPTIONS', 'EQ SECURIT DERIV', 'EMISSIONS', 'FX OPTIONS', 'FX FORWARD', 'OIL', 'IRD OPTIONS',
'FTD', 'BASE METALS', 'SYNTHETIC CDO - MGR', 'COAL')
AND ( DIM_DATE.YEAR_MONTH BETWEEN 200601 AND 200607)
AND ( DIM_EMPLOYEE_ROLE.NM_EMPLOYEE_ROLE = 'SALES' )
AND DIM_CLIENT.SECTOR_L1_3_NM = 'CORPORATES'
)
GROUP BY
DIM_CLIENT.NM_CMU_4, DIM_CLIENT.NM_CMU_3, DIM_CLIENT.NM_CMU_2, DIM_CLIENT.NM_CMU_1,
DIM_CLIENT.FRANCHISE_L1_1_NM, FACT_EMPLOYEE_ORDER.FEED_LEADING_SOURCE_SYSTEM_ID,
DIM_SOURCE_SYSTEM.NM_LEADING_SOURCE_SYSTEM, FACT_EMPLOYEE_ORDER.ARTEMIS_SOURCE_SYSTEM_ID,
DIM_DATE.NUM_YEAR, DIM_DATE.NM_ABBR_MONTH,
DIM_TEAM.TEAM_LEVEL_6_NM, DIM_TEAM.TEAM_LEVEL_5_NM, DIM_TEAM.TEAM_LEVEL_4_NM, DIM_TEAM.TEAM_LEVEL_3_NM, DIM_TEAM.TEAM_LEVEL_2_NM, DIM_TEAM.TEAM_LEVEL_1_NM,
DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_4_NM, DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_3_NM, DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_2_NM, DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_1_NM,
DIM_TRADING_DESK.NM_OU_ABBR, DIM_TRADING_DESK.ID_OU, DIM_TRADING_DESK.NM_LE, DIM_TRADING_DESK.ID_LE,
DIM_TRADING_DESK.NM_LEVEL_9, DIM_TRADING_DESK.NM_LEVEL_8, DIM_TRADING_DESK.NM_LEVEL_7, DIM_TRADING_DESK.NM_LEVEL_6, DIM_TRADING_DESK.NM_LEVEL_5,
DIM_TRADING_DESK.SUMM_PT_LEVEL_9, DIM_TRADING_DESK.SUMM_PT_LEVEL_8, DIM_TRADING_DESK.SUMM_PT_LEVEL_7, DIM_TRADING_DESK.SUMM_PT_LEVEL_6, DIM_TRADING_DESK.SUMM_PT_LEVEL_5,
DIM_DATE.DT_DATE, DIM_EMPLOYEE_ROLE.NM_EMPLOYEE_ROLE, DIM_TEAM.TEAM_LEVEL_0_NM, DIM_CLIENT.SECTOR_L1_1_NM,
DIM_CLIENT.SPN_ID
UNION ALL
SELECT
DIM_CLIENT.NM_CMU_4, DIM_CLIENT.NM_CMU_3, DIM_CLIENT.NM_CMU_2, DIM_CLIENT.NM_CMU_1,
DIM_CLIENT.FRANCHISE_L1_1_NM, FACT_EMPLOYEE_ORDER.FEED_LEADING_SOURCE_SYSTEM_ID,
DIM_SOURCE_SYSTEM.NM_LEADING_SOURCE_SYSTEM, FACT_EMPLOYEE_ORDER.ARTEMIS_SOURCE_SYSTEM_ID,
DIM_DATE.NUM_YEAR, DIM_DATE.NM_ABBR_MONTH,
DIM_TEAM.TEAM_LEVEL_6_NM, DIM_TEAM.TEAM_LEVEL_5_NM, DIM_TEAM.TEAM_LEVEL_4_NM, DIM_TEAM.TEAM_LEVEL_3_NM, DIM_TEAM.TEAM_LEVEL_2_NM, DIM_TEAM.TEAM_LEVEL_1_NM,
DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_4_NM, DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_3_NM, DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_2_NM, DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_1_NM,
DIM_TRADING_DESK.NM_OU_ABBR, DIM_TRADING_DESK.ID_OU, DIM_TRADING_DESK.NM_LE, DIM_TRADING_DESK.ID_LE,
DIM_TRADING_DESK.NM_LEVEL_9, DIM_TRADING_DESK.NM_LEVEL_8, DIM_TRADING_DESK.NM_LEVEL_7, DIM_TRADING_DESK.NM_LEVEL_6, DIM_TRADING_DESK.NM_LEVEL_5,
DIM_TRADING_DESK.SUMM_PT_LEVEL_9, DIM_TRADING_DESK.SUMM_PT_LEVEL_8, DIM_TRADING_DESK.SUMM_PT_LEVEL_7, DIM_TRADING_DESK.SUMM_PT_LEVEL_6, DIM_TRADING_DESK.SUMM_PT_LEVEL_5,
DIM_DATE.DT_DATE, DIM_EMPLOYEE_ROLE.NM_EMPLOYEE_ROLE, DIM_TEAM.TEAM_LEVEL_0_NM, DIM_CLIENT.SECTOR_L1_1_NM,
sum(FACT_EMPLOYEE_ORDER.AM_NET_CVA_DELTA), sum(FACT_EMPLOYEE_ORDER.AM_NET_CVA_ASGN_DELTA),
sum(FACT_EMPLOYEE_ORDER.AM_NET_CVA_X_ROLE_DELTA), sum(FACT_EMPLOYEE_ORDER.AM_NET_CVA_ROLE_DELTA),
DIM_CLIENT.SPN_ID
FROM
FACT_EMPLOYEE_ORDER,
DIM_CLIENT,
DIM_TEAM,
DIM_EMPLOYEE_ROLE,
DIM_PRODUCT_HIERARCHY,
DIM_SOURCE_SYSTEM,
DIM_DATE,
DIM_FINANCIAL_HIERARCHY DIM_TRADING_DESK
WHERE
( FACT_EMPLOYEE_ORDER.ID_SOURCE_SYSTEM=DIM_SOURCE_SYSTEM.ID_SOURCE_SYSTEM )
AND ( FACT_EMPLOYEE_ORDER.ID_CLIENT=DIM_CLIENT.ID_CLIENT )
AND ( FACT_EMPLOYEE_ORDER.ID_EMPLOYEE_ROLE=DIM_EMPLOYEE_ROLE.ID_EMPLOYEE_ROLE )
AND ( FACT_EMPLOYEE_ORDER.ID_LE_OU_TRADING_DESK=DIM_TRADING_DESK.ID_LE_OU )
AND ( DIM_PRODUCT_HIERARCHY.ID_INSTRUMENT_PRODUCT=FACT_EMPLOYEE_ORDER.ID_INSTRUMENT_PRODUCT )
AND ( DIM_TEAM.ID_TEAM=FACT_EMPLOYEE_ORDER.ID_TEAM )
AND ( FACT_EMPLOYEE_ORDER.ID_REPORTING_DATE=DIM_DATE.ID_DATE )
AND (IND_TO_BE_DELETED = 'N' AND IND_IS_REPORTABLE = 'Y')
AND (
DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_2_NM IN ('EQ OTC DERIV', 'OTHER CR EXO/HYB', 'SYNTH ETIC CDO - INV', 'FX SPOT', 'SOFTS',
'STR ALTERN INVEST', 'PRECIOUS METALS', 'FX SWAP', 'GAS', 'IRD SWAPS', 'IRD EXO/HYB', 'FX EXOTIC OPTIONS', 'POWER',
'TRANCHE', 'FX HYBRID OPTIONS', 'EQ SECURIT DERIV', 'EMISSIONS', 'FX OPTIONS', 'FX FORWARD', 'OIL', 'IRD OPTIONS',
'FTD', 'BASE METALS', 'SYNTHETIC CDO - MGR', 'COAL')
AND ( DIM_DATE.YEAR_MONTH BETWEEN 200601 AND 200607)
AND ( DIM_EMPLOYEE_ROLE.NM_EMPLOYEE_ROLE = 'SALES' )
AND DIM_CLIENT.SECTOR_L1_3_NM != 'CORPORATES'
)
GROUP BY
DIM_CLIENT.NM_CMU_4, DIM_CLIENT.NM_CMU_3, DIM_CLIENT.NM_CMU_2, DIM_CLIENT.NM_CMU_1,
DIM_CLIENT.FRANCHISE_L1_1_NM, FACT_EMPLOYEE_ORDER.FEED_LEADING_SOURCE_SYSTEM_ID,
DIM_SOURCE_SYSTEM.NM_LEADING_SOURCE_SYSTEM, FACT_EMPLOYEE_ORDER.ARTEMIS_SOURCE_SYSTEM_ID,
DIM_DATE.NUM_YEAR, DIM_DATE.NM_ABBR_MONTH,
DIM_TEAM.TEAM_LEVEL_6_NM, DIM_TEAM.TEAM_LEVEL_5_NM, DIM_TEAM.TEAM_LEVEL_4_NM, DIM_TEAM.TEAM_LEVEL_3_NM, DIM_TEAM.TEAM_LEVEL_2_NM, DIM_TEAM.TEAM_LEVEL_1_NM,
DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_4_NM, DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_3_NM, DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_2_NM, DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_1_NM,
DIM_TRADING_DESK.NM_OU_ABBR, DIM_TRADING_DESK.ID_OU, DIM_TRADING_DESK.NM_LE, DIM_TRADING_DESK.ID_LE,
DIM_TRADING_DESK.NM_LEVEL_9, DIM_TRADING_DESK.NM_LEVEL_8, DIM_TRADING_DESK.NM_LEVEL_7, DIM_TRADING_DESK.NM_LEVEL_6, DIM_TRADING_DESK.NM_LEVEL_5,
DIM_TRADING_DESK.SUMM_PT_LEVEL_9, DIM_TRADING_DESK.SUMM_PT_LEVEL_8, DIM_TRADING_DESK.SUMM_PT_LEVEL_7, DIM_TRADING_DESK.SUMM_PT_LEVEL_6, DIM_TRADING_DESK.SUMM_PT_LEVEL_5,
DIM_DATE.DT_DATE, DIM_EMPLOYEE_ROLE.NM_EMPLOYEE_ROLE, DIM_TEAM.TEAM_LEVEL_0_NM, DIM_CLIENT.SECTOR_L1_1_NM,
DIM_CLIENT.SPN_ID
HAVING
sum(FACT_EMPLOYEE_ORDER.AM_NET_CVA_DELTA) >= 250000;
and here is the explain plan
----------------------------------------------------------
Plan hash value: 2635515769
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1830 | 892K| 48942 (65)| 00:11:26 | | | | | |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 | 48785 | 4383K| 2213 (1)| 00:00:31 | | | Q1,01 | P->S | QC (RAND) |
| 4 | LOAD AS SELECT | | | | | | | | Q1,01 | PCWP | |
| 5 | PX RECEIVE | | 48785 | 4383K| 2213 (1)| 00:00:31 | | | Q1,01 | PCWP | |
| 6 | PX SEND ROUND-ROBIN | :TQ10000 | 48785 | 4383K| 2213 (1)| 00:00:31 | | | Q1,00 | P->P | RND-ROBIN |
| 7 | PX BLOCK ITERATOR | | 48785 | 4383K| 2213 (1)| 00:00:31 | | | Q1,00 | PCWC | |
|* 8 | TABLE ACCESS FULL | DIM_CLIENT | 48785 | 4383K| 2213 (1)| 00:00:31 | | | Q1,00 | PCWP | |
| 9 | PX COORDINATOR | | | | | | | | | | |
| 10 | PX SEND QC (RANDOM) | :TQ20018 | | | | | | | Q2,18 | P->S | QC (RAND) |
| 11 | BUFFER SORT | | 1830 | 892K| 48942 (65)| 00:11:26 | | | Q2,18 | PCWP | |
| 12 | UNION-ALL | | | | | | | | Q2,18 | PCWP | |
| 13 | SORT GROUP BY | | 298 | 136K| 15289 (2)| 00:03:35 | | | Q2,18 | PCWP | |
| 14 | PX RECEIVE | | 298 | 136K| 15288 (2)| 00:03:35 | | | Q2,18 | PCWP | |
| 15 | PX SEND HASH | :TQ20016 | 298 | 136K| 15288 (2)| 00:03:35 | | | Q2,16 | P->P | HASH |
|* 16 | HASH JOIN | | 298 | 136K| 15288 (2)| 00:03:35 | | | Q2,16 | PCWP | |
| 17 | PX RECEIVE | | 298 | 106K| 15009 (2)| 00:03:31 | | | Q2,16 | PCWP | |
| 18 | PX SEND BROADCAST | :TQ20014 | 298 | 106K| 15009 (2)| 00:03:31 | | | Q2,14 | P->P | BROADCAST |
|* 19 | HASH JOIN | | 298 | 106K| 15009 (2)| 00:03:31 | | | Q2,14 | PCWP | |
| 20 | PX RECEIVE | | 298 | 84632 | 14984 (2)| 00:03:30 | | | Q2,14 | PCWP | |
| 21 | PX SEND BROADCAST | :TQ20012 | 298 | 84632 | 14984 (2)| 00:03:30 | | | Q2,12 | P->P | BROADCAST |
|* 22 | HASH JOIN | | 298 | 84632 | 14984 (2)| 00:03:30 | | | Q2,12 | PCWP | |
| 23 | PX RECEIVE | | 298 | 51554 | 14956 (2)| 00:03:30 | | | Q2,12 | PCWP | |
| 24 | PX SEND BROADCAST | :TQ20010 | 298 | 51554 | 14956 (2)| 00:03:30 | | | Q2,10 | P->P | BROADCAST |
|* 25 | HASH JOIN | | 298 | 51554 | 14956 (2)| 00:03:30 | | | Q2,10 | PCWP | |
| 26 | PX RECEIVE | | 298 | 48276 | 14950 (2)| 00:03:30 | | | Q2,10 | PCWP | |
| 27 | PX SEND BROADCAST | :TQ20007 | 298 | 48276 | 14950 (2)| 00:03:30 | | | Q2,07 | P->P | BROADCAST |
|* 28 | HASH JOIN BUFFERED | | 298 | 48276 | 14950 (2)| 00:03:30 | | | Q2,07 | PCWP | |
| 29 | PX JOIN FILTER CREATE | :BF0000 | 298 | 34270 | 14944 (2)| 00:03:30 | | | Q2,07 | PCWP | |
| 30 | PX RECEIVE | | 298 | 34270 | 14944 (2)| 00:03:30 | | | Q2,07 | PCWP | |
| 31 | PX SEND HASH | :TQ20002 | 298 | 34270 | 14944 (2)| 00:03:30 | | | Q2,02 | P->P | HASH |
|* 32 | HASH JOIN | | 298 | 34270 | 14944 (2)| 00:03:30 | | | Q2,02 | PCWP | |
| 33 | PX RECEIVE | | 1 | 11 | 6 (0)| 00:00:01 | | | Q2,02 | PCWP | |
| 34 | PX SEND BROADCAST | :TQ20000 | 1 | 11 | 6 (0)| 00:00:01 | | | Q2,00 | P->P | BROADCAST |
| 35 | PX BLOCK ITERATOR | | 1 | 11 | 6 (0)| 00:00:01 | | | Q2,00 | PCWC | |
|* 36 | TABLE ACCESS FULL | DIM_EMPLOYEE_ROLE | 1 | 11 | 6 (0)| 00:00:01 | | | Q2,00 | PCWP | |
|* 37 | HASH JOIN | | 895 | 93080 | 14938 (2)| 00:03:30 | | | Q2,02 | PCWP | |
| 38 | PX RECEIVE | | 120 | 3120 | 11 (0)| 00:00:01 | | | Q2,02 | PCWP | |
| 39 | PX SEND BROADCAST | :TQ20001 | 120 | 3120 | 11 (0)| 00:00:01 | | | Q2,01 | P->P | BROADCAST |
| 40 | PX BLOCK ITERATOR | | 120 | 3120 | 11 (0)| 00:00:01 | | | Q2,01 | PCWC | |
|* 41 | TABLE ACCESS FULL | DIM_DATE | 120 | 3120 | 11 (0)| 00:00:01 | | | Q2,01 | PCWP | |
| 42 | PX PARTITION RANGE ALL | | 6179 | 470K| 14926 (2)| 00:03:29 | 1 | 26 | Q2,02 | PCWC | |
|* 43 | TABLE ACCESS BY LOCAL INDEX ROWID| FACT_EMPLOYEE_ORDER | 6179 | 470K| 14926 (2)| 00:03:29 | 1 | 26 | Q2,02 | PCWP | |
| 44 | BITMAP CONVERSION TO ROWIDS | | | | | | | | Q2,02 | PCWP | |
| 45 | BITMAP AND | | | | | | | | Q2,02 | PCWP | |
| 46 | BITMAP MERGE | | | | | | | | Q2,02 | PCWP | |
| 47 | BITMAP KEY ITERATION | | | | | | | | Q2,02 | PCWP | |
| 48 | BUFFER SORT | | | | | | | | Q2,02 | PCWP | |
|* 49 | TABLE ACCESS FULL | DIM_EMPLOYEE_ROLE | 1 | 11 | 6 (0)| 00:00:01 | | | Q2,02 | PCWP | |
|* 50 | BITMAP INDEX RANGE SCAN | XIF35FACT_EMPLOYEE_ORDER | | | | | 1 | 26 | Q2,02 | PCWP | |
| 51 | BITMAP MERGE | | | | | | | | Q2,02 | PCWP | |
| 52 | BITMAP KEY ITERATION | | | | | | | | Q2,02 | PCWP | |
| 53 | BUFFER SORT | | | | | | | | Q2,02 | PCWP | |
|* 54 | TABLE ACCESS FULL | DIM_PRODUCT_HIERARCHY | 802 | 37694 | 6 (0)| 00:00:01 | | | Q2,02 | PCWP | |
|* 55 | BITMAP INDEX RANGE SCAN | XIF26FACT_EMPLOYEE_ORDER | | | | | 1 | 26 | Q2,02 | PCWP | |
| 56 | BITMAP MERGE | | | | | | | | Q2,02 | PCWP | |
| 57 | BITMAP KEY ITERATION | | | | | | | | Q2,02 | PCWP | |
| 58 | BUFFER SORT | | | | | | | | Q2,02 | PCWP | |
|* 59 | TABLE ACCESS FULL | DIM_DATE | 120 | 3120 | 11 (0)| 00:00:01 | | | Q2,02 | PCWP | |
| 60 | BITMAP CONVERSION FROM ROWID| | | | | | | | Q2,02 | PCWP | |
|* 61 | INDEX RANGE SCAN | XIF13FACT_EMPLOYEE_ORDER | | | 265 (1)| 00:00:04 | 1 | 26 | Q2,02 | PCWP | |
| 62 | BITMAP MERGE | | | | | | | | Q2,02 | PCWP | |
| 63 | BITMAP KEY ITERATION | | | | | | | | Q2,02 | PCWP | |
| 64 | BUFFER SORT | | | | | | | | Q2,02 | PCWP | |
| 65 | TABLE ACCESS FULL | SYS_TEMP_0FD9D663D_4091BC55 | 1 | 13 | 2 (0)| 00:00:01 | | | Q2,02 | PCWP | |
|* 66 | BITMAP INDEX RANGE SCAN | XIF34FACT_EMPLOYEE_ORDER | | | | | 1 | 26 | Q2,02 | PCWP | |
| 67 | PX RECEIVE | | 802 | 37694 | 6 (0)| 00:00:01 | | | Q2,07 | PCWP | |
| 68 | PX SEND HASH | :TQ20003 | 802 | 37694 | 6 (0)| 00:00:01 | | | Q2,03 | P->P | HASH |
| 69 | PX JOIN FILTER USE | :BF0000 | 802 | 37694 | 6 (0)| 00:00:01 | | | Q2,03 | PCWP | |
| 70 | PX BLOCK ITERATOR | | 802 | 37694 | 6 (0)| 00:00:01 | | | Q2,03 | PCWC | |
|* 71 | TABLE ACCESS FULL | DIM_PRODUCT_HIERARCHY | 802 | 37694 | 6 (0)| 00:00:01 | | | Q2,03 | PCWP | |
| 72 | PX BLOCK ITERATOR | | 13200 | 141K| 6 (0)| 00:00:01 | | | Q2,10 | PCWC | |
| 73 | TABLE ACCESS FULL | DIM_SOURCE_SYSTEM | 13200 | 141K| 6 (0)| 00:00:01 | | | Q2,10 | PCWP | |
| 74 | PX BLOCK ITERATOR | | 20810 | 2255K| 27 (0)| 00:00:01 | | | Q2,12 | PCWC | |
| 75 | TABLE ACCESS FULL | DIM_TEAM | 20810 | 2255K| 27 (0)| 00:00:01 | | | Q2,12 | PCWP | |
| 76 | PX BLOCK ITERATOR | | 48785 | 3906K| 25 (0)| 00:00:01 | | | Q2,14 | PCWC | |
| 77 | TABLE ACCESS FULL | SYS_TEMP_0FD9D663D_4091BC55 | 48785 | 3906K| 25 (0)| 00:00:01 | | | Q2,14 | PCWP | |
| 78 | PX BLOCK ITERATOR | | 130K| 12M| 278 (2)| 00:00:04 | | | Q2,16 | PCWC | |
| 79 | TABLE ACCESS FULL | DIM_FINANCIAL_HIERARCHY | 130K| 12M| 278 (2)| 00:00:04 | | | Q2,16 | PCWP | |
|* 80 | FILTER | | | | | | | | Q2,18 | PCWC | |
| 81 | SORT GROUP BY | | 1532 | 755K| 31440 (1)| 00:07:21 | | | Q2,18 | PCWP | |
| 82 | PX RECEIVE | | 3451 | 1701K| 31439 (1)| 00:07:21 | | | Q2,18 | PCWP | |
| 83 | PX SEND HASH | :TQ20017 | 3451 | 1701K| 31439 (1)| 00:07:21 | | | Q2,17 | P->P | HASH |
|* 84 | HASH JOIN | | 3451 | 1701K| 31439 (1)| 00:07:21 | | | Q2,17 | PCWP | |
| 85 | PX RECEIVE | | 3451 | 1391K| 29225 (1)| 00:06:50 | | | Q2,17 | PCWP | |
| 86 | PX SEND BROADCAST | :TQ20015 | 3451 | 1391K| 29225 (1)| 00:06:50 | | | Q2,15 | P->P | BROADCAST |
|* 87 | HASH JOIN | | 3451 | 1391K| 29225 (1)| 00:06:50 | | | Q2,15 | PCWP | |
| 88 | PX RECEIVE | | 3451 | 1044K| 28946 (1)| 00:06:46 | | | Q2,15 | PCWP | |
| 89 | PX SEND BROADCAST | :TQ20013 | 3451 | 1044K| 28946 (1)| 00:06:46 | | | Q2,13 | P->P | BROADCAST |
|* 90 | HASH JOIN | | 3451 | 1044K| 28946 (1)| 00:06:46 | | | Q2,13 | PCWP | |
| 91 | PX BLOCK ITERATOR | | 20810 | 2255K| 27 (0)| 00:00:01 | | | Q2,13 | PCWC | |
| 92 | TABLE ACCESS FULL | DIM_TEAM | 20810 | 2255K| 27 (0)| 00:00:01 | | | Q2,13 | PCWP | |
| 93 | BUFFER SORT | | | | | | | | Q2,13 | PCWC | |
| 94 | PX RECEIVE | | 3451 | 670K| 28918 (1)| 00:06:45 | | | Q2,13 | PCWP | |
| 95 | PX SEND BROADCAST | :TQ20011 | 3451 | 670K| 28918 (1)| 00:06:45 | | | Q2,11 | P->P | BROADCAST |
|* 96 | HASH JOIN BUFFERED | | 3451 | 670K| 28918 (1)| 00:06:45 | | | Q2,11 | PCWP | |
| 97 | PX JOIN FILTER CREATE | :BF0001 | 13200 | 141K| 6 (0)| 00:00:01 | | | Q2,11 | PCWP | |
| 98 | PX RECEIVE | | 13200 | 141K| 6 (0)| 00:00:01 | | | Q2,11 | PCWP | |
| 99 | PX SEND HASH | :TQ20008 | 13200 | 141K| 6 (0)| 00:00:01 | | | Q2,08 | P->P | HASH |
| 100 | PX BLOCK ITERATOR | | 13200 | 141K| 6 (0)| 00:00:01 | | | Q2,08 | PCWC | |
| 101 | TABLE ACCESS FULL | DIM_SOURCE_SYSTEM | 13200 | 141K| 6 (0)| 00:00:01 | | | Q2,08 | PCWP | |
| 102 | PX RECEIVE | | 3451 | 633K| 28912 (1)| 00:06:45 | | | Q2,11 | PCWP | |
| 103 | PX SEND HASH | :TQ20009 | 3451 | 633K| 28912 (1)| 00:06:45 | | | Q2,09 | P->P | HASH |
| 104 | PX JOIN FILTER USE | :BF0001 | 3451 | 633K| 28912 (1)| 00:06:45 | | | Q2,09 | PCWP | |
|*105 | HASH JOIN | | 3451 | 633K| 28912 (1)| 00:06:45 | | | Q2,09 | PCWP | |
| 106 | PX RECEIVE | | 802 | 37694 | 6 (0)| 00:00:01 | | | Q2,09 | PCWP | |
| 107 | PX SEND BROADCAST | :TQ20004 | 802 | 37694 | 6 (0)| 00:00:01 | | | Q2,04 | P->P | BROADCAST |
| 108 | PX BLOCK ITERATOR | | 802 | 37694 | 6 (0)| 00:00:01 | | | Q2,04 | PCWC | |
|*109 | TABLE ACCESS FULL | DIM_PRODUCT_HIERARCHY | 802 | 37694 | 6 (0)| 00:00:01 | | | Q2,04 | PCWP | |
|*110 | HASH JOIN | | 3452 | 475K| 28906 (1)| 00:06:45 | | | Q2,09 | PCWP | |
| 111 | PX RECEIVE | | 120 | 3120 | 11 (0)| 00:00:01 | | | Q2,09 | PCWP | |
| 112 | PX SEND BROADCAST | :TQ20005 | 120 | 3120 | 11 (0)| 00:00:01 | | | Q2,05 | P->P | BROADCAST |
| 113 | PX BLOCK ITERATOR | | 120 | 3120 | 11 (0)| 00:00:01 | | | Q2,05 | PCWC | |
|*114 | TABLE ACCESS FULL | DIM_DATE | 120 | 3120 | 11 (0)| 00:00:01 | | | Q2,05 | PCWP | |
|*115 | HASH JOIN | | 23843 | 2677K| 28894 (1)| 00:06:45 | | | Q2,09 | PCWP | |
| 116 | PX RECEIVE | | 1 | 11 | 6 (0)| 00:00:01 | | | Q2,09 | PCWP | |
| 117 | PX SEND BROADCAST | :TQ20006 | 1 | 11 | 6 (0)| 00:00:01 | | | Q2,06 | P->P | BROADCAST |
| 118 | PX BLOCK ITERATOR | | 1 | 11 | 6 (0)| 00:00:01 | | | Q2,06 | PCWC | |
|*119 | TABLE ACCESS FULL | DIM_EMPLOYEE_ROLE | 1 | 11 | 6 (0)| 00:00:01 | | | Q2,06 | PCWP | |
| 120 | PX PARTITION RANGE ALL | | 71528 | 7264K| 28888 (1)| 00:06:45 | 1 | 26 | Q2,09 | PCWC | |
|*121 | TABLE ACCESS BY LOCAL INDEX ROWID| FACT_EMPLOYEE_ORDER | 71528 | 7264K| 28888 (1)| 00:06:45 | 1 | 26 | Q2,09 | PCWP | |
| 122 | BITMAP CONVERSION TO ROWIDS | | | | | | | | Q2,09 | PCWP | |
| 123 | BITMAP AND | | | | | | | | Q2,09 | PCWP | |
| 124 | BITMAP MERGE | | | | | | | | Q2,09 | PCWP | |
| 125 | BITMAP KEY ITERATION | | | | | | | | Q2,09 | PCWP | |
| 126 | BUFFER SORT | | | | | | | | Q2,09 | PCWP | |
|*127 | TABLE ACCESS FULL | DIM_EMPLOYEE_ROLE | 1 | 11 | 6 (0)| 00:00:01 | | | Q2,09 | PCWP | |
|*128 | BITMAP INDEX RANGE SCAN | XIF35FACT_EMPLOYEE_ORDER | | | | | 1 | 26 | Q2,09 | PCWP | |
| 129 | BITMAP MERGE | | | | | | | | Q2,09 | PCWP | |
| 130 | BITMAP KEY ITERATION | | | | | | | | Q2,09 | PCWP | |
| 131 | BUFFER SORT | | | | | | | | Q2,09 | PCWP | |
|*132 | TABLE ACCESS FULL | DIM_PRODUCT_HIERARCHY | 802 | 37694 | 6 (0)| 00:00:01 | | | Q2,09 | PCWP | |
|*133 | BITMAP INDEX RANGE SCAN | XIF26FACT_EMPLOYEE_ORDER | | | | | 1 | 26 | Q2,09 | PCWP | |
| 134 | BITMAP MERGE | | | | | | | | Q2,09 | PCWP | |
| 135 | BITMAP KEY ITERATION | | | | | | | | Q2,09 | PCWP | |
| 136 | BUFFER SORT | | | | | | | | Q2,09 | PCWP | |
|*137 | TABLE ACCESS FULL | DIM_DATE | 120 | 3120 | 11 (0)| 00:00:01 | | | Q2,09 | PCWP | |
| 138 | BITMAP CONVERSION FROM ROWID| | | | | | | | Q2,09 | PCWP | |
|*139 | INDEX RANGE SCAN | XIF13FACT_EMPLOYEE_ORDER | 92814 | | 265 (1)| 00:00:04 | 1 | 26 | Q2,09 | PCWP | |
| 140 | PX BLOCK ITERATOR | | 130K| 12M| 278 (2)| 00:00:04 | | | Q2,15 | PCWC | |
| 141 | TABLE ACCESS FULL | DIM_FINANCIAL_HIERARCHY | 130K| 12M| 278 (2)| 00:00:04 | | | Q2,15 | PCWP | |
| 142 | PX BLOCK ITERATOR | | 632K| 55M| 2213 (1)| 00:00:31 | | | Q2,17 | PCWC | |
|*143 | TABLE ACCESS FULL | DIM_CLIENT | 632K| 55M| 2213 (1)| 00:00:31 | | | Q2,17 | PCWP | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|
|
|
|
| Re: what to do about this one !!! [message #190372 is a reply to message #190353] |
Wed, 30 August 2006 07:06 |
wagnerch
Messages: 58
Registered: July 2006
|
Member |
|
|
Why do you need the UNION ALL? The only difference between the first half and the second half is:
- AND dim_client.sector_l1_3_nm = 'CORPORATES'
+ AND dim_client.sector_l1_3_nm != 'CORPORATES'
Why don't you just drop the second half and replace the clause in the top half with:
AND dim_client.sector_l1_3_nm IS NOT NULL
|
|
|
|
|
|
| Re: what to do about this one !!! [message #190378 is a reply to message #190374] |
Wed, 30 August 2006 07:23 |
wagnerch
Messages: 58
Registered: July 2006
|
Member |
|
|
Give a dynamic view a shot, how about something like this?
SELECT *
FROM (SELECT dim_client.nm_cmu_4
,dim_client.nm_cmu_3
,dim_client.nm_cmu_2
,dim_client.nm_cmu_1
,dim_client.franchise_l1_1_nm
,fact_employee_order.feed_leading_source_system_id
,dim_source_system.nm_leading_source_system
,fact_employee_order.artemis_source_system_id
,dim_date.num_year
,dim_date.nm_abbr_month
,dim_team.team_level_6_nm
,dim_team.team_level_5_nm
,dim_team.team_level_4_nm
,dim_team.team_level_3_nm
,dim_team.team_level_2_nm
,dim_team.team_level_1_nm
,dim_product_hierarchy.product_level_4_nm
,dim_product_hierarchy.product_level_3_nm
,dim_product_hierarchy.product_level_2_nm
,dim_product_hierarchy.product_level_1_nm
,dim_trading_desk.nm_ou_abbr
,dim_trading_desk.id_ou
,dim_trading_desk.nm_le
,dim_trading_desk.id_le
,dim_trading_desk.nm_level_9
,dim_trading_desk.nm_level_8
,dim_trading_desk.nm_level_7
,dim_trading_desk.nm_level_6
,dim_trading_desk.nm_level_5
,dim_trading_desk.summ_pt_level_9
,dim_trading_desk.summ_pt_level_8
,dim_trading_desk.summ_pt_level_7
,dim_trading_desk.summ_pt_level_6
,dim_trading_desk.summ_pt_level_5
,dim_date.dt_date
,dim_employee_role.nm_employee_role
,dim_team.team_level_0_nm
,dim_client.sector_l1_1_nm
,SUM (fact_employee_order.am_net_cva_delta) net_cva
,SUM (fact_employee_order.am_net_cva_asgn_delta) a_net_cva
,SUM (fact_employee_order.am_net_cva_x_role_delta) x_net_cva
,SUM (fact_employee_order.am_net_cva_role_delta) f_net_cva
,dim_client.spn_id
FROM fact_employee_order
,dim_client
,dim_team
,dim_employee_role
,dim_product_hierarchy
,dim_source_system
,dim_date
,dim_financial_hierarchy dim_trading_desk
WHERE fact_employee_order.id_source_system =
dim_source_system.id_source_system
AND fact_employee_order.id_client = dim_client.id_client
AND fact_employee_order.id_employee_role =
dim_employee_role.id_employee_role
AND fact_employee_order.id_le_ou_trading_desk =
dim_trading_desk.id_le_ou
AND dim_product_hierarchy.id_instrument_product =
fact_employee_order.id_instrument_product
AND dim_team.id_team = fact_employee_order.id_team
AND fact_employee_order.id_reporting_date = dim_date.id_date
AND ind_to_be_deleted = 'N'
AND ind_is_reportable = 'Y'
AND dim_product_hierarchy.product_level_2_nm IN
('EQ OTC DERIV'
,'OTHER CR EXO/HYB'
,'SYNTH ETIC CDO - INV'
,'FX SPOT'
,'SOFTS'
,'STR ALTERN INVEST'
,'PRECIOUS METALS'
,'FX SWAP'
,'GAS'
,'IRD SWAPS'
,'IRD EXO/HYB'
,'FX EXOTIC OPTIONS'
,'POWER'
,'TRANCHE'
,'FX HYBRID OPTIONS'
,'EQ SECURIT DERIV'
,'EMISSIONS'
,'FX OPTIONS'
,'FX FORWARD'
,'OIL'
,'IRD OPTIONS'
,'FTD'
,'BASE METALS'
,'SYNTHETIC CDO - MGR'
,'COAL'
)
AND dim_date.year_month BETWEEN 200601 AND 200607
AND dim_employee_role.nm_employee_role = 'SALES'
AND dim_client.sector_l1_3_nm IS NOT NULL
GROUP BY dim_client.nm_cmu_4
,dim_client.nm_cmu_3
,dim_client.nm_cmu_2
,dim_client.nm_cmu_1
,dim_client.franchise_l1_1_nm
,fact_employee_order.feed_leading_source_system_id
,dim_source_system.nm_leading_source_system
,fact_employee_order.artemis_source_system_id
,dim_date.num_year
,dim_date.nm_abbr_month
,dim_team.team_level_6_nm
,dim_team.team_level_5_nm
,dim_team.team_level_4_nm
,dim_team.team_level_3_nm
,dim_team.team_level_2_nm
,dim_team.team_level_1_nm
,dim_product_hierarchy.product_level_4_nm
,dim_product_hierarchy.product_level_3_nm
,dim_product_hierarchy.product_level_2_nm
,dim_product_hierarchy.product_level_1_nm
,dim_trading_desk.nm_ou_abbr
,dim_trading_desk.id_ou
,dim_trading_desk.nm_le
,dim_trading_desk.id_le
,dim_trading_desk.nm_level_9
,dim_trading_desk.nm_level_8
,dim_trading_desk.nm_level_7
,dim_trading_desk.nm_level_6
,dim_trading_desk.nm_level_5
,dim_trading_desk.summ_pt_level_9
,dim_trading_desk.summ_pt_level_8
,dim_trading_desk.summ_pt_level_7
,dim_trading_desk.summ_pt_level_6
,dim_trading_desk.summ_pt_level_5
,dim_date.dt_date
,dim_employee_role.nm_employee_role
,dim_team.team_level_0_nm
,dim_client.sector_l1_1_nm
,dim_client.spn_id)
WHERE ( dim_client.sector_l1_3_nm != 'CORPORATES'
AND net_cva_delta >= 250000
)
OR dim_client.sector_l1_3_nm = 'CORPORATES'
Full scans are not evil, and in this case many of them seem reasonable. It would be a good idea to eliminate the second half of the query and the buffer sort. Give the query above a shot and post a new plan, let us know if it improved or not. I believe the results should be the same.
[Updated on: Wed, 30 August 2006 07:32] Report message to a moderator |
|
|
|
|
|
| Re: what to do about this one !!! [message #190384 is a reply to message #190378] |
Wed, 30 August 2006 07:46 |
seemit
Messages: 7
Registered: July 2006
|
Junior Member |
|
|
In the query you modified, refer to following portion
WHERE ( dim_client.sector_l1_3_nm != 'CORPORATES'
AND net_cva_delta >= 250000
)
The where clause in braces will not detect table.column as there is no FROM in it.
Moreover even if it did, i wanted a sum(NET_CVA_DELTA) which ofcourse wont be possible after a where clause.
|
|
|
|
| Re: what to do about this one !!! [message #190385 is a reply to message #190384] |
Wed, 30 August 2006 07:48 |
wagnerch
Messages: 58
Registered: July 2006
|
Member |
|
|
My expectation is that you know enough to figure it out. Clearly I don't have the tables to validate syntax errors, so some of the items were overlooked.
It is evaluated based the SUM of the column, but I guess you do not understand my advice.
|
|
|
|
|
|
| Re: what to do about this one !!! [message #190393 is a reply to message #190343] |
Wed, 30 August 2006 08:31 |
seemit
Messages: 7
Registered: July 2006
|
Junior Member |
|
|
am sorry that I goofed up !
I totally understand the logic behind your query. Had to add a column in the dynamic view to make it work. here is the modified one
SELECT *
FROM (SELECT dim_client.sector_l1_3_nm sector
,dim_client.nm_cmu_4
,dim_client.nm_cmu_3
,dim_client.nm_cmu_2
,dim_client.nm_cmu_1
,dim_client.franchise_l1_1_nm
,fact_employee_order.feed_leading_source_system_id
,dim_source_system.nm_leading_source_system
,fact_employee_order.artemis_source_system_id
,dim_date.num_year
,dim_date.nm_abbr_month
,dim_team.team_level_6_nm
,dim_team.team_level_5_nm
,dim_team.team_level_4_nm
,dim_team.team_level_3_nm
,dim_team.team_level_2_nm
,dim_team.team_level_1_nm
,dim_product_hierarchy.product_level_4_nm
,dim_product_hierarchy.product_level_3_nm
,dim_product_hierarchy.product_level_2_nm
,dim_product_hierarchy.product_level_1_nm
,dim_trading_desk.nm_ou_abbr
,dim_trading_desk.id_ou
,dim_trading_desk.nm_le
,dim_trading_desk.id_le
,dim_trading_desk.nm_level_9
,dim_trading_desk.nm_level_8
,dim_trading_desk.nm_level_7
,dim_trading_desk.nm_level_6
,dim_trading_desk.nm_level_5
,dim_trading_desk.summ_pt_level_9
,dim_trading_desk.summ_pt_level_8
,dim_trading_desk.summ_pt_level_7
,dim_trading_desk.summ_pt_level_6
,dim_trading_desk.summ_pt_level_5
,dim_date.dt_date
,dim_employee_role.nm_employee_role
,dim_team.team_level_0_nm
,dim_client.sector_l1_1_nm
,SUM (fact_employee_order.am_net_cva_delta) net_cva
,SUM (fact_employee_order.am_net_cva_asgn_delta) a_net_cva
,SUM (fact_employee_order.am_net_cva_x_role_delta) x_net_cva
,SUM (fact_employee_order.am_net_cva_role_delta) f_net_cva
,dim_client.spn_id
FROM fact_employee_order
,dim_client
,dim_team
,dim_employee_role
,dim_product_hierarchy
,dim_source_system
,dim_date
,dim_financial_hierarchy dim_trading_desk
WHERE fact_employee_order.id_source_system = dim_source_system.id_source_system
AND fact_employee_order.id_client = dim_client.id_client
AND fact_employee_order.id_employee_role = dim_employee_role.id_employee_role
AND fact_employee_order.id_le_ou_trading_desk = dim_trading_desk.id_le_ou
AND dim_product_hierarchy.id_instrument_product = fact_employee_order.id_instrument_product
AND dim_team.id_team = fact_employee_order.id_team
AND fact_employee_order.id_reporting_date = dim_date.id_date
AND ind_to_be_deleted = 'N'
AND ind_is_reportable = 'Y'
AND dim_product_hierarchy.product_level_2_nm IN
('EQ OTC DERIV'
,'OTHER CR EXO/HYB'
,'SYNTH ETIC CDO - INV'
,'FX SPOT'
,'SOFTS'
,'STR ALTERN INVEST'
,'PRECIOUS METALS'
,'FX SWAP'
,'GAS'
,'IRD SWAPS'
,'IRD EXO/HYB'
,'FX EXOTIC OPTIONS'
,'POWER'
,'TRANCHE'
,'FX HYBRID OPTIONS'
,'EQ SECURIT DERIV'
,'EMISSIONS'
,'FX OPTIONS'
,'FX FORWARD'
,'OIL'
,'IRD OPTIONS'
,'FTD'
,'BASE METALS'
,'SYNTHETIC CDO - MGR'
,'COAL'
)
AND dim_date.year_month BETWEEN 200601 AND 200607
AND dim_employee_role.nm_employee_role = 'SALES'
AND dim_client.sector_l1_3_nm IS NOT NULL
GROUP BY dim_client.sector_l1_3_nm
,dim_client.nm_cmu_4
,dim_client.nm_cmu_3
,dim_client.nm_cmu_2
,dim_client.nm_cmu_1
,dim_client.franchise_l1_1_nm
,fact_employee_order.feed_leading_source_system_id
,dim_source_system.nm_leading_source_system
,fact_employee_order.artemis_source_system_id
,dim_date.num_year
,dim_date.nm_abbr_month
,dim_team.team_level_6_nm
,dim_team.team_level_5_nm
,dim_team.team_level_4_nm
,dim_team.team_level_3_nm
,dim_team.team_level_2_nm
,dim_team.team_level_1_nm
,dim_product_hierarchy.product_level_4_nm
,dim_product_hierarchy.product_level_3_nm
,dim_product_hierarchy.product_level_2_nm
,dim_product_hierarchy.product_level_1_nm
,dim_trading_desk.nm_ou_abbr
,dim_trading_desk.id_ou
,dim_trading_desk.nm_le
,dim_trading_desk.id_le
,dim_trading_desk.nm_level_9
,dim_trading_desk.nm_level_8
,dim_trading_desk.nm_level_7
,dim_trading_desk.nm_level_6
,dim_trading_desk.nm_level_5
,dim_trading_desk.summ_pt_level_9
,dim_trading_desk.summ_pt_level_8
,dim_trading_desk.summ_pt_level_7
,dim_trading_desk.summ_pt_level_6
,dim_trading_desk.summ_pt_level_5
,dim_date.dt_date
,dim_employee_role.nm_employee_role
,dim_team.team_level_0_nm
,dim_client.sector_l1_1_nm
,dim_client.spn_id) ABC
WHere ( abc.net_cva >=25000 and
abc.sector != 'CORPORATES' )
OR abc.sector = 'CORPORATES'
I wonder if its gonna be same.
Explain plan seems attractive.
Once again, Sorry I didnt even look at what you had pasted and posted something back for it. and Yes, I need to study lots of stuff bout Oracle, still in a newbee category. Thanks a ton for your help. I have fired the query up and will update you as soon as this gives same result.
|
|
|
|
Goto Forum:
Current Time: Sat Nov 23 14:03:03 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
