НОУ ИНТУИТ | Распределенные базы и хранилища данных. Лекция 12: Оптимизация выполнения запросов

Опубликован: 11.03.2009 | Уровень: специалист | Доступ: платный

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

Для выборки данных производится перебор всех строк таблицы, возвращаются строки подходящие под условия запроса. Может быть эффективнее использования индекса в случаях когда необходимо выбрать >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;

Листинг 12.2.

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

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;

Листинг 12.3.

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

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

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;

Листинг 12.4.

Дальше >>

Распределенные базы и хранилища данных

Распределенные базы и хранилища данных

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

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

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

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

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

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

Вопросы и ответы

Студенты

Авторизоваться

Распределенные базы и хранилища данных

Распределенные базы и хранилища данных

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

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

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

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

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

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

Вопросы и ответы

Студенты