| SQL Tuning and optimization- Help Needed [message #438995] |
Wed, 13 January 2010 09:26  |
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  |
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 |
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 |
cookiemonster
Messages: 13922
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
