Два КСлова про Oracle — пересечение множеств

— А какой самый главный прибор в кораблевождении?
— Голова, — отвечали педагоги...

Валентин Пикуль, «Мальчики с бантиками»

Заметка эта пишется по горячим следам, после трёх часов потерянного времени. Надеюсь, что пятнадцать минут, затраченные на её написание, сэкономят кому-нибудь эти самые три часа. Или хотя бы час.

Итак, дано: два списка объектов. Каждый объект идентифицируется номером (serial number), состоящим из 20 цифр. Номер лежит в поле типа varchar2, причём в первой таблице лежит список номеров, а во втором — список диапазонов:

create table theory(
  serial varchar2(20)
);

create table practice(
  serial_from varchar2(20),
  serial_to varchar2(20),
);

create index x_practice_serial on practice(
  serial_from,
  serial_to
);

В первой таблице около полутора миллионов записей, во второй — около четырёх тысяч.

Как найти пересечение этих списков? Пытливый ум быстренько напишет что-нибудь вроде

select --+ parallel(t,8) ordered use_nl(p)
  t.*
from
  theory t, practice p
where
  t.serial between p.serial_from and p.serial_to;

Есть ли криминал в этом запросе? На первый взгляд, нет. И действительно, выполняется он сравнительно быстро, особенно если пересечение невелико.

Теперь попробуем найти те объекты, которые должны быть в теории, но которых нет на практике. Окрылённый успехом, пытливый ум пишет следующее:

select --+ parallel(t,8)
  t.*
from
  theory t
where
  not exists (
    select * from practice 
    where t.serial between serial_from and serial_to
  );

Запустив этот запрос, программист уходит пить чай. Или курить. Или есть пиццу. Когда чай выпит, сигарета выкурена, а пицца съедена, он возвращается к голубому экрану и видит, что запрос продолжает работать. После этого начинается нервное раскладывание пасьянса, поглядывание на часы и ходьба по комнате. Параллельно обдумывается вопрос, как получить список объектов, которые есть на практике, но которых не должно быть в теории, и ответа на этот вопрос нет.

Что же надо сделать, чтобы не терять время? Добавить памяти? Добавить процессоров? Нет. Включить голову.

Итак, шаг первый: развернуть список диапазонов в список объектов:

create table practice_list(
  serial varchar2(20)
);

declare
  i number;
begin
  for c in (
    select to_number(serial_from) as i1, to_number(serial_to) as i2
    from practice
  ) loop
    i := c.i1;
    while i<=c.i2 loop
      insert into practice_list values(lpad(ltrim(to_char(i)),20,'0'));
      i := i+1;
    end loop;
    commit;
  end loop;
end;

Для действительно пытливых умов пояснения к коду не требуются. Для остальных замечу, что использование ltrim() обусловлено тем, что to_char() оставляет слева пробел для знака. Использование же while вместо for i in c.i1..c.i2 нужно потому, что ведущая переменная цикла имеет тип pls_integer, и 20-значное число в него не влезает.

Ну, а дальше всё очевидно:

select /*+ parallel(t,4) */ serial from theory t
minus
select /*+ parallel(p,4) */ serial from practice_list p;

Как выяснилось, этот запрос работает существенно быстрее, чем запрос с not exists. Хотя, если подумать перед тем, как писать запросы, можно было и не ставить столь бесчеловечных экспериментов над сервером и собственным терпением.

Контрольный вопрос: какая из компонент аппаратуры сильнее всего влияет на скорость исполнения приложений?

6.10.2003