Чем заменить union all oracle
Thanks. We have received your request and will respond promptly.
Come Join Us!
- 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!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.
Posting Guidelines
Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Answers
As an example here is a method 7.1 using model clause:
Without some context it is difficult to say what is a solid approach for your problem.
It would help to know more about your reason behind this question.
The solution below is more general: start with all the values in a single row and unpivot to three rows. The initial values can be anything at all - the majority of them being NULL is just an accident which plays no role in this solution. I wrote it for five columns instead of ten - the principle, obviously, is the same.
UNION ALL (set concatenation) is just about the quickest set operation a DB can do. No sorting, no hashing, nothing. Just whack rows on the end of things.
It might be verbose, but it's FAST and highly optimised. Unless you have a domain reason not to use it, you should probably stick with it.
Thanks for everyone's input. Honestly, this is part of an existing query that someone wrote before I joined the company, so I'm not sure why he did it this way. Yes, I did shorten it, but the only difference is that the original query has a total of 18 union all's, I'm just looking for an alternative that will make the query smaller. I'll eventually get around to rewriting the entire thing.
Are the non key columns always null and is col1 the operative column selected from dual?
select column_value as col1, null col2, null col3, null col4, null col5, null col6
from table(sys.odcivarchar2list('AA', 'BB', 'CC'))
Correct. They're always null and col1 is always operative.
If they are always NULL why are they needed in the select? Maybe a good option to clean up the statement would simply be to remove those NULL columns.
I'm new to unpivot, so can you tell me what the purpose of the "for rn" is? Especially since I don't see rn being declared any place.
FOR is a required clause in UNPIVOT. It shows which columns must be unpivoted. RN is an arbitrary name I gave to the resulting column - I use "RN" as short for Row Number. As you can see, in the FOR RN IN (. ) clause, I dictate that the values from columns 1 through 5 be shown in a row with RN = 1, columns 6 through 10 go with RN = 2, and columns 11 through 15 in the row with RN = 3. Absolutely, if you want you can include RN in the outer SELECT, but you didn't have it in your original question, so I left it out. Even so, I can at least use RN to order by it; you didn't tell us if your query was used just to generate the rows or to also show them in a report, where perhaps you need them ordered. (I always add the comment -- If needed after ORDER BY when I answer on this site, unless the OP says explicitly they DO need to order the results.)
Есть таблица A. Надо выбрать из нее все записи, на которые есть ссылки в таблицах B и C.
То есть, простейшее решение выглядит так:
select A.* from A inner join B on B.a = A.a
union
select A.* from A inner join C on C.a = A.a
Но мне надо, чтобы селект был только один, причем таблицы в запрос могут добавляться только через join'ы.
Эти ограничения накладывает Hibernate Criteria API. Я сначала составляю Criteria, который является обвязкой запроса, потом отдаю его, и запросивший этот Criteria может добавить еще условия по своему выбору.
В общем, можно как-либо сделать один селект через джойны заместо нескольких селектов с union?
Здравствуйте, Donz, Вы писали:
D>Есть таблица A. Надо выбрать из нее все записи, на которые есть ссылки в таблицах B и C.
D>То есть, простейшее решение выглядит так:
D>select A.* from A inner join B on B.a = A.a
D>union
D>select A.* from A inner join C on C.a = A.a
D>Но мне надо, чтобы селект был только один, причем таблицы в запрос могут добавляться только через join'ы.
D>Эти ограничения накладывает Hibernate Criteria API. Я сначала составляю Criteria, который является обвязкой запроса, потом отдаю его, и запросивший этот Criteria может добавить еще условия по своему выбору.
D>В общем, можно как-либо сделать один селект через джойны заместо нескольких селектов с union?
B и C? Или B ИЛИ C?
Здравствуйте, Donz, Вы писали:
D>Есть таблица A. Надо выбрать из нее все записи, на которые есть ссылки в таблицах B и C.
D>То есть, простейшее решение выглядит так:
D>select A.* from A inner join B on B.a = A.a
D>union
D>select A.* from A inner join C on C.a = A.a
select A.* from A. left join B on B.a = A.a left join C on C.a = A.a
where notNull(B.a) and notNull(C.a)
не помню как на null проверять
Здравствуйте, cvetkov, Вы писали:
C>Здравствуйте, Donz, Вы писали:
D>>Есть таблица A. Надо выбрать из нее все записи, на которые есть ссылки в таблицах B и C.
D>>То есть, простейшее решение выглядит так:
D>>select A.* from A inner join B on B.a = A.a
D>>union
D>>select A.* from A inner join C on C.a = A.a
C>select A.* from A. left join B on B.a = A.a left join C on C.a = A.a
C>where notNull(B.a) and notNull(C.a)
C>не помню как на null проверять
В данном случае проще написать
select A.* from A. join B on B.a = A.a join C on C.a = A.a
Результат будет тот же. Это работает для случая B и C, причем полным эквивалентом union будет не всегда — если в таблицах B или С есть несколько ссылок на A, то будет замножение результата. Нужен distinct или group by.
Для случая B ИЛИ C нужно писать
select distinct(A.a) from A left join B on B.a = A.a left join C on C.a = A.a
where B.a is not null or C.a is not null
Здравствуйте, Donz, Вы писали:
D>В общем, можно как-либо сделать один селект через джойны заместо нескольких селектов с union?
Не знаю, правильно ли, но я последний раз делал так — (сразу на HQL, специально открыл исходники, на перфоманс вроде жалоб нет)
У меня joinов было 6, таким образом заработало гораздо шустрее, чем в случае с left join (тогда жалобы были на перфоманс ).
Когда-то кажется делал другим способом, и кажется без in, но напрочь забыл, и это осталось в другом проекте:
что-то вроде
Увы, запросы я много писал более 4-х лет назад, сейчас один запрос в полгода максимум.
Здравствуйте, Donz, Вы писали:
D>Есть таблица A. Надо выбрать из нее все записи, на которые есть ссылки в таблицах B и C.
D>То есть, простейшее решение выглядит так:
D>select A.* from A inner join B on B.a = A.a
D>union
D>select A.* from A inner join C on C.a = A.a
D>Но мне надо, чтобы селект был только один, причем таблицы в запрос могут добавляться только через join'ы.
D>Эти ограничения накладывает Hibernate Criteria API. Я сначала составляю Criteria, который является обвязкой запроса, потом отдаю его, и запросивший этот Criteria может добавить еще условия по своему выбору.
D>В общем, можно как-либо сделать один селект через джойны заместо нескольких селектов с union?
А почему нельзя так:
select A.* from A inner join B on B.a = A.a
inner join C on C.a=A.a
?
и почему Вы берете "все" только из А?
Здравствуйте, svanir, Вы писали:
S>А почему нельзя так:
S>select A.* from A inner join B on B.a = A.a
S> inner join C on C.a=A.a
S>?
S>и почему Вы берете "все" только из А?
Здесь тогда будет не объединение, а пересечение. И условие — нужно показать все A, которые связаны либо с B, либо с C. Достаточно типичная задача кстати.
Здравствуйте, Donz, Вы писали:
D>В общем, можно как-либо сделать один селект через джойны заместо нескольких селектов с union?
Здравствуйте, Lloyd, Вы писали:
L>
По крайней мере на MS SQL 2005 тормоза наблюдал страшные (20 секунд запрос выполнялся), сойдет только на небольших объемах. Сервер похоже сначала таблицы соединит (а они черти какого объема), а только потом фильтрует — жуть.
Здравствуйте, Donz, Вы писали:
D>Эти ограничения накладывает Hibernate Criteria API. Я сначала составляю Criteria, который является обвязкой запроса, потом отдаю его, и запросивший этот Criteria может добавить еще условия по своему выбору.
D>В общем, можно как-либо сделать один селект через джойны заместо нескольких селектов с union?
А если создать вьюху с union-ами и использовать ее?
Здравствуйте, Romanzek, Вы писали:
D>>Есть таблица A. Надо выбрать из нее все записи, на которые есть ссылки в таблицах B и C.
D>>То есть, простейшее решение выглядит так:
D>>select A.* from A inner join B on B.a = A.a
D>>union
D>>select A.* from A inner join C on C.a = A.a
R>B и C? Или B ИЛИ C?
Или B, или C. В общем надо выбрать один раз все записи, ссылки на которые есть хотя бы в одной из таблиц B или C. Нужен аналог приведенного запроса
Здравствуйте, elmal, Вы писали:
D>>В общем, можно как-либо сделать один селект через джойны заместо нескольких селектов с union?
E>Не знаю, правильно ли, но я последний раз делал так — (сразу на HQL, специально открыл исходники, на перфоманс вроде жалоб нет)
E>
E>У меня joinов было 6, таким образом заработало гораздо шустрее, чем в случае с left join (тогда жалобы были на перфоманс ).
Запрос с двумя внутренними селектами и условиями in работает быстрее left join'ов? Что-то слабо верится.
Но в любом случае подселекты не катят — мне надо все сделать через Criteria API.
Здравствуйте, elmal, Вы писали:
L>>
E>По крайней мере на MS SQL 2005 тормоза наблюдал страшные (20 секунд запрос выполнялся), сойдет только на небольших объемах. Сервер похоже сначала таблицы соединит (а они черти какого объема), а только потом фильтрует — жуть.
Можно подробнее? Оптимизацию запросов в БД начал изучать заново с полгода назад. Пока я в этом запросе ничего криминального не вижу.
Здравствуйте, Lloyd, Вы писали:
D>>Эти ограничения накладывает Hibernate Criteria API. Я сначала составляю Criteria, который является обвязкой запроса, потом отдаю его, и запросивший этот Criteria может добавить еще условия по своему выбору.
D>>В общем, можно как-либо сделать один селект через джойны заместо нескольких селектов с union?
L>А если создать вьюху с union-ами и использовать ее?
Не подходит, так как выбранные объекты могут быть изменены. С вьхой придется делать еще один мэппинг уже конкретно на таблицу и перед изменениями таскать выбранные объекты еще одним запросом с условием in. В общем, не очень красиво и захламляет код.
Плюс это повлечет изменение БД, что в моем случае несколько геморройно.
Здравствуйте, svanir, Вы писали:
D>>Есть таблица A. Надо выбрать из нее все записи, на которые есть ссылки в таблицах B и C.
D>>То есть, простейшее решение выглядит так:
D>>select A.* from A inner join B on B.a = A.a
D>>union
D>>select A.* from A inner join C on C.a = A.a
S>А почему нельзя так:
S>select A.* from A inner join B on B.a = A.a
S> inner join C on C.a=A.a
S>?
Этот запрос выберет записи, которые одновременно находятся и в B, и в C. Мне же надо условие "или в B, или в C". elmal уже написал, в общем.
S>и почему Вы берете "все" только из А?
Не совсем понял вопрос. Надо мне так, взять только все данные из таблицы A, а остальные таблицы нужны только для ограничения выборки.
Здравствуйте, Donz, Вы писали:
D>Можно подробнее? Оптимизацию запросов в БД начал изучать заново с полгода назад. Пока я в этом запросе ничего криминального не вижу.
Да не могу подробнее, тут все от оптимизатора зависит. Я ж говорю, что пишу один более-менее сложный запрос в полгода, а оптимизация . на одном сервере будет шустро работать так, на другом иначе — не угадаешь, а все тонкости учить для каждого сервера — не окупится, слишком часто они меняются, по крайней мере у меня. Просто вот такой запрос в случае с left join у меня узким местом оказался. Когда данных было мало, все шустро, наполнили базу — тормоза сразу страшные (а под нагрузочным тестированием вообще жуть). Переписал в итоге на вариант с in — практически моментально стало (точнее время выполнения запроса меньше времени перерисовки, соответственно дальше я отпимизировать не стал). И еще были проблемы с left join на MS SQL (его генерил hibernate), тоже одна сущность грузилась в результате несколько секунд — переписал с использованием вложенных подзапросов, сразу все шустро стало. LEFT JOIN я бы потому старался избегать, часто сервер делает сначала его, а только потом фильтрацию (мои догадки это, только этим могу тормоза объяснить).
Ну и я там еще один вариант показывал (пересечение таблицы сама с собой который), по идее он самый шустрый должен быть (я не уверен что там я буз ошибок написал), на firebird я когда пересечения вот такие делал страшные для таблиц в миллионы записей — вполне шустро работало. А in мне не очень нравится, интуиция просто подсказывает что in лучше избегать, оптимизатор может не очень хорошо построить план выполнения.
Здравствуйте, Donz, Вы писали:
D>Запрос с двумя внутренними селектами и условиями in работает быстрее left join'ов? Что-то слабо верится.
У меня тогда этих left join было штук 6, и каждый еще соединялся еще с несколькоми таблицами (ох хотелось тогда высказать все, что я думаю о структуре базы, еле сдержался ). С in заработало быстрее, причем раз в 100 — скорость замерял.
D>Но в любом случае подселекты не катят — мне надо все сделать через Criteria API.
Нижний вариант пробовал? Он явно должен быть самым шустрым, так как там только join по первичному ключу идет (я правда не знаю, можно ли через Criteria API пересечения таблиц как я указал делать). Возможно я ошибся, и я что-то забыл в запросе, надо пробовать, но один раз у меня точно похожим образом union на хибернейте сделать получилось.
Здравствуйте, elmal, Вы писали:
E>У меня тогда этих left join было штук 6, и каждый еще соединялся еще с несколькоми таблицами (ох хотелось тогда высказать все, что я думаю о структуре базы, еле сдержался ). С in заработало быстрее, причем раз в 100 — скорость замерял.
Хотя, справедливости ради, запрос у меня был несколько посложнее, там у меня была задача похожая на твою, но там связи были по простым аттрибутам, которые с таблицей B были связана еще через 5 таблиц . Они не были даже внешними ключами и на них даже индексов не висело, потому и такой результат. Потом индексы добавили, я попросил того, кто занимается оптимизацией предыдущий запрос проверить, вроде один черт left join медленнее чем вложенные селекты с in оказалось.
Здравствуйте, elmal, Вы писали:
D>>Запрос с двумя внутренними селектами и условиями in работает быстрее left join'ов? Что-то слабо верится.
E>У меня тогда этих left join было штук 6, и каждый еще соединялся еще с несколькоми таблицами (ох хотелось тогда высказать все, что я думаю о структуре базы, еле сдержался ). С in заработало быстрее, причем раз в 100 — скорость замерял.
D>>Но в любом случае подселекты не катят — мне надо все сделать через Criteria API.
E>Нижний вариант пробовал? Он явно должен быть самым шустрым, так как там только join по первичному ключу идет (я правда не знаю, можно ли через Criteria API пересечения таблиц как я указал делать). Возможно я ошибся, и я что-то забыл в запросе, надо пробовать, но один раз у меня точно похожим образом union на хибернейте сделать получилось.
Нижний — это left join и проверка на null? Пока нет, пробовать буду в понедельник.
За информацию спасибо, буду проверять, так как запрос будет очень часто исполняемым.
Здравствуйте, Donz, Вы писали:
D>Нижний — это left join и проверка на null? Пока нет, пробовать буду в понедельник.
Я вариант с left join вообще не приводил. Он работать то будет, тут я не сомневаюсь, вопрос в скорости. Второй вариант, который я предложил — пересечение таблицы и двумя джоинами, как любят ораклисты писать (не джоин, а select from A,B,C where . ) вот он очень шустро должен выполняться теоретически, возможно даже шустрее, чем в случае с использованием union (неисповедимы пути оптимизатора запросов).
Еще раз —
Вот только не факт, что я его правильно написал, но один раз я точно что-то подобное проворачивал на хибернейте, помню точно, что я делал union и без left join, и без in, и основная идея была именно такая — пересечение таблицы с собой под разными алиасами (задача была другой помнится). Просьба попробовать, и сообщить, если получится — я тогда в следующий раз, когда это понадобится, поиском найду, а то опять забуду . На деле, синтаксис SQL весьма избыточен, и при желании одним запросом можно сделать многое, даже если куча фичь, вроде union, вложенных подзапросов и т.д не поддерживается.
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
You are selecting two result lists and combine them to get one list. This is exacly what UNION [ALL] is made for. So why would you want to avoid UNION here? Sorry, I can think of no reason.
Presumably this is because of the behaviour you're seeing on your previous question? It would be better to figure that out; even if you could come up with a hack to avoid union all , it could suffer from the same issue. Making a reproducible test case as Justin suggested is probably your best bet.
Yes it is because for some reason the union all is nullifying the clob objects. I am trying to find a quick temporary patch for it while i try to figure out the permanent solution.
The DBA has suggested that it could be something to do with the way the Optimizer is configured and he wont be able to look into it straight away hence i am trying to work out a temporary solution.
2 Answers 2
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:
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)
I probably know less about oracle syntax than you do =) But the idea is that you 1) create a working table with the layout of the required results. 2) run each query sequentially redirecting the output into this working table 3) select the contents of the working table. I'll see if I can get something similar going in SqlFiddle and edit it into my answer.
You can combine multiple queries using the set operators UNION , UNION ALL , INTERSECT , and MINUS . All set operators have equal precedence. If a SQL statement contains multiple set operators, then Oracle Database evaluates them from the left to right unless parentheses explicitly specify another order.
The corresponding expressions in the select lists of the component queries of a compound query must match in number and must be in the same data type group (such as numeric or character).
If component queries select character data, then the data type of the return values are determined as follows:
If both queries select values of data type CHAR of equal length, then the returned values have data type CHAR of that length. If the queries select values of CHAR with different lengths, then the returned value is VARCHAR2 with the length of the larger CHAR value.
If either or both of the queries select values of data type VARCHAR2 , then the returned values have data type VARCHAR2 .
If component queries select numeric data, then the data type of the return values is determined by numeric precedence:
If any query selects values of type BINARY_DOUBLE , then the returned values have data type BINARY_DOUBLE .
If no query selects values of type BINARY_DOUBLE but any query selects values of type BINARY_FLOAT , then the returned values have data type BINARY_FLOAT .
If all queries select values of type NUMBER , then the returned values have data type NUMBER .
In queries using set operators, Oracle does not perform implicit conversion across data type groups. Therefore, if the corresponding expressions of component queries resolve to both character data and numeric data, Oracle returns an error.
Table 2-8 for more information on implicit conversion and "Numeric Precedence" for information on numeric precedence
The following query is valid:
This is implicitly converted to the following compound query:
The following query returns an error:
Restrictions on the Set Operators
The set operators are subject to the following restrictions:
The set operators are not valid on columns of type BLOB , CLOB , BFILE , VARRAY , or nested table.
The UNION , INTERSECT , and MINUS operators are not valid on LONG columns.
If the select list preceding the set operator contains an expression, then you must provide a column alias for the expression in order to refer to it in the order_by_clause .
You cannot also specify the for_update_clause with the set operators.
You cannot specify the order_by_clause in the subquery of these operators.
You cannot use these operators in SELECT statements containing TABLE collection expressions.
To comply with emerging SQL standards, a future release of Oracle will give the INTERSECT operator greater precedence than the other set operators. Therefore, you should use parentheses to specify order of evaluation in queries that use the INTERSECT operator with other set operators.
The following statement combines the results of two queries with the UNION operator, which eliminates duplicate selected rows. This statement shows that you must match data type (using the TO_CHAR function) when columns do not exist in one or the other table:
UNION ALL Example
The UNION operator returns only distinct rows that appear in either result, while the UNION ALL operator returns all rows. The UNION ALL operator does not eliminate duplicate selected rows:
A location_id value that appears multiple times in either or both queries (such as ' 1700 ') is returned only once by the UNION operator, but multiple times by the UNION ALL operator.
The following statement combines the results with the INTERSECT operator, which returns only those unique rows returned by both queries:
The following statement combines results with the MINUS operator, which returns only unique rows returned by the first query but not by the second:
I have the following query, and I'm curious if there's an alternative to UNION ALL. It seems like it would be a candidate to UNPIVOT, but I can't get it to work. Any suggestions?
NULL AS column2,
NULL AS column3,
NULL AS column4,
NULL AS column5,
NULL AS column6,
NULL AS column7,
NULL AS column8,
NULL AS column9,
NULL AS column10
NULL AS column2,
NULL AS column3,
NULL AS column4,
NULL AS column5,
NULL AS column6,
NULL AS column7,
NULL AS column8,
NULL AS column9,
NULL AS column10
NULL AS column2,
NULL AS column3,
NULL AS column4,
NULL AS column5,
NULL AS column6,
NULL AS column7,
NULL AS column8,
NULL AS column9,
NULL AS column10
UNION All VS OR in oracle
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.
Читайте также: