Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3   вперед  Ctrl      все
 Re: Можно ли эмулировать TOP MSSQL на ORACLE одним запросом без подзапросов  [new]
env
Member

Откуда: Россия, Москва
Сообщений: 6727
Переходящий_c_MSSQL
как выяснилось у Oracla есть проблемы с order by в подзапросах.
См пример выше.

Это не у оракла проблемы с order by в подзапросах, это проблема в отсутствии здравого смысла у подобного запроса как такового.
in (select .. order by ..) - изначально бессмысленная конструкция.
19 апр 11, 13:36    [10536179]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли эмулировать TOP MSSQL на ORACLE одним запросом без подзапросов  [new]
Сергей Арсеньев
Member

Откуда:
Сообщений: 4118
Переходящий_c_MSSQL
Сергей Арсеньев
Не во всех случаях сработает - как выяснилось у Oracla есть проблемы с order by в подзапросах.
См пример выше.

Я и показываю как этот баг обходится.
19 апр 11, 13:36    [10536181]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли эмулировать TOP MSSQL на ORACLE одним запросом без подзапросов  [new]
-2-
Member

Откуда:
Сообщений: 15330
Сергей Арсеньев
Я и показываю как этот баг обходится.
еще вариант
select dr.r, d.*
from all_objects d
join (select owner, object_name, object_type, row_number() over(order by owner desc, object_name) r from all_objects) dr
   on (d.owner, d.object_name, d.object_type) = ((dr.owner, dr.object_name, dr.object_type)) and dr.r <=10
;
19 апр 11, 13:41    [10536215]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли эмулировать TOP MSSQL на ORACLE одним запросом без подзапросов  [new]
Сергей Арсеньев
Member

Откуда:
Сообщений: 4118
-2-
Сергей Арсеньев
баг

Ну да два релиза - фича. :)
19 апр 11, 13:43    [10536223]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли эмулировать TOP MSSQL на ORACLE одним запросом без подзапросов  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15487
Переходящий_c_MSSQL
orawish
Как будет выглядеть данный пример если для сортировки используется функция или выражение.
А почему шутейная? Дико тормозит?

1) как выглядеть.. да так же и выглядеть
over(order by cos(id))
2) и ресурсов потребляет много и версионно-лицензионные ограничения есть (EE + olap option) и надежность хромает (если в сочетаниях с другими факторами риска)
19 апр 11, 13:47    [10536268]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли эмулировать TOP MSSQL на ORACLE одним запросом без подзапросов  [new]
Переходящий_c_MSSQL
Guest
env
Ну почему сразу отсутствие смысла?
Конструкция на MSSQL не in (select .. order by ..) а in (select top N ... order by ..)
Почуствуйте разницу.

-2-
Этот вариант я уже предлагал только немножко в другом виде, в данном он не подходит из за не универсальности,
так как мне надо программно заменить выражение (select Top N ........) на аналогичное выражение Oracle в любом месте
SQL запроса.
19 апр 11, 13:53    [10536318]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли эмулировать TOP MSSQL на ORACLE одним запросом без подзапросов  [new]
env
Member

Откуда: Россия, Москва
Сообщений: 6727
Переходящий_c_MSSQL,

Почувствовал - это два абсолютно разных запроса. Как сделать второй, вам уже показали. Первая конструкция бессмысленна.
19 апр 11, 13:58    [10536361]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли эмулировать TOP MSSQL на ORACLE одним запросом без подзапросов  [new]
Vint
Member

Откуда: Москва
Сообщений: 4564
исключительно из любви к исскуству:

+
with t as (select /*+ MATERIALIZED*/
 level lv from dual
connect by level<101)
select lv from t
where rownum < 10
start with lv = 1 
connect by  lv = prior lv+1
order siblings by lv

with t as (select /*+ MATERIALIZED*/
 level lv from dual
connect by level<101)
select lv from t
where rownum < 10
connect by  1=2
order siblings by lv

эффект достигается засчёт магии и результат на реальных данных мягко говоря не такой)))
19 апр 11, 14:16    [10536519]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли эмулировать TOP MSSQL на ORACLE одним запросом без подзапросов  [new]
-2-
Member

Откуда:
Сообщений: 15330
Переходящий_c_MSSQL
так как мне надо программно заменить выражение (select Top N ........) на аналогичное выражение Oracle в любом месте
А программно выделять это "любое место" уже умеешь?
orawish
и версионно-лицензионные ограничения есть (EE + olap option)
select *
from v$version
model
dimension by (row_number() over(order by null) r) 
measures (banner, cast(null as varchar2(20)) ee)
rules (
   ee[any] = regexp_substr(banner[cv()], '[^ ]+ edition', 1, 1, 'i')
);

R                      BANNER                                                                           EE                   
---------------------- -------------------------------------------------------------------------------- -------------------- 
1                      Oracle Database 11g Release 11.2.0.1.0 - 64bit Production                                             
2                      PL/SQL Release 11.2.0.1.0 - Production                                                                
3                      CORE	11.2.0.1.0	Production                                                                              
4                      TNS for Linux: Version 11.2.0.1.0 - Production                                                        
5                      NLSRTL Version 11.2.0.1.0 - Production                                                                
19 апр 11, 14:16    [10536523]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли эмулировать TOP MSSQL на ORACLE одним запросом без подзапросов  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15487
-2-
..
select *
from v$version
model
..             

спасибо!
19 апр 11, 14:22    [10536582]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли эмулировать TOP MSSQL на ORACLE одним запросом без подзапросов  [new]
Exotic
Member

Откуда: Новороссийск
Сообщений: 322
Запрос Винта, кстати, можно написать значительно проще:
select t.* from custom_tab t
where rownum < 10
connect by  1=2
order siblings by custom_column
P.S: изыскания по ссылке мои, кидать камни в мою сторону.
19 апр 11, 14:23    [10536590]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли эмулировать TOP MSSQL на ORACLE одним запросом без подзапросов  [new]
Переходящий_c_MSSQL
Guest
-2-
А программно выделять это "любое место" уже умеешь?

Умею.
Exotic Спасибо за пример - это то что нужно!
Эх если бы была гарантия что это будет работать в любых версиях Oracle начиная с 10-ки.
19 апр 11, 14:50    [10536787]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли эмулировать TOP MSSQL на ORACLE одним запросом без подзапросов  [new]
Exotic
Member

Откуда: Новороссийск
Сообщений: 322
Переходящий_c_MSSQL,

обращаю внимание, что хоть, судя по тестам, эта конструкция работает на всех версиях начиная с 9.2 (ранние не проверялись), данный подход не является документированным и никаких гарантий дать нельзя.
19 апр 11, 14:56    [10536827]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли эмулировать TOP MSSQL на ORACLE одним запросом без подзапросов  [new]
Переходящий_c_MSSQL
Guest
Это я понимаю.
Поэтому буду использовать row_number() over(order by order value) хотя этот варианта более трудоемкий для автоматизации за счет
выноса внешних параметров на первый уровень подзапроса.
19 апр 11, 15:05    [10536908]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли эмулировать TOP MSSQL на ORACLE одним запросом без подзапросов  [new]
Сергей Арсеньев
Member

Откуда:
Сообщений: 4118
env
Первая конструкция бессмысленна.


Но это не извиняет Oracle, который на это генерит совем уж глупую ошибку, а если внести те
+ правки
select *
  from dual
 where (dummy) in (
  select dummy
    from dual
   order by dummy
 );
, которые он требует, то меняет свои показания на:

ORA-00920: неверен реляционный оператор
00920. 00000 - "invalid relational operator"
19 апр 11, 15:56    [10537283]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли эмулировать TOP MSSQL на ORACLE одним запросом без подзапросов  [new]
dbms_photoshop
Member

Откуда: sqlmdx.net
Сообщений: 5151
Переходящий_c_MSSQL
Это я понимаю.
Поэтому буду использовать row_number() over(order by order value) хотя этот варианта более трудоемкий для автоматизации за счет
выноса внешних параметров на первый уровень подзапроса.
Про count stopkey еще почитай, если хочешь понять как и что работает.
19 апр 11, 16:49    [10537632]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли эмулировать TOP MSSQL на ORACLE одним запросом без подзапросов  [new]
Elic
Member

Откуда:
Сообщений: 29980
некты
order siblings
siblings - это не roots, которые не сортируются таким образом. Давно известный факт.
Кажущийся желаемым порядок - иллюзия магии данных.
19 апр 11, 17:01    [10537721]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли эмулировать TOP MSSQL на ORACLE одним запросом без подзапросов  [new]
Exotic
Member

Откуда: Новороссийск
Сообщений: 322
Elic,

а можно ORDER ROOTS BY field? Это понятно, что здесь исключительно магия и бессмысленное использование деревянного запроса на плоскую выборку в отсутствие детей. Я лишь изобразил этот способ использования дерева для того, чтобы ROWNUM провести после сортировки данных (что, насколько я понимаю, происходит только в случае CONNECT BY), в результате мы получаем требуемые данные в нужном виде. Но да, это не железное правило, это лишь конкретная реализация, которая может поменяться в следующих версиях.
19 апр 11, 17:25    [10537873]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли эмулировать TOP MSSQL на ORACLE одним запросом без подзапросов  [new]
-2-
Member

Откуда:
Сообщений: 15330
Exotic
в результате мы получаем требуемые данные в нужном виде
Вроде автор отказался от подвложенного запроса из-за невозможности протолкнуть в него условие. Здесь результат такой же - сначала "сортировка", потом фильтр.
19 апр 11, 20:20    [10538497]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли эмулировать TOP MSSQL на ORACLE одним запросом без подзапросов  [new]
Переходящий_c_MSSQL
Guest
-2-
Вроде автор отказался от подвложенного запроса из-за невозможности протолкнуть в него условие. Здесь результат такой же - сначала "сортировка", потом фильтр.


Отказаться то отказался но других универсальных вариантов пока не придумал :(

select t1.*
from custom_tab1 t1
where t1.id in (select t.* from custom_tab t
where rownum < N and t1.param=t2.param and ....)
connect by  1=2
order siblings by t.orderfield)

Не работает ORA-00907 missing right parenthesis

select *
from custom_tab1 t1
where t1.id in 
(select t2.id
(select t.* from custom_tab t
where rownum < 10 and t1.param=t2.param and ....
order by t.orderfield) t2
where rownum < N)

Не работает так как в Oracle stff inline view не может быть коррелированным

Единственное остается попытаться как то приджойнить подзапрос к основному запросу наподобие предложенному
-2-
select dr.r, d.*
from all_objects d
join (select owner, object_name, object_type, row_number() over(order by owner desc, object_name) r from all_objects) dr
on (d.owner, d.object_name, d.object_type) = ((dr.owner, dr.object_name, dr.object_type)) and dr.r <=10
;

но пока не придумал универсальный вариант подходящий для любых запросов
19 апр 11, 22:02    [10538775]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли эмулировать TOP MSSQL на ORACLE одним запросом без подзапросов  [new]
-2-
Member

Откуда:
Сообщений: 15330
пройден один уровень игры инлайн-корреляция
create or replace function topNfilter(searchValue number, query sys_refcursor, limit number := 10) 
   return number as
   type t_values is table of number index by binary_integer;
   q_values t_values;
   res      number;
begin
   fetch query bulk collect into q_values limit limit;
   res := 0;
   for i in 1..q_values.count loop
      if q_values(i) = searchValue then
         res := i;
      end if;
   end loop;
   close query;
   return res;
end;
/

select rownum, object_id
from (select object_id from DBA_objects where object_name like 'X'||'%' order by owner, object_name)
where rownum <= 10;

ROWNUM                 OBJECT_ID              
---------------------- ---------------------- 
1                      55647                  
2                      55820                  
3                      55841                  
4                      118873                 
5                      118876                 
6                      81978                  
7                      64917                  
8                      64945                  
9                      64943                  
10                     57806                             

select *
from dual d
where topNfilter(81978, cursor(select object_id keyValue from dba_objects where object_name like d.dummy||'%' order by owner, object_name),  5) > 0;

DUMMY 
----- 
     

select *
from dual d
where topNfilter(81978, cursor(select object_id keyValue from dba_objects where object_name like d.dummy||'%' order by owner, object_name), 10) > 0;

DUMMY 
----- 
X    

20 апр 11, 01:21    [10539278]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли эмулировать TOP MSSQL на ORACLE одним запросом без подзапросов  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18351
-2-
create or replace function topNfilter

read consistency уже не модно?
20 апр 11, 02:15    [10539313]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли эмулировать TOP MSSQL на ORACLE одним запросом без подзапросов  [new]
-2-
Member

Откуда:
Сообщений: 15330
andrey_anonymous
read consistency уже не модно?
Почему же не модно. Тут их навалом.
20 апр 11, 04:56    [10539358]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли эмулировать TOP MSSQL на ORACLE одним запросом без подзапросов  [new]
Переходящий_c_MSSQL
Guest
-2-
Подход интересный, но не универсальный, так как в запросе могут быть выражения с операциями сравнения <,=,> и т.д.
плюс выражения и функции связки элементов верхнего запроса с подзапросом.

Попытался использовать другой подход - (умножаем данные верхнего уровня на значение показателя Top N в данном примере 10
и с помощью синтетической функции зависящей от типа аргумента в данном случае anytable1.lvl*1000000+Factor.lvl записываем два показателя в одно выражение )

Пример для числового значения связки:

Select *
FROM (select level lvl from dual connect by level < 100) anytable1 cross join  (select level lvl from dual connect by level < 10) Factor
Where anytable1.lvl*1000000+Factor.lvl 
      In (
Select (anytable2.lvl)*1000000
       +row_number() over(order by anytable2.lvl) 
       AS ROWNUMBER
FROM (select level lvl from dual connect by level < 100) anytable2
where anytable1.lvl=anytable2.lvl
)

Почему то не работает - возвращает 0 записей
Если убрать where anytable1.lvl=anytable2.lvl то работает нормально
Кто может подсказать в чем тут дело?
20 апр 11, 09:06    [10539553]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли эмулировать TOP MSSQL на ORACLE одним запросом без подзапросов  [new]
Переходящий_c_MSSQL
Guest
По поводу ошибки в предыдущем сообщении ни у кого мыслей нет?
Может это глюк конкретной версии Oracle?
20 апр 11, 10:52    [10540043]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3   вперед  Ctrl      все
Все форумы / Oracle Ответить