💡 Простой вопрос из собесов по SQL и индексам
(про план запросов, но с подвохом)

У нас есть таблица:

CREATE TABLE employees (
  fio            VARCHAR2(50) NOT NULL,
  department_id  NUMBER       NOT NULL,
  start_date     DATE         NOT NULL
);

CREATE INDEX employees_i01 ON employees (fio, department_id);

И вот такой запрос:

SELECT fio
FROM   employees
WHERE  fio = :f AND department_id = :dep_id;

На него отлично работает индекс employees_i01, ведь все условия входят в ключ.

Теперь добавим новый фильтр:

SELECT fio
FROM   employees
WHERE  fio = :f AND department_id = :dep_id AND start_date >= :d;

❓ Как изменится производительность запроса?
1️⃣ Станет быстрее
2️⃣ Станет медленнее
3️⃣ Ничего не изменится

⚠️ Хотите проверить скрипты, но нет базы под рукой - онлайн-песочница
💎 Поддержка канала⁉️

🤔 А как думаете вы?
📥 Пишите свои варианты и размышления в комментарии — обсудим!
#RealInterviewTasks

✍️ Ответ на вопрос по индексам:

Запрос:

SELECT fio
FROM employees
WHERE fio = :f AND department_id = :dep_id;
🔍 Отлично использует индекс employees_i01 (fio, department_id) — оба условия входят в его ключ, возможен index range scan с минимальным I/O.

Теперь добавляем:

AND start_date >= :d

💥 И тут может случиться подвох.

🧠 Что изменится? Индекс остался тем же, но появился дополнительный фильтр, не входящий в индекс. Oracle больше не сможет полностью удовлетворить запрос только по индексу — ему нужно будет идти в таблицу (table access by rowid), чтобы проверить start_date.

📉 Это добавляет дополнительный шаг (filter) и может замедлить выполнение. Особенно на больших таблицах с большим количество полей.

📌 Ответ: 2️⃣ Станет медленнее.

💎 Поддержка канала⁉️
#RealInterviewTasks

Анализируйте, пользуйтесь, ёшкин кот!!!👋

В прошлом посте мы разбирали, как обновлять разные наборы полей в одной процедуре с помощью значений-заглушек.
Это рабочий, но не идеальный метод.
Но прежний метод колхоз, по сравнению с текущим методом.
Универсального механизм на базе ключ-значение!

⚠️ Метод сложный, но он Вас обогатит во всех смыслах.
Много чему научитесь!

✨ Как это работает?

Вместо того чтобы передавать 50-100 параметров, мы используем специальную структуру данных, которая может хранить разные типы значений.

Тип-вариант (t_variant): Мы создаем объект, который умеет хранить числа, строки, даты и другие типы данных. Внутри него — разные поля, но за раз используется только одно.

Ассоциативный массив: Мы определяем константы (например, number_field= 1, varchar2_field = 5) для каждого поля в таблице. Затем создаем коллекцию или ассоциативный массив, где ключ — это номер поля, а значение — наш t_variant.

Универсальный метод (set_attribute): В процедуре мы просто добавляем в этот массив только те поля, которые нужно обновить.

Применение изменений (apply_changes): Отдельная процедура получает этот массив и обновляет только те столбцы, которые в нём есть.

🚀 Плюсы этого подхода:

Чистый код: Сигнатура процедуры обновления становится очень короткой — она принимает всего одну структуру!
Гибкость: Можно легко добавлять новые поля, не меняя сигнатуру процедуры.
Безопасно: Мы не используем "магические" значения, которые могут совпасть с реальными данными.

🚧 Минусы, конечно, тоже есть:

Сложность: Создание и поддержка кастомных типов (t_variant, коллекций) требует больше усилий.
Производительность: Работа с объектами и коллекциями может быть медленнее, чем простой UPDATE с CASE операторами.
Неочевидно: Для новичка это решение может показаться избыточным.

Этот метод позволяет сделать код более модульным и расширяемым. Он отлично подходит для сложных систем, где гибкость важнее микрооптимизаций.

💎 Поддержка канала⁉️

Все слова благодарности, лайки, гневные комменты и всё что на душе накипело... 👇
#CodeArchitecture

Скрипт для анализа и проверки!

🎵«Помню, помню, помню я,
Как меня мать любила.
И не раз, и не два
Она мне говорила:
Не ходи на тот конец,
Не водись с ворами,
Тебя на каторгу сошлют,
Бряцать кандалами...»
— Народная

⚠️ Никогда не попадайте на каторгу, как бы много Вам не платили - всё равно будет ощущение, что Вас обокрали.

Задача из собеса как раз на такую каторгу.

💡 Что произойдёт после выполнения этого скрипта?

WHENEVER SQLERROR CONTINUE
CREATE TABLE test_tbl (x INT);
INSERT INTO test_tbl (x) VALUES (1);

BEGIN
  INSERT INTO test_tbl (x) VALUES (2);
  RAISE VALUE_ERROR;
END;
/
SELECT * FROM test_tbl;

Внутри BEGIN ... END — вставка и ошибка. Но стоит WHENEVER SQLERROR CONTINUE.

🤔 Вопрос:
Что отработает, а что нет? Что покажет SELECT?
Будут ли строки (1) и (2), или только (1)?

⚠️ Хотите проверить скрипты, но нет базы под рукой - онлайн-песочница
💎 Поддержка канала⁉️

✍️ Пишите в комментариях, как думаете.
(Не забудьте обосновать — там всё не так просто 😉)
#RealInterviewTasks #sql #Oracle #PLSQL

💡 Разбор задачи с собеса

WHENEVER SQLERROR CONTINUE
CREATE TABLE test_tbl (x INT);
INSERT INTO test_tbl (x) VALUES (1);

BEGIN
  INSERT INTO test_tbl (x) VALUES (2);
  RAISE VALUE_ERROR;
END;
/
SELECT * FROM test_tbl;

❓ Что покажет SELECT?

📌 Ответ: будет только (1).

🛠 Почему:
INSERT (1) — отдельный SQL-оператор, он останется в транзакции.

BEGIN...END; — это одна операция. Необработанное исключение (VALUE_ERROR) откатывает всё, что внутри блока, до implicit savepoint. Поэтому INSERT (2) отменится.

WHENEVER SQLERROR CONTINUE — это настройка SQL*Plus, она лишь говорит не останавливать скрипт, но не отменяет откаты внутри Oracle.

⚠️ Хотите проверить скрипты, но нет базы под рукой - онлайн-песочница
💎 Поддержка канала⁉️

#RealInterviewTasks #sql #Oracle #PL/SQL

🎸 «И всё повторяется, как сон» — Король и Шут

🧠 Классическая задача с собеседований - почти в каждом собесе такая задача встречается.

Дана таблица:

sql
CREATE TABLE transactions (
  id NUMBER PRIMARY KEY NOT NULL,
  created DATE NOT NULL,
  amount NUMBER NOT NULL,
  currency VARCHAR2(3) NOT NULL,
  description VARCHAR2(100 CHAR)
);

📌 Нужно:
Написать SQL-запрос, который вернёт все лишние дубликаты, где дубликатом считается комбинация
created + currency + amount.

⚠️ Хотите проверить скрипты, но нет базы под рукой - онлайн-песочница
💎 Поддержка канала⁉️

🤔 Как бы вы решили?
Пишите свои варианты в комментариях — в следующем посте разберём.
#RealInterviewTasks #sql #Oracle #PostgreSQL #PL/SQL #PL/pgSQL

💡 Разбор задачи о дублях
Чтобы вытащить только лишние дубликаты (не оставляя первый уникальный), удобно использовать аналитическую функцию ROW_NUMBER():

SELECT id,
       created,
       amount,
       currency,
       description
FROM (
    SELECT t.*,
           ROW_NUMBER() OVER (
               PARTITION BY created, currency, amount
               ORDER BY id
           ) AS rn
    FROM transactions t
) sub
WHERE rn > 1;

📌 Как это работает:
PARTITION BY created, currency, amount
— разбиваем данные на группы с одинаковыми датой, валютой и суммой.

ORDER BY id
— определяем, какой из дублей считать "первым".

ROW_NUMBER()
— нумерует строки в каждой группе с 1.

WHERE rn > 1
— берём только строки с номером больше 1 (т.е. "лишние").

✅ Плюс этого подхода — он легко адаптируется под любые критерии поиска дубликатов и работает и в Oracle, и в PostgreSQL.

А вы бы решали через GROUP BY ... HAVING COUNT(*) > 1 или тоже через аналитику?

⚠️ Хотите проверить скрипты, но нет базы под рукой - онлайн-песочница
💎 Поддержка канала⁉️

#RealInterviewTasks #sql #Oracle #PostgreSQL #PLSQL #PLpgSQL

🔥 «Как увидят нас — так сразу ахнут, и кое-где жаренным запахнет, кое-что за пазухой мы держим...» — из «Бременских музыкантов»

🧠 Интересная задача с собеседования 2022 году

Дано:

CREATE TABLE table1 (
  numeric_value NUMBER NOT NULL,
  created DATE NOT NULL
);

CREATE OR REPLACE TYPE numeric_value_rec AS OBJECT (
  numeric_value NUMBER
)
/

CREATE OR REPLACE TYPE numeric_value_tbl IS TABLE OF numeric_value_rec
/

Код:

DECLARE
  portionSize CONSTANT INT := 100;

  CURSOR readData IS
    SELECT numeric_value_rec(numeric_value) AS x
    FROM   table1
    ORDER BY created
    FOR UPDATE;

  portionArray numeric_value_tbl;
BEGIN
  OPEN readData;
  FETCH readData BULK COLLECT INTO portionArray LIMIT portionSize;
  CLOSE readData;
END;
/

📌 Вопрос:
Почему этот код не выполняет свою задачу — читать данные по порциям, сохраняя потребление ресурсов постоянным, независимо от размера таблицы?

⚠️ Хотите проверить скрипты, но нет базы под рукой - онлайн-песочница
💎 Поддержка канала⁉️

#RealInterviewTasks #Oracle #PLSQL #SQL

💡 Разбор задачи с собеседования (2022)

📌 Цель — читать данные по порциям, сохраняя постоянное потребление ресурсов.

❗ В чём ошибки исходного решения:

Блокируется вся таблица
Из-за FOR UPDATE без ограничений на выборку блокировка накладывается на все строки, а не только на текущую порцию.

Нет порционного перебора
Если требуется пройти таблицу целиком, надо читать её в цикле по порциям и блокировать только нужную порцию перед обработкой, а затем делать COMMIT, чтобы снять блокировки.

Нет индекса по created
Это приведёт к сортировке всей таблицы при каждом запуске, что при больших объёмах сильно замедлит выполнение.

Нет блока EXCEPTION
При ошибке не будет гарантии, что курсор не будет закрыт, а коллекция — очищена, что создаёт утечки ресурсов.

💎 Поддержка канала⁉️

#RealInterviewTasks #Oracle #PLSQL #SQL

🧩 Задача для PL/SQL-разработчиков от Экспо-Банка из 2020 года

DECLARE
  TYPE rRec IS RECORD (
    vStr  VARCHAR2(100),
    vDate DATE
  );

  TYPE tRec IS TABLE OF rRec INDEX BY INTEGER;
  tableRec tRec;

Задание:
Написать процедуру сортировки tableRec любым методом, при котором:

vStr сортируется по возрастанию
vDate сортируется по убыванию

Пример входных данных:

'11'  31/10/17
'11'  15/10/17
'22'  01/01/20
'33'  10/07/19
'33'  09/07/19

⚠️ Хотите проверить скрипты, но нет базы под рукой - онлайн-песочница
💎 Поддержка канала⁉️

💬 Напишите как бы Вы решили в комментариях — завтра выложу разбор.
#RealInterviewTasks #sql #Oracle #PLSQL

💡 Разбор задачи от Экспо-Банка

Уловка задачи в том, что ассоциативный массив нельзя использовать в SELECT.
Поэтому сортировку приходится делать вручную методами процедурного языка.

📌 Для решения можно выбрать любой алгоритм сортировки.
Я взял быструю сортировку, но по условию подойдёт и сортировка "пузырьком".

DECLARE
    TYPE rRec IS RECORD
    (
        vStr     VARCHAR2 (100),
        vDate    DATE
    );

    TYPE tRec IS TABLE OF rRec
        INDEX BY PLS_INTEGER;

    tableRec   tRec;

    PROCEDURE quick_sort (left IN INTEGER, right IN INTEGER, sorted_table IN OUT NOCOPY tRec) IS
        i         INTEGER := left;
        j         INTEGER := right;
        mediana   rRec;
        tmp       rRec;

        FUNCTION greater (a IN rRec, b IN rRec)
            RETURN BOOLEAN IS
        BEGIN
            --vStr сортируется по возрастанию, а vDate по убыванию.
            RETURN (a.vStr > b.vStr) OR (a.vStr = b.vStr AND a.vDate < b.vDate);
        END greater;
    BEGIN
        mediana   := sorted_table ((left + right) / 2);

        WHILE i <= j LOOP
            WHILE greater (a => mediana, b => sorted_table (i)) LOOP
                i   := i + 1;
            END LOOP;

            WHILE greater (a => sorted_table (j), b => mediana) LOOP
                j   := j - 1;
            END LOOP;

            IF i <= j THEN
                tmp                := sorted_table (i);
                sorted_table (i)   := sorted_table (j);
                sorted_table (j)   := tmp;
                i                  := i + 1;
                j                  := j - 1;
            END IF;
        END LOOP;

        IF left < j THEN
            quick_sort (left => left, right => j, sorted_table => sorted_table);
        END IF;

        IF i < right THEN
            quick_sort (left => i, right => right, sorted_table => sorted_table);
        END IF;
    END quick_sort;
BEGIN
    tableRec (1).vStr    := 'Petrov';
    tableRec (1).vDate   := TO_DATE ('10.08.2025', 'dd.mm.yyyy');
    tableRec (2).vStr    := 'Petrov';
    tableRec (2).vDate   := TO_DATE ('11.08.2025', 'dd.mm.yyyy');
    tableRec (3).vStr    := 'Ivanov';
    tableRec (3).vDate   := TO_DATE ('01.08.2025', 'dd.mm.yyyy');
    tableRec (4).vStr    := 'Ivanov';
    tableRec (4).vDate   := TO_DATE ('02.08.2025', 'dd.mm.yyyy');

    quick_sort (left => 1, right => tableRec.COUNT, sorted_table => tableRec);

    DECLARE
        index#   INTEGER;
    BEGIN
        index#   := tableRec.FIRST;

        WHILE index# IS NOT NULL LOOP
            DBMS_OUTPUT.put_line (tableRec (index#).vStr || '   ' || TO_CHAR (tableRec (index#).vDate, 'dd.mm.yyyy'));
            index#   := tableRec.NEXT (index#);
        END LOOP;
    END;

    tableRec.delete;
EXCEPTION
    WHEN OTHERS THEN
        IF tableRec IS NOT NULL THEN
            tableRec.delete;
        END IF;
END;

⚠️ Хотите проверить скрипты, но нет базы под рукой - онлайн-песочница
💎 Поддержка канала⁉️

❓ А было бы вам интересно увидеть реализацию сортировки через бинарное дерево в PL/SQL?
Напишите в комментариях, обсудим.
#RealInterviewTasks #sql #Oracle #PLSQL

🎵 «Нас четверо, пока ещё мы вместе!
Но дело есть
И это дело чести!
Девиз наш — "Все за одного"
И в этом наш успех!»
— Три мушкетёра ⚔️

💼 Задача из собеса в СКБ-банк в далёком 2017 году.

📝 Задание 1
Сгенерировать произвольным образом содержимое таблицы C, на основе данных таблицы A и B.

📂 Таблица A:
EMPFIO — ФИО сотрудника 👨‍💼

📂 Таблица B:
OWNM — Наименование подразделения 🏢

📂 Таблица C:
OWNM — Наименование подразделения 🏢
EMPFIO — ФИО сотрудника 👨‍💼

📝 Задание 2
Объяснить, в каком порядке будут выбираться данные из таблиц, какие таблицы будут задействованы в первую очередь, а какие потом.
Таблицы и данные использовать из предыдущего задания.

select * from A, B, C
where a.empfio = c.empfio 
  and b.ownm = c.ownm;

select * from B, A, C 
where a.empfio = c.empfio 
  and b.ownm = c.ownm;

✏️ Написать запросы, выбирающие данные из таблиц в обратном порядке их использования в указанных двух запросах.
Дать комментарий по предоставленному решению.

📌 То есть:
C -> B -> A
C -> A -> B

⚠️ Хотите проверить скрипты, но нет базы под рукой - онлайн-песочница
💎 Поддержка канала⁉️

#RealInterviewTasks #sql #Oracle #PostgreSQL #PLSQL #PLpgSQL

💼 Разбор задачи из СКБ-банка — генерация данных и порядок соединений
Бог знает, что я написал тогда в 2017 году, но сейчас мой ответ был бы таким.

CREATE TABLE a (empfio VARCHAR2 (50));
CREATE TABLE b (ownm VARCHAR2 (50));
CREATE TABLE c (ownm VARCHAR2 (50), empfio VARCHAR2 (50));

DECLARE
  PROCEDURE generate_data (emp_count IN INTEGER, dept_count IN INTEGER, links_count IN INTEGER) IS
  BEGIN
    EXECUTE IMMEDIATE  'TRUNCATE TABLE c';
    EXECUTE IMMEDIATE  'TRUNCATE TABLE a';
    EXECUTE IMMEDIATE  'TRUNCATE TABLE b';

    INSERT INTO a (empfio)
      SELECT     DBMS_RANDOM.string ('U', 8) AS empfio
      FROM       DUAL
      CONNECT BY LEVEL <= generate_data.emp_count;

    INSERT INTO b (ownm)
      SELECT     DBMS_RANDOM.string ('U', 6) AS ownm
      FROM       DUAL
      CONNECT BY LEVEL <= generate_data.dept_count;

    INSERT INTO c (ownm, empfio)
      SELECT a.ownm, a.empfio
      FROM   (SELECT   b.ownm, a.empfio
              FROM     a CROSS JOIN B
              ORDER BY DBMS_RANDOM.value) a
      WHERE  ROWNUM <= generate_data.links_count;
  END generate_data;
BEGIN
  generate_data (emp_count => 5, dept_count => 3, links_count => 10);

  COMMIT;
END;

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

В текущих запросах нет фильтров и в таблицах нет индексов, значит он будет учитывать кардинальность таблиц и соединять их по мере роста объёма данных.

Порядок соединения можно явно задать хинтами ORDERED и LEADING.

select /*+ LEADING (C B A) */ *
from A, B, C
where a.empfio = c.empfio 
  and b.ownm = c.ownm;

select /*+ ORDERED */ *
from C, A, B 
where a.empfio = c.empfio 
  and b.ownm = c.ownm;

⚡️ Хинты позволяют контролировать порядок соединений и влияют на план выполнения.

⚠️ Хотите проверить скрипты, но нет базы под рукой - онлайн-песочница
💎 Поддержка канала⁉️

#RealInterviewTasks #sql #Oracle #PLSQL

🎵«И вновь продолжается бой,
И сердцу тревожно в груди.»
— Н. Добронравов

🌳 Работа с иерархическими данными в Oracle

Есть таблица с данными в виде дерева:

CREATE TABLE t (
  id  NUMBER,         -- идентификатор узла
  pid NUMBER,         -- идентификатор родительского узла
  nam VARCHAR2(255)   -- наименование
);

Пример данных

ID  PID  NAM
1       Корень
2   1   Узел2
3   1   Узел3
4   2   Узел4
5   4   Узел5
6   5   Узел6
7   4   Узел7

Требование:
Написать запрос для получения дерева от корневого узла.
Узел 5 и все его потомки должны быть исключены из результата.
Для каждого узла нужно вывести имя его родителя.
Данные отсортировать в порядке возрастания ID с учётом иерархии.

Ожидаемый результат

ID  PID  NAM    PARENT_NAM
1       Корень 
2   1   Узел2  Корень
4   2   Узел4  Узел2
7   4   Узел7  Узел4
3   1   Узел3  Корень

⚠️ Хотите проверить скрипты, но нет базы под рукой - онлайн-песочница
💎 Поддержка канала⁉️

💬 Напишите как бы Вы решили в комментариях — завтра выложу разбор.
#RealInterviewTasks #sql #Oracle #PLSQL

🌳 Разбор задачи с иерархическими данными в Oracle: исключаем ветку дерева

Исходные данные:

CREATE TABLE t (
  id  NUMBER,         -- идентификатор узла
  pid NUMBER,         -- идентификатор родительского узла
  nam VARCHAR2(255)   -- наименование
);

📋 Пример содержимого:

ID PID NAM
1  Корень
2 1 Узел2
3 1 Узел3
4 2 Узел4
5 4 Узел5
6 5 Узел6
7 4 Узел7

🚫 Дерево до фильтрации:

scss
Корень (1)
├── Узел2 (2)
│   └── Узел4 (4)
│       ├── Узел5 (5)
│       │   └── Узел6 (6)
│       └── Узел7 (7)
└── Узел3 (3)

✅ Дерево после фильтрации (узел 5 и потомки удалены):

scss
Корень (1)
├── Узел2 (2)
│   └── Узел4 (4)
│       └── Узел7 (7)
└── Узел3 (3)

📌 Решение 1 (CONNECT BY):

SELECT 
  t.id,
  t.pid,
  t.nam,
  PRIOR t.nam AS parent_nam
FROM t
START WITH t.pid IS NULL
CONNECT BY PRIOR t.id = t.pid AND t.id != 5
ORDER SIBLINGS BY t.id;

📌 Решение 2 (рекурсивный WITH, быстрее):

WITH t1 (id, pid, nam, parent_name) AS (
  SELECT t.id, t.pid, t.nam, NULL AS parent_name
  FROM t 
  WHERE t.pid IS NULL
UNION ALL
  SELECT t2.id, t2.pid, t2.nam, t1.nam AS parent_name
  FROM t t2
  INNER JOIN t1 ON t2.pid = t1.id
  WHERE t2.id != 5
)
SEARCH DEPTH FIRST BY id SET order1
SELECT t1.id, t1.pid, t1.nam, t1.parent_name
FROM t1
ORDER BY order1;

📊 Оба запроса дают одинаковый результат, но второй обычно эффективнее при больших объёмах данных.

⚠️ Хотите проверить скрипты, но нет базы под рукой - онлайн-песочница
💎 Поддержка канала⁉️

#RealInterviewTasks #sql #oracle #plsql

📖 Как я оказался одним из авторов курса по SQL от Яндекса

Вначале было очень интересно, и меня буквально распирало от важности. Но… платили мало, работать приходилось много. Я стал сочковать.

В итоге — выгнали. Но уроков 10 я всё же написал 🙂
Курс, кстати, не для DB, а скорее ознакомительный, для прикладных программистов.
И, думаю, свои цели он достигает. В этом плане могу его рекомендовать.
А в целом, без ограничения общности, уверен, что и другие аналогичные курсы не хуже.

💡 Я выкладываю свой ответ на тестовое задание - "Напишите и объясните человеку с улицы, что такое план запроса в PostgreSQL."
Текст — сырой, без редактуры, но я старался и собеседование прошел😁

💎 Поддержка канала⁉️
#sql #PostgreSQL #PLpgSQL

🎵"Нас атомной бомбой испугаешь едва ли"
— группа Ленинград
🎯 Тестовое задание на соискателя роли SQL Developer в 2022 году

Задача:
Дана таблица курсов валют:

create table rates(
  curr_id   number,   -- идентификатор валюты
  date_rate date,     -- дата, с которой действует курс
  rate      number    -- значение курса
);

Особенности:
Курс не устанавливается на каждую календарную дату.
Установленный курс действует до следующей его смены.
Уникальный ключ: curr_id + date_rate.

Пример данных:

URR_ID DATE_RATE RATE
1 01.01.2010 30
2 01.01.2010 40
1 02.01.2010 32
1 05.01.2010 33
2 10.01.2010 41
2 15.01.2010 42

Требуется:
Написать SQL-запрос, который вернёт действующий курс указанной валюты на любую заданную дату.

Пример результата:

Для валюты 1 на 03.01.2010 → курс 32
Для валюты 2 на 10.01.2010 → курс 41

⚠️ Хотите проверить скрипты, но нет базы под рукой - онлайн-песочница
💎 Поддержка канала⁉️

💬 Напишите как бы Вы решили в комментариях👇
#RealInterviewTasks #sql #Oracle #PostgreSQL #PLSQL #PLpgSQL

🔎 Разбираем задачу по курсам валют

Казалось бы, задача простая, но в ней есть два подвоха, которые соискатель в тестовом задании может не заметить:

1️⃣ Нетранкованные даты
В таблицах даты валюты могут храниться нетранкованные. Иными словами, на один и тот же день может быть несколько записей с разными временами:
например, курс валюты может быть установлен на дату 16.08.2025 00:00:00 и на 16.08.2025 12:00:00.
(Надеюсь, что такое никогда не произойдет — это признак галопирующей инфляции 💸).

2️⃣ Уникальный индекс
Указан уникальный индекс на (curr_id, date_rate). Если же требуется получать данные быстрее, стоит сделать уникальный индекс на все три поля.

📌 Логика запроса
Чтобы получить актуальный курс на заданную дату, нужно выбрать запись с максимальной датой, которая не превышает целевую.
Индекс на (curr_id, date_rate) обеспечивает быстрый поиск по валюте и дате.

1️⃣ Первый вариант (индекс по (curr_id, date_rate))

SELECT r.rate
FROM rates r
WHERE r.curr_id = 9
  AND r.date_rate <= SYSDATE - INTERVAL '100' DAY(3)
ORDER BY r.date_rate DESC
FETCH FIRST 1 ROW ONLY;

💡 Как работает:
Oracle использует индекс на (curr_id, date_rate) для поиска всех записей валюты 9 до целевой даты.
Сортировка по date_rate DESC находит последнюю актуальную дату.
После выбора строки Oracle обращается к таблице, чтобы получить значение rate.

⚠️ Эффект:
BUFFER_GETS и DISK_READS выше, так как приходится читать блоки таблицы.
Производительность нормальная, но есть возможность улучшения.

2️⃣ Второй вариант (covering index (curr_id, date_rate, rate))

CREATE UNIQUE INDEX rates_u01
ON rates (curr_id, date_rate, rate);

DECLARE
    currId     rates.curr_id%TYPE;
    dateIate   rates.date_rate%TYPE := SYSDATE - INTERVAL '100' DAY;
    rate       rates.rate%TYPE := 9;

    CURSOR getRate (curr_id IN rates.curr_id%TYPE, date_rate IN rates.date_rate%TYPE) IS
        SELECT /*+ FIRST_ROWS*/
                 r.rate
        FROM     rates r
        WHERE    r.curr_id = getRate.curr_id AND r.date_rate <= getRate.date_rate
        ORDER BY r.date_rate DESC;
BEGIN
    OPEN getRate (curr_id => currId, date_rate => dateIate);

    FETCH getRate INTO rate;

    CLOSE getRate;

    DBMS_OUTPUT.put_line ('rate = ' || TO_CHAR (rate));
EXCEPTION
    WHEN OTHERS THEN
        IF getRate%ISOPEN THEN
            CLOSE getRate;
        END IF;

        RAISE;
END;
/

💡 Как работает:
Индекс теперь содержит все нужные поля, включая rate.
Oracle получает значение курса прямо из индекса, не обращаясь к таблице.
Поиск последней даты по валюте работает так же — сортировка по date_rate DESC.

⚡ Эффект:
Снижается число блоков, которые нужно читать.
BUFFER_GETS и DISK_READS сокращаются более чем в 2 раза.
Запрос выполняется быстрее и эффективнее.
#RealInterviewTasks #sql #Oracle #PLSQL

Продолжим и сравним планы запросов двух вариантов более подробно ⚖️.

Вариант 1️⃣

----------------------------------------------------------------------------------------
| Id  | Operation                        | Name      | Rows | Bytes  | Cost | Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |           |    1 |     35 |    1 | 00:00:01 |
| * 1 |   VIEW                           |           |    1 |     35 |    1 | 00:00:01 |
| * 2 |    WINDOW NOSORT STOPKEY         |           | 9367 | 327845 |    1 | 00:00:01 |
|   3 |     TABLE ACCESS BY INDEX ROWID  | RATES     | 9367 | 327845 |    1 | 00:00:01 |
| * 4 |      INDEX RANGE SCAN DESCENDING | RATES_U01 |    2 |        |    1 | 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=1)
* 2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("R"."DATE_RATE") DESC )<=1)
* 4 - access("R"."CURR_ID"=9 AND "R"."DATE_RATE"<=SYSDATE@!-INTERVAL'+100 00:00:00' DAY(3) TO SECOND(0))

⚠️ Оптимизатор вынужден применять аналитическую функцию и делать дополнительную фильтрацию.

Вариант 2️⃣

----------------------------------------------------------
| Id  | Operation                   | Name      | E-Rows |
----------------------------------------------------------
|   0 | SELECT STATEMENT            |           |        |
|*  1 |  INDEX RANGE SCAN DESCENDING| RATES_U01 |   9367 |
----------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("R"."CURR_ID"=:B2 AND "R"."DATE_RATE"<=:B1")

✅ Комментарий:
Во втором варианте Oracle просто спускается по индексу и берёт последнее значение — всё.

📎 Тестовый скрипт для проверки прикрепил.

⚠️ Хотите проверить скрипты, но нет базы под рукой - онлайн-песочница
💎 Поддержка канала⁉️

Смотрите, анализируйте и пишите свои выводы в комментариях 👇
#RealInterviewTasks