Rainbow Engine

IT技術を分かりやすく簡潔にまとめることによる学習の効率化、また日常の気付きを記録に残すことを目指します。

Database (データベース) OracleDB SQL

SQLでCASE文の結果をWHERE句に指定する方法およびコスト面の考察

投稿日:2019年9月16日 更新日:

本記事では、
・SQLでCASE文の結果をWHERE句に指定する方法について記述します。
・CASE文を使う場合のコストについて考察します。

(0)目次&概説

(1) NG例
(2) OK例&解説
(3) CASE文の性能考察
 (3-1) UNIONで書いた時コスト
 (3-2) CASEで書いた時のコスト
 (3-3) 考察

(1) NG例

例えば「EMP」表と「DEPT」表の結合を例に考えてみます。
EMP表(TA)はCASE文で「SAL」カラムの多い少ないに応じて「HIGH SAL」または「LOW SAL」の値を動的に取得しています(例文1の4~7行目)
DEPT表(TB)は「DEPTNO」カラムでEMP表(TA)と結合(例文1の13行目)しています。

そしてここからが本題で、CASE文で取得した「SALARY」(別名)をWHERE句に使いたい場合です。(例文1:14行目)のように指定するとエラーになります(※「結局HIGHで絞るならCASE文いらなくね?」というツッコミもあると思いますが、まあ例なので・・笑)

(例文1)※構文エラー

SELECT
  --CASE文で下記のルールで値を動的に取得
  --SALが40万以上なら「HIGH SAL」/40万未満なら「LOW SAL」
  CASE
    WHEN TA.SAL > 400000 THEN 'HIGH SAL'
    WHEN TA.SAL < 400000 THEN 'LOW SAL'
  END AS SALARY
  ,TA.*
  ,TB.*
FROM 
  EMP TA
  ,DEPT TB
WHERE	TA.DEPTNO = TB.DEPTNO
AND 	SALARY = 'HIGH SAL'

(例文1)エラー

(2) OK例&解説

このエラーを回避するためにFROM句にテーブルをそのまま指定するのではなく、SELECT文の形で指定します。具体的には次の(例文2)の5行目~15行目のように、抽出データの加工はSELECTの部分ではなくFROMの部分で行っています。そうする事でWHERE句等でも指定が可能となります。

 (例文2)OK例

SELECT
  TA.*
  ,TB.* 
FROM
 (
  SELECT
	--CASE文で下記のルールで値を動的に取得
	--SALが40万以上なら「HIGH SAL」/40万未満なら「LOW SAL」
	CASE
	  WHEN E.SAL >= 400000 THEN 'HIGH SAL'
	  WHEN E.SAL < 400000 THEN 'LOW SAL'
	END AS SALARY
	,E.*
  FROM EMP E
 ) TA
,DEPT TB
WHERE	TA.DEPTNO = TB.DEPTNO
AND 	SALARY = 'HIGH SAL'


 (例文2)結果

目次にもどる

(3) CASE文の性能考察

次に性能面での考察を少し行います。今回の例は「EMP」表から「HIGH SAL」と「LOW SAL」の人数をそれぞれ出力するSQLを2通りのやり方で記述し、それぞれの「コスト」と比較を行います。

(3-1) UNIONで書いた時コスト

UNIONで書いた場合のコストは「917」で、実行時間が「0.049秒」でした。構造は、前半の「HIGH SAL」抽出SQLと後半の「LOW SAL」抽出SQLを単純にUNIONしているだけです。
(例文3)

-- ①'HIGH SAL'のデータを抽出するSQL
SELECT
  'HIGH SAL' AS SALARY
  ,COUNT(*)
FROM
	EMP TA
	,DEPT TB
WHERE	TA.DEPTNO = TB.DEPTNO
AND   TA.SAL >= 400000

--①と②を合わせる
UNION

-- ②'LOW SAL'のデータを抽出するSQL
SELECT
  'LOW SAL' AS SALARY
  ,COUNT(*)
FROM
	EMP TA
	,DEPT TB
WHERE	TA.DEPTNO = TB.DEPTNO
AND   TA.SAL < 400000

(例文3)結果

(3-2) CASEで書いた時コスト

CASEで書いた場合のコストは「459」で、実行時間が「0.069秒」でした。細かい部分は違えど、構造は(例文2)と同じです。
(例文4)

SELECT
  SALARY
  ,COUNT(SALARY)
FROM
	(
	  SELECT
		CASE
		  WHEN E.SAL >= 400000 THEN 'HIGH SAL'
		  WHEN E.SAL < 400000 THEN 'LOW SAL'
		END AS SALARY
		,E.*
	  FROM EMP E
	) TA
	,DEPT TB
WHERE	TA.DEPTNO = TB.DEPTNO
GROUP BY SALARY
ORDER BY SALARY

(例文4)結果

(3-3) 考察

CASEを使った方がUNIONよりもコスト(リソース消費)が抑えられたが、実行時間はUNIONの方が速かったため、必ずしもどちがら良いとは断言できず、環境のリソース状況や要件によってどのアプローチが最善かを検討する形になると考えます。

 >目次にもどる

Adsense審査用広告コード


Adsense審査用広告コード


-Database (データベース), OracleDB, SQL

執筆者:


comment

メールアドレスが公開されることはありません。 が付いている欄は必須項目です

関連記事

Linuxサーバ(CentOS6)にOracleDB11gをインストールする(その3)

目次 (5)リスナーの作成(netca)  (5-0)リスナーについての概要  (5-1)netcaの起動(netca)  (5-2)リスナー設定  (5-3)リスナー名の設定  (5-4)リスナーの …

SQL Developer接続エラーへの対処(ORA-12514, ORA-12505)

SQL Developer接続エラーへの対処 SQL Developerを起動してSYSTEMユーザで接続する際に発生した、下記2つのエラーの対処法について記載します。 エラー#1:ORA-12514 …

Oracleデータベースのバックアップをexpdpで取得する方法

(0)目次&概説 (1) expdp/impdpについて  (1-1) expdp/impdpの概要  (1-2) expdp/impdpの配置場所や転送方法 (2) エクスポート手順(expdp) …

Linuxサーバ(CentOS6)にOracleDB11gをインストールする(その2)

目次 (4)インストーラの進行  (4-1)セキュリティUpdate構成  (4-2)インストールオプション選択  (4-3)Gridインストールオプション  (4-4)製品言語の選択  (4-5)デ …

ORA-03113 end-of-file on communication channelエラーの対処

”ORA-03113 end-of-file on communication channel”の対処法について記述します。 (0) 目次 (1) 解析 (2) 原因 (3) 対策 (4) 解説  ( …

  • English (United States)
  • 日本語
Top