Весь написанный код работает и проверен на: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0. Бесплатную версию Оракла можно взять у самого Oracle
.
К вопросу о переносимости кода: попробуйте запустить и получить такие же результаты на вашей любимой СУБД. Все вопросы о переносимости сразу отпадут.
Сам вопрос — что именно надо сделать, чтобы ускорить работу БД, не имеет смысла. В данном случае, нужно решать в комплексе: СУБД и ОС.
Для проверки этого мифа нужно довольно сложное окружение. Более простой пример приветствуется в комментариях (П.Р.«ВУ»: речь о комментариях в блоге автора).
Создаем таблицы:
-- Создаем таблицу
CREATE TABLE table_small
(
id NUMBER(13) NOT NULL,
d_beg DATE NOT NULL,
d_end DATE NOT NULL
);
-- Создаем первичный ключ
ALTER TABLE table_small
ADD CONSTRAINT pk_small PRIMARY KEY (id)
USING INDEX;
-- Создаем индекс
CREATE INDEX ix_small ON table_small (d_beg, d_end);
-- Создаем таблицу
CREATE TABLE table_big
(
id NUMBER(13) NOT NULL,
d DATE NOT NULL
);
-- Создаем первичный ключ
ALTER TABLE table_big
ADD CONSTRAINT pk_big PRIMARY KEY (id)
USING INDEX;
-- Создаем индекс
CREATE INDEX ix_big ON TABLE_BIG (d);
Заполняем таблицы:
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 t
(
SELECT
ROWNUM,
TO_DATE('01.01.2000 01:00', 'DD.MM.YYYY HH24:MI'),
TO_DATE('02.01.2000 01:00', 'DD.MM.YYYY HH24:MI')
FROM DUAL
CONNECT BY LEVEL <= 12
);
Создаем типы и функции:
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;
Проверяем нормальное поведение, на которое часто закладываются:
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
Здесь стоит 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
Время отличается в 24 раза. И это не исключительный случай. Такое часто бывает при сложных запросах.
Как правило, надо пересматривать структуру таблиц, переписывать запросы, переделывать индексы. Нужен целый комплекс мер. Нет никаких магических действий.
Покажите мне план запроса, и тогда можно начинать думать. Без плана вообще ничего нельзя сказать про оптимальность работы.
Временные таблицы ни чем не хуже обычных. Если мы используем временную таблицу, чтобы запустить по ней какой-либо алгоритм, то действительно — индексы будут только вредить. Но бывает, что временная таблица используется как самая обычная, то есть мы помещаем в нее данные и потом обращаемся к ней посредством различных запросов. В этом случае первичный ключ и индексы сильно облегчат жизнь.
Очень хорошая статья на эту тему: «Внешние ключи. Индексировать или нет?»
[2]. Основные выводы оттуда:
Проблемы, которые могут возникнуть при наличии неиндексированных внешних ключей:
- Большая вероятность возникновения взаимных блокировок.
- При удалении из основной таблицы, если установлено ограничение ON DELETE CASCADE, вызывается каскадное удаление из починенной таблицы. И если нет индекса на внешний ключ, то Оракл вынужден делать полный просмотр починенной таблицы. А это не хорошо — требуются большие ресурсы и значительные расходы времени.
- Для запроса из главной таблицы в починенную при наличии в конструкции WHERE условия на ключ будет значительное замедление работы.
Можно не беспокоиться о наличии индекса на внешний ключ при следующих условиях:
- Никогда не удаляются записи из главной таблицы.
- Никогда не изменяется значение уникального, первичного ключа.
- В запросах главная и подчиненная таблица никогда не соединяются.
Дополнение от меня к пункту «в запросах главная и подчиненная таблица никогда не соединяются». Имеются в виду только запросы вида:
SELECT *
FROM table1 t1
INNER JOIN table2 t2 ON t1.id = t2.id
Если у нас все запросы вида:
SELECT *
FROM table1 t1
INNER JOIN table2 t2 ON t1.id = t2.id
WHERE t1.code = 'A'
AND t2.code = 'B'
то будет гораздо эффективнее создать составной индекс «(id, code)» и автоматически созданный «(id)» будет только мешать.
Это заблуждение от незнания алгоритмов стоимостного оптимизатора. Лучше Тома Кайта я не напишу — читайте «сравнение стоимости запросов»
[3].
Цитата оттуда:
Нельзя сравнивать друг с другом стоимости двух запросов. Они просто не сопоставимы.
При получении запроса сервер оценивает множество планов его выполнения. С каждым шагом плана связывается некоторая относительная стоимость. Затем вычисляется функция, определяющая суммарную стоимость запроса для каждого плана. Эти стоимости, (доступные только оптимизатору) можно сравнивать, поскольку они вычислены для одного и того же запроса в одинаковой среде.
Просто запустим этот код:
-- Создаем таблицу
CREATE TABLE table1
(
id NUMBER(13) NOT NULL
);
-- Заполняем таблицу
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
Разница по времени в 50 раз.
Для запросов, которые возвращают большую часть таблицы, доступ по индексу будет медленнее полного сканирования таблицы. Это подверждается на простеньком примере:
-- Создаем таблицу
CREATE TABLE table1
(
id NUMBER(13) NOT NULL
);
-- Создаем индекс
CREATE INDEX ix_table1 ON table1 (id);
-- Заполняем таблицу
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
Разница по времени в 2.7 раза.
Добавление индексов помогает, но это не панацея. В какой-то момент INSERT и UPDATE начнут работать очень медленно из-за большого числа индексов, а изменить что-либо будет уже очень сложно. Придется переделывать большую часть запросов. Лучше сразу писать так, чтобы такая ситуация не возникла.
Проверим скорость соединения таблиц по id и тех же самых таблиц по паре текстовых полей. Создаем таблицы:
-- Создаем таблицу
CREATE TABLE table1
(
id NUMBER(13) NOT NULL,
codea VARCHAR2(1024) NOT NULL,
codeb VARCHAR2(1024) NOT NULL
);
-- Создаем индекс
CREATE INDEX ix_table1 ON table1 (id);
CREATE INDEX ix1_table1 ON table1 (codea, codeb);
-- Создаем таблицу
CREATE TABLE table2
(
id NUMBER(13) NOT NULL,
codea VARCHAR2(1024) NOT NULL,
codeb VARCHAR2(1024) NOT NULL
);
-- Создаем индекс
CREATE INDEX ix_table2 ON table2 (id);
CREATE INDEX ix2_table2 ON table2 (codea, codeb);
-- Заполняем таблицы
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;
Проверяем:
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
Разброс значений в пределах погрешности.
Том Кайт писал про то, когда используется несколько индексов
[4].
Примечание редакции «ВУ»: на момент публикации ссылки на сайте www.ln.com.ua работали избирательно, по IP.