誰がどんな名前のペットを飼っているのかな？ 解答編 / JPOUG Advent Calendar Day 23 Tweet
さーて、いよいよ残りの窓も残りわずか、JPOUGアドベントカレンダー、22番目の窓は、Kazuhiro TakahashiさんのDBLINKを使ったときのLOBの制約について [SQL・DDL]でした。
23番目の窓は、1番目の窓でネタ振りしたエントリーの解答編です。みなさんには簡単過ぎますよねw どのような方法で解いたのでしょう:) 気になる!
私は、愚直な方法にしてみました。（予告通りw
まず、前回提示したDDLで元となる3表とデータを準備
- ペットのオーナー = owners
- ペットの名前 = pets
- 動物の種類 = animals
create table owners (name varchar2(30) not null unique);
create table pets (name varchar2(30) not null unique);
create table animals (kind varchar2(30) not null unique);
-- owners
insert into owners(name) values('Hiro');
insert into owners(name) values('Larry');
insert into owners(name) values('Scott');
insert into owners(name) values('Steve');
-- pets
insert into pets(name) values('Lisa');
insert into pets(name) values('Taro');
insert into pets(name) values('Tiger');
insert into pets(name) values('Wendy');
-- animals
insert into animals(kind) values('Cat');
insert into animals(kind) values('Dog');
insert into animals(kind) values('Snake');
insert into animals(kind) values('Turtle');
commit;
SCOTT@ORCL> select name from owners;
NAME
-------------------------------
Hiro
Larry
Scott
Steve
Elapsed: 00:00:00.01
SCOTT@ORCL> select name from pets;
NAME
------------------------------
Lisa
Taro
Tiger
Wendy
Elapsed: 00:00:00.01
SCOTT@ORCL> select kind from animals;
KIND
------------------------------
Cat
Dog
Snake
Turtle
Elapsed: 00:00:00.01
前回、簡単に脳内イメージをダンプしておきましたが、それらを元に関連エンティティの代用で3つのビューを作成することにしました。
見ていきましょう。
「ペットのオーナー」と「ペットの名前」を関連づけるビュー = owners_pets
ペットのオーナーとペットの名前の関係として、
Hiro : 「僕が飼っているのは、Tiger だよ。 Turtle じゃないよ」
だけがあるので、
- Hiro は Tiger という名のペットを飼っている。
- Hiro 以外は、Tiger という名のペット以外のいずれかを飼っている
という関連を以下のビューで定義
create view owners_pets
as
select
owners.name as owner_name
, pets.name as pet_name
from
owners
cross join pets
where
(
owners.name = 'Hiro'
and pets.name = 'Tiger'
)
or (
owners.name != 'Hiro'
and pets.name != 'Tiger'
)
/
問い合わせると以下のようになります（OWNER_NAME,PET_NAME列で昇順にソートしています）
OWNER_NAME PET_NAME
------------------------------------------------------------ ------------------------------
Hiro Tiger
Larry Lisa
Larry Taro
Larry Wendy
Scott Lisa
Scott Taro
Scott Wendy
Steve Lisa
Steve Taro
Steve Wendy
10 rows selected.
２つめのビューは、
「ペットの名前」と「動物の種類」を関連づけるビュー = pets_animals
- Hiro :「僕が飼っているのは、Tiger だよ。 Turtle じゃないよ」
- Larry :「Snake の名前は、Lisa じゃないよ」
- Steve :「Dog の名前は Wendy だよ。僕は Dog はかってないけど」
問題からペットの名前と動物の種類の関係として、以下が読み取れます。
- Wendyは、Dogだ。
- Tigerは、Turtleではない。また、Dogでもない。（同種のペットは複数いない。Wendy=Dogなので）
- Lisaは、Snakeではない。また、Dogでもない
- Taroは、少なくとも、Dogではない。
上記を元に作成したビューは以下
create view pets_animals
as
select
pets.name as pet_name
, animals.kind as animal_kind
from
pets
cross join animals
where
(
pets.name = 'Wendy'
and animals.kind = 'Dog'
)
or (
pets.name = 'Tiger'
and animals.kind not in ('Dog', 'Turtle')
)
or (
pets.name = 'Lisa'
and animals.kind not in ('Snake', 'Dog')
)
or (
pets.name = 'Taro'
and animals.kind not in ('Dog')
)
/
問い合わせると以下のようになります（PET_NAME, ANIMAL_KIND列で昇順にソートしています）
PET_NAME ANIMAL_KIND
------------------------------ ------------------------------
Lisa Cat
Lisa Turtle
Taro Cat
Taro Snake
Taro Turtle
Tiger Cat
Tiger Snake
Wendy Dog
8 rows selected.
3つ目のビュー、
「ペットのオーナー」と「動物の種類」を関連づけるビュー = owners_animals
- Hiro :「僕が飼っているのは、Tiger だよ。 Turtle じゃないよ」
- Scott :「僕は Snake を飼っているよ」
- Steve :「Dog の名前は Wendy だよ。僕は Dog はかってないけど」
問題からペットのオーナーと動物の種類を関連として、以下が読み取れますよね。
- Scottは、Snakeを飼っている
- Steveは、Dogを飼っていない。また、Snakeも飼っていない（同種のペットを飼っているオーナーはおらず、SnakeはScottが飼っているから）
- Hiroは、Turtleは飼っていない。また、Snakeも飼っていない（理由は同上）
- Larryは、何をか不明だが、少なくとも、Snakeではない（理由は同上）
上記を元に作ったビューが以下
create view owners_animals
as
select
owners.name as owner_name
, animals.kind as animal_kind
from
owners
cross join animals
where
(
owners.name = 'Scott'
and animals.kind = 'Snake'
)
or (
owners.name = 'Steve'
and animals.kind not in ('Dog', 'Snake')
)
or (
owners.name = 'Hiro'
and animals.kind not in ('Turtle', 'Snake')
)
or (
owners.name = 'Larry'
and animals.kind not in ('Snake')
)
/
問い合わせると以下のようになります（OWNER_NAME, ANIMAL_KIND列で昇順にソートしています）
OWNER_NAME ANIMAL_KIND
------------------------------------------------------------ ------------------------------
Hiro Cat
Hiro Dog
Larry Cat
Larry Dog
Larry Turtle
Scott Snake
Steve Cat
Steve Turtle
8 rows selected.
オーナー、ペットの名前、ペットの種類の関連を作成したので、これらを結合してみましょう（まだ途中経過ですよ）
ここまで来ると直積は不要ですねw
しかし、まだ、オーナー→ペットの名前→動物の種類の候補が複数あるオーナーが残っています！！！
Hiroと、Scottは確定ですが、LarryとSteveは複数候補があるので、さらに絞り込んでいく必要がありますよね。
select
count(op.owner_name) over(
partition by op.owner_name
) as num_of_rows
, op.owner_name
, op.pet_name
, oa.animal_kind
from
owners_pets op
inner join owners_animals oa
on
op.owner_name = oa.owner_name
inner join pets_animals pa
on
op.pet_name = pa.pet_name
and oa.animal_kind = pa.animal_kind
order by
op.owner_name
, op.pet_name
, oa.animal_kind
/
3つのビューを結合すると以下の結果を得られますが、繋がりを１つに絞りきれていません。＞＜
NUM_OF_ROWS OWNER_NAME PET_NAME ANIMAL_KIND
----------- ------------------------------ ------------------------------ ------------------------------
1 Hiro Tiger Cat
5 Larry Lisa Cat
5 Larry Lisa Turtle
5 Larry Taro Cat
5 Larry Taro Turtle
5 Larry Wendy Dog
1 Scott Taro Snake
4 Steve Lisa Cat
4 Steve Lisa Turtle
4 Steve Taro Cat
4 Steve Taro Turtle
11 rows selected.
上記、結合結果から num_or_rows = 1 は確定、2以上は絞りきれておらず、複数の候補が残っていることが見えてきます。
ここでポイントになるのは、確定したデータです。同一名のペットおよび、同種のペットはない！！ という条件があるので、HiroとScottがオーナーであると確定したことで以下が決まります。
- まだ、複数候補が残っているオーナーに関して、
- ペットの名前において、TigerとTaroは、Larryまたは、Steveのペット名ではない
- 動物の種類において、CatとSnakeは、Larryまたは、Steveが飼っている動物の種類ではない
これをもう少しブラッシュアップすると以下のようになります。
- まだ、複数候補が残っているオーナーに関して、
- ペットの名前において、オーナーの確定した名前は除外できる
- 動物の種類において、オーナーの確定した種類は除外できる
オーナーが確定した状態、複数の候補が残っている状態は、ウィンドウ関数COUNT()でカウントした行数で判断できるようにしてあります！！！
よし、解けそうだ！w
with pet_owner_unknown
as
(
select
count(op.owner_name) over(
partition by op.owner_name
) as num_of_rows
, op.owner_name
, op.pet_name
, oa.animal_kind
from
owners_pets op
inner join owners_animals oa
on
op.owner_name = oa.owner_name
inner join pets_animals pa
on
op.pet_name = pa.pet_name
and oa.animal_kind = pa.animal_kind
order by
op.owner_name
, op.pet_name
, oa.animal_kind
)
select
count(owner_name) over(
partition by owner_name
) as num_of_rows
, owner_name
, pet_name
, animal_kind
from
pet_owner_unknown
where
num_of_rows = 1
or (
num_of_rows > 1
and pet_name not in (
select
pet_name
from
pet_owner_unknown
where
num_of_rows = 1
)
and animal_kind not in (
select
animal_kind
from
pet_owner_unknown
where
num_of_rows = 1
)
)
order by
owner_name
/
結果は！！
NUM_OF_ROWS OWNER_NAME PET_NAME ANIMAL_KIND
----------- ------------------------------ ------------------------------ ------------------------------
1 Hiro Tiger Cat
2 Larry Wendy Dog
2 Larry Lisa Turtle
1 Scott Taro Snake
1 Steve Lisa Turtle
あ”〜〜〜おしい、まだ、Larryが確定できてないw Larry〜〜〜〜〜〜っw 狙ったわけではありませんが、Larryが残ってしまったw
ここまでくれば、あと一息w
同じ条件を適用して。。。。
with pet_owner_unknown
as
(
select
count(op.owner_name) over(
partition by op.owner_name
) as num_of_rows
, op.owner_name
, op.pet_name
, oa.animal_kind
from
owners_pets op
inner join owners_animals oa
on
op.owner_name = oa.owner_name
inner join pets_animals pa
on
op.pet_name = pa.pet_name
and oa.animal_kind = pa.animal_kind
order by
op.owner_name
, op.pet_name
, oa.animal_kind
),
pet_owners
as
(
select
count(owner_name) over(
partition by owner_name
) as num_of_rows
, owner_name
, pet_name
, animal_kind
from
pet_owner_unknown
where
num_of_rows = 1
or (
num_of_rows > 1
and pet_name not in (
select
pet_name
from
pet_owner_unknown
where
num_of_rows = 1
)
and animal_kind not in (
select
animal_kind
from
pet_owner_unknown
where
num_of_rows = 1
)
)
order by
owner_name
)
select
owner_name
, pet_name
, animal_kind
from
pet_owners
where
num_of_rows = 1
or (
num_of_rows > 1
and pet_name not in (
select
pet_name
from
pet_owners
where
num_of_rows = 1
)
and animal_kind not in (
select
animal_kind
from
pet_owners
where
num_of_rows = 1
)
)
order by
owner_name
/
OWNER_NAME PET_NAME ANIMAL_KIND
------------------------------ ------------------------------ ------------------------------
Hiro Tiger Cat
Larry Wendy Dog
Scott Taro Snake
Steve Lisa Turtle
できたーーーー。
明日のクリスマスイブ、24番目の窓は、みやくるさんのターンです。お楽しみに〜〜〜〜。
メリークリスマス！ そして、よいお年をお迎えください。
あ！、 One more thing!
実行計画はみておきましょうね。おそらくIndex Only ScanとTemp Table Transform後のマテリアライズされた一時表の全表走査のはず！
SCOTT@ORCL> select banner_full from v$version;
BANNER_FULL
----------------------------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
お！ ほぼ想定通りですが、 LOAD AS SELECT (CURSOR DURATION MEMORY) とありますね。12cの機能だったと思う（間違ってたら誰かコメントよろ）。
TEMP TABLE TRANSFORMATIONで、一時表を使用して問合せを実行したことを示していますが、CURSOR DURATION MEMORYとでているので、メモリーが利用可能であればメモリーへ一時表の結果を格納したことを示しています。（メモリーが使用できなかった場合は、一時データをディスクに書き込みます）という動きですね。メモリー使えればキャッシュされることになるので、一時表の全表走査のコストは低めに抑えられるということになります。この仕組みのおかげで、OLTPライクな軽めのSQLでもWITHがいい感じで使える可能性もあるな。と個人的には思ってるところ。一時表にしたところで行数絞り込めてないとOLTPにはキツイ場合もありますけど、その辺りは見極めと行数のブレの可能性次第。。。
Execution Plan
----------------------------------------------------------
Plan hash value: 1308123330
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 553 | 13 (8)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6619_665472 | | | | |
| 3 | WINDOW SORT | | 7 | 119 | 8 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 7 | 119 | 8 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 7 | 77 | 4 (0)| 00:00:01 |
| 6 | INDEX FULL SCAN | SYS_C0012896 | 4 | 24 | 1 (0)| 00:00:01 |
|* 7 | INDEX FAST FULL SCAN | SYS_C0012900 | 2 | 10 | 1 (0)| 00:00:01 |
|* 8 | INDEX FAST FULL SCAN | SYS_C0012898 | 1 | 6 | 1 (0)| 00:00:01 |
| 9 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D661A_665472 | | | | |
| 10 | WINDOW SORT | | 7 | 371 | 2 (0)| 00:00:01 |
|* 11 | FILTER | | | | | |
| 12 | VIEW | | 7 | 371 | 2 (0)| 00:00:01 |
| 13 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6619_665472 | 7 | 119 | 2 (0)| 00:00:01 |
|* 14 | VIEW | | 1 | 30 | 1 (0)| 00:00:01 |
| 15 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6619_665472 | 7 | 119 | 2 (0)| 00:00:01 |
|* 16 | VIEW | | 1 | 30 | 1 (0)| 00:00:01 |
| 17 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6619_665472 | 7 | 119 | 2 (0)| 00:00:01 |
| 18 | SORT ORDER BY | | 7 | 553 | 3 (34)| 00:00:01 |
|* 19 | FILTER | | | | | |
| 20 | VIEW | | 7 | 553 | 2 (0)| 00:00:01 |
| 21 | TABLE ACCESS FULL | SYS_TEMP_0FD9D661A_665472 | 7 | 217 | 2 (0)| 00:00:01 |
|* 22 | VIEW | | 7 | 210 | 2 (0)| 00:00:01 |
| 23 | TABLE ACCESS FULL | SYS_TEMP_0FD9D661A_665472 | 7 | 217 | 2 (0)| 00:00:01 |
|* 24 | VIEW | | 7 | 210 | 2 (0)| 00:00:01 |
| 25 | TABLE ACCESS FULL | SYS_TEMP_0FD9D661A_665472 | 7 | 217 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - filter("NAME"='Scott' AND "ANIMALS"."KIND"='Snake' OR "NAME"='Steve' AND "ANIMALS"."KIND"<>'Dog' AND
"ANIMALS"."KIND"<>'Snake' OR "NAME"='Hiro' AND "ANIMALS"."KIND"<>'Turtle' AND "ANIMALS"."KIND"<>'Snake' OR
"NAME"='Larry' AND "ANIMALS"."KIND"<>'Snake')
8 - filter(("OWNERS"."NAME"='Hiro' AND "PETS"."NAME"='Tiger' OR "OWNERS"."NAME"<>'Hiro' AND
"PETS"."NAME"<>'Tiger') AND ("NAME"='Wendy' AND "KIND"='Dog' OR "NAME"='Tiger' AND "KIND"<>'Dog' AND
"KIND"<>'Turtle' OR "NAME"='Lisa' AND "KIND"<>'Snake' AND "KIND"<>'Dog' OR "NAME"='Taro' AND "KIND"<>'Dog'))
11 - filter("NUM_OF_ROWS"=1 OR "NUM_OF_ROWS">1 AND NOT EXISTS (SELECT 0 FROM (SELECT /*+ CACHE ("T1") */
"C0" "NUM_OF_ROWS","C1" "OWNER_NAME","C2" "PET_NAME","C3" "ANIMAL_KIND" FROM
"SYS"."SYS_TEMP_0FD9D6619_665472" "T1") "PET_OWNER_UNKNOWN" WHERE "PET_NAME"=:B1 AND "NUM_OF_ROWS"=1) AND
NOT EXISTS (SELECT 0 FROM (SELECT /*+ CACHE ("T1") */ "C0" "NUM_OF_ROWS","C1" "OWNER_NAME","C2"
"PET_NAME","C3" "ANIMAL_KIND" FROM "SYS"."SYS_TEMP_0FD9D6619_665472" "T1") "PET_OWNER_UNKNOWN" WHERE
"ANIMAL_KIND"=:B2 AND "NUM_OF_ROWS"=1))
14 - filter("PET_NAME"=:B1 AND "NUM_OF_ROWS"=1)
16 - filter("ANIMAL_KIND"=:B1 AND "NUM_OF_ROWS"=1)
19 - filter("NUM_OF_ROWS"=1 OR "NUM_OF_ROWS">1 AND NOT EXISTS (SELECT 0 FROM (SELECT /*+ CACHE ("T1") */
"C0" "NUM_OF_ROWS","C1" "OWNER_NAME","C2" "PET_NAME","C3" "ANIMAL_KIND" FROM
"SYS"."SYS_TEMP_0FD9D661A_665472" "T1") "PET_OWNERS" WHERE "PET_NAME"=:B1 AND "NUM_OF_ROWS"=1) AND NOT
EXISTS (SELECT 0 FROM (SELECT /*+ CACHE ("T1") */ "C0" "NUM_OF_ROWS","C1" "OWNER_NAME","C2" "PET_NAME","C3"
"ANIMAL_KIND" FROM "SYS"."SYS_TEMP_0FD9D661A_665472" "T1") "PET_OWNERS" WHERE "ANIMAL_KIND"=:B2 AND
"NUM_OF_ROWS"=1))
22 - filter("PET_NAME"=:B1 AND "NUM_OF_ROWS"=1)
24 - filter("ANIMAL_KIND"=:B1 AND "NUM_OF_ROWS"=1)
Statistics
----------------------------------------------------------
0 recursive calls
2 db block gets
49 consistent gets
0 physical reads
384 redo size
835 bytes sent via SQL*Net to client
1827 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
4 rows processed
では。また。
誰がどんな名前のペットを飼っているのかな？ その１ / JPOUG Advent Calendar Day 1
類似エントリー
標準はあるにはあるが癖の多いSQL 全部俺 おまけ SQL de 湯婆婆やるにも癖がでるw
標準はあるにはあるが癖の多いSQL 全部俺 #25 SQL de Fractalsにも癖がある:)
こんなのでいいのかなぁ。ズンドコキヨシ ObjectScript / MUMPS
PL/SQL de ケンブリッジ関数
Mac de Caché というか MUMPS というか Objectscript か
Oracle de Fizzbuzz #2
Oracle de Fizzbuzz #1 - いまごろ・・・ですが・・
| 固定リンク | 0
コメント