A Query to understand DW concept

Hi Guys,

In month of July 2013 i was asked to improve reporting performance in datawarehouse.The reporting on DW was very slow .Report on DW were taking about 18 min to run which was unacceptable.

So i started from Oracle basics.You will see a lot of article on oracle ,Explain plan and other in July. After basics its time to understand how a query structure will be.How the actual joins should be taking place.Below is cognos GosalesDW query which has slightly Snowflaked schema same as my office environment so its good for analysis.In other article i am planning to study the explain plan of this and will try some tricks to improve plan.


SELECT (COALESCE("D2"."memberUniqueName2", "D3"."memberUniqueName2")) "memberUniqueName2",
  MIN((COALESCE("D2"."rc", "D3"."rc"))) over (partition BY (COALESCE("D2"."Product_type_key", "D3"."Product_type_key"))) "Product_type",
  (COALESCE("D2"."memberUniqueName4", "D3"."memberUniqueName4")) "memberUniqueName4",
  MIN((COALESCE("D2"."rc10", "D3"."rc8"))) over (partition BY (COALESCE("D2"."Retailer_country_key", "D3"."Retailer_country_key")), (COALESCE("D2"."Retailer_key", "D3"."Retailer_key"))) "Retailer_name",
  "D2"."memberUniqueName6" "memberUniqueName6",
  "D2"."Order_method_type" "Order_method_type",
  "D2"."Quantity" "Quantity",
  "D3"."Sales_target" "Sales_target",
  (COALESCE("D2"."Product_type_key", "D3"."Product_type_key")) "Product_type_key",
  (COALESCE("D2"."Retailer_country_key", "D3"."Retailer_country_key")) "Retailer_country_key",
  (COALESCE("D2"."Retailer_key", "D3"."Retailer_key")) "Retailer_key"
FROM
  (SELECT "T0"."C0" "memberUniqueName2",
    "T0"."C1" "memberUniqueName4",
    "T0"."C2" "memberUniqueName6",
    "T0"."C3" "Retailer_country_key",
    "T0"."C4" "Retailer_key",
    "T0"."C5" "Product_type_key",
    MIN("T0"."C6") over (partition BY "T0"."C2") "Order_method_type",
    "T0"."C7" "Quantity",
    "T0"."C8" "rc",
    "T0"."C9" "rc10"
  FROM
    (SELECT "coguda00"."PRODUCT_LINE_CODE" "C0",
      "coguda10"."REGION_CODE" "C1",
      "SLS_ORDER_METHOD_DIM"."ORDER_METHOD_KEY" "C2",
      "coguda10"."COUNTRY_KEY" "C3",
      "coguda11"."RETAILER_KEY" "C4",
      "coguda00"."PRODUCT_TYPE_KEY" "C5",
      MIN("SLS_ORDER_METHOD_DIM"."ORDER_METHOD_EN") "C6",
      SUM("SLS_SALES_FACT"."QUANTITY") "C7",
      MIN("coguda02"."PRODUCT_TYPE_EN") "C8",
      MIN("coguda11"."RETAILER_NAME") "C9"
    FROM "GOSALESDW"."SLS_PRODUCT_DIM" "coguda00",
      "GOSALESDW"."SLS_PRODUCT_LINE_LOOKUP" "coguda01",
      "GOSALESDW"."SLS_PRODUCT_TYPE_LOOKUP" "coguda02",
      "GOSALESDW"."SLS_PRODUCT_LOOKUP" "coguda03",
      "GOSALESDW"."SLS_PRODUCT_COLOR_LOOKUP" "coguda04",
      "GOSALESDW"."SLS_PRODUCT_SIZE_LOOKUP" "coguda05",
      "GOSALESDW"."SLS_PRODUCT_BRAND_LOOKUP" "coguda06",
      "GOSALESDW"."GO_REGION_DIM" "coguda10",
      "GOSALESDW"."SLS_RTL_DIM" "coguda11",
      "GOSALESDW"."SLS_ORDER_METHOD_DIM" "SLS_ORDER_METHOD_DIM",
      "GOSALESDW"."SLS_SALES_FACT" "SLS_SALES_FACT"
    WHERE "coguda00"."PRODUCT_KEY"         ="SLS_SALES_FACT"."PRODUCT_KEY"
    AND "SLS_SALES_FACT"."ORDER_METHOD_KEY"="SLS_ORDER_METHOD_DIM"."ORDER_METHOD_KEY"
    AND "coguda11"."RETAILER_SITE_KEY"     ="SLS_SALES_FACT"."RETAILER_SITE_KEY"
    AND "coguda10"."COUNTRY_CODE"          ="coguda11"."RTL_COUNTRY_CODE"
    AND "coguda00"."PRODUCT_LINE_CODE"     ="coguda01"."PRODUCT_LINE_CODE"
    AND "coguda00"."PRODUCT_NUMBER"        ="coguda03"."PRODUCT_NUMBER"
    AND "coguda00"."PRODUCT_SIZE_CODE"     ="coguda05"."PRODUCT_SIZE_CODE"
    AND "coguda00"."PRODUCT_TYPE_CODE"     ="coguda02"."PRODUCT_TYPE_CODE"
    AND "coguda00"."PRODUCT_COLOR_CODE"    ="coguda04"."PRODUCT_COLOR_CODE"
    AND "coguda06"."PRODUCT_BRAND_CODE"    ="coguda00"."PRODUCT_BRAND_CODE"
    AND "coguda03"."PRODUCT_LANGUAGE"      =N'EN'
    GROUP BY "coguda00"."PRODUCT_LINE_CODE",
      "coguda10"."REGION_CODE",
      "SLS_ORDER_METHOD_DIM"."ORDER_METHOD_KEY",
      "coguda00"."PRODUCT_TYPE_KEY",
      "coguda10"."COUNTRY_KEY",
      "coguda11"."RETAILER_KEY"
    ) "T0"
  ) "D2"
FULL OUTER JOIN
  (SELECT "T0"."C0" "memberUniqueName2",
    "T0"."C1" "memberUniqueName4",
    "T0"."C2" "Retailer_country_key",
    "T0"."C3" "Retailer_key",
    "T0"."C4" "Product_type_key",
    "T0"."C5" "Sales_target",
    "T0"."C6" "rc",
    "T0"."C7" "rc8"
  FROM
    (SELECT "Product"."Product_line_code" "C0",
      "Retailer_site"."Region_code" "C1",
      "Retailer_site"."Retailer_country_key" "C2",
      "Retailer_site"."Retailer_key" "C3",
      "Product"."Product_type_key" "C4",
      SUM("SLS_SALES_TARGET_FACT"."SALES_TARGET") "C5",
      MIN("Product"."Product_type") "C6",
      MIN("Retailer_site"."Retailer_name") "C7"
    FROM
      (SELECT "SLS_PRODUCT_DIM"."PRODUCT_LINE_CODE" "Product_line_code",
        "SLS_PRODUCT_DIM"."PRODUCT_TYPE_KEY" "Product_type_key",
        MIN("SLS_PRODUCT_TYPE_LOOKUP"."PRODUCT_TYPE_EN") "Product_type"
      FROM "GOSALESDW"."SLS_PRODUCT_DIM" "SLS_PRODUCT_DIM",
        "GOSALESDW"."SLS_PRODUCT_TYPE_LOOKUP" "SLS_PRODUCT_TYPE_LOOKUP"
      WHERE "SLS_PRODUCT_DIM"."PRODUCT_TYPE_CODE"="SLS_PRODUCT_TYPE_LOOKUP"."PRODUCT_TYPE_CODE"
      GROUP BY "SLS_PRODUCT_DIM"."PRODUCT_LINE_CODE",
        "SLS_PRODUCT_DIM"."PRODUCT_TYPE_KEY"
      ) "Product",
      (SELECT "Retailer_region_dimension"."REGION_CODE" "Region_code",
        "Retailer_region_dimension"."COUNTRY_KEY" "Retailer_country_key",
        "SLS_RETAILER_DIM"."RETAILER_KEY" "Retailer_key",
        MIN("SLS_RETAILER_DIM"."RETAILER_NAME") "Retailer_name"
      FROM "GOSALESDW"."GO_REGION_DIM" "Retailer_region_dimension",
        "GOSALESDW"."SLS_RTL_DIM" "SLS_RETAILER_DIM"
      WHERE "Retailer_region_dimension"."COUNTRY_CODE"="SLS_RETAILER_DIM"."RTL_COUNTRY_CODE"
      GROUP BY "Retailer_region_dimension"."REGION_CODE",
        "Retailer_region_dimension"."COUNTRY_KEY",
        "SLS_RETAILER_DIM"."RETAILER_KEY"
      ) "Retailer_site",
      "GOSALESDW"."SLS_SALES_TARG_FACT" "SLS_SALES_TARGET_FACT"
    WHERE "Product"."Product_type_key"           ="SLS_SALES_TARGET_FACT"."PRODUCT_TYPE_KEY"
    AND "SLS_SALES_TARGET_FACT"."RETAILER_KEY"   ="Retailer_site"."Retailer_key"
    AND "SLS_SALES_TARGET_FACT"."RTL_COUNTRY_KEY"="Retailer_site"."Retailer_country_key"
    GROUP BY "Product"."Product_line_code",
      "Retailer_site"."Region_code",
      "Product"."Product_type_key",
      "Retailer_site"."Retailer_country_key",
      "Retailer_site"."Retailer_key"
    ) "T0"
  ) "D3"
ON "D2"."memberUniqueName2"    ="D3"."memberUniqueName2"
AND "D2"."memberUniqueName4"   ="D3"."memberUniqueName4"
AND "D2"."Retailer_country_key"="D3"."Retailer_country_key"
AND "D2"."Retailer_key"        ="D3"."Retailer_key"
AND "D2"."Product_type_key"    ="D3"."Product_type_key"