2019年9月30日 (月)

なぜ、そこに、LONG型があるんだ / FAQ

all/dba/user_tab_columns

https://docs.oracle.com/cd/E82638_01/refrn/ALL_TAB_COLUMNS.html#GUID-F218205C-7D76-4A83-8691-BFD2AD372B63

これらのビューは、列の属性関連の情報を持つビューです。
たまに、便利なびゅーではあるのですが、これらのビューをアクセスする使うスクリプトというかPL/SQLでコード書くこともあるのですが、一箇所だけ、使いにくいところがあります。

 

どこかわかります?

下位互換のためだろうと思われるのですが、一般には推奨されていない LONG型の列 が残っています。

ご存知だとは思いますが、一般的なガイドだと、CLOBの利用が推奨されています。
下位互換のためだから仕方ないのだとは思うのですが。

LONG型といえば、とにかく制約が多くて、文字列操作を行うにもめんどくさいわけで、実際に利用したい状況になると、うううううっとなることしばしば。

LONG型

で、普段どうやって、その面倒くさいところを回避しているかといえば、CLOBに変換してしまうことがが多いです。
CLOBにしてしまえば、沢山の制約から解放されますしね :)

以下のような感じで。


SCOTT> l
1 CREATE TABLE my_dba_tab_columns
2 AS
3 SELECT
4 owner
5 ,table_name
6 ,column_name
7 ,data_type
8 ,data_type_mod
9 ,data_type_owner
10 ,data_length
11 ,data_precision
12 ,data_scale
13 ,nullable
14 ,column_id
15 ,TO_CLOB(default_length) AS default_length
16 ,num_distinct
17 ,low_value
18 ,high_value
19 ,density
20 ,num_nulls
21 ,num_buckets
22 ,last_analyzed
23 ,sample_size
24 ,character_set_name
25 ,char_col_decl_length
26 ,global_stats
27 ,user_stats
28 ,avg_col_len
29 ,char_length
30 ,char_used
31 ,v80_fmt_image
32 ,data_upgraded
33 ,histogram
34 ,default_on_null
35 ,identity_column
36 ,sensitive_column
37 ,evaluation_edition
38 ,unusable_before
39 ,unusable_beginning
40 ,collation
41 FROM
42* dba_tab_columns
SCOTT> /

Table created.

SCOTT> desc dba_tab_columns
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(128)
TABLE_NAME NOT NULL VARCHAR2(128)
COLUMN_NAME NOT NULL VARCHAR2(128)
DATA_TYPE VARCHAR2(128)
DATA_TYPE_MOD VARCHAR2(3)
DATA_TYPE_OWNER VARCHAR2(128)
DATA_LENGTH NOT NULL NUMBER
DATA_PRECISION NUMBER
DATA_SCALE NUMBER
NULLABLE VARCHAR2(1)
COLUMN_ID NUMBER
DEFAULT_LENGTH NUMBER
DATA_DEFAULT LONG
NUM_DISTINCT NUMBER
LOW_VALUE RAW(2000)
HIGH_VALUE RAW(2000)
DENSITY NUMBER
NUM_NULLS NUMBER
NUM_BUCKETS NUMBER
LAST_ANALYZED DATE
SAMPLE_SIZE NUMBER
CHARACTER_SET_NAME VARCHAR2(44)
CHAR_COL_DECL_LENGTH NUMBER
GLOBAL_STATS VARCHAR2(3)
USER_STATS VARCHAR2(3)
AVG_COL_LEN NUMBER
CHAR_LENGTH NUMBER
CHAR_USED VARCHAR2(1)
V80_FMT_IMAGE VARCHAR2(3)
DATA_UPGRADED VARCHAR2(3)
HISTOGRAM VARCHAR2(15)
DEFAULT_ON_NULL VARCHAR2(3)
IDENTITY_COLUMN VARCHAR2(3)
SENSITIVE_COLUMN VARCHAR2(3)
EVALUATION_EDITION VARCHAR2(128)
UNUSABLE_BEFORE VARCHAR2(128)
UNUSABLE_BEGINNING VARCHAR2(128)
COLLATION VARCHAR2(100)

SCOTT> desc my_dba_tab_columns
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(128)
TABLE_NAME NOT NULL VARCHAR2(128)
COLUMN_NAME NOT NULL VARCHAR2(128)
DATA_TYPE VARCHAR2(128)
DATA_TYPE_MOD VARCHAR2(3)
DATA_TYPE_OWNER VARCHAR2(128)
DATA_LENGTH NOT NULL NUMBER
DATA_PRECISION NUMBER
DATA_SCALE NUMBER
NULLABLE VARCHAR2(1)
COLUMN_ID NUMBER
DEFAULT_LENGTH CLOB
NUM_DISTINCT NUMBER
LOW_VALUE RAW(2000)
HIGH_VALUE RAW(2000)
DENSITY NUMBER
NUM_NULLS NUMBER
NUM_BUCKETS NUMBER
LAST_ANALYZED DATE
SAMPLE_SIZE NUMBER
CHARACTER_SET_NAME VARCHAR2(44)
CHAR_COL_DECL_LENGTH NUMBER
GLOBAL_STATS VARCHAR2(3)
USER_STATS VARCHAR2(3)
AVG_COL_LEN NUMBER
CHAR_LENGTH NUMBER
CHAR_USED VARCHAR2(1)
V80_FMT_IMAGE VARCHAR2(3)
DATA_UPGRADED VARCHAR2(3)
HISTOGRAM VARCHAR2(15)
DEFAULT_ON_NULL VARCHAR2(3)
IDENTITY_COLUMN VARCHAR2(3)
SENSITIVE_COLUMN VARCHAR2(3)
EVALUATION_EDITION VARCHAR2(128)
UNUSABLE_BEFORE VARCHAR2(128)
UNUSABLE_BEGINNING VARCHAR2(128)
COLLATION VARCHAR2(100)

 


db tech showcase 2019もおわり、今年も残すところ 3ヶ月あまり。一年早い. そして。
来週は、開催時期を秋に変更してから2回目の多摩川花火大会。天気がよいといいのですが:)

ではまた。

| | コメント (0)

2019年8月25日 (日)

FAQ / PL/SQL PACKAGEでパプリックスコープを持つ定数をSQL文中で利用するには...

かなーり、ご無沙汰しておりました。(本業でいっぱいいっぱいで、という言い訳はこれぐらいにしてw) 偶に聞かれることがあるので、FAQネタから。 パッケージでパブリックなスコープを持つ定数は無名PL/SQLブロックやパッケージ、プロシージャ、ファンクションでしか参照できないんですよねー 例えば、DBMS_CRYPTOパッケージでHASHファンクションを利用してSH-256を作成したいなーと思って、

39.4 DBMS_CRYPTOのアルゴリズム
https://docs.oracle.com/cd/F19136_01/arpls/DBMS_CRYPTO.html#GUID-CE3CF17D-E781-47CB-AEE7-19A9B2BCD3EC
DBMS_CRYPTO.HASH()は関数なのでSQL文から呼びたーい、と以下のような使い方をすると...

SQL> SELECT DBMS_CRYPTO.HASH(TO_CLOB('hoge'), DBMS_CRYPTO.HASH_SH2569) AS "SH-256" FROM dual;
SELECT DBMS_CRYPTO.HASH(TO_CLOB('hoge'), DBMS_CRYPTO.HASH_SH2569) AS "SH-256" FROM dual
*
ERROR at line 1:
ORA-00904: "DBMS_CRYPTO"."HASH_SH2569": invalid identifier


SQL>
SQL> select DBMS_CRYPTO.HASH_SH256 from dual;
select DBMS_CRYPTO.HASH_SH256 from dual
*
ERROR at line 1:
ORA-06553: PLS-221: 'HASH_SH256' is not a procedure or is undefined
見事にエラーとなるわけです。 DBMS_CRYPTO.HASH_SH256は、パッケージファンクションではないので...利用可能なのはPL/SQLでのみ。
SQL> set serveroutput on
SQL>
¥SQL>
SQL> begin
2 dbms_output.put_line('DBMS_CRYPTO.HASH_SH256 : ' || DBMS_CRYPTO.HASH_SH256);
3 end;
4 /
DBMS_CRYPTO.HASH_SH256 : 4

PL/SQL procedure successfully completed.
SQL文で活用する為には、ファンクションでラップする必要があります。 以下のように。
SQL> l
1 CREATE OR REPLACE FUNCTION get_hash_sh256_type
2 RETURN NUMBER
3 AS
4 BEGIN
5 RETURN DBMS_CRYPTO.HASH_SH256;
6* END;
SQL> /

Function created.

SQL>
冒頭でエラーとなっていたSQL文をDBMS_CRYPTO.HASH_SH256を返すファンクションを使うように書き換えると、 はい、できました。
SQL> l
1 SELECT
2 DBMS_CRYPTO.HASH(
3 TO_CLOB('hoge')
4 , get_hash_sh256_type()
5 ) AS "SH-256"
6 FROM
7* dual
SQL> /

SH-256
--------------------------------------------------------------------------------
ECB666D778725EC97307044D642BF4D160AABB76F56C0069C71EA25B1E926825

SQL>


露店の焼きそばと焼き鳥を食べつつ、晩夏の夏祭りと、涼しい朝晩の気温で熟睡可能な山形より。 では、では。

| | コメント (0)

2019年3月22日 (金)

ORA_HASH()を使ってリストパーティションにハッシュパーティションのような均一配分を

ハッシュパーティションってリストパーティションみたいにパーティション狙い撃ちできて、かつ、ハッシュパーティションみたいに、データをパーティション間で均一化できないのかなぁ
というずいぶん昔の話を思い出して、そう言えば書いてないかもしれない。いつの話だよってぐらい昔の話だけどw
どうやったかというと、
ハッシュキーにふさわしい値をもつ列を決める(一意キーとか主キー列が理想、カーディナリティの低い、分布に偏りのあるデータを持つ列は使わない)

で、ハッシュキーが決まったら、話は早くて、ORA_HASH()関数で取得できるハッシュ値を利用したリストパーティションを作成するだけ。
ゴニョゴニュ言わなくても、SQLとPL/SQLのコードを見ていただければ、理解していただけるかと。

ORCL@SCOTT> l
1 CREATE TABLE list_p_tab
2 (
3 id_code VARCHAR2(10) NOT NULL
4 , foo VARCHAR2(30)
5 , id_code_hash_value NUMBER(2) NOT NULL
6 )
7 PARTITION BY LIST (id_code_hash_value)
8 (
9 PARTITION list_p_tab_p1 VALUES(0)
10 ,PARTITION list_p_tab_p2 VALUES(1)
11 ,PARTITION list_p_tab_p3 VALUES(2)
12 ,PARTITION list_p_tab_p4 VALUES(3)
13* )
ORCL@SCOTT> /

Table created.

ORCL@SCOTT> ALTER TABLE list_p_tab ADD CONSTRAINT gpk_list_p_tab PRIMARY KEY(id_code) USING INDEX GLOBAL;

Table altered.

ORCL@SCOTT> l
1 DECLARE
2 TYPE id_code_t IS TABLE OF list_p_tab.id_code%TYPE INDEX BY PLS_INTEGER;
3 TYPE foo_t IS TABLE OF list_p_tab.foo%TYPE INDEX BY PLS_INTEGER;
4 id_codes id_code_t;
5 foos foo_t;
6 k PLS_INTEGER := 1;
7 BEGIN
8 FOR i IN 1..400000 LOOP
9 id_codes(k) := TO_CHAR(i,'fm0000000009');
10 foos(k) := i;
11 k := k + 1;
12 IF k > 1000 THEN
13 FORALL j in 1..k-1
14 INSERT INTO list_p_tab VALUES(id_codes(j), foos(j), ORA_HASH(id_codes(j),3));
15 COMMIT;
16 k := 1;
17 END IF;
18 END LOOP;
19* END;
ORCL@SCOTT> /

PL/SQL procedure successfully completed.

Elapsed: 00:00:12.28

ORCL@SCOTT> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'LIST_P_TAB',granularity=>'ALL',cascade=>true,no_invalidate=>false,degree=>4);

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.51

ORCL@SCOTT> r
1 select
2 table_name
3 ,partition_name
4 ,num_rows
5 from
6 user_tab_partitions
7 where
8 table_name = 'LIST_P_TAB'
9 order by
10* 1,2

TABLE_NAME PARTITION_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
LIST_P_TAB LIST_P_TAB_P1 99901
LIST_P_TAB LIST_P_TAB_P2 100194
LIST_P_TAB LIST_P_TAB_P3 100056
LIST_P_TAB LIST_P_TAB_P4 99849

ORCL@SCOTT> select id_code_hash_value,count(1) from list_p_tab group by id_code_hash_value order by 1;

ID_CODE_HASH_VALUE COUNT(1)
------------------ ----------
0 99901
1 100194
2 100056
3 99849

Elapsed: 00:00:00.06

ORCL@SCOTT> explain plan for
2 select
3 *
4 from
5 list_p_tab
6 where
7 id_code_hash_value = 1;

Explained.

Elapsed: 00:00:00.10
ORCL@SCOTT> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2143708561

----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K| 2054K| 275 (1)| 00:00:01 | | |
| 1 | PARTITION LIST SINGLE| | 100K| 2054K| 275 (1)| 00:00:01 | 2 | 2 |
| 2 | TABLE ACCESS FULL | LIST_P_TAB | 100K| 2054K| 275 (1)| 00:00:01 | 2 | 2 |
----------------------------------------------------------------------------------------------------

グローバル索引を作成してあるので、パーティション関係ない検索は主キー索引経由でも可。

ORCL@SCOTT> explain plan for select * from list_p_tab where id_code = '00004000000';

Explained.

Elapsed: 00:00:00.02
ORCL@SCOTT> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------
Plan hash value: 4132161764

---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 3 (0)| 00:00:01 | | |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| LIST_P_TAB | 1 | 21 | 3 (0)| 00:00:01 | ROWID | ROWID |
|* 2 | INDEX UNIQUE SCAN | GPK_LIST_P_TAB | 1 | | 2 (0)| 00:00:01 | | |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID_CODE"='00004000000')

| | コメント (0)

2019年3月21日 (木)

in-memory関連の謎パラメータ 18c

12cR2と18cのin-memory関連の隠しパラメータ以外の違い。
Database Reference Release 18
inmemory_optimized_arithmeticってパラメータ、SIMD向け最適化っぽい(デフォがDESABLEDみたいだが)
inmemory_prefer_xmem_memcompress と inmemory_prefer_xmem_priority inmemory_xmem_sizeこれらのパラメータ、隠しパラメータじゃないのにundocumented なのはなぜ?
もしかして、xmem って略語、Exadataの資料でヒットした。。。Exadata オンリー? なやつかな?
BANNER_LEGACY                                                                   BANNER                                                                          
------------------------------------------------------------------------------- -------------------------------------------------------------------------------
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

ORCL@SYS> show parameter memory orcl12c@SYS> show parameter memory

NAME TYPE VALUE NAME TYPE VALUE
------------------------------------ ----------- ------------------------------ ------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0 hi_shared_memory_address integer 0
inmemory_adg_enabled boolean TRUE inmemory_adg_enabled boolean TRUE
inmemory_automatic_level string OFF
inmemory_clause_default string inmemory_clause_default string
inmemory_expressions_usage string ENABLE inmemory_expressions_usage string ENABLE
inmemory_force string DEFAULT inmemory_force string DEFAULT
inmemory_max_populate_servers integer 0 inmemory_max_populate_servers integer 0
inmemory_optimized_arithmetic string DISABLE
inmemory_prefer_xmem_memcompress string
inmemory_prefer_xmem_priority string
inmemory_query string ENABLE inmemory_query string ENABLE
inmemory_size big integer 0 inmemory_size big integer 0
inmemory_trickle_repopulate_servers_ integer 1 inmemory_trickle_repopulate_servers_ integer 1
percent percent
inmemory_virtual_columns string MANUAL inmemory_virtual_columns string MANUAL
inmemory_xmem_size big integer 0
memory_max_target big integer 0 memory_max_target big integer 0
memory_target big integer 0 memory_target big integer 0
optimizer_inmemory_aware boolean TRUE optimizer_inmemory_aware boolean TRUE
shared_memory_address integer 0 shared_memory_address integer 0

| | コメント (0)

ハッシュパーティションでのデータの偏り / FAQ

ぼやき漫才みたいな感じですが、Oracleに限らず、ハッシュパーティションでパーティション間のデータを均一にしたいなら、ユニークな値かそれに準ずる列を選ぶべきなわけですが、なにを間違ってしまったのか、稀ではありますが、少々残念なことになっていすることもあります。
とは言え、早めに気づけば影響も小さくて済むわけで:)


というわけで、今回はそんなおはなし。

id_code列の値はユニーク
ORCL@SCOTT> select count(*),count(distinct id_code) from org;

COUNT(*) COUNT(DISTINCTID_CODE)
---------- ----------------------
400000 400000
type列の値は、一意性がなくカーディナリティーも低い、かつ、大きな偏りがある。。
ORCL@SCOTT> select type,count(1) from org group by type;

type COUNT(1)
---------- ----------
1 60000
2 60000
9 60000
0 220000
ハッシュパーティションを選択する主な理由は、パーティションへのデータの均一分散なので、列の値がユニークな列をパーティションキーとしてパーティション化することが多いわけですが、。。
以下は、ハッシュキーに一意な値を持つ列を選択した場合の例
ORCL@SCOTT> r
1 create table hash_p_tab
2 partition by hash(id_code)
3 (
4 partition hash_p_tab_p1
5 ,partition hash_p_tab_p2
6 ,partition hash_p_tab_p3
7 ,partition hash_p_tab_p4
8 )
9 as select
10 id_code
11 ,foo
12 ,type
13 from
14* org

Table created.

ORCL@SCOTT> alter table hash_p_tab add constraint gpk_hash_p_tab primary key(id_code) using index global;

Table altered.

ORCL@SCOTT> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'hash_p_tab',cascade=>true,no_invalidate=>false,granularity=>'ALL');


ORCL@SCOTT> select table_name,partition_name,num_rows from user_tab_partitions order by 1,2;

TABLE_NAME PARTITION_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
HASH_P_TAB HASH_P_TAB_P1 99901
HASH_P_TAB HASH_P_TAB_P2 100194
HASH_P_TAB HASH_P_TAB_P3 100056
HASH_P_TAB HASH_P_TAB_P4 99849


しかし、値の分布に偏りのあるカーディナリティの低い列を選んで残念なことになっているケースも稀にあったりします。

なぜ、ハッシュキーにこの列を選んだんだ! みたいな。。。

そんな時は、設計した人に聞くしかないです。何がやりたかったのかを。。。私に聞かれてもハッシュキーの選択をミスったんですよねーたぶん、としか言えないので。
ORCL@SCOTT> r
1 create table hash_p_tab_skew
2 partition by hash(type)
3 (
4 partition hash_p_tab_skew_p1
5 ,partition hash_p_tab_skew_p2
6 ,partition hash_p_tab_skew_p3
7 ,partition hash_p_tab_skew_p4
8 )
9 as select
10 id_code
11 ,foo
12 ,type
13 from
14* org

Table created.

ORCL@SCOTT> alter table hash_p_tab_skew add constraint gpk_hash_p_tab_skew primary key(id_code) using index global;

Table altered.

ORCL@SCOTT> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'hash_p_tab_skew',cascade=>true,no_invalidate=>false,granularity=>'ALL');

PL/SQL procedure successfully completed.

ORCL@SCOTT> select table_name,partition_name,num_rows from user_tab_partitions where table_name = upper('hash_p_tab_skew') order by 1,2

TABLE_NAME PARTITION_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
HASH_P_TAB_SKEW HASH_P_TAB_SKEW_P1 0
HASH_P_TAB_SKEW HASH_P_TAB_SKEW_P2 280000
HASH_P_TAB_SKEW HASH_P_TAB_SKEW_P3 60000
HASH_P_TAB_SKEW HASH_P_TAB_SKEW_P4 60000
で、データを均一にパーティションに分散させることをなんとなーく、イメージしつつ、上記のようなミスをしてしまうと、パラレルクエリーなどで最もデータの多いパーティションの処理時間に引っ張られて想定より処理時間が長くなるなんてことも。。。あるわけで
例がイケてないけど(気にしないでw)、パラレルサーバーのスレーブ間でbuffer gets部分を見ていただくと偏りはわかりやすいはず。(赤字部分):)
偏りあり
orcl2@SCOTT> @sql_skew
1 select
2 /*+
3 monitor
4 parallel(4)
5 */
6 count(1)
7 from
8 hash_p_tab_skew t01
9 inner join hash_p_tab_skew2 t02
10 on
11 t01.id_code = t02.id_code
12* and t01.type = t02.type


Parallel Execution Details (DOP=4 , Servers Allocated=4)
==========================================================================================
| Name | Type | Server# | Elapsed | Cpu | Other | Buffer | Wait Events |
| | | | Time(s) | Time(s) | Waits(s) | Gets | (sample #) |
==========================================================================================
| PX Coordinator | QC | | 0.02 | 0.00 | 0.02 | 72 | |
| p000 | Set 1 | 1 | 0.18 | 0.17 | 0.01 | 1940 | |
| p001 | Set 1 | 2 | 0.05 | 0.05 | 0.00 | 448 | |
| p002 | Set 1 | 3 | 0.05 | 0.05 | 0.00 | 448 | |
| p003 | Set 1 | 4 | 0.00 | 0.00 | 0.00 | | |
==========================================================================================

偏りの悪影響のイメージはこんな感じ
20190321-165450
20190321-165622

偏りなし
orcl2@SCOTT> @sql_noskew
1 select
2 /*+
3 monitor
4 parallel(4)
5 */
6 count(1)
7 from
8 hash_p_tab t01
9 inner join hash_p_tab2 t02
10 on
11 t01.id_code = t02.id_code
12* and t01.type = t02.type


Parallel Execution Details (DOP=4 , Servers Allocated=4)
==========================================================================================
| Name | Type | Server# | Elapsed | Cpu | Other | Buffer | Wait Events |
| | | | Time(s) | Time(s) | Waits(s) | Gets | (sample #) |
==========================================================================================
| PX Coordinator | QC | | 0.01 | 0.00 | 0.00 | 96 | |
| p000 | Set 1 | 1 | 0.08 | 0.08 | | 714 | |
| p001 | Set 1 | 2 | 0.08 | 0.08 | 0.00 | 714 | |
| p002 | Set 1 | 3 | 0.08 | 0.07 | 0.01 | 714 | |
| p003 | Set 1 | 4 | 0.08 | 0.08 | | 712 | |
==========================================================================================
パーティション毎にぞれぞれ得手不得手があります。そこんとこを把握したうえで、有効活用したいものですよね。(パーティションもいろいろ進化してきて便利になってきたわけですが、その分わかりにくいところも増えてきて理解するのに大変だったり 18cのも差分は把握しといたほうがいいかw...:)

| | コメント (0)

Join Elimination(結合の排除)と 参照整合性制約 / FAQ

偶に聞かれることがあるので、再び、Join Elimination(結合の排除)について
まずは、以下のSQL文を。
order表とcustomers表をinner joinしている単純な文ですが、重要なのは、実行計画の方!


order表とcustomers表をinner joinしているのに、order表だけ(この場合、order表の主キー索引だけのIndex Only Scanになっていますが)で、customes表を結合していせん。

理由は単純で、以下のSQL文では、customsers表の結合が不要なだけなんです。なぜかわかりますか?
以前、浅瀬でジャブジャブしていたセッション資料にヒントがあります。
order表に定義されている参照整合性制約によりcustomer_idがcustomsers表に存在していることを確認するための結合は不要と、オプティマイザーが判断した結果なんですよね。これ。
上記以外のケースでも無駄な結合を排除しようとする最適化を行うことがあります。内部的にはSQL文を書き換えてくれているわけですね。無駄に結合を行わないために。。。10053トレースをとって、 Join Elimination で grep をかけてみるとオプティマイザの気持ちが見えてきます:)
ORCL@OE> explain plan for
2 select
3 distinct
4 order_id
5 from
6 orders o
7 , customers c
8 where
9 o.customer_id = c.customer_id
10 and order_id < 2400;

Explained.

ORCL@OE> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
Plan hash value: 1653993310

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 46 | 184 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| ORDER_PK | 46 | 184 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("ORDER_ID"<2400)
上記、SQL文は、参照整合性制約により、orders表に存在するcustomer_idがcustomers表に存在することが保証されているため、結合により存在確認が不要となり、Optimizerは内部的にSQL文を以下のように書き換えたということになります。賢いですよね。
ORCL@OE> r
1 explain plan for
2 select
3 distinct
4 order_id
5 from
6 orders o
7 where
8* order_id < 2400

Explained.

ORCL@OE> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
Plan hash value: 1653993310

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 46 | 184 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| ORDER_PK | 46 | 184 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("ORDER_ID"<2400)

13 rows selected.


order表の参照整合性制約を確認しておきます。

ORCL@OE> r
1 select
2 table_name
3 ,owner
4 ,constraint_name
5 ,constraint_type
6 ,r_owner
7 ,r_constraint_name
8 ,status
9 ,rely
10 from
11 user_constraints
12 where
13* constraint_type='R'

TABLE_NAME OWNER CONSTRAINT_NAME C R_OWNER R_CONSTRAINT_NAME STATUS RELY
------------------------------ ------------------------------ ------------------------------ - ------------------------------ ------------------------------ -------- ----
ORDERS OE ORDERS_CUSTOMER_ID_FK R OE CUSTOMERS_PK ENABLED
INVENTORIES OE INVENTORIES_WAREHOUSES_FK R OE WAREHOUSES_PK ENABLED
INVENTORIES OE INVENTORIES_PRODUCT_ID_FK R OE PRODUCT_INFORMATION_PK ENABLED
ORDER_ITEMS OE ORDER_ITEMS_ORDER_ID_FK R OE ORDER_PK ENABLED
ORDER_ITEMS OE ORDER_ITEMS_PRODUCT_ID_FK R OE PRODUCT_INFORMATION_PK ENABLED
PRODUCT_DESCRIPTIONS OE PD_PRODUCT_ID_FK R OE PRODUCT_INFORMATION_PK ENABLED

ORCL@OE> r
1 select
2 table_name
3 ,column_name
4 ,constraint_name
5 from
6 user_cons_columns
7 where
8 table_name in ('ORDERS','CUSTOMERS')
9 order by
10* table_name

TABLE_NAME COLUMN_NAME CONSTRAINT_NAME
------------------------------ ------------------------------ ------------------------------
CUSTOMERS CUSTOMER_ID CUSTOMERS_PK
CUSTOMERS CUST_FIRST_NAME CUST_FNAME_NN
CUSTOMERS CUSTOMER_ID CUSTOMER_ID_MIN
CUSTOMERS CREDIT_LIMIT CUSTOMER_CREDIT_LIMIT_MAX
CUSTOMERS CUST_LAST_NAME CUST_LNAME_NN
ORDERS ORDER_ID ORDER_PK
ORDERS ORDER_TOTAL ORDER_TOTAL_MIN
ORDERS ORDER_MODE ORDER_MODE_LOV
ORDERS CUSTOMER_ID ORDER_CUSTOMER_ID_NN
ORDERS CUSTOMER_ID ORDERS_CUSTOMER_ID_FK
ORDERS ORDER_DATE ORDER_DATE_NN




Oracle SQL DeveloperでリバースエンジニアリングしたERDは以下のとおり

20190321-144842

では、最後に、参照整合性制約を無効化した場合、実行計画はどうなるか見ておきましょう。
ORCL@OE> alter table orders disable constraint orders_customer_id_fk;

Table altered.

ORCL@OE> r
1 select
2 table_name
3 ,owner
4 ,constraint_name
5 ,constraint_type
6 ,r_owner
7 ,r_constraint_name
8 ,status
9 ,rely
10 from
11 user_constraints
12 where
13* constraint_type='R'

TABLE_NAME OWNER CONSTRAINT_NAME C R_OWNER R_CONSTRAINT_NAME STATUS RELY
------------------------------ ------------------------------ ------------------------------ - ------------------------------ ------------------------------ -------- ----
ORDERS OE ORDERS_CUSTOMER_ID_FK R OE CUSTOMERS_PK DISABLED
INVENTORIES OE INVENTORIES_WAREHOUSES_FK R OE WAREHOUSES_PK ENABLED
INVENTORIES OE INVENTORIES_PRODUCT_ID_FK R OE PRODUCT_INFORMATION_PK ENABLED
ORDER_ITEMS OE ORDER_ITEMS_ORDER_ID_FK R OE ORDER_PK ENABLED
ORDER_ITEMS OE ORDER_ITEMS_PRODUCT_ID_FK R OE PRODUCT_INFORMATION_PK ENABLED
PRODUCT_DESCRIPTIONS OE PD_PRODUCT_ID_FK R OE PRODUCT_INFORMATION_PK ENABLED

あらまあ、不思議w わざとらしいw
customers表げ結合されちゃってネステッドループ結合に!
ORCL@OE> r
1 explain plan for
2 select
3 distinct
4 order_id
5 from
6 orders o
7 , customers c
8 where
9 o.customer_id = c.customer_id
10* and order_id < 2400

Explained.

ORCL@OE> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
Plan hash value: 2552081916

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 46 | 552 | 3 (34)| 00:00:01 |
| 1 | SORT UNIQUE NOSORT | | 46 | 552 | 3 (34)| 00:00:01 |
| 2 | NESTED LOOPS SEMI | | 46 | 552 | 2 (0)| 00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID| ORDERS | 46 | 368 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | ORDER_PK | 46 | | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | CUSTOMERS_PK | 319 | 1276 | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("O"."CUSTOMER_ID">0)
4 - access("ORDER_ID"<2400)
5 - access("O"."CUSTOMER_ID"="C"."CUSTOMER_ID")

目黒方面の密林で、美登利の寿司弁当を食べるのが最近のマイブームw
ではまた。

| | コメント (0)

2019年2月21日 (木)

Wait Events

データベース関連で待機イベントと言えば、これまでは、Oracle Database しか浮かばなかったわけですが、今は、PostgreSQL、そして、MySQL にも実装された。

待機イベントを知らずして、どうするの? でも大丈夫。 今までOracleの待機イベントに親しんできたデータベースエンジニアの活躍の場が広がるんじゃないかなぁ。。。と遠くをみている。。。

Oracle Database Wait Events

PostgreSQL Wait Events

MySQL : 25.12.15.1 Wait Event Summary Tables

| | コメント (0) | トラックバック (0)

2019年2月11日 (月)

SQL*Plusでcsv出力できるんですよ #2 null はどうなる? / FAQ

前回は、SQL*Plusでcsvファイルをお手軽にできることを確認したので、今回はもう少し細かいところを確認しておきます。

csvファイルを作成するOracle Databaseのバージョン等は以下のとおり。

SQL> select
2 banner_full
3 from
4 v$version;

BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

データベースキャラクタセットは最近では一般的なAL32UTF8

SQL> r
1 select
2 parameter
3 , value
4 from
5 nls_database_parameters
6 where
7* parameter in ('NLS_CHARACTERSET')

PARAMETER VALUE
---------------------------------------- ------------------------------
NLS_CHARACTERSET AL32UTF8

SQL>
SQL> !echo $NLS_LANG
Japanese_Japan.AL32UTF8

SQL> !echo $LANG
ja_JP.UTF-8

適当に作成した表は以下のとおり。NULLの取り込みを見ておきたかったのでnullも含めてあります。

SQL> desc test
名前 NULL? 型
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER
DATA VARCHAR2(10)
FOO NOT NULL VARCHAR2(10)

SQL> select * from test order by id;

ID DATA FOO
---------- ---------- ----------
1 テスト note
2 平成 note
3 abcdbef note
4 あ note
5 A note
6 note

6行が選択されました。


id=6のdata列は null なのですが空白区別しにくいので可視化して確認しておきます。
注意)set null コマンドで設定した文字列は csv作成時のにも反映されるため空にリセットすることをお忘れなく。

SQL> set null [null]
SQL> select * from test order by id;

ID DATA FOO
---------- ---------- ----------
1 テスト note
2 平成 note
3 abcdbef note
4 あ note
5 A note
6 [null] note

6行が選択されました。

SQL> set null ""

csvファイルの作成。スクリプトの例は前回の記事(SQL*Plusでcsv出力できるんですよ / FAQ)参照のこと。

SQL> @makecsv test
SQL> !cat loaddata_test.csv
1,"テスト","note"
2,"平成","note"
3,"abcdbef","note"
4,"あ","note"
5,"A","note"
6,,"note"

SQL> exit
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Productionとの接続が切断されました。
discus-mother:˜ oracle$


ということで、 nullは、,, としてcsvファイルに書き出されることを確認しました。
だたし、set nullでnullを他の文字列に置き換えている場合には、置換した文字列がそのままcsvファイルへかきだされてしまうので注意が必要です。




previously on Mac De Oracle
SQL*Plusでcsv出力できるんですよ / FAQ


数日前の朝、仕事先に向かおうとしら、ちょいと熱っぽい?、頭痛もあるな! と体温を測ったら37度、インフル?
と思い仕事を休んで夕方まで様子見。。。熱は夜更けすぎに、平熱と変わっていましたw めでたしめでたし:) インフルじゃなくてよかった。
家庭内隔離解除されてほっとしているところ。。
では、また。

| | コメント (0) | トラックバック (0)

SQL*Plusでcsv出力できるんですよ / FAQ

SQL*Plusでcsv出力する簡単な方法って、意外に知られてないようなのでメモ程度に書いておきます。
自分でもコピペネタとするためにw

SQL> select * from q order by id;

ID DATA
---------- ----------
1 テスト
2 平成
3 abcdbef
4 あ
5 A

SQL> set markup csv on
SQL> select * from q order by id;

"ID","DATA"
1,"テスト"
2,"平成"
3,"abcdbef"
4,"あ"
5,"A"

SQL> set markup csv off

set markup csv on でcsv出力を簡単に取得できます。
これがなかったころはパッケージ作ったりしてましたけど、これなら手間いらず:)

スプールしてファイルに書き出すスクリプトを作っておくと便利です。
以下のスクリプトは &1 パラメータでcsv化する表名称を渡すだけ。

SQL> !cat makecsv.sql
--
-- parameter 1 : table name
--
set feed off
set timi off
set head off
set termout off
set veri off
set markup csv on
spool loaddata_&1..csv
select * from &1 order by id;
spo off
set markup csv off
set termout on
set head on
set feed on
set veri on
undefine 1

SQL> @makecsv q
SQL>
SQL> !cat loaddata_q.csv
1,"テスト"
2,"平成"
3,"abcdbef"
4,"あ"
5,"A"

| | コメント (0) | トラックバック (0)

2019年2月10日 (日)

18cのv$versionは列が増えてるのね / FAQ

今気づいたが、Oracle Database 18cのv$versionがいくつかの表示パターンにあわせたのか、3列になったのね。

SQL> select * from v$version;

BANNER BANNER_FULL BANNER_LEGACY CON_ID
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production 0
Version 18.3.0.0.0

| | コメント (0) | トラックバック (0)

2019年1月20日 (日)

Understanding the Oracle Database 18c Technical Architecture

私が、Ingresを使ってた横で、
Oracle 7のマニュアルよんでも意味わからなーいというプロジェクトメンバーのヘルプために、
なんで俺が読まなきゃいけないのーーと思いながら、
Oracle 7のアーキテクチャをマニュアル読み進めていくうちにどっぷりはまっていったという
くらい綺麗だなーと思ったことを、最新のマニュアルを見て思い出すなどw(どんな昔話だよw

今見ても綺麗だなーとはおもいますね。

Understanding the Oracle Database 18c Technical Architecture
https://www.oracle.com/webfolder/technetwork/tutorials/architecture-diagrams/18/technical-architecture/database-technical-architecture.html#

| | コメント (0) | トラックバック (0)