Home » RDBMS Server » Performance Tuning » Complex query
Complex query [message #228537] Tue, 03 April 2007 05:41 Go to next message
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 Go to previous messageGo to next message
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 Go to previous message
rleishman
Messages: 3724
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
Previous Topic: Database Performance Issue VS Network Performance Issue
Next Topic: How to tune my sql script
Goto Forum:
  


Current Time: Fri Dec 09 17:29:00 CST 2016

Total time taken to generate the page: 0.32601 seconds