2010-04-30

自分用メモ

PostgreSQL

全部自分用メモなんだけれど。

PostgreSQLのロケールの設定がイケてないとLIKEでの前方一致検索時にインデックスが効かない。

データベース名は「test001」。

データベース作成時にはロケールの指定を行わない。

OSはWindows XP。

インデックス作成時にtext_pattern_opsを付けてやることで前方一致の時もインデックスが使用される。

データベース作成時にロケールを「C」とかにしていれば上記オプション（？）は必要ない。

以下、ちょっとしたお試し。

test001=#

test001=# select version();

version

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

PostgreSQL 8.4.3, compiled by Visual C++ build 1400, 32-bit

(1 行)

test001=#

test001=#

test001=#

test001=# select name, setting, context from pg_settings where name like 'lc%';

name | setting | context

-------------+-----------------+-----------

lc_collate | Japanese, Japan | internal

lc_ctype | Japanese, Japan | internal

lc_messages | Japanese, Japan | superuser

lc_monetary | Japanese, Japan | user

lc_numeric | Japanese, Japan | user

lc_time | Japanese, Japan | user

(6 行)

test001=#

test001=#

test001=#

test001=# create table locale_test1(name varchar(10));

CREATE TABLE

test001=# create table locale_test2(name varchar(10));

CREATE TABLE

test001=#

test001=#

test001=# create index idx_test1 on locale_test1(name);

CREATE INDEX

test001=#

test001=# create index idx_test2 on locale_test2(name text_pattern_ops);

CREATE INDEX

test001=#

test001=#

test001=# \d locale_test1

テーブル "public.locale_test1"

カラム | 型 | 修飾語

--------+-----------------------+--------

name | character varying(10) |

インデックス:

"idx_test1" btree (name)

test001=#

test001=#

test001=# \d locale_test2

テーブル "public.locale_test2"

カラム | 型 | 修飾語

--------+-----------------------+--------

name | character varying(10) |

インデックス:

"idx_test2" btree (name text_pattern_ops)

test001=#

test001=#

test001=# CREATE FUNCTION test1() RETURNS INTEGER AS

test001-# $$

test001$# DECLARE

test001$# CNT INTEGER;

test001$# BEGIN

test001$# FOR CNT IN 1..1000 LOOP

test001$# INSERT INTO locale_test1 VALUES (CNT);

test001$# END LOOP;

test001$# RETURN CNT;

test001$# END;

test001$# $$

test001-# LANGUAGE plpgsql;

CREATE FUNCTION

test001=#

test001=#

test001=#

test001=#

test001=# SELECT test1();

test1

-------

0

(1 行)

test001=#

test001=# select count(*) from locale_test1;

count

-------

1000

(1 行)

test001=# select * from locale_test1 limit 10;

name

------

1

2

3

4

5

6

7

8

9

10

(10 行)

test001=#

test001=#

test001=# explain analyze

test001-# select * from locale_test1 where name='1';

QUERY PLAN

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

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

Index Scan using idx_test1 on locale_test1 (cost=0.00..8.27 rows=1 width=3) (actual time=0.043..0

048 rows=1 loops=1)

Index Cond: *1

Filter: ((name)::text ~~ '100%'::text)

Total runtime: 0.118 ms

(4 行)

test001=#

*1:name)::text = '1'::text)

Total runtime: 0.112 ms

(3 行)

test001=#

test001=# explain analyze

test001-# select * from locale_test1 where name like '100%';

QUERY PLAN

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

---

Seq Scan on locale_test1 (cost=0.00..17.50 rows=1 width=3) (actual time=0.081..0.545 rows=2 loops

Filter: ((name)::text ~~ '100%'::text)

Total runtime: 0.607 ms

(3 行)

test001=#

test001=# CREATE FUNCTION test2() RETURNS INTEGER AS

test001-# $$

test001$# DECLARE

test001$# CNT INTEGER;

test001$# BEGIN

test001$# FOR CNT IN 1..1000 LOOP

test001$# INSERT INTO locale_test2 VALUES (CNT);

test001$# END LOOP;

test001$# RETURN CNT;

test001$# END;

test001$# $$

test001-# LANGUAGE plpgsql;

CREATE FUNCTION

test001=#

test001=# SELECT test2();

test2

-------

(1 行)

test001=#

test001=# select count(*) from locale_test2;

count

-------

1000

(1 行)

test001=#

test001=# select * from locale_test2 limit 10;

name

------

(10 行)

test001=#

test001=# select * from locale_test2 where name='1';

name

------

(1 行)

test001=# explain analyze

test001-# select * from locale_test2 where name='1';

QUERY PLAN

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

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

Index Scan using idx_test2 on locale_test2 (cost=0.00..8.27 rows=1 width=3) (actual time=0.031..0

035 rows=1 loops=1)

Index Cond: ((name)::text = '1'::text)

Total runtime: 0.095 ms

(3 行)

test001=#

test001=# explain analyze

test001-# select * from locale_test2 where name like '100%';

QUERY PLAN

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

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

Index Scan using idx_test2 on locale_test2 (cost=0.00..8.27 rows=1 width=3) (actual time=0.040..0

052 rows=2 loops=1)

Index Cond: (((name)::text ~>=~ '100'::text) AND ((name)::text ~<~ '101'::text

2010-03-10

Statspack

Oracle

パフォーマンスが悪化しているシステムの原因がSQLにある事を前提とした、Statspackを用いたチューニング概要資料を会社の勉強会の為に作成中…。

最近知ったのですが、Oracle 9i辺りから使える機能に「仮想インデックス」というものがあるのですね。

セグメントは使用しないが、メモリ上には乗っててalter session文で隠しパラメータをセットすると使用できるっていう。

ちょっとインデックス張って効果を試したいって場合には有用かもしれません。

詳しい情報は「virtual index」とかでググると出てきます。

2010-02-09

営業電話

Oracle

たまにOracleさんからお電話頂くんですが、これって営業として入社した方の研修の一環なんでしょうか？

大体が新規にOracleを導入する予定がありますか？みたいな感じなんですが、皆さん非常にたどたどしくて…。

何がどうとか言うわけじゃないのですが、なんとなく気になったもので。

今日は『Ask Tom』に行ってきます！！

非常に楽しみです。

2009-12-13

バグ？

MySQL

http://bugs.mysql.com/bug.php?id=23069

MySQLで文字コードをUTF8にしていると、カラムのデータ型がvarcharの時、

1文字で3バイト使用してしまう。

なので、データベースエンジンがInnoDBの場合はPrimary KeyやUnique制約に

指定したカラムのバイト数(文字数)の最大値は255になってしまう。

バージョンが5.1.38ではこの現象が起きますが、最新のは調べてないので再現

するか不明です。

対応としては、

カラムのバイト数(文字数)を減らす。
文字コードを変える。
データベースエンジンを変える。
そもそもインデックスを張らない。
ソースを編集して再コンパイル(?)。

辺りなんでしょうか…。

2009-11-24

Oracle ORION

Oracle

Oracle ORIONという、データベースへ疑似的なトランザクションを発生させて

ディスクに関するパフォーマンス情報を提供してくれるツールがあるようです。

http://www.oracle.com/technology/software/tech/orion/index.html

上記からダウンロード可能。

使ったことがないので分かりませんが、ディスクデバイスを設定ファイルに記述

して実行するとシーケンシャルI/OやランダムI/Oを生成してくれるとか。

使ったことがなく、よく分からないので今度遊んでみようと思います…。

2009-11-17

大文字小文字

MySQL

MySQLではcharとvarcharの、(少なくとも半角英数字の。それ以外は試していません)大文字小文字を

区別してくれないようです。

ただし、「BINARY」属性を付けると大文字小文字を区別してくれます。

マニュアルを読みましょうって話だと思いますが…こういう仕様なんですねぇ…。

「show create table」コマンドを用いるとテーブルの違いが明らかになりますが、

「desc」コマンドでは属性情報までは出ないようです。

例：

C:\xampp\mysql\bin>mysql -u ***** -p ********

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 4

Server version: 5.1.37 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test;

Database changed

mysql>

mysql> create table test_table_01 (

-> name varchar(10)

-> );

Query OK, 0 rows affected (0.05 sec)

mysql>

mysql> insert into test_table_01 values ('AAA');

Query OK, 1 row affected (0.06 sec)

mysql> insert into test_table_01 values ('aaa');

Query OK, 1 row affected (0.00 sec)

mysql> select * from test_table_01\G;

/*************************** 1. row ***************************

/*************************** 2. row ***************************

2 rows in set (0.00 sec)

ERROR:

No query specified

mysql>

mysql> select * from test_table_01 where name = 'aaa'\G;

/*************************** 1. row ***************************

/*************************** 2. row ***************************

2 rows in set (0.00 sec)

ERROR:

No query specified

mysql>

mysql> select * from test_table_01 where name = 'AAA'\G;

/*************************** 1. row ***************************

/*************************** 2. row ***************************

2 rows in set (0.00 sec)

ERROR:

No query specified

mysql>

mysql> show create table test_table_01\G;

/*************************** 1. row ***************************

Table: test_table_01

Create Table: CREATE TABLE `test_table_01` (

`name` varchar(10) DEFAULT NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

ERROR:

No query specified

mysql>

mysql> alter table test_table_01 modify name varchar(10) binary;

Query OK, 2 rows affected (0.09 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql>

mysql> select * from test_table_01 where name = 'aaa'\G;

/*************************** 1. row ***************************

1 row in set (0.02 sec)

ERROR:

No query specified

mysql>

mysql> select * from test_table_01 where name = 'AAA'\G;

/*************************** 1. row ***************************

1 row in set (0.00 sec)

ERROR:

No query specified

mysql>

mysql> show create table test_table_01\G;

/*************************** 1. row ***************************

Table: test_table_01

Create Table: CREATE TABLE `test_table_01` (

`name` varchar(10) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

ERROR:

No query specified

mysql>

2009-11-12

ネットワーク越しのエクスポート/インポート

SQL Server

Windows認証を用いてローカルサーバのSQL Server 2005のテーブルデータをエクスポートし、

ネットワーク越しに別サーバのSQL Serverのテーブルにインポートする一例です。

諸々環境依存の所はありますが、その辺がクリアになっているとコマンドプロンプト上のみからで行えます。

bcp <データベース名>.<スキーマ名>.<テーブル名> out "<フルパスでファイル名>" -n -T

bcp <データベース名>.<スキーマ名>.<テーブル名> in "<フルパスでファイル名>" -n -S <インポートしたいサーバのIPとかホスト名> -T

テーブルデータを消したい場合は、同様にコマンドプロンプトから以下のようにしたり。

osql -E -S <サーバ名(ローカルなら[localhost])> -d <データベース名> -Q "TRUNCATE TABLE <スキーマ名>.<テーブル名>"

トランザクションログを消したい場合は、、、

osql -E -S <サーバ名(ローカルなら[localhost])> -d <データベース名> -Q "BACKUP LOG <データベース名> WITH TRUNCATE_ONLY"

データベースのエディションによっては使えない？

bcpコマンド及びosqlコマンドの詳細オプションはMSDNを参照してください。

osqlコマンドは未来のバージョンでは削除予定らしいので、sqlcmdコマンドの使用が推奨みたいです。