| How to avoid union? [message #361018] |
Mon, 24 November 2008 15:39  |
newToPlsql
Messages: 4
Registered: October 2008
|
Junior Member |
|
|
Hi All
i have a script which has almost 12 different such selects,and gets inserted into a table.The issue is the INDEX defined on the columns in select staments are not getting used because i am using a ditinct(i am not sure of this).Is there any way i can improve the perfomance of this script?
Will spliting these script into individual 12 diffrent insert statment yield a better perfomance?
INSERT /*+append*/ INTO table (
name,
number,
columnname
)
(
SELECT
DISTINCT t.Proname,
t.ProNumber
'ProdName',
FROM
AnotherTable t
GROUP BY
t.Proname
)
UNION
(
SELECT
DISTINCT t.Custname,
t.CustNumber
'CustName',
FROM
AnotherTable t
GROUP BY
t.Custname
)
Thanks in advance
|
|
|
|
| Re: How to avoid union? [message #361021 is a reply to message #361018] |
Mon, 24 November 2008 20:13  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
It's not the DISTINCT that is stopping index use. You are not restricting the query with a WHERE clause, so all rows are candidates for the query. Because you are reading every row, Oracle figures (correctly) that a full scan will be faster.
You don't need the DISTINCTs or GROUP BYs anyway because the UNION will remove duplicates. Still, Oracle should detect this and optimise it out. It wouldn't hurt to remove them though.
Otherwise, what you are doing is the quickest way.
How many rows are in AnotherTable?
And how many distinct combinations are inserted for each SQL?
Ross Leishman
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
