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 уникальных строк в группе

ORACLE: основы стоимостной оптимизации

• Общие понятия: селективность, кардинальность, стоимость.
• Стоимость табличного доступа и системная статистика
• Стоимость индексного доступа.
• Гистограммы.
• Проблемы расчета селективности: Секционированные таблицы, значения поумолчанию, текстовые ключи, bind переменные и зависимые/коррелируемые столбцы.
• Селективность соединения таблиц
• Стоимость соединения таблиц: Nested Loops, Hash Join, Merge join
• Стоимость внешней сортировки.
• Кардинальности set операторов.
• Определение последовательности соединения таблиц.

На основе книги "ORACLE - основы стоимостной оптимизации" - автор Дж. Льюис.

Общие понятия

Селективность = (макс значение - мин значение отобранных данных)/(макс значение - мин значение всех данных)
в общем случае = отобранный интервал / весь интервал
Весь интервал определяется на основе статистики таблицы, это столбец destiny - разряженность: обратный показатель от кол-ва уникальных значений = 1/num_distinct.
Если на столбце есть гистограмма, то рассчитывается на основании ее, как сумма квадратов частоты нечастно встречающихся значения / (кол-во не пустых строк * кол-во не пустых нечастых строк)
т.е. на таблице с 2000 строк, где 1000 строк имеют одно значение, то без гистограммы destiny = 1/1001
с гистограммой = 1*1 / (2000 * 1) = 1/2000 в итоге получается, что запрос по гистограмме получится более селективным = отбираемый интервал * destiny, что справедливо, т.к. кроме уникальных значение будут использоваться гранулы гистограммы.

Итоговая селективность = селктивность * доля NULL полей

Особенности расчета:
* при расчете селективности при выходе за high_value (верхнее или нижне значение) - селективность линейно падает, т.е. не сразу обнуляется.
отсюда проблема : в динамичной oltp бд следуюем смещать high_value заранее перед началом нового дня, т.к. статистика собирается вконце дня, а запросы выше high_value уже идут
* когда селективность падает меньше числа строк таблицы, то селектиновсть = 1 / число строк таблицы

Селективности логических операций:
* and = селективность 1 * селективность 2
* or = селективность 1 + селективность 2 - (селективность 1 * селективность 2 )
* not = 1 - селективность 1

Кардинальность ( число отобранных записей ) = селективность * общее число записей

Стоимость = необходимое кол-во операций выраженное в стандартных единицах