Oracle データベースから Azure SQL ファミリー（ + Synapse SQL ）に移行する際の照合順序の検討方法

概要

Oracle データベース（以後、オラクル）から Azure SQL ファミリー、あるいｈ Synapse SQL （以後、Azure SQL 群）に移行する際の照合順序の検討方法を共有します。Synapse SQL には専用 SQL プールとサーバーレス SQL プールがあり、いずれも照合順序の設定が可能です。

検証結果としては、下記のような照合順序を設定を検討する方針としました。Azure SQL 群を基準とする場合には、移行元のプログラムの書き換えが必要となる場合があります。

• オラクル型の照合を基準とする場合
  • オラクルの NLS 関連パラメータにて、NLS_NCHAR_CHARACTERSETがAL16UTF16であり、NLS_CHARACTERSETがAL32UTF8であることが前提
    • オラクルの NLS 関連パラメータにて、NLS_COMP が BINARYであり、NLS_SORT が BINARYであり、NLS_LANGUAGE が JAPANESEである場合
      • 基本的には、Japanese_XJIS_100_CS_AS_KS_WS_SC_UTF8 を設定
    • オラクルの NLS 関連パラメータにて、NLS_COMP が LINGUISTICであり、NLS_SORT が JAPANESE_M_CIである場合
      • 基本的には、Japanese_XJIS_100_CI_AS_SC_UTF8 を設定
    • オラクルの NLS 関連パラメータにて、NLS_COMP が LINGUISTICであり、NLS_SORT が JAPANESE_M_AIである場合
      • 基本的には、Japanese_XJIS_100_CI_AI_SC_UTF8 を設定
    • オラクルの NLS 関連パラメータにて、NLS_COMP が LINGUISTICであり、NLS_SORT が JAPANESE_Mである場合
      • 基本的には、Japanese_XJIS_100_CS_AS_KS_WS_SC_UTF8 を設定
  • その他の場合
    • オラクルの設定値から、適切な照合順序を設定
• Azure SQL 群を基準とする場合
  • BIツールなどから接続する場合
    • 基本的には、Japanese_XJIS_100_CI_AS_KS_WS を設定
  • その他の場合
    • 適切な照合順序を設定

オラクル側にて、主に確認すべきパラメータは次のものです。

• NLS_NCHAR_CHARACTERSET
• NLS_CHARACTERSET
• NLS_COMP
• NLS_SORT
• NLS_LANGUAGE

オラクルの設定値は、下記のクエリで確認できます。

SELECT *
FROM NLS_DATABASE_PARAMETERS
;

Linux 上にオラクルを構築した場合には、次の結果を取得できます。

PARAMETER	VALUE
NLS_RDBMS_VERSION	19.0.0.0.0
NLS_NCHAR_CONV_EXCP	FALSE
NLS_LENGTH_SEMANTICS	BYTE
NLS_COMP	BINARY
NLS_DUAL_CURRENCY	$
NLS_TIMESTAMP_TZ_FORMAT	DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_TZ_FORMAT	HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_FORMAT	DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_FORMAT	HH.MI.SSXFF AM
NLS_SORT	BINARY
NLS_DATE_LANGUAGE	AMERICAN
NLS_DATE_FORMAT	DD-MON-RR
NLS_CALENDAR	GREGORIAN
NLS_NUMERIC_CHARACTERS	.,
NLS_NCHAR_CHARACTERSET	AL16UTF16
NLS_CHARACTERSET	AL32UTF8
NLS_ISO_CURRENCY	AMERICA
NLS_CURRENCY	$
NLS_TERRITORY	AMERICA
NLS_LANGUAGE	AMERICAN

検証方法

オラクルにて設定される照合のパターンを検討後、Azure SQL 群での照合順序の観点を考慮した上で、お互いのサービスに SQL を発行して確認します。

オラクルを日本語で利用する場合には、次のパターンがあるようです。

1. NLS_COMP が BINARYであり、NLS_SORT が BINARYである場合
2. NLS_COMP が LINGUISTICであり、NLS_SORT が JAPANESE_M_CIである場合
3. NLS_COMP が LINGUISTICであり、NLS_SORT が JAPANESE_M_AIである場合
4. NLS_COMP が LINGUISTICであり、NLS_SORT が JAPANESE_Mである場合

Azure SQL 群では、次の観点が主に確認が必要なオプションです。

#	オプション	確認方法
1	大文字と小文字を区別する (_CS)	A と a を区別するか
2	アクセントを区別する (_AS)	ấ と A を区別するか
3	かなを区別する (_KS)	ふ と フ を区別するか
4	文字幅を区別する (_WS)	A と Ａ を区別するか

オラクルのNLS_COMP 設定値が BINARYである場合には、 Azure SQL 群ではバイナリコード ポイント (_BIN2)オプションを設定を想定していたのですが、UTF-8をサポートしていないことからバイナリコード ポイント (_BIN2)オプションを利用しませんでした。

Azure SQL 群にて日本語を扱う場合には、Japanese_XJIS_100の利用を前提とします。Japanese_XJIS_140を利用しても問題ないと思いますが、専用 SQL プールではサポートされていないようです。

引用元：データ ウェアハウスの照合順序の種類 - Azure Synapse Analytics | Microsoft Learn

オラクルと Azure SQL 群 に対して、次のようなSQLを DB の仕様差異を反映したうえで発行し、オラクルの照合と同等の Azure SQL 群の照合順序を確認します。ソート順を基準には実施しておりません。

SELECT
  CASE 
    WHEN 'A' = 'a'
        THEN 'CI'
    ELSE
        'CS'
    END AS CS_CHECK
  ,CASE 
    WHEN 'ấ' = 'A'
        THEN 'AI'
    ELSE
        'AS'
    END AS AS_CHECK
  ,CASE 
    WHEN 'ふ' = 'フ'
        THEN 'KI'
    ELSE
        'KS'
    END AS KS_CHECK
  ,CASE 
    WHEN 'A' = 'Ａ'
        THEN 'WI'
    ELSE
        'WS'
    END AS WS_CHECK
  ,DATALENGTH('A') AS EN_BYTES
  ,DATALENGTH('あ') AS JA_BYTES

検証結果

前提条件

次の環境で検証しております。

• オラクル環境
  • Oracle Linux Server release 7.9
  • Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
• Azure SQL 群
  • Microsoft SQL Azure (RTM) - 12.0.2000.8 Mar 8 2023 17:58:50 Copyright (C) 2022 Microsoft Corporation

オラクルの NLS 関連パラメータにて、NLS_COMP が BINARYであり、NLS_SORT が BINARYであり、NLS_LANGUAGE が JAPANESEである場合

オラクルでの確認 SQL と結果

ALTER SESSION SET NLS_COMP=BINARY;
ALTER SESSION SET NLS_SORT=BINARY;
ALTER SESSION SET NLS_LANGUAGE=JAPANESE;

SELECT
  'BINARY' AS NLS_COMP
  ,'BINARY' AS NLS_SORT
  ,CASE 
    WHEN 'A' = 'a'
        THEN 'CI'
    ELSE
        'CS'
    END AS CS_CHECK
  ,CASE 
    WHEN 'ấ' = 'A'
        THEN 'AI'
    ELSE
        'AS'
    END AS AS_CHECK
  ,CASE 
    WHEN 'ふ' = 'フ'
        THEN 'KI'
    ELSE
        'KS'
    END AS KS_CHECK
  ,CASE 
    WHEN 'A' = 'Ａ'
        THEN 'WI'
    ELSE
        'WS'
    END AS WS_CHECK
  ,LENGTHB('A') AS EN_BYTES
  ,LENGTHB('あ') AS JA_BYTES
FROM 
 DUAL
;

NLS_COMP	NLS_SORT	CS_CHECK	AS_CHECK	KS_CHECK	WS_CHECK	EN_BYTES	JA_BYTES
BINARY	BINARY	CS	AS	KS	WS	1	3

Azure 群での確認 SQL と結果

-- Japanese_XJIS_100_CS_AS_KS_WS_SC_UTF8
IF OBJECT_ID(N'tempdb..#Japanese_XJIS_100_CS_AS_KS_WS_SC_UTF8', N'U') IS NOT NULL
DROP TABLE #Japanese_XJIS_100_CS_AS_KS_WS_SC_UTF8;

CREATE TABLE #Japanese_XJIS_100_CS_AS_KS_WS_SC_UTF8(
     CS_CHECK varchar(10) COLLATE Japanese_XJIS_100_CS_AS_KS_WS_SC_UTF8 
     ,AS_CHECK varchar(10) COLLATE Japanese_XJIS_100_CS_AS_KS_WS_SC_UTF8 
     ,KS_CHECK varchar(10) COLLATE Japanese_XJIS_100_CS_AS_KS_WS_SC_UTF8
     ,WS_CHECK varchar(10) COLLATE Japanese_XJIS_100_CS_AS_KS_WS_SC_UTF8 
     ,EN_BYTES varchar(10) COLLATE Japanese_XJIS_100_CS_AS_KS_WS_SC_UTF8
     ,JA_BYTES varchar(10) COLLATE Japanese_XJIS_100_CS_AS_KS_WS_SC_UTF8 
)
;

INSERT INTO #Japanese_XJIS_100_CS_AS_KS_WS_SC_UTF8
SELECT 
    'A'
    ,N'ấ'
    ,N'ふ'
    ,N'A'
    ,N'A'
    ,N'あ'
;

SELECT
  N'Japanese_XJIS_100_CS_AS_KS_WS_SC_UTF8' AS Collation 
  ,CASE 
    WHEN CS_CHECK = 'a'
        THEN 'CI'
    ELSE
        'CS'
    END AS CS_CHECK
  ,CASE 
    WHEN AS_CHECK = 'a'
        THEN 'AI'
    ELSE
        'AS'
    END AS AS_CHECK
  ,CASE 
    WHEN KS_CHECK = N'フ'
        THEN 'KI'
    ELSE
        'KS'
    END AS KS_CHECK
  ,CASE 
    WHEN WS_CHECK = N'Ａ'
        THEN 'WI'
    ELSE
        'WS'
    END AS WS_CHECK 
   ,DATALENGTH(EN_BYTES) AS EN_BYTES
   ,DATALENGTH(JA_BYTES) AS JA_BYTES
FROM #Japanese_XJIS_100_CS_AS_KS_WS_SC_UTF8
;

Collation	CS_CHECK	AS_CHECK	KS_CHECK	WS_CHECK	EN_BYTES	JA_BYTES
Japanese_XJIS_100_CS_AS_KS_WS_SC_UTF8	CS	AS	KS	WS	1	3

オラクルの NLS 関連パラメータにて、NLS_COMP が LINGUISTICであり、NLS_SORT が JAPANESE_M_CIである場合

オラクルでの確認 SQL と結果

ALTER SESSION SET NLS_COMP=LINGUISTIC;
ALTER SESSION SET NLS_SORT=JAPANESE_M_CI;

SELECT
  'LINGUISTIC' AS NLS_COMP
  ,'JAPANESE_M_CI' AS NLS_SORT
  ,CASE 
    WHEN 'A' = 'a'
        THEN 'CI'
    ELSE
        'CS'
    END AS CS_CHECK
  ,CASE 
    WHEN 'ấ' = 'A'
        THEN 'AI'
    ELSE
        'AS'
    END AS AS_CHECK
  ,CASE 
    WHEN 'ふ' = 'フ'
        THEN 'KI'
    ELSE
        'KS'
    END AS KS_CHECK
  ,CASE 
    WHEN 'A' = 'Ａ'
        THEN 'WI'
    ELSE
        'WS'
    END AS WS_CHECK
  ,LENGTHB('A') AS EN_BYTES
  ,LENGTHB('あ') AS JA_BYTES
FROM 
 DUAL
;

NLS_COMP	NLS_SORT	CS_CHECK	AS_CHECK	KS_CHECK	WS_CHECK	EN_BYTES	JA_BYTES
LINGUISTIC	JAPANESE_M_CI	CI	AS	KI	WI	1	3

Azure 群での確認 SQL と結果

-- Japanese_XJIS_100_CI_AS_SC_UTF8
IF OBJECT_ID(N'tempdb..#Japanese_XJIS_100_CI_AS_SC_UTF8', N'U') IS NOT NULL
DROP TABLE #Japanese_XJIS_100_CI_AS_SC_UTF8;

CREATE TABLE #Japanese_XJIS_100_CI_AS_SC_UTF8(
     CS_CHECK nvarchar(10) COLLATE Japanese_XJIS_100_CI_AS_SC_UTF8 
     ,AS_CHECK nvarchar(10) COLLATE Japanese_XJIS_100_CI_AS_SC_UTF8 
     ,KS_CHECK nvarchar(10) COLLATE Japanese_XJIS_100_CI_AS_SC_UTF8 
     ,WS_CHECK nvarchar(10) COLLATE Japanese_XJIS_100_CI_AS_SC_UTF8 
     ,EN_BYTES varchar(10) COLLATE Japanese_XJIS_100_CS_AS_KS_WS_SC_UTF8
     ,JA_BYTES varchar(10) COLLATE Japanese_XJIS_100_CS_AS_KS_WS_SC_UTF8 
)
;

INSERT INTO #Japanese_XJIS_100_CI_AS_SC_UTF8
SELECT 
    'A'
    ,N'ấ'
    ,N'ふ'
    ,N'A'
    ,N'A'
    ,N'あ'
;

SELECT
  N'Japanese_XJIS_100_CI_AS_SC_UTF8' AS Collation 
  ,CASE 
    WHEN CS_CHECK = 'a'
        THEN 'CI'
    ELSE
        'CS'
    END AS CS_CHECK
  ,CASE 
    WHEN AS_CHECK = 'a'
        THEN 'AI'
    ELSE
        'AS'
    END AS AS_CHECK
  ,CASE 
    WHEN KS_CHECK = N'フ'
        THEN 'KI'
    ELSE
        'KS'
    END AS KS_CHECK
  ,CASE 
    WHEN WS_CHECK = N'Ａ'
        THEN 'WI'
    ELSE
        'WS'
    END AS WS_CHECK 
   ,DATALENGTH(EN_BYTES) AS EN_BYTES
   ,DATALENGTH(JA_BYTES) AS JA_BYTES
FROM #Japanese_XJIS_100_CI_AS_SC_UTF8
;

Collation	CS_CHECK	AS_CHECK	KS_CHECK	WS_CHECK	EN_BYTES	JA_BYTES
Japanese_XJIS_100_CI_AS_SC_UTF8	CI	AS	KI	WI	1	3

オラクルの NLS 関連パラメータにて、NLS_COMP が LINGUISTICであり、NLS_SORT が JAPANESE_M_AIである場合

オラクルでの確認 SQL と結果

ALTER SESSION SET NLS_COMP=LINGUISTIC;
ALTER SESSION SET NLS_SORT=JAPANESE_M_AI;

SELECT
  'LINGUISTIC' AS NLS_COMP
  ,'JAPANESE_M_AI' AS NLS_SORT
  ,CASE 
    WHEN 'A' = 'a'
        THEN 'CI'
    ELSE
        'CS'
    END AS CS_CHECK
  ,CASE 
    WHEN 'ấ' = 'A'
        THEN 'AI'
    ELSE
        'AS'
    END AS AS_CHECK
  ,CASE 
    WHEN 'ふ' = 'フ'
        THEN 'KI'
    ELSE
        'KS'
    END AS KS_CHECK
  ,CASE 
    WHEN 'A' = 'Ａ'
        THEN 'WI'
    ELSE
        'WS'
    END AS WS_CHECK
  ,LENGTHB('A') AS EN_BYTES    
  ,LENGTHB('あ') AS JA_BYTES    
FROM 
 DUAL
;

NLS_COMP	NLS_SORT	CS_CHECK	AS_CHECK	KS_CHECK	WS_CHECK	EN_BYTES	JA_BYTES
LINGUISTIC	JAPANESE_M_AI	CI	AI	KI	WI	1	3

Azure 群での確認 SQL と結果

-- Japanese_XJIS_100_CI_AI_SC_UTF8
IF OBJECT_ID(N'tempdb..#Japanese_XJIS_100_CI_AI_SC_UTF8', N'U') IS NOT NULL
DROP TABLE #Japanese_XJIS_100_CI_AI_SC_UTF8;

CREATE TABLE #Japanese_XJIS_100_CI_AI_SC_UTF8(
     CS_CHECK nvarchar(10) COLLATE Japanese_XJIS_100_CI_AI_SC_UTF8 
     ,AS_CHECK nvarchar(10) COLLATE Japanese_XJIS_100_CI_AI_SC_UTF8 
     ,KS_CHECK nvarchar(10) COLLATE Japanese_XJIS_100_CI_AI_SC_UTF8 
     ,WS_CHECK nvarchar(10) COLLATE Japanese_XJIS_100_CI_AI_SC_UTF8 
     ,EN_BYTES varchar(10) COLLATE Japanese_XJIS_100_CI_AI_SC_UTF8
     ,JA_BYTES varchar(10) COLLATE Japanese_XJIS_100_CI_AI_SC_UTF8 )
;

INSERT INTO #Japanese_XJIS_100_CI_AI_SC_UTF8
SELECT 
    'A'
    ,N'ấ'
    ,N'ふ'
    ,N'A'
    ,N'A'
    ,N'あ'
;

SELECT
  N'Japanese_XJIS_100_CI_AI_SC_UTF8' AS Collation 
  ,CASE 
    WHEN CS_CHECK = 'a'
        THEN 'CI'
    ELSE
        'CS'
    END AS CS_CHECK
  ,CASE 
    WHEN AS_CHECK = 'a'
        THEN 'AI'
    ELSE
        'AS'
    END AS AS_CHECK
  ,CASE 
    WHEN KS_CHECK = N'フ'
        THEN 'KI'
    ELSE
        'KS'
    END AS KS_CHECK
  ,CASE 
    WHEN WS_CHECK = N'Ａ'
        THEN 'WI'
    ELSE
        'WS'
    END AS WS_CHECK 
   ,DATALENGTH(EN_BYTES) AS EN_BYTES
   ,DATALENGTH(JA_BYTES) AS JA_BYTES
FROM #Japanese_XJIS_100_CI_AI_SC_UTF8
;

Collation	CS_CHECK	AS_CHECK	KS_CHECK	WS_CHECK	EN_BYTES	JA_BYTES
Japanese_XJIS_100_CI_AI_SC_UTF8	CI	AI	KI	WI	1	3

オラクルの NLS 関連パラメータにて、NLS_COMP が LINGUISTICであり、NLS_SORT が JAPANESE_Mである場合

オラクルでの確認 SQL と結果

ALTER SESSION SET NLS_COMP=LINGUISTIC;
ALTER SESSION SET NLS_SORT=JAPANESE_M;

SELECT
  'LINGUISTIC' AS NLS_COMP
  ,'JAPANESE_M' AS NLS_SORT
  ,CASE 
    WHEN 'A' = 'a'
        THEN 'CI'
    ELSE
        'CS'
    END AS CS_CHECK
  ,CASE 
    WHEN 'ấ' = 'A'
        THEN 'AI'
    ELSE
        'AS'
    END AS AS_CHECK
  ,CASE 
    WHEN 'ふ' = 'フ'
        THEN 'KI'
    ELSE
        'KS'
    END AS KS_CHECK
  ,CASE 
    WHEN 'A' = 'Ａ'
        THEN 'WI'
    ELSE
        'WS'
    END AS WS_CHECK
  ,LENGTHB('A') AS EN_BYTES    
  ,LENGTHB('あ') AS JA_BYTES    
FROM 
 DUAL
;

NLS_COMP	NLS_SORT	CS_CHECK	AS_CHECK	KS_CHECK	WS_CHECK	EN_BYTES	JA_BYTES
LINGUISTIC	JAPANESE_M	CS	AS	KS	WS	1	3

Azure 群での確認 SQL と結果

-- Japanese_XJIS_100_CS_AS_KS_WS_SC_UTF8
IF OBJECT_ID(N'tempdb..#Japanese_XJIS_100_CS_AS_KS_WS_SC_UTF8', N'U') IS NOT NULL
DROP TABLE #Japanese_XJIS_100_CS_AS_KS_WS_SC_UTF8;

CREATE TABLE #Japanese_XJIS_100_CS_AS_KS_WS_SC_UTF8(
     CS_CHECK nvarchar(10) COLLATE  Japanese_XJIS_100_CS_AS_KS_WS_SC_UTF8 
     ,AS_CHECK nvarchar(10) COLLATE Japanese_XJIS_100_CS_AS_KS_WS_SC_UTF8 
     ,KS_CHECK nvarchar(10) COLLATE Japanese_XJIS_100_CS_AS_KS_WS_SC_UTF8 
     ,WS_CHECK nvarchar(10) COLLATE Japanese_XJIS_100_CS_AS_KS_WS_SC_UTF8 
     ,EN_BYTES varchar(10) COLLATE  Japanese_XJIS_100_CS_AS_KS_WS_SC_UTF8
     ,JA_BYTES varchar(10) COLLATE  Japanese_XJIS_100_CS_AS_KS_WS_SC_UTF8 
)
;

INSERT INTO #Japanese_XJIS_100_CS_AS_KS_WS_SC_UTF8
SELECT 
    'A'
    ,N'ấ'
    ,N'ふ'
    ,N'A'
    ,N'A'
    ,N'あ'
;

SELECT
  N'Japanese_XJIS_100_CS_AS_KS_WS_SC_UTF8' AS Collation 
  ,CASE 
    WHEN CS_CHECK = 'a'
        THEN 'CI'
    ELSE
        'CS'
    END AS CS_CHECK
  ,CASE 
    WHEN AS_CHECK = 'a'
        THEN 'AI'
    ELSE
        'AS'
    END AS AS_CHECK
  ,CASE 
    WHEN KS_CHECK = N'フ'
        THEN 'KI'
    ELSE
        'KS'
    END AS KS_CHECK
  ,CASE 
    WHEN WS_CHECK = N'Ａ'
        THEN 'WI'
    ELSE
        'WS'
    END AS WS_CHECK 
   ,DATALENGTH(EN_BYTES) AS EN_BYTES
   ,DATALENGTH(JA_BYTES) AS JA_BYTES
FROM #Japanese_XJIS_100_CS_AS_KS_WS_SC_UTF8
;

Collation	CS_CHECK	AS_CHECK	KS_CHECK	WS_CHECK	EN_BYTES	JA_BYTES
Japanese_XJIS_100_CS_AS_KS_WS_SC_UTF8	CS	AS	KS	WS	1	3

参考リンク

• オラクルドキュメント
  • 言語ソートと照合 (oracle.com)
  • SQL操作の照合導出および決定ルール (oracle.com)
  • ロケール・データ (oracle.com)
  • グローバリゼーション・サポート環境の設定 (oracle.com)
• マイクロソフトドキュメント
  • 照合順序と Unicode のサポート - SQL Server | Microsoft Learn
• その他
  • SQL Server / SQL Database の照合順序とコードページの関係 at SE の雑記 (engineer-memo.com)
  • 20180216 sapporo techbar_db_migration (slideshare.net)
  • 主要RDBMS製品の比較 – 文字コード, 文字セット, 照合順序 | コーソルDatabaseエンジニアのBlog (cosol.jp)

更新履歴

2023/04/05: Oracle 側にて文字コードをUTF-8を前提としたバイト数に基づいた処理がよく実施されることが発覚したため、Azure SQL ファミリー側もUTF-8に変更