Home » RDBMS Server » Performance Tuning » Complex query
| Complex query [message #228537] |
Tue, 03 April 2007 05:41  |
a_developer
Messages: 194
Registered: January 2006
|
Senior Member |
|
|
hi,
I have this query below. Notice that it has 3 select statements within it which are all the same except for the selected column: one is fob, the other is freight and the last is just 1.
select
f.client_id,
un.primary_store_id,
kts.dc_product_num,
f.invoice_trx_date,
sum(f.fact_delivered_cost) as total_purchases,
sum(f.equival_cases) as total_cases,
sum((select dlc.fob
from dist_landed_cost_mst dlc
where dlc.operator_name = 'CGA'
and dlc.dc_num = dc.dc_num
and dlc.dc_product_num = kts.dc_product_num
and f.invoice_trx_date between dlc.effective_start_date and dlc.effective_end_date)
* f.equival_cases ) as total_fob,
sum((select dlc.freight
from dist_landed_cost_mst dlc
where dlc.operator_name = 'CGA'
and dlc.dc_num = dc.dc_num
and dlc.dc_product_num = kts.dc_product_num
and f.invoice_trx_date between dlc.effective_start_date and dlc.effective_end_date)
* f.equival_cases) as total_freight,
sum((select 1
from dist_landed_cost_mst dlc
where dlc.operator_name = 'CGA'
and dlc.dc_num = dc.dc_num
and dlc.dc_product_num = kts.dc_product_num
and f.invoice_trx_date between dlc.effective_start_date and dlc.effective_end_date)
* f.equival_cases) as total_fob_cases
from t_cga_fact f,
cga_unit un,
cga_product_dim kts,
dc_mv dc
where f.org_dim_dc_id = dc.org_dim_dc_id
and f.org_dim_unit_id = un.org_dim_unit_id
and f.product_dim_id = kts.product_dim_id
and f.invoice_trx_date > (select add_months(max(invoice_trx_date),-16) from t_cga_fact)
group by f.client_id,
un.primary_store_id,
kts.dc_product_num,
f.invoice_trx_date
I'm looking at the possibility of having it to something like:
select
f.client_id,
un.primary_store_id,
kts.dc_product_num,
f.invoice_trx_date,
sum(f.fact_delivered_cost) as total_purchases,
sum(f.equival_cases) as total_cases,
select sum(dlc.fob) * f.equival_cases as total_fob,
sum(dlc.freight) * f.equival_cases as total_freight,
count(1) * f.equival_cases as total_fob_cases
from dist_landed_cost_mst dlc
where dlc.operator_name = 'CGA'
and dlc.dc_num = dc.dc_num
and dlc.dc_product_num = kts.dc_product_num
and f.invoice_trx_date between dlc.effective_start_date and dlc.effective_end_date
from t_cga_fact f,
cga_unit un,
cga_product_dim kts,
dc_mv dc
where f.org_dim_dc_id = dc.org_dim_dc_id
and f.org_dim_unit_id = un.org_dim_unit_id
and f.product_dim_id = kts.product_dim_id
and f.invoice_trx_date > (select add_months(max(invoice_trx_date),-16) from t_cga_fact)
group by f.client_id,
un.primary_store_id,
kts.dc_product_num,
f.invoice_trx_date
Is it possible?? How can I optimize this??
[Updated on: Tue, 03 April 2007 05:43] Report message to a moderator |
|
|
|
| Re: Complex query [message #228551 is a reply to message #228537] |
Tue, 03 April 2007 06:13  |
a_developer
Messages: 194
Registered: January 2006
|
Senior Member |
|
|
By the way, here's the explan plan:
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2059515001
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10M| 1215M| | 64773 (1)| 00:15:07 | | | | | |
|* 1 | MAT_VIEW ACCESS BY INDEX ROWID | DIST_LANDED_COST_MST | 1 | 49 | | 1 (0)| 00:00:01 | | | | | |
|* 2 | INDEX RANGE SCAN | XIE3T_DIST_LANDED_COST_OP | 1 | | | 1 (0)| 00:00:01 | | | | | |
|* 3 | MAT_VIEW ACCESS BY INDEX ROWID | DIST_LANDED_COST_MST | 1 | 49 | | 1 (0)| 00:00:01 | | | | | |
|* 4 | INDEX RANGE SCAN | XIE3T_DIST_LANDED_COST_OP | 1 | | | 1 (0)| 00:00:01 | | | | | |
|* 5 | MAT_VIEW ACCESS BY INDEX ROWID | DIST_LANDED_COST_MST | 1 | 36 | | 1 (0)| 00:00:01 | | | | | |
|* 6 | INDEX RANGE SCAN | XIE3T_DIST_LANDED_COST_OP | 1 | | | 1 (0)| 00:00:01 | | | | | |
| 7 | PX COORDINATOR | | | | | | | | | | | |
| 8 | PX SEND QC (RANDOM) | :TQ10004 | 10M| 1215M| | 64773 (1)| 00:15:07 | | | Q1,04 | P->S | QC (RAND) |
| 9 | HASH GROUP BY | | 10M| 1215M| 2656M| 64773 (1)| 00:15:07 | | | Q1,04 | PCWP | |
| 10 | PX RECEIVE | | 10M| 1215M| | 55815 (1)| 00:13:02 | | | Q1,04 | PCWP | |
| 11 | PX SEND HASH | :TQ10003 | 10M| 1215M| | 55815 (1)| 00:13:02 | | | Q1,03 | P->P | HASH |
|* 12 | HASH JOIN | | 10M| 1215M| | 55815 (1)| 00:13:02 | | | Q1,03 | PCWP | |
| 13 | PX RECEIVE | | 160K| 2196K| | 104 (1)| 00:00:02 | | | Q1,03 | PCWP | |
| 14 | PX SEND BROADCAST | :TQ10001 | 160K| 2196K| | 104 (1)| 00:00:02 | | | Q1,01 | P->P | BROADCAST |
| 15 | PX BLOCK ITERATOR | | 160K| 2196K| | 104 (1)| 00:00:02 | | | Q1,01 | PCWC | |
| 16 | INDEX FAST FULL SCAN | XIE_IPD_DIM_ID_DC_PROD_NUM | 160K| 2196K| | 104 (1)| 00:00:02 | | | Q1,01 | PCWP | |
|* 17 | HASH JOIN | | 10M| 1079M| | 55708 (1)| 00:13:00 | | | Q1,03 | PCWP | |
| 18 | PX RECEIVE | | 24227 | 567K| | 23 (0)| 00:00:01 | | | Q1,03 | PCWP | |
| 19 | PX SEND BROADCAST | :TQ10002 | 24227 | 567K| | 23 (0)| 00:00:01 | | | Q1,02 | P->P | BROADCAST |
| 20 | PX BLOCK ITERATOR | | 24227 | 567K| | 23 (0)| 00:00:01 | | | Q1,02 | PCWC | |
| 21 | INDEX FAST FULL SCAN | XIE_UNIT_DIM_ID_PRSTORE_ID | 24227 | 567K| | 23 (0)| 00:00:01 | | | Q1,02 | PCWP | |
|* 22 | HASH JOIN | | 10M| 846M| | 55682 (1)| 00:13:00 | | | Q1,03 | PCWP | |
| 23 | BUFFER SORT | | | | | | | | | Q1,03 | PCWC | |
| 24 | PX RECEIVE | | 458 | 10992 | | 1 (0)| 00:00:01 | | | Q1,03 | PCWP | |
| 25 | PX SEND BROADCAST | :TQ10000 | 458 | 10992 | | 1 (0)| 00:00:01 | | | | S->P | BROADCAST |
| 26 | INDEX FULL SCAN | XIE_DC_MV_DC_NUM_PR_ID | 458 | 10992 | | 1 (0)| 00:00:01 | | | | | |
| 27 | PX BLOCK ITERATOR | | 10M| 612M| | 55678 (1)| 00:13:00 | KEY | 35 | Q1,03 | PCWC | |
|* 28 | TABLE ACCESS FULL | T_CGA_FACT | 10M| 612M| | 55678 (1)| 00:13:00 | KEY | 35 | Q1,03 | PCWP | |
| 29 | SORT AGGREGATE | | 1 | 8 | | | | | | Q1,03 | PCWP | |
| 30 | PARTITION RANGE ALL | | 203M| 1556M| | 682 (0)| 00:00:10 | 1 | 35 | Q1,03 | PCWP | |
| 31 | BITMAP INDEX SINGLE VALUE| XIE_T_CGA_FACT_TRX_DT | 203M| 1556M| | 682 (0)| 00:00:10 | 1 | 35 | Q1,03 | PCWP | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DLC"."DC_PRODUCT_NUM"=:B1 AND "DLC"."DC_NUM"=:B2 AND "DLC"."EFFECTIVE_START_DATE"<=:B3 AND "DLC"."EFFECTIVE_END_DATE">=:B4)
2 - access("DLC"."OPERATOR_NAME"='CGA')
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3 - filter("DLC"."DC_PRODUCT_NUM"=:B1 AND "DLC"."DC_NUM"=:B2 AND "DLC"."EFFECTIVE_START_DATE"<=:B3 AND "DLC"."EFFECTIVE_END_DATE">=:B4)
4 - access("DLC"."OPERATOR_NAME"='CGA')
5 - filter("DLC"."DC_PRODUCT_NUM"=:B1 AND "DLC"."DC_NUM"=:B2 AND "DLC"."EFFECTIVE_START_DATE"<=:B3 AND "DLC"."EFFECTIVE_END_DATE">=:B4)
6 - access("DLC"."OPERATOR_NAME"='CGA')
12 - access("F"."PRODUCT_DIM_ID"="KTS"."PRODUCT_DIM_ID")
17 - access("F"."ORG_DIM_UNIT_ID"="UN"."ORG_DIM_UNIT_ID")
22 - access("F"."ORG_DIM_DC_ID"="DC"."ORG_DIM_DC_ID")
28 - filter("F"."INVOICE_TRX_DATE"> (SELECT ADD_MONTHS(MAX("INVOICE_TRX_DATE"),-16) FROM "T_CGA_FACT" "T_CGA_FACT"))
|
|
|
|
| Re: Complex query [message #228719 is a reply to message #228551] |
Tue, 03 April 2007 22:21 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Since you haven't said what's wrong with your second query, I'll have to guess.
My guess is that Oracle complains that the sub-query in the SELECT clause must return a single value, not 3 values. Right?
It can be done (Haven't tried this, but I reckon it will work...). You do have to return a single value, but that value can be an object type instead of 3 scalars.
Create an object type on the database with 3 numbers:
CREATE OR REPLACE TYPE MY_TYPE AS OBJECT (
num1 NUMBER
,num2 NUMBER
,num3 NUMBER
);
Then you use that type to wrap up the three values:
SELECT col, col, ...
,(
SELECT my_type( SUM(...), SUM(....), COUNT(*)
FROM ...
WHERE ...
)
FROM ...
Then you wrap that SQL as an inline view to extract the three columns from the object type:
SELECT col, col, ...
,my_obj.num1, my_obj.num2, my_obj.num3
FROM (
SELECT col, col, ...
,(
SELECT my_type( SUM(...), SUM(....), COUNT(*)
FROM ...
WHERE ...
) AS my_obj
FROM ...
)
Ross Leishman
|
|
|
|
Goto Forum:
Current Time: Thu May 16 03:03:40 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
