SQLer 生島勘富 のブログ

RDB・SQLの話題を中心に情報発信をしています。

SQL中級・上級

DBオブジェクトの命名法と主キー

DBオブジェクトについて、Rails などのようにどうしても「外側から管理したい」と考える人たちは、言語側の命名法で処理したいと考えるようですが、DB側から見ると非常に使いにくいです。 そのため、弊社では以下のような命名法で用途に応じて別名を提供して…

パーティションテーブルについて

はてなの日記からはてなのブログに移り今まで数本の記事を書いてきましたが、Markdownが使えることに今回初めて気づきました(早く言ってよね) ここからは、Markdownで書きます。 目次 目次 パーティションテーブルはなかなか利用されません パーティション…

NoSQL と SQL について

かなり古いネタですが、DeNA の松信氏のブログから NoSQL と SQLについて考えてみましょう。 目次 目次 DeNA の松信氏のブログ DeNA の松信氏がやった実験 一番遅いのはネットワーク SQLは滅茶苦茶重いが、速くもできる! ユーザアクションに対して SQL は極…

SQLと手続き型言語 - JOIN を手続き型で書いてみる

SQL と手続き型言語(オブジェクト指向言語)は最終的には同じです。 今回は、JOIN を手続き型言語(Javaっぽく)書いてみます。 目次 目次 オプティマイズについて 2つの表をJOINしたSQL 単純なネスティッドループ(NESTED LOOP) インデックスを使ったネ…

実行計画のキャッシュについて2 - 各RDBMSについて

SQL文の動的生成を避けるには、実行計画のキャッシュを意識しなければなりません。 そこで、RDBMS毎にキャッシュのやり方が違いますからその違いと、「本当はこういう構造にすれば良いのに」という提案について書きます。 目次 目次 各RDBMSの実行計画のキャ…

実行計画のキャッシュについて1 - 分岐を避けるSQL

かつて文字列連結をして、SQL文を構築していた時代がありました。 現在では ORM を利用することが多いかもしれません。 しかし、論理演算をしっかり理解して工夫すれば SQL だけで WHERE句などを分岐せずに作ることが可能です。 目次 目次 分岐(IF文)か計…

SQLでbit演算を使うパターン

WHERE句は論理演算の塊になるため、論理演算の理解は非常に重要です。 論理演算の応用として bit演算も SQL にもあります。 bit演算結果をデータとして保存することは、多用はいけませんが効果が高いこともあるのでご紹介します。 目次 目次 FROM句、WHERE句…

MySQLで全文検索2

前回(MySQLで全文検索1)の続き。 my.cnf(my.ini)の調整 FullTextIndex でインデックスされる最小の文字数を確認する。 MySQL5.5以下 mysql> SHOW VARIABLES like 'ft_min_word_len'; +-----------------+-------+ | Variable_name | Value | +------------…

MySQLで全文検索1

私は、MySQLをほとんど使わないのであまり考えたこともないのですが、MySQLの全文検索はブランクやカンマで区切られた単語単位でしかインデックスしてくれないので、単語の区切れのない日本語ではほぼ使えません。 そこで nGram でカットするファンクション…

トリガーを自動生成2

前回の続き。 非正規化した項目の整合性を維持するトリガーをかいてみます。 トランザクションテーブルにトリガーを設定する。 例として、以下の様な受注明細があったとして、その非正規化項目の整合性を維持するトリガーを書いてみます。 CREATE TABLE T010…

トリガーを自動生成1

トリガーの適応範囲はエクセルで出力できるところまでとすると、必然的にできることは限られます。関連チェックや整合性の維持に限られるでしょう。 まずは書いてみる。 以前書いたトリガーを少し修正してみましょう。(SQLServerで書きました。) トリガー…

トリガーの勘所 -- 「自動的にやってくれる」と「勝手に変わった」

トリガーを嫌う人は非常に多い。 トリガーはレコードの更新を起点(トリガーにして)に自動的に動くので、アプリケーション側のプログラムソースからは追いかけられず、プログラマが意図しない結果になることがある。というのが嫌われる一番の理由でしょう。…

サロゲートキーは後付けでもできる。

業務システムのほとんどはナチュラルキーで構築されていると思います。 しかし、シノニムやトリガーを利用すれば、既存システムを変更することなくサロゲートキーを追加して、それ以降、サロゲートキーによる運用も可能になります。手順は以下の通りです。 …

命名法 - 主キーはサロゲートキーで、テーブル名はナチュラルキーで(笑)

完全に新規の案件というのは本当に少ないので、実践できることはほぼない理想論ですが、私の理想とするテーブル構造と命名法です。 まずは、サロゲートキーについて サロゲートキーというのは業務上意味のないキーのことです。 例えば、生徒テーブルは、年次…

お手製パーティションテーブルと分散データベース

お手製パーティションテーブルも、分散データベースも、このようなやり方では実はやったことはない。 理論上はお手製で可能ですが、パーティションテーブルと分散データベースが必要な規模では Enterprise Edition を導入した方が良いという判断になり、政治…

コメントを頂いたので

数値を文字型で持つべきではない コメントをいただいたので。 前の記事はこちら。 http://d.hatena.ne.jp/Sikushima/20110809/1312871002 元ネタはこちら。 http://d.hatena.ne.jp/iad_otomamay/20110808/1312805917 http://d.hatena.ne.jp/iad_otomamay/201…

JOIN禁止と固定長カラムについて

あまりに気になったので「山本大@クロノスの日記」にチャチャを入れてみる。 http://d.hatena.ne.jp/iad_otomamay/20110808/1312805917 http://d.hatena.ne.jp/iad_otomamay/20100906/1283786846 まあ、政治的にはどのみち勝てなかったでしょう。私も同じ条…

関連チェックはトリガーで

有効期限付きのマスターを使うことが増えてきましたが、これらはトリガーでチェックすることが効率的です。 トリガーならエラーを返すことができる。 サロゲートキーを利用した以下の様なテーブルがあったとします。 CREATE TABLE Item ( ID int IDENTITY(1,…

LIKE検索は使ったらダメな場合もある - 全文検索について

SQLのLIKE検索は非常に便利です。しかし、データ量によっては使ってはいけません。 例えば、 WHERE 備考 LIKE '%大阪%' とすれば備考欄に'大阪'が含まれているレコードをすべて取得することが可能ですが、当然、インデックスは使えません。必ずテーブルをフ…

ストアドプロシージャでループ処理 - 複雑なSQLをデチューンしてみよう

前回作成したスカラー値関数(fn誤差配賦消費税額)をループして処理する形にのものを作成しましたが、今回はメインのプロシージャを修正することにします。 都合、4種類書いてみましたが、全角の利用でイライラしながらも全部で6時間ぐらいでした。やってみ…

SQLでユーザ関数を活用2 - 複雑なSQLをデチューンしてみよう

元のストアドプロシージャをデチューンしていきます。 前回作成したスカラー値関数(fn誤差配賦消費税額)をループして処理する形に修正します。長くなっていますが、この方が読みやすいかも知れません。 ※ もちろん、実際には、明細単位、納品書単位、請求…

SQLでユーザ関数を活用 - 複雑なSQLをデチューンしてみよう

元のSQLを今回は、OLAP関数が使えないという条件でデチューンしてみます。ソースコードはSQLServerで記述しています。 まずはベースになるSQLを考える。 元のSQLが複雑と感じるか簡単と感じるかは人それぞれですが、ベースの部分は極めて単純です。重要な部…

デチューンの前にダミーのストアドプロシージャ

前回、複雑なSQLをデチューンするという話を書きましたが、弊社ではまずはダミーのストアドプロシージャを作るということを推奨していますので、ダミーのストアドプロシージャから作成します。 出力する内容が以下になるので、その通りのダミービューを作り…

複雑なSQLをデチューンしてみよう

初級編の続きは、 インデックスについて(カラオケ本で考えよう) IN、EXISTSと違いについて などなどを予定しているのですが、twitter で何度かつぶやいたもののまとめなので、ログなどと差は大きくはありません。 ちょっと訳あって、初級編をお休みして、…

ストアドプロシージャでできないと思うのはOracleのせい

ストアドプロシージャでできない。できるわけがない。というのは、Oracleのストアドプロシージャ(ファンクション)でSELECT系の処理がどうしても難しいからです。 ストアドプロシージャで弊社で使っているスタブとしてのストアドプロシージャ(ファンクショ…