Home » SQL & PL/SQL » SQL & PL/SQL » SQL Query performance (Oracle 9i)
SQL Query performance [message #418324] Fri, 14 August 2009 03:25 Go to next message
discoverer
Messages: 30
Registered: January 2007
Member
Hi All,

I am working on one oracle applications report.
This report should pick details of transactions made by the users. Hence I am using MTL_MATERIAL_TRANSACTIONS table which is having huge amount of data (14 million records). Also I have to use created_by column of this table to identify who has made that aparticular transaction.This column is not indexed. Here is the query I am using :

SELECT FU.user_name "User_ID",
REPLACE(FU.DESCRIPTION,',',' ') "User_Name",
OAP.period_name "Accounting_Period",
SUBSTR(mtlp.ORGANIZATION_CODE,1,2) ORG,
MSI.segment1 Item,
REPLACE(MSI.description,',',' ') "Item Description",
TO_DATE(MMT.transaction_date,'DD-MON-YYYY') "Transaction_Date",
MMT.subinventory_code "Sub_inventory",
MMT.transaction_reference "Transaction_Reference",
MMT.transaction_source_name "Transaction_Source",
MTT.transaction_type_name "Transaction_Type",
--TST.transaction_source_type_name "Transaction_Source_Type",
MTR.reason_name reason_name,
MMT.transaction_uom UOM,
MMT.transaction_quantity "Transaction_QTY",
MMT.actual_cost TPC,
round((MMT.actual_cost * MMT.transaction_quantity),2) "Transaction_Value"
FROM mtl_material_transactions MMT,
mtl_system_items_b MSI,
fnd_user FU,
mtl_parameters mtlp,
org_acct_periods OAP,
mtl_transaction_types MTT,
mtl_transaction_reasons MTR
WHERE 1 = 1
AND MMT.created_by = FU.user_id
AND MMT.inventory_item_id = MSI.inventory_item_id
AND MMT.reason_id = MTR.reason_id (+)
AND MMT.organization_id = MSI.organization_id
AND MSI.organization_id = mtlp.organization_id
AND MMT.acct_period_id = OAP.acct_period_id
AND MMT.transaction_type_id = MTT.transaction_type_id
AND MMT.created_by = :P_USER
AND MMT.transaction_date BETWEEN :P_FROM_DATE AND :P_TO_DATE


I would appreciate if any one could give me a valuable suggestion on how to make this query run faster.Right now its taking more than one hour to complete.



Thanks and regards ,
Pavan
Re: SQL Query performance [message #418326 is a reply to message #418324] Fri, 14 August 2009 03:30 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Index Created_By and Transaction_Date columns.
Re: SQL Query performance [message #418418 is a reply to message #418326] Fri, 14 August 2009 22:56 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Also fix your outerjoin. It is wrong; at least it looks wrong to me.

Common errors seen when using OUTER-JOIN

Kevin
Re: SQL Query performance [message #418445 is a reply to message #418418] Sat, 15 August 2009 08:03 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Kevin, you're killing me. I'm staring at this thing and I can't see the problem with the outer join. MTR is the table being joined in the outer join, but there are no other predicates that either filter MTR or on-join to another table.

I'm stumped.

To the OP:
- paste your explain plan,
- the number of rows in each table,
- the number of rows in MMT matching the CREATED_BY and TRANSACTION_DATE predicates,
- the number of rows returned by the query.

Ross Leishman
Re: SQL Query performance [message #419639 is a reply to message #418324] Mon, 24 August 2009 11:43 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
My biggest appologies to you Ross.

I WAS MISTAKEN. There is no error here. You are correct there is no chain of joins where an outer is missing.

I AM SORRY BUDDY. I was too quick on the submit button.

Kevin
Re: SQL Query performance [message #419738 is a reply to message #418324] Tue, 25 August 2009 07:15 Go to previous message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
Some hints:

    Always use the magic "code" button when inserting a code fragment.


    Use trace and check what is the bottleneck


    Create an index - it (almost) always can increase access time

Previous Topic: duplicate problem fix
Next Topic: How to create procedure
Goto Forum:
  


Current Time: Sat Dec 10 07:06:47 CST 2016

Total time taken to generate the page: 0.13632 seconds