Home » RDBMS Server » Performance Tuning » Insert from Select - Performance Issue (Oracle 11 g)
 Insert from Select - Performance Issue [message #641711] |
Thu, 20 August 2015 09:22  |
ram50958
Messages: 7
Registered: September 2010
|
Junior Member |
|
|
How can I rewrite the below query to make it faster? I have indexes on all the columns being used in where clause. Both the tables being used have millions of records.
INSERT INTO au_comparison_goc(src_au_src_name, src_seq_id, trgt_au_src_name, trgt_seq_id, active_goc)
SELECT DISTINCT src.au_source, src.seq_id, trgt.au_source, trgt.seq_id, 'A'
FROM dsmt_assmt_au_mv src,
dsmt_assmt_au_mv trgt,
au_distinct_goc src_goc ,
au_distinct_goc trgt_goc
WHERE src.seq_id = src_goc.seq_id
AND trgt.seq_id = trgt_goc.seq_id
AND trgt.seq_id != src.seq_id
AND src.au_source = au_src.au_source
AND trgt.au_source = trgt_src.au_source
AND src_goc.goc = trgt_goc.goc
AND src_goc.src_eff_status = 'A'
AND trgt_goc.src_eff_status = 'A';
*BlackSwan added {code} tags. Please do so yourself in the future.
How to use {code} tags and make your code easier to read
[Updated on: Thu, 20 August 2015 09:26] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Insert from Select - Performance Issue [message #641811 is a reply to message #641716] |
Mon, 24 August 2015 21:22  |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
Please post a valid piece of sql, preferrably the sql that actually has the problem (OH wait, I see CookieMonster has already asked for that, good job).
Once you post the real thing we will give you more help.
In the mean time, consider the following:
There are only two kinds of queries, WAREHOUSE STYLE and PRECISION STYLE. A warehouse style query wants more than 2% of the rows in your tables, a precision style query wants less than 2% of the rows in your tables.
Given the (admittedly a little controversial yet overall very accurate) statement above, which is your query? Do you want lots of data (>2% of rows) or do you want a small amount of data (< 2% of rows (ideally lots less))? If your query is a warehouse style query then your indexes will be mostly useless and you will be using HASH JOIN to put tables together. If your query is a precision style query then your indexes may be crucial to success along with using NESTED LOOPS JOIN to put tables together.
OK so there is always a little wiggle room and I have seen my fair share of queries that ignore the information above. Yet in the grand scheme of things this basic rule is golden. So you have to tell us (in addition to providing the real query) what is the data load in terms of percentages of rows from each table. Once you know this, you will have taken the first major step towards tuning your query. For example, you will be able to tell where you are using indexes and should not be or vice versa. The same with joins. You will be able to tell if you are using NESTED LOOPS JOIN and instead should be using HASH JOIN. Once you know this you can then decide if your problem is the need to change basic access methods and join strategies, or if you need to tweak the ones you already have to make them more efficient.
So get us these things:
1. the real query
2. number of rows in each table
3. number of rows after applying filter criteria for each table in the WHERE clause
4. an actual query plan that contains at a minimum, PLAN_TABLE_OUTPUT and PREDICATE_INFORMATION sections
** If you have the luxury of being able to run the query then use GATHER_PLAN_STATISTICS hint and generate the plan from that. It will contain the above info.
Kevin
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sun May 04 02:29:30 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
