ROWNUM и почему его нельзя использовать в соединениях : Oracle

OlegON · █ 12.02.2019 18:50

Достаточно часто вижу запросы вроде

SQL код:

 select * from table where username='VASIA' and rownum<3

программисты почему-то полагают, что в этом случае они отберут две строки с Васей. Попробую объяснить на пальцах, почему это не совсем так.

Представим себе таблицу со столбцом username

VASIA
PETYA
MISHA
VASIA

вот те, кто думает, что отберется первая и последняя колонка, глубоко заблуждаются - запускаем запрос, сначала псевдоколонка rownum появится

1 VASIA
2 PETYA
3 MISHA
4 VASIA

и теперь отбираем по условию, а условие у нас rownum<3, отберется только

1 VASIA

Причем, порядок выборки из таблицы может быть совершенно неожиданный. Как и

SQL код:

 select * from table where username='VASIA' and rownum=4

тоже не имеет смысл.

В общем случае, чтобы меньше путаться, ограничение по количеству строк лучше выносить через подзапрос, т.е. в нашем случае лучше переделать так

SQL код:

 select * from (select * from table where username='VASIA') where rownum<3

и просто запомните, что rownum в соединениях условий использовать нельзя. Такие глюки вылезают, что диву даешься.

YuraZ · █ 13.02.2019 11:21

Ну что же, протестируем :)

Создаем и заполняем таблицу:

Код:

create table rownumtest(name varchar2(1000));
insert into rownumtest(name) values('ВАСЯ');
insert into rownumtest(name) values('ПЕТЯ');
insert into rownumtest(name) values('ГЕНА');
insert into rownumtest(name) values('ВАСЯ');
insert into rownumtest(name) values('ПЕТЯ');
insert into rownumtest(name) values('ГЕНА');
insert into rownumtest(name) values('ВАСЯ');
insert into rownumtest(name) values('ПЕТЯ');
insert into rownumtest(name) values('ГЕНА');
commit;

Делаем тестовую выборку:

Код:

select rownum, t.name
from rownumtest t

получаем:
ROWNUM,NAME
1,ВАСЯ
2,ПЕТЯ
3,ГЕНА
4,ВАСЯ
5,ПЕТЯ
6,ГЕНА
7,ВАСЯ
8,ПЕТЯ
9,ГЕНА

Усложним выборку:

Код:

select rownum, t.name
from rownumtest t
where name = 'ВАСЯ'

получаем:
ROWNUM,NAME
1,ВАСЯ
2,ВАСЯ
3,ВАСЯ

Ну и финальный запрос:

Код:

select rownum, t.name
from rownumtest t
where name = 'ВАСЯ' and rownum <= 2

в результате:
ROWNUM,NAME
1,ВАСЯ
2,ВАСЯ

Можно еще усложнить:

Код:

select rownum, t.name
from rownumtest t
where name in ('ВАСЯ', 'ГЕНА')

получаем:
ROWNUM,NAME
1,ВАСЯ
2,ГЕНА
3,ВАСЯ
4,ГЕНА
5,ВАСЯ
6,ГЕНА

Ну и наконец:

Код:

select rownum, t.name
from rownumtest t
where name in ('ВАСЯ', 'ГЕНА') and rownum <= 2

получаем:
ROWNUM,NAME
1,ВАСЯ
2,ГЕНА

Я конечно не очень дружу с английским, но вроде как Том Кайт пишет однозначно ():

select ..., ROWNUM
from t
where <where clause>
group by <columns>
having <having clause>
order by <columns>;
Think of it as being processed in this order:

1. The FROM/WHERE clause goes first.
2. ROWNUM is assigned and incremented to each output row from the FROM/WHERE clause.
3. SELECT is applied.
4. GROUP BY is applied.
5. HAVING is applied.
6. ORDER BY is applied.

Т.е. как я понимаю, сначала отрабатывают условия в FROM и WHERE, а потом уже идет нумерация.

Ну и сакраментальный вопрос - "Что не так?"

OlegON · █ 13.02.2019 12:18

Да, накосячил я с примером, согласен. ROWNUM действительно увеличивается только после обработки WHERE.
Однако, как факт, соединения вида

SQL код:

 select * from table where ... and rownum<Х

глючные и их надо избегать. Конкретный пример я тебе сейчас не приведу, но я точно на это налетал.

YuraZ · █ 13.02.2019 12:38

У меня тоже так бывает. Долго ищешь проблему. Наконец находишь - проблема в конструкции с rownum. А разбираться в чем конкретно проблема нет времени. Главное, что проблема найдена - это rownum. И в памяти откладывается - "rownum глючный и нужно избегать его использования". И спустя годы ты уже не помнишь в чем была проблема, но четко помнишь - "rownum глючный". Но на самом деле - rownum не глючный. Он работает в рамках заявленного. Просто нужно выполнять определенные правила. Возможно, что в твоем случае, было соединение нескольких таблиц со сложным условием. И в результате, rownum повел себя не так как ожидал разработчик. Но от этого он не стал глючным!
А тема с выборкой первых N строк, уже давно стала хрестоматийной. На каждом углу цитируется статья Тома Кайта, где все детально расписано.

OlegON · █ 13.02.2019 13:51

Сейчас немного некогда глубоко погружаться, начал гуглить, как я понял, такое поведение, как я описываю, определялось какими-то финтами оптимизатора и то ли было багом, то ли нет, проявляется на каких-то версиях сервера, как минимум.