先日投稿した「NULLを排除した設計①」の続きです。
若干変更
前回の投稿から若干変更です。せっかくCLOBカラムを定義したので、I/O負荷を高くするため値を設定する際に4000バイト長のデータにして格納することにしました。（LOBカラムへの正規の値格納方法ではないのですが、簡単に4000バイトのデータを設定しました。）
SQL> update CUSTOMERS_1 set REMARKS = rpad('0',4000,'0') where mod(CUST_ID,5 ) = 0;
10000行が更新されました。
SQL> update CUSTOMERS_1 set REMARKS = rpad('1',4000,'1') where mod(CUST_ID,10) = 1;
5000行が更新されました。
SQL> update CUSTOMERS_1 set REMARKS = rpad('2',4000,'2') where mod(CUST_ID,20) = 2;
2500行が更新されました。
SQL> update CUSTOMERS_1 set REMARKS = rpad('3',4000,'3') where mod(CUST_ID,50) = 3;
1000行が更新されました。
SQL> commit;
コミットが完了しました。
SQL> select
2 CUST_ID
3 ,nvl(DBMS_LOB.SUBSTR(REMARKS, 10, 1),'Null') REMARKS
4 ,DBMS_LOB.GETLENGTH(REMARKS) LENGTH
5 from CUSTOMERS_1 where CUST_ID < 21
6 order by CUST_ID;
CUST_ID REMARKS LENGTH
---------- --------------- ----------
1 1111111111 4000
2 2222222222 4000
3 3333333333 4000
4 Null
5 0000000000 4000
6 Null
7 Null
8 Null
9 Null
10 0000000000 4000
11 1111111111 4000
CUST_ID REMARKS LENGTH
---------- --------------- ----------
12 Null
13 Null
14 Null
15 0000000000 4000
16 Null
17 Null
18 Null
19 Null
20 0000000000 4000
20行が選択されました。
SQL> select
2 nvl(DBMS_LOB.SUBSTR(REMARKS, 10, 1),'Null') REMARKS
3 ,count(*)
4 from
5 CUSTOMERS_1
6 group by
7 DBMS_LOB.SUBSTR(REMARKS, 10, 1)
8 order by
9 1;
REMARKS COUNT(*)
--------------- ----------
0000000000 10000
1111111111 5000
2222222222 2500
3333333333 1000
Null 31500
パフォーマンス比較
テストは同じSQL文をそれぞれ３回連続で実行し、一番経過時間が短かった結果を採用しました。
まずは全件検索（結果：50,000件）
①-1 単純カラム追加（NULLを排除しない設計）
SQL> select
2 CUST_ID
3 ,CUST_FNAME
4 ,CUST_LNAME
5 ,SEX
6 ,REMARKS
7 from
8 CUSTOMERS_1;
50000行が選択されました。
経過: 00:00:30.41
実行計画
----------------------------------------------------------
Plan hash value: 2813274236
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50000 | 3857K| 140 (0)| 00:00:02 |
| 1 | TABLE ACCESS FULL| CUSTOMERS_1 | 50000 | 3857K| 140 (0)| 00:00:02 |
---------------------------------------------------------------------------------
統計
----------------------------------------------------------
0 recursive calls
0 db block gets
198074 consistent gets
148000 physical reads
0 redo size
24824948 bytes sent via SQL*Net to client
17644472 bytes received via SQL*Net from client
124002 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50000 rows processed
②-1 テーブル追加（Nullを排除した設計）
SQL> select
2 C.CUST_ID
3 ,C.CUST_FNAME
4 ,C.CUST_LNAME
5 ,C.SEX
6 ,R.REMARKS
7 from
8 CUSTOMERS_2 C left outer join CUST_2_REMARKS R
9 on C.CUST_ID = R.CUST_ID;
50000行が選択されました。
経過: 00:00:30.31
実行計画
----------------------------------------------------------
Plan hash value: 2466772454
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50000 | 7373K| | 361 (1)| 00:00:05 |
|* 1 | HASH JOIN RIGHT OUTER| | 50000 | 7373K| 2248K| 361 (1)| 00:00:05 |
| 2 | TABLE ACCESS FULL | CUST_2_REMARKS | 18500 | 2023K| | 40 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | CUSTOMERS_2 | 50000 | 1904K| | 90 (0)| 00:00:02 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("C"."CUST_ID"="R"."CUST_ID"(+))
統計
----------------------------------------------------------
0 recursive calls
0 db block gets
198137 consistent gets
148000 physical reads
0 redo size
24824948 bytes sent via SQL*Net to client
17644472 bytes received via SQL*Net from client
124002 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
50000 rows processed
このクエリーはOUTER JOINで書くのがポイントです。
コスト値は単純カラム追加の方が半分以下なのですが、physical readsは両者全く同じで、経過時間もほとんど同じです。
②のコストが大きいのはハッシュ・ジョインのためですが、パフォーマンス的には遜色のない結果となっています。
IS NOT NULL検索はどうか？（結果：18,500件）
①-2 単純カラム追加（NULLを排除しない設計）
SQL> select
2 CUST_ID
3 ,CUST_FNAME
4 ,CUST_LNAME
5 ,SEX
6 ,REMARKS
7 from
8 CUSTOMERS_1
9 where REMARKS is not null;
18500行が選択されました。
経過: 00:00:24.06
実行計画
----------------------------------------------------------
Plan hash value: 2813274236
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 18500 | 1427K| 143 (2)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| CUSTOMERS_1 | 18500 | 1427K| 143 (2)| 00:00:02 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("REMARKS" IS NOT NULL)
統計
----------------------------------------------------------
0 recursive calls
0 db block gets
166771 consistent gets
148000 physical reads
0 redo size
20641347 bytes sent via SQL*Net to client
17297972 bytes received via SQL*Net from client
92502 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
18500 rows processed
②-2 テーブル追加（Nullを排除した設計）
SQL> select
2 C.CUST_ID
3 ,C.CUST_FNAME
4 ,C.CUST_LNAME
5 ,C.SEX
6 ,R.REMARKS
7 from
8 CUSTOMERS_2 C inner join CUST_2_REMARKS R
9 on C.CUST_ID = R.CUST_ID;
18500行が選択されました。
経過: 00:00:23.60
実行計画
----------------------------------------------------------
Plan hash value: 2209842270
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 18500 | 2728K| | 361 (1)| 00:00:05 |
|* 1 | HASH JOIN | | 18500 | 2728K| 2248K| 361 (1)| 00:00:05 |
| 2 | TABLE ACCESS FULL| CUST_2_REMARKS | 18500 | 2023K| | 40 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| CUSTOMERS_2 | 50000 | 1904K| | 90 (0)| 00:00:02 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("C"."CUST_ID"="R"."CUST_ID")
統計
----------------------------------------------------------
0 recursive calls
0 db block gets
166835 consistent gets
148000 physical reads
0 redo size
20641347 bytes sent via SQL*Net to client
17297972 bytes received via SQL*Net from client
92502 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
18500 rows processed
このクエリーはINNER JOINで書くのがポイントですが、全件検索と同様に②-1と較べて遜色ないというか、むしろ若干よい結果が出ました。（これは偶然だと思いますが。）
IS NULL検索はどうか？（結果：31,500件）
③-1 単純カラム追加（NULLを排除しない設計）
SQL> select
2 CUST_ID
3 ,CUST_FNAME
4 ,CUST_LNAME
5 ,SEX
6 ,REMARKS
7 from
8 CUSTOMERS_1
9 where REMARKS is null;
31500行が選択されました。
経過: 00:00:06.56
実行計画
----------------------------------------------------------
Plan hash value: 2813274236
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 31500 | 2430K| 143 (2)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| CUSTOMERS_1 | 31500 | 2430K| 143 (2)| 00:00:02 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("REMARKS" IS NULL)
統計
----------------------------------------------------------
0 recursive calls
0 db block gets
31745 consistent gets
0 physical reads
0 redo size
4184175 bytes sent via SQL*Net to client
346972 bytes received via SQL*Net from client
31502 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
31500 rows processed
③-2 テーブル追加（Nullを排除した設計）
SQL> select
2 CUST_ID
3 ,CUST_FNAME
4 ,CUST_LNAME
5 ,SEX
6 ,null REMARKS
7 from
8 CUSTOMERS_2
9 where not exists (
10 select
11 1
12 from
13 CUST_2_REMARKS
14 where CUSTOMERS_2.CUST_ID = CUST_2_REMARKS.CUST_ID
15 );
31500行が選択されました。
経過: 00:00:00.75
実行計画
----------------------------------------------------------
Plan hash value: 2401287113
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 31500 | 1353K| 93 (4)| 00:00:02 |
| 1 | NESTED LOOPS ANTI | | 31500 | 1353K| 93 (4)| 00:00:02 |
| 2 | TABLE ACCESS FULL| CUSTOMERS_2 | 50000 | 1904K| 90 (0)| 00:00:02 |
|* 3 | INDEX UNIQUE SCAN| PK_CUST_2_REMARKS | 6845 | 34225 | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("CUSTOMERS_2"."CUST_ID"="CUST_2_REMARKS"."CUST_ID")
統計
----------------------------------------------------------
0 recursive calls
0 db block gets
6386 consistent gets
0 physical reads
0 redo size
1577675 bytes sent via SQL*Net to client
23561 bytes received via SQL*Net from client
2101 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
31500 rows processed
このクエリーはNOT EXIST句を使って書くのがポイントですが、テーブル分割した方が圧倒的にパフォーマンスがよいです！
しかし、実はこれには落とし穴があります。IS NULL条件なので結果のREMARKSカラムは必ずNullになります。③-1は以下のように書き換えが可能です。
SQL> select
2 CUST_ID
3 ,CUST_FNAME
4 ,CUST_LNAME
5 ,SEX
6 ,null REMARKS
7 from
8 CUSTOMERS_1
9 where REMARKS is null;
31500行が選択されました。
経過: 00:00:00.71
実行計画
----------------------------------------------------------
Plan hash value: 2813274236
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 31500 | 2430K| 143 (2)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| CUSTOMERS_1 | 31500 | 2430K| 143 (2)| 00:00:02 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("REMARKS" IS NULL)
統計
----------------------------------------------------------
0 recursive calls
0 db block gets
2531 consistent gets
0 physical reads
0 redo size
1577675 bytes sent via SQL*Net to client
23561 bytes received via SQL*Net from client
2101 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
31500 rows processed
律儀にREMARKSカラムをクエリーに入れてしまうと、パフォーマンスが極端に悪くなります。
こうして見ると設計の違いでパフォーマンスに特筆すべき差はないので、「単純なカラム追加でよいではないか。」という意見もあるかと思いますが、Nullを排除した設計の利点を次回考察していきたいと思います。
（続く）