実測による検証
前回の記事で正規形と非正規形における論理的な性能の違いは理解していただけたと思います。
今回は、実際の測定でどのような性能の差が出るかを検証してみたいと思います。細かい説明が多くなりますので、面倒と思われる方は、ざっくり図と表だけ見て頂ければ概略は分かると思います。
単純なテーブルでの検証ではイメージしづらいので、図書館の業務を想定した簡単なシステムのDBを実装してみます。
このシステムの要件を挙げます。
- 図書館における図書の貸出業務を実現する。
- 図書館は約2700万冊の蔵書を保有する。
- 蔵書は日本十進図書分類に基づく約10万のカテゴリに分類される。
- 蔵書がどの書架に収蔵されているかを管理する。
- 図書館には5棟の建屋があり、各棟は10のフロアを持つ。
各フロアには5つの部屋がある。
各部屋には20台の書架がある。
- 図書館には5棟の建屋があり、各棟は10のフロアを持つ。
- 図書館には10万人の利用者が登録されており、図書の貸出を利用する。
- 図書館では100人の職員が業務を行う。
- 図書館の業務には、図書の貸出・返却と未返却図書の督促がある。
- 1回の貸出・返却では10冊までが対象となる。
- 督促のために、1利用者あたり3件までの連絡先(TEL・メール・住所)を登録する。
- 1日あたり1000件の貸出が発生する。
- 2年分の貸出データとして約74万件のデータを記録する。
- 2年分の返却データとして約70万件のデータを記録する。
- 2年分の督促データとして約3万7千件（貸出の5%）のデータを記録する。
- 図書館業務の集計として、以下のサマリを集計する。
- 日本十進図書分類の単位で、毎月の貸出件数・返却件数・督促件数・貸出冊数・返却冊数を集計する。
- ユーザ単位で、毎月の貸出件数・返却件数・督促件数・貸出冊数・返却冊数を集計する。
正規形１スキーマの定義
この要件を実現するための正規形DBとして、次のER図のスキーマを定義します。
詳しく見て頂く必要はありませんが、以下の方針で設計しています。
- テーブルの種類として、マスタ・トランザクション・サマリに分類する
- 第三正規形まで正規化する
- 以下の条件でサマリテーブルを作ることは、副作用のない非正規化として許容する
- サマリデータだけ切り出したテーブルとし、マスタ・トランザクションの正規形を崩さない
- サマリデータは、高負荷な集計処理の実行頻度を減らす目的で作成する
- サマリデータには原子性・一貫性がないため、判定などには使用しない（主に表示目的）
- ライフサイクルを考慮してテーブルを分割する
- サロゲートキーで主キーを定義する
- 必要な外部キー制約を定義する
- プログラムが解釈するカラムにはNOT NULL制約を定義する
- 氏名など表示目的のカラムはNULLを許容する
- 必要なインデックスを定義する
- 外部キーとなるカラムには、逆引きインデックスを定義する
- 検索対象となるカラムには、インデックスを定義する
※以下のER図はクラス図作成ツールを代用しています。図の見方は下段の「凡例」をご覧ください。
---------------------------------------------------------------------------------------------------
\set ON_ERROR_STOP
set client_encoding to 'Shift_JIS';
set client_min_messages to WARNING;
---------------------------------------------------------------------------------------------------
\echo 図書館スキーマ
CREATE SCHEMA library;
GRANT USAGE ON SCHEMA library TO library;
---------------------------------------------------------------------------------------------------
\echo 利用者マスタ
CREATE SEQUENCE SEQ_UserNo
START WITH 1
INCREMENT BY 1
NO CYCLE
CACHE 10
;
CREATE TABLE UserMT (
UserID SERIAL PRIMARY KEY,
UserNo TEXT UNIQUE NOT NULL DEFAULT 'U' || to_char(nextval('SEQ_UserNo'), 'FM00000000'),
Account TEXT UNIQUE NOT NULL,
Password TEXT NOT NULL,
ValidFlag BOOLEAN NOT NULL DEFAULT true,
-- true: 有効
-- false: 無効
RegistrationDate TIMESTAMP NOT NULL DEFAULT clock_timestamp(),
LastModifiedDate TIMESTAMP NOT NULL DEFAULT clock_timestamp(),
Affiliation TEXT,
Name TEXT,
NameKana TEXT,
Notes TEXT
);
---------------------------------------------------------------------------------------------------
\echo 利用者連絡先マスタ
CREATE TABLE UserContactMT (
UserContactID SERIAL PRIMARY KEY,
UserID INT NOT NULL,
TEL TEXT NOT NULL,
Email TEXT NOT NULL,
PostalCD TEXT NOT NULL,
Address TEXT NOT NULL,
FOREIGN KEY (UserID) REFERENCES UserMT(UserID) ON DELETE CASCADE
);
CREATE INDEX IDX_UserContactMT_Fkey1 ON UserContactMT(UserID, UserContactID);
---------------------------------------------------------------------------------------------------
\echo 職員マスタ
CREATE SEQUENCE SEQ_StaffNo
START WITH 1
INCREMENT BY 1
NO CYCLE
CACHE 10
;
CREATE TABLE StaffMT (
StaffID SERIAL PRIMARY KEY,
StaffNo TEXT UNIQUE NOT NULL DEFAULT 'S' || to_char(nextval('SEQ_StaffNo'), 'FM00000000'),
Affiliation TEXT,
Name TEXT,
NameKana TEXT
);
---------------------------------------------------------------------------------------------------
\echo 建屋マスタ
CREATE TABLE BuildingMT (
BuildingID SERIAL PRIMARY KEY,
BuildingCD TEXT UNIQUE NOT NULL,
BuildingName TEXT
);
---------------------------------------------------------------------------------------------------
\echo フロアマスタ
CREATE TABLE FloorMT (
FloorID SERIAL PRIMARY KEY,
BuildingID INT NOT NULL,
FloorCD TEXT UNIQUE NOT NULL,
FloorName TEXT,
FOREIGN KEY (BuildingID) REFERENCES BuildingMT(BuildingID) ON DELETE CASCADE
);
CREATE INDEX IDX_FloorMT_Fkey1 ON FloorMT(BuildingID, FloorID);
---------------------------------------------------------------------------------------------------
\echo 部屋マスタ
CREATE TABLE RoomMT (
RoomID SERIAL PRIMARY KEY,
FloorID INT NOT NULL,
RoomCD TEXT UNIQUE NOT NULL,
RoomName TEXT,
FOREIGN KEY (FloorID) REFERENCES FloorMT(FloorID) ON DELETE CASCADE
);
CREATE INDEX IDX_RoomMT_Fkey1 ON RoomMT(FloorID, RoomID);
---------------------------------------------------------------------------------------------------
\echo 書架マスタ
CREATE TABLE ShelfMT (
ShelfID SERIAL PRIMARY KEY,
RoomID INT NOT NULL,
ShelfNo TEXT UNIQUE NOT NULL,
FOREIGN KEY (RoomID) REFERENCES RoomMT(RoomID) ON DELETE CASCADE
);
CREATE INDEX IDX_ShelfMT_Fkey1 ON ShelfMT(RoomID, ShelfID);
---------------------------------------------------------------------------------------------------
\echo 日本十進分類マスタ
CREATE TABLE NDCMT (
NDCID SERIAL PRIMARY KEY,
ParentNDCID INT DEFAULT NULL,
NDCCD TEXT UNIQUE NOT NULL,
NDCClass CHAR(1) NOT NULL
CHECK(NDCClass IN ('1', '2', '3')),
-- 区分:
-- '1': 第一次
-- '2': 第二次
-- '3': 第三次
NDCName TEXT,
FOREIGN KEY (ParentNDCID) REFERENCES NDCMT(NDCID) ON DELETE CASCADE
);
CREATE INDEX IDX_NDCMT_Fkey1 ON NDCMT(ParentNDCID, NDCID);
---------------------------------------------------------------------------------------------------
\echo 蔵書マスタ
CREATE SEQUENCE SEQ_BookNo
START WITH 1
INCREMENT BY 1
NO CYCLE
CACHE 10
;
CREATE TABLE BookMT (
BookID SERIAL PRIMARY KEY,
ShelfID INT NOT NULL,
NDCID INT NOT NULL,
BookNo TEXT UNIQUE NOT NULL DEFAULT to_char(nextval('SEQ_BookNO'), 'FM0000000000'),
ISBN TEXT NOT NULL,
Title TEXT,
TitleKana TEXT,
Author TEXT,
AuthorKana TEXT,
Publisher TEXT,
PublicationYM NUMERIC(6, 0) NOT NULL,
NumOfPage TEXT NOT NULL,
OverView TEXT,
Notes TEXT,
FOREIGN KEY (NDCID) REFERENCES NDCMT(NDCID) ON DELETE CASCADE,
FOREIGN KEY (ShelfID) REFERENCES ShelfMT(ShelfID) ON DELETE CASCADE
);
CREATE INDEX IDX_BookMT_ISBN ON BookMT(ISBN, BookID);
CREATE INDEX IDX_BookMT_Fkey1 ON BookMT(ShelfID, BookID);
CREATE INDEX IDX_BookMT_Fkey2 ON BookMT(NDCID, BookID);
---------------------------------------------------------------------------------------------------
\echo 貸出トランザクション
CREATE SEQUENCE SEQ_LendingNo
START WITH 1
INCREMENT BY 1
NO CYCLE
CACHE 10
;
CREATE TABLE LendingTR (
LendingID SERIAL PRIMARY KEY,
LendingNo TEXT UNIQUE NOT NULL DEFAULT 'LN' || to_char(nextval('SEQ_LendingNo'), 'FM0000000000'),
UserID INT NOT NULL,
ReceptionStaffID INT NOT NULL,
LendingStatus CHAR(1) NOT NULL
CHECK(LendingStatus IN ('L', 'C')) DEFAULT 'L',
-- 'L': 貸し出し中(Lending)
-- 'C': 完了(Complete)
LendingDate TIMESTAMP NOT NULL DEFAULT clock_timestamp(),
FOREIGN KEY (UserID) REFERENCES UserMT(UserID) ON DELETE CASCADE,
FOREIGN KEY (ReceptionStaffID) REFERENCES StaffMT(StaffID) ON DELETE CASCADE
);
CREATE INDEX IDX_LendingTR_Fkey1 ON LendingTR(UserID, LendingID);
CREATE INDEX IDX_LendingTR_Fkey2 ON LendingTR(ReceptionStaffID, LendingID);
CREATE INDEX IDX_LendingTR_Search1 ON LendingTR(LendingDate, LendingID);
---------------------------------------------------------------------------------------------------
\echo 貸出対象蔵書トランザクション
CREATE TABLE LendingItemTR (
LendingID INT NOT NULL,
BookID INT NOT NULL,
ReturnDueYMD NUMERIC(8, 0) NOT NULL,
PRIMARY KEY (LendingID, BookID),
FOREIGN KEY (LendingID) REFERENCES LendingTR(LendingID) ON DELETE CASCADE,
FOREIGN KEY (BookID) REFERENCES BookMT(BookID) ON DELETE CASCADE
);
CREATE INDEX IDX_LendingItemTR_Fkey1 ON LendingItemTR(BookID, LendingID);
---------------------------------------------------------------------------------------------------
\echo 返却トランザクション
CREATE SEQUENCE SEQ_ReturnNo
START WITH 1
INCREMENT BY 1
NO CYCLE
CACHE 10
;
CREATE TABLE ReturnTR (
ReturnID SERIAL PRIMARY KEY,
ReturnNo TEXT UNIQUE NOT NULL DEFAULT 'RT' || to_char(nextval('SEQ_ReturnNo'), 'FM0000000000'),
LendingID INT NOT NULL,
ReceptionStaffID INT NOT NULL,
ReturnDate TIMESTAMP NOT NULL DEFAULT clock_timestamp(),
FOREIGN KEY (LendingID) REFERENCES LendingTR(LendingID) ON DELETE CASCADE,
FOREIGN KEY (ReceptionStaffID) REFERENCES StaffMT(StaffID) ON DELETE CASCADE
);
CREATE INDEX IDX_ReturnTR_Fkey1 ON ReturnTR(LendingID, ReturnID);
CREATE INDEX IDX_ReturnTR_Fkey2 ON ReturnTR(ReceptionStaffID, ReturnID);
CREATE INDEX IDX_ReturnTR_Search1 ON ReturnTR(ReturnDate, ReturnID);
---------------------------------------------------------------------------------------------------
\echo 返却対象蔵書トランザクション
CREATE TABLE ReturnItemTR (
ReturnID INT NOT NULL,
BookID INT NOT NULL,
PRIMARY KEY (ReturnID, BookID),
FOREIGN KEY (ReturnID) REFERENCES ReturnTR(ReturnID) ON DELETE CASCADE,
FOREIGN KEY (BookID) REFERENCES BookMT(BookID) ON DELETE CASCADE
);
CREATE INDEX IDX_ReturnItemTR_Fkey1 ON ReturnItemTR(BookID, ReturnID);
---------------------------------------------------------------------------------------------------
\echo 督促トランザクション
CREATE SEQUENCE SEQ_RemindNo
START WITH 1
INCREMENT BY 1
NO CYCLE
CACHE 10
;
CREATE TABLE ReminderTR (
ReminderID SERIAL PRIMARY KEY,
ReminderNo TEXT NOT NULL DEFAULT 'RM' || to_char(nextval('SEQ_RemindNo'), 'FM0000000000'),
LendingID INT NOT NULL,
StaffID INT NOT NULL,
ContactID INT NOT NULL,
ReminderDate TIMESTAMP NOT NULL DEFAULT clock_timestamp(),
ReminderType CHAR(1) NOT NULL
CHECK(ReminderType IN ('M', 'T', 'P')),
-- 'M': メール(Mail)
-- 'T': TEL
-- 'P': 郵便(Postal)
FOREIGN KEY (LendingID) REFERENCES LendingTR(LendingID) ON DELETE CASCADE,
FOREIGN KEY (StaffID) REFERENCES StaffMT(StaffID) ON DELETE CASCADE,
FOREIGN KEY (ContactID) REFERENCES UserContactMT(UserContactID) ON DELETE CASCADE
);
CREATE INDEX IDX_ReminderTR_Fkey1 ON ReminderTR(LendingID, ReminderID);
CREATE INDEX IDX_ReminderTR_Fkey2 ON ReminderTR(StaffID, ReminderID);
CREATE INDEX IDX_ReminderTR_Fkey3 ON ReminderTR(ContactID, ReminderID);
CREATE INDEX IDX_ReminderTR_Search1 ON ReminderTR(ReminderDate, ReminderID);
---------------------------------------------------------------------------------------------------
\echo 分類別貸出状況サマリ
CREATE TABLE CategoryLendingSM (
NDCID INT NOT NULL,
YM NUMERIC(6, 0) NOT NULL,
LendingCount INT NOT NULL DEFAULT 0,
ReturnCount INT NOT NULL DEFAULT 0,
ReminderCount INT NOT NULL DEFAULT 0,
LendingBookCount INT NOT NULL DEFAULT 0,
ReturnBookCount INT NOT NULL DEFAULT 0,
PRIMARY KEY (NDCID, YM),
FOREIGN KEY (NDCID) REFERENCES NDCMT(NDCID) ON DELETE CASCADE
);
---------------------------------------------------------------------------------------------------
\echo 利用者別貸出状況サマリ
CREATE TABLE UserLendingSM (
UserID INT NOT NULL,
YM NUMERIC(6, 0) NOT NULL,
LendingCount INT NOT NULL DEFAULT 0,
ReturnCount INT NOT NULL DEFAULT 0,
ReminderCount INT NOT NULL DEFAULT 0,
LendingBookCount INT NOT NULL DEFAULT 0,
ReturnBookCount INT NOT NULL DEFAULT 0,
PRIMARY KEY(UserID, YM),
FOREIGN KEY (UserID) REFERENCES UserMT(UserID) ON DELETE CASCADE
);
---------------------------------------------------------------------------------------------------非正規形スキーマ1の定義
次に、同じ要件を実現する非正規形のDBとして、次のER図のスキーマを定義します。
こちらは以下の方針で設計しています。
- テーブルの種類として、マスタ・トランザクション・サマリに分類する
- 第一正規化をしない（繰り返し項目の許容）
- 第二正規化をしない（関数従属の許容）
- 第三正規化をしない（推移的関数従属の許容）
- JOINの必要がなくなるように冗長なカラム（繰り返し項目・推移的関数従属の項目）を持たせる
- ナチュラルキーで主キーを定義する
- 必要な外部キー制約を定義する
- プログラムが解釈するカラムには基本的にNOT NULL制約を定義する。ただし、非正規化により不可能な場合には設定しない
- 氏名など表示目的のカラムはNULLを許容する
- 必要なインデックスを定義する（正規形と同様）
---------------------------------------------------------------------------------------------------
\set ON_ERROR_STOP
set client_encoding to 'Shift_JIS';
set client_min_messages to WARNING;
---------------------------------------------------------------------------------------------------
\echo 図書館スキーマ
CREATE SCHEMA library;
GRANT USAGE ON SCHEMA library TO library;
---------------------------------------------------------------------------------------------------
\echo 利用者マスタ
CREATE SEQUENCE SEQ_UserNo
START WITH 1
INCREMENT BY 1
NO CYCLE
CACHE 10
;
CREATE TABLE UserMT (
UserNo TEXT PRIMARY KEY DEFAULT 'U' || to_char(nextval('SEQ_UserNo'), 'FM00000000'),
Account TEXT UNIQUE NOT NULL,
Password TEXT NOT NULL,
ValidFlag BOOLEAN NOT NULL DEFAULT true,
-- true: 有効
-- false: 無効
RegistrationDate TIMESTAMP NOT NULL DEFAULT clock_timestamp(),
LastModifiedDate TIMESTAMP NOT NULL DEFAULT clock_timestamp(),
Affiliation TEXT,
Name TEXT,
NameKana TEXT,
Notes TEXT,
TEL_1 TEXT DEFAULT NULL,
TEL_2 TEXT DEFAULT NULL,
TEL_3 TEXT DEFAULT NULL,
Email_1 TEXT DEFAULT NULL,
Email_2 TEXT DEFAULT NULL,
Email_3 TEXT DEFAULT NULL,
PostalCD_1 TEXT DEFAULT NULL,
PostalCD_2 TEXT DEFAULT NULL,
PostalCD_3 TEXT DEFAULT NULL,
Address_1 TEXT DEFAULT NULL,
Address_2 TEXT DEFAULT NULL,
Address_3 TEXT DEFAULT NULL
);
---------------------------------------------------------------------------------------------------
\echo 職員マスタ
CREATE SEQUENCE SEQ_StaffNo
START WITH 1
INCREMENT BY 1
NO CYCLE
CACHE 10
;
CREATE TABLE StaffMT (
StaffNo TEXT PRIMARY KEY DEFAULT 'S' || to_char(nextval('SEQ_StaffNo'), 'FM00000000'),
Affiliation TEXT,
Name TEXT,
NameKana TEXT
);
---------------------------------------------------------------------------------------------------
\echo 建屋マスタ
CREATE TABLE BuildingMT (
BuildingCD TEXT PRIMARY KEY,
BuildingName TEXT
);
---------------------------------------------------------------------------------------------------
\echo フロアマスタ
CREATE TABLE FloorMT (
FloorCD TEXT PRIMARY KEY,
BuildingCD TEXT NOT NULL,
BuildingName TEXT,
FloorName TEXT,
FOREIGN KEY (BuildingCD) REFERENCES BuildingMT(BuildingCD) ON DELETE CASCADE
);
CREATE INDEX IDX_FloorMT_Search1 ON FloorMT(BuildingCD, FloorCD);
---------------------------------------------------------------------------------------------------
\echo 部屋マスタ
CREATE TABLE RoomMT (
RoomCD TEXT PRIMARY KEY,
BuildingCD TEXT NOT NULL,
BuildingName TEXT,
FloorCD TEXT NOT NULL,
FloorName TEXT,
RoomName TEXT,
FOREIGN KEY (BuildingCD) REFERENCES BuildingMT(BuildingCD) ON DELETE CASCADE,
FOREIGN KEY (FloorCD) REFERENCES FloorMT(FloorCD) ON DELETE CASCADE
);
CREATE INDEX IDX_RoomMT_Search1 ON RoomMT(BuildingCD, FloorCD, RoomCD);
CREATE INDEX IDX_RoomMT_Search2 ON RoomMT(FloorCD, RoomCD);
---------------------------------------------------------------------------------------------------
\echo 書架マスタ
CREATE TABLE ShelfMT (
ShelfCD TEXT PRIMARY KEY,
BuildingCD TEXT NOT NULL,
BuildingName TEXT,
FloorCD TEXT NOT NULL,
FloorName TEXT,
RoomCD TEXT NOT NULL,
RoomName TEXT,
FOREIGN KEY (BuildingCD) REFERENCES BuildingMT(BuildingCD) ON DELETE CASCADE,
FOREIGN KEY (FloorCD) REFERENCES FloorMT(FloorCD) ON DELETE CASCADE,
FOREIGN KEY (RoomCD) REFERENCES RoomMT(RoomCD) ON DELETE CASCADE
);
CREATE INDEX IDX_ShelfMT_Search1 ON ShelfMT(BuildingCD, FloorCD, RoomCD, ShelfCD);
CREATE INDEX IDX_ShelfMT_Search2 ON ShelfMT(FloorCD, ShelfCD);
CREATE INDEX IDX_ShelfMT_Search3 ON ShelfMT(RoomCD, ShelfCD);
---------------------------------------------------------------------------------------------------
\echo 日本十進分類マスタ
CREATE TABLE NDCMT (
NDCCD TEXT PRIMARY KEY,
ParentNDCCD TEXT DEFAULT NULL,
NDCClass CHAR(1) NOT NULL
CHECK(NDCClass IN ('1', '2', '3')),
-- 区分:
-- '1': 第一次
-- '2': 第二次
-- '3': 第三次
ParentNDCName TEXT,
NDCName TEXT,
FOREIGN KEY (ParentNDCCD) REFERENCES NDCMT(NDCCD) ON DELETE CASCADE
);
CREATE INDEX IDX_NDCMT_Search1 ON NDCMT(ParentNDCCD, NDCCD);
---------------------------------------------------------------------------------------------------
\echo 蔵書マスタ
CREATE SEQUENCE SEQ_BookNo
START WITH 1
INCREMENT BY 1
NO CYCLE
CACHE 10
;
CREATE TABLE BookMT (
BookNo TEXT PRIMARY KEY DEFAULT to_char(nextval('SEQ_BookNO'), 'FM0000000000'),
NDCCD TEXT NOT NULL,
NDCName TEXT,
ISBN TEXT NOT NULL,
BuildingCD TEXT NOT NULL,
BuildingName TEXT,
FloorCD TEXT NOT NULL,
FloorName TEXT,
RoomCD TEXT NOT NULL,
RoomName TEXT,
ShelfCD TEXT NOT NULL,
Title TEXT,
TitleKana TEXT,
Author TEXT,
AuthorKana TEXT,
Publisher TEXT,
PublicationYM NUMERIC(6, 0) NOT NULL,
NumOfPage TEXT NOT NULL,
OverView TEXT,
Notes TEXT,
FOREIGN KEY (NDCCD) REFERENCES NDCMT(NDCCD) ON DELETE CASCADE,
FOREIGN KEY (BuildingCD) REFERENCES BuildingMT(BuildingCD) ON DELETE CASCADE,
FOREIGN KEY (FloorCD) REFERENCES FloorMT(FloorCD) ON DELETE CASCADE,
FOREIGN KEY (RoomCD) REFERENCES RoomMT(RoomCD) ON DELETE CASCADE,
FOREIGN KEY (ShelfCD) REFERENCES ShelfMT(ShelfCD) ON DELETE CASCADE
);
CREATE INDEX IDX_BookMT_ISBN ON BookMT(ISBN, BookNo);
CREATE INDEX IDX_BookMT_NDC ON BookMT(NDCCD, BookNo);
CREATE INDEX IDX_BookMT_Shelf1 ON BookMT(BuildingCD, FloorCD, RoomCD, ShelfCD, BookNo);
CREATE INDEX IDX_BookMT_Shelf2 ON BookMT(FloorCD, BookNo);
CREATE INDEX IDX_BookMT_Shelf3 ON BookMT(RoomCD, BookNo);
CREATE INDEX IDX_BookMT_Shelf4 ON BookMT(ShelfCD, BookNo);
---------------------------------------------------------------------------------------------------
\echo 貸出トランザクション
CREATE SEQUENCE SEQ_LendingNo
START WITH 1
INCREMENT BY 1
NO CYCLE
CACHE 10
;
CREATE TABLE LendingTR (
LendingNo TEXT PRIMARY KEY DEFAULT 'LN' || to_char(nextval('SEQ_LendingNo'), 'FM0000000000'),
UserNo TEXT NOT NULL,
UserAffiliation TEXT,
UserName TEXT,
UserNameKana TEXT,
LendingDate TIMESTAMP NOT NULL DEFAULT clock_timestamp(),
ReceptionStaffNo TEXT NOT NULL,
ReceptionStaffAffiliation TEXT,
ReceptionStaffName TEXT,
ReceptionStaffNameKana TEXT,
LendingStatus CHAR(1) NOT NULL DEFAULT 'L'
CHECK(LendingStatus IN ('L', 'C')),
-- 'L': 貸し出し中(Lending)
-- 'C': 完了(Complete)
NumOfBook INT DEFAULT 0 NOT NULL,
BookNo_1 TEXT DEFAULT NULL,
BookNo_2 TEXT DEFAULT NULL,
BookNo_3 TEXT DEFAULT NULL,
BookNo_4 TEXT DEFAULT NULL,
BookNo_5 TEXT DEFAULT NULL,
BookNo_6 TEXT DEFAULT NULL,
BookNo_7 TEXT DEFAULT NULL,
BookNo_8 TEXT DEFAULT NULL,
BookNo_9 TEXT DEFAULT NULL,
BookNo_10 TEXT DEFAULT NULL,
ReturnDueYMD_1 NUMERIC(8, 0) DEFAULT NULL,
ReturnDueYMD_2 NUMERIC(8, 0) DEFAULT NULL,
ReturnDueYMD_3 NUMERIC(8, 0) DEFAULT NULL,
ReturnDueYMD_4 NUMERIC(8, 0) DEFAULT NULL,
ReturnDueYMD_5 NUMERIC(8, 0) DEFAULT NULL,
ReturnDueYMD_6 NUMERIC(8, 0) DEFAULT NULL,
ReturnDueYMD_7 NUMERIC(8, 0) DEFAULT NULL,
ReturnDueYMD_8 NUMERIC(8, 0) DEFAULT NULL,
ReturnDueYMD_9 NUMERIC(8, 0) DEFAULT NULL,
ReturnDueYMD_10 NUMERIC(8, 0) DEFAULT NULL,
Title_1 TEXT DEFAULT NULL,
Title_2 TEXT DEFAULT NULL,
Title_3 TEXT DEFAULT NULL,
Title_4 TEXT DEFAULT NULL,
Title_5 TEXT DEFAULT NULL,
Title_6 TEXT DEFAULT NULL,
Title_7 TEXT DEFAULT NULL,
Title_8 TEXT DEFAULT NULL,
Title_9 TEXT DEFAULT NULL,
Title_10 TEXT DEFAULT NULL,
FOREIGN KEY (UserNo) REFERENCES UserMT(UserNo) ON DELETE CASCADE,
FOREIGN KEY (ReceptionStaffNo) REFERENCES StaffMT(StaffNo) ON DELETE CASCADE,
FOREIGN KEY (BookNo_1) REFERENCES BookMT(BookNo) ON DELETE CASCADE,
FOREIGN KEY (BookNo_2) REFERENCES BookMT(BookNo) ON DELETE CASCADE,
FOREIGN KEY (BookNo_3) REFERENCES BookMT(BookNo) ON DELETE CASCADE,
FOREIGN KEY (BookNo_4) REFERENCES BookMT(BookNo) ON DELETE CASCADE,
FOREIGN KEY (BookNo_5) REFERENCES BookMT(BookNo) ON DELETE CASCADE,
FOREIGN KEY (BookNo_6) REFERENCES BookMT(BookNo) ON DELETE CASCADE,
FOREIGN KEY (BookNo_7) REFERENCES BookMT(BookNo) ON DELETE CASCADE,
FOREIGN KEY (BookNo_8) REFERENCES BookMT(BookNo) ON DELETE CASCADE,
FOREIGN KEY (BookNo_9) REFERENCES BookMT(BookNo) ON DELETE CASCADE,
FOREIGN KEY (BookNo_10) REFERENCES BookMT(BookNo) ON DELETE CASCADE
);
CREATE INDEX IDX_LendingTR_Search1 ON LendingTR(UserNo, LendingNo);
CREATE INDEX IDX_LendingTR_Search2 ON LendingTR(ReceptionStaffNo, LendingNo);
CREATE INDEX IDX_LendingTR_Search3 ON LendingTR(BookNo_1, LendingNo);
CREATE INDEX IDX_LendingTR_Search4 ON LendingTR(BookNo_2, LendingNo);
CREATE INDEX IDX_LendingTR_Search5 ON LendingTR(BookNo_3, LendingNo);
CREATE INDEX IDX_LendingTR_Search6 ON LendingTR(BookNo_4, LendingNo);
CREATE INDEX IDX_LendingTR_Search7 ON LendingTR(BookNo_5, LendingNo);
CREATE INDEX IDX_LendingTR_Search8 ON LendingTR(BookNo_6, LendingNo);
CREATE INDEX IDX_LendingTR_Search9 ON LendingTR(BookNo_7, LendingNo);
CREATE INDEX IDX_LendingTR_Search10 ON LendingTR(BookNo_8, LendingNo);
CREATE INDEX IDX_LendingTR_Search11 ON LendingTR(BookNo_9, LendingNo);
CREATE INDEX IDX_LendingTR_Search12 ON LendingTR(BookNo_10, LendingNo);
CREATE INDEX IDX_LendingTR_Search13 ON LendingTR(LendingDate, LendingNo);
---------------------------------------------------------------------------------------------------
\echo 返却トランザクション
CREATE SEQUENCE SEQ_ReturnNo
START WITH 1
INCREMENT BY 1
NO CYCLE
CACHE 10
;
CREATE TABLE ReturnTR (
ReturnNo TEXT PRIMARY KEY DEFAULT 'RT' || to_char(nextval('SEQ_ReturnNo'), 'FM0000000000'),
LendingNo TEXT NOT NULL,
UserNo TEXT NOT NULL,
UserAffiliation TEXT,
UserName TEXT,
UserNameKana TEXT,
ReturnDate TIMESTAMP NOT NULL DEFAULT clock_timestamp(),
ReceptionStaffNo TEXT NOT NULL,
ReceptionStaffAffiliation TEXT,
ReceptionStaffName TEXT,
ReceptionStaffNameKana TEXT,
NumOfBook INT DEFAULT 0 NOT NULL,
BookNo_1 TEXT DEFAULT NULL,
BookNo_2 TEXT DEFAULT NULL,
BookNo_3 TEXT DEFAULT NULL,
BookNo_4 TEXT DEFAULT NULL,
BookNo_5 TEXT DEFAULT NULL,
BookNo_6 TEXT DEFAULT NULL,
BookNo_7 TEXT DEFAULT NULL,
BookNo_8 TEXT DEFAULT NULL,
BookNo_9 TEXT DEFAULT NULL,
BookNo_10 TEXT DEFAULT NULL,
ReturnDueYMD_1 NUMERIC(8, 0) DEFAULT NULL,
ReturnDueYMD_2 NUMERIC(8, 0) DEFAULT NULL,
ReturnDueYMD_3 NUMERIC(8, 0) DEFAULT NULL,
ReturnDueYMD_4 NUMERIC(8, 0) DEFAULT NULL,
ReturnDueYMD_5 NUMERIC(8, 0) DEFAULT NULL,
ReturnDueYMD_6 NUMERIC(8, 0) DEFAULT NULL,
ReturnDueYMD_7 NUMERIC(8, 0) DEFAULT NULL,
ReturnDueYMD_8 NUMERIC(8, 0) DEFAULT NULL,
ReturnDueYMD_9 NUMERIC(8, 0) DEFAULT NULL,
ReturnDueYMD_10 NUMERIC(8, 0) DEFAULT NULL,
Title_1 TEXT DEFAULT NULL,
Title_2 TEXT DEFAULT NULL,
Title_3 TEXT DEFAULT NULL,
Title_4 TEXT DEFAULT NULL,
Title_5 TEXT DEFAULT NULL,
Title_6 TEXT DEFAULT NULL,
Title_7 TEXT DEFAULT NULL,
Title_8 TEXT DEFAULT NULL,
Title_9 TEXT DEFAULT NULL,
Title_10 TEXT DEFAULT NULL,
FOREIGN KEY (LendingNo) REFERENCES LendingTR(LendingNo) ON DELETE CASCADE,
FOREIGN KEY (UserNo) REFERENCES UserMT(UserNo) ON DELETE CASCADE,
FOREIGN KEY (ReceptionStaffNo) REFERENCES StaffMT(StaffNo) ON DELETE CASCADE,
FOREIGN KEY (BookNo_1) REFERENCES BookMT(BookNo) ON DELETE CASCADE,
FOREIGN KEY (BookNo_2) REFERENCES BookMT(BookNo) ON DELETE CASCADE,
FOREIGN KEY (BookNo_3) REFERENCES BookMT(BookNo) ON DELETE CASCADE,
FOREIGN KEY (BookNo_4) REFERENCES BookMT(BookNo) ON DELETE CASCADE,
FOREIGN KEY (BookNo_5) REFERENCES BookMT(BookNo) ON DELETE CASCADE,
FOREIGN KEY (BookNo_6) REFERENCES BookMT(BookNo) ON DELETE CASCADE,
FOREIGN KEY (BookNo_7) REFERENCES BookMT(BookNo) ON DELETE CASCADE,
FOREIGN KEY (BookNo_8) REFERENCES BookMT(BookNo) ON DELETE CASCADE,
FOREIGN KEY (BookNo_9) REFERENCES BookMT(BookNo) ON DELETE CASCADE,
FOREIGN KEY (BookNo_10) REFERENCES BookMT(BookNo) ON DELETE CASCADE
);
CREATE INDEX IDX_ReturnTR_Search1 ON ReturnTR(LendingNo, ReturnNo);
CREATE INDEX IDX_ReturnTR_Search2 ON ReturnTR(UserNo, ReturnNo);
CREATE INDEX IDX_ReturnTR_Search3 ON ReturnTR(ReceptionStaffNo, ReturnNo);
CREATE INDEX IDX_ReturnTR_Search4 ON ReturnTR(BookNo_1, ReturnNo);
CREATE INDEX IDX_ReturnTR_Search5 ON ReturnTR(BookNo_2, ReturnNo);
CREATE INDEX IDX_ReturnTR_Search6 ON ReturnTR(BookNo_3, ReturnNo);
CREATE INDEX IDX_ReturnTR_Search7 ON ReturnTR(BookNo_4, ReturnNo);
CREATE INDEX IDX_ReturnTR_Search8 ON ReturnTR(BookNo_5, ReturnNo);
CREATE INDEX IDX_ReturnTR_Search9 ON ReturnTR(BookNo_6, ReturnNo);
CREATE INDEX IDX_ReturnTR_Search10 ON ReturnTR(BookNo_7, ReturnNo);
CREATE INDEX IDX_ReturnTR_Search11 ON ReturnTR(BookNo_8, ReturnNo);
CREATE INDEX IDX_ReturnTR_Search12 ON ReturnTR(BookNo_9, ReturnNo);
CREATE INDEX IDX_ReturnTR_Search13 ON ReturnTR(BookNo_10, ReturnNo);
CREATE INDEX IDX_ReturnTR_Search14 ON ReturnTR(ReturnDate, ReturnNo);
---------------------------------------------------------------------------------------------------
\echo 督促トランザクション
CREATE SEQUENCE SEQ_RemindNo
START WITH 1
INCREMENT BY 1
NO CYCLE
CACHE 10
;
CREATE TABLE ReminderTR (
ReminderNo TEXT PRIMARY KEY DEFAULT 'RM' || to_char(nextval('SEQ_RemindNo'), 'FM0000000000'),
LendingNo TEXT NOT NULL,
ReminderDate TIMESTAMP NOT NULL DEFAULT clock_timestamp(),
UserNo TEXT NOT NULL,
UserAffiliation TEXT,
UserName TEXT,
UserNameKana TEXT,
ReceptionStaffNo TEXT NOT NULL,
ReceptionStaffAffiliation TEXT,
ReceptionStaffName TEXT,
ReceptionStaffNameKana TEXT,
ReminderType CHAR(1) NOT NULL
CHECK(ReminderType IN ('M', 'T', 'P')),
-- 'M': メール(Mail)
-- 'T': TEL
-- 'P': 郵便(Postal)
TEL TEXT NOT NULL,
Email TEXT NOT NULL,
PostalCD TEXT NOT NULL,
Address TEXT NOT NULL,
FOREIGN KEY (LendingNo) REFERENCES LendingTR(LendingNo) ON DELETE CASCADE,
FOREIGN KEY (UserNo) REFERENCES UserMT(UserNo) ON DELETE CASCADE,
FOREIGN KEY (ReceptionStaffNo) REFERENCES StaffMT(StaffNo) ON DELETE CASCADE
);
CREATE INDEX IDX_ReminderTR_Search1 ON ReminderTR(LendingNo, ReminderNo);
CREATE INDEX IDX_ReminderTR_Search2 ON ReminderTR(UserNo, ReminderNo);
CREATE INDEX IDX_ReminderTR_Search3 ON ReminderTR(ReceptionStaffNo, ReminderNo);
CREATE INDEX IDX_ReminderTR_Search4 ON ReminderTR(ReminderDate, ReminderNo);
---------------------------------------------------------------------------------------------------
\echo 分類別貸出状況サマリ
CREATE TABLE CategoryLendingSM (
NDCCD TEXT NOT NULL,
YM NUMERIC(6, 0) NOT NULL,
LendingCount INT NOT NULL DEFAULT 0,
ReturnCount INT NOT NULL DEFAULT 0,
ReminderCount INT NOT NULL DEFAULT 0,
LendingBookCount INT NOT NULL DEFAULT 0,
ReturnBookCount INT NOT NULL DEFAULT 0,
PRIMARY KEY (NDCCD, YM),
FOREIGN KEY (NDCCD) REFERENCES NDCMT(NDCCD) ON DELETE CASCADE
);
CREATE INDEX IDX_CategoryLendingSM_Search1 ON CategoryLendingSM(NDCCD, YM);
---------------------------------------------------------------------------------------------------
\echo 利用者別貸出状況サマリ
CREATE TABLE UserLendingSM (
UserNo TEXT NOT NULL,
YM NUMERIC(6, 0) NOT NULL,
LendingCount INT NOT NULL DEFAULT 0,
ReturnCount INT NOT NULL DEFAULT 0,
ReminderCount INT NOT NULL DEFAULT 0,
LendingBookCount INT NOT NULL DEFAULT 0,
ReturnBookCount INT NOT NULL DEFAULT 0,
PRIMARY KEY(UserNo, YM),
FOREIGN KEY (UserNo) REFERENCES UserMT(UserNo) ON DELETE CASCADE
);
CREATE INDEX IDX_UserLendingSM_Search1 ON UserLendingSM(UserNo, YM);
---------------------------------------------------------------------------------------------------非正規形スキーマ2
要件が簡素であるため、非正規形スキーマ1程度ではレコードサイズが1ページを超過する状況になりませんでした。
そこで比較のため、さらに極端な非正規化を行った非正規形スキーマ2を用意しました。少し無理のある設計ですが、この程度にテーブルが肥大化した状況は、それほど珍しいものではありません。
こちらは以下の方針で設計しています。
- 基本的な方針は非正規形スキーマ1と同等
- レコードサイズが1ページ(8KB)以上になるように冗長なカラムを増やす
---------------------------------------------------------------------------------------------------
\set ON_ERROR_STOP
set client_encoding to 'Shift_JIS';
set client_min_messages to WARNING;
---------------------------------------------------------------------------------------------------
\echo 図書館スキーマ
CREATE SCHEMA library;
GRANT USAGE ON SCHEMA library TO library;
---------------------------------------------------------------------------------------------------
\echo 利用者マスタ
CREATE SEQUENCE SEQ_UserNo
START WITH 1
INCREMENT BY 1
NO CYCLE
CACHE 10
;
CREATE TABLE UserMT (
UserNo TEXT PRIMARY KEY DEFAULT 'U' || to_char(nextval('SEQ_UserNo'), 'FM00000000'),
Account TEXT UNIQUE NOT NULL,
Password TEXT NOT NULL,
ValidFlag BOOLEAN NOT NULL DEFAULT true,
-- true: 有効
-- false: 無効
RegistrationDate TIMESTAMP NOT NULL DEFAULT clock_timestamp(),
LastModifiedDate TIMESTAMP NOT NULL DEFAULT clock_timestamp(),
Affiliation TEXT,
Name TEXT,
NameKana TEXT,
Notes TEXT,
NumOfContact INT DEFAULT 0 NOT NULL,
TEL_1 TEXT DEFAULT NULL,
TEL_2 TEXT DEFAULT NULL,
TEL_3 TEXT DEFAULT NULL,
Email_1 TEXT DEFAULT NULL,
Email_2 TEXT DEFAULT NULL,
Email_3 TEXT DEFAULT NULL,
PostalCD_1 TEXT DEFAULT NULL,
PostalCD_2 TEXT DEFAULT NULL,
PostalCD_3 TEXT DEFAULT NULL,
Address_1 TEXT DEFAULT NULL,
Address_2 TEXT DEFAULT NULL,
Address_3 TEXT DEFAULT NULL
);
---------------------------------------------------------------------------------------------------
\echo 職員マスタ
CREATE SEQUENCE SEQ_StaffNo
START WITH 1
INCREMENT BY 1
NO CYCLE
CACHE 10
;
CREATE TABLE StaffMT (
StaffNo TEXT PRIMARY KEY DEFAULT 'S' || to_char(nextval('SEQ_StaffNo'), 'FM00000000'),
Affiliation TEXT,
Name TEXT,
NameKana TEXT
);
---------------------------------------------------------------------------------------------------
\echo 建屋マスタ
CREATE TABLE BuildingMT (
BuildingCD TEXT PRIMARY KEY,
BuildingName TEXT
);
---------------------------------------------------------------------------------------------------
\echo フロアマスタ
CREATE TABLE FloorMT (
FloorCD TEXT PRIMARY KEY,
BuildingCD TEXT NOT NULL,
BuildingName TEXT,
FloorName TEXT,
FOREIGN KEY (BuildingCD) REFERENCES BuildingMT(BuildingCD) ON DELETE CASCADE
);
CREATE INDEX IDX_FloorMT_Search1 ON FloorMT(BuildingCD, FloorCD);
---------------------------------------------------------------------------------------------------
\echo 部屋マスタ
CREATE TABLE RoomMT (
RoomCD TEXT PRIMARY KEY,
BuildingCD TEXT NOT NULL,
BuildingName TEXT,
FloorCD TEXT NOT NULL,
FloorName TEXT,
RoomName TEXT,
FOREIGN KEY (BuildingCD) REFERENCES BuildingMT(BuildingCD) ON DELETE CASCADE,
FOREIGN KEY (FloorCD) REFERENCES FloorMT(FloorCD) ON DELETE CASCADE
);
CREATE INDEX IDX_RoomMT_Search1 ON RoomMT(BuildingCD, FloorCD, RoomCD);
CREATE INDEX IDX_RoomMT_Search2 ON RoomMT(FloorCD, RoomCD);
---------------------------------------------------------------------------------------------------
\echo 書架マスタ
CREATE TABLE ShelfMT (
ShelfCD TEXT PRIMARY KEY,
BuildingCD TEXT NOT NULL,
BuildingName TEXT,
FloorCD TEXT NOT NULL,
FloorName TEXT,
RoomCD TEXT NOT NULL,
RoomName TEXT,
FOREIGN KEY (BuildingCD) REFERENCES BuildingMT(BuildingCD) ON DELETE CASCADE,
FOREIGN KEY (FloorCD) REFERENCES FloorMT(FloorCD) ON DELETE CASCADE,
FOREIGN KEY (RoomCD) REFERENCES RoomMT(RoomCD) ON DELETE CASCADE
);
CREATE INDEX IDX_ShelfMT_Search1 ON ShelfMT(BuildingCD, FloorCD, RoomCD, ShelfCD);
CREATE INDEX IDX_ShelfMT_Search2 ON ShelfMT(FloorCD, ShelfCD);
CREATE INDEX IDX_ShelfMT_Search3 ON ShelfMT(RoomCD, ShelfCD);
---------------------------------------------------------------------------------------------------
\echo 日本十進分類マスタ
CREATE TABLE NDCMT (
NDCCD TEXT PRIMARY KEY,
ParentNDCCD TEXT DEFAULT NULL,
NDCClass CHAR(1) NOT NULL
CHECK(NDCClass IN ('1', '2', '3')),
-- 区分:
-- '1': 第一次
-- '2': 第二次
-- '3': 第三次
ParentNDCName TEXT,
NDCName TEXT,
FOREIGN KEY (ParentNDCCD) REFERENCES NDCMT(NDCCD) ON DELETE CASCADE
);
CREATE INDEX IDX_NDCMT_Search1 ON NDCMT(ParentNDCCD, NDCCD);
---------------------------------------------------------------------------------------------------
\echo 蔵書マスタ
CREATE SEQUENCE SEQ_BookNo
START WITH 1
INCREMENT BY 1
NO CYCLE
CACHE 10
;
CREATE TABLE BookMT (
BookNo TEXT PRIMARY KEY DEFAULT to_char(nextval('SEQ_BookNO'), 'FM0000000000'),
NDCCD_1 TEXT NOT NULL,
NDCCD_2 TEXT NOT NULL,
NDCCD_3 TEXT NOT NULL,
NDCName_1 TEXT,
NDCName_2 TEXT,
NDCName_3 TEXT,
ISBN TEXT NOT NULL,
BuildingCD TEXT NOT NULL,
BuildingName TEXT,
FloorCD TEXT NOT NULL,
FloorName TEXT,
RoomCD TEXT NOT NULL,
RoomName TEXT,
ShelfCD TEXT NOT NULL,
Title TEXT,
TitleKana TEXT,
Author TEXT,
AuthorKana TEXT,
Publisher TEXT,
PublicationYM NUMERIC(6, 0) NOT NULL,
NumOfPage TEXT NOT NULL,
OverView TEXT,
Notes TEXT,
FOREIGN KEY (NDCCD_1) REFERENCES NDCMT(NDCCD) ON DELETE CASCADE,
FOREIGN KEY (NDCCD_2) REFERENCES NDCMT(NDCCD) ON DELETE CASCADE,
FOREIGN KEY (NDCCD_3) REFERENCES NDCMT(NDCCD) ON DELETE CASCADE,
FOREIGN KEY (BuildingCD) REFERENCES BuildingMT(BuildingCD) ON DELETE CASCADE,
FOREIGN KEY (FloorCD) REFERENCES FloorMT(FloorCD) ON DELETE CASCADE,
FOREIGN KEY (RoomCD) REFERENCES RoomMT(RoomCD) ON DELETE CASCADE,
FOREIGN KEY (ShelfCD) REFERENCES ShelfMT(ShelfCD) ON DELETE CASCADE
);
CREATE INDEX IDX_BookMT_ISBN ON BookMT(ISBN, BookNo);
CREATE INDEX IDX_BookMT_NDC_1 ON BookMT(NDCCD_1, BookNo);
CREATE INDEX IDX_BookMT_NDC_2 ON BookMT(NDCCD_2, BookNo);
CREATE INDEX IDX_BookMT_NDC_3 ON BookMT(NDCCD_3, BookNo);
CREATE INDEX IDX_BookMT_Shelf1 ON BookMT(BuildingCD, FloorCD, RoomCD, ShelfCD, BookNo);
CREATE INDEX IDX_BookMT_Shelf2 ON BookMT(FloorCD, BookNo);
CREATE INDEX IDX_BookMT_Shelf3 ON BookMT(RoomCD, BookNo);
CREATE INDEX IDX_BookMT_Shelf4 ON BookMT(ShelfCD, BookNo);
---------------------------------------------------------------------------------------------------
\echo 貸出トランザクション
CREATE SEQUENCE SEQ_LendingNo
START WITH 1
INCREMENT BY 1
NO CYCLE
CACHE 10
;
CREATE TABLE LendingTR (
LendingNo TEXT PRIMARY KEY DEFAULT 'LN' || to_char(nextval('SEQ_LendingNo'), 'FM0000000000'),
UserNo TEXT NOT NULL,
UserAffiliation TEXT,
UserName TEXT,
UserNameKana TEXT,
LendingDate TIMESTAMP NOT NULL DEFAULT clock_timestamp(),
ReceptionStaffNo TEXT NOT NULL,
ReceptionStaffAffiliation TEXT,
ReceptionStaffName TEXT,
ReceptionStaffNameKana TEXT,
LendingStatus CHAR(1) NOT NULL DEFAULT 'L'
CHECK(LendingStatus IN ('L', 'C')),
-- 'L': 貸し出し中(Lending)
-- 'C': 完了(Complete)
NumOfBook INT DEFAULT 0 NOT NULL,
BookNo_1 TEXT DEFAULT NULL,
BookNo_2 TEXT DEFAULT NULL,
BookNo_3 TEXT DEFAULT NULL,
BookNo_4 TEXT DEFAULT NULL,
BookNo_5 TEXT DEFAULT NULL,
BookNo_6 TEXT DEFAULT NULL,
BookNo_7 TEXT DEFAULT NULL,
BookNo_8 TEXT DEFAULT NULL,
BookNo_9 TEXT DEFAULT NULL,
BookNo_10 TEXT DEFAULT NULL,
ReturnDueYMD_1 NUMERIC(8, 0) DEFAULT NULL,
ReturnDueYMD_2 NUMERIC(8, 0) DEFAULT NULL,
ReturnDueYMD_3 NUMERIC(8, 0) DEFAULT NULL,
ReturnDueYMD_4 NUMERIC(8, 0) DEFAULT NULL,
ReturnDueYMD_5 NUMERIC(8, 0) DEFAULT NULL,
ReturnDueYMD_6 NUMERIC(8, 0) DEFAULT NULL,
ReturnDueYMD_7 NUMERIC(8, 0) DEFAULT NULL,
ReturnDueYMD_8 NUMERIC(8, 0) DEFAULT NULL,
ReturnDueYMD_9 NUMERIC(8, 0) DEFAULT NULL,
ReturnDueYMD_10 NUMERIC(8, 0) DEFAULT NULL,
Title_1 TEXT DEFAULT NULL,
Title_2 TEXT DEFAULT NULL,
Title_3 TEXT DEFAULT NULL,
Title_4 TEXT DEFAULT NULL,
Title_5 TEXT DEFAULT NULL,
Title_6 TEXT DEFAULT NULL,
Title_7 TEXT DEFAULT NULL,
Title_8 TEXT DEFAULT NULL,
Title_9 TEXT DEFAULT NULL,
Title_10 TEXT DEFAULT NULL,
TitleKana_1 TEXT DEFAULT NULL,
TitleKana_2 TEXT DEFAULT NULL,
TitleKana_3 TEXT DEFAULT NULL,
TitleKana_4 TEXT DEFAULT NULL,
TitleKana_5 TEXT DEFAULT NULL,
TitleKana_6 TEXT DEFAULT NULL,
TitleKana_7 TEXT DEFAULT NULL,
TitleKana_8 TEXT DEFAULT NULL,
TitleKana_9 TEXT DEFAULT NULL,
TitleKana_10 TEXT DEFAULT NULL,
NDCCD_1 TEXT DEFAULT NULL,
NDCCD_2 TEXT DEFAULT NULL,
NDCCD_3 TEXT DEFAULT NULL,
NDCCD_4 TEXT DEFAULT NULL,
NDCCD_5 TEXT DEFAULT NULL,
NDCCD_6 TEXT DEFAULT NULL,
NDCCD_7 TEXT DEFAULT NULL,
NDCCD_8 TEXT DEFAULT NULL,
NDCCD_9 TEXT DEFAULT NULL,
NDCCD_10 TEXT DEFAULT NULL,
NDCName_1 TEXT DEFAULT NULL,
NDCName_2 TEXT DEFAULT NULL,
NDCName_3 TEXT DEFAULT NULL,
NDCName_4 TEXT DEFAULT NULL,
NDCName_5 TEXT DEFAULT NULL,
NDCName_6 TEXT DEFAULT NULL,
NDCName_7 TEXT DEFAULT NULL,
NDCName_8 TEXT DEFAULT NULL,
NDCName_9 TEXT DEFAULT NULL,
NDCName_10 TEXT DEFAULT NULL,
Author_1 TEXT DEFAULT NULL,
Author_2 TEXT DEFAULT NULL,
Author_3 TEXT DEFAULT NULL,
Author_4 TEXT DEFAULT NULL,
Author_5 TEXT DEFAULT NULL,
Author_6 TEXT DEFAULT NULL,
Author_7 TEXT DEFAULT NULL,
Author_8 TEXT DEFAULT NULL,
Author_9 TEXT DEFAULT NULL,
Author_10 TEXT DEFAULT NULL,
AuthorKana_1 TEXT DEFAULT NULL,
AuthorKana_2 TEXT DEFAULT NULL,
AuthorKana_3 TEXT DEFAULT NULL,
AuthorKana_4 TEXT DEFAULT NULL,
AuthorKana_5 TEXT DEFAULT NULL,
AuthorKana_6 TEXT DEFAULT NULL,
AuthorKana_7 TEXT DEFAULT NULL,
AuthorKana_8 TEXT DEFAULT NULL,
AuthorKana_9 TEXT DEFAULT NULL,
AuthorKana_10 TEXT DEFAULT NULL,
Publisher_1 TEXT DEFAULT NULL,
Publisher_2 TEXT DEFAULT NULL,
Publisher_3 TEXT DEFAULT NULL,
Publisher_4 TEXT DEFAULT NULL,
Publisher_5 TEXT DEFAULT NULL,
Publisher_6 TEXT DEFAULT NULL,
Publisher_7 TEXT DEFAULT NULL,
Publisher_8 TEXT DEFAULT NULL,
Publisher_9 TEXT DEFAULT NULL,
Publisher_10 TEXT DEFAULT NULL,
PublicationYM_1 NUMERIC(6, 0) DEFAULT NULL,
PublicationYM_2 NUMERIC(6, 0) DEFAULT NULL,
PublicationYM_3 NUMERIC(6, 0) DEFAULT NULL,
PublicationYM_4 NUMERIC(6, 0) DEFAULT NULL,
PublicationYM_5 NUMERIC(6, 0) DEFAULT NULL,
PublicationYM_6 NUMERIC(6, 0) DEFAULT NULL,
PublicationYM_7 NUMERIC(6, 0) DEFAULT NULL,
PublicationYM_8 NUMERIC(6, 0) DEFAULT NULL,
PublicationYM_9 NUMERIC(6, 0) DEFAULT NULL,
PublicationYM_10 NUMERIC(6, 0) DEFAULT NULL,
NumOfPage_1 TEXT DEFAULT NULL,
NumOfPage_2 TEXT DEFAULT NULL,
NumOfPage_3 TEXT DEFAULT NULL,
NumOfPage_4 TEXT DEFAULT NULL,
NumOfPage_5 TEXT DEFAULT NULL,
NumOfPage_6 TEXT DEFAULT NULL,
NumOfPage_7 TEXT DEFAULT NULL,
NumOfPage_8 TEXT DEFAULT NULL,
NumOfPage_9 TEXT DEFAULT NULL,
NumOfPage_10 TEXT DEFAULT NULL,
OverView_1 TEXT DEFAULT NULL,
OverView_2 TEXT DEFAULT NULL,
OverView_3 TEXT DEFAULT NULL,
OverView_4 TEXT DEFAULT NULL,
OverView_5 TEXT DEFAULT NULL,
OverView_6 TEXT DEFAULT NULL,
OverView_7 TEXT DEFAULT NULL,
OverView_8 TEXT DEFAULT NULL,
OverView_9 TEXT DEFAULT NULL,
OverView_10 TEXT DEFAULT NULL,
Notes_1 TEXT DEFAULT NULL,
Notes_2 TEXT DEFAULT NULL,
Notes_3 TEXT DEFAULT NULL,
Notes_4 TEXT DEFAULT NULL,
Notes_5 TEXT DEFAULT NULL,
Notes_6 TEXT DEFAULT NULL,
Notes_7 TEXT DEFAULT NULL,
Notes_8 TEXT DEFAULT NULL,
Notes_9 TEXT DEFAULT NULL,
Notes_10 TEXT DEFAULT NULL,
FOREIGN KEY (UserNo) REFERENCES UserMT(UserNo) ON DELETE CASCADE,
FOREIGN KEY (ReceptionStaffNo) REFERENCES StaffMT(StaffNo) ON DELETE CASCADE,
FOREIGN KEY (BookNo_1) REFERENCES BookMT(BookNo) ON DELETE CASCADE,
FOREIGN KEY (BookNo_2) REFERENCES BookMT(BookNo) ON DELETE CASCADE,
FOREIGN KEY (BookNo_3) REFERENCES BookMT(BookNo) ON DELETE CASCADE,
FOREIGN KEY (BookNo_4) REFERENCES BookMT(BookNo) ON DELETE CASCADE,
FOREIGN KEY (BookNo_5) REFERENCES BookMT(BookNo) ON DELETE CASCADE,
FOREIGN KEY (BookNo_6) REFERENCES BookMT(BookNo) ON DELETE CASCADE,
FOREIGN KEY (BookNo_7) REFERENCES BookMT(BookNo) ON DELETE CASCADE,
FOREIGN KEY (BookNo_8) REFERENCES BookMT(BookNo) ON DELETE CASCADE,
FOREIGN KEY (BookNo_9) REFERENCES BookMT(BookNo) ON DELETE CASCADE,
FOREIGN KEY (BookNo_10) REFERENCES BookMT(BookNo) ON DELETE CASCADE
);
CREATE INDEX IDX_LendingTR_Search1 ON LendingTR(UserNo, LendingNo);
CREATE INDEX IDX_LendingTR_Search2 ON LendingTR(ReceptionStaffNo, LendingNo);
CREATE INDEX IDX_LendingTR_Search3 ON LendingTR(BookNo_1, LendingNo);
CREATE INDEX IDX_LendingTR_Search4 ON LendingTR(BookNo_2, LendingNo);
CREATE INDEX IDX_LendingTR_Search5 ON LendingTR(BookNo_3, LendingNo);
CREATE INDEX IDX_LendingTR_Search6 ON LendingTR(BookNo_4, LendingNo);
CREATE INDEX IDX_LendingTR_Search7 ON LendingTR(BookNo_5, LendingNo);
CREATE INDEX IDX_LendingTR_Search8 ON LendingTR(BookNo_6, LendingNo);
CREATE INDEX IDX_LendingTR_Search9 ON LendingTR(BookNo_7, LendingNo);
CREATE INDEX IDX_LendingTR_Search10 ON LendingTR(BookNo_8, LendingNo);
CREATE INDEX IDX_LendingTR_Search11 ON LendingTR(BookNo_9, LendingNo);
CREATE INDEX IDX_LendingTR_Search12 ON LendingTR(BookNo_10, LendingNo);
CREATE INDEX IDX_LendingTR_Search13 ON LendingTR(LendingDate, LendingNo);
---------------------------------------------------------------------------------------------------
\echo 返却トランザクション
CREATE SEQUENCE SEQ_ReturnNo
START WITH 1
INCREMENT BY 1
NO CYCLE
CACHE 10
;
CREATE TABLE ReturnTR (
ReturnNo TEXT PRIMARY KEY DEFAULT 'RT' || to_char(nextval('SEQ_ReturnNo'), 'FM0000000000'),
LendingNo TEXT NOT NULL,
UserNo TEXT NOT NULL,
UserAffiliation TEXT,
UserName TEXT,
UserNameKana TEXT,
ReturnDate TIMESTAMP NOT NULL DEFAULT clock_timestamp(),
ReceptionStaffNo TEXT NOT NULL,
ReceptionStaffAffiliation TEXT,
ReceptionStaffName TEXT,
ReceptionStaffNameKana TEXT,
NumOfBook INT DEFAULT 0 NOT NULL,
BookNo_1 TEXT DEFAULT NULL,
BookNo_2 TEXT DEFAULT NULL,
BookNo_3 TEXT DEFAULT NULL,
BookNo_4 TEXT DEFAULT NULL,
BookNo_5 TEXT DEFAULT NULL,
BookNo_6 TEXT DEFAULT NULL,
BookNo_7 TEXT DEFAULT NULL,
BookNo_8 TEXT DEFAULT NULL,
BookNo_9 TEXT DEFAULT NULL,
BookNo_10 TEXT DEFAULT NULL,
ReturnDueYMD_1 NUMERIC(8, 0) DEFAULT NULL,
ReturnDueYMD_2 NUMERIC(8, 0) DEFAULT NULL,
ReturnDueYMD_3 NUMERIC(8, 0) DEFAULT NULL,
ReturnDueYMD_4 NUMERIC(8, 0) DEFAULT NULL,
ReturnDueYMD_5 NUMERIC(8, 0) DEFAULT NULL,
ReturnDueYMD_6 NUMERIC(8, 0) DEFAULT NULL,
ReturnDueYMD_7 NUMERIC(8, 0) DEFAULT NULL,
ReturnDueYMD_8 NUMERIC(8, 0) DEFAULT NULL,
ReturnDueYMD_9 NUMERIC(8, 0) DEFAULT NULL,
ReturnDueYMD_10 NUMERIC(8, 0) DEFAULT NULL,
Title_1 TEXT DEFAULT NULL,
Title_2 TEXT DEFAULT NULL,
Title_3 TEXT DEFAULT NULL,
Title_4 TEXT DEFAULT NULL,
Title_5 TEXT DEFAULT NULL,
Title_6 TEXT DEFAULT NULL,
Title_7 TEXT DEFAULT NULL,
Title_8 TEXT DEFAULT NULL,
Title_9 TEXT DEFAULT NULL,
Title_10 TEXT DEFAULT NULL,
TitleKana_1 TEXT DEFAULT NULL,
TitleKana_2 TEXT DEFAULT NULL,
TitleKana_3 TEXT DEFAULT NULL,
TitleKana_4 TEXT DEFAULT NULL,
TitleKana_5 TEXT DEFAULT NULL,
TitleKana_6 TEXT DEFAULT NULL,
TitleKana_7 TEXT DEFAULT NULL,
TitleKana_8 TEXT DEFAULT NULL,
TitleKana_9 TEXT DEFAULT NULL,
TitleKana_10 TEXT DEFAULT NULL,
NDCCD_1 TEXT DEFAULT NULL,
NDCCD_2 TEXT DEFAULT NULL,
NDCCD_3 TEXT DEFAULT NULL,
NDCCD_4 TEXT DEFAULT NULL,
NDCCD_5 TEXT DEFAULT NULL,
NDCCD_6 TEXT DEFAULT NULL,
NDCCD_7 TEXT DEFAULT NULL,
NDCCD_8 TEXT DEFAULT NULL,
NDCCD_9 TEXT DEFAULT NULL,
NDCCD_10 TEXT DEFAULT NULL,
NDCName_1 TEXT DEFAULT NULL,
NDCName_2 TEXT DEFAULT NULL,
NDCName_3 TEXT DEFAULT NULL,
NDCName_4 TEXT DEFAULT NULL,
NDCName_5 TEXT DEFAULT NULL,
NDCName_6 TEXT DEFAULT NULL,
NDCName_7 TEXT DEFAULT NULL,
NDCName_8 TEXT DEFAULT NULL,
NDCName_9 TEXT DEFAULT NULL,
NDCName_10 TEXT DEFAULT NULL,
Author_1 TEXT DEFAULT NULL,
Author_2 TEXT DEFAULT NULL,
Author_3 TEXT DEFAULT NULL,
Author_4 TEXT DEFAULT NULL,
Author_5 TEXT DEFAULT NULL,
Author_6 TEXT DEFAULT NULL,
Author_7 TEXT DEFAULT NULL,
Author_8 TEXT DEFAULT NULL,
Author_9 TEXT DEFAULT NULL,
Author_10 TEXT DEFAULT NULL,
AuthorKana_1 TEXT DEFAULT NULL,
AuthorKana_2 TEXT DEFAULT NULL,
AuthorKana_3 TEXT DEFAULT NULL,
AuthorKana_4 TEXT DEFAULT NULL,
AuthorKana_5 TEXT DEFAULT NULL,
AuthorKana_6 TEXT DEFAULT NULL,
AuthorKana_7 TEXT DEFAULT NULL,
AuthorKana_8 TEXT DEFAULT NULL,
AuthorKana_9 TEXT DEFAULT NULL,
AuthorKana_10 TEXT DEFAULT NULL,
Publisher_1 TEXT DEFAULT NULL,
Publisher_2 TEXT DEFAULT NULL,
Publisher_3 TEXT DEFAULT NULL,
Publisher_4 TEXT DEFAULT NULL,
Publisher_5 TEXT DEFAULT NULL,
Publisher_6 TEXT DEFAULT NULL,
Publisher_7 TEXT DEFAULT NULL,
Publisher_8 TEXT DEFAULT NULL,
Publisher_9 TEXT DEFAULT NULL,
Publisher_10 TEXT DEFAULT NULL,
PublicationYM_1 NUMERIC(6, 0) DEFAULT NULL,
PublicationYM_2 NUMERIC(6, 0) DEFAULT NULL,
PublicationYM_3 NUMERIC(6, 0) DEFAULT NULL,
PublicationYM_4 NUMERIC(6, 0) DEFAULT NULL,
PublicationYM_5 NUMERIC(6, 0) DEFAULT NULL,
PublicationYM_6 NUMERIC(6, 0) DEFAULT NULL,
PublicationYM_7 NUMERIC(6, 0) DEFAULT NULL,
PublicationYM_8 NUMERIC(6, 0) DEFAULT NULL,
PublicationYM_9 NUMERIC(6, 0) DEFAULT NULL,
PublicationYM_10 NUMERIC(6, 0) DEFAULT NULL,
NumOfPage_1 TEXT DEFAULT NULL,
NumOfPage_2 TEXT DEFAULT NULL,
NumOfPage_3 TEXT DEFAULT NULL,
NumOfPage_4 TEXT DEFAULT NULL,
NumOfPage_5 TEXT DEFAULT NULL,
NumOfPage_6 TEXT DEFAULT NULL,
NumOfPage_7 TEXT DEFAULT NULL,
NumOfPage_8 TEXT DEFAULT NULL,
NumOfPage_9 TEXT DEFAULT NULL,
NumOfPage_10 TEXT DEFAULT NULL,
OverView_1 TEXT DEFAULT NULL,
OverView_2 TEXT DEFAULT NULL,
OverView_3 TEXT DEFAULT NULL,
OverView_4 TEXT DEFAULT NULL,
OverView_5 TEXT DEFAULT NULL,
OverView_6 TEXT DEFAULT NULL,
OverView_7 TEXT DEFAULT NULL,
OverView_8 TEXT DEFAULT NULL,
OverView_9 TEXT DEFAULT NULL,
OverView_10 TEXT DEFAULT NULL,
Notes_1 TEXT DEFAULT NULL,
Notes_2 TEXT DEFAULT NULL,
Notes_3 TEXT DEFAULT NULL,
Notes_4 TEXT DEFAULT NULL,
Notes_5 TEXT DEFAULT NULL,
Notes_6 TEXT DEFAULT NULL,
Notes_7 TEXT DEFAULT NULL,
Notes_8 TEXT DEFAULT NULL,
Notes_9 TEXT DEFAULT NULL,
Notes_10 TEXT DEFAULT NULL,
FOREIGN KEY (LendingNo) REFERENCES LendingTR(LendingNo) ON DELETE CASCADE,
FOREIGN KEY (UserNo) REFERENCES UserMT(UserNo) ON DELETE CASCADE,
FOREIGN KEY (ReceptionStaffNo) REFERENCES StaffMT(StaffNo) ON DELETE CASCADE,
FOREIGN KEY (BookNo_1) REFERENCES BookMT(BookNo) ON DELETE CASCADE,
FOREIGN KEY (BookNo_2) REFERENCES BookMT(BookNo) ON DELETE CASCADE,
FOREIGN KEY (BookNo_3) REFERENCES BookMT(BookNo) ON DELETE CASCADE,
FOREIGN KEY (BookNo_4) REFERENCES BookMT(BookNo) ON DELETE CASCADE,
FOREIGN KEY (BookNo_5) REFERENCES BookMT(BookNo) ON DELETE CASCADE,
FOREIGN KEY (BookNo_6) REFERENCES BookMT(BookNo) ON DELETE CASCADE,
FOREIGN KEY (BookNo_7) REFERENCES BookMT(BookNo) ON DELETE CASCADE,
FOREIGN KEY (BookNo_8) REFERENCES BookMT(BookNo) ON DELETE CASCADE,
FOREIGN KEY (BookNo_9) REFERENCES BookMT(BookNo) ON DELETE CASCADE,
FOREIGN KEY (BookNo_10) REFERENCES BookMT(BookNo) ON DELETE CASCADE
);
CREATE INDEX IDX_ReturnTR_Search1 ON ReturnTR(LendingNo, ReturnNo);
CREATE INDEX IDX_ReturnTR_Search2 ON ReturnTR(UserNo, ReturnNo);
CREATE INDEX IDX_ReturnTR_Search3 ON ReturnTR(ReceptionStaffNo, ReturnNo);
CREATE INDEX IDX_ReturnTR_Search4 ON ReturnTR(BookNo_1, ReturnNo);
CREATE INDEX IDX_ReturnTR_Search5 ON ReturnTR(BookNo_2, ReturnNo);
CREATE INDEX IDX_ReturnTR_Search6 ON ReturnTR(BookNo_3, ReturnNo);
CREATE INDEX IDX_ReturnTR_Search7 ON ReturnTR(BookNo_4, ReturnNo);
CREATE INDEX IDX_ReturnTR_Search8 ON ReturnTR(BookNo_5, ReturnNo);
CREATE INDEX IDX_ReturnTR_Search9 ON ReturnTR(BookNo_6, ReturnNo);
CREATE INDEX IDX_ReturnTR_Search10 ON ReturnTR(BookNo_7, ReturnNo);
CREATE INDEX IDX_ReturnTR_Search11 ON ReturnTR(BookNo_8, ReturnNo);
CREATE INDEX IDX_ReturnTR_Search12 ON ReturnTR(BookNo_9, ReturnNo);
CREATE INDEX IDX_ReturnTR_Search13 ON ReturnTR(BookNo_10, ReturnNo);
CREATE INDEX IDX_ReturnTR_Search14 ON ReturnTR(ReturnDate, ReturnNo);
---------------------------------------------------------------------------------------------------
\echo 督促トランザクション
CREATE SEQUENCE SEQ_RemindNo
START WITH 1
INCREMENT BY 1
NO CYCLE
CACHE 10
;
CREATE TABLE ReminderTR (
ReminderNo TEXT PRIMARY KEY DEFAULT 'RM' || to_char(nextval('SEQ_RemindNo'), 'FM0000000000'),
LendingNo TEXT NOT NULL,
ReminderDate TIMESTAMP NOT NULL DEFAULT clock_timestamp(),
UserNo TEXT NOT NULL,
UserAffiliation TEXT,
UserName TEXT,
UserNameKana TEXT,
ReceptionStaffNo TEXT NOT NULL,
ReceptionStaffAffiliation TEXT,
ReceptionStaffName TEXT,
ReceptionStaffNameKana TEXT,
ReminderType CHAR(1) NOT NULL
CHECK(ReminderType IN ('M', 'T', 'P')),
-- 'M': メール(Mail)
-- 'T': TEL
-- 'P': 郵便(Postal)
TEL TEXT NOT NULL,
Email TEXT NOT NULL,
PostalCD TEXT NOT NULL,
Address TEXT NOT NULL,
FOREIGN KEY (LendingNo) REFERENCES LendingTR(LendingNo) ON DELETE CASCADE,
FOREIGN KEY (UserNo) REFERENCES UserMT(UserNo) ON DELETE CASCADE,
FOREIGN KEY (ReceptionStaffNo) REFERENCES StaffMT(StaffNo) ON DELETE CASCADE
);
CREATE INDEX IDX_ReminderTR_Search1 ON ReminderTR(LendingNo, ReminderNo);
CREATE INDEX IDX_ReminderTR_Search2 ON ReminderTR(UserNo, ReminderNo);
CREATE INDEX IDX_ReminderTR_Search3 ON ReminderTR(ReceptionStaffNo, ReminderNo);
CREATE INDEX IDX_ReminderTR_Search4 ON ReminderTR(ReminderDate, ReminderNo);
---------------------------------------------------------------------------------------------------
\echo 分類別貸出状況サマリ
CREATE TABLE CategoryLendingSM (
NDCCD TEXT NOT NULL,
YM NUMERIC(6, 0) NOT NULL,
LendingCount INT NOT NULL DEFAULT 0,
ReturnCount INT NOT NULL DEFAULT 0,
ReminderCount INT NOT NULL DEFAULT 0,
LendingBookCount INT NOT NULL DEFAULT 0,
ReturnBookCount INT NOT NULL DEFAULT 0,
PRIMARY KEY (NDCCD, YM),
FOREIGN KEY (NDCCD) REFERENCES NDCMT(NDCCD) ON DELETE CASCADE
);
CREATE INDEX IDX_CategoryLendingSM_Search1 ON CategoryLendingSM(NDCCD, YM);
---------------------------------------------------------------------------------------------------
\echo 利用者別貸出状況サマリ
CREATE TABLE UserLendingSM (
UserNo TEXT NOT NULL,
YM NUMERIC(6, 0) NOT NULL,
LendingCount INT NOT NULL DEFAULT 0,
ReturnCount INT NOT NULL DEFAULT 0,
ReminderCount INT NOT NULL DEFAULT 0,
LendingBookCount INT NOT NULL DEFAULT 0,
ReturnBookCount INT NOT NULL DEFAULT 0,
PRIMARY KEY(UserNo, YM),
FOREIGN KEY (UserNo) REFERENCES UserMT(UserNo) ON DELETE CASCADE
);
CREATE INDEX IDX_UserLendingSM_Search1 ON UserLendingSM(UserNo, YM);
---------------------------------------------------------------------------------------------------スキーマの比較
3つのER図を見比べると、同等の情報を表す定義でありながら以下の違いがあることが分かります。
- 正規形ではカラム数が少ないが、テーブル数は多い。
- 非正規形ではカラム数が多いが、テーブル数は少ない。
- マスタ・サマリテーブルでは、非正規形でもカラム数はあまり変わらない。
- トランザクションテーブルでは、冗長性が高いためカラム数に大きな差がある。
前回の記事で、レコードサイズがI/O性能に影響すると説明しましたので、ここで各スキーマにおけるレコードサイズ1を比較します。
レコードサイズが、非正規形2 > 非正規形1 > 正規形1のようになっています。
検証環境
測定を行う環境として、以下の仮想マシンを用意しました。それほど速い環境ではありませんが、比較目的としては問題ないと思います。
- OS: Windows Server 2022 Standard x64
- CPU: Core i7-6770HQ
- メモリ: 8GB
- ディスク: M.2 SSD
- DB: PostgreSQL 16.3.2
測定はPL/PgSQLのスクリプトで行います。以下のようにスクリプトを実装しました。
- 各テーブルに対する登録・更新・削除を行う専用関数を用意する
- PL/PgSQLで登録データを組み立て、上記関数を呼び出して登録する
- スクリプト完了までの時間を測定する
- データ量の増加に合わせるため10分間隔でVACUUM ANALYZEを行う
- 要件で定めた件数のマスタデータを登録する
- 下記の条件で貸出のトランザクションデータを登録する
- 2年分(2022/1/1～2023/12/31)のデータを登録する
- 1日あたり1000件の貸出データを登録する
- 貸出1件あたりランダムに決定した10冊の蔵書を貸出対象とする
- 貸出に関わる利用者・職員はランダムに決定する
- 下記の条件で返却のトランザクションデータを登録する
- 2年分(2022/1/1～2023/12/31)のデータを登録する
- 1日あたり900件の返却データを登録する（100件は督促を行う）
- 返却1件あたり貸出に対応する10冊の蔵書を返却対象とする
- 返却に関わる職員はランダムに決定する
- 下記の条件で督促のトランザクションデータを登録する
- 2年分(2022/1/1～2023/12/31)のデータを登録する
- 1日あたり100件の督促データと、それに対応する返却データを登録する
- 返却に関わる職員はランダムに決定する
- 下記の条件で分類別貸出状況サマリを集計する
- 2年分(2022/1/1～2023/12/31)の集計を行う
- 日本十進図書分類の分類コードごとに、毎月の貸出件数・返却件数・督促件数・貸出冊数・返却冊数を集計する
- 下記の条件で利用者別貸出状況サマリを集計する
- 2年分(2022/1/1～2023/12/31)の集計を行う
- 利用者ごとに、毎月の貸出件数・返却件数・督促件数・貸出冊数・返却冊数を集計する
測定時間にはデータ組み立て処理やVACUUMのオーバーヘッドが含まれますが、単純なDMLの実行よりも実際の業務システムの動作に近い計測ができると思います。
また、今回の測定にはランダムアクセスに強いM.2 SSDを使用していますが、もしHDDを使用した場合では、性能差はより顕著になるはずです。
性能測定1
各スキーマに対するデータ登録の実行時間は、以下のようになりました。
グラフから、次の傾向があることが分かります。
- 全体的には、正規形の方が性能が良好
- 「分類別貸出状況サマリ」だけ、非正規形2の性能が良い
- 概ねレコードサイズに比例した処理時間となっている
- レコードサイズの差が小さいテーブル（主にマスタ）では、性能差も少ない
- レコードサイズの差が大きいテーブル（主にトランザクション）では、大きな性能の違いがある
マスタ・トランザクションテーブルに対する登録ではINSERTの比率が高いため、書き込むデータ量に応じて時間がかかるという結果は妥当です。
ここではUPDATE・DELETEについて計測していませんが、非正規形では情報を冗長に持つため、複数のカラム・テーブルを更新する必要が発生し、性能差はより顕著になるはずです。
サマリテーブルでは集計のSELECTを行いその結果をINSERTするため、SELECT処理の比率が高くなります。この違いで、少し面白い結果になりました。
- 利用者別貸出状況サマリでは、想定通り正規形が速い。
- 分類別貸出状況サマリでは、非正規形2が最も速いという逆転が起きている。
explainコマンドで詳しく調べたところ、以下の原因であることが分かりました。
- 非正規形2では、貸出・返却トランザクションに推移的関数従属の形で「日本十進分類」の情報を持っているため、蔵書マスタをJOINする必要がない。
- 正規形1・非正規形1では、貸出・返却トランザクションに「日本十進分類」の情報を持たないため、蔵書マスタをJOINする必要がある。
- 蔵書マスタのレコードサイズは1,500byte程度と比較的大きく、さらに貸出・返却1件ごとに対象の蔵書が10件あるため、蔵書マスタをJOINすると読み込みデータ量が大きくなる。
- 正規形1の読み込みデータ量（貸出1件あたり概算）: 15,809[byte]
- 非正規形1の読み込みデータ量（貸出1件あたり概算）: 18,532[byte]
- 非正規形2の読み込みデータ量（貸出1件あたり概算）: 15,626[byte]
レコードサイズが大きい状況では、正規形であっても性能低下を起こしていることが分かりました。
では、この場合トランザクションテーブルに対する非正規化が必要でしょうか？
そうではないことを示すため、さらに2つの測定を行います。
正規形2スキーマの定義
正規形1の反省点として、蔵書マスタのレコードサイズが大きいことが分かりました。
蔵書マスタには「概要」「備考」といった大きなサイズのカラムが含まれていますが、このカラムの意味を考えると、これを必要とする処理は限定的です。必要ないデータを毎回読み込むのは、I/Oの無駄になります。
そこで正規形2のスキーマを定義し、蔵書マスタを2つのテーブルに分割します。
- 蔵書マスタには蔵書の基本情報のみ定義する。
- 「概要」など付帯的な情報を蔵書詳細マスタとして切り出す。
---------------------------------------------------------------------------------------------------
\set ON_ERROR_STOP
set client_encoding to 'Shift_JIS';
set client_min_messages to WARNING;
---------------------------------------------------------------------------------------------------
\echo 図書館スキーマ
CREATE SCHEMA library;
GRANT USAGE ON SCHEMA library TO library;
---------------------------------------------------------------------------------------------------
\echo 利用者マスタ
CREATE SEQUENCE SEQ_UserNo
START WITH 1
INCREMENT BY 1
NO CYCLE
CACHE 10
;
CREATE TABLE UserMT (
UserID SERIAL PRIMARY KEY,
UserNo TEXT UNIQUE NOT NULL DEFAULT 'U' || to_char(nextval('SEQ_UserNo'), 'FM00000000'),
Account TEXT UNIQUE NOT NULL,
Password TEXT NOT NULL,
ValidFlag BOOLEAN NOT NULL DEFAULT true,
-- true: 有効
-- false: 無効
RegistrationDate TIMESTAMP NOT NULL DEFAULT clock_timestamp(),
LastModifiedDate TIMESTAMP NOT NULL DEFAULT clock_timestamp(),
Affiliation TEXT,
Name TEXT,
NameKana TEXT,
Notes TEXT
);
---------------------------------------------------------------------------------------------------
\echo 利用者連絡先マスタ
CREATE TABLE UserContactMT (
UserContactID SERIAL PRIMARY KEY,
UserID INT NOT NULL,
TEL TEXT NOT NULL,
Email TEXT NOT NULL,
PostalCD TEXT NOT NULL,
Address TEXT NOT NULL,
FOREIGN KEY (UserID) REFERENCES UserMT(UserID) ON DELETE CASCADE
);
CREATE INDEX IDX_UserContactMT_Fkey1 ON UserContactMT(UserID, UserContactID);
---------------------------------------------------------------------------------------------------
\echo 職員マスタ
CREATE SEQUENCE SEQ_StaffNo
START WITH 1
INCREMENT BY 1
NO CYCLE
CACHE 10
;
CREATE TABLE StaffMT (
StaffID SERIAL PRIMARY KEY,
StaffNo TEXT UNIQUE NOT NULL DEFAULT 'S' || to_char(nextval('SEQ_StaffNo'), 'FM00000000'),
Affiliation TEXT,
Name TEXT,
NameKana TEXT
);
---------------------------------------------------------------------------------------------------
\echo 建屋マスタ
CREATE TABLE BuildingMT (
BuildingID SERIAL PRIMARY KEY,
BuildingCD TEXT UNIQUE NOT NULL,
BuildingName TEXT
);
---------------------------------------------------------------------------------------------------
\echo フロアマスタ
CREATE TABLE FloorMT (
FloorID SERIAL PRIMARY KEY,
BuildingID INT NOT NULL,
FloorCD TEXT UNIQUE NOT NULL,
FloorName TEXT,
FOREIGN KEY (BuildingID) REFERENCES BuildingMT(BuildingID) ON DELETE CASCADE
);
CREATE INDEX IDX_FloorMT_Fkey1 ON FloorMT(BuildingID, FloorID);
---------------------------------------------------------------------------------------------------
\echo 部屋マスタ
CREATE TABLE RoomMT (
RoomID SERIAL PRIMARY KEY,
FloorID INT NOT NULL,
RoomCD TEXT UNIQUE NOT NULL,
RoomName TEXT,
FOREIGN KEY (FloorID) REFERENCES FloorMT(FloorID) ON DELETE CASCADE
);
CREATE INDEX IDX_RoomMT_Fkey1 ON RoomMT(FloorID, RoomID);
---------------------------------------------------------------------------------------------------
\echo 書架マスタ
CREATE TABLE ShelfMT (
ShelfID SERIAL PRIMARY KEY,
RoomID INT NOT NULL,
ShelfNo TEXT UNIQUE NOT NULL,
FOREIGN KEY (RoomID) REFERENCES RoomMT(RoomID) ON DELETE CASCADE
);
CREATE INDEX IDX_ShelfMT_Fkey1 ON ShelfMT(RoomID, ShelfID);
---------------------------------------------------------------------------------------------------
\echo 日本十進分類マスタ
CREATE TABLE NDCMT (
NDCID SERIAL PRIMARY KEY,
ParentNDCID INT DEFAULT NULL,
NDCCD TEXT UNIQUE NOT NULL,
NDCClass CHAR(1) NOT NULL
CHECK(NDCClass IN ('1', '2', '3')),
-- 区分:
-- '1': 第一次
-- '2': 第二次
-- '3': 第三次
NDCName TEXT,
FOREIGN KEY (ParentNDCID) REFERENCES NDCMT(NDCID) ON DELETE CASCADE
);
CREATE INDEX IDX_NDCMT_Fkey1 ON NDCMT(ParentNDCID, NDCID);
---------------------------------------------------------------------------------------------------
\echo 蔵書マスタ
CREATE SEQUENCE SEQ_BookNo
START WITH 1
INCREMENT BY 1
NO CYCLE
CACHE 10
;
CREATE TABLE BookMT (
BookID SERIAL PRIMARY KEY,
ShelfID INT NOT NULL,
NDCID INT NOT NULL,
BookNo TEXT UNIQUE NOT NULL DEFAULT to_char(nextval('SEQ_BookNO'), 'FM0000000000'),
ISBN TEXT NOT NULL,
Title TEXT,
TitleKana TEXT,
Author TEXT,
AuthorKana TEXT,
FOREIGN KEY (NDCID) REFERENCES NDCMT(NDCID) ON DELETE CASCADE,
FOREIGN KEY (ShelfID) REFERENCES ShelfMT(ShelfID) ON DELETE CASCADE
);
CREATE INDEX IDX_BookMT_ISBN ON BookMT(ISBN, BookID);
CREATE INDEX IDX_BookMT_Fkey1 ON BookMT(ShelfID, BookID);
CREATE INDEX IDX_BookMT_Fkey2 ON BookMT(NDCID, BookID);
---------------------------------------------------------------------------------------------------
\echo 蔵書詳細マスタ
CREATE TABLE BookDetailMT (
BookID INT PRIMARY KEY,
Publisher TEXT,
PublicationYM NUMERIC(6, 0) NOT NULL,
NumOfPage TEXT NOT NULL,
OverView TEXT,
Notes TEXT,
FOREIGN KEY (BookID) REFERENCES BookMT(BookID) ON DELETE CASCADE
);
---------------------------------------------------------------------------------------------------
\echo 貸出トランザクション
CREATE SEQUENCE SEQ_LendingNo
START WITH 1
INCREMENT BY 1
NO CYCLE
CACHE 10
;
CREATE TABLE LendingTR (
LendingID SERIAL PRIMARY KEY,
LendingNo TEXT UNIQUE NOT NULL DEFAULT 'LN' || to_char(nextval('SEQ_LendingNo'), 'FM0000000000'),
UserID INT NOT NULL,
ReceptionStaffID INT NOT NULL,
LendingStatus CHAR(1) NOT NULL
CHECK(LendingStatus IN ('L', 'C')) DEFAULT 'L',
-- 'L': 貸し出し中(Lending)
-- 'C': 完了(Complete)
LendingDate TIMESTAMP NOT NULL DEFAULT clock_timestamp(),
FOREIGN KEY (UserID) REFERENCES UserMT(UserID) ON DELETE CASCADE,
FOREIGN KEY (ReceptionStaffID) REFERENCES StaffMT(StaffID) ON DELETE CASCADE
);
CREATE INDEX IDX_LendingTR_Fkey1 ON LendingTR(UserID, LendingID);
CREATE INDEX IDX_LendingTR_Fkey2 ON LendingTR(ReceptionStaffID, LendingID);
CREATE INDEX IDX_LendingTR_Search1 ON LendingTR(LendingDate, LendingID);
---------------------------------------------------------------------------------------------------
\echo 貸出対象蔵書トランザクション
CREATE TABLE LendingItemTR (
LendingID INT NOT NULL,
BookID INT NOT NULL,
ReturnDueYMD NUMERIC(8, 0) NOT NULL,
PRIMARY KEY (LendingID, BookID),
FOREIGN KEY (LendingID) REFERENCES LendingTR(LendingID) ON DELETE CASCADE,
FOREIGN KEY (BookID) REFERENCES BookMT(BookID) ON DELETE CASCADE
);
CREATE INDEX IDX_LendingItemTR_Fkey1 ON LendingItemTR(BookID, LendingID);
---------------------------------------------------------------------------------------------------
\echo 返却トランザクション
CREATE SEQUENCE SEQ_ReturnNo
START WITH 1
INCREMENT BY 1
NO CYCLE
CACHE 10
;
CREATE TABLE ReturnTR (
ReturnID SERIAL PRIMARY KEY,
ReturnNo TEXT UNIQUE NOT NULL DEFAULT 'RT' || to_char(nextval('SEQ_ReturnNo'), 'FM0000000000'),
LendingID INT NOT NULL,
ReceptionStaffID INT NOT NULL,
ReturnDate TIMESTAMP NOT NULL DEFAULT clock_timestamp(),
FOREIGN KEY (LendingID) REFERENCES LendingTR(LendingID) ON DELETE CASCADE,
FOREIGN KEY (ReceptionStaffID) REFERENCES StaffMT(StaffID) ON DELETE CASCADE
);
CREATE INDEX IDX_ReturnTR_Fkey1 ON ReturnTR(LendingID, ReturnID);
CREATE INDEX IDX_ReturnTR_Fkey2 ON ReturnTR(ReceptionStaffID, ReturnID);
CREATE INDEX IDX_ReturnTR_Search1 ON ReturnTR(ReturnDate, ReturnID);
---------------------------------------------------------------------------------------------------
\echo 返却対象蔵書トランザクション
CREATE TABLE ReturnItemTR (
ReturnID INT NOT NULL,
BookID INT NOT NULL,
PRIMARY KEY (ReturnID, BookID),
FOREIGN KEY (ReturnID) REFERENCES ReturnTR(ReturnID) ON DELETE CASCADE,
FOREIGN KEY (BookID) REFERENCES BookMT(BookID) ON DELETE CASCADE
);
CREATE INDEX IDX_ReturnItemTR_Fkey1 ON ReturnItemTR(BookID, ReturnID);
---------------------------------------------------------------------------------------------------
\echo 督促トランザクション
CREATE SEQUENCE SEQ_RemindNo
START WITH 1
INCREMENT BY 1
NO CYCLE
CACHE 10
;
CREATE TABLE ReminderTR (
ReminderID SERIAL PRIMARY KEY,
ReminderNo TEXT NOT NULL DEFAULT 'RM' || to_char(nextval('SEQ_RemindNo'), 'FM0000000000'),
LendingID INT NOT NULL,
StaffID INT NOT NULL,
ContactID INT NOT NULL,
ReminderDate TIMESTAMP NOT NULL DEFAULT clock_timestamp(),
ReminderType CHAR(1) NOT NULL
CHECK(ReminderType IN ('M', 'T', 'P')),
-- 'M': メール(Mail)
-- 'T': TEL
-- 'P': 郵便(Postal)
FOREIGN KEY (LendingID) REFERENCES LendingTR(LendingID) ON DELETE CASCADE,
FOREIGN KEY (StaffID) REFERENCES StaffMT(StaffID) ON DELETE CASCADE,
FOREIGN KEY (ContactID) REFERENCES UserContactMT(UserContactID) ON DELETE CASCADE
);
CREATE INDEX IDX_ReminderTR_Fkey1 ON ReminderTR(LendingID, ReminderID);
CREATE INDEX IDX_ReminderTR_Fkey2 ON ReminderTR(StaffID, ReminderID);
CREATE INDEX IDX_ReminderTR_Fkey3 ON ReminderTR(ContactID, ReminderID);
CREATE INDEX IDX_ReminderTR_Search1 ON ReminderTR(ReminderDate, ReminderID);
---------------------------------------------------------------------------------------------------
\echo 分類別貸出状況サマリ
CREATE TABLE CategoryLendingSM (
NDCID INT NOT NULL,
YM NUMERIC(6, 0) NOT NULL,
LendingCount INT NOT NULL DEFAULT 0,
ReturnCount INT NOT NULL DEFAULT 0,
ReminderCount INT NOT NULL DEFAULT 0,
LendingBookCount INT NOT NULL DEFAULT 0,
ReturnBookCount INT NOT NULL DEFAULT 0,
PRIMARY KEY (NDCID, YM),
FOREIGN KEY (NDCID) REFERENCES NDCMT(NDCID) ON DELETE CASCADE
);
---------------------------------------------------------------------------------------------------
\echo 利用者別貸出状況サマリ
CREATE TABLE UserLendingSM (
UserID INT NOT NULL,
YM NUMERIC(6, 0) NOT NULL,
LendingCount INT NOT NULL DEFAULT 0,
ReturnCount INT NOT NULL DEFAULT 0,
ReminderCount INT NOT NULL DEFAULT 0,
LendingBookCount INT NOT NULL DEFAULT 0,
ReturnBookCount INT NOT NULL DEFAULT 0,
PRIMARY KEY(UserID, YM),
FOREIGN KEY (UserID) REFERENCES UserMT(UserID) ON DELETE CASCADE
);
---------------------------------------------------------------------------------------------------性能測定2
正規形2を含めた測定は、以下の結果となりました。
- 正規形2における分類別貸出状況サマリの登録時間は改善され、正規形1の半分程度の時間で処理できるようになった。
- 正規形2では分類別貸出状況サマリの性能だけでなく、貸出・返却・督促トランザクションの登録に対する性能も改善した。
- 正規形2における蔵書・蔵書詳細マスタの登録時間は、若干増加した。
正規形2（緑のグラフ）では、蔵書マスタ登録の更新性能が若干犠牲となっていますが、全体的な性能は良好になっています。
チューニングによる改善
正規形2である程度性能を改善できましたが、分類別貸出状況サマリの処理時間は、まだ少し非正規形2に負けています。
バッチ処理と考えればこの程度の性能でも問題ないレベルですが、非正規形に負けたままでは残念なので、別の手段による改善も試してみることにします。
正規形1では、分類別貸出状況サマリとして「日本十進分類ID」を取得するためだけに、蔵書マスタの読み込みが発生していました。
これをチューニングで改善する方法として、インデックスの追加が考えられます。
そこで正規形1に対し、以下のインデックスを追加しました。
CREATE INDEX IDX_BookMT_TEST ON BookMT(BookID, NDCID); このインデックスは、蔵書IDに対応する日本十進分類IDのペアを表したものになります。
このインデックスを追加した後で分類別貸出状況サマリの処理を測定し直すと、以下のようになりました。
正規形1のままでも、非正規形2より十分速い結果となりました。
この方法で性能が改善された理由は、オプティマイザの動作によるものです。
追加したインデックスから「日本十進分類ID」を取得できるため、蔵書マスタのレコードを読み込む必要がなくなり、I/Oが大幅に改善されました。
この改善方法は、読み出したいカラムがインデックスの中に含まれることが条件となりますので、「分類名称」のようなカラムが必要になる場合は適用できません。
サマリといえど定義するのは最小限のカラムに留めておき、必要に応じてマスタをJOINするという考え方が重要です。
また、インデックスの追加により蔵書マスタに対する更新性能は低下しますので、実際にこのチューニングを施すかどうかは、更新処理と参照処理の頻度に基づいて検討すべきです。
以上の結果から、正規形で性能が出せないケースでも、設計やチューニングによって改善する方法があることを理解して頂けたと思います。
性能測定3
データ登録を主体とした性能測定だけでは少し物足りないので、最後に単体のSELECTにおける参照性能も測定してみます。
以下の検索を行うSQLを測定しました。
- 指定した日に発生した1日分の貸出・返却の明細(10,000件)を出力する。
- 明細として、以下の情報を出力する。
- 利用者の情報（利用者番号・氏名・所属）
- 受付職員の情報（職員番号・氏名・所属）
- 貸出状態・貸出日時
- 貸出対象の蔵書の以下の情報
- 蔵書管理番号・ISBN番号・タイトル・著者名
- 日本十進分類番号とその名称
- 蔵書の収蔵場所に関する情報（建屋・フロア・部屋・書架）
- 返却日時
- 明細は貸出日時・貸出ID・蔵書IDで昇順ソートする。
- 明細として、以下の情報を出力する。
- explain analyzeコマンドでExecution Timeを測定する。
- 測定にはばらつきが出るため、10回実行した時の最小値・最大値・平均値・中央値を測定する。
- できるだけキャッシュの影響を避けるため、測定ごとに対象の日付を変更する。
測定結果は以下のようになりました。
EXPLAIN ANALYZE
SELECT UM.UserNo AS "利用者番号",
UM.Name AS "利用者氏名",
UM.Affiliation AS "利用者所属",
STM.StaffNo AS "受付職員番号",
STM.Name AS "職員氏名",
STM.Affiliation AS "職員所属",
LT.LendingStatus AS "貸出状態",
LT.LendingDate AS "貸出日時",
BM.BookNo AS "蔵書管理番号",
BM.ISBN AS "ISBN",
BM.Title AS "タイトル",
BM.Author AS "著者名",
NDM.NDCCD AS "日本十進分類番号",
NDM.NDCName AS "分類名",
BLM.BuildingName AS "所蔵建屋",
FLM.FloorName AS "所蔵フロア",
RMM.RoomName AS "所属部屋",
SHM.ShelfNo AS "所蔵書架",
RT.ReturnDate AS "返却日時"
FROM LendingTR LT
INNER JOIN UserMT UM ON (
UM.UserID = LT.UserID
)
INNER JOIN LendingItemTR LIT ON (
LIT.LendingID = LT.LendingID
)
INNER JOIN BookMT BM ON (
BM.BookID = LIT.BookID
)
INNER JOIN NDCMT NDM ON (
NDM.NDCID = BM.NDCID
)
INNER JOIN ShelfMT SHM ON (
SHM.ShelfID = BM.ShelfID
)
INNER JOIN RoomMT RMM ON (
RMM.RoomID = SHM.RoomID
)
INNER JOIN FloorMT FLM ON (
FLM.FloorID = RMM.FloorID
)
INNER JOIN BuildingMT BLM ON (
BLM.BuildingID = FLM.BuildingID
)
INNER JOIN StaffMT STM ON (
STM.StaffID = LT.ReceptionStaffID
)
LEFT OUTER JOIN ReturnTR RT ON (
RT.LendingID = LT.LendingID
)
WHERE LendingDate BETWEEN '2023-01-01'::TIMESTAMP AND '2023-01-02'::TIMESTAMP
ORDER BY LT.LendingDate, LT.LendingID, BM.BookID
;Gather Merge (cost=43397.79..44779.86 rows=12018 width=754) (actual time=1530.404..1545.934 rows=10000 loops=1)
Workers Planned: 1
Workers Launched: 1
-> Sort (cost=42397.78..42427.83 rows=12018 width=754) (actual time=1478.864..1479.105 rows=5000 loops=2)
Sort Key: lt.lendingdate, lt.lendingid, bm.bookid
Sort Method: quicksort Memory: 4112kB
Worker 0: Sort Method: quicksort Memory: 4014kB
-> Nested Loop Left Join (cost=201.38..41583.39 rows=12018 width=754) (actual time=12.398..1452.627 rows=5000 loops=2)
-> Hash Join (cost=200.94..36398.23 rows=12018 width=746) (actual time=11.835..1437.508 rows=5000 loops=2)
Hash Cond: (lt.receptionstaffid = stm.staffid)
-> Hash Join (cost=195.69..36360.10 rows=12018 width=592) (actual time=11.177..1433.781 rows=5000 loops=2)
Hash Cond: (flm.buildingid = blm.buildingid)
-> Hash Join (cost=194.58..36300.70 rows=12018 width=586) (actual time=10.526..1430.278 rows=5000 loops=2)
Hash Cond: (rmm.floorid = flm.floorid)
-> Hash Join (cost=192.46..36264.35 rows=12018 width=577) (actual time=10.002..1426.947 rows=5000 loops=2)
Hash Cond: (shm.roomid = rmm.roomid)
-> Hash Join (cost=183.83..36223.65 rows=12018 width=560) (actual time=9.357..1423.462 rows=5000 loops=2)
Hash Cond: (bm.shelfid = shm.shelfid)
-> Nested Loop (cost=39.33..36047.58 rows=12018 width=546) (actual time=5.925..1415.488 rows=5000 loops=2)
-> Nested Loop (cost=39.03..35087.46 rows=12018 width=521) (actual time=5.864..1402.277 rows=5000 loops=2)
-> Nested Loop (cost=38.59..6827.89 rows=12018 width=183) (actual time=2.079..28.060 rows=5000 loops=2)
-> Nested Loop (cost=38.16..4619.54 rows=1202 width=179) (actual time=1.671..17.655 rows=500 loops=2)
-> Parallel Bitmap Heap Scan on lendingtr lt (cost=37.87..1933.23 rows=1202 width=22) (actual time=1.617..2.671 rows=500 loops=2)
Recheck Cond: ((lendingdate >= '2023-01-10 00:00:00'::timestamp without time zone) AND (lendingdate <= '2023-01-11 00:00:00'::timestamp without time zone))
Heap Blocks: exact=18
-> Bitmap Index Scan on idx_lendingtr_search1 (cost=0.00..37.35 rows=2043 width=0) (actual time=3.085..3.085 rows=1000 loops=1)
Index Cond: ((lendingdate >= '2023-01-10 00:00:00'::timestamp without time zone) AND (lendingdate <= '2023-01-11 00:00:00'::timestamp without time zone))
-> Index Scan using usermt_pkey on usermt um (cost=0.29..2.23 rows=1 width=165) (actual time=0.026..0.026 rows=1 loops=1000)
Index Cond: (userid = lt.userid)
-> Index Only Scan using lendingitemtr_pkey on lendingitemtr lit (cost=0.43..1.71 rows=13 width=8) (actual time=0.012..0.017 rows=10 loops=1000)
Index Cond: (lendingid = lt.lendingid)
Heap Fetches: 0
-> Index Scan using bookmt_pkey on bookmt bm (cost=0.44..2.35 rows=1 width=342) (actual time=0.274..0.274 rows=1 loops=10000)
Index Cond: (bookid = lit.bookid)
-> Memoize (cost=0.30..2.20 rows=1 width=33) (actual time=0.002..0.002 rows=1 loops=10000)
Cache Key: bm.ndcid
Cache Mode: logical
Hits: 4830 Misses: 240 Evictions: 0 Overflows: 0 Memory Usage: 32kB
Worker 0: Hits: 4703 Misses: 227 Evictions: 0 Overflows: 0 Memory Usage: 31kB
-> Index Scan using ndcmt_pkey on ndcmt ndm (cost=0.29..2.19 rows=1 width=33) (actual time=0.015..0.015 rows=1 loops=467)
Index Cond: (ndcid = bm.ndcid)
-> Hash (cost=82.00..82.00 rows=5000 width=22) (actual time=3.382..3.383 rows=5000 loops=2)
Buckets: 8192 Batches: 1 Memory Usage: 338kB
-> Seq Scan on shelfmt shm (cost=0.00..82.00 rows=5000 width=22) (actual time=0.610..1.955 rows=5000 loops=2)
-> Hash (cost=5.50..5.50 rows=250 width=25) (actual time=0.627..0.630 rows=250 loops=2)
Buckets: 1024 Batches: 1 Memory Usage: 23kB
-> Seq Scan on roommt rmm (cost=0.00..5.50 rows=250 width=25) (actual time=0.497..0.569 rows=250 loops=2)
-> Hash (cost=1.50..1.50 rows=50 width=17) (actual time=0.514..0.514 rows=50 loops=2)
Buckets: 1024 Batches: 1 Memory Usage: 11kB
-> Seq Scan on floormt flm (cost=0.00..1.50 rows=50 width=17) (actual time=0.463..0.468 rows=50 loops=2)
-> Hash (cost=1.05..1.05 rows=5 width=14) (actual time=0.634..0.634 rows=5 loops=2)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on buildingmt blm (cost=0.00..1.05 rows=5 width=14) (actual time=0.579..0.581 rows=5 loops=2)
-> Hash (cost=4.00..4.00 rows=100 width=162) (actual time=0.641..0.641 rows=100 loops=2)
Buckets: 1024 Batches: 1 Memory Usage: 28kB
-> Seq Scan on staffmt stm (cost=0.00..4.00 rows=100 width=162) (actual time=0.571..0.605 rows=100 loops=2)
-> Memoize (cost=0.43..2.40 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=10000)
Cache Key: lt.lendingid
Cache Mode: logical
Hits: 4563 Misses: 507 Evictions: 0 Overflows: 0 Memory Usage: 55kB
Worker 0: Hits: 4437 Misses: 493 Evictions: 0 Overflows: 0 Memory Usage: 53kB
-> Index Scan using idx_returntr_fkey1 on returntr rt (cost=0.42..2.39 rows=1 width=12) (actual time=0.016..0.017 rows=1 loops=1000)
Index Cond: (lendingid = lt.lendingid)
Planning Time: 8.097 ms
Execution Time: 1547.495 msEXPLAIN ANALYZE
SELECT UM.UserNo AS "利用者番号",
UM.Name AS "利用者氏名",
UM.Affiliation AS "利用者所属",
STM.StaffNo AS "受付職員番号",
STM.Name AS "職員氏名",
STM.Affiliation AS "職員所属",
LT.LendingStatus AS "貸出状態",
LT.LendingDate AS "貸出日時",
BM.BookNo AS "蔵書管理番号",
BM.ISBN AS "ISBN",
BM.Title AS "タイトル",
BM.Author AS "著者名",
NDM.NDCCD AS "日本十進分類番号",
NDM.NDCName AS "分類名",
BLM.BuildingName AS "所蔵建屋",
FLM.FloorName AS "所蔵フロア",
RMM.RoomName AS "所属部屋",
SHM.ShelfNo AS "所蔵書架",
RT.ReturnDate AS "返却日時"
FROM LendingTR LT
INNER JOIN UserMT UM ON (
UM.UserID = LT.UserID
)
INNER JOIN LendingItemTR LIT ON (
LIT.LendingID = LT.LendingID
)
INNER JOIN BookMT BM ON (
BM.BookID = LIT.BookID
)
INNER JOIN NDCMT NDM ON (
NDM.NDCID = BM.NDCID
)
INNER JOIN ShelfMT SHM ON (
SHM.ShelfID = BM.ShelfID
)
INNER JOIN RoomMT RMM ON (
RMM.RoomID = SHM.RoomID
)
INNER JOIN FloorMT FLM ON (
FLM.FloorID = RMM.FloorID
)
INNER JOIN BuildingMT BLM ON (
BLM.BuildingID = FLM.BuildingID
)
INNER JOIN StaffMT STM ON (
STM.StaffID = LT.ReceptionStaffID
)
LEFT OUTER JOIN ReturnTR RT ON (
RT.LendingID = LT.LendingID
)
WHERE LendingDate BETWEEN '2023-01-01'::TIMESTAMP AND '2023-01-02'::TIMESTAMP
ORDER BY LT.LendingDate, LT.LendingID, BM.BookID
;Gather Merge (cost=36404.78..37697.50 rows=11241 width=754) (actual time=1329.746..1344.558 rows=10000 loops=1)
Workers Planned: 1
Workers Launched: 1
-> Sort (cost=35404.77..35432.88 rows=11241 width=754) (actual time=1272.492..1272.828 rows=5000 loops=2)
Sort Key: lt.lendingdate, lt.lendingid, bm.bookid
Sort Method: quicksort Memory: 4084kB
Worker 0: Sort Method: quicksort Memory: 4044kB
-> Nested Loop Left Join (cost=197.83..34648.45 rows=11241 width=754) (actual time=15.917..1239.235 rows=5000 loops=2)
-> Hash Join (cost=197.39..29964.70 rows=11241 width=746) (actual time=15.443..1224.342 rows=5000 loops=2)
Hash Cond: (lt.receptionstaffid = stm.staffid)
-> Hash Join (cost=192.14..29928.70 rows=11241 width=592) (actual time=14.669..1220.462 rows=5000 loops=2)
Hash Cond: (flm.buildingid = blm.buildingid)
-> Hash Join (cost=191.03..29873.07 rows=11241 width=586) (actual time=14.073..1217.056 rows=5000 loops=2)
Hash Cond: (rmm.floorid = flm.floorid)
-> Hash Join (cost=188.90..29838.93 rows=11241 width=577) (actual time=13.658..1213.956 rows=5000 loops=2)
Hash Cond: (shm.roomid = rmm.roomid)
-> Hash Join (cost=180.28..29800.30 rows=11241 width=560) (actual time=12.982..1210.307 rows=5000 loops=2)
Hash Cond: (bm.shelfid = shm.shelfid)
-> Nested Loop (cost=35.78..29626.27 rows=11241 width=546) (actual time=10.812..1203.871 rows=5000 loops=2)
-> Nested Loop (cost=35.48..28797.39 rows=11241 width=521) (actual time=10.738..1191.375 rows=5000 loops=2)
-> Nested Loop (cost=35.04..6213.67 rows=11241 width=183) (actual time=6.238..40.473 rows=5000 loops=2)
-> Nested Loop (cost=34.61..4144.95 rows=1124 width=179) (actual time=4.026..20.252 rows=500 loops=2)
-> Parallel Bitmap Heap Scan on lendingtr lt (cost=34.31..1857.47 rows=1124 width=22) (actual time=3.961..4.941 rows=500 loops=2)
Recheck Cond: ((lendingdate >= '2023-01-05 00:00:00'::timestamp without time zone) AND (lendingdate <= '2023-01-06 00:00:00'::timestamp without time zone))
Heap Blocks: exact=15
-> Bitmap Index Scan on idx_lendingtr_search1 (cost=0.00..33.83 rows=1911 width=0) (actual time=7.737..7.738 rows=1000 loops=1)
Index Cond: ((lendingdate >= '2023-01-05 00:00:00'::timestamp without time zone) AND (lendingdate <= '2023-01-06 00:00:00'::timestamp without time zone))
-> Index Scan using usermt_pkey on usermt um (cost=0.29..2.04 rows=1 width=165) (actual time=0.028..0.028 rows=1 loops=1000)
Index Cond: (userid = lt.userid)
-> Index Only Scan using lendingitemtr_pkey on lendingitemtr lit (cost=0.43..1.71 rows=13 width=8) (actual time=0.031..0.037 rows=10 loops=1000)
Index Cond: (lendingid = lt.lendingid)
Heap Fetches: 0
-> Index Scan using bookmt_pkey on bookmt bm (cost=0.44..2.01 rows=1 width=342) (actual time=0.229..0.229 rows=1 loops=10000)
Index Cond: (bookid = lit.bookid)
-> Memoize (cost=0.30..1.83 rows=1 width=33) (actual time=0.002..0.002 rows=1 loops=10000)
Cache Key: bm.ndcid
Cache Mode: logical
Hits: 4787 Misses: 233 Evictions: 0 Overflows: 0 Memory Usage: 31kB
Worker 0: Hits: 4741 Misses: 239 Evictions: 0 Overflows: 0 Memory Usage: 32kB
-> Index Scan using ndcmt_pkey on ndcmt ndm (cost=0.29..1.82 rows=1 width=33) (actual time=0.015..0.015 rows=1 loops=472)
Index Cond: (ndcid = bm.ndcid)
-> Hash (cost=82.00..82.00 rows=5000 width=22) (actual time=2.107..2.108 rows=5000 loops=2)
Buckets: 8192 Batches: 1 Memory Usage: 338kB
-> Seq Scan on shelfmt shm (cost=0.00..82.00 rows=5000 width=22) (actual time=0.334..1.142 rows=5000 loops=2)
-> Hash (cost=5.50..5.50 rows=250 width=25) (actual time=0.657..0.658 rows=250 loops=2)
Buckets: 1024 Batches: 1 Memory Usage: 23kB
-> Seq Scan on roommt rmm (cost=0.00..5.50 rows=250 width=25) (actual time=0.466..0.558 rows=250 loops=2)
-> Hash (cost=1.50..1.50 rows=50 width=17) (actual time=0.399..0.400 rows=50 loops=2)
Buckets: 1024 Batches: 1 Memory Usage: 11kB
-> Seq Scan on floormt flm (cost=0.00..1.50 rows=50 width=17) (actual time=0.360..0.370 rows=50 loops=2)
-> Hash (cost=1.05..1.05 rows=5 width=14) (actual time=0.566..0.567 rows=5 loops=2)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on buildingmt blm (cost=0.00..1.05 rows=5 width=14) (actual time=0.551..0.553 rows=5 loops=2)
-> Hash (cost=4.00..4.00 rows=100 width=162) (actual time=0.761..0.762 rows=100 loops=2)
Buckets: 1024 Batches: 1 Memory Usage: 28kB
-> Seq Scan on staffmt stm (cost=0.00..4.00 rows=100 width=162) (actual time=0.636..0.687 rows=100 loops=2)
-> Memoize (cost=0.43..2.31 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=10000)
Cache Key: lt.lendingid
Cache Mode: logical
Hits: 4518 Misses: 502 Evictions: 0 Overflows: 0 Memory Usage: 54kB
Worker 0: Hits: 4482 Misses: 498 Evictions: 0 Overflows: 0 Memory Usage: 54kB
-> Index Scan using idx_returntr_fkey1 on returntr rt (cost=0.42..2.30 rows=1 width=12) (actual time=0.016..0.016 rows=1 loops=1000)
Index Cond: (lendingid = lt.lendingid)
Planning Time: 4.365 ms
Execution Time: 1346.157 msEXPLAIN ANALYZE
WITH L AS (
SELECT LT.LendingNo,
LT.UserNo,
LT.UserName,
LT.UserAffiliation,
LT.ReceptionStaffNo,
LT.ReceptionStaffName,
LT.ReceptionStaffAffiliation,
LT.LendingStatus,
LT.LendingDate,
LT.BookNo_1 AS BookNo,
LT.Title_1 AS Title
FROM LendingTR LT
WHERE LendingDate BETWEEN '2023-01-01'::TIMESTAMP AND '2023-01-02'
UNION ALL
SELECT LT.LendingNo,
LT.UserNo,
LT.UserName,
LT.UserAffiliation,
LT.ReceptionStaffNo,
LT.ReceptionStaffName,
LT.ReceptionStaffAffiliation,
LT.LendingStatus,
LT.LendingDate,
LT.BookNo_2 AS BookNo,
LT.Title_2 AS Title
FROM LendingTR LT
WHERE LendingDate BETWEEN '2023-01-01'::TIMESTAMP AND '2023-01-02'
UNION ALL
SELECT LT.LendingNo,
LT.UserNo,
LT.UserName,
LT.UserAffiliation,
LT.ReceptionStaffNo,
LT.ReceptionStaffName,
LT.ReceptionStaffAffiliation,
LT.LendingStatus,
LT.LendingDate,
LT.BookNo_3 AS BookNo,
LT.Title_3 AS Title
FROM LendingTR LT
WHERE LendingDate BETWEEN '2023-01-01'::TIMESTAMP AND '2023-01-02'
UNION ALL
SELECT LT.LendingNo,
LT.UserNo,
LT.UserName,
LT.UserAffiliation,
LT.ReceptionStaffNo,
LT.ReceptionStaffName,
LT.ReceptionStaffAffiliation,
LT.LendingStatus,
LT.LendingDate,
LT.BookNo_4 AS BookNo,
LT.Title_4 AS Title
FROM LendingTR LT
WHERE LendingDate BETWEEN '2023-01-01'::TIMESTAMP AND '2023-01-02'
UNION ALL
SELECT LT.LendingNo,
LT.UserNo,
LT.UserName,
LT.UserAffiliation,
LT.ReceptionStaffNo,
LT.ReceptionStaffName,
LT.ReceptionStaffAffiliation,
LT.LendingStatus,
LT.LendingDate,
LT.BookNo_5 AS BookNo,
LT.Title_5 AS Title
FROM LendingTR LT
WHERE LendingDate BETWEEN '2023-01-01'::TIMESTAMP AND '2023-01-02'
UNION ALL
SELECT LT.LendingNo,
LT.UserNo,
LT.UserName,
LT.UserAffiliation,
LT.ReceptionStaffNo,
LT.ReceptionStaffName,
LT.ReceptionStaffAffiliation,
LT.LendingStatus,
LT.LendingDate,
LT.BookNo_6 AS BookNo,
LT.Title_6 AS Title
FROM LendingTR LT
WHERE LendingDate BETWEEN '2023-01-01'::TIMESTAMP AND '2023-01-02'
UNION ALL
SELECT LT.LendingNo,
LT.UserNo,
LT.UserName,
LT.UserAffiliation,
LT.ReceptionStaffNo,
LT.ReceptionStaffName,
LT.ReceptionStaffAffiliation,
LT.LendingStatus,
LT.LendingDate,
LT.BookNo_7 AS BookNo,
LT.Title_7 AS Title
FROM LendingTR LT
WHERE LendingDate BETWEEN '2023-01-01'::TIMESTAMP AND '2023-01-02'
UNION ALL
SELECT LT.LendingNo,
LT.UserNo,
LT.UserName,
LT.UserAffiliation,
LT.ReceptionStaffNo,
LT.ReceptionStaffName,
LT.ReceptionStaffAffiliation,
LT.LendingStatus,
LT.LendingDate,
LT.BookNo_8 AS BookNo,
LT.Title_8 AS Title
FROM LendingTR LT
WHERE LendingDate BETWEEN '2023-01-01'::TIMESTAMP AND '2023-01-02'
UNION ALL
SELECT LT.LendingNo,
LT.UserNo,
LT.UserName,
LT.UserAffiliation,
LT.ReceptionStaffNo,
LT.ReceptionStaffName,
LT.ReceptionStaffAffiliation,
LT.LendingStatus,
LT.LendingDate,
LT.BookNo_9 AS BookNo,
LT.Title_9 AS Title
FROM LendingTR LT
WHERE LendingDate BETWEEN '2023-01-01'::TIMESTAMP AND '2023-01-02'
UNION ALL
SELECT LT.LendingNo,
LT.UserNo,
LT.UserName,
LT.UserAffiliation,
LT.ReceptionStaffNo,
LT.ReceptionStaffName,
LT.ReceptionStaffAffiliation,
LT.LendingStatus,
LT.LendingDate,
LT.BookNo_10 AS BookNo,
LT.Title_10 AS Title
FROM LendingTR LT
WHERE LendingDate BETWEEN '2023-01-01'::TIMESTAMP AND '2023-01-02'
)
SELECT D."利用者番号",
D."利用者氏名",
D."利用者所属",
D."受付職員番号",
D."職員氏名",
D."職員所属",
D."貸出状態",
D."貸出日時",
D."蔵書管理番号",
D."ISBN",
D."タイトル",
D."著者名",
D."日本十進分類番号",
D."分類名",
D."所蔵建屋",
D."所蔵フロア",
D."所属部屋",
D."所蔵書架",
D."返却日時"
FROM (
SELECT L.LendingNo,
L.UserNo AS "利用者番号",
L.UserName AS "利用者氏名",
L.UserAffiliation AS "利用者所属",
L.ReceptionStaffNo AS "受付職員番号",
L.ReceptionStaffName AS "職員氏名",
L.ReceptionStaffAffiliation AS "職員所属",
L.LendingStatus AS "貸出状態",
L.LendingDate AS "貸出日時",
L.BookNo AS "蔵書管理番号",
BM.ISBN AS "ISBN",
L.Title AS "タイトル",
BM.Author AS "著者名",
NDM.NDCCD AS "日本十進分類番号",
NDM.NDCName AS "分類名",
BM.BuildingName AS "所蔵建屋",
BM.FloorName AS "所蔵フロア",
BM.RoomName AS "所属部屋",
BM.ShelfCD AS "所蔵書架",
RT.ReturnDate AS "返却日時"
FROM L
INNER JOIN BookMT BM ON (
BM.BookNo = L.BookNo
)
INNER JOIN NDCMT NDM ON (
NDM.NDCCD = BM.NDCCD
)
LEFT OUTER JOIN ReturnTR RT ON (
RT.LendingNo = L.LendingNo
)
) D
ORDER BY D."貸出日時", D.LendingNo, D."蔵書管理番号"
;Gather Merge (cost=52250.77..54051.53 rows=15434 width=725) (actual time=3227.544..3245.207 rows=10000 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=51250.75..51270.04 rows=7717 width=725) (actual time=3170.331..3170.544 rows=3333 loops=3)
Sort Key: "*SELECT* 1".lendingdate, "*SELECT* 1".lendingno, "*SELECT* 1".bookno
Sort Method: quicksort Memory: 3226kB
Worker 0: Sort Method: quicksort Memory: 2256kB
Worker 1: Sort Method: quicksort Memory: 2295kB
-> Nested Loop Left Join (cost=1.72..50752.47 rows=7717 width=725) (actual time=12.168..3148.407 rows=3333 loops=3)
-> Nested Loop (cost=1.29..31722.36 rows=7717 width=717) (actual time=11.756..2745.859 rows=3333 loops=3)
-> Nested Loop (cost=0.99..30859.90 rows=7717 width=694) (actual time=11.636..2729.346 rows=3333 loops=3)
-> Parallel Append (cost=0.42..10105.68 rows=7710 width=485) (actual time=0.448..594.412 rows=3333 loops=3)
-> Subquery Scan on "*SELECT* 1" (cost=0.42..2013.42 rows=1852 width=371) (actual time=0.058..581.885 rows=1000 loops=1)
-> Index Scan using idx_lendingtr_search13 on lendingtr lt (cost=0.42..1994.90 rows=1852 width=371) (actual time=0.057..581.138 rows=1000 loops=1)
Index Cond: ((lendingdate >= '2023-01-07 00:00:00'::timestamp without time zone) AND (lendingdate <= '2023-01-08 00:00:00'::timestamp without time zone))
-> Subquery Scan on "*SELECT* 2" (cost=0.42..2013.42 rows=1852 width=371) (actual time=0.056..582.598 rows=1000 loops=1)
-> Index Scan using idx_lendingtr_search13 on lendingtr lt_1 (cost=0.42..1994.90 rows=1852 width=371) (actual time=0.055..581.746 rows=1000 loops=1)
Index Cond: ((lendingdate >= '2023-01-07 00:00:00'::timestamp without time zone) AND (lendingdate <= '2023-01-08 00:00:00'::timestamp without time zone))
-> Subquery Scan on "*SELECT* 3" (cost=0.42..2013.42 rows=1852 width=480) (actual time=0.021..2.276 rows=1000 loops=1)
-> Index Scan using idx_lendingtr_search13 on lendingtr lt_2 (cost=0.42..1994.90 rows=1852 width=480) (actual time=0.020..2.062 rows=1000 loops=1)
Index Cond: ((lendingdate >= '2023-01-07 00:00:00'::timestamp without time zone) AND (lendingdate <= '2023-01-08 00:00:00'::timestamp without time zone))
-> Subquery Scan on "*SELECT* 4" (cost=0.42..2013.42 rows=1852 width=518) (actual time=0.030..1.972 rows=1000 loops=1)
-> Index Scan using idx_lendingtr_search13 on lendingtr lt_3 (cost=0.42..1994.90 rows=1852 width=518) (actual time=0.028..1.690 rows=1000 loops=1)
Index Cond: ((lendingdate >= '2023-01-07 00:00:00'::timestamp without time zone) AND (lendingdate <= '2023-01-08 00:00:00'::timestamp without time zone))
-> Subquery Scan on "*SELECT* 5" (cost=0.42..2013.42 rows=1852 width=519) (actual time=0.015..2.149 rows=1000 loops=1)
-> Index Scan using idx_lendingtr_search13 on lendingtr lt_4 (cost=0.42..1994.90 rows=1852 width=519) (actual time=0.014..1.905 rows=1000 loops=1)
Index Cond: ((lendingdate >= '2023-01-07 00:00:00'::timestamp without time zone) AND (lendingdate <= '2023-01-08 00:00:00'::timestamp without time zone))
-> Subquery Scan on "*SELECT* 6" (cost=0.42..2013.42 rows=1852 width=519) (actual time=0.012..2.048 rows=1000 loops=1)
-> Index Scan using idx_lendingtr_search13 on lendingtr lt_5 (cost=0.42..1994.90 rows=1852 width=519) (actual time=0.012..1.789 rows=1000 loops=1)
Index Cond: ((lendingdate >= '2023-01-07 00:00:00'::timestamp without time zone) AND (lendingdate <= '2023-01-08 00:00:00'::timestamp without time zone))
-> Subquery Scan on "*SELECT* 7" (cost=0.42..2013.42 rows=1852 width=519) (actual time=0.024..1.603 rows=1000 loops=1)
-> Index Scan using idx_lendingtr_search13 on lendingtr lt_6 (cost=0.42..1994.90 rows=1852 width=519) (actual time=0.024..1.471 rows=1000 loops=1)
Index Cond: ((lendingdate >= '2023-01-07 00:00:00'::timestamp without time zone) AND (lendingdate <= '2023-01-08 00:00:00'::timestamp without time zone))
-> Subquery Scan on "*SELECT* 8" (cost=0.42..2013.42 rows=1852 width=519) (actual time=0.017..2.505 rows=1000 loops=1)
-> Index Scan using idx_lendingtr_search13 on lendingtr lt_7 (cost=0.42..1994.90 rows=1852 width=519) (actual time=0.016..2.175 rows=1000 loops=1)
Index Cond: ((lendingdate >= '2023-01-07 00:00:00'::timestamp without time zone) AND (lendingdate <= '2023-01-08 00:00:00'::timestamp without time zone))
-> Subquery Scan on "*SELECT* 9" (cost=0.42..2013.42 rows=1852 width=519) (actual time=0.035..2.204 rows=1000 loops=1)
-> Index Scan using idx_lendingtr_search13 on lendingtr lt_8 (cost=0.42..1994.90 rows=1852 width=519) (actual time=0.034..1.968 rows=1000 loops=1)
Index Cond: ((lendingdate >= '2023-01-07 00:00:00'::timestamp without time zone) AND (lendingdate <= '2023-01-08 00:00:00'::timestamp without time zone))
-> Subquery Scan on "*SELECT* 10" (cost=0.42..2013.42 rows=1852 width=519) (actual time=1.225..601.073 rows=1000 loops=1)
-> Index Scan using idx_lendingtr_search13 on lendingtr lt_9 (cost=0.42..1994.90 rows=1852 width=519) (actual time=1.224..600.276 rows=1000 loops=1)
Index Cond: ((lendingdate >= '2023-01-07 00:00:00'::timestamp without time zone) AND (lendingdate <= '2023-01-08 00:00:00'::timestamp without time zone))
-> Index Scan using bookmt_pkey on bookmt bm (cost=0.56..2.69 rows=1 width=220) (actual time=0.638..0.638 rows=1 loops=10000)
Index Cond: (bookno = "*SELECT* 1".bookno)
-> Memoize (cost=0.30..2.23 rows=1 width=29) (actual time=0.003..0.003 rows=1 loops=10000)
Cache Key: bm.ndccd
Cache Mode: logical
Hits: 3720 Misses: 280 Evictions: 0 Overflows: 0 Memory Usage: 37kB
Worker 0: Hits: 2720 Misses: 280 Evictions: 0 Overflows: 0 Memory Usage: 37kB
Worker 1: Hits: 2720 Misses: 280 Evictions: 0 Overflows: 0 Memory Usage: 37kB
-> Index Scan using ndcmt_pkey on ndcmt ndm (cost=0.29..2.22 rows=1 width=29) (actual time=0.018..0.018 rows=1 loops=840)
Index Cond: (ndccd = bm.ndccd)
-> Index Scan using idx_returntr_search1 on returntr rt (cost=0.42..2.46 rows=1 width=21) (actual time=0.118..0.119 rows=1 loops=10000)
Index Cond: (lendingno = "*SELECT* 1".lendingno)
Planning Time: 3.286 ms
Execution Time: 3246.371 msEXPLAIN ANALYZE
WITH L AS (
SELECT LT.LendingNo,
LT.UserNo,
LT.UserName,
LT.UserAffiliation,
LT.ReceptionStaffNo,
LT.ReceptionStaffName,
LT.ReceptionStaffAffiliation,
LT.LendingStatus,
LT.LendingDate,
LT.BookNo_1 AS BookNo,
LT.Title_1 AS Title,
LT.Author_1 AS Author,
LT.NDCCD_1 AS NDCCD,
LT.NDCName_1 AS NDCName
FROM LendingTR LT
WHERE LendingDate BETWEEN '2023-01-01'::TIMESTAMP AND '2023-01-02'
UNION ALL
SELECT LT.LendingNo,
LT.UserNo,
LT.UserName,
LT.UserAffiliation,
LT.ReceptionStaffNo,
LT.ReceptionStaffName,
LT.ReceptionStaffAffiliation,
LT.LendingStatus,
LT.LendingDate,
LT.BookNo_2 AS BookNo,
LT.Title_2 AS Title,
LT.Author_2 AS Author,
LT.NDCCD_2 AS NDCCD,
LT.NDCName_2 AS NDCName
FROM LendingTR LT
WHERE LendingDate BETWEEN '2023-01-01'::TIMESTAMP AND '2023-01-02'
UNION ALL
SELECT LT.LendingNo,
LT.UserNo,
LT.UserName,
LT.UserAffiliation,
LT.ReceptionStaffNo,
LT.ReceptionStaffName,
LT.ReceptionStaffAffiliation,
LT.LendingStatus,
LT.LendingDate,
LT.BookNo_3 AS BookNo,
LT.Title_3 AS Title,
LT.Author_3 AS Author,
LT.NDCCD_3 AS NDCCD,
LT.NDCName_3 AS NDCName
FROM LendingTR LT
WHERE LendingDate BETWEEN '2023-01-01'::TIMESTAMP AND '2023-01-02'
UNION ALL
SELECT LT.LendingNo,
LT.UserNo,
LT.UserName,
LT.UserAffiliation,
LT.ReceptionStaffNo,
LT.ReceptionStaffName,
LT.ReceptionStaffAffiliation,
LT.LendingStatus,
LT.LendingDate,
LT.BookNo_4 AS BookNo,
LT.Title_4 AS Title,
LT.Author_4 AS Author,
LT.NDCCD_4 AS NDCCD,
LT.NDCName_4 AS NDCName
FROM LendingTR LT
WHERE LendingDate BETWEEN '2023-01-01'::TIMESTAMP AND '2023-01-02'
UNION ALL
SELECT LT.LendingNo,
LT.UserNo,
LT.UserName,
LT.UserAffiliation,
LT.ReceptionStaffNo,
LT.ReceptionStaffName,
LT.ReceptionStaffAffiliation,
LT.LendingStatus,
LT.LendingDate,
LT.BookNo_5 AS BookNo,
LT.Title_5 AS Title,
LT.Author_5 AS Author,
LT.NDCCD_5 AS NDCCD,
LT.NDCName_5 AS NDCName
FROM LendingTR LT
WHERE LendingDate BETWEEN '2023-01-01'::TIMESTAMP AND '2023-01-02'
UNION ALL
SELECT LT.LendingNo,
LT.UserNo,
LT.UserName,
LT.UserAffiliation,
LT.ReceptionStaffNo,
LT.ReceptionStaffName,
LT.ReceptionStaffAffiliation,
LT.LendingStatus,
LT.LendingDate,
LT.BookNo_6 AS BookNo,
LT.Title_6 AS Title,
LT.Author_6 AS Author,
LT.NDCCD_6 AS NDCCD,
LT.NDCName_6 AS NDCName
FROM LendingTR LT
WHERE LendingDate BETWEEN '2023-01-01'::TIMESTAMP AND '2023-01-02'
UNION ALL
SELECT LT.LendingNo,
LT.UserNo,
LT.UserName,
LT.UserAffiliation,
LT.ReceptionStaffNo,
LT.ReceptionStaffName,
LT.ReceptionStaffAffiliation,
LT.LendingStatus,
LT.LendingDate,
LT.BookNo_7 AS BookNo,
LT.Title_7 AS Title,
LT.Author_7 AS Author,
LT.NDCCD_7 AS NDCCD,
LT.NDCName_7 AS NDCName
FROM LendingTR LT
WHERE LendingDate BETWEEN '2023-01-01'::TIMESTAMP AND '2023-01-02'
UNION ALL
SELECT LT.LendingNo,
LT.UserNo,
LT.UserName,
LT.UserAffiliation,
LT.ReceptionStaffNo,
LT.ReceptionStaffName,
LT.ReceptionStaffAffiliation,
LT.LendingStatus,
LT.LendingDate,
LT.BookNo_8 AS BookNo,
LT.Title_8 AS Title,
LT.Author_8 AS Author,
LT.NDCCD_8 AS NDCCD,
LT.NDCName_8 AS NDCName
FROM LendingTR LT
WHERE LendingDate BETWEEN '2023-01-01'::TIMESTAMP AND '2023-01-02'
UNION ALL
SELECT LT.LendingNo,
LT.UserNo,
LT.UserName,
LT.UserAffiliation,
LT.ReceptionStaffNo,
LT.ReceptionStaffName,
LT.ReceptionStaffAffiliation,
LT.LendingStatus,
LT.LendingDate,
LT.BookNo_9 AS BookNo,
LT.Title_9 AS Title,
LT.Author_9 AS Author,
LT.NDCCD_9 AS NDCCD,
LT.NDCName_9 AS NDCName
FROM LendingTR LT
WHERE LendingDate BETWEEN '2023-01-01'::TIMESTAMP AND '2023-01-02'
UNION ALL
SELECT LT.LendingNo,
LT.UserNo,
LT.UserName,
LT.UserAffiliation,
LT.ReceptionStaffNo,
LT.ReceptionStaffName,
LT.ReceptionStaffAffiliation,
LT.LendingStatus,
LT.LendingDate,
LT.BookNo_10 AS BookNo,
LT.Title_10 AS Title,
LT.Author_10 AS Author,
LT.NDCCD_10 AS NDCCD,
LT.NDCName_10 AS NDCName
FROM LendingTR LT
WHERE LendingDate BETWEEN '2023-01-01'::TIMESTAMP AND '2023-01-02'
)
SELECT D."利用者番号",
D."利用者氏名",
D."利用者所属",
D."受付職員番号",
D."職員氏名",
D."職員所属",
D."貸出状態",
D."貸出日時",
D."蔵書管理番号",
D."ISBN",
D."タイトル",
D."著者名",
D."日本十進分類番号",
D."分類名",
D."所蔵建屋",
D."所蔵フロア",
D."所属部屋",
D."所蔵書架",
D."返却日時"
FROM (
SELECT L.LendingNo,
L.UserNo AS "利用者番号",
L.UserName AS "利用者氏名",
L.UserAffiliation AS "利用者所属",
L.ReceptionStaffNo AS "受付職員番号",
L.ReceptionStaffName AS "職員氏名",
L.ReceptionStaffAffiliation AS "職員所属",
L.LendingStatus AS "貸出状態",
L.LendingDate AS "貸出日時",
L.BookNo AS "蔵書管理番号",
BM.ISBN AS "ISBN",
L.Title AS "タイトル",
L.Author AS "著者名",
L.NDCCD AS "日本十進分類番号",
L.NDCName AS "分類名",
BM.BuildingName AS "所蔵建屋",
BM.FloorName AS "所蔵フロア",
BM.RoomName AS "所属部屋",
BM.ShelfCD AS "所蔵書架",
RT.ReturnDate AS "返却日時"
FROM L
INNER JOIN BookMT BM ON (
BM.BookNo = L.BookNo
)
LEFT OUTER JOIN ReturnTR RT ON (
RT.LendingNo = L.LendingNo
)
) D
ORDER BY D."貸出日時", D.LendingNo, D."蔵書管理番号"
;Gather Merge (cost=51078.82..52853.21 rows=15208 width=242) (actual time=3197.624..3215.238 rows=10000 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=50078.79..50097.80 rows=7604 width=242) (actual time=3125.510..3125.753 rows=3333 loops=3)
Sort Key: "*SELECT* 1".lendingdate, "*SELECT* 1".lendingno, "*SELECT* 1".bookno
Sort Method: quicksort Memory: 919kB
Worker 0: Sort Method: quicksort Memory: 1194kB
Worker 1: Sort Method: quicksort Memory: 919kB
-> Nested Loop (cost=1.41..49588.62 rows=7604 width=242) (actual time=19.745..3097.329 rows=3333 loops=3)
-> Nested Loop Left Join (cost=0.85..29092.52 rows=7604 width=178) (actual time=1.422..960.329 rows=3333 loops=3)
-> Parallel Append (cost=0.42..10141.32 rows=7610 width=170) (actual time=1.052..371.877 rows=3333 loops=3)
-> Subquery Scan on "*SELECT* 1" (cost=0.42..2020.66 rows=1825 width=170) (actual time=0.083..351.410 rows=1000 loops=1)
-> Index Scan using idx_lendingtr_search13 on lendingtr lt (cost=0.42..2002.41 rows=1825 width=170) (actual time=0.082..350.302 rows=1000 loops=1)
Index Cond: ((lendingdate >= '2023-01-08 00:00:00'::timestamp without time zone) AND (lendingdate <= '2023-01-09 00:00:00'::timestamp without time zone))
-> Subquery Scan on "*SELECT* 2" (cost=0.42..2020.66 rows=1825 width=170) (actual time=0.119..349.886 rows=1000 loops=1)
-> Index Scan using idx_lendingtr_search13 on lendingtr lt_1 (cost=0.42..2002.41 rows=1825 width=170) (actual time=0.118..348.840 rows=1000 loops=1)
Index Cond: ((lendingdate >= '2023-01-08 00:00:00'::timestamp without time zone) AND (lendingdate <= '2023-01-09 00:00:00'::timestamp without time zone))
-> Subquery Scan on "*SELECT* 3" (cost=0.42..2020.66 rows=1825 width=170) (actual time=0.043..3.264 rows=1000 loops=1)
-> Index Scan using idx_lendingtr_search13 on lendingtr lt_2 (cost=0.42..2002.41 rows=1825 width=170) (actual time=0.043..2.913 rows=1000 loops=1)
Index Cond: ((lendingdate >= '2023-01-08 00:00:00'::timestamp without time zone) AND (lendingdate <= '2023-01-09 00:00:00'::timestamp without time zone))
-> Subquery Scan on "*SELECT* 4" (cost=0.42..2020.66 rows=1825 width=170) (actual time=0.030..3.426 rows=1000 loops=1)
-> Index Scan using idx_lendingtr_search13 on lendingtr lt_3 (cost=0.42..2002.41 rows=1825 width=170) (actual time=0.030..3.054 rows=1000 loops=1)
Index Cond: ((lendingdate >= '2023-01-08 00:00:00'::timestamp without time zone) AND (lendingdate <= '2023-01-09 00:00:00'::timestamp without time zone))
-> Subquery Scan on "*SELECT* 5" (cost=0.42..2020.66 rows=1825 width=170) (actual time=0.033..3.043 rows=1000 loops=1)
-> Index Scan using idx_lendingtr_search13 on lendingtr lt_4 (cost=0.42..2002.41 rows=1825 width=170) (actual time=0.033..2.731 rows=1000 loops=1)
Index Cond: ((lendingdate >= '2023-01-08 00:00:00'::timestamp without time zone) AND (lendingdate <= '2023-01-09 00:00:00'::timestamp without time zone))
-> Subquery Scan on "*SELECT* 6" (cost=0.42..2020.66 rows=1825 width=170) (actual time=0.035..2.937 rows=1000 loops=1)
-> Index Scan using idx_lendingtr_search13 on lendingtr lt_5 (cost=0.42..2002.41 rows=1825 width=170) (actual time=0.034..2.623 rows=1000 loops=1)
Index Cond: ((lendingdate >= '2023-01-08 00:00:00'::timestamp without time zone) AND (lendingdate <= '2023-01-09 00:00:00'::timestamp without time zone))
-> Subquery Scan on "*SELECT* 7" (cost=0.42..2020.66 rows=1825 width=170) (actual time=0.032..2.862 rows=1000 loops=1)
-> Index Scan using idx_lendingtr_search13 on lendingtr lt_6 (cost=0.42..2002.41 rows=1825 width=170) (actual time=0.031..2.592 rows=1000 loops=1)
Index Cond: ((lendingdate >= '2023-01-08 00:00:00'::timestamp without time zone) AND (lendingdate <= '2023-01-09 00:00:00'::timestamp without time zone))
-> Subquery Scan on "*SELECT* 8" (cost=0.42..2020.66 rows=1825 width=170) (actual time=0.017..3.175 rows=1000 loops=1)
-> Index Scan using idx_lendingtr_search13 on lendingtr lt_7 (cost=0.42..2002.41 rows=1825 width=170) (actual time=0.016..2.848 rows=1000 loops=1)
Index Cond: ((lendingdate >= '2023-01-08 00:00:00'::timestamp without time zone) AND (lendingdate <= '2023-01-09 00:00:00'::timestamp without time zone))
-> Subquery Scan on "*SELECT* 9" (cost=0.42..2020.66 rows=1825 width=170) (actual time=0.031..3.501 rows=1000 loops=1)
-> Index Scan using idx_lendingtr_search13 on lendingtr lt_8 (cost=0.42..2002.41 rows=1825 width=170) (actual time=0.029..3.134 rows=1000 loops=1)
Index Cond: ((lendingdate >= '2023-01-08 00:00:00'::timestamp without time zone) AND (lendingdate <= '2023-01-09 00:00:00'::timestamp without time zone))
-> Subquery Scan on "*SELECT* 10" (cost=0.42..2020.66 rows=1825 width=170) (actual time=2.948..388.145 rows=1000 loops=1)
-> Index Scan using idx_lendingtr_search13 on lendingtr lt_9 (cost=0.42..2002.41 rows=1825 width=170) (actual time=2.946..387.030 rows=1000 loops=1)
Index Cond: ((lendingdate >= '2023-01-08 00:00:00'::timestamp without time zone) AND (lendingdate <= '2023-01-09 00:00:00'::timestamp without time zone))
-> Index Scan using idx_returntr_search1 on returntr rt (cost=0.42..2.48 rows=1 width=21) (actual time=0.173..0.174 rows=1 loops=10000)
Index Cond: (lendingno = "*SELECT* 1".lendingno)
-> Index Scan using bookmt_pkey on bookmt bm (cost=0.56..2.70 rows=1 width=75) (actual time=0.638..0.638 rows=1 loops=10000)
Index Cond: (bookno = "*SELECT* 1".bookno)
Planning Time: 2.647 ms
Execution Time: 3216.397 msグラフとexplainの結果から、次の傾向が分かります。
- 正規形の方が非正規形より性能が良好である
- 正規形のSQLはJOINが多くなる傾向だが、性能は良好である
- 非正規形はJOINが少ない代わりに、繰り返し項目を展開するUNION ALLが多くなる
- explainの結果を詳しく見ると、非正規形ではJOIN処理に多くの時間がかかっている
正規形と非正規形の違いは明らかですが、正規形同士・非正規形同士の比較ではあまり差が出ませんでした。今回は日付による検索であったことで、連続的なページに対するシーケンシャル読み出しが多くなり、レコードサイズによるI/O効率の差が出にくかったようです。
ランダムアクセスが多い問い合わせでは、もう少し明確な差が出ると思います。
まとめ
今回は長くなってしまいましたが、整理すると以下の結論となります。
- 実測においても、正規形の方が非正規形より良好な性能となった
- 概ねレコードサイズに比例して処理時間が増加する
- 正規形で性能が出ないケースでも、改善する方法がある
今回の検証ではデータ件数もレコードサイズもそれなりでしたが、データ件数が1億件を超えたり、レコードサイズがMB単位になるようなケースでは、性能差はもっと顕著に表れるでしょう。
正規化と非正規化については性能に関する議論が多いため、ここまで性能に着目して説明してきました。
「正規形が遅い」という説が誤解であることについて、理解して頂けたのではないでしょうか？
しかし、正規化を行うメリットは性能面だけではありません。
次回からは設計と実装の観点で、正規化がもたらすメリットについて説明していきたいと思います。本当の正規化のメリットはむしろこちらの方にありますので、どうぞご期待ください。
- レコードサイズを求める場合、統計情報（pg_classテーブル）を使うことが一般的ですが、統計情報で計算されたレコードサイズにはTOAST領域のサイズを含まないので、今回の目的には適合しません。ここではテーブルごとの平均データサイズをレコードサイズとして概算しました。正確なレコードサイズには、他に管理情報のサイズも含まれることになります。 ↩︎
(まだ評価がありません)
