Oracle - ORDER BY

[GeneDupe]

Вопрос такой:

Предположим есть таблица (ID, NAME):
1 A
2 B
3 C
4 C

Такой запрос: SELECT * FROM table ORDER BY NAME
Можем вернуть записи в таком порядке:
1 A
2 B
3 C
4 C

А может по идее и в таком:
1 A
2 B
4 C
3 C

Гарантирует ли ORACLE что записи всегда (при неоднократном исполнении одного и того же запроса) возвращаются в одинаковом порядке, если используется ORDER BY?

[vc]

Вопрос такой:

Предположим есть таблица (ID, NAME):
1 A
2 B
3 C
4 C

Такой запрос: SELECT * FROM table ORDER BY NAME
Можем вернуть записи в таком порядке:
1 A
2 B
3 C
4 C

А может по идее и в таком:
1 A
2 B
4 C
3 C

Гарантирует ли ORACLE что записи всегда (при неоднократном исполнении одного и того же запроса) возвращаются в одинаковом порядке, если используется ORDER BY?

No, it does not.

1. Parallel query can return rows with ids 4 and 3 in any order.
2. For the IOT, if the primary key is modified, the order (4,3) may be different.
3. If the table is partitioned and the partition key is modified, the order (4,3) may be different.

Code: Select all

create table t1(id int, name varchar2(20), dt date)
   partition by range(dt) (
    partition part1 values less than ( to_date( '10-mar-2004', 'dd-mon-yyyy') ),
    partition part2 values less than ( to_date( '15-mar-2004', 'dd-mon-yyyy') )
   ) enable row movement;
   
insert into t1 values(1, 'A', trunc(sysdate)-10);
insert into t1 values(2, 'B', trunc(sysdate)-9);
insert into t1 values(3, 'C', trunc(sysdate)-2);
insert into t1 values(4, 'C', trunc(sysdate)-1);

select * from t1 order by name;

ID   NAME   DT
1   A   3/4/2004
2   B   3/5/2004
3   C   3/12/2004
4   C   3/13/2004

update t1 set dt=trunc(sysdate)-10 where id=4;
select * from t1 order by name;

ID   NAME   DT
1   A   3/4/2004
2   B   3/5/2004
4   C   3/4/2004
3   C   3/12/2004

The question itself does not make much sense. If you want a 'repeatable' order, then you need to disambiguate the order by clause.

VC

[GeneDupe]

Спасибо за исчерпывающий ответ! Я в принципе так и думал.

Мне нужно написать запрос, возращающий данные постранично для web приложения, типа такого:

Code: Select all

SELECT * FROM (SELECT ID, NAME, ROWNUM row_id FROM (SELECT ID, NAME FROM T1 ORDER BY NAME, ID)) WHERE row_id >= startFrom AND row_id < startFrom + 10

Т.е.я добавил ID в ORDER BY, хотя это и не требуется business requirements, но это будет гарантировать одинаковый порядок записей при повторных запросах.

Будет ли это (постраничная выборка) работать в таком виде?

[vc]

.......

Мне нужно написать запрос, возращающий данные постранично для web приложения, типа такого:

Code: Select all

SELECT * FROM (SELECT ID, NAME, ROWNUM row_id FROM (SELECT ID, NAME FROM T1 ORDER BY NAME, ID)) WHERE row_id >= startFrom AND row_id < startFrom + 10

Будет ли это (постраничная выборка) работать в таком виде?

Sure, why not ? This, however, will be faster:

Code: Select all

select * 
from ( select a.*, rownum row_id
       from ( SELECT ID, NAME FROM T1 ORDER BY NAME, ID ) a
       where rownum < startFrom + 10 
     )
where row_id >= startFrom

[JustMax]

А так еще на порядок быстрее при условии, что на NAME, ID создан избыточный уникальный ключ

SELECT /* +INDEX_(ASC | DESC)(t1.your_unique_key) */NAME, ID FROM T1 t1 where ID > lastID and rownum <= pageSize

[Dmitry67]

А так еще на порядок быстрее при условии, что на NAME, ID создан избыточный уникальный ключ

SELECT /* +INSEX_(ASC | DESC)(t1.your_unique_key) */NAME, ID FROM T1 t1 where ID > lastID and rownum <= pageSize

Толькос осторожностью отнестес к этим советам если потом будете портировать на другие базы
В пиведенном операторе стандартного примерно поцентов пять

[GeneDupe]

Всем спасибо!

Мне и самый простой вариант даст не плохое ускорение, т.к. тут индус так красиво напрограмировал - все 40.000 записей считываются да еще и два раза копируются в памяти, каждый раз когда надо показать страницу из 10 зап.

[vc]

А так еще на порядок быстрее при условии, что на NAME, ID создан избыточный уникальный ключ

SELECT /* +INSEX_(ASC | DESC)(t1.your_unique_key) */NAME, ID FROM T1 t1 where ID > lastID and rownum <= pageSize

Толькос осторожностью отнестес к этим советам если потом будете портировать на другие базы
В пиведенном операторе стандартного примерно поцентов пять

Here's 100% standard solution (SQL-99) which works under DB2 as well as under Oracle and is as efficient as the first posted solution:

Code: Select all

select *
from ( select id, name, row_number() over(order by name,id) rn from t1) 
where rn between startFrom and startFrom + 10

Yukon presumably should have it too, eventually ...

VC

[Dmitry67]

row_number over ? не знал что это в стандарте
В SQL такого нет или я не знаю
tengiz ?

P.S.
Кроме того в MS SQL inline view надо заключать в двойные скобки
А в конце обязательно писать имя view
select * from ((select ...)) XXX

[JustMax]

А так еще на порядок быстрее при условии, что на NAME, ID создан избыточный уникальный ключ

SELECT /* +INDEX_(ASC | DESC)(t1.your_unique_key) */NAME, ID FROM T1 t1 where ID > lastID and rownum <= pageSize

Толькос осторожностью отнестес к этим советам если потом будете портировать на другие базы
В пиведенном операторе стандартного примерно поцентов пять

Here's 100% standard solution (SQL-99) which works under DB2 as well as under Oracle and is as efficient as the first posted solution:

Code: Select all

select *
from ( select id, name, row_number() over(order by name,id) rn from t1) 
where rn between startFrom and startFrom + 10

Yukon presumably should have it too, eventually ...

VC

Не совсем - внутренний селект все равно считает все записи, хотя уже и без сортировки, а внешний наложит фильтр.(Хотя, возможно оптимизатор и разрулит), а в моем примере pageSize строк будут моментально считаны за идин проход без фильтрации. Но да - это не портабельно. Но тут ведь как всегда - или шашечки или ехать.

[vc]

...........
Кроме того в MS SQL inline view надо заключать в двойные скобки
А в конце обязательно писать имя view
select * from ((select ...)) XXX

1. The correlation name is supported but not enforced by Oracle. The standard does require a correlation name for the derived table.

2. The double parenthesis are NOT required by the standard.

VC