11. Optimization

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

Выполнение запросов в Oracle. Общая схема и взаимодействие с клиентским приложением и машиной PL/SQL.

Все SQL-запросы, поступающие в СУБД, обрабатываются примерно по одной схеме.
На первой фазе запрос, заданный на языке запросов, подвергается лексическому и синтаксическому анализу. При этом вырабатывается его внутреннее представление, отражающее структуру запроса и содержащее информацию, которая характеризует объекты базы данных, упомянутые в запросе (отношения, поля и константы). Информация о хранимых в базе данных объектах выбирается из каталогов базы данных. Внутреннее представление запроса используется и преобразуется на следующих стадиях обработки запроса.
На второй фазе запрос во внутреннем представлении подвергается логической оптимизации. Могут применяться различные преобразования, "улучшающие" начальное представление запроса. Среди преобразований могут быть эквивалентные, после проведения которых получается внутреннее представление, семантически эквивалентное начальному (например, приведение запроса к некоторой канонической форме), Преобразования могут быть и семантическими: получаемое представление не является семантически эквивалентным начальному, но гарантируется, что результат выполнения преобразованного запроса совпадает с результатом запроса в начальной форме при соблюдении ограничений целостности, существующих в базе данных. После выполнения второй фазы обработки запроса его внутреннее представление остается непроцедурным, хотя и является в некотором смысле более эффективным, чем начальное.
Третий этап обработки запроса состоит в выборе на основе информации, которой располагает оптимизатор, набора альтернативных процедурных планов выполнения данного запроса в соответствии с его внутреннем представлением, полученным на второй фазе. Для каждого плана оценивается предполагаемая стоимость выполнения запроса. При оценках используется статистическая информация о состоянии базы данных, доступная оптимизатору. Из полученных альтернативных планов выбирается наиболее дешевый, и его внутреннее представление теперь соответствует обрабатываемому запросу.
На четвертом этапе по внутреннему представлению наиболее оптимального плана выполнения запроса формируется выполняемое представление плана.
Наконец, на пятом этапе обработки запроса происходит его реальное выполнение.

Оптимизатор. Его назначение. Этапы работы оптимизатора.

Одним из основных преимуществ реляционных СУБД является механизм запросов на основе декларативного языка запросов SQL. При формулировании запроса пользователь указывает ЧТО он хочет получить а за то КАК это получить , отвечает СУБД. Поскольку существует потенциально очень большое множество способов выполнить конкретный запрос (комбинация способов и порядка соединения таблиц, путей доступа к данным и т.д.), появляется задача выбрать из всего множества способов выполнения запроса оптимальный. За эту задачу отвечает оптимизатор запросов.
Функция оптимизатора - выбрать наиболее оптимальный (исходя из набора критериев) план выполнения запроса. При формировании оптимального плана, оптимизатор решает следующие задачи

Вычисление выражений и операций
Преобразование SQL операторов
Выбор способа оптимизации - по стоимости или по правилам
Выбор путей доступа
Выбор порядка соединений таблиц
Выбор метода соединений таблиц
Определение наиболее эффективного плана выполнения
В Oracle реализовано два подхода к оптимизации запроса, отличающиеся в выборе критериев оптимизации.

Оптимизация по правилам (RULE BASED). Подход, при котором учитываются только способы доступа к данным, с зафиксированными приоритетами по эффективности доступа. Данный подход использовался в ранних версиях ORACLE и обладает существенным недостатком - он не учитывает реального распределения данных.
Оптимизация по стоимости (COST BASED). Помимо эффективности различных путей доступа к данным, учитывается так же статистика по распределению данных и ресурсов операционной системы.

Режимы работы оптимизатора по стоимости. Установка режимов. Параметры, влияющие на работу оптимизатора.

CBO управляется статистикой. Причем, когда он вычисляет стоимость, он учитывает количество блоков в таблице, количество строк, количество различных значений индексов и так далее. Чтобы он корректно работал, статистику надо регулярно собирать. Работа оптимизатора управляется параметром optimizer_mode, который может указываться на уровне сессии или на уровне экземпляра. Он может иметь следующие значения.
optimizer_mode = rule - RBO (был заморожен в версии 7), RBO, например, не умеет пользоваться Bitmap индексами.
optimizer_mode = all_rows - CBO, выбирает план выполнения с оптимальной стоимостью, режим работы оптимизатора по умолчанию.
optimizer_mode = first_rows - CBO, вычисляется стоимость разных планов выполнения, выбирает несколько планов с оптимальной стоимостью, по разным эвристическим соображениям пытается выбрать план, который наиболее быстро возвращает первые строки.
optimizer_mode = choose - Oracle сам выбирает, какой режим оптимизатора выбрать. Самый плохой случай: установлен данный режим и по каким-то таблицам есть статистика, а по каким-то нет. Пример, если хотя бы по одной из таблиц в запросе статистика есть, то в большинстве случаев будет использоваться all_rows, а если ни по одной из таблиц нет, то - rule. Если RBO не поддерживает интерфейс запроса (например, Bitmap-индексы), то используется CBO. CBO может оптимизировать запросы по таблицам, по которым не собрана статистика, используя умолчания для таблиц.
optimizer_mode = first_rows_1, first_rows_10, first_rows_1000 - при использовании first_rows Oracle вычисляет стоимость выполнения всего оператора, потом выбирает оптимальный план, при использовании first_rows_n вычисляет стоимость получения первых n строк, а стоимость выполнения всего оператора не вычисляется (данные режимы оптимальны для форм и нервных операторов ).

Пример:

alter session set optimizer_mode= first_rows;

Статистика. Назначение, способы формирования.

Отсутствие статистики оптимизатора или устаревшие статистические данные часто являются причиной неоптимальной производительности обработки запросов. Оптимизатор, работающий на основе стоимости, использует для определения стоимости пути доступа такую статистику, как число элементов (cardinality) таблицы, число возможных значений столбца и распределение данных. Стоимость является мерой того, сколько памяти, ресурсов процессора и каналов ввода-вывода потребуется для выполнения запроса. Чтобы эффективно использовать оптимизатор, работающий на основе стоимости, нужно собрать статистику числа элементов (cardinality) и распределения данных для каждой таблицы, индекса и материализованного представления. Статистика собирается с помощью пакета DBMS_STATS. Статистические данные можно собирать либо путем чтения всех строк, либо путем проведения оценки на основе чтения только небольшой выборки строк или блоков. В пакете DBMS_STATS предлагаются процедуры для сбора статистики уровня базы данных, схемы или таблицы, а также раздела таблицы.

Сбор статистики с помощью analyze

analyze table t compute statistics for table for all indexes for all columns;
analyze table t compute statistics for table for all indexes for all indexed columns;
analyze table t compute statistics for table for columns i, s;

analyze index t_i compute statistics for table for all indexes for all columns;

analyze table t estimate statistics for table for all indexes for all columns sample 10 rows;
analyze table t estimate statistics for table for all indexes for all columns sample 10 percent;

analyze table t compute statistics for table for all indexes for all columns size 100;
analyze table t compute statistics for table for columns i size 100, s size 200;

Сбор статистики с помощью пакета dbms_stats

execute dbms_stats.gather_index_stats(ownname=>'stud', indname=>'i', partname=>null, estimate_percent=>50, stattab=>null, statid=>null, statown=>null);

execute dbms_stats.gather_table_stats(ownname=>'stud', tabname=>'t', partname=>null, estimate_percent=>50, block_sample=>false, 
                                      method_opt=>'FOR ALL COLUMNS', degree=>null, cascade=>true, stattab=>null, statid=>null, statown=>null);

execute dbms_stats.gather_table_stats(ownname=>'stud', tabname=>'t', partname=>null, estimate_percent=>50, block_sample=>false, 
                                      method_opt=>'FOR COLUMNS object_name, object_id', degree=>null, cascade=>true, stattab=>null, 
                                      statid=>null, statown=>null);

execute dbms_stats.gather_schema_stats(ownname=>'stud', estimate_percent=>50, block_sample=>false, method_opt=>'FOR ALL COLUMNS', 
                                       degree=>null, cascade=>true, stattab=>null, statid=>null, statown=>null);

execute dbms_stats.delete_index_stats(ownname=>'stud', indname=>'i', partname=>null, stattab=>null, statid=>null, statown=>null);

execute dbms_stats.delete_table_stats(ownname=>'stud', tabname=>'t', partname=>null, stattab=>null, statid=>null, 
                                      statown=>null, cascade_parts=>true, cascade_columns=>true);

execute dbms_stats.delete_schema_stats(ownname=>'stud', stattab=>null, statid=>null, statown=>null);

Статистика по таблицам

Данные по статистике таблиц можно посмотреть в словаре USER_TABLES. Основная статистика -

количество строк
количество блоков
количество пустых блоков
среднее доступное свободное пространство
количество мигрировавших строк
средняя длина строки

Пример:

select * from user_tables where table_name = 'T';

analyze table t delete statistics;

select num_rows, blocks, empty_blocks, avg_space, chain_cnt, avg_row_len, avg_space_freelist_blocks, 
       num_freelist_blocks, sample_size, last_analyzed from user_tables 
   where table_name = 'T';

analyze table t compute statistics for table;

select num_rows, blocks, empty_blocks, avg_space, chain_cnt, avg_row_len, avg_space_freelist_blocks, 
       num_freelist_blocks, sample_size, last_analyzed from user_tables 
   where table_name = 'T';

Статистика по индексам

Статистику по индексам можно посмотреть в словаре USER_INDEXES. Основная статистика -

глубина индекса
кол-во листовых блоков
кол-во различн. ключей
средн. кол-во лист. блоков на ключ
средн. кол-во блоков данных на ключ
кол-во узлов индекса
фактор кластеризации (кол-во блоков, которое надо выбрать для выборки всех строк из таблицы по индексу)

Пример:

analyze table t delete statistics;

select blevel, leaf_blocks, distinct_keys, avg_leaf_blocks_per_key, avg_data_blocks_per_key, 
       clustering_factor, num_rows, sample_size, last_analyzed from user_indexes 
   where table_name = 'T' and index_name = 'T_I';

analyze table t compute statistics for all indexes;

select blevel, leaf_blocks, distinct_keys, avg_leaf_blocks_per_key, avg_data_blocks_per_key, 
       clustering_factor, num_rows, sample_size, last_analyzed from user_indexes 
   where table_name = 'T' and index_name = 'T_I';

Статистика по столбцам

Статистику по столбцам можно посмотреть в словаре USER_TAB_COLUMNS, USER_TAB_COL_STATISTICS. Статистика -

кол-во различн. значений
мин значение
макс значение
кол-во null

Пример:

select * from user_tab_columns where table_name = 'T' and column_name = 'I';
select * from user_tab_col_statistics where table_name = 'T' and column_name = 'I';

analyze table t delete statistics;

select * from user_tab_col_statistics where table_name = 'T' and column_name = 'I';

analyze table t compute statistics for all columns;

select * from user_tab_col_statistics where table_name = 'T' and column_name = 'I';

Данная статистика не содержит одну очень важную информацию - распределение значений по диапазонам. Для хранения этой информации используются гистограммы. Существует два типа гистограмм - частотные гистограммы и сбалансированные по высоте. Максимальное количество брикетов в гистограмме - 254. Если количество различных значений в столбце меньше указанного размера гистограммы, то строятся частотная гистограмма. В противном случае - сбалансированная по высоте.
Сбалансированные по высоте гистограммы содержат одинаковое количество строк в каждом брикете гистограммы. Значение ячейки соответствует максимальному значению столбца в группе. Частотные гистограммы содержат столько брикетов, сколько есть различных значений в столбце. Значение ячейки содержит количество строк со значениями столбца, меньше либо равными данному.

Пример:

analyze table t compute statistics for columns s size 254;
select endpoint_number, endpoint_value, substr(endpoint_actual_value, 1, 30) 
   from user_tab_histograms where table_name = 'T' and column_name = 'S';

analyze table t compute statistics for columns s size 8;

select endpoint_number, endpoint_value, substr(endpoint_actual_value, 1, 30) 
   from user_tab_histograms where table_name = 'T' and column_name = 'S';

analyze table t compute statistics for columns s size 254;

select endpoint_number, endpoint_value, substr(endpoint_actual_value, 1, 30) 
   from user_tab_histograms where table_name = 'T' and column_name = 'S';

select s, count(*) from t group by s;

Пути доступа к данным

Подготовка данных.

-- создание объектов
drop table h;

-- create table h(p number constraint pk primary key, s varchar2(100) null, n number null, nu number null constraint un unique);
create table h(p number, s varchar2(100) null, n number null, nu number null);

Полное сканирование таблицы. Преимущества, недостатки.

Для выборки данных производится перебор всех строк таблицы, возвращаются строки подходящие под условия запроса. Может быть эффективнее использования индекса в случаях когда необходимо выбрать >25-30% записей в таблице. Возникает проблема High Water Mark.

-- полное сканирование таблицы. high water mark

insert into h select -rownum, object_name, object_id, object_id from all_objects where rownum < 100;

insert into h select rownum, object_name, object_id, object_id from all_objects;

select * from h where p < 0;

analyze table h compute statistics for table for all columns for all indexes;
select num_rows, blocks, empty_blocks from user_tables where table_name = 'H';

delete from h where p > 0;

Классические индексы.

Индексы на основе В*-дерева наиболее широко используемый тип индексной структуры в базе данных. По реализации они подобны двоичному дереву поиска. Цель их создания — минимизировать время поиска данных сервером Oracle. При наличии индекса по числовому столбцу, структура индекса может выглядеть так:

Блоки самого нижнего уровня в индексе, которые называют листовыми вершинами, содержат все проиндексированные ключи и идентификаторы строк (rid на схеме), ссылающиеся на соответствующие строки. Промежуточные блоки над листовыми вершинами называют блоками ветвления. Они используются для переходов по структуре. Например, если необходимо найти в индексе значение 42, надо начать с вершины дерева и двигаться вправо. При проверке этого блока оказывается, что необходимо перейти к блоку в диапазоне "от 40 до 50". Этот блок оказывается листовым и ссылается на строки, содержащие число 42. Интересно отметить, что листовые блоки фактически образуют двухсвязный список. Как только найдено "начало" среди листовых вершин, т.е. первое значение, очень легко просматривать значения по порядку (это называют также просмотром диапазона по индексу, index range scan). Проходить по структуре индекса больше не нужно; мы просто переходим по листовым вершинам. Это существенно упрощает поиск строк но условиям следующего вида:

where x between 20 and 30

-- индексы
-- структура индекса, rowid
truncate table h;
insert into h select rownum, object_name, object_id, object_id from all_objects where rownum < 10;
commit;

select rowid, p from h;

select * from h where rowid = 'AAAN95AAEAAAAfHAAA';

declare
	r_t number;
	o_n number;
	f_n number;
	b_n number;
	r_n number;
	r rowid;
begin
	select rowid into r from h where rownum < 2;

	dbms_rowid.rowid_info(r, r_t, o_n, f_n, b_n, r_n);

	dbms_output.put_line('rowid type    = ' || r_t);
	dbms_output.put_line('object number = ' || o_n);
	dbms_output.put_line('file number   = ' || f_n);
	dbms_output.put_line('block number  = ' || b_n);
	dbms_output.put_line('row number    = ' || r_n);
end;

select * from user_objects where object_name = 'H';
select * from user_tables where table_name = 'H';
select * from dba_data_files;

-- создание индексов
truncate table h;

insert into h select rownum, object_name, object_id, object_id from all_objects;

commit;

create index ind_s on h(s);
select * from user_indexes where table_name = 'H';
drop index ind_s;
select * from user_indexes where table_name = 'H';

create index ind_s_1 on h(s desc);
create unique index ind_n on h(n);

select index_name, index_type, table_name, status, funcidx_status from user_indexes where table_name = 'H';

-- информация по таблицам и индексам
select * from user_tables;
select * from user_tab_columns;
select * from user_constraints;
select * from user_cons_columns;
select * from user_indexes;
select * from user_ind_columns;

Доступ к таблицам по индексам

analyze table h compute statistics for table for all indexes for all indexed columns;

set autotrace on
set timing on

-- операции по индексу
drop index ind_s on h(s);
select * from h where s = 'aaaa';

create index ind_s on h(s);
select * from h where s = 'aaaa';

select * from h where s||'a' = 'aaaa';
select * from h where upper(s) = 'aaaa';

select * from h where s like 'aaaa%';
select * from h where s like '%aaaa';

drop index ind_nu;
select * from h where nu = 1222;

create index ind_nu on h(nu);
select * from h where nu = 1222;

select * from h where nu+1 = 1222;
select * from h where nu = 1222+1;
select * from h where abs(nu) = 1222;

select * from h where s = '123';
select * from h where s = 123;

-- выбор между доступом по индексам и full scan
delete from h;

insert into h(p, s, n, nu) select object_id, object_name, object_id, object_id from all_objects where rownum < 5;

analyze table h compute statistics for table for all indexes for all indexed columns;

select * from h where s = 'DUAL';

delete from h;

insert into h(p, s, n, nu) select object_id, object_name, object_id, object_id from all_objects;

-- со старой статистикой !!!
select * from h where s = 'DUAL';

analyze table h compute statistics for table for all indexes for all indexed columns;

-- с новой статистикой !!!
select * from h where s = 'DUAL';

-- обработка null значений

-- поля null не проверяются на уникальность
insert into h(p, s, n, nu) values(-1, 'asd', null, null);
insert into h(p, s, n, nu) values(-2, 'asd', null, null);
insert into h(p, s, n, nu) values(-3, 'asd', null, -1);

select * from h where s is null;

Функциональные индексы.

Индексы по функции. Эти индексы на основе В*-дерева или битовых карт хранят вычисленный результат применения функции к столбцу или столбцам строки, а не сами данные строки. Это можно использовать для ускорения выполнения запросов вида:
SELECT * FROM T WHERE ФУНКЦИЯ(СТОЛБЕЦ) = НЕКОТОРОЕ_ЗНАЧЕНИЕ,
поскольку значение ФУНКЦИЯ(СТОЛБЕЦ) уже вычислено и хранится в индексе.

-- функциональные индексы
select * from h where n*n = 100;

-- надо иметь такие права для создания функциональных индексов
alter system set query_rewrite_enabled=true;
grant query rewrite to stud;

create index ind_f_n on h(n*n);

select * from h where n*n = 100;

create index ind_n on h(n);

select index_name, index_type, table_name, status, funcidx_status from user_indexes where table_name = 'H';

select * from h where n = 100;
select * from h where n > 0;

Битовые индексы.

Обычно в В*-дереве имеется однозначное соответствие между записью индекса и строкой - запись индекса указывает на строку. В индексе на основе битовых карт запись использует битовую карту для ссылки на большое количество строк одновременно. Такие индексы подходят для данных с небольшим количеством различных значений, которые обычно только читаются. Столбец, имеющий всего три значения — Y, N и NULL, — в таблице с миллионом строк очень хорошо подходит для создания индекса на основе битовых карт. Предположим, создается индекс на основе битовых карт по столбцу JOB в таблице ЕМР:

 create BITMAP index job_idx on emp(job);

Сервер Oracle будет хранить в индексе примерно следующее:

Это показывает, что в строках 8, 10 и 13 находится значение ANALYST, тогда как в строках 4, 6 и 7 — значение MANAGER. Также понятно, что пустых строк нет (индексы на основе битовых карт содержат записи для пустых значений — отсутствие такой записи в индексе означает, что пустых строк нет). Если необходимо посчитать, в скольких строках хранится значение MANAGER, индекс на основе битовых карт позволит сделать это очень быстро. Если необходимо найти все строки, в которых в столбце JOB хранится значение CLERK или MANAGER, достаточно просто скомбинировать соответствующие битовые карты из индекса.

-- bitmap индексы
drop table bt;

create table bt(n number, s varchar2(100), b number, i number);

select min(object_id), max(object_id), 0.5*(max(object_id)+min(object_id)) from all_objects;

delete from bt;

insert into bt(n, s, b, i) 
select object_id, object_name, case when object_id > 36116 then 1 else 2 end, case when object_id > 36116 then 1 else 2 end from all_objects;

create index ibt_i on bt(i);
create bitmap index ibt_b on bt(b);

select index_name, index_type, table_name, status, funcidx_status from user_indexes where table_name = 'BT';

analyze table bt compute statistics for table for all indexes for all indexed columns;

select i from bt where i = 1;
select b from bt where b = 1;

drop index ibt_i;
create bitmap index ibt_i on bt(i);

select * from bt where i = 0 or b = 0;
select * from bt where i = 1 or b = 0;

drop index ibt_i;
create index ibt_i on bt(i);

select /*+ index(bt ibt_b) */ * from bt where b = 1;
select /*+ index(bt ibt_i) */ * from bt where i = 1;

select count(*) from bt where b = 1;
select count(*) from bt where i = 1;

update bt set b = null where rownum < 100;
update bt set i = null where rownum < 100;

select * from bt where i is null;
select * from bt where b is null;

Индекс-организованные таблицы.

Индекс-оргранизованные таблицы - кластерные индексы, в индексе хранятся значения столбцов, выбранных для индекса, соединенные в одно значение. Индекс-оргранизованные таблицы имеют фиктивный rowid - значение индекса. ри запросе осуществляется быстрое сканирование индексов. Использование кластерных индексов способно заметно увеличить производительность поиска данных даже по сравнению с некластерными индексами, особенно при работе с последовательностями данных. В качестве кластерного индекса следует выбирать столбцы, наиболее часто задаваемые в качестве критериев поиска. При этом не следует использовать для индексирования слишком длинные столбцы. Кластерный индекс может включать несколько столбцов, но количество столбцов кластерного индекса следует по возможности свести к минимуму. Следует избегать создания кластерного индекса для часто изменяемых столбцов, так как сервер должен будет выполнять физическое перемещение всех данных в таблице, чтобы они находились в упорядоченном состоянии, как того требует кластерный индекс. Для интенсивно изменяемых столбцов больше подходит некластерный индекс.

-- индекс-организованные таблицы

drop table ih;

create table ih(p number constraint ipk primary key, s varchar2(100) null, n number null, nu number null constraint iun unique)
organization index;

select constraint_name, constraint_type, table_name, index_name from user_constraints where table_name = 'IH';
select index_name, index_type, table_name, status, funcidx_status from user_indexes where table_name = 'IH';

insert into ih(p, s, n, nu) select object_id, object_name, object_id, object_id from all_objects;

analyze table ih compute statistics for table for all indexes for all indexed columns;

commit;

select * from ih where s = 'h';

select s from ih where p = 123;

create index iind_s on ih(s);

analyze table ih compute statistics for table for all indexes for all indexed columns;

select * from ih where s = 'h';

-- фактически это не сегмент таблицы а сегмент индекса
select * from user_segments where segment_name in ('H', 'IH', 'PK', 'IPK');

-- отсутствие full table scan - вместо этого fast full scan
select * from ih;

-- отсутствие rowid в индексных таблицах
select rowid from ih where rownum < 10;

Принципы построения запросов для эффективного использования индексов.

Полного сканирования таблицы при запросе, в том числе и в Oracle, стараются избежать, для этого используют индексы. При выборе столбца для индекса следует проанализировать, какие типы запросов чаще всего выполняются пользователем, и какие столбцы являются ключевыми. Не стоит индексировать столбцы, которые только считываются и не играют никакой роли в определении порядка выполнения запроса. Не следует индексировать слишком длинные столбцы, например, столбцы с адресами или названиями компаний, достигающие длины несколько десятков символов, т.к. индексирование длинных столбцов может существенно снизить производительность работы сервера. Действительно, каждая запись файла индекса должна содержать, помимо ссылки на строку таблицы и само значение в индексированном столбце, поэтому, индексируя длинные столбцы, пространство базы данных расходуется неэкономно. Кроме того, операции обновления и сравнения длинных столбцов занимают много времени. В крайнем случае, можно создать укороченный вариант такого длинного столбца (до десяти символов), и индексирование проводить по этому столбцу.

Материализованные представления

Как известно, представление (view) — это запрос на выборку, хранящийся на сервере, как отдельный объект. Так как, результат этого запроса можно рассматривать в качестве таблицы, представление допускается использовать в других запросах, также как любую обычную таблицу. Материализованное представление хранится на сервере в виде таблицы, которая автоматически обновляется при изменении данных, имеющих отношение к этому представлению. При выполнении запроса, основанного на материализованном представлении, используются не исходные таблицы и запрос, на котором основано представление, а данные, которые хранятся в этом представлении. За счет этого скорость выполнения запросов может быть повышена на порядки. Основным недостатком материализованного представления является то, что для его использования требуется дополнительная дисковое пространство и его необходимо синхронизировать с основными данными.
Создание объектов

drop table h1;
drop table h2;

create table h1(n number primary key, s varchar2(100) null, n1 number null, s1 varchar2(100));
create table h2(n number primary key, s varchar2(100) null, n1 number null, s1 varchar2(100));

insert into h1(n, s, n1, s1) select object_id, object_name, object_id, owner from all_objects;
insert into h2(n, s, n1, s1) select object_id, object_name, object_id, owner from all_objects;

analyze table h1 compute statistics for table for all columns for all indexes;
analyze table h2 compute statistics for table for all columns for all indexes;

create view v1 as select h1.n, h1.s, h2.s1, h2.n1 from h1 inner join h2 on h1.n = h2.n and h1.s = h2.s;

select * from v1 where n = 10 and s = '23423';

grant create materialized view to bor;
grant query rewrite to bor;

create materialized view v2 as select h1.n, h1.s, h2.s1, h2.n1 from h1 inner join h2 on h1.n = h2.n and h1.s = h2.s;

select * from v2 where n = 10 and s = '23423';

create index iv on v2(n);

select * from v2 where n = 10 and s = '23423';

Информация по материализованным представлениям

select * from user_mviews;
select * from user_views;
select * from user_segments where segment_name in('V1', 'V2');

create materialized view v3 as select h1.s1 s1, count(h1.n1) n1 from h1 group by s1 having count(*) > 1;

select h1.s1 s1, count(h1.n1) n1 from h1 group by s1 having count(*) > 1;

select s1, n1 from v3;

Оценка требуемого размера

variable v_rows number
variable v_bytes number
exec DBMS_MVIEW.ESTIMATE_MVIEW_SIZE(1, 'select h1.s1 s1, count(h1.n1) n1 from h1 group by s1 having count(*) > 1', :v_rows, :v_bytes);
print :v_rows :v_bytes

Способы построения

Существует два способа формирования представления - непосредственно при его создании или отложенное, по явной команде.

drop materialized view v3;

create materialized view v3 build immediate as select h1.s1 s1, count(h1.n1) n1 from h1 group by s1 having count(*) > 1;
create materialized view v3 build deferred as select h1.s1 s1, count(h1.n1) n1 from h1 group by s1 having count(*) > 1;

select * from v3;

exec dbms_mview.refresh('V3');

select * from v3;

Частота обновления

Материализованное представление может синхронизироваться с исходными данными или автоматически на момент завершения транзакции или по явной команде синхронизации. Возможна синхронизация по расписанию.

drop materialized view v3;

create materialized view v3 build immediate refresh complete on commit as select h1.s1 s1, count(h1.n1) n1 from h1 group by s1;

insert into h1(n, s, n1, s1) values(-1, 'aaa', -1, 'aaa');
insert into h1(n, s, n1, s1) values(-2, 'aaa', -2, 'aaa');

select * from v3;

commit;

select * from v3;

drop materialized view v3;

create materialized view v3 build immediate refresh complete on commit as select h1.s1 s1, count(h1.n1) n1 from h1 group by s1 having count(*) > 1;

create materialized view v3 build immediate refresh complete on demand as select h1.s1 s1, count(h1.n1) n1 from h1 group by s1 having count(*) > 1;

insert into h1(n, s, n1, s1) values(-3, 'bbb', -3, 'bbb');
insert into h1(n, s, n1, s1) values(-4, 'bbb', -4, 'bbb');

commit;

exec dbms_mview.refresh('V3', 'F' /* ? C */); 

drop materialized view v3;

create materialized view v3 build immediate refresh complete start with '17-MAY-2004' next sysdate+1 as select h1.s1 s1, count(h1.n1) n1 from h1 group by s1 having count(*) > 1;

@?/rdbms/admin/utlxmv.sql

set linesize 200
column statement_id format a15
column mvowner format a5
column mvname format a10
column statement_id format a3
column related_text format a10
column msgtxt format a60

exec dbms_mview.explain_mview('v3', '111');

select * from mv_capabilities_table;

exec dbms_mview.explain_mview('select h1.s1 s1, count(h1.n1) n1 from h1 group by s1', '222');

select * from mv_capabilities_table where statement_id = 222;

Способ обновления

Существует два основных способа обновления материализованных представлений

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

Частичное обновление. Для каждой таблицы. Участвующей в запросе на представление, создается журнал изменений и изменения представления выполняются только данных, измененных в основных таблицах.

drop materialized view v3;

create materialized view v3 build immediate refresh complete as select h1.s1 s1, count(h1.n1) n1 from h1 group by s1 having count(*) > 1;

insert into h1(n, s, n1, s1) values(-1, 'aaa', -1, 'aaa');
insert into h1(n, s, n1, s1) values(-2, 'aaa', -2, 'aaa');

commit;

exec dbms_mview.refresh('v3');

select * from v3;

drop materialized view v3;

create materialized view v3 refresh fast as select h1.s1 s1, count(h1.n1) n1 from h1 group by s1 having count(*) > 1;

create materialized view v3 refresh fast as select h1.s1 s1, count(h1.n1) n1 from h1 group by s1;
create materialized view v3 refresh fast as select h1.s1 s1, avg(n1) n, count(h1.n1) n1 from h1 group by s1;
create materialized view v3 refresh fast as select h1.s1 s1, avg(n1) n from h1 group by s1;

create materialized view log on h1 nologging with sequence, rowid (n1, s1) including new values;
create materialized view log on h2 nologging with sequence, rowid (n1, s1) including new values;

drop materialized view v3;

create materialized view v3 refresh fast on commit as select h1.s1 s1, count(h1.n1) n1 from h1 group by s1;

insert into h1(n, s, n1, s1) values(-11, 'aaa1', -11, 'aaa1');
insert into h1(n, s, n1, s1) values(-21, 'aaa1', -21, 'aaa1');

commit;

exec dbms_mview.explain_mview('v3', '2');
select * from mv_capabilities_table where statement_id = 2;

drop materialized view v3;

exec dbms_mview.explain_mview('select h1.s1 s1, count(h1.n1) n1 from h1 group by s1', '4');
select * from mv_capabilities_table where statement_id = 4;

select * from user_segments where segment_name like 'MLOG%';

select * from user_mview_logs;


drop materialized view log on h1;
drop materialized view log on h2;

create materialized view log on h1 nologging with sequence, rowid (n, n1, s1) including new values;
create materialized view log on h2 nologging with sequence, rowid (n, n1, s1) including new values;

truncate table mv_capabilities_table;

exec dbms_mview.explain_mview('select h1.s1 s1, h2.s1 s2, h1.n n from h1, h2 where h1.n = h2.n', '5');
select * from mv_capabilities_table where statement_id = 5;

truncate table mv_capabilities_table;

exec dbms_mview.explain_mview('select h1.rowid h1_rowid, h2.rowid h2_rowid, h1.s1 s1, h2.s1 s2, h1.n n from h1, h2 where h1.n = h2.n', '5');
select * from mv_capabilities_table where statement_id = 5;

drop materialized view v3;

create materialized view v3 refresh fast as select h1.s1 s1, h2.s1 s2, h1.n n from h1, h2 where h1.n = h2.n;
create materialized view v3 refresh fast as select h1.rowid h1_rowid, h2.rowid h2_rowid, h1.s1 s1, h2.s1 s2, h1.n n from h1, h2 where h1.n = h2.n;

create materialized view v3 refresh fast on commit as select h1.s1 s1, h2.s1 s2, h1.n n from h1, h2 where h1.n = h2.n;
create materialized view v3 refresh fast on commit as select h1.rowid h1_rowid, h2.rowid h2_rowid, h1.s1 s1, h2.s1 s2, h1.n n from h1, h2 where h1.n = h2.n;

Вложенные страницы

11. Optimization

Выполнение запросов в Oracle. Общая схема и взаимодействие с клиентским приложением и машиной PL/SQL.

Оптимизатор. Его назначение. Этапы работы оптимизатора.

Режимы работы оптимизатора по стоимости. Установка режимов. Параметры, влияющие на работу оптимизатора.

Статистика. Назначение, способы формирования.

Сбор статистики с помощью analyze

Сбор статистики с помощью пакета dbms_stats

Статистика по таблицам

Статистика по индексам

Статистика по столбцам

Пути доступа к данным

Полное сканирование таблицы. Преимущества, недостатки.

Классические индексы.

Доступ к таблицам по индексам

Функциональные индексы.

Битовые индексы.

Индекс-организованные таблицы.

Принципы построения запросов для эффективного использования индексов.

Материализованные представления

Информация по материализованным представлениям

Оценка требуемого размера

Способы построения

Частота обновления

Способ обновления