【基本】データベース設計

Transcript

1. 【基本】 データベース設計 Ritsuko Tokunaga ⽇本オラクル株式会社 2024年4⽉
2. 1. 論理設計 • E-R図 • 正規化 • CRUD図 2. 物理設計 • オブジェクトの設計 Ø 表 Ø ビュー Ø 索引 • データ格納領域の設計 Ø 表領域設計、データ・ファイル設計 Ø 領域割り当ての設計 Ø ブロックの設計 アジェンダ Copyright © 2024, Oracle and/or its affiliates 2
3. 1. 論理設計 • E-R図 • 正規化 • CRUD図 2. 物理設計 • オブジェクトの設計 Ø 表 Ø ビュー Ø 索引 • データ格納領域の設計 Ø 表領域設計、データ・ファイル設計 Ø 領域割り当ての設計 Ø ブロックの設計 アジェンダ Copyright © 2024, Oracle and/or its affiliates 3
4. 論理設計とは • 論理設計とは • システム化の範囲となるデータを整理し、誰にでも分かる形式で明確化すること • 業務要件を論理モデルに変換し、最適なテーブル構成を設計すること • 代表的なデータベースのモデリング⼿法 • E-R図 • 正規化 • CRUD図 データベースの論理設計 Copyright © 2024, Oracle and/or its affiliates 4
5. 実体(Entity)と実体間の関連(Relationship)の概念を使い、データの構造を図に表したもの E-R図の4つの要素 ① エンティティ ② リレーションシップ ③ 属性(アトリビュート) ④ ⼀意識別⼦ E-R図 Copyright © 2024, Oracle and/or its affiliates 5 従業員 *社員番号 *名前 *部⾨番号 o電話番号 部⾨ # ①エンティティ ④⼀意識別⼦ *部⾨番号 *部⾨名 o所在地 # ③属性 ②リレーションシップ IE記法やIDEF1X記法など、 多数のE-R図記法が有りますが、 今回はSQL Developer Data ModelerのBarker記法 に準拠した説明を致します。
6. ①エンティティ、②リレーションシップの概要 ①エンティティ • ⼈や物、具体的または抽象的なものを含めて、情報 を蓄積する対象となるもの • エンティティの記述⽅法 Ø ⼀意なエンティティ名を記述 E-R図 Copyright © 2024, Oracle and/or its affiliates 6 ②リレーションシップ • エンティティ間の関連を表現するもの • リレーションシップの記述⽅法 Ø関連があるエンティティを線で結ぶ Ø実線で必須、点線で任意を表す Øカーディナリティ(多対多、1対多などの関係値)を記述 従業員 部⾨ エンティティ カーディナリティ 例) 従業員と部⾨はn: 1の関係 従業員は必ずいずれかの部⾨に 所属する(必須) 新規部⾨には従業員は 配属されていなくても良い(任意)
7. ③属性、④⼀意識別⼦の概要 ③属性(アトリビュート) • エンティティを構成する情報 • 属性の記述⽅法 Ø エンティティの中に記述 Ø それぞれの属性の任意性を記述 E-R図 Copyright © 2024, Oracle and/or its affiliates 7 ④⼀意識別⼦ • エンティティを⼀意に識別するもの • 属性の記述⽅法 Ø ⼀意識別⼦となる属性には「#」印を記述 従業員 部⾨ *部⾨番号 *部⾨名 o所在地 # 属性 *社員番号 *名前 *部⾨番号 o電話番号 ⼀意識別⼦ # 必須 任意
8. データ・モデリング作業を簡素化する無償のグラフィカル・ツール • 論理モデル、リレーショナル・モデル、物理モデル、など多数のモデルを作成、参照、および編集ができる • こちらよりダウンロードして利⽤が可能 • Autonomous Databaseの場合は、Database ActionsからData Modelerの利⽤もできる SQL Developer Data Modeler Copyright © 2024, Oracle and/or its affiliates 8 ドキュメント: https://docs.oracle.com/en/database/oracle/sql-developer-data-modeler/index.html SQL Developer Data Modeler のダウンロード画⾯ Autonomous DatabaseのDatabase Actionsの画⾯
9. 試しにE-R図を作成してみる SQL Developer Data Modeler を使って書いたE-R図 SQL Developer Data Modeler Copyright © 2024, Oracle and/or its affiliates 9
10. 正規化とは • 正規化とは、情報の重複をなくし、データを整理すること。 • 正規化の⽬的は、冗⻑性を排除して、その後のデータの操作による問題の発⽣を防ぐこと。 例) 「営業部」 ⇒ 「法⼈営業部」に部署名を変更したい 表の正規化 Copyright © 2024, Oracle and/or its affiliates 10 社員番号 名前 部署 001 ⽥中太郎 営業部 002 佐藤⼀郎 法務部 003 鈴⽊三郎 営業部 004 ⾼橋四郎 営業部 社員番号 名前 部署ID 001 ⽥中太郎 1 002 佐藤⼀郎 2 003 鈴⽊三郎 1 004 ⾼橋四郎 1 部署ID 部署 1 営業部 2 法務部 3か所修正する必要がある 修正は1か所のみで成り⽴つ 社員表 社員表 部署表 正規化なしの場合 正規化ありの場合
11. 正規化とは • 正規化の流れ ※今回は第3正規化までを対象とします。 • 正規化の⾒直し 表の正規化 Copyright © 2024, Oracle and/or its affiliates 11 受注伝票 ーーーーーーーー お買い上げ明細 受注番号 : 12345678 受注⽇ : 2024年06⽉01⽇ 顧客番号 : 1234 顧客⽒名 : ⽥中太郎 商品番号 商品名 単価 個数 価格 A001 Oracle Database⼊⾨ 1200 1 1200 M021 ORACLE MASTER Gold DBA参考書 2800 2 5600 X005 データベース設計 1700 1 1700 計 8500 ⾮正規化 正規化されていない繰り返しの部分をもつ表 第1正規化 繰り返し部分を排除し、独⽴したレコードをもつ表 第2正規化 部分関数従属している列を切り出した形の表 第3正規化 主キー以外の列に関数従属している列を切り出した形の表 この受注伝票を例に正規化を説明していきます
12. ⾮正規化: 正規化されていない繰り返しの部分をもつ表 • 受注伝票のデータから表を設計する 正規化 Copyright © 2024, Oracle and/or its affiliates 12 受注伝票 ーーーーーーーー お買い上げ明細 受注番号 : 12345678 受注⽇ : 2024年06⽉01⽇ 顧客番号 : 1234 顧客⽒名 : ⽥中太郎 商品番号 商品名 単価 個数 価格 A001 Oracle Database⼊⾨ 1200 1 1200 M021 ORACLE MASTER Gold DBA 参考書 2800 2 5600 X005 データベース設計 1700 1 1700 計 8500 受注番号 受注⽇ 顧客番号 顧客⽒名 商品番号 商品名 単価 個数 価格 12345678 2024/6/1 1234 ⽥中太郎 A001 Oracle Database⼊⾨ 1200 1 1200 商品番号 商品名 単価 個数 価格 商品番号 商品名 単価 個数 価格 M021 ORACLE MASTER Gold DBA参考書 2800 2 5600 X005 データベース設計 1700 1 1700 レコードとして並べてみる
13. 第1正規化 • 第1正規化 「繰り返し項⽬」を排除し、エンティティとして独⽴させる 正規化 Copyright © 2024, Oracle and/or its affiliates 13 受注番号 受注⽇ 顧客番号 顧客⽒名 商品番号 商品名 単価 個数 12345678 2024/6/1 1234 ⽥中太郎 A001 Oracle Database⼊⾨ 1200 1 12345678 2024/6/1 1234 ⽥中太郎 M021 ORACLE MASTER Gold DBA参考書 2800 2 12345678 2024/6/1 1234 ⽥中太郎 X005 データベース設計 1700 1 繰り返し項⽬を独⽴したレコードとして挿⼊ 繰り返し項⽬ 受注伝票 ーーーーーーーー お買い上げ明細 受注番号 : 12345678 受注⽇ : 2024年06⽉01⽇ 顧客番号 : 1234 顧客⽒名 : ⽥中太郎 商品番号 商品名 単価 個数 価格 A001 Oracle Database⼊⾨ 1200 1 1200 M021 ORACLE MASTER Gold DBA 参考書 2800 2 5600 X005 データベース設計 1700 1 1700 計 8500
14. 第2正規化 • 第2正規化 主キー項⽬に依存している項⽬を別のエンティティとして独⽴させる 正規化 Copyright © 2024, Oracle and/or its affiliates 14 商品番号 商品名 単価 A001 Oracle Database⼊⾨ 1200 M021 ORACLE MASTER Gold DBA参考書 2800 X005 データベース設計 1700 受注番号 受注⽇ 顧客番号 顧客⽒名 12345678 2024/6/1 1234 ⽥中太郎 受注番号 商品番号 個数 12345678 A001 1 12345678 M021 2 12345678 X005 1 受注番号 受注⽇ 顧客番号 顧客⽒名 商品番号 商品名 単価 個数 12345678 2024/6/1 1234 ⽥中太郎 A001 Oracle Database⼊⾨ 1200 1 12345678 2024/6/1 1234 ⽥中太郎 M021 ORACLE MASTER Gold DBA 参考書 2800 2 12345678 2024/6/1 1234 ⽥中太郎 X005 データベース設計 1700 1 主キー 主キー 第2正規化 明細表 商品表 受注表 受注表 受注番号(主キー)が決まると、 「受注⽇」「顧客番号」「顧客⽒名」が決まる 第1正規形 第2正規形 ※主キーとは、⾏を⼀意に識別するための項⽬
15. 第3正規化 • 第3正規化 主キー以外の依存関係のある項⽬を、新しいエンティティとして独⽴させる 正規化 Copyright © 2024, Oracle and/or its affiliates 15 商品番号 商品名 単価 A001 Oracle Database⼊⾨ 1200 M021 ORACLE MASTER Gold DBA参考書 2800 X005 データベース設計 1700 受注番号 受注⽇ 顧客番号 12345678 2024/6/1 1234 明細表 商品表 受注表 顧客番号 顧客⽒名 1234 ⽥中太郎 受注番号 受注⽇ 顧客番号 顧客⽒名 12345678 2024/6/1 1234 ⽥中太郎 第3正規化 依存関係あり 顧客表 商品番号 商品名 単価 A001 Oracle Database⼊⾨ 1200 M021 ORACLE MASTER Gold DBA参考書 2800 X005 データベース設計 1700 受注番号 商品番号 個数 12345678 A001 1 12345678 M021 2 12345678 X005 1 明細表 商品表 受注表 第2正規形 受注番号 商品番号 個数 12345678 A001 1 12345678 M021 2 12345678 X005 1 第3正規形
16. ⼀度正規化をしたうえで、処理傾向やパフォーマンスを考慮してテーブル構造を崩すこと 正規化の⾒直し Copyright © 2024, Oracle and/or its affiliates 16 表A 表B 例) 表Aと表Bからデータを取ってくるSELECT⽂を実⾏した場合 物理IO 発⽣ 物理IO 発⽣ 表ごとの物理IO Why 正規化の⾒直し: 正規化したテーブルはデータを維持管理する上で理想的な形だが、⽬標の性能が出ない場合もある ※表Aの検索で済むように表Bの要素を⼊れ込んだ表Aʻ ※正規化の⾒直しをせず、マテリアライズド・ビューを使う⽅法もある。 ※マテリアライズド・ビューとは、SELECTの実⾏結果をデータとして保持しておける。 正規化の⾒直し 1つ表の物理IOのみ 表A’ 物理IO 発⽣
17. Q. 2024年6⽉に受注された受注⾦額を抽出 【正規化された状態】 【正規化の⾒直し案1】 【正規化の⾒直し案2】 【参考】正規化の⾒直し例 Copyright © 2024, Oracle and/or its affiliates 17 SELECT SUM("商品"."単価" * "明細"."個数") FROM "商品" INNER JOIN "明細" ON "商品"."商品番号" = "明細"."商品番号" INNER JOIN "受注" ON "明細"."受注番号" = "受注"."受注番号" WHERE "受注"."受注⽇" BETWEEN TO_DATE('2024/06/01','YYYY/MM/DD') AND TO_DATE('2024/06/30','YYYY/MM/DD') E-R図 受注 #*受注番号 *受注⽇ *顧客番号 明細 #*受注番号 #*商品名 *個数 商品 #*商品番号 *商品名 *単価 顧客 #*顧客番号 *顧客名 E-R図 受注 #*受注番号 *受注⽇ *顧客番号 明細 #*受注番号 #*商品名 *個数 *受注⽇ 商品 #*商品番号 *商品名 *単価 顧客 #*顧客番号 *顧客名 SELECT SUM("商品"."単価" * "明細"."個数") FROM "商品" INNER JOIN "明細" ON "商品"."商品番号" = "明細"."商品番号" WHERE "明細"."受注⽇" BETWEEN TO_DATE('2024/06/01','YYYY/MM/DD') AND TO_DATE('2024/06/30','YYYY/MM/DD') E-R図 受注 #*受注番号 *受注⽇ *顧客番号 *受注⾦額 明細 #*受注番号 #*商品名 *個数 商品 #*商品番号 *商品名 *単価 顧客 #*顧客番号 *顧客名 SELECT SUM("受注"."受注⾦額") FROM "受注" WHERE "受注"."受注⽇" BETWEEN TO_DATE('2024/06/01','YYYY/MM/DD') AND TO_DATE('2024/06/30','YYYY/MM/DD') ※リレーションを含む⼀意識別⼦には縦棒 (|) を引く
18. データ処理の流れの整理 • 業務処理と表の関係を整理し、データのライフサイクルを明確化する⼿法 • 表の整理、問題の洗い出しが可能 • 読み取り(R)されないのに存在する表はないか • 更新(U)削除(D)されない表のメンテナンスは誰が⾏うのか • どの表にどのような権限や監査が必要か CRUD図 Copyright © 2024, Oracle and/or its affiliates 18 C: Create R: Read U: Update D: Delete 業務内容 ⇐ 顧客 担当 受注 明細 商品 顧客管理 CRUD 商品管理 CRUD 受注 R R CRUD CRUD R ⇒ 表 CRUD図 受注 *受注番号 *受注⽇ *顧客番号 *担当者番号 明細 *受注番号 *商品番号 *個数 商品 *商品番号 *商品名 *単価 顧客 *顧客番号 *顧客名 *住所 担当 *担当者番号 *担当者名 E-R図
19. 1. 論理設計 • E-R図 • 正規化 • CRUD図 2. 物理設計 • オブジェクトの設計 Ø 表 Ø ビュー Ø 索引 • データ格納領域の設計 Ø 表領域設計、データ・ファイル設計 Ø 領域割り当ての設計 Ø ブロックの設計 アジェンダ Copyright © 2024, Oracle and/or its affiliates 19
20. 物理設計とは • 物理設計とは • 論理設計を実際の環境にあてはめて、物理デー タベース構造の記述に変換すること • システムの性能について考慮し、データベースに依 存する機能の使⽤を検討する • 物理設計で検討するポイント ① オブジェクトの設計 ② データ格納領域の設計 データベースの物理設計 Copyright © 2024, Oracle and/or its affiliates 20 表領域 データ・ファイル ①オブジェクトの設計 (表、索引、ビュー) ②データ格納領域 （表領域、データファイル） ②データ格納領域 (表領域、データファイル)
21. オブジェクトの設計 Copyright © 2024, Oracle and/or its affiliates 21 表 ビュー 索引 表領域 表(テーブル) 論理設計したモデルを元に、表を作成 索引(インデックス) 早く検索をするために、表につける「索引」 ビュー 表データの⼀部を加⼯して⾒せる「仮想表」 オブジェクトには主に以下の要素が含まれます。
22. 表定義の決定 表設計の際に決定すること 1. 表名、列名 2. データ型、サイズ: どんな種類のデータが、どれくらい⼊るのか 3. 制約: 格納するデータに対するビジネス・ルール 表の設計 Copyright © 2024, Oracle and/or its affiliates 22 社員表 社員番号 社員名 給料 ⼊社⽇ 部⾨番号 1 ⽥中 30000 2000/04/01 10 2 鈴⽊ 25000 2002/04/01 20 3 吉⽥ 20000 2002/09/01 20 4 佐藤 20000 2004/04/01 30 5 ⼩林 15000 2010/04/01 30
23. 表名、列名の決定 表設計の際に決定すること • 表名、列名 表の設計 Copyright © 2024, Oracle and/or its affiliates 23 社員表 社員番号 社員名 給料 ⼊社⽇ 部⾨番号 1 ⽥中 30000 2000/04/01 10 2 鈴⽊ 25000 2002/04/01 20 3 吉⽥ 20000 2002/09/01 20 4 佐藤 20000 2004/04/01 30 5 ⼩林 15000 2010/04/01 30 表名、列名の命名規則 • データベースキャラクタセットに含まれる⽂字は使⽤可能 (ただし、ダブルクォート(")とNULL⽂字は使⽤不可) • 名前の⻑さは128バイト以下 • 名前の先頭に数字は使⽤不可 • Oracle SQLの予約語(ALTER、BY等)は使⽤不可 ※データベースキャラクタセットはデータベースで使⽤する⽂字コードに 相当しデータベース作成時に指定 ※データベースのキャラクタセットがAL32UTF8の場合は、⽇本語は ⼤抵1⽂字が3バイト 表名 列名 SQL⾔語リファレンス: Oracle SQLの基本要素 データベース・オブジェクト名および修飾⼦
24. データ型の決定 表の設計 Copyright © 2024, Oracle and/or its affiliates 24 社員表 Oracleで使⽤可能なデータ型の例 表設計の際に決定すること • データ型、サイズ: どんな種類のデータが、どれくらい⼊るのか ⇒意図しないデータが格納されるのを防ぐ データ型種類 データ型例 ⽂字データ型 固定⻑※: CHAR, NCHAR, 可変⻑: VARCHAR2, NVARCHAR2 数値データ型 NUMBER ⽇時データ型 DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE ラージ・オブジェクト (LOB)・データ型 BLOB, CLOB, NCLOB 社員番号 社員名 給料 ⼊社⽇ 部⾨番号 1 ⽥中 30000 2000/04/01 10 2 鈴⽊ 25000 2002/04/01 20 3 吉⽥ 20000 2002/09/01 20 4 佐藤 20000 2004/04/01 30 5 ⼩林 15000 2010/04/01 30 NUMBER 型 VARCHAR2 型 DATE 型 ※指定したバイトよりも⼩さいサイズの⽂字列を⼊⼒した場合、 不⾜サイズ分の空⽩⽂字が⾃動的に⽂字列末尾に追加される SQL⾔語リファレンス: 2Oracle SQLの基本要素 データ型
25. 制約の設計: 格納するデータに対するルールを決める 表の設計 Copyright © 2024, Oracle and/or its affiliates 社員表 部⾨表 reference Oracleで使⽤可能な制約 社員番号 社員名 給料 ⼊社⽇ 部⾨番号 1 ⽥中 30000 2000/04/01 10 2 鈴⽊ 25000 2002/04/01 20 3 吉⽥ 20000 2002/09/01 20 4 佐藤 20000 2004/04/01 30 5 ⼩林 15000 2010/04/01 30 部⾨番号 部⾨名 10 営業 20 技術 30 サポート 25 PRIMARY KEY制約 ⾏を⼀意に特定する主キー (⼀意＋必須) NOT NULLとUNIQUEの組合せ 社員番号は必須で⼀意なもの NOT NULL制約 値がNULLになることを禁⽌ 社員名は必須 UNIQUE 制約 複数の⾏が同じ値を持つことを禁⽌(NULL値は許可) - FOREIGN KEY制約 ある表の値が、別の表の値と⼀致することを要求 部⾨番号は部⾨表の部⾨番号と⼀致する CHECK制約 指定されたチェック条件を満たす必要がある 給料は０以上 NN PK CN FK 2⽇で開発者ガイド 4 スキーマ・オブジェクトの作成および管理 4.2.3.1 制約について PK
26. 索引は表データの格納場所を格納し、問合せを⾼速化するためのオブジェクト n 索引がない場合: 表のすべてのデータにアクセスしてデータを検索する n 索引がある場合: 索引から検索対象⾏の場所(ROWID)を特定して、対象⾏にアクセス 【参考】索引 Copyright © 2024, Oracle and/or its affiliates 26 CUST_ID CUST_NAME CUST_ADDRESS 100 ⽥中 東京都… 101 鈴⽊ ⼤阪府… 102 ⼭⽥ 福岡県… … 498 佐藤 宮城県… 499 吉⽥ 北海道… 100~ 300~ 100~ 200~ 300~ 400~ 100 ROWID 101 ROWID 102 ROWID … 198 ROWID 199 ROWID 200 ROWID 201 ROWID 202 ROWID … 298 ROWID 299 ROWID 300 ROWID 301 ROWID 302 ROWID … 398 ROWID 399 ROWID 400 ROWID 401 ROWID 402 ROWID … 498 ROWID 499 ROWID 例: 索引のCUST_ID_IDX=498を使って検索する場合 CUST_ID_IDX (顧客番号索引) CUSTOMERS (顧客表) CUST_ID_IDX=498は300~ CUST_ID_IDX=498は400~ CUST_ID_IDX=498の データの格納場所を特定 Oracle Database⼊⾨ 概要編 より引⽤
27. ビューを使⽤し、複数表データから条件によってデータを⼀部抜粋し組み合わせて参照可能 n ビューの実体は、⾒せるデータの「定義」 (データは含まない) n 実表のデータを様々に加⼯することができる。例えば、複数の表を結合して1つのオブジェクトのように⾒たり、集計結果 を定義して⾒ることができる。 n ビューを利⽤することで実表への直接アクセスを防ぎ、セキュリティを担保できる。 【参考】ビュー Copyright © 2024, Oracle and/or its affiliates 27 CUST_ID CUST_NAME CUST_ADDRESS 100 ⽥中 東京都… 101 鈴⽊ ⼤阪府… 102 ⼭⽥ 福岡県… ORD_ID ORD_DATE CUST_ID 10000 2023-04-01 100 10001 2023-04-02 102 10002 2023-04-03 101 10003 2023-04-04 200 10004 2023-04-05 100 例: CUSTOMERS表(顧客表)、ORDERS(注⽂票)、ORDER_DETAIL(注⽂明細表)から顧客ごとに購⼊商品と購⼊⽇を⾒るためのビューを作成 ORD_ID DETAIL_ID PROD_ID NUMBER 10000 1 10 1 10001 2 30 1 10002 1 10 4 10003 2 20 2 10004 3 30 1 CUSTOMERS(顧客表) ORDERS(注⽂表) ORDERS_DETAIL(注⽂票表) CUST_NAME ORD_DATE PROD_ID ⽥中 2023-04-01 10 ⼭⽥ 2023-04-02 30 鈴⽊ 2023-04-03 10 SQL> CREATE VIEW cust_sum AS SELECT cust_name, ord_date・・・ Oracle Database⼊⾨ 概要編 より引⽤ cust_sum
28. 集計結果を格納しておくためのオブジェクト n 事前に集計処理した結果セットを保持しておくことで検索処理時間の短縮を図る n 特徴 n SELECT⽂を書き直さなくても、参照先をマテリアライズド・ビューに透過的に変換するクエリー・リライト n 最新データを迅速に反映する⾼速リフレッシュ 【参考】マテリアライズド・ビュー(Materialized View: MVIEW) Copyright © 2024, Oracle and/or its affiliates 28 都度SELECT 遅い 都道府県 ⽀店 製品 ⽇時 売上明細 事前集計済みの データをSELECT 売上集計 (マテリアライズド・ビュー) ⾼速!! SELECT結果を事前に格納 定期的にリフレッシュ※ CREATE MATERIALIZED VIEW 売上集計 AS SELECT … FROM 売上, 製品, ⽀店, ⽇時 WHERE … ※リフレッシュ・タイミングは元表に対するCOMMIT発⾏時など選択可 Oracle Databaseデータベース概要 マテリアライズド・ビューの概要
29. 1. 論理設計 • E-R図 • 正規化 • CRUD図 2. 物理設計 • オブジェクトの設計 Ø 表 Ø ビュー Ø 索引 • データ格納領域の設計 Ø 表領域設計、データ・ファイル設計 Ø 領域割り当ての設計 Ø ブロックの設計 アジェンダ Copyright © 2024, Oracle and/or its affiliates 29
30. データ格納先のファイルを「表領域」という単位で管理 • 表領域は表の論理的な格納先 • データは物理的にはデータ・ファイルに格納される • 表領域は1つ以上のデータ・ファイルから構成される(bigfile表領域の場合は1つ) データベースには、⽬的ごとに複数の表領域を作成 • アプリケーション単位(売り上げアプリケーション⽤/⼈事アプリケーション⽤等) • メンテナンス単位(バックアップ単位など) • ディスクI/Oの分散を考慮して配置 表領域とデータファイル Copyright © 2024, Oracle and/or its affiliates 30 表領域 HR 表領域 Sales 表領域: 表の論理格納先 表などのスキーマ・オブジェクトをまとめて管理する 論理グループ データ・ファイル データ・ファイル データ・ファイル: データの格納先 表などのスキーマ・オブジェクトのデータをまとめて格納
31. Oracle Database 10g 以前 表領域の設計⽅針の例 • 格納オブジェクト単位(表と索引を別領域に配置) • メンテナンスの単位 Ø バックアップの取得単位(ユーザー管理バックアップ) Ø オンライン・オフラインの切り替え単位 • アプリケーション単位 データ・ファイルの設計⽅針の例 • I/Oの分散を意識してファイルを配置 • ⼤規模テーブルを、複数のストレージにまたがるように表領域を構 成するためにデータ・ファイルを分割配置 • REDOログ・ファイルは別ディスクへ配置 Ø 通常のSQLアクセスはランダム・アクセス Ø REDOログ・ファイルへのアクセスはシーケンシャル・アクセス • (SSDではなく)HDDを利⽤した場合、ディスクを分けた⽅が性能が良い 過去の表領域設計、データ・ファイル設計の考え⽅ Copyright © 2024, Oracle and/or its affiliates 31 表領域をどのように分割するか︖ データ・ファイルをどのように分割するか︖ 表領域 Sales 表領域 HR 表領域 IDX アプリケーション毎に表領域を分けて、 領域管理やバックアップをおこなう 索引は再作成可能なため、 定期的なバックアップ対象外に 表領域 REDO SELECT
32. 表領域の設計⽅針の例 • 無理に分割しなくてもよい • 格納オブジェクトの管理主体をわける場合、表領域単位で はなく、PDB単位とする データ・ファイルの設計⽅針の例 • ASM利⽤前提とし、I/O分散のためのデータ・ファイル分割 はおこなわない • ⼤量データ格納にはbigfile表領域を利⽤ 現在の表領域設計、データ・ファイル設計の考え⽅ Copyright © 2024, Oracle and/or its affiliates 32 表領域をどのように分割するか︖ データ・ファイルをどのように分割するか︖
33. 表領域の設計⽅針の例 • 無理に分割しなくてもよい • 格納オブジェクトの管理主体をわける場合、表領域単位で はなく、PDB単位とする データ・ファイルの設計⽅針の例 • ASM利⽤前提とし、I/O分散のためのデータ・ファイル分割 はおこなわない • ⼤量データ格納にはbigfile表領域を利⽤ 現在の表領域設計、データ・ファイル設計の考え⽅ Copyright © 2024, Oracle and/or its affiliates 33 表領域をどのように分割するか︖ データ・ファイルをどのように分割するか︖ ASMによるI/O分散 (SSD利⽤) bigfile表領域の利⽤ RMANによるバックアップ PDBによる管理
34. 表領域の設計⽅針の例 • 無理に分割しなくてもよい • 格納オブジェクトの管理主体をわける場合、表領域単位で はなく、PDB単位とする データ・ファイルの設計⽅針の例 • ASM利⽤前提とし、I/O分散のためのデータ・ファイル分割 はおこなわない • ⼤量データ格納にはbigfile表領域を利⽤ 現在の表領域設計、データ・ファイル設計の考え⽅ Copyright © 2024, Oracle and/or its affiliates 34 表領域をどのように分割するか︖ データ・ファイルをどのように分割するか︖ ASMによるI/O分散 (SSD利⽤) bigfile表領域の利⽤ RMANによるバックアップ PDBによる管理
35. バックアップ⽤ ストレージ データベース⽤ ストレージ ユーザー管理バックアップからRMANバックアップへ Recovery Manager(RMAN) • Oracle Databaseで物理バックアップおよびリカバリを 実⾏するための標準ツール • プラットフォームを問わず、同じコマンドで対処可能 • データのコピー時にデータ・ブロックの破損検査を⾏う • マルチレベル増分バックアップ • 変更されたブロックのみバックアップすることで バックアップ時間の短縮 ユーザー管理バックアップ • RMAN以外の以下の⽅法を使⽤しておこなわれる バックアップ ex) ストレージ機能のスナップ・ショット OSコマンドによるファイルコピーなど • 破損領域がそのままコピーされる可能性や、バックアッ プも破損している場合がある • 毎回、対象となるデータ・ファイルを丸ごとバックアップ オンライン・バックアップ⽅法の進化 SQLコマンド例 • バックアップの開始 • バックアップの終了 ※ バックアップの開始と終了のSQLコマンドの間において OSコマンドでデータ・ファイルのコピーを取得 ALTER TABLESPACE users BEGIN BACKUP; ALTER TABLESPACE users END BACKUP; RMANコマンド例 • バックアップの実施例 RMAN> backup database; RMAN> backup database plus archivelog; サーバー プロセス RMAN クライアント Copyright © 2024, Oracle and/or its affiliates 35
36. 表領域の設計⽅針の例 • 無理に分割しなくてもよい • 格納オブジェクトの管理主体をわける場合、表領域単位で はなく、PDB単位とする データ・ファイルの設計⽅針の例 • ASM利⽤前提とし、I/O分散のためのデータ・ファイル分割 はおこなわない • ⼤量データ格納にはbigfile表領域を利⽤ 現在の表領域設計、データ・ファイル設計の考え⽅ Copyright © 2024, Oracle and/or its affiliates 36 表領域をどのように分割するか︖ データ・ファイルをどのように分割するか︖ ASMによるI/O分散 (SSD利⽤) bigfile表領域の利⽤ RMANによるバックアップ PDBによる管理
37. スキーマ分割によるデータベース集約からマルチテナント機能の活⽤ スキーマ分割によるデータベース集約 • 業務DBをスキーマ(データベース・ユーザー)単位で分離し、 ⼀つのOracle Databaseインスタンスに集約 • 業務DB単位でストレージ障害の影響を最⼩化させるため、 バックアップとリストア、表領域のオンラインとオフラインを選択 できるように、表領域およびデータ・ファイルをスキーマにあわ せて分割 Ø データベース全体の管理だけではなく、表領域を意識した管理も 必要となる マルチテナントによるデータベース集約 • 業務DBをPDB(プラガブル・データベース)単位で分離し、 CDB(コンテナ・データベース)に格納 • 業務DBとPDBを紐付けることで、管理の考え⽅を簡素化 Ø 表領域単位での管理を考えずに済む • マルチテナントのメリット: Ø PDB単位でCPU、メモリおよびIOの各資源の割り当てを制御でき るため、効率的な運⽤が可能 Ø PDBのクローンや別なCDBへの再配置などの可搬性と迅速なプロ ビジョニングの機能を提供 データベース集約⽅式の進化 Copyright © 2024, Oracle and/or its affiliates 37 データベース・インスタンス 業務DB スキーマ 表領域 表領域 データ ファイル データ ファイル データ ファイル データ ファイル 業務DB スキーマ 表領域 表領域 データ ファイル データ ファイル データ ファイル データ ファイル 業務DB スキーマ 表領域 表領域 データ ファイル データ ファイル データ ファイル データ ファイル データベース・インスタンス(CDB) 業務DB 業務DB 業務DB PDB スキーマ 表領域 データ ファイル スキーマ 表領域 データ ファイル PDB スキーマ 表領域 データ ファイル スキーマ 表領域 データ ファイル PDB スキーマ 表領域 データ ファイル スキーマ 表領域 データ ファイル 管理単位 管理単位
38. 表領域の設計⽅針の例 • 無理に分割しなくてもよい • 格納オブジェクトの管理主体をわける場合、表領域単位で はなく、PDB単位とする データ・ファイルの設計⽅針の例 • ASM利⽤前提とし、I/O分散のためのデータ・ファイル分割 はおこなわない • ⼤量データ格納にはbigfile表領域を利⽤ 現在の表領域設計、データ・ファイル設計の考え⽅ Copyright © 2024, Oracle and/or its affiliates 38 表領域をどのように分割するか︖ データ・ファイルをどのように分割するか︖ ASMによるI/O分散 (SSD利⽤) bigfile表領域の利⽤ RMANによるバックアップ PDBによる管理
39. ASM登場後 • S. A. M. Eに基づいたストレージ構成をASMが⾃動 的に⾏う • I/O性能を最⼤限引き出しつつ、ストレージ管理⼯数 を⼤幅削減 Ø全てのストレージ・デバイスに跨ったディスクの仮想化とストラ イピングを⾃動で⾏いアクセスを均⼀化 Øストレージ・デバイスの増減にあわせた⾃動リバランス Oracle Automatic Storage Management (Oracle ASM)の登場による変化 ストレージ物理設計の変化 Copyright © 2024, Oracle and/or its affiliates 39 ASM登場前 • ディスク毎に同じアクセス・タイプになるようにディスク配置 • しかし以下ような課題がある Øすべてのアクセス・パターンを分析することは難しい Ø運⽤でアクセス・パターンが変化し、その変化にディスク配置を 常に合わせるのは難しい 表A TMP 表A 索引A シーケンシャル・アクセス ランダム・アクセス 「全てのDiskの均等利⽤を⽬的に、データをストライプして全てのDisk上に分散配置し、ミラーリングも⾏う」という設計⼿法 Stripe And Mirror Everything (S. A. M. E)とは ミラーリング ストライピング
40. smallfile表領域(デフォルト) • 複数のデータファイルを束ねて利⽤可能 (最⼤1023 個まで) • smallfile表領域では、ブロック・サイズが 8KBの場合、 1つのデータファイルに 32 GB のデータを格納できる それ以上の容量の表領域を作成したい場合は複数 のデータファイルを束ねる必要がある bigfile表領域(Oracle Database 10g〜) • 単⼀の⾮常に⼤きなデータファイルのみで構成 • ファイル管理が簡素化できる • bigfile表領域では、ブロックサイズが8KBの場合、 32TBまでデータを格納できる • ローカル管理表領域かつ⾃動セグメント領域管理で あること • Oracle Automatic Storage Management (ASM) 上での利⽤を推奨 表領域とデータ・ファイルの進化 Copyright © 2024, Oracle and/or its affiliates 40 【参考】物理データベースの制限 db_block_size データファイルの最⼤サイズ 2KB 約8GB (8,589,932,544 byte) 4KB 約16GB (17,179,865,088 byte) 8KB 約32GB (34,359,730,176 byte) 16KB 約64GB (68,719,460,352 byte)
41. Autonomous Database により⾃動作成・管理される • ユーザーによる表領域作成、削除は不可。必要に応じて⾃動的に拡張 【参考】Autonomous Database Serverlessの表領域管理 Copyright © 2024, Oracle and/or its affiliates 41 表領域名 ⾃動拡張 最⼤サイズ(*1) NOTE SYSTEM Yes - SYSAUX Yes - DATA Yes - (*2) • デフォルト表領域(変更不可) DBFS_DATA Yes - • ローカル・ファイルシステム(ディレクトリオブジェクトとして利⽤) SAMPLESCHEMA Yes - • サンプルスキーマが格納される • 読み取り専⽤、ストレージサイズには計上されないため課⾦対象外 UNDOTBS1 Yes RACノード毎にストレージサイズの5% UNDO_8 Yes - TEMP Yes ストレージサイズの30% • デフォルト⼀時表領域(変更不可) *1: 表領域のMAXBYTESは32TBだが、実質インスタンス毎のストレージサイズが上限 *2: インスタンス上限が32TB以上で、格納されるデータが32TBを超えた場合は、複数のDATA表領域が作成される
42. 表領域 領域の管理単位 【参考】論理記憶域構造の概要 Copyright © 2024, Oracle and/or its affiliates 42 索引A 表A セグメント セグメント 1対1 1対1 データファイル データファイル 物理構成 論理構成 索引A 表A データ・ブロック データ格納におけるI/Oの単位。 デフォルト8KB。 エクステント 複数の連続したデータ・ブロッ クで構成される、データベース 記憶域の割当ての論理単位 セグメント 1つのオブジェクトに割り当 てられた領域
43. ディクショナリ管理表領域からローカル管理表領域、⾃動セグメント領域管理へ ⾃動セグメント領域管理 Automatic Segment Space Management (ASSM) • 8i以前のフリーリストによる管理に ⽐べ、管理性、領域使⽤効率性、 同時実⾏処理が向上 • ASSMでなければ以下の機能は 利⽤できない • bigfile表領域 • オンライン・セグメント縮⼩ 表領域におけるエクステント割当と空き領域管理の進化 Copyright © 2024, Oracle and/or its affiliates 43 表領域におけるセグメントに対するエクステント割り当て 【ローカル管理表領域】 【ディクショナリ管理表領域】 【UNIFORM】 【AUTOALLOCATE】 表領域 空き領域 File Header エクステント管理情報 セグメント01 セグメント02 セグメント03 表領域 空き領域 File Header エクステント管理情報 セグメント01 セグメント02 セグメント03 エクステント データ・ ディクショナリ 表領域 空き領域 File Header セグメント01 セグメント02 セグメント03 エクステント管理情報 • エクステント管理はデータ・ディクショナリ • 表領域内のエクステントサイズは詳細指定可能 • 断⽚化率: ⼤ • エクステント管理は表領域内のファイルヘッ ダー内のビットマップで管理 • エクステント・サイズは指定したサイズで全て 均⼀ • 断⽚化率: ⼩ • エクステント管理は表領域内のファイルヘッダー 内のビットマップで管理 • エクステント・サイズはセグメントのサイズに従い、 Oracle内部アルゴリズムで決定 • 断⽚化率: 中 セグメント内の空き領域管理 Oracle 8.1.5 よりも前 セグメント03 空き領域
44. 表領域 領域の管理単位 【参考】論理記憶域構造の概要 Copyright © 2024, Oracle and/or its affiliates 44 索引A 表A セグメント セグメント 1対1 1対1 データファイル データファイル 物理構成 論理構成 索引A 表A データ・ブロック データ格納におけるI/Oの単位。 デフォルト8KB。 エクステント 複数の連続したデータ・ブロッ クで構成される、データベース 記憶域の割当ての論理単位 セグメント 1つのオブジェクトに割り当 てられた領域
45. データ・ブロックのサイズ • 標準サイズ データベース作成時に指定 (デフォルト8KB) Ø表領域作成時に指定しなかった場合のデフォルト・サイズ • ⾮標準サイズ 表領域ごとに異なるブロック・サイズを指定可能 Ø表領域ごとにサイズを指定 (2KB,4KB,8KB,16KB,32KBなど) Ø標準と異なるサイズを指定した表領域を作成した場合は、 同じサイズを指定した⾮標準ブロック・サイズ・バッファ (DB_16K_CACHE_SIZEなど)を構成する データ・ブロックのサイズ設計 Copyright © 2024, Oracle and/or its affiliates 45 ブロックサイズ ⼩ ⼤ 1ブロックの格納⾏数 少ない 多い ブロックI/Oのコスト 低い ⾼い ブロックI/Oの回数 (選択性による) 多くなりやすい 少なくなりやすい トランザクション競合 発⽣しにくい 発⽣しやすい ⾏連鎖の可能性 ⼤ ⼩ セグメント圧縮の効果 ⼩ ⼤ 基本的には標準サイズ(デフォルト8KB)で考える
46. データ・ブロックの空き領域を「PCTFREE」で設定 • ⾏データを格納後、更新処理でデータ量が増える場合の予備領域 • デフォルトで10%がPCTFREEとして確保 • 表ごとにPCTFREEのサイズを変更することも可能 PCTFREEの設定指針 • 更新の多い表ではPCTFREEを⼤きめに設定 • 更新の少ない表ではPCTFREEを⼩さめに設定 データ・ブロック内の領域設計 Copyright © 2024, Oracle and/or its affiliates 46 ⾏データ ⾏データ ⾏データ ⾏データ ⾏データ ⾏データ ⾏データ ⾏データ ⾏データ ⾏データ 空き領域 (PCTFREE) ⾏データ 【補⾜】⾏移⾏ 更新時のデータ量の増加により、⼀つのブロックに収まりきらず、 別のブロックに⾏データが移動すること。 該当⾏の検索時、2ブロック分読み込むことになるので効率が悪い。 ⾏データ ⾏データ ⾏データ ⾏データ ⾏データ ⾏データ ⾏データ ⾏データ ⾏データ ⾏データ(増分) ⾏データ ⾏データ ⾏移⾏ 空きなし データ・ブロック データ・ブロック データ・ブロック
47. • 論理設計 • E-R図によるデータのモデリングと正規化 ü データの洗い出しとエンティティの関係性を明確化する ü 正規化によって、冗⻑性を排除し、データの整合性と⼀貫性を確保する(正規化を⾒直す場合もある) • CRUD図により、表のライフサイクルを明確化する ü 業務で使⽤されない表はないか ü 更新や削除などのメンテナンスされない表はないか • 物理設計 • 表、ビュー、索引などのオブジェクト設計 ü 表名、列名、データ型、制約を決める ü 効率よく問い合わせするために、索引やビュー、マテリアライズド・ビューの活⽤を検討する • データ格納領域の設計 ü 技術の発展や機能の登場により、設計観点に変化がある(場合によっては、考慮不要の設計もある) ü 更新の多い表では、データ・ブロック内の空き領域を⼤きめに設定する まとめ Copyright © 2024, Oracle and/or its affiliates 47