With read only oracle view что означает
Представление - это инструкция, которая инкапсулирует сложный запрос. Представление - это окно, которое предоставляет запрос, и все данные берутся из исходной таблицы.
Простые и сложные представления
С практической точки зрения, классификация между простыми и сложными представлениями основана на возможности выполнять DML команды к представлению: простые представления могут (обычно) выполнять DML запросы; сложные – не могут. Определим какие представления простые, а какие сложные
- Простые представления используют данные из одной таблицы, без использваония функций и аггрегации
- Сложные представления могут использовать объединениям, функции и аггрегацию.
Согласно этим определениями первое и третье представления из прошлой подглавы являютяс простыми, а второе и четвертое сложные.
Команды INSERT, UPDATE или DELETE нельзя выполнить к сложным представлениям. Соотношение строк в представлении к исходной таблице неможет всегда быть один-к-одному, что необходимо для DML операций. Обычно возможно выполнять DML команды к простым представлениям, но не всегда. Например если представление не включает в себя столбец с ограничением обязательности, тогда INSERT к представление не выполнится успешно (выполнится если у столбца есть значение по умолчанию). Выполнение такого запроса вернет странную ошибку так как ошибка ссылается на таблицу и столбец которого нет в запросе, как показано на примере в рисунке 7-5.
Превое представление RNAME_V на рисунке удовлетворяет определению простого представления, однаго команда INSERT не может быть выполена так как отсутствует значение для обязательного поля. Второе представление RUPPERNAME_V – сложное представление так как основано на результате выполнения функции. Это делает невозможным вставку значений, так как нет способа БД узнать что действительно необходимо вставить в таблицу. Однако команда DELETE может быть выполнена, так как нет зависимости от использования функции.
Представления для безопасности
Иногда пользователь должен видеть только определённые строки или столбцы таблицы. Доступно несколько способов для достижения этой цели, но создание представления обычно наиболее лёгкий. К примеру таблица HR.EMPLOYEES содержит личную информацию которая не должна быть видна никому кроме отдела персонала. Но финансовый отдел должен видеть информацию о затратах. Такое представление может убрать личную информацию
create view hr.emp_fin as select hire_date,job_id,salary,commission_pct,department_id from hr.employees;
Обратите внимание на название схемы для таблицы источника и схемы представления: представления это объекты схемы и могут получать данные из таблицы той же схемы, или другой. Если схема не указана явно используется текущая схема.
Финансовый отдел может получить доступ к представлению но не к таблице и тогда сотрудники смогут выполнять запросы вида
select * from emp_fin where department_id=50;
Будет доступно всего пять столбцов вместо всех столбцов таблицы EMPLOYEES. Представление можно объединять с другими таблицами или использовать функции аггрегация как будто это таблица
select department_name, sum(salary) from departments natural join emp_fin group by department_name;
Хорошо продуманный набор представлений может реализовывать всю систему безопасности в БД, предоставляя пользователям доступ к данным которые им необходимы и сокрывая всё остальное.
Изменение данных представления
Таблицы, которые используются в запросе представления, называются базовыми таблицами.
Представления, которые созданы на основании одной базовой таблицы, можно изменять также, как и обычную таблицу.
Например, создадим представление vdepartments и добавим в него несколько записей.
Конечно, фактически данные добавляются не в представление, а в базовую таблицу(в данном случае departments ):
Строки можно и удалять, а также и изменять:
Посмотрим на результаты:
Представления чтобы сделать данные понятными
Структуры базы данных это нормализованные таблицы. Нет смысла ожидать от пользователя понимания нормализованных данных. Таблицы связываются между собой первчиными ключами и вторичными ключами, но ключи ограничений обычно не видны пользователю, значения ключей генерируются автоматически из сиквенсов. Пользователю нет нужды знать как объединять родительские таблицы с дочерними, он хочет видеть данные в окнах приложения или отчёты содержащие понятную ему информацию. Для этого можно использовать представления.
Помимо удобства предоставления данных пользователю, использование представлений добавляет уровень абстракции между объектами пользователя и объектами БД что может быть полезно при разработке и поддержке. Возможно изменить структуру данных без переписывания приложения. Если изменились таблицы достаточно просто изменить определение таблицы без изменений кода SQL или PL/SQL. Также представления можно использовать для добавления приложению совместимости между разными базами данных.
Представления для упрощения запросов
Для пользователя будет гораздо проще выбирать данные если сложная работа (такая как объединения таблиц или группировка данных с аггрегацией) сделано за них в коде запроса который определяет представление. В предыдущем примере сотрудникам финансового отдела необходимо было объединять представление EMP_FIN с таблицей DEPARTMENTS и суммировать зарплату по департаменту. Но можно создать новое представление
create view dept_sal as select d.department_name, sum(e.salary) from departments d left outer join employees e on d.department_id=e.department_id group by department_name order by department_name;
И тогда сотрудники смогут писать запросы к представлению DEPT_SAL без необходимости знать ою объединениях или о том как сортировать результат
select * from dept_sal;
В частности, пользователям даже не надо знать как убедиться что все департаменты отображены, даже в которых нет сотрудников. Представление созданное в примере выше отобразит все департаменты.
Строим ETL процесс из материализованных представлений
У нас имеются таблица-источник и справочник: table_a и dim_b соответственно. Под собой они скрывают реальные сущности, однако в разборе обойдемся абстрактными названиями. Приведенные таблицы нам необходимо объединить, отфильтровать, агрегировать, при этом оставив только актуальную информацию. В итоге мы должны получить что-то похожее (Рис. 1):
Рисунок 1. Целевая схема ETL-процесса
Наполнение таблицы-источника table_a крайне критично, потому что даже в "сыром" виде она несет полезную информацию. Из-за этого вариант с ON COMMIT отпадает, поскольку если наш процесс сломается, то новые данные не поступят в таблицы-источники, следовательно выбираем ON DEMAND . Да и в общем-то у нашей учетной есть права только на чтение этой таблицы.
А что на счет опции FAST ? Тоже не подойдет, поскольку исходя из постановки задачи, нам нужны только свежие данные, поэтому используем COMPLETE .
Создание, изменение и удаление представлений
Синтаксис создания представления
CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW
[schema.]viewname [(alias [,alias]…]
[WITH CHECK OPTION [CONSTRAINT constraintname]]
[WITH READ ONLY [CONSTRAINT constraintname]] ;
Помним что представление это объект схемы. Нет причин не создавать представления владельцем которых является один пользователь, таблицы-основания которых принадлежат другому пользователю. По умолчанию представление будет создано в текущей схеме. Дополнительные директивы которые ещё не использовались в примерах выше это
OR REPLACE – если представление уже существует оно будет удалено перед созданием нового
FORCE или NOFORCE – Использование FORCE приведёт к созданию представления даже если базовые таблицы не существуют. NOFORCE значение по умолчанию и если таблицы не существуют команды выполняется с ошибкой
WITH CHECK OPTION – эта директива влияет на DML команды. Если подзапрос включает условие WHERE, тогда эта директива предотвратит возможность вставки стро которые не видно в представлении, или совершать обновления данных которое приведёт к пропаже данных из представления. По умолчанию эта директива отключена что может приводить к неожидаемым результатам выполнения запросов
WITH READ ONLY – отключения возможности использование DML команд к представлению
Набор псевдонимов позволяет назначать новые имена столбцам представления. Если псевдоним не указан, столбец будет иметь такое же имя как в таблице или будет использоваться псевдоним из подзапроса.
Команда ALTER VIEW в основном используется для компиляции представления. Представление должно быть успешно скомпилированно перед использованием. Когда преставление создаётся, Oracle проверяет что все столбцы и таблицы существуют. Если они не существуют компиляция происходит неудачно и представление не будет создано – но будет создано если вы используете директиву FORCE. В этом случае представление создастся, но будет недоступно для использования пока не будут созданы таблицы и столбцы используемые в подзапросе и не будет заново скомпилировано. Когда выполняется запрос к нескомпилированному представлению – Oracle попробует скомпилировать его автоматически. Если компиляция будет успешна (вы устранили проблемы) – то пользователи даже не узнает что что-то не работало – единственное отличие будет в том что запрос будет выполняться чуть дольше. Вам следует самим вручную компилировать представления чтобы убедиться что компиляция прошла успешно, вместо того чтобы позволять пользователям обнаружить ошибку.
Невозможно изменить определения столбцов после создания представления так как это делается для таблицы. Представление должно быть удалено и затем создано новое. Синтаксис команды DROP
DROP VIEW [schema.]viewname ;
Использование директивы OR REPLACE в команде CREATE VIEW приведёт к автоматическому удаление представления (если оно существует) перед созданием.
What is the difference between Views and Materialized Views in Oracle?
2). Войдите в систему как пользователь с разрешением dba.
Разрешения dba можно использовать для межпользовательских запросов!
Представления для предотвращения ошибок
Пользователи всегда допускают ошибки, но хорошо-спроектированные представления могут предотвратить некоторые из них, возникающие из-за отсустствия понимания как надо понимать данные. В прерыдущем примере мы посмотрели пример когда представление отображает все департаменты, даже в которых нет сотрудников.
Представление помогает представить данные в недвусмысленном виде. Например многие приложения в реальности не удаляют данные. Рассмотрим таблицу
create table emp(empno number constraint emp_empno_pk primary key,ename varchar2(10),deptno number,active varchar2(1) default ‘Y’);
Столбец ACTIVE это флаг показывающий нанят ли сотрудник в текущий момент и при добавлении строки будет выставлен в ‘Y’. Когда пользователь, через пользовательский интерфейс “удалит” сотрудника, на самом деле выполнится запрос который обновит значение ACTIVE в ‘N’. Если пользователь не знает о структуре таблицы и такой особенности то результат “удаления” будет не очень понятен. Поэтому лучше дать пользователю доступ к представлению
create view current_staff as select * from emp where active=’Y’;
Запросы к такому представлению не отображат “удалённых” сотрудников.
1). Роль взгляда
- Просмотр может блокировать конфиденциальные поля
- Убедитесь, что данные головного офиса и филиала унифицированы по времени;
- Например: проверка штаб-квартиры из таблицы, ветвь, чтобы увидеть представление, чтобы гарантировать согласованность данных! Ограниченное предотвращение несвоевременного обновления данных запроса
3). Три синтаксиса для создания представлений
1. Синтаксис①: создать представление
- Формат грамматики
- Создать представление
- Использовать вид
3. Синтаксис: создание или обложка.
Если представление уже существует, таким образом вы можете перезаписать предыдущее представление с тем же именем.
4. Синтаксис: ограниченный вид доступен только для чтения.
Ранее созданный вид можно изменить;
- Измените данные через ранее созданное представление
Посредством запроса выясняется, что данные могут быть изменены через представление; но обычноНе рекомендуется изменять исходные данные через представление; Можно сделать вид доступным только для чтения
2). Как создать индекс
1. Индекс в одном столбце
Индекс с одним столбцом - это индекс, основанный на одном столбце;
【Запуск правил индекса с одним столбцом]: Условие должно быть исходным значением в столбце индекса; если неоригинальное значение столбца индекса используется в условии, например, однорядная функция, нечеткий запрос, механизм откажется от запуска индекса и выполнит поиск по всей таблице
- Создайте индекс с одним столбцом
- Индекс триггера с одним столбцом
2. Составной индекс
Составной индекс - это индекс, основанный на двух или более столбцах. В одной таблице может быть несколько индексов, конечно, комбинация столбцов должна быть разной!
【Правила составного индекса триггера]: Первый столбец является столбцом приоритетного поиска. Если вы хотите активировать составной индекс, он должен содержать исходное значение в столбце приоритетного поиска.
Представления(Views) - это такой объект в БД, который:
- Выглядит как таблица
- Внутри себя содержит SQL запрос, которым заменяется таблица при обращении к ней.
Во многом представления работают также, как и обычные таблицы. В них можно(правда с определенными ограничениями) вставлять, изменять и удалять данные.
Зачем нужны представления
Возможными причинами могут быть: безопасность, упрощение пользовательских запросов, предотвращение ошибок, улучшение производительности и преобразование данных в более понятный вид. Таблица и название столбцов обычно имеют длинные и неонпятные названия. Представление и его столбцы могут быть более понятными.
1). Принцип использования индекса
- Имеет смысл строить индексы на больших таблицах.
- Создайте индекс после предложения where или для условия подключения или для поля после условия фильтрации, имеющего.
- Не рекомендуется создавать индекс при частом изменении данных в таблице, иначе это значительно увеличит стоимость обслуживания обновления.
Запрет изменения представления
Чтобы создать представление, которое нельзя будет изменять, нужно создать его с опцией with read only .
Пересоздадим представление vdepartments и попробуем добавить туда данные:
В результате получим ошибку cannot perform a DML operation on a read-only view .
Для пользователя представления выглядят как таблицы: двумерная структура из строк и столбцов, к которой пользователь может писать SELECT и DML запросы. Программист знает правду: представление это всего лишь команда SELECT с именем. Любой SELECT запрос возвращает двумерный набор данных. Когда SELECT запрос сохранен как представление, то когда бы пользователь не читал или обновлял данные в представлении (думая что это таблица) запрос выполняется и результат представляется пользователя как будет это таблица. Команда SELECT на которой базируется представление может быть чем угодно. Объединением таблиц, результатом после аггрегации, сортировки. Абсолютно любой валидный запрос может быть основой для представления.
Views share the same namespace as tables: anywhere that a table name can be used, a view name is also syntactically correct.
1). Роль взгляда
- Просмотр может блокировать конфиденциальные поля
- Убедитесь, что данные головного офиса и филиала унифицированы по времени;
- Например: проверка штаб-квартиры из таблицы, ветвь, чтобы увидеть представление, чтобы гарантировать согласованность данных! Ограниченное предотвращение несвоевременного обновления данных запроса
Представления с проверкой (WITH CHECK OPTION)
Можно создавать представления, которые будут ограничивать изменение данных в базовых таблицах. Для этого используется опция WITH CHECK OPTION при создании представления.
Создадим представление, которое содержит в себе только менеджеров:
Данное представление содержит только менеджеров, но это не означает, что в него нельзя добавить сотрудников других профессий:
Данные в представлении остались те же, что и были:
А вот в таблицу employees был добавлен новый сотрудник Иван Иванов:
Для того, чтобы через представление можно было изменять только те данные, которые в нем содержатся(а точнее, которые можно получить через представление), при его создании следует указать опцию WITH CHECK OPTION .
Создадим заново представление vemp_managers , только с добавлением with check option , и попробуем снова добавить в него запись:
При попытке это сделать, мы получим ошибку view WITH CHECK OPTION where-clause violation .
Но зато добавить сотрудника с position_id = 1 можно без проблем:
Ограничения в изменяемых представлениях
Изменения в представлениях возможны не всегда. Есть определенные условия, при которых они запрещены:
- Наличие в представлении агрегатных функций, конструкции group by , оператора distinct , операторов для работы с множествами( union , union all , minus ).
- Если данные не будут удовлетворять условию, прописанному в опции WITH CHECK OPTION .
- Если колонка в базовой таблице NOT NULL , не имеет значения по-умолчанию, и отсутствует в представлении.
- Если колонки в представлении представляют собой выражения (Например что-то вроде nvl(a.value, -1) ).
Создание представлений
Общий синтаксис создания представления следующий:
Т.е. для создания представления достаточно написать запрос, который возвращать нужные данные.
Можно создавать представления с опцией or replace , тогда в том случае, если такое представление уже существует, оно будет заменено на новое.
Создадим таблицу с сотрудниками, должностями и подразделениями:
Создадим представление vemployees , которое будет выводить данные по сотрудникам в уже “соединенном” виде:
Следует обратить внимание на то, что представлениям и колонкам в них можно задавать комментарии как и обычным таблицам.
Теперь, чтобы получить нужные нам данные, нам не нужно заново писать запрос, достаточно сразу выбрать данные из представления:
При создании представлений можно использовать уже существующие представления:
Следует с осторожностью использовать уже созданные представления при создании других представлений. Может случиться так, что написать новый запрос будет куда лучше, чем использовать существующие, но не полностью подходящие.
Символ * при создании представлений
Когда при создании представления используется символ “*”, то Oracle заменяет звездочку на список столбцов. Это означает, что если в таблицу будет добавлена новая колонка, то она не будет автоматически добавлена в представление.
Это очень просто проверить:
Посмотрим, какие данные содержатся в представлении:
Теперь добавим в таблицу tst еще одну колонку( изменение таблиц будет рассматриваться позже, сейчас достаточно понимать, что данный запрос добавляет новую колонку в таблицу):
Если сейчас получить все данные из представления, мы увидим, что список колонок в ней не изменился:
Чтобы добавить колонку “n3” в представление, можно изменить его, добавив в список колонок нужную, либо заново создать(с использованием create or replace ):
Организация хранения данных применяемых в продукте (Oracle + Postgre)
Архитектура всех современных корпоративных систем подчиняется определенным стандартам: она должна быть построена на переиспользуемых микросервисах, то есть быть business oriented. Наш продукт не является исключением, он также построен на микросервисах. При этом в каждом из них используется своя база PostgreSQL.
Я хочу остановиться на основном источнике (хранилище), который используется в Департаменте цифрового развития — Oracle. Часть данных для продукта проходит предварительную обработку внутри хранилища данных, поскольку другие команды также используют их в своих проектах.
Постановка задачи
Данные для «Цифрового вагона» мы получаем из нескольких источников. Прежде всего, это данные с датчиков, которые установлены на сети железных дорог. Они измеряют технические показатели колесных пар вагонов и позволяют видеть информацию практически в режиме реального времени (на деле же обычная пакетная обработка). Что мы можем увидеть? Номер вагона и детали, а также показатели ее технического состояния: толщину гребня и толщину обода. Полученные данные необходимо обогатить справочниками, преобразовать и снова обогатить.
Поскольку хранилище построено на Oracle, то первый вариант реализации возложили на пакеты (Package Oracle PL/SQL). Написали несколько модулей, протестировали, все заработало. Процедуры запускались с помощью планировщика задач Oracle Scheduler.
Спустя некоторое время, столкнулись с рядом проблем, приведу несколько из них:
пакеты содержали более тысячи строк чистого SQL-кода. Подобное затрудняет знакомство новых коллег с исходниками, а также дальнейшую поддержку;
формируемая таблица и генерирующая её процедура, связаны через планировщик, который может находиться в другой схеме. Следовательно, периодически возникают трудности в поиске участка кода, который заполняет сущность и задачу, запускающую его;
отсутствие графического интерфейса (писать запросы - это не GUI) для мониторинга за запуском задач по расписанию.
2. Указатель [Важно]
Индексы - это объекты данных, используемые для ускорения доступа к данным. Существует множество различных реализаций структур данных. Основная идея состоит в том, чтобы построить двоичное дерево по столбцам таблицы. Разумное использование индексов может значительно уменьшить количество операций ввода-вывода, тем самым повышая производительность доступа к данным. Индексы значительно повышают эффективность запросов, но они увеличивают затраты на обслуживание обновлений. Следовательно, вы не можете строить индексы случайным образом, и вы не можете строить слишком много индексов. Для полей, которые не часто запрашиваются, не создавайте соответствующие индексы.
После создания индекса обновите под индексами слева от инструмента pl / sql, чтобы увидеть созданный индекс.
Представления для производительности
Команда SELECT являющаяся основой для представления может быть оптимизирована программистами, и пользователям не надо переживать об оптимизации кода. Существует много способов получения одного и того же результата, но некоторые способы гораздо медленнее чем другие. Например при объединении двух таблиц обычно происходи выбор между nested loop объединением и hash join объединением. Nested loop использует индекса для поиска конкретной строки, hash join считывает всю таблицу в память. Выбор между методами основывается на данных и необходимых ресурсах.
Теоретически кто-то всегда полагается на результат работы оптимизатора Oracle, но иногда оптимизатор совершает ошибки. Если программист понимает какой метод лучше использовать в конкретном случае то можно дать необходимые иснтрукции оптимизатору. Например этот запрос заставит использовать hash join
create view dept_emp as select /*+USE_HASH (employees departments)*/ department_name, last_name from departments natural join employees;
Когда бы пользователь не выполнил запрос к представлению DEPT_EMP объединение будет осуществляться поиском совпадений в подсоединяемой таблице в памяти (hash join). Пользователям не нужно знать как заставить базу использовать метод объединения. Мы тоже не будем детально обсуждать оптимизацию но необходимо знать принцип оптимизации при помощи представлений.
Материализованные представления в Oracle
Пару слов о материализованных представлениях. Materialized view (MV) позволяет выполнять SQL-запрос в определенный момент времени и сохранять результат в таблице (локально или в удаленной базе данных). Во время выполнения команды на создание MV, Oracle создает объект материализованного представления и обычную таблицу с таким же названием, как и MV (базовая таблица). После инициализации MV, можно перезапустить запрос MV и обновить данные в базовой таблице. Подробнее здесь.
Материализованное представление может формироваться основываясь на таблицах, представлениях и других материализованных представлениях. Таблицы, которые указываются в конструкции FROM MV, часто называют мастер таблицами.
При определении материализованного представления можно указать три параметра для обновления (ссылка):
можно ли использовать "доверительные" ограничения при обновлении (в этой статье про это говорить не будем).
Существуют два варианта запуска обновления MV — ON COMMIT и ON DEMAND :
ON COMMIT - обновление происходит во время фиксации транзакции, которая изменила одну из мастер-таблиц. Реализует событийную модель, однако имеет ряд ограничений - необходимо иметь права на ON COMMIT и если обновить MV не выйдет, то вся транзакция откатится.
ON DEMAND - обновление происходит при ручном запуске одной из доступной процедуры из пакета: DBMS_MVIEW ( REFRESH , REFREASH_ALL_MVIEWS , REFRESH_DEPENDENT ).
Также можно указать каким образом MV будет обновляться из мастер-таблиц:
COMPLETE - обновляется, пересчитывая весь запрос, перезаписывая базовую таблицу;
FAST - применить инкрементные изменения для MV, используя информацию записанную в журналы материализованных представлений;
FORCE - если возможно, применяется FAST , в противном случае применяется COMPLETE ;
NEVER - MV не будет обновляться механизмами обновления.
Сделаем небольшую сводку. Наиболее подходящий вариант обновления MV для нашей задачи - это опции ON COMMIT и FAST , однако в этом случае накладываются ограничения из-за которых данный вариант не подойдет. Собственно, о них ниже, на демонстрационном примере.
8 Answers 8
Materialized views are disk based and are updated periodically based upon the query definition.
Views are virtual only and run the query definition each time they are accessed.
Also when you need performance on data that don't need to be up to date to the very second, materialized views are better, but your data will be older than in a standard view. Usually BI reports gain a lot of benefit from materialized views.
@Marthinus - that is correct except in the case of a materialized view which is REFRESH ON COMMIT - the MV will return exactly the data that has been committed.
What is the meaning of DISK based? Is it mean table is not part of DISK? Is it stored in a file and DISK access is faster that File access .
@dacracot Thanks! i think you mean to say other than DB tables in the DISK; this MVIEW also maintain a table by resolving all the joins. So that in the runtime single table access is enough; and no need to query multiple tables for join conditions which is usually done by the normal view. Thank you!
They evaluate the data in the tables underlying the view definition at the time the view is queried. It is a logical view of your tables, with no data stored anywhere else.
The upside of a view is that it will always return the latest data to you. The downside of a view is that its performance depends on how good a select statement the view is based on. If the select statement used by the view joins many tables, or uses joins based on non-indexed columns, the view could perform poorly.
They are similar to regular views, in that they are a logical view of your data (based on a select statement), however, the underlying query result set has been saved to a table. The upside of this is that when you query a materialized view, you are querying a table, which may also be indexed.
In addition, because all the joins have been resolved at materialized view refresh time, you pay the price of the join once (or as often as you refresh your materialized view), rather than each time you select from the materialized view. In addition, with query rewrite enabled, Oracle can optimize a query that selects from the source of your materialized view in such a way that it instead reads from your materialized view. In situations where you create materialized views as forms of aggregate tables, or as copies of frequently executed queries, this can greatly speed up the response time of your end user application. The downside though is that the data you get back from the materialized view is only as up to date as the last time the materialized view has been refreshed.
Materialized views can be set to refresh manually, on a set schedule, or based on the database detecting a change in data from one of the underlying tables. Materialized views can be incrementally updated by combining them with materialized view logs, which act as change data capture sources on the underlying tables.
Materialized views are most often used in data warehousing / business intelligence applications where querying large fact tables with thousands of millions of rows would result in query response times that resulted in an unusable application.
Materialized views also help to guarantee a consistent moment in time, similar to snapshot isolation.
Приветствую! Меня зовут Жумабаев Султан, и в ПГК я работаю инженером данных на проекте «Цифровой вагон». Могу уверенно сказать, Oracle сегодня — одно из самых популярных и надежных хранилищ, хотя рынок и предлагает множество новых современных разработок. В этой статье я расскажу про использование Materialized Views для организации ETL-процессов в рамках проекта.
Существует два основных способа использования материализованных представлений:
Репликация данных в отдельные базы данных для снижения нагрузки запросов.
Повышение производительности запросов за счет периодического вычисления и хранения результатов сложных агрегаций данных, что позволяет пользователям запрашивать результаты сложных запросов на определенный момент времени.
Система «Цифровой вагон» помогает собирать и анализировать большое количество данных о состоянии вагона, принимать своевременные решения о его ремонте и, таким образом, оптимизировать затраты ПГК. Проект стартовал с модуля, который позволяет отслеживать технические показатели колесных пар с датчиков, массово расположенных на сети железных дорог — ИС КТИ (контрольно-технические измерения). Благодаря этой информации мы можем осуществлять их предиктивный, то есть предупредительный ремонт. Подробнее здесь.
Изменение представлений из нескольких таблиц
В Oracle можно изменять данные через представления, которые получают данные из нескольких таблиц.
Но есть определенные ограничения:
- Изменять можно данные только одной базовой таблицы
- Изменяемая таблица должна быть т.н. “key preserved table” (таблица с сохранением ключа).
Второй пункт возможно самый важный для понимания того, можно ли изменять данные в представлении из нескольких таблиц или нет.
Так вот, таблица называется key preserved, если каждой ее строке соответствует максимум одна строка в представлении.
Следует помнить, что свойство сохранения ключа в представлениях не зависит от данных, а скорее от структуры таблиц и их отношений между собой. Фактически в представлении данные могут выглядеть так, что для одной строки базовой таблицы есть лишь одна строка представления, но это не означает, что этот вид не изменится при изменении данных в таблицах представления.
Для примера создадим представление vemp_depts , которое будет содержать информацию о сотрудниках и подразделениях, в которых они работают:
Посмотрим, какие данные там находятся:
Как мы видим, каждая строка из базовой таблицы employees встречается в представлении всего один раз. Попробуем добавить нового сотрудника через это представление:
В результате получаем ошибку cannot modify a column which maps to a non key-preserved table , которая говорит о том, что таблица не обладает нужными свойствами для обновления через представление.
Зная, что проблему нужно искать не в самих данных, а в схеме БД, посмотрим, как мы создавали наши таблицы и как выглядит наш запрос в представлении.
Здесь мы берем каждую строку из таблицы employees и соединяем с таблицей departments по полю dept_id . В каком случае может произойти так, что в представлении для одной строки из таблицы employees окажутся 2 строки после соединения с таблицей departments ? Правильно, в том случае, если в таблице departments будут 2 строки с одинаковым значением в колонке id . Сейчас таких данных в таблице нет, но это не означает, что они не могут появиться. Посмотрим, как мы создавали таблицу departments :
Как видно, нет никаких ограничений на колонку id . Но мы можем сделать ее уникальной, добавив первичный или уникальный ключ.
Теперь снова попробуем добавить нового сотрудника:
Добавить данные в таблицу departments через это представление не получится:
Причина здесь та же: нельзя гарантировать, что в таблице employees каждый сотрудник имеет уникальное значение dept_id .
Читайте также: