SQL заметки за 2019

Продолжение цикла заметок и статьи 2016 года.
Хочу зафиксировать моменты Oracle и SQL в общем, которые достаточно интересны, но малы для отдельной статьи.

• Трансформация запросов
  • Виды преобразований запросов
  • Результрующий запрос после всех преобразований оптимизатора
  • Ручная трансформаиця 1 запроса в другой
• Статистика
  • Устаревание статистики
  • Инкрементальный сбор статистики в партицированных таблицах
  • Селективность колонки с 12.2
  • Хинт для задания статистики колонки
  • статистика по использованию сегмента
  • Ассоциация статистики к функции
  • Колонки-кандидаты для гистограммы
  • Просмотр данных гистограммы
  • Join cardinality по гистограмме
  • Определение селективности, если на обоих столбцах соединения есть гистограмма
• PLSQL
  • Автономная транзакция
  • Иключение при поиске элемента по ключу
  • plsql redefinition
  • Консистентность функций
  • Параллелизация pipeline функций
• PLSQL коллекции
  • Varrays - обычный массив
  • Hash table - Associative array над связанным списком
  • Nested tables
• Анализ производительности запросов
  • индекс - кандидат на удаление
  • forall - в статистике (ash/awr)
  • Выявление skew через oem monitor
  • Пометка запроса для awr
  • Чтение плана
  • Монотонный рост значений в индексе
  • Долгий вызов plsql в запросе
  • Undo/redo при вставке
  • Параллельное последовательное чтение индекса
  • Result_cache
  • Вставка игнорируя consraint, но с сохранением ошибок
  • Пометка блока горячим
• Оптимизация хранения
  • Создание not null поля с default
  • Index coalesce
  • Вставка в новую таблицу
  • Вставка в длинную таблицу
  • Include Индекс
  • Дополнительные параметры таблиц в Exadata
  • Отрицательная эффективность Exadata
  • Структура Lob
• Партицирование
  • Системное партицирование
  • Reference partitions
  • Глобальные индексы
  • INDEXING OFF|On
  • Тепловая карта партиций
• Настройки бд
  • Виды репликаций
  • Exadata 12.2
  • Особенности только в exadata
• Разные SQL алгоритмы
  • Пагинация на ключах
  • start_of_group - нумерация групп по разрывам
  • Забор таблицы частями без fullscan, индексов и партиций
  • Поиск одного пропуска
  • Вставка данных больше размера varchar
  • partition join
  • Округление через to int
  • Удаление из обновляемого представления
  • Выражение на месте join
  • DBMS_HS_PASSTHROUGH - Полное выполнение запроса на удаленной бд
  • Top уникальных строк в группе

Трансформация запросов

Виды преобразований запросов Optimizer Transformations
Документация

* join elimination (aggregate elimination)
убираются лишние неиспольлзуемые join (если есть FK), group by (по колонкам с уникальным индексом) или объединяет несколько group by(sum/max) в один

* view merging
подзапрос или view разворачивается в основной запрос

* subquery unnesting
подзапрос разворачивается в основной запрос
Пример - Semi join. Преобразование in/exists в join
Oracle ищет строки в правой таблице до первого совпадения (очень быстро по индексу, если в левой таблице немного строк)

* join predicate pushdown
фильтр из внешнего запроса проталкивается ниже, чтобы раньше отфильтровать данные

* join factorization
повторяющаяся часть Union all выносится во внешний запрос, внутри Union all остается только различия

* star transforamtion
join измерений с фильтрами разворачивается в in фильтры
таким образом получаем rowid битмап индексов на каждой колонке, потом мержим битовые карты через and и таблица быстро фильтруется по этим rowid без выполнения соединения

* Table Expansion
На партицированной таблице можно отключить индексы на часто изменяющихся частях. Тогда oracle разобъем запрос на 2 части: full scan по партициям без индекса и index scan с индексом.
Без такого преобразования был бы только full scan.

* or expansion
or Запрос разворачивается Union all или or merge битмап индекса или inlist iterator
Каждая итерация concatenation становится сложней, т.к. нужно доплонительно отфильтровать записи, которые уже попали в предыдущий этап конкатенации
Включение конкатенации при index skip scan: +use_concat(OR_PREDICATES(1))

* matview rewrite
Перезапись части запроса данными matview, если совпадает текст

* px: join filter/bloom
bloom предфильтрация потока 2 по данным потока 1

* транзитивность constraint
к примеру есть функциональный constraint: col2 = trunc(col1, 'yyyy'). При фильтрации по col1 на таблицу будет наложен также и на col2


результрующий запрос после всех преобразований оптимизатора
dbms_sql2.expand_sql_text

Ручная трансформаиця 1 запроса в другой

BEGIN
    DBMS_SQL_TRANSLATOR.REGISTER_SQL_TRANSLATION(
    profile_name    => 'deneme',
    sql_text        => 'select count(*) from pm.employee',
    translated_text => 'select count(*) from sh.employee');

    SYS.DBMS_ADVANCED_REWRITE.declare_rewrite_equivalence (
    name             => 'test_rewrite',
    source_stmt      => 'SELECT * FROM rewrite_test_tab',
    destination_stmt => 'SELECT * FROM rewrite_test_tab_v',
    validate         => FALSE,
    rewrite_mode     => 'TEXT_MATCH');
END;

Статистика

Устаревание статистики
Если число модификаций превышает STALE_PERCENT в настройках таблицы от общего числа строк, то будет автоматический пересбор
Число модификаций таблиц:

select  * from    dba_tab_modifications where   table_owner = 'DWH'

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

EXEC dbms_stats.set_table_prefs(null,'SALES','INCREMENTAL','TRUE')

Селективность колонки с 12.2
c 12.2 используется призительный distinct (http://blog.skahin.ru/2017/02/oracle-hash-distinct.html)

Хинт для задания статистики колонки
+COLUMN_STATS(DD, VN, scale, length=3 distinct=5 nulls=0 min=2 max=10)

статистика по использованию сегмента
запись, полное сканирование, лукап - это инфаормация используется для тепловых карт и автоархивации

select * from DBA_HEAT_MAP_SEG_HISTOGRAM WHERE OBJECT_NAME='WALE' order by TRACK_TIME desc

Ассоциация статистики к функции
* статистика поумолчанию в селективности и стоимости

ASSOCIATE STATISTICS WITH FUNCTIONS quick_function DEFAULT SELECTIVITY 0.1;
ASSOCIATE STATISTICS WITH FUNCTIONS high_cpu_io DEFAULT COST (10000, 1000, 0);

* диманическая статистика в зависимости от входных параметров через ODCIStatsSelectivity

ASSOCIATE STATISTICS WITH FUNCTIONS promo_function USING promo_stats_ot;

Как описать promo_stats_ot подробней тут

Колонки-кандидаты для гистограммы
Это должен быть столбец часто используемый в where с оператором = (equality)
Статистика ипользования столбца в условиях фильтрации содержится в таблице SYS.COL_USAGE$
Дополнительное условие создание гистограммы - это наличие перекоса в числе строк группы при сборе статистики по числу уникальных значений.

Просмотр данных гистограммы

select
        endpoint_actual_value ,
        endpoint_number,
        endpoint_number - nvl(prev_endpoint,0)  frequency,
        ENDPOINT_REPEAT_COUNT
from    (
        select
                endpoint_number,
                lag(endpoint_number,1) over(
                        order by endpoint_number
                )                               prev_endpoint,
                endpoint_actual_value,
                ENDPOINT_REPEAT_COUNT
        from
                dba_tab_histograms
        where
                table_name = 'SKU' AND COLUMN_NAME = 'LOC'
        )
order by endpoint_number
;

D-0901 3 3 3
D-7000 24 21 21
D-7006 45 21 6
D-7013 66 21 21
D-7015 86 20 6
D-7033 107 21 9
...

Планируемое число строк = общее число строк таблицы * SUM(frequency подходящее под условие) / последнее endpoint_number
ENDPOINT_REPEAT_COUNT - это число повторений популярного значения в endpoint_actual_value
Если значение не попало в endpoint_actual_value, то число строк = общее число / число уникальных

Join cardinality по гистограмме
если столбец факта сильно перекошен, и этот стоблец используется в join с измерением, то оптимизатор будет использовать стандартный план: число строк факта / селективность измерения , что даст не верный результат
Для обхода можно скопировать гистограмму с перекошенного столбца на столбец с фильтром измерения, тогда оценка части измерения ~= оценки части перекошенных данных

Определение селективности, если на обоих столбцах соединения есть гистограмма
* SUM(frequency ведущей гистограммы * frequency ведомой гистограммы) (по всем совпадающим значениям) * селективность фильтра ведущей таблицы
* если это frequency гистограмма и часть данных выпала, то по выпашей части будет дан приблизительный расчет на основе distinct

PLSQL

Автономная транзакция
начинает работать с begin, т.е. все select в declare работают в основной

Иключение при поиске элемента по ключу
Если заранее известно, что exception будет немного или их почти не будет, то этот вариант быстрей всех других: min, цикл, подзапрос
Чем больше exception будет случаться, тем медленней

plsql redefinition
возможность делать ревизии (несколько версий) пакета и переключать через alter session/system.
Т.е. возможно выкладка изменений без остановки работы пользователей.

Консистентность функций
sql функция возвращает данные на момент своего вызова, а не на момент старта основного запроса! (Документация)

Параллелизация pipeline функций

CREATE FUNCTION function-name(parameter-name ref-cursor-type)
  RETURN rec_tab_type PIPELINED
  PARALLEL_ENABLE(PARTITION parameter-name BY [{HASH | RANGE} (column-list) | ANY ]) 
  [ORDER | CLUSTER] parameter-name BY (column-list) IS
BEGIN

PLSQL коллекции

Varrays - обычный массив
не может быть пропусков, должно быть точно указан размер

TYPE Foursome IS VARRAY(4) OF VARCHAR2(15);
team Foursome := Foursome('John', 'Mary', 'Alberto', 'Juanita');

* .count == .last - число элементов всегда равно индексу последнего
* может быть столбцом в таблице

Hash table - Associative array над связанным списком
ключ ассоциативного массива: VARCHAR2 или PLS_INTEGER

DECLARE
  TYPE population IS TABLE OF NUMBER  -- Associative array type
    INDEX BY VARCHAR2(64);            --  indexed by string
  city_population  population;
BEGIN
 city_population('Smallville')  := 2000;
END;

+ можно использовать для O(1) обращения по ключу
+ так и для прохождения first-last
+ хранится в pga памяти
- нельзя использовать в sql

Nested tables

TYPE Roster IS TABLE OF VARCHAR2(15);

хранится в автогенерируемой системной таблице
аналогично Varrays индекс массива автогенерируем, таблица обходится через FIRST..LAST
можно удалять элементы в отличии от Varrays, тогда .count < .last

+ может использоваться в запросах
+ может быть колонкой в таблице
+ поддерживает множественные операции в plsql: SET/DISTINCT/NOT/IN
+ может использоваться в bulk операциях select, delete

DECLARE
  TYPE NumTab IS TABLE OF employees.employee_id%TYPE;
  TYPE NameTab IS TABLE OF employees.last_name%TYPE;
 
  enums NumTab;
  names NameTab;
BEGIN
  SELECT employee_id, last_name
  BULK COLLECT INTO enums, names
  FROM employees
  ORDER BY employee_id;

Анализ производительности запросов

индекс - кандидат на удаление
если "db block changes" > "logical reads"/3 из DBA_HIST_SEG_STAT - т.е. запись превышает логические чтения в 3 раза

forall - в статистике (ash/awr)
будет выглядеть как одно выполнение (exec), но обработавшее N строк.

Выявление skew через oem monitor
* смотрим активность, что несмотря на параллельность db time ~= time
* потом делаем монитор в разрезе plan_line - сразу будет видно на каком этапе плана
* потом на parallel будет видно какое parallel set выполнял большую часть работы
* зная plan_line и сервер переходим в статистику выполнения, выбираем из выпадающего списка наш сервер и смотрим нашу plan_line
** в actual rows увидим сколько строк было обработано (оцениваем с общим числом), тамже размер памяти будет

Пометка запроса для awr
dbms_workload_repository.add_colored_sql - запрос всегда будет попадать в awr , несмотря на его частоту и скорость

Чтение плана
Читать план правильно сверху вниз, как будто это стек вызов процедур
Например FILER вначале, вообще может выключить работу sql
Сверху спускаемся до самого глубокого листа и от него стэк разворачивается в обратную сторону

Монотонный рост значений в индексе
может раздуть ITL у индекса, т.к. ITL листа наследуюется при расщеплении блока

Долгий вызов plsql в запросе
функцию можно увидеть в секции projection тормозящей строки плана

Undo/redo при вставке
append - уменьшает undo
append+nologging - уменьшает и undo и redo
CTAS == insert + append

Параллельное последовательное чтение индекса
1 поток читает адреса в связанном списке, а другие потоки считывают сами данные из блоков + дофильтровывают

Result_cache
* есть хинты на
** заставить кэшировать системные объекты
** задать время жизни
* при активации кэша на таблице, нужно проверить что запросов на ней немного и они возвращают небольшое число строк (основное время тратится на запрос, а не на возвращение строк)
* есть блэк лист (или хинтом) - отключить для разовых
* один latch на весь result_cache, так что вставка/чтение блокирует всех остальных

Вставка игнорируя consraint, но с сохранением ошибок

ALTER TABLE dept ENABLE PRIMARY KEY EXCEPTIONS INTO EXCEPTIONS;

а потом смотреть ключи и текст ошибки:
SELECT * FROM EXCEPTIONS;
+IGNORE_ROW_ON_DUPKEY_INDEX - хинт заставляет игнорировать дубликаты ключа при вставке

Пометка блока горячим
dbms_shared_pool.markHot(hash, namespace) - Оракл делает несколько клонов "горячих" объектов в пуле, как следствие соревновательность между сессиями за эти объекты несколько снижается.

Оптимизация хранения

Создание not null поля с default
не создает блоки физически, а только помечает в словаре.
Только при следующих измнеениях обновляется это значение.

Index coalesce
перемещает пустоты в конец индекса, которые потом можно будет использовать при равномерном добавлении (размер индекса не уменьшеается, blvel тоже, индекс и таблица не блокируется)

Вставка в новую таблицу
будет идти медленней, чем в старую, но с truncate. Т.к. asm в новой постоянно выделяет место на диске, а в старой место выделено, просто смещено HWM

Вставка в длинную таблицу
При вставке в таблицу с более 255 колонок, все колонки кладутся в 1 блок
При update строка разбивается на 2 блока, т.к. обновлеяемое значение записывается в новом
Так что такие таблицы имеет смысл ребилдить периодически, для избавления от одноблочных чтений

Include Индекс
Подходи, когда можно все нужные столбцы включить в unique Индекс, чтобы исключить обращение к таблице

CREATE UNIQUE INDEX newidx ON sometab (c1, c2) INCLUDE (c3, c4);

заменяет 2 индекса:

CREATE UNIQUE INDEX oldunqidx ON sometab (c1, c2);
CREATE        INDEX oldcvridx ON sometab (c1, c2, c3, c4);

Но c3,c4 не могут использоваться для фильтрации, только для извлечения данных

Дополнительные параметры таблиц в Exadata
Attribute clustering
упорядочивание данных согласно какогото индекса - делается автоматически при вставке и может использоваться exadata софтом при отсечении данных

 ALTER TABLE sales_ac ADD CLUSTERING BY LINEAR ORDER (customer_id) WITHOUT MATERIALIZED ZONEMAP;

Zone map
группирует блоки таблицы по какому то столбцу и в zone-map записывает верхнее и нижнее значение конкретной части, что также может использоваться Exadata для отсечения данных
группировать можно по значениям другой таблицы, по join запросу

  ALTER TABLE sales  ADD CLUSTERING sales 
  JOIN locations ON (sales_ac.location_id = locations.location_id) 
  BY LINEAR ORDER (locations.state, locations.county)
  WITH MATERIALIZED ZONEMAP;

Отрицательная эффективность Exadata
Может быть двойное разжатие, если после разжатия в Exadata получился размер CU больше 1МБ, тогда в бд отсылается сжатый вариант и там разжимается повторно
Так что сильное сжатие может дать отрицательную эффективность при колоночном сжатии в Exadata.

Структура Lob
lob состоит из 2 частей:
* lob locator (индекс, указывающий на части lob - хранится непосредственно в таблице)
* lob value - само значение хранится отдельно от таблицы (если больше 4000 байт)

Партицирование

Системное партицирование
нет указания колонки при создании, нужно указывать конкретную партицию при вставке или выборке.

Reference partitions

create table orders (id integer, sm number, odate date, CONSTRAINT opk PRIMARY KEY (id) PARTITION BY RANGE(odate) (...);
create table order_itemss (oid integer, sm number, CONSTRAINT ofk FK to orders) PARTITION BY REFERENCE(ofk)

т.е. в строках нет даты, она автоматом подтягивается по FK из заголовка и партицируется по ней
У такого способопа партицирования плохая производительность.

* IOT могут быть партицированы по range/hash
* object table - также можно пратицировать.
* nested table - партицируется аналогично родительской

Глобальные индексы
можно партицировать только по range и hash

INDEXING OFF|On
можно задать активность индексов на конкретной партиции (ora 12)
индекс при этом должен быть создан как INDEXING PARTIAL
при запросе на партиции без индекса и с ним, будет конкатенация (см. раздел трансформаций)

Тепловая карта партиций
на основании статистики использования партиций (DBA_HEAT_MAP_SEG_HISTOGRAM) можно включить компресию

ALTER TABLE T MODIFY [PART] ILM ADD POLICY COMPRESS ADVANCED ROW AFTER 30 DAYS OF NO MODIFICATION;

Настройки бд

Виды репликаций
* Statement: передаются запросы
+ меньше места, т.к. один запрос может обновлять N строк
- недерменированность резальутата в разных средах (NOW(), RAND() и прочее)
* передаются изменения данных:
+ однозначность результата
- большие объем

Exadata 12.2
* кэширование temp в flash disk (раньше только физ. диски) - ускорение!
* smart scan на сжатых индексах (раньше не работало, только бд)
* lob до 4 кб смогут использоваться в smart scan (иначе на бд)

Особенности только в exadata
* возможно для избранных таблиц делать keep flash cache
* _serial_direct_read=true - читать минуя буферный кэш
( минус в том, что бы его выполнить, нужно сбросить все данные из кэша на диск, чтобы прямым чтением забрались последние данные )
* частые запросы не используют smartscan, по тому, что таблица целиком уходит в кэш
* для inmemory можно использовать flash cache exadata

Разные SQL алгоритмы

Пагинация на ключах
Чтобы быстро пагинировать и не допускать съезжание страниц (если данные на предыдущую страницу добавилась между переключениями), нужно запоминать ID последней записи на странице и при переходе на следующую фильтровать по ней.
Так не надо будет сортировать все сначала, а достаточно пройтись по индексу нужные N записей и остановиться.

SELECT ...
FROM ...
WHERE ...
AND id < ?last_seen_id ---!!!!!
ORDER BY id DESC
FETCH FIRST 10 ROWS ONLY

start_of_group - нумерация групп по разрывам
lag(a, 1, a) over (order by b) start_of_group -> sum(start_of_group) over(order by a)
определение начала группы, потом нарастающий итог по группам, чтобы их пронумеровать

Забор таблицы частями без fullscan, индексов и партиций
Таблица можно забирать по экстентно:
1. Определяем диапазон блоков, которые входят в экcтент.
Сортируем экстенты таблицы (v_table) по rowid и набираем нужное число блоков (num_part) на основании размеров в bytes.

SET SERVEROUTPUT ON;
declare
    num_part integer := 3;
    v_owner varchar(8) := 'DWH';
    v_table varchar(64) := '/BI0/PMAT_PLANT';
    
    obj_id integer;
    sm_bytes number;
    cur_bytes number := 0;
    part_bytes number;
begin
	--определяем ID Объекта
    select data_object_id into obj_id from dba_objects where object_name = v_table and owner = v_owner;
    --общий объем объека
    select SUM(bytes) into sm_bytes from dba_extents where owner = v_owner and segment_name = v_table;
    DBMS_OUTPUT.PUT_LINE('sm_bytes = ' || sm_bytes);
    
    --расчитываем размер 1 партиции 
    part_bytes := sm_bytes / num_part;    
    DBMS_OUTPUT.PUT_LINE('part_bytes = ' || part_bytes);
    
    --идем по отсортированным экстентам
    for i IN(select bytes, dbms_rowid.rowid_create(1, obj_id, relative_fno, block_id, 0) rwd
        from dba_extents where owner = v_owner and segment_name = v_table
        order by rwd
    ) LOOP
    	--набираем нужное число экстентов до part_bytes размера
        cur_bytes := cur_bytes + i.bytes;
        if(cur_bytes > part_bytes) then
        	--когда набираем партицию: выводим граничный rowid и сбрасываем счетчик размера партиций
            DBMS_OUTPUT.PUT_LINE('cur_bytes = ' || cur_bytes);
            DBMS_OUTPUT.PUT_LINE('rwd = ' || i.rwd);
            cur_bytes := 0;
        end if;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('end_bytes = ' || cur_bytes);
end;

2. Получаем идеально ровные 3 части данных по 5.3ГБ:

sm_bytes = 16001335296
part_bytes = 5333778432

cur_bytes = 5349507072
rwd = AABtDGADXAAOuAAAAA
cur_bytes = 5338759168
rwd = AABtDGAENAADO4AAAA
end_bytes = 5313069056

3. Формируем диапазон rowid для забора:

select count(*) from dwh."/BI0/PMAT_PLANT" where rowid < 'AABtDGADXAAOuAAAAA'; --22 388 148 
select count(*) from dwh."/BI0/PMAT_PLANT" where rowid >= 'AABtDGADXAAOuAAAAA' and rowid < 'AABtDGAENAADO4AAAA'; -- + 40 817 578
select count(*) from dwh."/BI0/PMAT_PLANT" where rowid > 'AABtDGAENAADO4AAAA'; -- + 72 817 218 = 136022944

Размеры пачек получаются с разным количеством строк, но равные по размеру в байтах. Это обеспечит идеальное равномерное копирование данных, без перекоса при передаче по сети.
Сверяемся с полной таблицей:

select count(*) from dwh."/BI0/PMAT_PLANT"; --136 022 944

4. Каждый поток забирает свой диапазон rowid
+ каждый поток читает свои блоки, нет полных или повторных сканирований
+ не нужны партиции или индексы
+ идеальное равномерное распределние по объему в байтах
+ мгновенный доступ по идентификаторам блоков (быстрей индексов и лучше работает на больших диапазонах)
+ не нужно указывать при заборе: по какому полю, в каких границах. Все можно сделать автоматически.
- В полной мере можно использовать только для полных заборов таблиц или партиций.
Сверху можно наложить дополнительные фильтры, но это может дать сильные перекосы данных между потоками забора, т.к. неизвестно в каком из блоков лежит нужная часть данных.

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

поиск одного пропуска
* математической формулой:

select (max(n)-min(n))*(max(n)-min(n)+1)/2+(count(*)+1)*min(n)-sum(n) from t;

( max-min ) * ( max - min + 1 ) / 2 + (count + 1) * min - sum -- ~= квадрат разницы/2 + число элементов - сумма
* через minus / not exists с генеренной полной последовательность тоже будет быстро (2 FTS + antijoin/sort)
* если n возрастающее число, то можно так: (1 FTS, возможно сортировка для аналитики)

select n-rownum, to_char(max(n)+1) --последнее число группы (перед разрывом)
    ||'-'||(lead(min(n)) over (order by n-rownum)-1),n-rownum --первое число следующей группы (после разрыва)
from t
group by n-rownum --делаем группы (каждый разрыв даст новую группу)
order by n-rownum;

Вставка данных больше размера varchar
при вставке в varchar данных больше 4000, то он обрезается до максимума
если вставляется в середину, строка обрежется по месту вставки

select replace('hello xulio', 'x',  rpad('x', 32767)) from dual -- вернет hello

partition join
Добавляет недостающие данные в факте по f.cust_id (не нужно самому генерить)

SELECT 
  f.cust_id, 
  to_char(t.mth, 'DD.MON.YYYY') mth_name, 
  sum(nvl(vol,0)) vol
FROM 
  time_dim t LEFT OUTER JOIN 
  fct_tbl f  PARTITION BY  (f.cust_id) 
ON(t.mth = f.mth)
GROUP BY f.cust_id, t.mth
order by t.mth, f.cust_id;

Округление через to int

round = int(x+0.5)

Удаление из обновляемого представления

DELETE   FROM (
    SELECT
        s.item item,
        s.loc loc
    FROM
        stsc.sku s,
        stsc.planarriv pa
    WHERE
    s.item = pa.item
        AND   s.loc = pa.dest
)

возьмется таблица с максимальным ключом, которая однозначно определяем результат джойна
если у sku ключ из 2 полей, а planarriv из 3, то возьмется максимальный с 3 полями: planarriv


Выражение на месте join

select * from table1
left join table2 join table13 
     on table13.groupid = table2.groupid and table13.grouptype = 'тип1'
     on table2.id = table1.id 

если расставить скобки:

select * from table1
left join ( table2 join table13 on table13.groupid = table2.groupid and table13.grouptype = 'тип1' )
on table2.id = table1.id 

DBMS_HS_PASSTHROUGH - Полное выполнение запроса на удаленной бд
пакет DBMS_HS_PASSTHROUGH для выполнения запросов по дблинку к другим бд (все выполняется на стороне источника)

Top уникальных строк в группе
по xt_test.a, xt_test.b - должен быть индекс (ix_xt_test_ab)
xt_test.a - это категория (их относительно немного), b - другие поля, которых много
это лучше чем distinct - т.к. просматривается не весь индекс, а только ветви редкостречающегося xt_test.a потом по xt_test.b берется пара первых значений также по индексу (index range scan + stop key)

with t_unique( a ) as (
              select min(t1.a)
              from xt_test t1
              union all
              select (select min(t1.a) from xt_test t1 where t1.a>t.a)
              from t_unique t
              where a is not null
)
select/*+ use_nl(rids tt) */ *
from t_unique v
    ,lateral(
              select/*+ index_desc(tt ix_xt_test_ab) */ tt.*
              from xt_test tt
              where tt.a=v.a
                and rownum<=5
              order by tt.a, b desc
     ) r
order by r.a,r.b desc