SQL Tuning and optimization- Help Needed [message #438995] |
Wed, 13 January 2010 09:26 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
benjamin.tl
Messages: 12 Registered: January 2010
|
Junior Member |
|
|
Hi Guys I need a help from you....
I need to reduce the time of executing an insert statement which is in the below format.
******************************************************
insert into temp_customer NOLOGGING ( A,B, C, D, E, F )
select a.cust_code
, customer_hierarchy.cust_code
, customer_hierarchy.cust_market
, customer_hierarchy.cust_zone
, customer_hierarchy.cust_region
, customer_hierarchy.cust_area
from customer_os a,
(select mo1.cust_code
, mo1.LEVEL_1_CODE
, mo1.LEVEL_2_CODE
, mo1.LEVEL_3_CODE
, mo1.LEVEL_4_CODE
from marketing_os mo1
where mo1.LEVEL_1_CODE<>' '
and mo1.LEVEL_2_CODE<>' '
and mo1.LEVEL_3_CODE<>' '
and mo1.LEVEL_4_CODE<>' '
) dealer_hierarchy
******************************************************
Note:
*temp_customer is a table.
*customer_os is a synonym
*customer_hierarchy is a result of a select query which gets data from another synonym(marketing_os).
Please help me guys...I am new to this SQL Tuning and optimization.
******************************************************
|
|
|
|
Re: SQL Tuning and optimization- Help Needed [message #439007 is a reply to message #438995] |
Wed, 13 January 2010 10:32 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Are you sure you've got that query right?
1) There is no join condition between the two tables in the SELECT
2) It selects values from Marketing_OS, but never uses them.
The effect of this is to use MARKETING_OS as a row generator - ie you will get n identical records for each row in CUSTOMER_OS where n is the number of rows returned by the DEALER_HIERARCHY sub select.
To broaden the question - why are you inserting into a temporary table? It's not generally neccessary in Oracle, and in the cases where it is, you'd probably be much better off using a proper Temporary Table
|
|
|
Re: SQL Tuning and optimization- Help Needed [message #439099 is a reply to message #439007] |
Thu, 14 January 2010 04:27 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
benjamin.tl
Messages: 12 Registered: January 2010
|
Junior Member |
|
|
@JRowbottom
Sorry there has been small mistake in the query I gave...it is customer_hierarchy not dealer_hierarchy(in the last line)
the logic is to insert the data selected by the "select statement" into the table temp_customer.
The "select statement" fetches data from two different synonyms "customer_os" and "marketing_os" which has an alias name as "a" and "customer_hierarchy(not dealer_hierarchy)" respectively....since I am new to this forum I dont know how to correct the error in the query which I gave.
This is the correct one:
insert into temp_customer NOLOGGING ( A,B, C, D, E, F )
select a.cust_code
, customer_hierarchy.cust_code
, customer_hierarchy.cust_market
, customer_hierarchy.cust_zone
, customer_hierarchy.cust_region
, customer_hierarchy.cust_area
from customer_os a,
(select mo1.cust_code
, mo1.LEVEL_1_CODE
, mo1.LEVEL_2_CODE
, mo1.LEVEL_3_CODE
, mo1.LEVEL_4_CODE
from marketing_os mo1
where mo1.LEVEL_1_CODE<>' '
and mo1.LEVEL_2_CODE<>' '
and mo1.LEVEL_3_CODE<>' '
and mo1.LEVEL_4_CODE<>' '
) customer_hierarchy
I am new to this forum and I don't have much knowledge in tuning and optimization. But I am in a big trouble in finding a solution since the time frame allocated to me is less.
|
|
|
Re: SQL Tuning and optimization- Help Needed [message #439103 is a reply to message #438995] |
Thu, 14 January 2010 04:49 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Nope that's still not correct. There's no column called cust_market in the inline view for starters.
It would a lot easier if you just copied and pasted the original query.
Once you've posted the correct insert statement, read the post Blackswan linked to and post the requested information - the explain plan and a list of indexes on the tables would be a good start.
|
|
|
|