| performance [message #375150] |
Tue, 31 July 2001 11:29  |
Kaiser
Messages: 2
Registered: July 2001
|
Junior Member |
|
|
I have two large select statements in a insert statement (Select 1 UNION ALL Select 2) and they have outer joins in their where clauses. (can't remove them). First I tried to write functions and remove outer joins but got poor performance, then use "USE_MERGE", "MERGE_JOIN" hints, but it didn't make any difference in performance. I'm stuck!! anybody can help me??
Insert into TEMP_TABLE
Select (40 columns) from TABLE a, table b, table c, table d
....
where b.field1 (+) = a.field1
And c.field2 (+) = a.field2.
Here table a is the largest table others have at most 20 rows.
Thanks in advance...
|
|
|
|
| Re: performance [message #375151 is a reply to message #375150] |
Tue, 31 July 2001 12:49  |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
I'd try to determine if the insert or the select is causing the problem.
in sqlplus, "set timing on, set autotrace on"
select * from
(Select 1 UNION ALL Select 2)
where rownum <=1;
Also consider the overhead of the indexes on the table being inserted into (and any triggers you may have). An old rule of thumb (probably not accurate) says that if an insert into an un-indexed table has a cost of 1, then each index on the table adds a cost of 2-3.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
