Все справедливо считают, что конструкция ORDER BY расходует ресурсы на проведение сортировки результата и в итоге мы должны получить результат несколько позже. Всегда ли это так?..
Давайте представим простой тривиальный запрос:
SET echo OFF
SET linesize 192
SET pagesize 0
SET TRIM ON
SET trims ON
SET feedback OFF
SET heading OFF
SET term OFF
SET TIME ON
SET timing ON
SET autot ON stat
spool s.txt
SELECT clnt_clnt_id,
name,
start_date,
end_date
FROM client_histories;
spool OFF
exit
Вроде всё просто:
- делается выборка из таблицы
- результат выгоняется в файл
- результат на терминал не выводится
- в конце запроса отображается время и статистика
Теперь взглянем на статистику:
Затрач.время: 00:00:17.97
Статистика
----------------------------------------------------------
0 recursive calls
0 db block gets
6515 consistent gets
0 physical reads
0 redo size
14182576 bytes sent via SQL*Net to client
242558 bytes received via SQL*Net from client
22012 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
330154 rows processed
А теперь представим, что данные нам надо упорядочить. Вопрос — что будет со временем? Первое мнение — сортировка займёт некое время и результат придёт позже. Что-ж выполняем:
SET echo OFF
SET linesize 192
SET pagesize 0
SET TRIM ON
SET trims ON
SET feedback OFF
SET heading OFF
SET term OFF
SET time ON
SET timing ON
SET autot ON stat
spool s1.txt
SELECT clnt_clnt_id ,
name ,
start_date ,
end_date
FROM client_histories
ORDER BY 1,
2;
spool OFF
exit
Теперь взглянем на статистику:
Затрач.время: 00:00:16.92
Статистика
----------------------------------------------------------
0 recursive calls
0 db block gets
6115 consistent gets
0 physical reads
0 redo size
13166047 bytes sent via SQL*Net to client
242558 bytes received via SQL*Net from client
22012 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
330154 rows processed
Оказывается, что в случае использования order by результат мы получаем быстрее. В статистике имеем только два отличия — время, затраченное на операцию, и количество информации, передаваемой по SQL*Net.
Напрашивается один вывод — операция сортировка проходит быстрее на 33000 строк, чем пересылка по существующему каналу 992 kb данных.
Но вот откуда взялась разница?..
А всё дело в том, что данные, пересылаемые по sql*net, сжимаются и сжимаются буферами. На это влияют размер SDU в TNS описании SQL*Net, а так же размер буфера, настраиваемого в SQL*Plus посредством параметра ARRAYSIZE, который по-умолчанию равен 15. Если данные отсортированы, то в буфере больше одинаковых данных и процент сжатия выше. Т.о. передаётся меньше данных по SQL*Net.
Давайте проэксперементируем, а именно — внесём небольшое изменение во второй скрипт:
SET autot ON stat
SET arraysize 5000
spool s1.txt
Теперь мы увеличили размер буфера до 5000 (это максимум) и выполнили запрос с сортировкой. Взглянем на результат:
Затрач.время: 00:00:06.47
Статистика
----------------------------------------------------------
0 recursive calls
0 db block gets
6115 consistent gets
0 physical reads
0 redo size
11278863 bytes sent via SQL*Net to client
1174 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
330154 rows processed
- Мы сократили кол-во информации, передаваемое на клиента ещё на 1.8Mb
- Мы сократили кол-во информации, передаваемой от клиента в 200 раз (на 235кб)
- Мы сократили количество roundtrip (запросов между клиентом и сервером SQL*Net) в 300 раз (с 22012 до 68).
Итого: благодаря увеличению буфера мы сокращаем кол-во roundtrip при передаче данных и это практически всегда положительно скажется на больших запросах. Но, что интересно, на медленных каналах связи (например 1 м/бит и медленнее) даже обычная сортировка данных может положительным образом сказаться на результате доставки запроса.
Да, и по уровню сжатия. Пусть у вас данные подготовлены следующим образом:
CREATE TABLE tbl0 AS
SELECT object_name,
object_id,
min(object_id) over (partition BY object_name) AS min_object_id
FROM dba_objects;
CREATE TABLE tbl1 AS SELECT DISTINCT object_name,
object_id
FROM tbl0
WHERE object_id = min_object_id;
CREATE TABLE tbl2 AS SELECT object_name FROM tbl1 WHERE rownum < 1;
BEGIN
FOR i IN 1..20 LOOP
INSERT INTO tbl2 SELECT object_name
FROM tbl1
ORDER BY reverse(object_id||object_name);
END LOOP;
COMMIT;
END;
Теперь сравним статистику для ARRAYSIZE 5000 по запросам:
SELECT object_name
FROM tbl2;
SELECT object_name
FROM tbl2
ORDER BY 1;
получаем следующую статистику:
Статистика
----------------------------------------------------------
0 recursive calls
0 db block gets
4992 consistent gets
0 physical reads
0 redo size
34152895 bytes sent via SQL*Net to client
3088 bytes received via SQL*Net from client
250 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1242280 rows processed
Статистика
----------------------------------------------------------
167 recursive calls
16 db block gets
5211 consistent gets
16377 physical reads
0 redo size
7629058 bytes sent via SQL*Net to client
3088 bytes received via SQL*Net from client
250 SQL*Net roundtrips to/from client
21 sorts (memory)
4 sorts (disk)
1242280 rows processed
Как мы видим, при ARRAYSIZE 5000 все 1.2 миллиона строк перекачиваются за одинаковое количество roundtrip-ов, т.е. влияние задержек SQL*Net на запрос/ответ будет приблизительно одинакова, зато объем информации по отсортированным данным 7.3 мб против 32.5 мб для не отсортированных. Т.о. при предварительной сортировке повторяющихся данных мы сократили объем трафика по сети в 4.5 раза, что очень существенно на медленных каналах связи.
Автор: softwind
