Hi MaryAnn/All,
This is the query plan for the query posted in the below mail.
CLIENT 1-CHUNK PARALLEL 1-WAY RANGE SCAN OVER _IDX_Shipterms [-32768]
SERVER FILTER BY FIRST KEY ONLY
PARALLEL INNER-JOIN TABLE 0
CLIENT 1-CHUNK PARALLEL 1-WAY RANGE SCAN OVER _IDX_Payterms [-32768]
SERVER FILTER BY FIRST KEY ONLY
PARALLEL INNER-JOIN TABLE 0
CLIENT 1-CHUNK PARALLEL 1-WAY RANGE SCAN OVER _IDX_Order_Details
[-32768]
PARALLEL INNER-JOIN TABLE 0
CLIENT 1-CHUNK PARALLEL 1-WAY RANGE SCAN OVER _IDX_Orders
[-32768]
PARALLEL INNER-JOIN TABLE 0
CLIENT 1-CHUNK PARALLEL 1-WAY RANGE SCAN OVER _IDX_Address
[-32768]
PARALLEL INNER-JOIN TABLE 0
CLIENT 1-CHUNK PARALLEL 1-WAY RANGE SCAN OVER
_IDX_Customers [-32768,'45954']
SERVER FILTER BY FIRST KEY ONLY
DYNAMIC SERVER FILTER BY Payt_Id IN (O.Orders_Family:O_Pay_Terms)
DYNAMIC SERVER FILTER BY Shipt_Id IN (O.Orders_Family:O_Ship_Terms)
Thanks,
Siddharth Ubale
From: Maryann Xue [mailto:[email protected]]
Sent: Tuesday, December 16, 2014 9:07 PM
To: [email protected]
Subject: Re: FW: Join Queries
Hi Siddharth,
Could you please run "explain <your_query>" and post the query plan you got?
Thanks,
Maryann
On Tue, Dec 16, 2014 at 9:31 AM, Siddharth Ubale
<[email protected]<mailto:[email protected]>> wrote:
Just a correction;
Order_details :400000
Orders :200000
Rest of the tables 100000
Thanks,
Siddharth
From: Siddharth Ubale
Sent: Tuesday, December 16, 2014 8:01 PM
To: '[email protected]<mailto:[email protected]>'
Subject: Join Queries
Hi ,
Can somebody help me to optimize this query :
select /*+ NO_STAR_JOIN */ * from "Address" as a
inner join "Customers" as c on a."A_C_Id"=c."C_Id"
inner join "Orders" as o on o."O_C_Id"=a."A_C_Id"
inner join "Order_Details" as od on od."O_Id"=o."O_Id"
inner join "Payterms" as p on p."Payt_Id" =o."O_Pay_Terms"
inner join "Shipterms" as s on s."Shipt_Id" =o."O_Ship_Terms"
where c."C_Id" = '45954' ;
we are getting response time of 3sec on this query …
How do we possibly get it down to 500mS.
Each table contains 100000 rows other than Order_details which contains 400000
rows.
Thanks,
Siddharth Ubale,
Synchronized Communications
#43, Velankani Tech Park, Block No. II,
3rd Floor, Electronic City Phase I,
Bangalore – 560 100
Tel : +91 80 3202 4060<tel:%2B91%2080%203202%204060>
Web: www.syncoms.com<http://www.syncoms.com/>
[LogoNEWmohLARGE]
London|Bangalore|Orlando
we innovate, plan, execute, and transform the business
--
Thanks,
Maryann
