Example 14-1 Display the sharding configurations in ranking order

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;

Example 14-2 Display the table family of the top ranked sharding configuration

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;

Example 14-3 Display the table families in ranking order

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;

Example 14-4 Display the duplicated tables of the top ranked sharding configuration

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;

Example 14-5 Display the number of sharding configurations with table_name as the root table

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

Example 14-6 Display the table families of the sharding configurations with root table 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;

Example 14-7 Display the details of the sharding configurations in ranking order

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;

Example 14-8 Display the details of your chosen sharding configuration

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;