Oracle найти связи между таблицами
Добрый день! Мы команда системных аналитиков одного из подразделений управления данными «Ростелекома». В нашей компании насчитывается более 300 неоднородных источников данных — такое многообразие необходимо для поддержки работы Ростелекома по всем многочисленным направлениям. Мы изучаем источники данных и по необходимости частично выгружаем в контур хранилища.
В этом процессе выделяется две подзадачи: определение стратегии сбора данных из таблиц источника в зависимости от их свойств и подготовка таблиц-«приемников» хранилища данных. Для этого мы используем различные GUI и средства реверс-инжиниринга. Кроме того, при сборе информации системный аналитик начинает обрастать пулом вспомогательных запросов к информационным таблицам СУБД (преимущественно Oracle). В этой статье я поделюсь «джентльменским набором» таких скриптов, используемых нашей командой.
Для начала небольшое пояснение ко всем приведенным скриптам:
- Во многих скриптах для агрегации строк используется xmlagg, так как listagg не может обработать слишком длинные строки, получающиеся в результате конкатенации.
- Во всех скриптах кроме «Процедуры, функции и пакеты» целевые таблицы задаются через таблицу filter в блоке «with». Заполняется наименование схемы и наименование таблицы.
- К каждому скрипту прилагается один или несколько сценариев использования, описание спецификации (результирующего набора), а также список используемых системных таблиц (для оценки возможности использования на конкретной БД).
Скрипт «Информация о таблицах»
Наименование колонки | Комментарий |
SCHEMA_NAME | Наименование схемы данных (OWNER) |
TABLE_NAME | Наименование таблицы |
COMMENTS | Комментарий к таблице |
HEIGHT | Количество строк в таблице (приблизительно) |
WIDTH | Количество столбцов |
DATETIME_COLUMNS | Столбцы с временнЫми типами данных и столбцы, исходя из наименования, предположительно являющиеся временнЫми метками (паттерны – %period%, %date%, %time%) |
AVG_ROW_LEN | Средняя длина строки в байтах |
PART_KEY | Столбцы по которым осуществлено партиционирование |
SUBPART_KEY | Столбцы по которым осуществлено субпартиционирование |
Используемые системные таблицы: all_tab_columns, all_tab_comments, all_tab_statistics, all_part_key_columns, all_subpart_key_columns.
Запрос полезен для определения стратегии выгрузки данных из системы источника. Если на рассматриваемой таблице построен первичный ключ, то можно организовать выгрузку с последующим выделением «инкремента» по нему. При наличии метки времени — например, в технических полях с информацией о вставке данных или об обновлении — можно организовать выгрузку только измененных/добавленных записей за период времени. Информация о структуре партиций может пригодиться при создании аналогичной таблицы-«приемника».
Тело запроса:
Скрипт «Партиции и субпартиции»
Наименование колонки | Комментарий |
SCHEMA_NAME | Наименование схемы данных (OWNER) |
TABLE_NAME | Наименование таблицы |
PART_KEY | Столбцы по которым осуществлено партиционирование |
PARTITION_NAME | Наименование партиции |
PARTITION_POSITION | Номер партиции |
PARTITION_HEIGHT | Количество строк в партиции |
SUBPART_KEY | Столбцы по которым осуществлено субпартиционирование |
SUBPARTITION_NAME | Наименование субпартиции |
SUBPARTITION_POSITION | Номер субпартиции |
SUBPARTITION_HEIGHT | Количество строк в субпартиции |
Используемые системные таблицы: all_tab_partitions, all_tab_subpartitions, all_part_key_columns, all_subpart_key_columns.
Скрипт будет полезен для получения характеристик (наименование, размеры) партиций при их непосредственном использовании в качестве источников данных.
Тело запроса:
Скрипт «Атрибутный состав таблиц»
Используемые системные таблицы: all_tables, all_constraints, all_cons_columns, all_tab_columns, all_col_comments, v$nls_parameters.
Этот скрипт будет полезен для подготовки таблиц-«приемников» в хранилище данных, когда нужна подробная информация о таблице, ее взаимосвязях с другими таблицами, а также полном атрибутном составе. Через вспомогательную таблицу filter2 задается фильтрация таблиц, для которых осуществляется поиск ссылок (от и к). По умолчанию берутся таблицы из всех схем, кроме системных.
Тело запроса:
Скрипт «Процедуры, функции и пакеты»
Наименование колонки | Комментарий |
SCHEMA_NAME | Наименование схемы данных (OWNER) |
NAME | Наименование процедуры/функции/пакета/заголовка пакета |
BODY | Тело |
TYPE | Тип (PACKAGE BODY, PACKAGE, FUNCTION, PROCEDURE) |
WRAPPED | Флаг «Закодировано тело или нет (wrapped)» |
Используемые системные таблицы: all_source
Тело запроса:
Заключение
Описанные выше скрипты помогают нашим системным аналитикам избавиться от многих рутинных действий по сбору информации о базе данных и сосредоточиться на более творческих вещах, таких как стратегия загрузки и структура таблиц-«приемников». Надеюсь, скрипты пригодятся и вам. Было бы интересно узнать, как вы автоматизируете эти и подобные задачи.
Пытаюсь найти связь между таблицами и если если, то какая.
Условно у меня есть первая таблица, я хочу понять связана ли она как то с какой то другой таблицей:
Для этого попытался сделать так: зашел d PLSQL Debeloper`е в other_Users ---> далее нашел Юзера у кого есть эта таблица, открыл эту таблицу ---> в ней открыл вкладку "Constraints". Скриншот:
И далее не очень понимаю, если есть возможность подскажите пожалуйста.
Вот что, как мне показалось понял и что не понял:
1)В таблице есть главный и внешний ключ. Правда при самом запросе SELECT* from Table - название указанных ключей RTPL_PK и RTPL_RTPB_FK - не выгружается.
2)В Столбец R_TABLE_NAME - указано название таблицы на какую ссылается "данная" таблица то есть ссылается на таблицу ICE_TABLES. Такая таблица сущесвует.
В столбце R_CONSTRAINT_NAME указано наименование столбца PRTB_PK в таблице ICE_TABLES на которую ссылается "данная" таблица. Вот только проблема в том, что в таблице ICE_TABLES - нет столбца с названием PRTB_PK, а есть с названием PRTB_ID. И как это понимать, что то я не очень понимаю.
3)Не понял, что такое R_OWNER. Нагуглил, что это некая "схема", но что это значит не понятно. Единственно, что понятно, что название в столбце R_OWNER - "TC" - совпадает с названием Users в котором две эти таблицы находятся.
4)Так же меня смущают столбцы DELETE_RULE и STATUS - в которых напротив строки с "внешним ключом" стоит наименование NO_ACTION и DISABLED.
Это значит, внешний ключ не работает или что ?
5)И непонятно для чего столбца INDEX_ONWER и INDEX_NAME - что за информация в них указана и как ее нужно или можно использовать?
Связь между таблицами
Всем привет, пока только начал изучение, и столкнулся с некоторыми проблемами: 1) есть 2.
Как посмотреть связь между таблицами
Здравствуйте. Есть задания: конвектировать БД из MS Access в SQL Server Management Studio. Я.
Как посмотреть связь между таблицами
Здравствуйте. Как посмотреть связь между таблицами в sql server management? Как на картинке ниже
Надеюсь из прошлых, почти полностью теоретических, изложений стало немного яснее, что же все-таки есть реляционные данные и все, что с ними связано. Давайте сейчас попробуем просмотреть все это практически. Наша с вами учебная БД в схеме miller содержит, пять таблиц. Все они в принципе отвечают требованиям 3НФ. Но, когда я их создавал, я не связал столбцы этих таблиц между собой с помощью стандартных средств. А, вот сейчас давайте мы с вами это сделаем. Итак для примера организуем связь, которая чаше всего рекомендована к применению, типа один-ко-многим. Ярким примером для построения такой связи служит две из наших пяти таблиц это CUSTOMERS и SALESREPS. Оператор CREATE TABLE их DDL определений записан следующим образом:
Таблица CUSTOMERS:
Таблица SALESREPS:
При просмотре данных, этих таблиц почти сразу видно, что столбец таблицы SALESREPS - EMPL_NUM есть отношение один-ко-многим столбца CUST_REP для таблицы CUSTOMERS. Для определения связи между таблицами воспользуемся оператором ALTER TABLE и запишем вот такую конструкцию:
Все, связь между столбцами таблиц установлена! Все достаточно просто. Теперь действует ограничение ссылочной целостности и нарушить его нам с вами не позволят! Можно убедиться в этом. Столбец таблицы SALESREPS - EMPL_NUM содержит следующее множество значений 101 .. 110 и отдельно 120. Попробуйте что-нибудь вроде:
После ввода, получаем:
Естественно ошибка ORA-02291! А все потому, что множество 101 .. 110 и отдельно 120 не содержит числа 150! И по этому в данном случае не допустимо! Вот и получилось жесткое отношение один-ко-многим! Так же, можно и удалить связь, между столбцами таблиц применив оператор DROP. Но, нужно узнать имя ссылочной целостности в системе. Сейчас мы его знаем благодаря ошибке. А что если, в процессе работы нужно удалить ссылочную целостность, а потом снова восстановить ее! Для этого обратимся к представлению в вашей схеме USER_CONSTRAINTS. Оно содержит все имена ваших ограничений. Дадим такой запрос:
Там где поле CONSTRAINT_TYPE содержит значение R и есть наше ограничение (по моему от REFERENCES, точно не помню!) Получаем имя ограничения - SYS_C003548 (тоже номер был и в ошибке помните?). Вот теперь давайте от него избавимся:
Вот теперь ограничение снято. Повторим наш предыдущий запрос и посмотрим, что содержит USER_CONSTRAINTS сейчас:
Хорошо видно, что осталось только ограничение первичного ключа таблицы CUSTOMERS имеющее имя SYS_C003506. Кроме того, таблица может содержать ограничение на саму себя например все с той же таблицей SALESREPS можно проделать следующее:
Теперь таблица, как бы это лучше сказать - "самоограничилась", хотя это не всегда оправдано, но вполне применимо и может использоваться! Можете сами с этим всем поработать и определить приоритеты, при проектировании БД, оптимизации и определении ссылочных целостностей таблиц! Но, слишком не увлекайтесь, границы сознания не бесконечны и не стоит выходить за границы понимания, а уж во всяком случае выпускать за них свою БД. :)
мне нужно лучше понять правила о том, когда я могу ссылаться на внешнюю таблицу в подзапросе и когда (и почему) это неуместный запрос. Я обнаружил дублирование в Oracle SQL-запрос, я пытаюсь рефакторинг, но я бегу в вопросы, когда я пытаюсь включить мой таблицы в подзапрос с группировкой.
следующий оператор работает соответствующим образом:
к сожалению, table2 иногда имеет повторяющиеся записи, поэтому мне нужно сначала агрегировать t2, прежде чем Я присоединю его к t1. Однако, когда я пытаюсь обернуть его в подзапрос для выполнения этой операции, внезапно SQL engine больше не может распознать внешнюю таблицу.
Я знаю, что это принципиально разные запросы, которые я прошу компилятор собрать, но я не вижу, почему один будет работать, но не другой.
Я знаю, что могу дублировать ссылки на таблицы в моем подзапросе и эффективно отсоединить мой подзапрос из внешней таблицы, но это кажется действительно уродливым способом выполнения этой задачи (со всем дублированием кода и обработки).
Полезные Ссылки
Я нашел это фантастическое описание порядка, в котором выполняются предложения в SQL Server: (INNER JOIN ON vs WHERE clause). Я использую Oracle, но я думаю, что это будет стандарт по всем направлениям. Существует четкий порядок клаузулы оценка (с первого), поэтому я думаю, что любой пункт, происходящий дальше по списку, будет иметь доступ ко всей ранее обработанной информации. Я могу только предположить, что мой 2-й запрос каким-то образом изменяет порядок, чтобы мой подзапрос оценивался слишком рано?
кроме того, я нашел похожий вопрос (таблицы внешнего запроса в подзапросе ) но хотя вход был хорошим, они никогда не объясняли, почему он не мог сделать то, что он делает и просто дает альтернативные решения своей проблемы. Я пробовал их альтернативные решения, но это вызывает у меня другие проблемы. А именно, этот подзапрос со ссылкой на дату является фундаментальным для всей операции, поэтому я не могу избавиться от него.
вопросы
Я хочу понять, что я сделал здесь. Почему мой первоначальный подзапрос может видеть внешнюю таблицу, но не после того, как я оберну весь оператор в подзапрос?
тем не менее, если то, что я пытаюсь сделать, не может быть сделано, каков наилучший способ рефакторинга первого запроса для устранения дублирования? Должен ли я ссылаться на таблицу 1 дважды (со всем необходимым дублированием)? Или есть (возможно) лучший способ решения этой проблемы?
------ редактировать------
как некоторые предположили, эти запросы выше на самом деле не являются запрос я рефакторинг, но пример проблемы, с которой я сталкиваюсь. Запрос, с которым я работаю, намного сложнее, поэтому я не решаюсь опубликовать его здесь, поскольку я боюсь, что это собьет людей с пути.
------ обновление------
поэтому я запустил это другим разработчиком, и у него было одно возможное объяснение того, почему мой подзапрос теряет доступ к t1. Поскольку я обертываю этот подзапрос в скобки, он думает, что этот подзапрос оценивается перед моим таблица t1 оценивается. Это определенно объясняет 'ORA-00904:"t1"."id": ошибка недопустимого идентификатора, которую я получаю. Это также предполагает, что, как и арифметический порядок операций, добавление parens в оператор дает ему приоритет в определенных оценках предложения. Я все равно хотел бы, чтобы эксперт взвесил, если они согласны / не согласны, что это логическое объяснение того, что я вижу здесь.
поэтому я понял это на основе комментария, который Мартин Смит сделал выше (спасибо, Мартин!) и я хотел убедиться, что я поделился своим открытием для всех, кто сталкивается с этой проблемой.
Технические Вопросы
во-первых, это, безусловно, поможет, если я использую правильную терминологию для описания моей проблемы: мое первое утверждение выше использует коррелируется subquery:
на самом деле это довольно неэффективный способ возврата данных при повторном выполнении подзапроса для каждой строки во внешней таблице. По этой причине я собираюсь искать способы устранения этих подзапросов в моем код:
мое второе утверждение, с другой стороны, использовало то, что называется встроенные вида в Oracle также известный как производная таблица в SQL Сервер:
встроенное представление / производная таблица создает временное неназванное представление в начале запроса, а затем обрабатывает его как другую таблицу до завершения операции. Потому что компилятору необходимо создать временное представление, когда он видит на этих подзапросах на линии FROM,эти подзапросы должны быть полностью автономны без ссылок за пределами подзапроса.
почему то, что я делал, было глупо
то, что я пытался сделать в этой второй таблице, по существу, создавало представление, основанное на двусмысленной ссылке на другую таблицу, которая была вне знания моего утверждения. Это было бы похоже на попытку ссылаться на поле в таблице, которое вы явно не указали в запросе.
решение
наконец, стоит отметить, что Мартин предложил довольно умный, но в конечном счете неэффективный способ выполнить то, что я пытался сделать. Инструкция Apply-это проприетарная функция SQL Server, но она позволяет разговаривать с объектами за пределами производной таблицы:
дополнительно это функциональность доступна в Oracle через другой синтаксис:
в конечном итоге я собираюсь переоценить весь свой подход к этому запросу, что означает, что мне придется перестроить его с нуля (верьте или нет, я не создавал это чудовище изначально - я клянусь!). большое спасибо всем, кто прокомментировал - это определенно ставило меня в тупик, но все входные данные это помогло мне встать на правильный путь!
Связи — это довольна важная тема, которую следует понимать при проектировании баз данных. По своему личному опыту скажу, что осознав связи, мне намного легче далось понимание нормализации базы данных.
1.1. Для кого эта статья?
Эта статья будет полезна тем, кто хочет разобраться со связями между таблицами базы данных. В ней я постарался рассказать на понятном языке, что это такое. Для лучшего понимания темы, я чередую теоретический материал с практическими примерами, представленными в виде диаграммы и запроса, создающего нужные нам таблицы. Я использую СУБД Microsoft SQL Server и запросы пишу на T-SQL. Написанный мною код должен работать и на других СУБД, поскольку запросы являются универсальными и не используют специфических конструкций языка T-SQL.
1.2. Как вы можете применить эти знания?
- Процесс создания баз данных станет для вас легче и понятнее.
- Понимание связей между таблицами поможет вам легче освоить нормализацию, что является очень важным при проектировании базы данных.
- Разобраться с чужой базой данных будет значительно проще.
- На собеседовании это будет очень хорошим плюсом.
2. Благодарности
Учтены были советы и критика авторов jobgemws, unfilled, firnind, Hamaruba.
Спасибо!
3.1. Как организовываются связи?
Связи создаются с помощью внешних ключей (foreign key).
Внешний ключ — это атрибут или набор атрибутов, которые ссылаются на primary key или unique другой таблицы. Другими словами, это что-то вроде указателя на строку другой таблицы.
3.2. Виды связей
Связи делятся на:
- Многие ко многим.
- Один ко многим.
- с обязательной связью;
- с необязательной связью;
- Один к одному.
- с обязательной связью;
- с необязательной связью;
4. Многие ко многим
Представим, что нам нужно написать БД, которая будет хранить работником IT-компании. При этом существует некий стандартный набор должностей. При этом:
- Работник может иметь одну и более должностей. Например, некий работник может быть и админом, и программистом.
- Должность может «владеть» одним и более работников. Например, админами является определенный набор работников. Другими словами, к админам относятся некие работники.
4.1. Как построить такие таблицы?
Мы уже имеем две таблицы, описывающие работника и профессию. Теперь нам нужно установить между ними связь многие ко многим. Для реализации такой связи нам нужен некий посредник между таблицами «Employee» и «Position». В нашем случае это будет некая таблица «EmployeesPositions» (работники и должности). Эта таблица-посредник связывает между собой работника и должность следующим образом:
Слева указаны работники (их id), справа — должности (их id). Работники и должности на этой таблице указываются с помощью id’шников.
На эту таблицу можно посмотреть с двух сторон:
- Таким образом, мы говорим, что работник с id 1 находится на должность с id 1. При этом обратите внимание на то, что в этой таблице работник с id 1 имеет две должности: 1 и 2. Т.е., каждому работнику слева соответствует некая должность справа.
- Мы также можем сказать, что должности с id 3 принадлежат пользователи с id 2 и 3. Т.е., каждой роли справа принадлежит некий работник слева.
4.2. Реализация
С помощью ограничения foreign key мы можем ссылаться на primary key или unique другой таблицы. В этом примере мы
- ссылаемся атрибутом PositionId таблицы EmployeesPositions на атрибут PositionId таблицы Position;
- атрибутом EmployeeId таблицы EmployeesPositions — на атрибут EmployeeId таблицы Employee;
4.3. Вывод
Для реализации связи многие ко многим нам нужен некий посредник между двумя рассматриваемыми таблицами. Он должен хранить два внешних ключа, первый из которых ссылается на первую таблицу, а второй — на вторую.
5. Один ко многим
Эта самая распространенная связь между базами данных. Мы рассматриваем ее после связи многие ко многим для сравнения.
Предположим, нам нужно реализовать некую БД, которая ведет учет данных о пользователях. У пользователя есть: имя, фамилия, возраст, номера телефонов. При этом у каждого пользователя может быть от одного и больше номеров телефонов (многие номера телефонов).
В этом случае мы наблюдаем следующее: пользователь может иметь многие номера телефонов, но нельзя сказать, что номеру телефона принадлежит определенный пользователь.
Другими словами, телефон принадлежит только одному пользователю. А пользователю могут принадлежать 1 и более телефонов (многие).
Как мы видим, это отношение один ко многим.
5.1. Как построить такие таблицы?
Пользователей будет представлять некая таблица «Person» (id, имя, фамилия, возраст), номера телефонов будет представлять таблица «Phone». Она будет выглядеть так:
PhoneId | PersonId | PhoneNumber |
---|---|---|
1 | 5 | 11 091-10 |
2 | 5 | 19 124-66 |
3 | 17 | 21 972-02 |
Данная таблица представляет три номера телефона. При этом номера телефона с id 1 и 2 принадлежат пользователю с id 5. А вот номер с id 3 принадлежит пользователю с id 17.
Заметка. Если бы у таблицы «Phones» было бы больше атрибутов, то мы смело бы их добавляли в эту таблицу.
5.2. Почему мы не делаем тут таблицу-посредника?
Таблица-посредник нужна только в том случае, если мы имеем связь многие-ко-многим. По той простой причине, что мы можем рассматривать ее с двух сторон. Как, например, таблицу EmployeesPositions ранее:
- Каждому работнику принадлежат несколько должностей (многие).
- Каждой должности принадлежит несколько работников (многие).
5.3. Реализация
Наша таблица Phone хранит всего один внешний ключ. Он ссылается на некого пользователя (на строку из таблицы Person). Таким образом, мы как бы говорим: «этот пользователь является владельцем данного телефона». Другими словами, телефон знает id своего владельца.
6. Один к одному
Представим, что на работе вам дали задание написать БД для учета всех работников для HR. Начальник уверял, что компании нужно знать только об имени, возрасте и телефоне работника. Вы разработали такую БД и поместили в нее всю 1000 работников компании. И тут начальник говорит, что им зачем-то нужно знать о том, является ли работник инвалидом или нет. Наиболее простое, что приходит в голову — это добавить новый столбец типа bool в вашу таблицу. Но это слишком долго вписывать 1000 значений и ведь true вы будете вписывать намного реже, чем false (2% будут true, например).
Более простым решением будет создать новую таблицу, назовем ее «DisabledEmployee». Она будет выглядеть так:
Но это еще не связь один к одному. Дело в том, что в такую таблицу работник может быть вписан более одного раза, соответственно, мы получили отношение один ко многим: работник может быть несколько раз инвалидом. Нужно сделать так, чтобы работник мог быть вписан в таблицу только один раз, соответственно, мог быть инвалидом только один раз. Для этого нам нужно указать, что столбец EmployeeId может хранить только уникальные значения. Нам нужно просто наложить на столбец EmloyeeId ограничение unique. Это ограничение сообщает, что атрибут может принимать только уникальные значения.
Выполнив это мы получили связь один к одному.
Заметка. Обратите внимание на то, что мы могли также наложить на атрибут EmloyeeId ограничение primary key. Оно отличается от ограничения unique лишь тем, что не может принимать значения null.
6.1. Вывод
Можно сказать, что отношение один к одному — это разделение одной и той же таблицы на две.
6.2. Реализация
Таблица DisabledEmployee имеет атрибут EmployeeId, что является внешним ключом. Он ссылается на атрибут EmployeeId таблицы Employee. Кроме того, этот атрибут имеет ограничение unique, что говорит о том, что в него могут быть записаны только уникальные значения. Соответственно, работник может быть записан в эту таблицу не более одного раза.
7. Обязательные и необязательные связи
Связи можно поделить на обязательные и необязательные.
7.1. Один ко многим
- Один ко многим с обязательной связью:
К одному полку относятся многие бойцы. Один боец относится только к одному полку. Обратите внимание, что любой солдат обязательно принадлежит к одному полку, а полк не может существовать без солдат. - Один ко многим с необязательной связью:
На планете Земля живут все люди. Каждый человек живет только на Земле. При этом планета может существовать и без человечества. Соответственно, нахождение нас на Земле не является обязательным
А) У женщины необязательно есть свои дети. Соответственно, связь необязательна.
Б) У ребенка обязательно есть только одна биологическая мать – в таком случае, связь обязательна.
7.2. Один к одному
- Один к одному с обязательной связью:
У одного гражданина определенной страны обязательно есть только один паспорт этой страны. У одного паспорта есть только один владелец. - Один к одному с необязательной связью:
У одной страны может быть только одна конституция. Одна конституция принадлежит только одной стране. Но конституция не является обязательной. У страны она может быть, а может и не быть, как, например, у Израиля и Великобритании.
У одного человека может быть только один загранпаспорт. У одного загранпаспорта есть только один владелец.
А) Наличие загранпаспорта необязательно – его может и не быть у гражданина. Это необязательная связь.
Б) У загранпаспорта обязательно есть только один владелец. В этом случае, это уже обязательная связь.
7.3. Многие ко многим
Человек может инвестировать в акции разных компаний (многих). Инвесторами какой-то компании являются определенные люди (многие).
А) Человек может вообще не инвестировать свои деньги в акции.
Б) Акции компании мог никто не купить.
8. Как читать диаграммы?
Выше я приводил диаграммы созданных нами таблиц. Но для того, чтобы их понимать, нужно знать, как их «читать». Разберемся в этом на примере диаграммы из пункта 5.3.
Мы видим отношение один ко многим. Одной персоне принадлежит много телефонов.
Читайте также: