Чем заменить union all oracle
Обновлено: 04.07.2024
Thanks. We have received your request and will respond promptly.
Come Join Us!
Are you aComputer / IT professional?
Join Tek-Tips Forums!
- Talk With Other Members
- Be Notified Of Responses
To Your Posts - Keyword Search
- One-Click Access To Your
Favorite Forums - Automated Signatures
On Your Posts - Best Of All, It's Free!
Posting Guidelines
Promoting, selling, recruiting, coursework and thesis posting is forbidden.
1 Answer 1
I noticed that in your query the only real functional difference is in the two WHERE clauses. In the first half of the UNION ALL the condition has:
while the second half has:
You can use a CASE expression to generate a computed column containing the attribute type. For clarity, here is that CASE expression by itself:
And here is the full query:
The only major potential setback in this query is that you lose the two null header rows. However, for reporting purposes there should be no problem. If your boss insists on those null header rows, then tell him that they are synthesized data and the only way to add them to the result set is by doing a UNION .
UNION All VS OR in oracle
Чем заменить union all oracle
select distinct * from
(
select * from aaa
union all
select * from bbb
union all
select * from ccc)
А если уверен, что в таблице нет будлирующий записей, то еще проще
select distinct * from
(
select * from aaa
union
select * from bbb
union
select * from ccc)
Executed in 7.203 seconds
SQL> create table t2 as select rownum "ID", table_name from user_tab_columns;
Executed in 2.437 seconds
SQL> select count(*) from t1;
Executed in 0 seconds
SQL> select count(*) from t2;
Executed in 0.031 seconds
SQL> select count(*) from
2 (select distinct * from (select * from t1 union select * from t2));
Executed in 0.094 seconds
SQL> select count(*) from (select distinct * from (select * from t1 union all select * from t2));
Executed in 0.063 seconds
SQL>
SQL> select count(*) from (select * from (select * from t1 union select * from t2));
Executed in 0.063 seconds
SQL> select count(*) from (select distinct * from (select * from t1 union all select * from t2));
Executed in 0.047 seconds
SQL> insert into t1 select * from t1;
1006 rows inserted
Executed in 0.016 seconds
2012 rows inserted
Executed in 0 seconds
4024 rows inserted
Executed in 0.015 seconds
8048 rows inserted
Executed in 0.031 seconds
16096 rows inserted
Executed in 0.125 seconds
32192 rows inserted
Executed in 0.141 seconds
64384 rows inserted
Executed in 0.281 seconds
128768 rows inserted
Executed in 0.672 seconds
257536 rows inserted
Executed in 1.313 seconds
515072 rows inserted
Executed in 2.469 seconds
Executed in 0.016 seconds
SQL> insert into t2 select * from t2;
23413 rows inserted
Executed in 0.094 seconds
46826 rows inserted
Executed in 0.266 seconds
93652 rows inserted
Executed in 0.391 seconds
187304 rows inserted
Executed in 1.938 seconds
374608 rows inserted
Executed in 2.718 seconds
749216 rows inserted
Executed in 4.469 seconds
1498432 rows inserted
Executed in 12.89 seconds
2996864 rows inserted
Executed in 28.625 seconds
Executed in 0 seconds
SQL> select count(*) from t1;
Executed in 0.547 seconds
SQL> select count(*) from t2;
Executed in 13.656 seconds
SQL> select count(*) from (
2 select distinct * from (select * from t1 union all select * from t2));
Executed in 31.765 seconds
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=128 Card=1)
1 0 SORT (AGGREGATE)
2 1 VIEW (Cost=128 Card=24419)
3 2 SORT (UNIQUE) (Cost=128 Card=24419 Bytes=634894)
4 3 VIEW (Cost=6 Card=24419 Bytes=634894)
5 4 UNION-ALL (PARTITION)
6 5 TABLE ACCESS (FULL) OF 'T1' (Cost=2 Card=1006 By
tes=25150)
7 5 TABLE ACCESS (FULL) OF 'T2' (Cost=6 Card=23413 B
ytes=585325)
SQL> select count(*) from (
2 select * from (select * from t1 union select * from t2));
Executed in 27.219 seconds
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=128 Card=1)
1 0 SORT (AGGREGATE)
2 1 VIEW (Cost=128 Card=24419)
3 2 SORT (UNIQUE) (Cost=128 Card=24419 Bytes=610475)
4 3 UNION-ALL
5 4 TABLE ACCESS (FULL) OF 'T1' (Cost=2 Card=1006 Byte
s=25150)
6 4 TABLE ACCESS (FULL) OF 'T2' (Cost=6 Card=23413 Byt
es=585325)
SQL> select 24419/(1030144 + 5993728) * 100 from dual;
24419/(1030144+5993728)*100
---------------------------
0.347657246601305 ( t.e deviation > 25%)
А вот пример Ваш интересен, я только после него заметил, что distinct не туда сунул и планы отличаются способом выполнения union-all. Спасибо!
Для distinct *-union all варианта объединение выпоняется в несколько проходов
4 3 VIEW (Cost=6 Card=24419 Bytes=634894)
5 4 UNION-ALL (PARTITION)
а для *-union в один
4 3 UNION-ALL
UNION All VS OR in oracle
I am using oracle 9i. I have written a database view using UNION ALL to get the required data.
I am getting the same data using OR condition also. Which is good in performance?
I tested both, OR condition view is taking more time than UNION ALL View. Can anybody explain which is good and why?
Hi,
Can you run an explain plan of each version?
If possible, post the 2 versions of the query.
To Paraphrase:"The Help you get is proportional to the Help you give.."
When i try to run the explain plan in toad, it says "specified plan table not found".
the example query with OR
select * from a,b
where (a.x=b.x and a.y='abc'etc., OR a.x=b.x and A.Y='XYZ'etc.,)
the example query with UNION ALL
select * from a,b
where (a.x=b.x and A.y='abc'. )
select * from a,c
where (a.x=b.x and a.y='XYZ' . )
when i try to run the explain plan in toad, i am getting this error" specified plan table not found".
example query using OR:
select * from a,b where
(a.x=b.x and a.y='ABC' OR a.x=b.x and a.y='XYZ')
example query using UNION ALL:
select * from a,b where
(a.x=b.x and a.y='ABC")
SELECT * FROM a,b where (a.x=b.x and a.y='XYZ')
You should create plan table or get access to already created, if any. Actually Oracle optimizer may build similar execution plans for both options. Or different. So in general the answer is "it depends"Generally an OR should be preferred, since using UNION ALL is always a little challenge for the optimizer to see that it actually needs to scan an index/table only once and not twice.
I'd also go and update statistics. See who will then be faster.
Also it appears you are not sending the query you are actually running. With additional conditions coming using ORDER or GROUP operations things can be different.
Dima is very right, only a plan will show what the optimizer is doing .
I don't agree that OR should be preferred. Sometimes it's a great pain to make optimizer evaluate OR to UNION, in some cases it just refuses to use index, assuming that 2 values is too much. But I agree that in most cases it may save logical reads from other tables.I tested both queries and UNION All taking less time. So I am using UNION All. Thanks for all your help.
Red Flag Submitted
Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.
Reply To This Thread
Posting in the Tek-Tips forums is a member-only feature.
Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Alternative to UNION ALL in Oracle SQL
I am trying to recreate this query without using UNION ALL statement, I have tried Scalar Subqueries and the model clause but none seem to do what I want. My manager doesn't want to use Union All but I don't really see what the alternative is
any help would be great
This is why UNION was created. If you insist on doing it, you can use FULL OUTER JOIN ON(1 = 2) between all this queries, and NVL or CASE EXPRESSION to replace the null values, that will do the same as union.UNION All VS OR in oracle
Oracle SQL - Alternative to the UNION ALL statement
You actually can do this in Oracle, but the coding is a bit messy. The idea is to do a full outer join on a non-matching field, and then use coalesce() to bring the results together:
However, I'm not sure if the preceding will work on the temp column. One reason is that the cast() doesn't seem to be fully formulated:
1.2m 51 51 gold badges 512 512 silver badges 651 651 bronze badges When I do a Union All and key column for both tables is non-nullable, it still reports the view columns as nullable. This makes it impossible to bring into Entity Framework. I gave your coalesce a shot, but its the same deal. Really wish there was a way for to specify that a column of a view that its not nullable, regardless of what Oracle thinks. @DavidP . . . I have no idea what your comment has to do with this answer. If you have a question, though, feel free to ask one as a question.You could store the results of every query in a (temporary) result table and then in the end fetch all the results at once.
The only reason I can think of to do this is to split the work into smaller chunks either to conserve resources (not sure it will make that much of a difference though) or to benchmark the different queries separately.
Below how this would look more or less in Oracle. (I'm more of a MSSQL man)
Читайте также: