« VirtualBox 4.1.20 released! | トップページ | VirtualBox 4.1.22 、 4.2近くに来てるし〜:) »

2012年9月 2日 (日)

Index Only Accessネタのおまけのおまけ

Caché de Index Only Access できるのか?

CachéってSQLもサポートしてるので、Index Only Accessサポートしてるのかな〜と素朴な疑問から調べてみたもののマニュアルには記載されていない。
書かれていなきゃどうなるか、試すのが手っ取り早いですね。
(ダウンロードするにはフォームからリクエストする必要があります。1セッション限定の試用ライセンスのようです。他の制限は該当ページ読んでくださいな。)


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

確認は管理ポータルで!(どこみればよいか分からなかったので楽な方法で..)

Desc_tab3

Tab3_indexes_no_index_only

Desc_tab31

Tab31_indexes_no_index_only

Desc_tab311

Tab311_indexes_no_index_only_scan

Oracleなら統計情報取得、他のRDBMSならアナライズ(昔はオラクルもアナライズだったんですけどね)、Cachéの世界では「テーブルチューニング」というそうな。
(管理ポータルから実行しています)
Table_analyze

Tab31_analyze

Tab311_analyze

表と索引ができたのでデータ登録。
これまた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を2つ作成します。(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>>

索引は正しく作成されています。(管理ポータルから確認した結果)
Tab31_indexes_index_only

Tab311_indexes_inde_only

お〜〜〜〜〜っ!。 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).

|

トラックバック

この記事のトラックバックURL:
http://app.cocolog-nifty.com/t/trackback/106341/55566730

この記事へのトラックバック一覧です: Index Only Accessネタのおまけのおまけ:

コメント

コメントを書く