Home » RDBMS Server » Performance Tuning » sql query too slow -maybe needs indexes?
sql query too slow -maybe needs indexes? [message #236891] |
Fri, 11 May 2007 03:53 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
eb222
Messages: 7 Registered: May 2007
|
Junior Member |
|
|
Hi - can anyone help me with below query as it takes all day to run -any advise will be appreciated.
thanks
SELECT "Facts_Receivables_AsOfMonth"."INVOICE_CURRENCY_CODE" "C0",
"T2"."Customer_Nr_And_Name" "C1",
"T2"."Bill_To_Site_Nr_And_Name_" "C2",
"Facts_Receivables_AsOfMonth"."INVOICE_DATE" "C3",
"Facts_Receivables_AsOfMonth"."DUE_DATE" "C4",
"Facts_Receivables_AsOfMonth"."INVOICE_NUMBER" "C5",
SUM ("Facts_Receivables_AsOfMonth"."AMOUNT_DUE_ORIGINAL_IN_EUR") OVER (PARTITION BY "Facts_Receivables_AsOfMonth"."INVOICE_CURRENCY_CODE", "T2"."Customer_Nr_And_Name", "T2"."Bill_To_Site_Nr_And_Name_", "Facts_Receivables_AsOfMonth"."INVOICE_DATE", "Facts_Receivables_AsOfMonth"."DUE_DATE", "Facts_Receivables_AsOfMonth"."INVOICE_NUMBER")
"C6",
SUM
(CASE
WHEN "Facts_Receivables_AsOfMonth"."INVOICE_CURRENCY_CODE" =
'EUR'
THEN NULL
ELSE "Facts_Receivables_AsOfMonth"."AR_BALANCE"
END
) OVER (PARTITION BY "Facts_Receivables_AsOfMonth"."INVOICE_CURRENCY_CODE", "T2"."Customer_Nr_And_Name", "T2"."Bill_To_Site_Nr_And_Name_", "Facts_Receivables_AsOfMonth"."INVOICE_DATE", "Facts_Receivables_AsOfMonth"."DUE_DATE", "Facts_Receivables_AsOfMonth"."INVOICE_NUMBER")
"C7",
SUM
(CASE
WHEN "Facts_Receivables_AsOfMonth"."AR_BALANCE" > 0
THEN "Facts_Receivables_AsOfMonth"."AR_BALANCE"
ELSE NULL
END
) OVER (PARTITION BY "Facts_Receivables_AsOfMonth"."INVOICE_CURRENCY_CODE", "T2"."Customer_Nr_And_Name", "T2"."Bill_To_Site_Nr_And_Name_", "Facts_Receivables_AsOfMonth"."INVOICE_DATE", "Facts_Receivables_AsOfMonth"."DUE_DATE", "Facts_Receivables_AsOfMonth"."INVOICE_NUMBER")
"C8",
SUM
(CASE
WHEN "Facts_Receivables_AsOfMonth"."AR_BALANCE" < 0
THEN "Facts_Receivables_AsOfMonth"."AR_BALANCE"
ELSE NULL
END
) OVER (PARTITION BY "Facts_Receivables_AsOfMonth"."INVOICE_CURRENCY_CODE", "T2"."Customer_Nr_And_Name", "T2"."Bill_To_Site_Nr_And_Name_", "Facts_Receivables_AsOfMonth"."INVOICE_DATE", "Facts_Receivables_AsOfMonth"."DUE_DATE", "Facts_Receivables_AsOfMonth"."INVOICE_NUMBER")
"C9",
SUM ("Facts_Receivables_AsOfMonth"."AR_BALANCE") OVER (PARTITION BY "Facts_Receivables_AsOfMonth"."INVOICE_CURRENCY_CODE", "T2"."Customer_Nr_And_Name", "T2"."Bill_To_Site_Nr_And_Name_", "Facts_Receivables_AsOfMonth"."INVOICE_DATE", "Facts_Receivables_AsOfMonth"."DUE_DATE", "Facts_Receivables_AsOfMonth"."INVOICE_NUMBER")
"C10"
FROM (SELECT "F_AR_BALANCE_ASOFMONTH"."INVOICE_NUMBER" "INVOICE_NUMBER",
"F_AR_BALANCE_ASOFMONTH"."INVOICE_CURRENCY_CODE"
"INVOICE_CURRENCY_CODE",
"F_AR_BALANCE_ASOFMONTH"."INVOICE_DATE" "INVOICE_DATE",
"F_AR_BALANCE_ASOFMONTH"."DUE_DATE" "DUE_DATE",
"F_AR_BALANCE_ASOFMONTH"."AMOUNT_DUE_ORIGINAL_IN_EUR"
"AMOUNT_DUE_ORIGINAL_IN_EUR",
"F_AR_BALANCE_ASOFMONTH"."AR_BALANCE" "AR_BALANCE",
"F_AR_BALANCE_ASOFMONTH"."SOURCE_OPER_UNIT_ID"
"SOURCE_OPER_UNIT_ID",
"F_AR_BALANCE_ASOFMONTH"."SOURCE_BILL_TO_SITE_USE_ID"
"SOURCE_BILL_TO_SITE_USE_ID"
FROM (SELECT *
FROM "DWADMIN"."F_AR_BALANCE_ASOFMONTH"
WHERE "ASOFMONTH" = '200704') "F_AR_BALANCE_ASOFMONTH"
WHERE "F_AR_BALANCE_ASOFMONTH"."SOURCE_OPER_UNIT_ID" IN
('EBS87', 'EBS88', 'EBS89')) "Facts_Receivables_AsOfMonth",
(SELECT "D_CUST_BILL_TO_ALL"."SOURCE_BILL_TO_SITE_USE_ID"
"SOURCE_BILL_TO_SITE_USE_ID1",
CASE
WHEN (CASE
WHEN ("D_CUST_BILL_TO_ALL"."BILL_TO_ACCOUNT_NUMBER" IS NULL
)
OR (' ' IS NULL)
THEN NULL
ELSE ( "D_CUST_BILL_TO_ALL"."BILL_TO_ACCOUNT_NUMBER"
|| ' '
)
END IS NULL
)
OR ("D_CUST_BILL_TO_ALL"."BILL_TO_PARTY_NAME" IS NULL)
THEN NULL
ELSE ( CASE
WHEN ("D_CUST_BILL_TO_ALL"."BILL_TO_ACCOUNT_NUMBER" IS NULL
)
OR (' ' IS NULL)
THEN NULL
ELSE ( "D_CUST_BILL_TO_ALL"."BILL_TO_ACCOUNT_NUMBER"
|| ' '
)
END
|| "D_CUST_BILL_TO_ALL"."BILL_TO_PARTY_NAME"
)
END "Customer_Nr_And_Name",
CASE
WHEN (CASE
WHEN ("D_CUST_BILL_TO_ALL"."BILL_TO_PARTY_SITE_NUMBER" IS NULL
)
OR (' ' IS NULL)
THEN NULL
ELSE ( "D_CUST_BILL_TO_ALL"."BILL_TO_PARTY_SITE_NUMBER"
|| ' '
)
END IS NULL
)
OR ("D_CUST_BILL_TO_ALL"."BILL_TO_SITE_NAME" IS NULL)
THEN NULL
ELSE ( CASE
WHEN ("D_CUST_BILL_TO_ALL"."BILL_TO_PARTY_SITE_NUMBER" IS NULL
)
OR (' ' IS NULL)
THEN NULL
ELSE ( "D_CUST_BILL_TO_ALL"."BILL_TO_PARTY_SITE_NUMBER"
|| ' '
)
END
|| "D_CUST_BILL_TO_ALL"."BILL_TO_SITE_NAME"
)
END "Bill_To_Site_Nr_And_Name_",
"D_CUST_BILL_TO_ALL"."SOURCE_BILL_TO_OU_ID"
"SOURCE_BILL_TO_OU_ID"
FROM (SELECT "D_CUST_BILL_TO_ALL"."SOURCE_BILL_TO_SITE_USE_ID"
"SOURCE_BILL_TO_SITE_USE_ID",
"D_CUST_BILL_TO_ALL"."SOURCE_BILL_TO_OU_ID"
"SOURCE_BILL_TO_OU_ID",
"D_CUST_BILL_TO_ALL"."BILL_TO_PARTY_NAME"
"BILL_TO_PARTY_NAME",
"D_CUST_BILL_TO_ALL"."BILL_TO_PARTY_SITE_NUMBER"
"BILL_TO_PARTY_SITE_NUMBER",
"D_CUST_BILL_TO_ALL"."BILL_TO_ACCOUNT_NUMBER"
"BILL_TO_ACCOUNT_NUMBER",
"D_CUST_BILL_TO_ALL"."BILL_TO_SITE_NAME"
"BILL_TO_SITE_NAME"
FROM "DWADMIN"."D_CUST_BILL_TO_ALL" "D_CUST_BILL_TO_ALL"
WHERE "D_CUST_BILL_TO_ALL"."CURRENT_FLAG" = 'Y') "D_CUST_BILL_TO_ALL"
WHERE "D_CUST_BILL_TO_ALL"."SOURCE_BILL_TO_OU_ID" IN
('EBS87', 'EBS88', 'EBS89')) "T2"
WHERE "Facts_Receivables_AsOfMonth"."SOURCE_OPER_UNIT_ID" = 'EBS88'
AND "T2"."SOURCE_BILL_TO_SITE_USE_ID1" =
"Facts_Receivables_AsOfMonth"."SOURCE_BILL_TO_SITE_USE_ID"
|
|
|
|
|
|
|
Goto Forum:
Current Time: Wed Aug 21 06:13:54 CDT 2024
|