| 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 #641715 is a reply to message #641714] |
Thu, 20 August 2015 09:47  |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
|
Actually before you do any of that (though you do still need to post the requested info) you need to post the SQL you're actually running. What you've posted has multiple invalid table aliases and won't compile.
|
|
|
|
|
|
| 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
|
|
|
|
|
|
| Re: Insert from Select - Performance Issue [message #646628 is a reply to message #641845] |
Tue, 05 January 2016 23:36 |
 |
garan
Messages: 27
Registered: January 2016
|
Junior Member |
|
|
Yes you can use APPEND HINT
Ex inserting 2 million rows using the above hint is fast and generates less redo when compared to INSERT without the append hint
CREATE TABLE EMP(ID NUMBER);
SET AUTOTRACE ON
INSERT /*+ append */INTO EMP(ID)
SELECT LEVEL
FROM DUAL WHERE LEVEL <=2000000
;
Statistics
----------------------------------------------------------
0 recursive calls
3189 db block gets
0 consistent gets
0 physical reads
7184 redo size
657 bytes sent via SQL*Net to client
612 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
2000000 rows processed
garani
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
