title: Oracle Query Optimization containing many partition clause
body: <p>This is the oracle 11g query I am running.
  The table contains a total of 180 Million Records and 400 Columns.
  Can you suggest some tuning techniques to make this query faster.
  Last time this query without the 'direct' hint option, executed for 2+ hours and went on. I am running now with 'direct' hint option.</p>
  
  <pre><code>CREATE /*+ direct */ GLOBAL  TEMPORARY TABLE MAXMINGRP_TEMP
         ON COMMIT PRESERVE ROWS
         AS ( SELECT ORIG_QPL,OM_SRC_SYS_KY,SO_ID,SRC_SYS_KY,ORDER_DATE,BUS_AREA_CD,SO_LN_ITM_ID,TRIM(PRODUCT_BASE_NUMBER)||TRIM(PRODUCT_OPTION_NUMBER) PROD_ID,FDW_TRAN_TYPE_CD,SO_DTL_STAT_CD,CONTRA_FG,
      MAX(ORIGINAL_CURRENCY_CODE) OVER(PARTITION BY ORIG_QPL,OM_SRC_SYS_KY,SO_ID,SRC_SYS_KY,ORDER_DATE,BUS_AREA_CD,SO_LN_ITM_ID,TRIM(PRODUCT_BASE_NUMBER)||TRIM(PRODUCT_OPTION_NUMBER),FDW_TRAN_TYPE_CD,SO_DTL_STAT_CD,CONTRA_FG) MAX_ORIGINAL_CURRENCY_CODE,
      MIN(ORIGINAL_CURRENCY_CODE) OVER(PARTITION BY ORIG_QPL,OM_SRC_SYS_KY,SO_ID,SRC_SYS_KY,ORDER_DATE,BUS_AREA_CD,SO_LN_ITM_ID,TRIM(PRODUCT_BASE_NUMBER)||TRIM(PRODUCT_OPTION_NUMBER),FDW_TRAN_TYPE_CD,SO_DTL_STAT_CD,CONTRA_FG) MIN_ORIGINAL_CURRENCY_CODE,
      MAX(GL_GRP_ACCT_ID) OVER(PARTITION BY ORIG_QPL,OM_SRC_SYS_KY,SO_ID,SRC_SYS_KY,ORDER_DATE,BUS_AREA_CD,SO_LN_ITM_ID,TRIM(PRODUCT_BASE_NUMBER)||TRIM(PRODUCT_OPTION_NUMBER),FDW_TRAN_TYPE_CD,SO_DTL_STAT_CD,CONTRA_FG) MAX_GL_GRP_ACCT_ID,
      MIN(GL_GRP_ACCT_ID) OVER(PARTITION BY ORIG_QPL,OM_SRC_SYS_KY,SO_ID,SRC_SYS_KY,ORDER_DATE,BUS_AREA_CD,SO_LN_ITM_ID,TRIM(PRODUCT_BASE_NUMBER)||TRIM(PRODUCT_OPTION_NUMBER),FDW_TRAN_TYPE_CD,SO_DTL_STAT_CD,CONTRA_FG) MIN_GL_GRP_ACCT_ID,
      MAX(CUSTOMER_BASE_NUMBER) OVER(PARTITION BY ORIG_QPL,OM_SRC_SYS_KY,SO_ID,SRC_SYS_KY,ORDER_DATE,BUS_AREA_CD,SO_LN_ITM_ID,TRIM(PRODUCT_BASE_NUMBER)||TRIM(PRODUCT_OPTION_NUMBER),FDW_TRAN_TYPE_CD,SO_DTL_STAT_CD,CONTRA_FG) MAX_CUSTOMER_BASE_NUMBER,
      MIN(CUSTOMER_BASE_NUMBER) OVER(PARTITION BY ORIG_QPL,OM_SRC_SYS_KY,SO_ID,SRC_SYS_KY,ORDER_DATE,BUS_AREA_CD,SO_LN_ITM_ID,TRIM(PRODUCT_BASE_NUMBER)||TRIM(PRODUCT_OPTION_NUMBER),FDW_TRAN_TYPE_CD,SO_DTL_STAT_CD,CONTRA_FG) MIN_CUSTOMER_BASE_NUMBER,
      MAX(REC_EXPLN_CD) OVER(PARTITION BY ORIG_QPL,OM_SRC_SYS_KY,SO_ID,SRC_SYS_KY,ORDER_DATE,BUS_AREA_CD,SO_LN_ITM_ID,TRIM(PRODUCT_BASE_NUMBER)||TRIM(PRODUCT_OPTION_NUMBER),FDW_TRAN_TYPE_CD,SO_DTL_STAT_CD,CONTRA_FG) MAX_REC_EXPLN_CD,
      MIN(REC_EXPLN_CD) OVER(PARTITION BY ORIG_QPL,OM_SRC_SYS_KY,SO_ID,SRC_SYS_KY,ORDER_DATE,BUS_AREA_CD,SO_LN_ITM_ID,TRIM(PRODUCT_BASE_NUMBER)||TRIM(PRODUCT_OPTION_NUMBER),FDW_TRAN_TYPE_CD,SO_DTL_STAT_CD,CONTRA_FG) MIN_REC_EXPLN_CD,
      MAX(PRFT_CTR_CD) OVER(PARTITION BY ORIG_QPL,OM_SRC_SYS_KY,SO_ID,SRC_SYS_KY,ORDER_DATE,BUS_AREA_CD,SO_LN_ITM_ID,TRIM(PRODUCT_BASE_NUMBER)||TRIM(PRODUCT_OPTION_NUMBER),FDW_TRAN_TYPE_CD,SO_DTL_STAT_CD,CONTRA_FG) MAX_PRFT_CTR_CD,
      MIN(PRFT_CTR_CD) OVER(PARTITION BY ORIG_QPL,OM_SRC_SYS_KY,SO_ID,SRC_SYS_KY,ORDER_DATE,BUS_AREA_CD,SO_LN_ITM_ID,TRIM(PRODUCT_BASE_NUMBER)||TRIM(PRODUCT_OPTION_NUMBER),FDW_TRAN_TYPE_CD,SO_DTL_STAT_CD,CONTRA_FG) MIN_PRFT_CTR_CD,
      MAX(FUNC_AREA_CD) OVER(PARTITION BY ORIG_QPL,OM_SRC_SYS_KY,SO_ID,SRC_SYS_KY,ORDER_DATE,BUS_AREA_CD,SO_LN_ITM_ID,TRIM(PRODUCT_BASE_NUMBER)||TRIM(PRODUCT_OPTION_NUMBER),FDW_TRAN_TYPE_CD,SO_DTL_STAT_CD,CONTRA_FG) MAX_FUNC_AREA_CD,
      MIN(FUNC_AREA_CD) OVER(PARTITION BY ORIG_QPL,OM_SRC_SYS_KY,SO_ID,SRC_SYS_KY,ORDER_DATE,BUS_AREA_CD,SO_LN_ITM_ID,TRIM(PRODUCT_BASE_NUMBER)||TRIM(PRODUCT_OPTION_NUMBER),FDW_TRAN_TYPE_CD,SO_DTL_STAT_CD,CONTRA_FG) MIN_FUNC_AREA_CD,
      MAX(ORIG_SOLD_TO_SITE_ID) OVER(PARTITION BY ORIG_QPL,OM_SRC_SYS_KY,SO_ID,SRC_SYS_KY,ORDER_DATE,BUS_AREA_CD,SO_LN_ITM_ID,TRIM(PRODUCT_BASE_NUMBER)||TRIM(PRODUCT_OPTION_NUMBER),FDW_TRAN_TYPE_CD,SO_DTL_STAT_CD,CONTRA_FG) MAX_ORIG_SOLD_TO_SITE_ID,
      MIN(ORIG_SOLD_TO_SITE_ID) OVER(PARTITION BY ORIG_QPL,OM_SRC_SYS_KY,SO_ID,SRC_SYS_KY,ORDER_DATE,BUS_AREA_CD,SO_LN_ITM_ID,TRIM(PRODUCT_BASE_NUMBER)||TRIM(PRODUCT_OPTION_NUMBER),FDW_TRAN_TYPE_CD,SO_DTL_STAT_CD,CONTRA_FG) MIN_ORIG_SOLD_TO_SITE_ID,
      MAX(SHIP_TO_SITE_ID) OVER(PARTITION BY ORIG_QPL,OM_SRC_SYS_KY,SO_ID,SRC_SYS_KY,ORDER_DATE,BUS_AREA_CD,SO_LN_ITM_ID,TRIM(PRODUCT_BASE_NUMBER)||TRIM(PRODUCT_OPTION_NUMBER),FDW_TRAN_TYPE_CD,SO_DTL_STAT_CD,CONTRA_FG) MAX_SHIP_TO_SITE_ID,
      MIN(SHIP_TO_SITE_ID) OVER(PARTITION BY ORIG_QPL,OM_SRC_SYS_KY,SO_ID,SRC_SYS_KY,ORDER_DATE,BUS_AREA_CD,SO_LN_ITM_ID,TRIM(PRODUCT_BASE_NUMBER)||TRIM(PRODUCT_OPTION_NUMBER),FDW_TRAN_TYPE_CD,SO_DTL_STAT_CD,CONTRA_FG) MIN_SHIP_TO_SITE_ID,
      MAX(INVOICE_TO_SITE_ID) OVER(PARTITION BY ORIG_QPL,OM_SRC_SYS_KY,SO_ID,SRC_SYS_KY,ORDER_DATE,BUS_AREA_CD,SO_LN_ITM_ID,TRIM(PRODUCT_BASE_NUMBER)||TRIM(PRODUCT_OPTION_NUMBER),FDW_TRAN_TYPE_CD,SO_DTL_STAT_CD,CONTRA_FG) MAX_INVOICE_TO_SITE_ID,
      MIN(INVOICE_TO_SITE_ID) OVER(PARTITION BY ORIG_QPL,OM_SRC_SYS_KY,SO_ID,SRC_SYS_KY,ORDER_DATE,BUS_AREA_CD,SO_LN_ITM_ID,TRIM(PRODUCT_BASE_NUMBER)||TRIM(PRODUCT_OPTION_NUMBER),FDW_TRAN_TYPE_CD,SO_DTL_STAT_CD,CONTRA_FG) MIN_INVOICE_TO_SITE_ID,
      MAX(END_USER_ID) OVER(PARTITION BY ORIG_QPL,OM_SRC_SYS_KY,SO_ID,SRC_SYS_KY,ORDER_DATE,BUS_AREA_CD,SO_LN_ITM_ID,TRIM(PRODUCT_BASE_NUMBER)||TRIM(PRODUCT_OPTION_NUMBER),FDW_TRAN_TYPE_CD,SO_DTL_STAT_CD,CONTRA_FG) MAX_END_USER_ID,
      MIN(END_USER_ID) OVER(PARTITION BY ORIG_QPL,OM_SRC_SYS_KY,SO_ID,SRC_SYS_KY,ORDER_DATE,BUS_AREA_CD,SO_LN_ITM_ID,TRIM(PRODUCT_BASE_NUMBER)||TRIM(PRODUCT_OPTION_NUMBER),FDW_TRAN_TYPE_CD,SO_DTL_STAT_CD,CONTRA_FG) MIN_END_USER_ID,
      MAX(ARM_EDIT_CODE) OVER(PARTITION BY ORIG_QPL,OM_SRC_SYS_KY,SO_ID,SRC_SYS_KY,ORDER_DATE,BUS_AREA_CD,SO_LN_ITM_ID,TRIM(PRODUCT_BASE_NUMBER)||TRIM(PRODUCT_OPTION_NUMBER),FDW_TRAN_TYPE_CD,SO_DTL_STAT_CD,CONTRA_FG) MAX_ARM_EDIT_CODE,
      MIN(ARM_EDIT_CODE) OVER(PARTITION BY ORIG_QPL,OM_SRC_SYS_KY,SO_ID,SRC_SYS_KY,ORDER_DATE,BUS_AREA_CD,SO_LN_ITM_ID,TRIM(PRODUCT_BASE_NUMBER)||TRIM(PRODUCT_OPTION_NUMBER),FDW_TRAN_TYPE_CD,SO_DTL_STAT_CD,CONTRA_FG) MIN_ARM_EDIT_CODE,
      MAX(SAP_ORDER_TYPE) OVER(PARTITION BY ORIG_QPL,OM_SRC_SYS_KY,SO_ID,SRC_SYS_KY,ORDER_DATE,BUS_AREA_CD,SO_LN_ITM_ID,TRIM(PRODUCT_BASE_NUMBER)||TRIM(PRODUCT_OPTION_NUMBER),FDW_TRAN_TYPE_CD,SO_DTL_STAT_CD,CONTRA_FG) MAX_SAP_ORDER_TYPE,
      MIN(SAP_ORDER_TYPE) OVER(PARTITION BY ORIG_QPL,OM_SRC_SYS_KY,SO_ID,SRC_SYS_KY,ORDER_DATE,BUS_AREA_CD,SO_LN_ITM_ID,TRIM(PRODUCT_BASE_NUMBER)||TRIM(PRODUCT_OPTION_NUMBER),FDW_TRAN_TYPE_CD,SO_DTL_STAT_CD,CONTRA_FG) MIN_SAP_ORDER_TYPE,
      MAX(SO_ADJ_CD) OVER(PARTITION BY ORIG_QPL,OM_SRC_SYS_KY,SO_ID,SRC_SYS_KY,ORDER_DATE,BUS_AREA_CD,SO_LN_ITM_ID,TRIM(PRODUCT_BASE_NUMBER)||TRIM(PRODUCT_OPTION_NUMBER),FDW_TRAN_TYPE_CD,SO_DTL_STAT_CD,CONTRA_FG) MAX_SO_ADJ_CD,
      MIN(SO_ADJ_CD) OVER(PARTITION BY ORIG_QPL,OM_SRC_SYS_KY,SO_ID,SRC_SYS_KY,ORDER_DATE,BUS_AREA_CD,SO_LN_ITM_ID,TRIM(PRODUCT_BASE_NUMBER)||TRIM(PRODUCT_OPTION_NUMBER),FDW_TRAN_TYPE_CD,SO_DTL_STAT_CD,CONTRA_FG) MIN_SO_ADJ_CD
      FROM ord_dtl );
  </code></pre>