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

Опубликован: 11.03.2009 | Доступ: свободный | Студентов: 2791 / 676 | Оценка: 4.22 / 4.22 | Длительность: 11:41:00

Тема: Базы данных

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

Индекс-организованные таблицы - кластерные индексы, в индексе хранятся значения столбцов, выбранных для индекса, соединенные в одно значение. Индекс-организованные таблицы имеют фиктивный 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;

Листинг 12.5.

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

Полного сканирования таблицы при запросе, в том числе и в 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';

Листинг 12.6.

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

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;

Листинг 12.7.

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

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

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

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;

Листинг 12.7.

Практические занятия

Постановка задачи. Проектирование данных на концептуальном и логическом уровнях. Нормализация отношений.

Презентация по ER-моделированию

ER нотации

Пример модели в ERwin

Подготовка SQL скриптов генерации схемы отношений БД в ERwin. Разработка скрипта для ввода тестовой информации.

Видео-презентация (Для проигрывания требуется Windows Media Player)

Архитектура MS SQL Server 2005. Настройка и использование основных компонент среды. Создание учебной базы данных.

Видео-презентация (Для проигрывания требуется Windows Media Player)

Работа с СУБД MS SQL Server 2005, ORACLE 10g. Примеры соединений с БД, технологии разработки клиенского приложения

Использование технологии Java Database Connectivity (JDBC) для работы с базами данных

Презентация

Примеры к презентации

SQL-скрипты, проект и исходные коды

package org.mai806.jdbcsample;

import java.sql.*;

public class QuerySample {
    
    public static void main(String[] args) throws Exception {
        
        /* ======== Подключение к MS SQL Server ===== */
        // Загрузка драйвера
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        // Соединение с базой данных
        Connection connection = DriverManager.getConnection(
                "jdbc:sqlserver://localhost:1433;databaseName=o01;", 
                // localhost - сервер СУБД, o01 - имя базы данных
                "sa", "123"); // пользователь, пароль
        
        /* ======== Подключение к Oracle ============
        // Загрузка драйвера
        Class.forName("oracle.jdbc.OracleDriver");
        // Соединение с базой данных
        Connection connection = DriverManager.getConnection(
                        "jdbc:oracle:thin:@localhost:1521:orcl", 
                        // localhost - сервер СУБД, orcl - SID базы оракла
                        "o01", "o01"); // пользователь, пароль         
        
        // Создание Statement
        PreparedStatement stmt = connection.prepareStatement
        ("select ID, NAME from PERSON where NAME like ?");
        stmt.setString(1, "%S%");
        // Выполнение запроса
        ResultSet rs = stmt.executeQuery();
        // Перебор результата выполнения запроса
        while(rs.next()) {
            // Пример выбора параметра по номеру или по имени
            System.out.println("ID: " +
                    rs.getLong(1) + "; NAME="+
                    rs.getString("NAME"));
        }
        
        // закрытие использованных ресурсов БД
        rs.close();
        stmt.close();
        connection.close();
    }
}

Листинг P.1. Выполнение запроса: QuerySample.java

package org.mai806.jdbcsample;

import java.sql.*;
import java.util.ResourceBundle;

public class StoredProcedureSample {
    
    private static Connection connection = null;
    public static void main(String[] args) throws Exception {
        // Получение соединения из значений параметров в файле properties
        ResourceBundle properties = ResourceBundle.getBundle("database");
        Class.forName(properties.getString("driver"));
        connection = DriverManager.getConnection(
                properties.getString("url"),
                properties.getString("user"),
                properties.getString("password"));
        
        transferAmount(1,2,100.0);
        connection.close();
    }
    
    /**
     * Переводит указанную сумму с одного счета на другой
     * @param from счет плательщика
     * @param to счет получателя
     * @param amount сумма
     */
    public static void transferAmount(long from, long to, double amount)
    throws Exception {
        // Создание Statement
        CallableStatement stmt
                = connection.prepareCall("{call TransferAmount(?,?,?)}");
        // Установка параметров
        stmt.setLong(1, from);
        stmt.setLong(2, to);
        stmt.setDouble(3, amount);
        // Выполнение процедуры
        stmt.execute(); 
    }
}

Листинг P.2. Выполнение хранимой процедуры: StoredProcedureSample.java

package org.mai806.jdbcsample;

import java.sql.*;
import java.util.ResourceBundle;

public class TransactionalSample {
    
    private static Connection connection = null;
    public static void main(String[] args) throws Exception {
        
        // Получение соединения из значений параметров в файле properties
        ResourceBundle properties = ResourceBundle.getBundle("database");
        Class.forName(properties.getString("driver"));
        connection = DriverManager.getConnection(
                properties.getString("url"),
                properties.getString("user"),
                properties.getString("password"));
        
        // Ручное управление транзакциями
        connection.setAutoCommit(false);
        
        try {
            transferAmount(2, 1, 10.0);
        } finally {
            connection.close();
        }
    }
    
    /**
     * Переводит указанную сумму с одного счета на другой
     * @param from счет плательщика
     * @param to счет получателя
     * @param amount сумма
     */
    public static void transferAmount(long from, long to,
            double amount) throws Exception {
        PreparedStatement stmt = null;
        Statement query = null;
        try {
            stmt = connection.prepareStatement
            ("update ACCOUNT set AMOUNT=AMOUNT+? where ID=?");
            // Забираем сумму со счета плательщика
            stmt.setDouble(1, -amount);
            stmt.setLong(2, from);
            stmt.execute();
            
            // Добавляем сумму на счет получателя
            stmt.setDouble(1, amount);
            stmt.setLong(2, to);
            stmt.execute();
            
            // Пост-проверка: отрицательность счета плательщика
            query = connection.createStatement();
            ResultSet rs = query.executeQuery(
                    "select AMOUNT from ACCOUNT where ID="+from+" and AMOUNT<0");
            if (rs.next()) {
                throw new Exception("На счете №"+from+" 
                недосточно средств ["+(amount+rs.getDouble(1))+"] 
                для снятия суммы ["+amount+"]");
            }
            connection.commit();
            System.out.println("Перечисление средств успешно выполнено");
        } catch(Exception e) {
            e.printStackTrace();
            connection.rollback();
        } finally {
            if (stmt!=null)
                stmt.close();
            if (query!=null)
                query.close();
        }
    }
    
}

Листинг P.3. Работа с транзакциями: TransactionalSample.java

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

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

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