- 类别：
SYSTEM$EXPLAIN_JSON_TO_TEXT¶
此函数将 EXPLAIN 输出从 JSON 转换为格式化文本。
语法¶
SYSTEM$EXPLAIN_JSON_TO_TEXT( <explain_output_in_json_format> )
实参¶
explain_output_in_json_format字符串或计算结果为字符串的表达式，包含 JSON 兼容字符串形式的 EXPLAIN 输出。如果输入是字符串，则字符串应放在单引号
'内。
返回¶
该函数返回一个 VARCHAR，其中包含文本形式的 EXPLAIN 输出，其格式对于人类来说相对容易阅读。
使用说明¶
此函数将 EXPLAIN 信息从 JSON 转换为格式化文本。通常，JSON 值直接或间接由 SYSTEM$EXPLAIN_PLAN_JSON 函数生成。例如，SYSTEM$EXPLAIN_PLAN_JSON 的输出可以存储在一个表中，然后使用这个 SYSTEM$EXPLAIN_JSON_TO_TEXT 函数显示出来。
如果字符串字面量作为输入传递，字符串周围的分隔符可以是单引号
'或双美元符号$$。如果字符串字面量包含单引号（并且不包含双美元符号），则通过使用双美元符号分隔字符串，便无需将字符串中嵌入的单引号字符进行转义。
示例¶
下面的示例使用以下表格：
CREATE TABLE Z1 (ID INTEGER); CREATE TABLE Z2 (ID INTEGER); CREATE TABLE Z3 (ID INTEGER);
如果要以 JSON 格式存储 EXPLAIN 输出，但将其显示为格式化文本，则可以调用 SYSTEM$EXPLAIN_JSON_TO_TEXT()，如下所示：
首先，获取 JSON 格式的 EXPLAIN 输出，并将其存储在表中：
SET QUERY_10 = 'SELECT Z1.ID, Z2.ID FROM Z1, Z2 WHERE Z2.ID = Z1.ID'; CREATE TABLE json_explain_output_for_analysis ( ID INTEGER, query VARCHAR, explain_plan VARCHAR ); INSERT INTO json_explain_output_for_analysis (ID, query, explain_plan) SELECT 1, $QUERY_10 AS query, SYSTEM$EXPLAIN_PLAN_JSON($QUERY_10) AS explain_plan;JSON 输出如下所示：
SELECT query, explain_plan FROM json_explain_output_for_analysis; +-----------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | QUERY | EXPLAIN_PLAN | |-----------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| | SELECT Z1.ID, Z2.ID FROM Z1, Z2 WHERE Z2.ID = Z1.ID | {"GlobalStats":{"partitionsTotal":2,"partitionsAssigned":2,"bytesAssigned":1024},"Operations":[[{"id":0,"operation":"Result","expressions":["Z1.ID","Z2.ID"]},{"id":1,"parentOperators":[0],"operation":"InnerJoin","expressions":["joinKey: (Z2.ID = Z1.ID)"]},{"id":2,"parentOperators":[1],"operation":"TableScan","objects":["TESTDB.TEMPORARY_DOC_TEST.Z2"],"expressions":["ID"],"partitionsAssigned":1,"partitionsTotal":1,"bytesAssigned":512},{"id":3,"parentOperators":[1],"operation":"JoinFilter","expressions":["joinKey: (Z2.ID = Z1.ID)"]},{"id":4,"parentOperators":[3],"operation":"TableScan","objects":["TESTDB.TEMPORARY_DOC_TEST.Z1"],"expressions":["ID"],"partitionsAssigned":1,"partitionsTotal":1,"bytesAssigned":512}]]} | +-----------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+将 JSON 存储在表中后，可以将 JSON 传递给 SYSTEM$EXPLAIN_JSON_TO_TEXT 函数，以通过调用 SYSTEM&EXPLAIN_JSON_TO_TEXT 将其转换为更易于人类阅读的文本格式：
SELECT SYSTEM$EXPLAIN_JSON_TO_TEXT(explain_plan) FROM json_explain_output_for_analysis WHERE json_explain_output_for_analysis.ID = 1; +------------------------------------------------------------------------------------------------------------------------------------+ | SYSTEM$EXPLAIN_JSON_TO_TEXT(EXPLAIN_PLAN) | |------------------------------------------------------------------------------------------------------------------------------------| | GlobalStats: | | bytesAssigned=1024 | | partitionsAssigned=2 | | partitionsTotal=2 | | Operations: | | 1:0 ->Result Z1.ID, Z2.ID | | 1:1 ->InnerJoin joinKey: (Z2.ID = Z1.ID) | | 1:2 ->TableScan TESTDB.TEMPORARY_DOC_TEST.Z2 ID {partitionsTotal=1, partitionsAssigned=1, bytesAssigned=512} | | 1:3 ->JoinFilter joinKey: (Z2.ID = Z1.ID) | | 1:4 ->TableScan TESTDB.TEMPORARY_DOC_TEST.Z1 ID {partitionsTotal=1, partitionsAssigned=1, bytesAssigned=512} | | | +------------------------------------------------------------------------------------------------------------------------------------+