standingzzz's Oracle memo

OracleSQLチューニングの豊富な経歴を生かして、主に11gと12cのオプティマイザ動作の違いなどについて、実用的な検証結果をメモしています。

Oracle リストを横に並べて見やすくする[集計関数LISTAGG]

概要

リストが縦にズラーっと出てきて画面内に収まらない。
そんな時にはLISTAGG関数を使うとキレイになります。

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
CentOS Linux release 7.3.1611 (Core) 

検証

データ用意

某野球チーム軍の選手リストを用意しました。

SQL> select * from tab_baseball;

REG_TYPE   UNI_NUMBER PLAYER_NAME   LEFTRI CAREER                                              DRAFT   
---------- ---------- ------------- ------ ------------------------------------------------ ----------------
監督               73 藤田 元司    右右   西条北高-慶應大-日本石油-巨人           
投手               11 斎藤 雅樹    右右   市立川口高                                       1982①
投手               17 槙原 寛己    右右   大府高                                           1981①
投手               18 桑田 真澄    右右   PL学園高                                         1985①
投手               19 吉田 修司    左左   滝高-北海道拓殖銀行                             1988①
投手               21 宮本 和知    左左   下関工業高-川崎製鉄水島                         1984③
投手               25 谷口 功一    右右   天理高                                           1991①
投手               28 広田 浩章    右左   萩商業高-NTT四国                                1985②
投手               30 橋本 清      右右   PL学園高                                         1987①
投手               31 水野 雄仁    右右   池田高                                           1983①
投手               47 木田 優夫    右右   日大明誠高                                       1986①
投手               48 香田 勲男    右左   佐世保工高                                       1983②
投手               59 石毛 博史    右右   市立銚子高                                       1988外           
捕手                9 村田 真一    右右   滝川高                                           1981⑤
捕手               22 中尾 孝義    右右   滝川高-専修大-プリンスホテル-中日           
捕手               22 大久保 博元  右右   水戸商業高-西武         
内野手              0 川相 昌弘    右右   岡山南高                                         1982④
内野手              5 岡崎 郁      右左   大分商高                                         1979③
内野手              6 篠塚 和典    右左   銚子商高                                         1975①
内野手             10 駒田 徳広    左左   桜井商高                                         1980②
内野手             27 福王 昭仁    右左   日大三高-明治大-                               1985⑤
内野手             32 大野 雄次    右右   君津商業高-専修大(中退)-川崎製鉄千葉-大洋           
内野手             35 四條 稔      左左   東海大甲府高-三菱自動車川崎                     1988④
内野手             37 元木 大介    右右   上宮高                                           1990①
内野手             38 勝呂 博憲    右右   千葉商業高-日本通運                             1986⑤
内野手             44 緒方 耕一    右両   熊本工業高                                       1986⑥
内野手             50 後藤 孝志    右左   中京高                                           1987②
外野手              7 吉村 禎章    左左   PL学園高                                         1981③
外野手              8 原 辰徳      右右   東海大相模高-東海大                             1980①
外野手             24 大森 剛      右左   高松商業高-慶應大                               1989①
外野手             29 西岡 良洋    右右   田辺高-西武           
外野手             49 L.モスビー    右左   MLB・デトロイト           
外野手             52 井上 真二    右右   熊本工業高                                       198433 rows selected.

Elapsed: 00:00:00.01
SQL>
懐かしい

背番号1桁の選手名を抽出してみます。

  1  select uni_number
  2       , substr(PLAYER_NAME, 1, instr(PLAYER_NAME, ' ', 1, 1) - 1) player_xing
  3    from tab_baseball where uni_number < 10
  4*  order by 1
SQL> /

UNI_NUMBER PLAYER_XING
---------- ------------
         0 川相
         5 岡崎
         6 篠塚
         7 吉村
         89 村田

6 rows selected.

Elapsed: 00:00:00.01
SQL>
ドラフト外は除いたリスト

最近はテスト生から這い上がって活躍する選手もいますね。

  1  select reg_type
  2       , uni_number
  3       , substr(PLAYER_NAME, 1, instr(PLAYER_NAME, ' ', 1, 1) - 1) player_xing
  4    from tab_baseball
  5   where draft is not null
  6*  order by 1, 2
SQL> /

REG_TYPE   UNI_NUMBER PLAYER_XING
---------- ---------- ------------
内野手              0 川相
内野手              5 岡崎
内野手              6 篠塚
内野手             10 駒田
内野手             27 福王
内野手             35 四條
内野手             37 元木
内野手             38 勝呂
内野手             44 緒方
内野手             50 後藤
外野手              7 吉村
外野手              8 原
外野手             24 大森
外野手             52 井上
投手               11 斎藤
投手               17 槙原
投手               18 桑田
投手               19 吉田
投手               21 宮本
投手               25 谷口
投手               28 広田
投手               30 橋本
投手               31 水野
投手               47 木田
投手               48 香田
投手               59 石毛
捕手                9 村田

27 rows selected.

Elapsed: 00:00:00.00
SQL>

しかし、内外野ピッチャーキャッチャー合わせて27名。
こんなに見辛くては藤田監督に怒られちゃいますね。

LISTAGGの利用

LISTAGG関数で選手情報を横に並べます。

  1  select reg_type
  2       , listagg( '[' || uni_number || ',' || substr(PLAYER_NAME, 1, instr(PLAYER_NAME, ' ', 1, 1) - 1) || ']' )
  3         within group( order by uni_number ) player_list
  4    from tab_baseball
  5   where draft is not null
  6   group by
  7         reg_type
  8   order by
  9*        reg_type
SQL> /

REG_TYPE   PLAYER_LIST
---------- ------------------------------------------------------------------------------------------------------------------------
内野手     [0,川相][5,岡崎][6,篠塚][10,駒田][27,福王][35,四條][37,元木][38,勝呂][44,緒方][50,後藤]
外野手     [7,吉村][8,原][24,大森][52,井上]
投手       [11,斎藤][17,槙原][18,桑田][19,吉田][21,宮本][25,谷口][28,広田][30,橋本][31,水野][47,木田][48,香田][59,石毛]
捕手       [9,村田]

4 rows selected.

Elapsed: 00:00:00.00
SQL>

これなら監督も落ち着いて先発オーダーを決められるでしょう。

Oracle12c ヒント句による実行計画の制御[ORDERED, LEADING, USE_NL, INDEX, OPT_PARAM]

概要

SQLが望ましくない実行計画となっている場合に、
ヒント句により結合順などを制御する例をあげます。

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
CentOS Linux release 7.3.1611 (Core) 

検証

結合テスト用オブジェクト、データ用意

市場名と業種名はマスタ化して、メインテーブルには各コードを保持します。

実行計画の制御

stoke_nameが「東京」で始まる企業の、市場/業種を集計するSQLを例とします。


全部をメモリ上に読み込んで、HASH JOIN、MERGE JOINでガシャッという感じですね。
まあ、テーブル件数も少ないので、単体ではこの方が速いのは事実です。
しかし本番運用では多数のSQLが流れるので、こういった実行計画は
好ましくないことがほとんどです。という理屈をつけて、実行計画を制御します。


orderedヒント句は、SQLのテーブル記述順でどうですか、というだけなので、
結合順の選択肢が1つしかなく、その順序では実行計画が成り立たないと判断されると、
オプティマイザはヒント句を無視するので、効かないこともしばしば。
ここでは意図あってテーブルを結合順に並べているので、ヒント句は効きました。


今回の場合、orderedと同じ結合順をleadingに変えているだけなので、
意味合いは全く同じです。当然、実行計画も同じ。
orderedでもleadingでも、結合順はヒント句が効きましたが、
オプティマイザはやはりHASH JOINしたいようです。(間違いではありません)


しかし、本番システム上で1000多重で実行することを考えて、use_nlヒント句により、
結合方法をNestedLoopに変えて見ました。
まとめてメモリ上に読み込まずに、ループしながら必要な件数ずつ読み込むことで、
メモリリソースの使用を抑える意図です。
あとは、せっかく用意したstock_name用のインデックス、idx01_tab_stockを使って欲しいところ。
東京%の条件で、24件まで絞り込めるので。※この想定はオプティマイザには難しい部分

SQL> select count(*) from tab_stock where stock_name like '東京%';

  COUNT(*)
----------
        24

indexヒント句を入れると、これも何とも素直に効いてくれました。

access("S"."STOCK_NAME" LIKE '東京%')

今回のヒント句を入れたものが、必ずしも最適解というわけではありませんが、
オンラインで多数実行される場合に、メモリリソースを抑えるようなケースでは、
このような実行計画が無難なものとなりますね。


もう1つはおまけで。

opt_paramヒント句で、このSQL実行時のoptimizer_index_cost_adjの値を1(デフォルトは100)
に変更してみると、あらら。
leadingもuse_nlも無しで、この実行計画になりました。

Oracle12c 接続中のPDBをSQLPLUSからSQLで確認する

概要

11gまでは接続先を確認する場合、v$instance見て、show userして、
という具合で良かったのですが、12cでPDBに繋いでいる場合の
確認方法がサッと出てこなかったので、メモです。

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
CentOS Linux release 7.3.1611 (Core) 

確認

接続先instanceを確認します。

  1  select instance_name
  2      , host_name
  3      , startup_time
  4      , status
  5*   from v$instance
SQL> /

INSTANCE_NAME        HOST_NAME              STARTUP_TIME          STATUS
-------------------- ------------------------------ ------------------------- ------------
orcl             oracle12c.test         2017/09/18 11:34:49       OPEN

接続userを確認します。

SQL> show user
USER is "TEST01"

11gまではここまでで良いのですが。

v$pdbsに問い合わせて、接続中のPDBを確認します。

  1  select DBID
  2      , NAME
  3      , OPEN_MODE
  4      , OPEN_TIME
  5*  from v$pdbs
SQL> /

      DBID NAME         OPEN_MODE  OPEN_TIME
---------- -------------------- ---------- ---------------------------------------------------------------------------
3547537298 ORCLPDB2        READ WRITE 17-10-13 17:48:53.484 +09:00

1 row selected.

接続情報を確認するだけなら、これで十分ですね。

Oracle12c SQL*Loader(12.2.0.1)を動かしてみる

概要

こちらでインストールしたSQL*Loaderを動かしてみます。

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
CentOS Linux release 7.3.1611 (Core) 

検証

テスト用テーブル作成
ロード用CSVデータ
[standingzzz@apl work]$ wc -l KEN_ALL_ROME_u.CSV
124117 KEN_ALL_ROME_u.CSV
[standingzzz@apl work]$


0060050,北海道,札幌市 手稲区,星置南,HOKKAIDO,SAPPORO SHI TEINE KU,HOSHIOKIMINAMI
0060811,北海道,札幌市 手稲区,前田 一条,HOKKAIDO,SAPPORO SHI TEINE KU,MAEDA 1-JO
0060812,北海道,札幌市 手稲区,前田 二条,HOKKAIDO,SAPPORO SHI TEINE KU,MAEDA 2-JO
0060813,北海道,札幌市 手稲区,前田 三条,HOKKAIDO,SAPPORO SHI TEINE KU,MAEDA 3-JO
コントロールファイル
options ( errors=-1, rows=10000 )
load data
append
into table tab_KEN_ALL_ROME
fields terminated by ','
trailing nullcols
(   
    postal_cd     char(7)
  , pref_jp       char(256)
  , city_jp       char(256)
  , town_jp       char(256)
  , pref_en       char(256)
  , city_en       char(256)
  , town_en       char(256)
)
ロード
$ sqlldr test01@PDB1 control=KEN_ALL_ROME.ctl data=KEN_ALL_ROME_u.CSV log=KEN_ALL_ROME.log bad=KEN_ALL_ROME.bad readsize=100000000 bindsize=100000000
Password:

SQL*Loader: Release 12.2.0.1.0 - Production on Wed Oct 11 19:32:54 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

LRM-00104: '-1' is not a legal integer for 'errors'
SQL*Loader-114: Error in OPTIONS statement
[standingzzz@apl work]$

errors=-1のところで、integerじゃ無いからエラーだと言われました。
確かにマニュアル見るとこのような記述があります。12cから変わったのですね。

エラーを許容しない場合は、ERRORS=0を設定します。
エラーを無制限に許容する場合は、非常に大きい値を指定します。
再ロード errors=2147483648

型はintergerなので、integerのmax+1をあえて設定してみます。

$ sqlldr test01@PDB1 control=KEN_ALL_ROME.ctl data=KEN_ALL_ROME_u.CSV log=KEN_ALL_ROME.log bad=KEN_ALL_ROME.bad readsize=100000000 bindsize=100000000
Password:

SQL*Loader: Release 12.2.0.1.0 - Production on Thu Oct 12 20:40:38 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

SQL*Loader-350: Syntax error at line 1.
Expecting non-negative integer, found "-2147483648".
options ( errors=2147483648, rows=10000 )
                  ^
[standingzzz@apl work]$

エラーになりました。

再ロード errors=2147483647

それではintegerのmaxにしてみます。

$ sqlldr test01@PDB1 control=KEN_ALL_ROME.ctl data=KEN_ALL_ROME_u.CSV log=KEN_ALL_ROME.log bad=KEN_ALL_ROME.bad readsize=100000000 bindsize=100000000
Password:

SQL*Loader: Release 12.2.0.1.0 - Production on Thu Oct 12 20:42:06 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 10000
Commit point reached - logical record count 20000
Commit point reached - logical record count 30000
Commit point reached - logical record count 40000
Commit point reached - logical record count 50000
Commit point reached - logical record count 60000
Commit point reached - logical record count 70000
Commit point reached - logical record count 80000
Commit point reached - logical record count 90000
Commit point reached - logical record count 100000
Commit point reached - logical record count 110000
Commit point reached - logical record count 120000
Commit point reached - logical record count 124117

Table TAB_KEN_ALL_ROME:
  124117 Rows successfully loaded.

Check the log file:
  KEN_ALL_ROME.log
for more information about the load.
[standingzzz@apl work]$

正常に動きました。まあ、0で良いのでしょうけど。

Oracle12c INDEXがUNUSABLEになるとEXTENT,SEGMENTは削除される

概要

ALTER TABLEなどでINDEXがUNUSABLEとなった場合に、EXTENT、SEGMENTは削除されるという事象の検証(というかメモ)です。 OracleMasterの教科書に載っているような話らしいですね。資格は持っていないので知らないです。

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
CentOS Linux release 7.3.1611 (Core) 

検証

テーブル、インデックスの用意

検証用のテーブルとインデックス、データを用意します。

事前確認

まずは、テーブル、インデックスを作成しただけの状態で、関連ディクショナリを確認します。

ALTER INDEX xxx UNUSABLE

次に、インデックスをUNUSABLEに変更してみます。

SQL> alter index IDX1_TAB_UNUSABLE unusable;

Index altered.

Elapsed: 00:00:00.06
SQL>

すると、user_extents,user_segmentsからは削除されました。

ALTER INDEX xxx REBUILD

UNUSABLEとなった、インデックスをREBUILDします。

SQL> alter index IDX1_TAB_UNUSABLE rebuild;

Index altered.

Elapsed: 00:00:00.08
SQL>

extent,segmentも復活しています。

ALTER TABLE xxx MOVE

テーブル圧縮を実行してみます。

SQL> alter table TAB_UNUSABLE move compress for oltp;

Table altered.

Elapsed: 00:00:00.09
SQL>

テーブルをMOVEすると、インデックスはUNUSABLEになるので、当然と言えば当然なのですが、
やはりextent,segmentからは削除されました。

Centos7にOracle12cのSQL*Loader(12.2.0.1)をインストール

概要

Oracle12cR2のSQL*Loaderをインストールして接続するまでのメモです。

手順

rpmのダウンロード

直リンクだといつしか変わっていたりするので、下記ページで
oracle client download Linux x86-64」を検索して、
http://www.oracle.com/technetwork/topics/index.html

こちらをダウンロード。

oracle-instantclient12.2-tools-12.2.0.1.0-1.x86_64.rpm
インストール
rpm -ivh oracle-instantclient12.2-tools-12.2.0.1.0-1.x86_64.rpm

ORACLE_HOMEのbinにsqlldrが配置されました。

[standingzzz@apl ~]$ which sqlldr 
/usr/lib/oracle/12.2/client64/bin/sqlldr
[standingzzz@apl ~]$ 
[standingzzz@apl ~]$ echo $ORACLE_HOME
/usr/lib/oracle/12.2/client64

とりあえず接続の確認だけしてみます。

[standingzzz@apl ~]$ sqlldr test01@PDB1
Password:

control = 
SQL*Loader-287: No control file name specified.
[standingzzz@apl ~]$ 

python3.6でOracle12c接続の確認(cx_Oracle)

概要

python3.6でOracle12cへ接続してSQLを実行する際のメモです。

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
CentOS Linux release 7.3.1611 (Core) 
Python 3.6.2

確認

接続情報
DBserver:oracle12c.test
port:1521
service_name:orclpdb.test
user:test01
password:xxxx
pythonコード

ora_connect.py

import cx_Oracle
import sys
import os
import configparser


def connect_Oracle( server, port, user, pswd, service ):

    try:

        conn = cx_Oracle.connect( user, pswd, server + ':' + port + '/' + service )
        cur = conn.cursor()

        return ( cur )

    except ( cx_Oracle.DatabaseError ) as ex:
        print ( sys.exc_info()[1] )
        raise ex


def exec_Oracle_SQL( cur, sql ):

    try:

        cur.execute( sql )
        rows = cur.fetchall()

        return ( rows )

    except ( cx_Oracle.DatabaseError ) as ex:
        print ( sys.exc_info()[1] )
        raise ex


if __name__ == '__main__':
    cur = connect_Oracle( 'oracle12c.test', '1521', 'test01', 'xxxx', 'orclpdb.test' )
    rows = exec_Oracle_SQL( cur, 'select username, user_id from user_users' )
    for row in rows:
        print ( row[0] + ', ', end = "" )
        print ( row[1] )
実行結果
[standingzzz@apl common]$ python ora_connect.py 
TEST01, 108
[standingzzz@apl common]$