Мифы SQL. Продолжение.

Недавно опубликованная на хабре статья Мифы SQL хабаролюдям понравилась. И многие попросили привести примеры почему все работает именно так и не иначе.


Весь написанный код работает и проверен на

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 — Prod
PL/SQL Release 10.2.0.4.0 — Production
CORE   10.2.0.4.0   Production
TNS for 32-bit Windows: Version 10.2.0.4.0 — Production
NLSRTL Version 10.2.0.4.0 — Production

Executed in 0,094 seconds* This source code was highlighted with Source Code Highlighter.

Бесплатную версию Оракла можно взять тут.

К вопросу о переносимости кода. Посмотрите, попробуйте запустить и получить такие же зультаты на вашей любимой СУБД. Все вопросы о переносимости сразу отпадут.

Магический параметр где-нибудь глубоко в конфигах сервера fast = true
Можно взять и оптимизировать любой отдельный запрос, или даже часть запроса не трогая ничего кроме него
Сам вопрос что именно надо сделать чтобы ускорить работу БД не имеет смысла.
Как правило надо пересматривать структуру таблиц, переписывать запросы, переделывать индексы. Нужен целый комплекс мер. Нет никаких магических действий.

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

Во временных таблицах не надо делать ни Primary Key, ни индексов
Временные таблицы ни чем не хуже обычных. Если мы используем временную таблицу чтобы запустить по ней какой-либо алгоритм, то действительно индексы будут только вредить. Но бывает что временная таблица используется как самая обычная, то есть мы кладем в нее данные потом делаем к ней много разных запросов. В этом случае PK и индексы сильно облегчат жизнь.

Внешний ключ = Индекс по полю в дочерней таблице
Очень хорошая статья на эту тему
Основные выводы оттуда:

Проблемы, которые могут возникнуть при наличии неиндексированных внешних ключей. 
•	большая вероятность возникновения взаимных блокировок.
•	при удалении из основной таблицы, если установлено ограничение on delete cascade, вызывается каскадное удаление из починенной таблицы. И если нет индекса на внешний ключ, то Оракл вынужден делать полный просмотр починенной таблицы. А это не есть хорошо - требуются большие ресурсы и значительные расходы времени.
•	для запроса из главной таблицы в починенную, при наличии в конструкции where условия на ключ, будет значительное замедление работы.
Можно не беспокоиться о наличии индекса на внешний ключ при следующих условиях: 
•	никогда записи из главной таблицы не удаляются.
•	Никогда не изменяется значение уникального, первичного ключа.
•	Никогда в запросах главная и подчиненная таблица не соединяются.

Дополнение от меня к пункту «Никогда в запросах главная и починенная таблица не соединяются.»
Имеются в виду только запросы вида

SELECT *
FROM table1 t1
INNER JOIN table2 t2 ON t1.id = t2.id* This source code was highlighted with Source Code Highlighter.

Если у нас все запросы вида

SELECT *
FROM table1 t1
INNER JOIN table2 t2 ON t1.id = t2.id
WHERE t1.code = 'A'
   AND t2.code = ‘B’* This source code was highlighted with Source Code Highlighter.

То будет гораздо эффективнее создать составной индекс (id, code) и автоматически созданный (id) будет только мешать.

Запрос с меньшей стоимостью обязан работать быстрее
Лучше Тома Кайта я не напишу.
Первая ссылка
Вторая ссылка
По ссылкам лежит одно и тоже, на всякий случай если вдруг один из серверов исчезнет.

Абсолютная идентичность следующих вариантов кода
Просто запустим этот код.

— Create table
create table TABLE1
(
 ID NUMBER(13) not null
)
tablespace USERS
 pctfree 10
 initrans 1
 maxtrans 255
 storage
 (
  initial 64K
  minextents 1
  maxextents unlimited
 );

insert into table1
select rownum
from dual
connect by level < 100000

SQL> declare
 2  summ number(10);
 3  i number(10);
 4  cursor c is select id from table1;
 5 begin
 6  summ := 0;
 7  open c;
 8 
 9  loop
10   fetch c into i;
11   exit when c%notfound;
12 
13   summ := summ + i;
14  end loop;
15 
16  close c;
17 
18  dbms_output.put_line(summ);
19 
20 end;
21 /

PL/SQL procedure successfully completed

Executed in 1,515 seconds

SQL> select sum(id) from table1;

  SUM(ID)
----------
4999950000

Executed in 0,031 seconds* This source code was highlighted with Source Code Highlighter.

Разница в 50 раз.

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

Это подверждается на простеньком примере:

— Create table
create table TABLE1
(
 ID NUMBER(13) not null
)
tablespace USERS
 pctfree 10
 initrans 1
 maxtrans 255
 storage
 (
  initial 64K
  minextents 1
  maxextents unlimited
 );
— Create/Recreate indexes
create index IX_TABLE1 on TABLE1 (ID)
 tablespace USERS
 pctfree 10
 initrans 2
 maxtrans 255
 storage
 (
  initial 64K
  minextents 1
  maxextents unlimited
 );

insert into table1
select rownum
from dual
connect by level < 1000000;

SQL> select /*+ index(table1 IX_TABLE1) */
 2 COUNT(*) from table1;

 COUNT(*)
----------
  1099998

Executed in 0,172 seconds

SQL>
SQL> select /*+ no_index(table1 IX_TABLE1) */
 2  COUNT(*) from table1;

 COUNT(*)
----------
  1099998

Executed in 0,063 seconds* This source code was highlighted with Source Code Highlighter.

Разница в 2.7 раза.

Запрос работает медленно, надо добавить индекс
Добавление индексов помогает, но это не панацея. В какой-то момент INSERT и UPDATE начнут работать очень медленно из-за большого числа индексов, а изменить что-либо будет уже очень сложно. Придется переделывать большую часть запросов. Лучше сразу писать так, чтобы такая ситуация не возникла.

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

Создаем таблицы

— Create table
create table TABLE1
(
 ID  NUMBER(13) not null,
 CODEA VARCHAR2(1024) not null,
 CODEB VARCHAR2(1024) not null
)
tablespace USERS
 pctfree 10
 initrans 1
 maxtrans 255
 storage
 (
  initial 64K
  minextents 1
  maxextents unlimited
 );
— Create/Recreate indexes
create index IX_TABLE1 on TABLE1 (ID)
 tablespace USERS
 pctfree 10
 initrans 2
 maxtrans 255
 storage
 (
  initial 64K
  minextents 1
  maxextents unlimited
 );
create index IX1_TABLE1 on TABLE1 (CODEA, CODEB)
 tablespace USERS
 pctfree 10
 initrans 2
 maxtrans 255
 storage
 (
  initial 64K
  minextents 1
  maxextents unlimited
 );

— Create table
create table TABLE2
(
 ID  NUMBER(13) not null,
 CODEA VARCHAR2(1024) not null,
 CODEB VARCHAR2(1024) not null
)
tablespace USERS
 pctfree 10
 initrans 1
 maxtrans 255
 storage
 (
  initial 64K
  minextents 1
  maxextents unlimited
 );
— Create/Recreate indexes
create index IX_TABLE2 on TABLE2 (ID)
 tablespace USERS
 pctfree 10
 initrans 2
 maxtrans 255
 storage
 (
  initial 64K
  minextents 1
  maxextents unlimited
 );
create index IX2_TABLE2 on TABLE2 (CODEA, CODEB)
 tablespace USERS
 pctfree 10
 initrans 2
 maxtrans 255
 storage
 (
  initial 64K
  minextents 1
  maxextents unlimited
 );* This source code was highlighted with Source Code Highlighter.

Заполняем

insert into table1
select rownum, rownum, rownum
from dual
connect by level < 100000;

insert into table2
select rownum, rownum, rownum
from dual
connect by level < 100000;* This source code was highlighted with Source Code Highlighter.

Проверяем

SQL>
SQL> select count(*)
 2 from table1 t1
 3 INNER JOIN table2 t2 ON t2.codea = t1.codea
 4            AND t2.codeb = t2.codeb;

 COUNT(*)
----------
   99999

Executed in 0,078 seconds

SQL>
SQL> select count(*)
 2 from table1 t1
 3 INNER JOIN table2 t2 ON t2.id = t1.id;

 COUNT(*)
----------
   99999

Executed in 0,079 seconds* This source code was highlighted with Source Code Highlighter.

Разброс значений в пределах погрешности.

При запросе с фильтром по 2 полям работают оба индекса
Том Кайт писал про то когда используется несколько индексов.
Первая ссылка
Вторая ссылка

Скорость работы запроса оценивается по тому как быстро вывелись первые 20 строк
Для проверки этого мифа нужно довольно сложное окружение. Более простой пример приветствуется в коментариях.

Создаем таблицы:

— Create table
create table TABLE_SMALL
(
 ID  NUMBER(13) not null,
 D_BEG DATE not null,
 D_END DATE not null
)
tablespace USERS
 pctfree 10
 initrans 1
 maxtrans 255
 storage
 (
  initial 64K
  minextents 1
  maxextents unlimited
 );
— Create/Recreate primary, unique and foreign key constraints
alter table TABLE_SMALL
 add constraint PK_SMALL primary key (ID)
 using index
 tablespace USERS
 pctfree 10
 initrans 2
 maxtrans 255
 storage
 (
  initial 64K
  minextents 1
  maxextents unlimited
 );
— Create/Recreate indexes
create index IX_SMALL on TABLE_SMALL (D_BEG, D_END)
 tablespace USERS
 pctfree 10
 initrans 2
 maxtrans 255
 storage
 (
  initial 64K
  minextents 1
  maxextents unlimited
 );

— Create table
create table TABLE_BIG
(
 ID NUMBER(13) not null,
 D DATE not null
)
tablespace USERS
 pctfree 10
 initrans 1
 maxtrans 255
 storage
 (
  initial 64K
  minextents 1
  maxextents unlimited
 );
— Create/Recreate primary, unique and foreign key constraints
alter table TABLE_BIG
 add constraint PK_BIG primary key (ID)
 using index
 tablespace USERS
 pctfree 10
 initrans 2
 maxtrans 255
 storage
 (
  initial 64K
  minextents 1
  maxextents unlimited
 );
— Create/Recreate indexes
create index IX_BIG on TABLE_BIG (D)
 tablespace USERS
 pctfree 10
 initrans 2
 maxtrans 255
 storage
 (
  initial 64K
  minextents 1
  maxextents unlimited
 );* This source code was highlighted with Source Code Highlighter.

Заполняем таблицы

insert into table_big t
(
SELECT rownum,
    TO_DATE('01.01.2000','dd.mm.yyyy') + (rownum — 1) / 24 AS thour
FROM dual
CONNECT BY LEVEL <=(TO_DATE('01.03.2000','dd.mm.yyyy') — TO_DATE('01.01.2000','dd.mm.yyyy')) * 24   
);

insert into table_small
values (1, TO_DATE('01.01.2000 01:00', 'dd.mm.yyyy HH24:MI'), TO_DATE('02.01.2000 01:00', 'dd.mm.yyyy HH24:MI'));

insert into table_small
values (2, TO_DATE('01.01.2000 01:00', 'dd.mm.yyyy HH24:MI'), TO_DATE('02.01.2000 01:00', 'dd.mm.yyyy HH24:MI'));

insert into table_small
values (3, TO_DATE('01.01.2000 01:00', 'dd.mm.yyyy HH24:MI'), TO_DATE('02.01.2000 01:00', 'dd.mm.yyyy HH24:MI'));

insert into table_small
values (4, TO_DATE('01.01.2000 01:00', 'dd.mm.yyyy HH24:MI'), TO_DATE('02.01.2000 01:00', 'dd.mm.yyyy HH24:MI'));

insert into table_small
values (5, TO_DATE('01.01.2000 01:00', 'dd.mm.yyyy HH24:MI'), TO_DATE('02.01.2000 01:00', 'dd.mm.yyyy HH24:MI'));

insert into table_small
values (6, TO_DATE('01.01.2000 01:00', 'dd.mm.yyyy HH24:MI'), TO_DATE('02.01.2000 01:00', 'dd.mm.yyyy HH24:MI'));

insert into table_small
values (7, TO_DATE('01.01.2000 01:00', 'dd.mm.yyyy HH24:MI'), TO_DATE('02.01.2000 01:00', 'dd.mm.yyyy HH24:MI'));

insert into table_small
values (8, TO_DATE('01.01.2000 01:00', 'dd.mm.yyyy HH24:MI'), TO_DATE('02.01.2000 01:00', 'dd.mm.yyyy HH24:MI'));

insert into table_small
values (9, TO_DATE('01.01.2000 01:00', 'dd.mm.yyyy HH24:MI'), TO_DATE('02.01.2000 01:00', 'dd.mm.yyyy HH24:MI'));

insert into table_small
values (10, TO_DATE('01.01.2000 01:00', 'dd.mm.yyyy HH24:MI'), TO_DATE('02.01.2000 01:00', 'dd.mm.yyyy HH24:MI'));

insert into table_small
values (11, TO_DATE('01.01.2000 01:00', 'dd.mm.yyyy HH24:MI'), TO_DATE('02.01.2000 01:00', 'dd.mm.yyyy HH24:MI'));

insert into table_small
values (12, TO_DATE('01.01.2000 01:00', 'dd.mm.yyyy HH24:MI'), TO_DATE('02.01.2000 01:00', 'dd.mm.yyyy HH24:MI'));
* This source code was highlighted with Source Code Highlighter.

Создаем типы и функции

create or replace type t_data as object
(
 id number(13),
 d date
);

create or replace type t_data_table as table of t_data;

create or replace function expand_small return t_data_table
pipelined
is
begin
   FOR v IN (
     SELECT
        s.id AS id,
        hours.thour AS tdate
     FROM
     (
      SELECT TO_DATE('01.01.2000', 'dd.mm.yyyy') + (rownum — 1) / 24 AS thour
      FROM dual
      CONNECT BY LEVEL <= 10 * 24 — 10 дней
     ) hours
     INNER JOIN table_small s ON s.d_beg BETWEEN TO_DATE('01.01.2000', 'dd.mm.yyyy') AND TO_DATE('10.01.2000', 'dd.mm.yyyy')
                      OR s.d_beg BETWEEN TO_DATE('01.01.2000', 'dd.mm.yyyy') AND TO_DATE('10.01.2000', 'dd.mm.yyyy')
                      OR s.d_beg < TO_DATE('01.01.2000', 'dd.mm.yyyy') AND s.d_end > TO_DATE('10.01.2000', 'dd.mm.yyyy')
   )
   LOOP
    PIPE ROW(t_data(v.id, v.tdate));
   END LOOP;
   RETURN;
end expand_small;

create or replace function expand_big return t_data_table
pipelined
is
begin
   FOR v IN (
    SELECT
       b.id,
       b.d as tdate
    FROM table_big b
    WHERE
       b.d BETWEEN TO_DATE('01.01.2000', 'dd.mm.yyyy') AND TO_DATE('10.01.2000', 'dd.mm.yyyy')
   )
   LOOP
    PIPE ROW(t_data(v.id, TO_DATE(v.tdate)));
   END LOOP;
   RETURN;
end expand_big;* This source code was highlighted with Source Code Highlighter.

Проверяем.
Нормальное поведение, на которое часто закладываются.

SQL>
SQL> select COUNT(*) from table_big
 2 where rownum < 10;

 COUNT(*)
----------
     9

Executed in 0,015 seconds

SQL>
SQL> select COUNT(*) from table_big
 2 where rownum < 200;

 COUNT(*)
----------
    199

Executed in 0,016 seconds
* This source code was highlighted with Source Code Highlighter.

COUNT(*) стоит чтобы исключить время необходимое на передачу данных на клиент и на отрисовку.

Время получения первых 10 строк и первых 200 строк отличается незначительно. И таких запросов большинство.

Но иногда бывает и так

SQL>
SQL> select COUNT(*) from table(expand_big) t
 2 where t.id not in
 3 (
 4 SELECT t1.id from table(expand_small) t1
 5 )
 6 and rownum < 10;

 COUNT(*)
----------
     9

Executed in 0,578 seconds

SQL>
SQL> select COUNT(*) from table(expand_big) t
 2 where t.id not in
 3 (
 4 SELECT t1.id from table(expand_small) t1
 5 )
 6 and rownum < 200;

 COUNT(*)
----------
    199

Executed in 12,063 seconds* This source code was highlighted with Source Code Highlighter.

Время отличается в 24 раза. И это не исключительный случай. Такое часто бывает при сложных запросах.