シャーディング・アドバイザの出力を確認するためのSQLの例

シャーディング・アドバイザの出力は、通常のデータベース表に含まれているため、各種のSQL問合せを実行して様々な観点から出力を参照できます。

例 14-1 ランキング順でのシャーディング構成の表示

SELECT rank, tableName as tname, tabletype as type,
           tablelevel as tlevel, parent, shardby as shardBy,
           shardingorreferencecols as cols, unenforceableconstraints,
           sizeoftable  
FROM SHARDINGADVISOR_CONFIGURATIONS
ORDER BY rank, tlevel, tname, parent;

例14-2 最上位ランク・シャーディング構成の表ファミリの表示

SELECT rank, tableName as tname, tabletype as type,
        tablelevel as tlevel, parent, shardby as shardBy,
        shardingorreferencecols as cols, unenforceableconstraints,
        sizeoftable
FROM SHARDINGADVISOR_CONFIGURATIONS 
WHERE rank = 1 AND tabletype = 'S' 
ORDER BY tlevel, tname, parent;

例 14-3 ランキング順での表ファミリの表示

SELECT rank, tableName as tname, tabletype as type,
        tablelevel as tlevel, parent, shardby as shardBy,
        shardingorreferencecols as cols, unenforceableconstraints,
        sizeoftable 
FROM SHARDINGADVISOR_CONFIGURATIONS
WHERE tabletype = 'S'
ORDER BY rank, tlevel, tname, parent;

例14-4 最上位ランクのシャーディング構成の重複表の表示

SELECT rank, tableName as tname, tabletype as type,
           tablelevel as tlevel, parent, shardby as shardBy,
           shardingorreferencecols as cols, unenforceableconstraints,
           sizeoftable   
FROM SHARDINGADVISOR_CONFIGURATIONS   
WHERE rank = 1 AND tabletype = 'D'   
ORDER BY tlevel, tname, parent;

例 14-5 table_nameがルート表であるシャーディング構成の数の表示

SELECT COUNT(*)
FROM SHARDINGADVISOR_CONFIGURATIONS 
WHERE tablename = 'TABLE_NAME' AND tablelevel = 0;

例 14-6 table_nameがルート表であるシャーディング構成の表ファミリの表示

SELECT rank, tableName as tname, tabletype as type,
        tablelevel as tlevel, parent, shardby as shardBy, 
        shardingorreferencecols as cols 
FROM SHARDINGADVISOR_CONFIGURATIONS 
WHERE tabletype = 'S'
    AND rank IN
        (SELECT rank 
        FROM SHARDINGADVISOR_CONFIGURATIONS
        WHERE tablename = 'TABLE_NAME' and tablelevel = 0)
ORDER BY rank, tlevel, tname, parent;

例14-7 ランキング順でのシャーディング構成の詳細の表示

SELECT rank, chosenbyuser,
        numshardedtables as stabs, sizeofshardedtables as sizestabs,
        numduplicatedtables as dtabs,
        sizeofduplicatedtables as sizedtabs,
        numsingleshardqueries as numssq,
        nummultishardqueries as nummsq,
        numcrossshardqueries as numcsq, cost
FROM SHARDINGADVISOR_CONFIGDETAILS
ORDER BY rank;

例 14-8 選択したシャーディング構成の詳細の表示

SELECT rank,
        numshardedtables as stabs, sizeofshardedtables as sizestabs,
        numduplicatedtables as dtabs,
        sizeofduplicatedtables as sizedtabs,
        numsingleshardqueries as numssq,
        nummultishardqueries as nummsq,
        numcrossshardqueries as numcsq, cost
FROM SHARDINGADVISOR_CONFIGDETAILS
WHERE CHOSENBYUSER = ‘Y’
ORDER BY RANK;