Index Only Accessネタのおまけのおまけ Tweet
Caché de Index Only Access できるのか？
CachéってSQLもサポートしてるので、Index Only Accessサポートしてるのかな〜と素朴な疑問から調べてみたもののマニュアルには記載されていない。
書かれていなきゃどうなるか、試すのが手っ取り早いですね。
（ダウンロードするにはフォームからリクエストする必要があります。１セッション限定の試用ライセンスのようです。他の制限は該当ページ読んでくださいな。）
OracleのIndex Only Accessは以下のエントリを参考にしてください。
・いん！、イン！、Index どっぷり Inde Only Access生活w - Oracle OpenWorld Unconference presented by JPOUG
・JPOUG SET EVENTS 20120721 - 「(続)いん！、イン！、Index 大人の事情縛りのSQLチューニング」資料公開
MySQL/PostgreSQLのIndex Only Accessは以下のエントリを参考にしてください。
・Index Only Accessネタのおまけ
いつもはMacBook Air使うんですが、今回は都合により母艦のVirtualBoxを使いました (^^;;
MacPro Mid 2012 12Core/24GB (OS X Mountain Lion Server)
VirtualBox4.1.20 for MacOS X
GuestOS：CentOS5.8 x86_64 (CPU数とメモリサイズはMacBook Airのx86環境と同じ）
Caché 2012.1 for Linux x86_64
まず、ターミナルで接続して表、索引、データ登録、他のデータベースで言う統計情報取得から。
[oracle@pleco ˜]$ csession cache -U samples
ノード: pleco.macdeoracle.jp インスタンス: CACHE
SAMPLES>
SAMPLES>d $System.SQL.Shell()
SQL Command Line Shell
----------------------------------------------------
The command prefix is currently set to: <<nothing>>.
Enter q to quit, ? for help.
SAMPLES>>
SAMPLES>>create table tab3(unique_id numeric(10) not null, item_code char(15) not null, data varchar(500), is_delete numeric(1), status_code char(2))
1. create table tab3(unique_id numeric(10) not null, item_code char(15) not null, data varchar(500), is_delete numeric(1), status_code char(2))
0 Rows Affected
statement prepare time: 0.0118s, elapsed execute time: 0.2405s.
---------------------------------------------------------------------------
SAMPLES>>
SAMPLES>>alter table tab3 add constraint tab3_pk primary key(unique_id)
3. alter table tab3 add constraint tab3_pk primary key(unique_id)
0 Rows Affected
statement prepare time: 0.0092s, elapsed execute time: 4.9465s.
---------------------------------------------------------------------------
SAMPLES>>
SAMPLES>>create table tab311 (unique_id numeric(10) not null,sub_item_code char(10),data varchar(500),is_delete numeric(1))
4. create table tab311 (unique_id numeric(10) not null,sub_item_code char(10),data varchar(500),is_delete numeric(1))
0 Rows Affected
statement prepare time: 0.0115s, elapsed execute time: 0.1953s.
---------------------------------------------------------------------------
SAMPLES>>create table tab31 (item_code char(15) not null,sub_item_code char(10),data varchar(500),is_delete numeric(1))
5. create table tab31 (item_code char(15) not null,sub_item_code char(10),data varchar(500),is_delete numeric(1))
0 Rows Affected
statement prepare time: 0.0133s, elapsed execute time: 0.1985s.
---------------------------------------------------------------------------
SAMPLES>>
SAMPLES>>alter table tab31 add constraint tab31_pk primary key(item_code)
7. alter table tab31 add constraint tab31_pk primary key(item_code)
0 Rows Affected
statement prepare time: 0.0102s, elapsed execute time: 0.2589s.
---------------------------------------------------------------------------
SAMPLES>>alter table tab311 add constraint tab311_pk primary key(unique_id)
8. alter table tab311 add constraint tab311_pk primary key(unique_id)
0 Rows Affected
statement prepare time: 0.0116s, elapsed execute time: 0.2627s.
---------------------------------------------------------------------------
SAMPLES>>create index tab311_ix on tab311(sub_item_code)
9. create index tab311_ix on tab311(sub_item_code)
0 Rows Affected
statement prepare time: 0.0111s, elapsed execute time: 0.2870s.
---------------------------------------------------------------------------
SAMPLES>>q
確認は管理ポータルで！（どこみればよいか分からなかったので楽な方法で..)
Oracleなら統計情報取得、他のRDBMSならアナライズ（昔はオラクルもアナライズだったんですけどね）、Cachéの世界では「テーブルチューニング」というそうな。
（管理ポータルから実行しています）
表と索引ができたのでデータ登録。
これまたOracleとは勝っても違うし、DSMの時代(DIGITAL STANDARD MUMPS)から大きく拡張されてるのでかなり辛いな〜MUMPS思い出すというより別物に近い感じw。あの当時はSQL使えねーしというより必要ねーし、だったしね (^^;;;
以下、データ作成中のログ..
グルグル回しているのは気にしないでねw
[oracle@pleco ˜]$ csession cache -U samples
ノード: pleco.macdeoracle.JP インスタンス: CACHE
SAMPLES>
SAMPLES>
SAMPLES>s rs=##class(%SQL.Statement).%ExecDirect(,"start transaction")
TL1:SAMPLES>s sql="insert into SQLUser.tab3 values(?,to_char((?#500000)+1,'FM099999999999999'),repeat('*',250-length(to_char(?)))||to_char(?),0,'00')"
TL1:SAMPLES>s stmt=##class(%SQL.Statement).%New() s stat=stmt.%Prepare(sql)
TL1:SAMPLES>f i=1:1:1000000 {s rs=stmt.%Execute(i,i,i,i)}
TL1:SAMPLES>s rs=##class(%SQL.Statement).%ExecDirect(,"commit")
SAMPLES>d $System.SQL.Shell()
SQL Command Line Shell
----------------------------------------------------
The command prefix is currently set to: <<nothing>>.
Enter q to quit, ? for help.
SAMPLES>>select count(1) from tab3
7. select count(1) from tab3
Aggregate_1
1000000
1 Rows(s) Affected
statement prepare time: 0.0009s, elapsed execute time: 2.6558s.
---------------------------------------------------------------------------
SAMPLES>>select min(unique_id) as "min", max(unique_id) as "max" from tab3
8. select min(unique_id) as "min", max(unique_id) as "max" from tab3
min max
1 1000000
1 Rows(s) Affected
statement prepare time: 0.1423s, elapsed execute time: 0.0006s.
---------------------------------------------------------------------------
SAMPLES>>select min(item_code) "min",max(item_code) "max" from tab3
9. select min(item_code) "min",max(item_code) "max" from tab3
min max
000000000000001 000000000500000
1 Rows(s) Affected
statement prepare time: 0.1324s, elapsed execute time: 3.4683s.
---------------------------------------------------------------------------
SAMPLES>>
SAMPLES>>q
SAMPLES>halt
[oracle@pleco ˜]$
SAMPLES>d $System.SQL.SetAutoCommit(2)
SAMPLES>s rs=##class(%SQL.Statement).%ExecDirect(,"start transaction")
TL1:SAMPLES>s sql="insert into SQLUser.tab31 values(to_char(?,'FM099999999999999'),to_char((?#500000)+1,'FM0999999999'),repeat('*',250-length(to_char(?)))||to_char(?),0)"
TL1:SAMPLES>s stmt=##class(%SQL.Statement).%New() s stat=stmt.%Prepare(sql)
TL1:SAMPLES>f i=1:1:2000000 {s rs=stmt.%Execute(i,i,i,i)}
TL1:SAMPLES>s rs=##class(%SQL.Statement).%ExecDirect(,"commit")
SAMPLES>d $System.SQL.Shell()
SQL Command Line Shell
----------------------------------------------------
The command prefix is currently set to: <<nothing>>.
Enter q to quit, ? for help.
SAMPLES>>select count(1) from tab31
2. select count(1) from tab31
Aggregate_1
2000000
1 Rows(s) Affected
statement prepare time: 0.1349s, elapsed execute time: 6.0684s.
---------------------------------------------------------------------------
SAMPLES>>select min(item_code) "item_code(MIN)",max(item_code) "item_code(MAX)" from tab31
3. select min(item_code) "item_code(MIN)",max(item_code) "item_code(MAX)" from tab31
item_code(MIN) item_code(MAX)
000000000000001 000000002000000
1 Rows(s) Affected
statement prepare time: 0.1361s, elapsed execute time: 0.0010s.
---------------------------------------------------------------------------
SAMPLES>>select min(sub_item_code) "sub_item_code(MIN)",max(sub_item_code) "sub_item_code(MAX)" from tab31
4. select min(sub_item_code) "sub_item_code(MIN)",max(sub_item_code) "sub_item_code(MAX)" from tab31
sub_item_code(MIN) sub_item_code(MAX)
0000000001 0000500000
1 Rows(s) Affected
statement prepare time: 0.1391s, elapsed execute time: 6.7417s.
---------------------------------------------------------------------------
SAMPLES>>q
SAMPLES>
SAMPLES>d $System.SQL.SetAutoCommit(2)
SAMPLES>s rs=##class(%SQL.Statement).%ExecDirect(,"start transaction")
TL1:SAMPLES>s sql="insert into SQLUser.tab311 values(?,to_char((?#500000)+1,'FM0999999999'),repeat('*',250-length(to_char(?)))||to_char(?),0)"
TL1:SAMPLES>s stmt=##class(%SQL.Statement).%New() s stat=stmt.%Prepare(sql)
TL1:SAMPLES>f i=1:1:2000000 {s rs=stmt.%Execute(i,i,i,i)}
TL1:SAMPLES>s rs=##class(%SQL.Statement).%ExecDirect(,"commit")
SAMPLES>d $System.SQL.Shell()
SQL Command Line Shell
----------------------------------------------------
The command prefix is currently set to: <<nothing>>.
Enter q to quit, ? for help.
SAMPLES>>select count(1) from tab311
5. select count(1) from tab311
Aggregate_1
2000000
1 Rows(s) Affected
statement prepare time: 0.0009s, elapsed execute time: 3.7845s.
---------------------------------------------------------------------------
SAMPLES>>select min(sub_item_code) "sub_item_code(MIN)", max(sub_item_code) "sub_item_code(MAX)" from tab311
6. select min(sub_item_code) "sub_item_code(MIN)", max(sub_item_code) "sub_item_code(MAX)" from tab311
sub_item_code(MIN) sub_item_code(MAX)
0000000001 0000500000
1 Rows(s) Affected
statement prepare time: 0.1558s, elapsed execute time: 0.0004s.
---------------------------------------------------------------------------
SAMPLES>>q
本題からはずれるのですが... SQL使ってるとはいっても、ベースはMUMPSというかM言語だからね〜というお話を。。
tab3表及び主キー制約で作成された索引に対応するglobals。索引を見るとSparse Multidimensional Arrayをうまく利用しているのがよくわかる :)
Cachéの表や索引の実態はSparse Multidimensional Arrayなんだお。
1: ^User.tab3D = 1512812
2: ^User.tab3D(512813) = $lb("",1,"000000000000002","***************** ...中略... *******************************1",0,"00")
3: ^User.tab3D(512814) = $lb("",2,"000000000000003","***************** ...中略... *******************************2",0,"00")
4: ^User.tab3D(512815) = $lb("",3,"000000000000004","***************** ...中略... *******************************3",0,"00")
...以下略...
tab3表の主キー索引
1: ^User.tab3I("tab3pk",1,512813) = ""
2: ^User.tab3I("tab3pk",2,512814) = ""
3: ^User.tab3I("tab3pk",3,512815) = ""
...以下略...
次は、tab31表と主キー索引
1: ^User.tab31D = 2000000
2: ^User.tab31D(1) = $lb("","000000000000001","0000000002","***************** ...中略... *************************1",0)
3: ^User.tab31D(2) = $lb("","000000000000002","0000000003","***************** ...中略... *************************2",0)
4: ^User.tab31D(3) = $lb("","000000000000003","0000000004","***************** ...中略... *************************3",0)
...以下略...
1: ^User.tab31I("tab31pk"," 000000000000001",1) = ""
2: ^User.tab31I("tab31pk"," 000000000000002",2) = ""
3: ^User.tab31I("tab31pk"," 000000000000003",3) = ""
...以下略...
最後に、tab311表と主キー索引及び、非ユニーク索引に対応したSparse Multidimensional Arrayの内容
1: ^User.tab311D = 2000000
2: ^User.tab311D(1) = $lb("",1,"0000000002","********************************* ...中略... ************************1",0)
3: ^User.tab311D(2) = $lb("",2,"0000000003","********************************* ...中略... ************************2",0)
4: ^User.tab311D(3) = $lb("",3,"0000000004","********************************* ...中略... ************************3",0)
...以下略...
1: ^User.tab311I("tab311ix"," 0000000001",500000) = ""
2: ^User.tab311I("tab311ix"," 0000000001",1000000) = ""
3: ^User.tab311I("tab311ix"," 0000000001",1500000) = ""
4: ^User.tab311I("tab311ix"," 0000000001",2000000) = ""
5: ^User.tab311I("tab311ix"," 0000000002",1) = ""
6: ^User.tab311I("tab311ix"," 0000000002",500001) = ""
7: ^User.tab311I("tab311ix"," 0000000002",1000001) = ""
8: ^User.tab311I("tab311ix"," 0000000002",1500001) = ""
9: ^User.tab311I("tab311ix"," 0000000003",2) = ""
10: ^User.tab311I("tab311ix"," 0000000003",500002) = ""
11: ^User.tab311I("tab311ix"," 0000000003",1000002) = ""
12: ^User.tab311I("tab311ix"," 0000000003",1500002) = ""
13: ^User.tab311I("tab311ix"," 0000000004",3) = ""
14: ^User.tab311I("tab311ix"," 0000000004",500003) = ""
15: ^User.tab311I("tab311ix"," 0000000004",1000003) = ""
16: ^User.tab311I("tab311ix"," 0000000004",1500003) = ""
...以下略...
実行するクエリはOracle/MySQL/PostgreSQLで実行したものと同じです。
select
t1.unique_id,
t1.item_code,
(
select
max(t3.unique_id)
from
tab31 t2 join tab311 t3
on
t2.sub_item_code = t3.sub_item_code
and t3.is_delete = 0
where
t2.item_code = t1.item_code
and t2.is_delete = 0
) current_sub_item
from
tab3 t1
where
t1.unique_id between 1 and 10000
and t1.is_delete = 0
and t1.status_code = '00'
以下、管理ポータルのSQL実行プラン表示で取得した実行計画とコスト。
Index Only AccessではないのでCacheのSQLの世界で表アクセスを意味するオペレーションである、Read Master mapがスカラー問合せ部分に現れている。
ちなみに、索引アクセスを意味するオペレーションは、Read index mapのようだ。
実行プランが以下に表示されます:
クエリ文字列
SELECT t1 . unique_id , t1 . item_code , ( SELECT MAX ( t3 . unique_id ) FROM tab31 t2 JOIN tab311 t3 ON t2 . sub_item_code = t3 . sub_item_code AND t3 .
is_delete = ? WHERE t2 . item_code = t1 . item_code AND t2 . is_delete = ? ) current_sub_item FROM tab3 t1 WHERE t1 . unique_id BETWEEN ? AND ? AND t1 .
is_delete = ? AND t1 . status_code = ?
クエリプラン
相対コスト = 74082
Call module B, which populates bitmap temp-file A.
Read bitmap temp-file A, looping on ID.
For each row:
Read master map SQLUser.tab3.IDKEY, using the given idkey value.
Output the row.
module B
Read index map SQLUser.tab3.tab3_pk, looping on unique_id (with a range condition) and ID.
For each row:
Add ID bit to bitmap temp-file A.
subquery
Call module E.
Determine subquery result.
module E
Read index map SQLUser.tab31.tab31_pk, using the given %SQLUPPER(item_code), and looping on ID.
For each row:
Read master map SQLUser.tab31.IDKEY, using the given idkey value.
Read index map SQLUser.tab311.tab311_ix, using the given %SQLUPPER(sub_item_code), and looping on ID.
For each row:
Read master map SQLUser.tab311.IDKEY, using the given idkey value.
Accumulate the max(unique_id).
では、Index Only Accessによるチューニング。 Covering Indexを２つ作成します。(Oracle/MySQL/PostgreSQLで作成した索引と同じ名称にしてあります）
[oracle@pleco ˜]$ csession cache -U samples
ノード: pleco.macdeoracle.JP インスタンス: CACHE
SAMPLES>d $System.SQL.Shell()
SQL Command Line Shell
----------------------------------------------------
The command prefix is currently set to: <<nothing>>.
Enter q to quit, ? for help.
SAMPLES>>create index tab31_demo_ix on tab31(item_code, is_delete, sub_item_code)
1. create index tab31_demo_ix on tab31(item_code, is_delete, sub_item_code)
0 Rows Affected
statement prepare time: 4.8277s, elapsed execute time: 9.6483s.
---------------------------------------------------------------------------
SAMPLES>>create index tab311_demo_ix on tab311(sub_item_code, is_delete, unique_id)
2. create index tab311_demo_ix on tab311(sub_item_code, is_delete, unique_id)
0 Rows Affected
statement prepare time: 0.0130s, elapsed execute time: 8.6777s.
---------------------------------------------------------------------------
SAMPLES>>
索引は正しく作成されています。（管理ポータルから確認した結果）
お〜〜〜〜〜っ！。 CacheのSQLワールドでもIndex Only Accessになってる〜〜〜。 （ヒントのような仕組みはないようなのですが、狙い通りの索引が利用されていますね）
スカラー副問合せの実行計画から Read Master Map（表に対応するSparse Multidimensional Array）をアクセスする操作が消え、Read index Map（索引に対応するSparse Multidimensional Array）をアクセスする操作だけになっていることが確認できた。 :)
以下、管理ポータルの「SQL実行」＞「実行計画」で取得した実行計画
クエリプラン
相対コスト = 74082
Call module B, which populates bitmap temp-file A.
Read bitmap temp-file A, looping on ID.
For each row:
Read master map SQLUser.tab3.IDKEY, using the given idkey value.
Output the row.
module B
Read index map SQLUser.tab3.tab3_pk, looping on unique_id (with a range condition) and ID.
For each row:
Add ID bit to bitmap temp-file A.
subquery
Call module E.
Determine subquery result.
module E
Read index map SQLUser.tab31.tab31_demo_ix, using the given %SQLUPPER(item_code) and is_delete, and looping on %SQLUPPER(sub_item_code) and ID.
For each row:
Read index map SQLUser.tab311.tab311_demo_ix, using the given %SQLUPPER(sub_item_code) and is_delete, and looping on unique_id and ID.
For each row:
Accumulate the max(unique_id).
| 固定リンク | 0
コメント