PSI Labs RSS feed

社員旅行2018 in ケアンズ!

こんにちは、ついに社員旅行に行くことができましたmukaiです。

*

弊社では以前、創立45周年記念ということで、2015年11月にグアム旅行を行いました。(私は出産&育児休暇で参加しませんでした)
次は50周年なのかな~と思っていたのですが
なんと48周年記念ということで、ケアンズに行くことに!
子供2人を置いて自分だけ海外旅行にいくのはさすがに夫に悪いということで...娘6歳と参加しました。
(息子3歳...連れて行くと迷子になりそうだし溺れそうだしオムツ取れてないし正直面倒なのでお留守番)

*

今回の社員旅行は2018/11/22(木)~11/25(日)と2015/11/23(金)~11/26(月)の2班編成で行きました。
11/24(土)に全員でのキュランダ観光&記念パーティがあり、それには社員全員が参加することになっています。

2018_IMG_0611.jpg2018_DSC04975.jpg

行きは深夜便。17時成田空港集合、20時過ぎに離陸、22時に機内食(!?)、2時ごろに明かりがついて起こされ、4時ごろケアンズ到着(時差1時間)
これがなかなかハードで、、、よく眠れたと言っている人は、私が聞いた限り一人もいませんでした笑

今回の社員旅行、宿泊場所はヒルトン・ケアンズ。

C1.jpgC2.jpg

朝食ビュッフェがとても豪華!日本人の宿泊客も多いのか、朝食メニューに味噌汁やごはんや蕎麦も...。
うちの子は自動で焼きあがるホットケーキマシンに興味津々。
ホテル内にはプールもあり、時間が空いた時は、子供とプールで遊んだりもしました(深かった!)

*

さて、私は1班だったのですが、初日がフリーな日程。
私は娘と一緒にケアンズから最も近い珊瑚礁の楽園、グリーン島に行ってきました。
グラスボートという、上からサンゴ礁を眺めることのできるボートに乗ると、ファインディング・ドリーに出てきたような魚たちがいっぱい!カメもいたそうなのですが、私は見れず...無念...
娘は海で遊んだり、海外グルメ・カラフルなデザートを喜んだりしていました。
G1.jpgG3.jpg

こちらの方はフィッツロイ島に行ってきたそうです。
海が奇麗~インスタ映え!
P1.jpgP2.jpg

世界遺産でもあるグレートバリアリーフでダイビングしている方たちも!
楽しそうですね。
D2.jpgD1.jpg

夜はみんなでレストランにてディナー。
本場オージービーフに加え、カンガルー、ワニなどアボリジニ伝統の食材をつかったお料理をいただきました。
オーストラリアに来たぞー!と感じさせてくれるお肉メニューでした!
個人的にはカンガルーが美味しかったです。ワニも堅めの鶏肉、といった感じでなかなか美味。
うちの子はパンばっかり食べてましたが...(パンがものすごくおいしかったらしい)
O1.jpgO2.jpg

*

翌日は、2班の方々と合流し、全員でのキュランダ観光です。
まずはスカイレールというロープウェイに乗り、キュランダ村へ。
実はわたくし、約10年ほど前にケアンズに1度来たことがあるのですが、その時は土砂降りにあいスカイレールに乗れなかったのです。。。
今回は天候にも恵まれ、ついに乗ることができました。むしろ、天気が良すぎて暑いくらいでした。
ユリシスという青い蝶を見ると幸せになれるとのことで、上空から探してみましたが、見つからず...(見たという人もいました!)
幸せになりたい...
C1.jpgC2.jpg

キュランダ村ではコアラ抱っこ写真(シドニーではもう撮れないそうです。ケアンズでももうすぐ禁止されるかも、とのことでした)や、アーミーダックツアー。
アーミーダックというのは、軍事目的で作られた水陸両用車のことで、爆音を上げながら熱帯雨林を爆走。古いのか、すさまじい音でした。
C4.jpgC5.jpg

村を散策した後は、世界の車窓からのOPでも有名なキュランダ観光鉄道で、のんびりケアンズ市内まで戻ってきました。
途中、ツアーガイドの方がケアンズでの暮らしやオーストラリアのことについて話してくれたのですが、中々面白かったです。
オーストラリアは物価がかなり高いのですが(お水やジュースなんかも高かったです...)、
そのかわり給料も高くて、マクドナルドは時給2000円くらいだそうです。日本の倍以上ですね。
車窓の景色も日本と違うので、見ていて飽きません。車窓にはのどかな郊外の風景が広がります。一度くらいこういうところに住んでみたいです。
C6.jpgC7.jpg

一旦ホテルに戻ってから、夜はプールサイドでのディナーパーティです。
フェイスペインティング体験やアボリジニのダンス、クイズ大会や洗面器で息を止める戦いなど、大盛り上がり!
(娘は旅行以来、いつもお風呂でもぐって息を止める練習をしています...)
今回は、うちの子も含め家族連れでの参加も多く、子供たちもワイワイ楽しそうでした。
A0.jpgA1.jpgA3.jpgA2.jpgA5.jpg

*

久しぶりの海外旅行...(パスポートも取り直しました)
きれいな景色と海外特有の空気にとっても癒されました。
正直、ケアンズって、一度行ったことあるしな、、、とも思っていたのですが
入国審査や出国審査が顔認証になっていたり、
10年前に乗ったキュランダ村のブランコがまだ残っていたり(そのブランコに娘を乗せた写真を撮って、昔一緒に行った友達にLINEで送信したら「おぼえてるー!」と返事が返ってきました)
アイスクリーム屋のおじさんは場所を変えてまだやっていたり、
過去の思い出に加え、新しい思い出を作れたことは本当に貴重な経験だったと思います。

ピー・エス・アイは大塚本社と多摩事業所に分かれているため、社員が一堂に会することはほとんどありません。
唯一、毎年4月に行われる創立記念・入社式典で全員が集合するのですが、普段はあまり話すことのできない社員も多いです。
そんな中での社員旅行、ということで、いつもはなかなか話しが出来ない人とも話ができ、とても充実した旅行でした。

groupphoto.png

さて、、、次回こそ50周年でしょうか!?
そのころには息子も5歳...。迷子にならないくらいに成長していたら連れて行こうかな笑
社長、よろしくお願いします!


Oracle:expdpもexpコマンドも使えない場合の日々のデータバックアップ方法(BLOB,CLOB含む場合も)

こんにちは。shintaniです。

Oracleで日々の運用でテーブル単位のバックアップを取る場合、expdpまたは expを使うことが多いと思います。
しかし expdpではDBサーバ上にしかファイルを出力できず、かつ expは暗号化表領域の場合に使えなかったりします。

これらの問題に引っかかり、expdpも expコマンドも使えないという場合のデータバックアップ方法について紹介します。

その方法とは

データをカンマ区切りで出力し、SQL*Loaderによって復元できるようにする。

です。

バックアップ処理では、SQL*Plus の spool コマンドで下記のSQLの結果を出力します。


SET ECHO OFF
SET TERMOUT OFF
SET VERIFY OFF
SET HEAD OFF
SET FEEDBACK OFF
SET PAGES 0
SET LINESIZE 32767
SET TRIMSPOOL ON

select
      '"' ||  DATA_NO
 || '","' ||  DATA_YYYYMMDD
 || '","' ||  DATA_COL2
 || '","' ||  DATA_COL3
 || '","' ||  DATA_COL4
 || '","' ||  TO_CHAR(INSERT_TIME ,  'YYYY/MM/DD HH24:MI:SS.FF')
 || '"'  as "ALL_COLS"
  from
    TEST_TABLE
  where
    DATA_YYYYMMDD = '&BACKUP_DATE'
order by
  DATA_NO
;

---------------------------------------

この結果は下記のようになります。

"00000000000001","20170901","0000000","000000000201","ABCDE001","2017/09/01 18:00:01.000001"
"00000000000002","20170901","0000000","000000000202","ABCDE002","2017/09/01 18:00:02.000002"
"00000000000003","20170901","0000000","000000000203","ABCDE003","2017/09/01 18:00:03.000003"
"00000000000004","20170901","0000000","000000000204","ABCDE004","2017/09/01 18:00:04.000004"

復元が必要になった場合、このファイルをSQL*Loaderで取り込みます。
(コントロールファイルの書式などは別途調べて下さい)


さて、この書き方ではBLOBやCLOBなどサイズが大きい列は出力できません。SQL*Plusでは表示が切れます。
この場合の手順を紹介します。
少し長くなるのでポイントとなるBLOB項目の部分だけをピックアップすると、下記の図のようなことをします。



WS000000.JPG

以下はBLOBの場合の手順です。CLOBの場合は文字型なので色々簡単になると思います。
※ Linux上でシェル実行する手順が含まれています。


【SQL その1】

SET ECHO OFF
SET TERMOUT OFF
SET VERIFY OFF
SET HEAD OFF
SET FEEDBACK OFF
SET PAGES 0
SET LINESIZE 32767
SET TRIMSPOOL ON



SELECT
      '"' ||  DATA_NO
 || '","' ||  DATA_YYYYMMDD
 || '","' ||  DATA_COL2
 || '","' ||  'DATA_COL3/' || DATA_NO || '.blob'
 || '"'  as "ALL_COLS"
  FROM
    TEST_TABLE_2
  where
    DATA_YYYYMMDD = '&BACKUP_DATE'
order by
  DATA_NO
;

【SQL その2】

SET ECHO OFF
SET TERMOUT OFF
SET VERIFY OFF
SET HEAD OFF
SET FEEDBACK OFF
SET PAGES 0
SET LINESIZE 32767
SET TRIMSPOOL ON

COLUMN BLOB0 FORMAT A2010
COLUMN BLOB1 FORMAT A2010
COLUMN BLOB2 FORMAT A2010
COLUMN BLOB3 FORMAT A2010
COLUMN BLOB4 FORMAT A2010
COLUMN BLOB5 FORMAT A2010
COLUMN BLOB6 FORMAT A2010
COLUMN BLOB7 FORMAT A2010
COLUMN BLOB8 FORMAT A2010
COLUMN BLOB9 FORMAT A2010

SELECT
     '"' ||  DATA_NO  || '",'     as "PK_COLS"
          ,rawtohex(dbms_lob.substr(DATA_COL3 , 1000,1)   )  as BLOB0
          ,rawtohex(dbms_lob.substr(DATA_COL3 , 1000,1001))  as BLOB1
          ,rawtohex(dbms_lob.substr(DATA_COL3 , 1000,2001))  as BLOB2
          ,rawtohex(dbms_lob.substr(DATA_COL3 , 1000,3001))  as BLOB3
          ,rawtohex(dbms_lob.substr(DATA_COL3 , 1000,4001))  as BLOB4
          ,rawtohex(dbms_lob.substr(DATA_COL3 , 1000,5001))  as BLOB5
          ,rawtohex(dbms_lob.substr(DATA_COL3 , 1000,6001))  as BLOB6
          ,rawtohex(dbms_lob.substr(DATA_COL3 , 1000,7001))  as BLOB7
          ,rawtohex(dbms_lob.substr(DATA_COL3 , 1000,8001))  as BLOB8
          ,rawtohex(dbms_lob.substr(DATA_COL3 , 1000,9001))  as BLOB9
  FROM
    TEST_TABLE_2
  where
    DATA_YYYYMMDD = '&BACKUP_DATE'
order by
  DATA_NO
;

それぞれの出力ファイルは下記のようになります。


【SQL1結果ファイル】

"00000000000001","20170901","0000000","DATA_COL3/00000000000001.blob
"00000000000002","20170901","0000000","DATA_COL3/00000000000002.blob
"00000000000003","20170901","0000000","DATA_COL3/00000000000003.blob
"00000000000004","20170901","0000000","DATA_COL3/00000000000004.blob


【SQL2結果ファイル】
"00000000000001", 330313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313030313031303130313031303130313031303130313031303130313031303130313031303031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130 30313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303031303130313031303130313031303130313031303130313031303130313031303031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313030313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130 3031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313030313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031
"00000000000002", 33031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031
"00000000000003", 33031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031



SQL1の結果ファイルでは「主キーの値と、列ディレクトリの下にある.blobのファイル名」という形式で出力されます
SQL2の結果ファイルでは「主キーの値と、BLOB項目のHEX表示(1000バイト区切り)」という形式で出力されます。


SQL1結果ファイルは特に編集の必要はありません。
SQL2結果ファイルについては、方法は何でもいいのでこのような形式のファイルに変更します。
(表示上は改行されていますが、実際は echo ごとに1行です)

echo 330313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313030313031303130313031303130313031303130313031303130313031303130313031303031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130 >> 00000000000001_HEX.blob
echo 330313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313030313031303130313031303130313031303130313031303130313031303130313031303031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130 >> 00000000000002_HEX.blob
(以下略)

※ 先頭にecho 付加
※ 主キーの値を末尾に持ってきて _HEX.blob を追記
※ BLOB項目の間の半角スペースを除去
※ ファイル名と BLOB項目の間に >> を追記


上記のファイルをLinux上に配置し、実行します
※大量にファイルが作成されるので、DATA_COL3 ディレクトリを作成し、その下に配置して実行して下さい。


作成されたファイルの一覧を出力し、それを元に、下記のコマンドを作成します。

xxd -r -p 00000000000001_HEX.txt > 00000000000001.blob
xxd -r -p 00000000000002_HEX.txt > 00000000000002.blob
xxd -r -p 00000000000003_HEX.txt > 00000000000003.blob



xxd コマンドは HEXファイルの内容をバイナリに変換してファイル出力するものです。
これにより blob項目のデータ内容を持ったファイルが作成されます。

この作成されたファイルと、メインとなるデータファイルを配置し、SQL*Loaderを実行します。

コントロールファイルは以下のようになります。


OPTIONS(ERRORS=1)
LOAD DATA
INFILE 'TEST_TABLE_2_20170901.csv'
REPLACE
PRESERVE BLANKS
INTO TABLE TEST_TABLE_2
FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
  DATA_NO
 ,DATA_YYYYMMDD
 ,DATA_COL2
 ,FILENAME_DATA_COL3  FILLER  CHAR, DATA_COL3  LOBFILE( FILENAME_DATA_COL3 ) TERMINATED BY EOF
)

FILENAME_REQ の行で、ファイルパスとファイル名からロードするBLOBファイルを読み込みます。
これによりデータが復元されます。

途中、BLOB列のHEX形式ファイルの編集方法などは大きく端折りました。
皆さまの実行する環境での効率の良い方法を各自ご検討下さい。

ではでは。


※ なおこの方法は SQL*PlusのSPOOLで出力するという方法上、下記の条件を満たさないと使えません。

① LOB項目の最大長が決まっていること。
② LOB項目の出力行の長さが SQL*Plusの1行の最大長32767バイトを超えないこと。


PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT を設定して SSL証明書 の CN検証をパスする

こんにちは、tomitaです。

PDO_MySQL を使った SSL 接続で少しつまづいたのでメモがてら残しておきます。

環境は以下の通りです。

  • CentOS 7
  • MySQL 8.0.11
  • PHP 7.2.5

まず、MySQL のSSL接続設定を有効にします。

cd /var/lib/mysql
mysql_ssl_rsa_setup
chown mysql:mysql *.pem
systemctl restart mysqld

次にSSL接続用ユーザを作成します。

mysql -u root -p
> CREATE USER 'ssl'@'localhost' IDENTIFIED BY 'sslpassword' require SSL;
> CREATE USER 'ssl'@'127.0.0.1' IDENTIFIED BY 'sslpassword' require SSL;
> GRANT ALL PRIVILEGES ON *.* TO 'ssl'@'localhost';
> GRANT ALL PRIVILEGES ON *.* TO 'ssl'@"127.0.0.1";

以下で接続できれば準備完了です。

mysql -u ssl -p --ssl-mode=REQUIRED
(パスワード入力)

次に、以下のPHPスクリプト経由で接続してみます。

<?php
$dsn = "mysql:host=127.0.0.1;port=3306;dbname=testdb;charset=utf8mb4";
$user = "ssl";
$password = "sslpassword";
$options = [
  \PDO::MYSQL_ATTR_SSL_KEY  => "/var/lib/mysql/client-key.pem",
  \PDO::MYSQL_ATTR_SSL_CERT => "/var/lib/mysql/client-cert.pem",
  \PDO::MYSQL_ATTR_SSL_CA   => "/var/lib/mysql/ca.pem",
];

try{
  $P = new \PDO($dsn, $user, $password, $options);
} catch (\Exception $e) {
  do {
    printf("%s:%d %s\n", $e->getFile(), $e->getLine(), $e->getMessage());
  } while($e = $e->getPrevious());
  exit();
}

echo "success!!!\n";

実行すると以下エラーが発生しました。SSL証明書のCN検証でエラーが発生しています。

/home/tomita/work/ssl_con_test.php:12 SQLSTATE[HY000] [2002]
/home/tomita/work/ssl_con_test.php:12 PDO::__construct(): Peer certificate CN=`MySQL_Server_8.0.11_Auto_Generated_Server_Certificate' did not match expected CN=`127.0.0.1'

mysqli 関数経由の場合、SSL証明書の検証をパスできる MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT 定数が定義されているんですが、PDO_MYSQL には見つからない……。と思ったらドキュメントのコメント欄に以下の投稿がありました。なぜかドキュメント化されていないようです。

http://php.net/manual/ja/ref.pdo-mysql.php#122326

There is an important undocumented attribute which disables certificate CN verification available
5.6.22 (not sure), 7.0.18 (verified) and 7.1.15 (not sure)

PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT

possible values: true, false
default value: true

$options に \PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false を追加、再度スクリプトを実行して「success!!!」表示を確認しました。

$options = [
  \PDO::MYSQL_ATTR_SSL_KEY  => "/var/lib/mysql/client-key.pem",
  \PDO::MYSQL_ATTR_SSL_CERT => "/var/lib/mysql/client-cert.pem",
  \PDO::MYSQL_ATTR_SSL_CA   => "/var/lib/mysql/ca.pem",
  \PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false, // ← これ追加
];

それでは~


Oracle:統計情報取得を停止した環境で、日付を使った検索の性能が急激に劣化

こんにちは。shintaniです。

Oracle10gからRBOがサポートされなくなり、統計情報を取得してのCBOでの運用が必須となりました。
しかし、やはり「統計情報を取得したら実行計画が変わって急激に性能が劣化するのではないか?」という心配はあります。
そのため「一度取得した後、テーブルに統計情報ロックを設定して実行計画を固定化する」という運用をしている現場もあると思います。

「統計情報を取得しなければ、徐々に性能劣化していくとしても急激に劣化することは無い」
私もそう思っていた時期がありました・・・(遠い目)


しかしタイトルのような現象が実際に起こってしまいました。
起こったのは下記のような画面においてでした。

WS000000.JPG

その検証のため下記のようなテーブルとデータを用意しました。

【検証環境:Oracle12.1.0.2】

create table TBL_TEST
(   col1_yyyymmdd     VARCHAR2(8)
   ,col2_unique       VARCHAR2(20)
   ,col3_non_unique   VARCHAR2(10)
);

create index  IND_TEST_COL1  ON  TBL_TEST(col1_yyyymmdd) TABLESPACE IDX_TBS;
create index  IND_TEST_COL2  ON  TBL_TEST(col2_unique) TABLESPACE IDX_TBS;
create index  IND_TEST_COL3  ON  TBL_TEST(col3_non_unique) TABLESPACE IDX_TBS;

データ:7300万レコード(1日10万レコード × 2年分)
COL1 は日付項目。文字型YYYYMMDD。
COL2 は全データで一意。
COL3 は日付の中で一意。


【データ作成手順】

※2年間データ保存するテーブル対して2015/12/31に統計情報の取得を停止。そして2年経過したという想定。

① テーブルに 20140101 ~ 20151231 で、7300万レコード作成する。
② 統計情報を取得する
③ テーブル統計情報をロックする
④ ①のデータを削除し、 20160101 ~ 20171231 のデータを作成する。


この状態のテーブルに対し、col3 の一致条件 、および col1 の範囲条件(1か月)を設定し、
日付を1日ずつずらしながらどのように実行計画が変わるか調査しました。

SELECT * FROM  TBL_TEST T WHERE COL3_NON_UNIQUE = '0000000001' AND COL1_YYYYMMDD between '20120101' AND '20120201';
SELECT * FROM  TBL_TEST T WHERE COL3_NON_UNIQUE = '0000000001' AND COL1_YYYYMMDD between '20120102' AND '20120202';
SELECT * FROM  TBL_TEST T WHERE COL3_NON_UNIQUE = '0000000001' AND COL1_YYYYMMDD between '20120103' AND '20120203';
・
・
・
SELECT * FROM  TBL_TEST T WHERE col3_non_unique = '0000000001' AND COL1_YYYYMMDD between '20181129' AND '20181230';
SELECT * FROM  TBL_TEST T WHERE col3_non_unique = '0000000001' AND COL1_YYYYMMDD between '20181130' AND '20181231';
SELECT * FROM  TBL_TEST T WHERE col3_non_unique = '0000000001' AND COL1_YYYYMMDD between '20181201' AND '20190101';

また下記のように col1のインデックスのヒント句を使って、このインデックスを使った場合の見積もり情報も調査しました。

SELECT /*+ INDEX(T IND_TEST_COL1) */  * FROM  TBL_TEST T WHERE COL3_NON_UNIQUE = '0000000001' AND COL1_YYYYMMDD between '20120101' AND '20120201';
SELECT /*+ INDEX(T IND_TEST_COL1) */  * FROM  TBL_TEST T WHERE COL3_NON_UNIQUE = '0000000001' AND COL1_YYYYMMDD between '20120102' AND '20120202';
SELECT /*+ INDEX(T IND_TEST_COL1) */  * FROM  TBL_TEST T WHERE COL3_NON_UNIQUE = '0000000001' AND COL1_YYYYMMDD between '20120103' AND '20120203';
・
・
・
SELECT /*+ INDEX(T IND_TEST_COL1) */  * FROM  TBL_TEST T WHERE COL3_NON_UNIQUE = '0000000001' AND COL1_YYYYMMDD between '20181129' AND '20181230';
SELECT /*+ INDEX(T IND_TEST_COL1) */  * FROM  TBL_TEST T WHERE COL3_NON_UNIQUE = '0000000001' AND COL1_YYYYMMDD between '20181130' AND '20181231';
SELECT /*+ INDEX(T IND_TEST_COL1) */  * FROM  TBL_TEST T WHERE COL3_NON_UNIQUE = '0000000001' AND COL1_YYYYMMDD between '20181201' AND '20190101';

【set autotrace on traceonly explain で実行計画の見積もりだけ表示】

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                        |    34 |  1394 | 41494   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TBL_TEST               |    34 |  1394 | 41494   (1)| 00:00:02 |
|*  2 |   INDEX RANGE SCAN                  | IND_TEST_COL1          |  3451K|       |  9637   (1)| 00:00:01 | ← 約345万レコードがヒットする見積もり
-------------------------------------------------------------------------------------------------------------

その結果をまとめたものが下記のイメージです。

20180101_統計情報_日付_グラフ.JPG

統計情報を取得した時点での日付範囲については、col1のインデックスを使った場合のヒット数は 約300万レコード(10万×30日分)と見積もられています。
しかしそれから外れた日付については前後1年間は 1/100 以下 (30000~ 10000) に落ち、そして 2017/01/01を境に急激に落ちて 「1」となりました。
そうなると col3インデックスを使った場合の見積もり 30 より小さいため、col1のインデックスが使用されます。

しかし実際には1日でも10万レコードが入っているため大量にヒットし、非常に遅いSQLとなります。。

この問題の原因は、統計情報として「列の統計情報」と「列ヒストグラム」も取得されていることです。

select 
TABLE_NAME, COLUMN_NAME,  UTL_RAW.CAST_TO_VARCHAR2(LOW_VALUE) ,  UTL_RAW.CAST_TO_VARCHAR2(HIGH_VALUE)
 from USER_TAB_COL_STATISTICS 
  where TABLE_NAME = 'TBL_TEST'
and column_name = 'COL1_YYYYMMDD'

TABLE_NAME, COLUMN_NAME, UTL_RAW.CAST_TO_VARCHAR2(LOW_VALUE), UTL_RAW.CAST_TO_VARCHAR2(HIGH_VALUE)
---------------------------------------------------------------------------------
TBL_TEST	COL1_YYYYMMDD	20140101	20151231


-- ※ヒストグラムは省略。

LOW_VALUEとHIGH_VALUE に最小値と最大値を持ち、ヒストグラムでその分布情報を持っているため、
その範囲から外れた場合はヒットするレコードを少なく見積もります。

一定期間(この場合は1年ほど)は、いきなり0ではなくある程度の余裕を持って見積もっているから
実行計画が変わることは無かったのですが、1年以上経過したら「もういいよね・・・」と、1レコードと見積もって、実行計画が変わってしまったわけです。

みなさんもご注意下さい。

ではでは。


【追記】
この現象ですが、データを保持する期間や列のデータ型などによって変わってくると思われます。

① 検証環境では見積もり数の激減は 年替わり(20161231 → 20170101)の箇所で起こったが、実環境では 年の途中で起こった。
② 検証環境では「統計情報から外れても 1/100 程度に見積もる期間」は1年間ほどだったが、実環境では2年だった。
③ 最初データ保持期間を「1年間」で作ったが、この場合はどれだけ統計情報から外れても 1/100 程度に見積もり続けた。

特に③ですが、これは「データに間が空いた部分が無かった」ためではないかと推測しています。
「20141231 → 20150101」 は人の感覚では +1日ですが、文字型データ的には「8700ほど飛んだ」形になっています。
DATE型で列を作ってあったならはこの間の空きは無いわけですから、違う結果が出る可能性もあります。


Oracle:複数のインデックスヒントを記述した場合の動作

こんにちは。shintaniです。

WS000000.JPG

こういった画面があり、どの項目も任意入力であるとします。
そしてテーブルの各列には個別にインデックスが貼られているとします。

create table TBL_TEST
(   col1_yyyymmdd     VARCHAR2(8)
   ,col2_unique       VARCHAR2(20)
   ,col3_non_unique   VARCHAR2(10)
);

create index  IND_TEST_col1  ON  TBL_TEST(col1_yyyymmdd) TABLESPACE IDX_TBS;
create index  IND_TEST_col2  ON  TBL_TEST(col2_unique) TABLESPACE IDX_TBS;
create index  IND_TEST_col3  ON  TBL_TEST(col3_non_unique) TABLESPACE IDX_TBS;

この場合に「どのインデックスが使われるか?」は、統計情報を適切に取得したうえでオプティマイザに任せるのが基本です。
しかし、ヒント句を付けることによって制御しなければならなくなった場合はどうなるでしょうか?

値の絞り込み度は
col2_unique > col3_non_unique > col1_yyyymmdd
とし、インデックスの優先度もこの順とします。

この場合、「もっとも優先度が高いもの1つだけINDEXヒントで指定する」とすると、条件判断ロジックをどこかに組み込まないといけません。
これは面倒です。特に条件が多くなればなるほど。

しかし、何らかの法則があればそれに従って検索条件に設定されたものを無条件に設定することで対応できます。

select 
  /*+ INDEX(T IND_TEST_col2) */
  /*+ INDEX(T IND_TEST_col3) */
  /*+ INDEX(T IND_TEST_col1) */
     *
  from 
     TBL_TEST T
where 
   col1_yyyymmdd between  '20150201' and   '20150301'
and
   col2_unique = '201502110000000475'
and
   col3_non_unique = '0000000475'
;

上記の場合、どのインデックスが使われるでしょうか?
検証過程を省略して結果だけ書くと下記のようになります。


書いたものの中で一番最初だけ有効となる。
もしそれが検索条件に無いなどで使えない場合、INDEXヒント無しと同じ。

つまり上記のように優先して使いたいINDEXヒントを上に書けば良いです。

なお下記のようにINDEXヒントの中に併用不可のインデックスを複数指定した場合は、統計情報から判断された一番優先度が高いものが選択されます

select 
   /*+ INDEX(T IND_TEST_col1  IND_TEST_col2 IND_TEST_col3 ) */
   *
from 
   TBL_TEST   T
where 
   col1_yyyymmdd between  '20150201' and   '20150301'
and
   col2_unique = '201502110000000475'
and
   col3_non_unique = '0000000475'
;

なお、以上の検証は Oracle12.1.0.2 で行いました。

ではでは。