完整连接级别设置和输出示例

SET EXPLAIN 语句支持完整连接级别设置。

SET EXPLAIN 语句支持完整连接级别设置。这意味着在连接时将本地会话环境中的值传播到所有下列类型的新的或恢复的事务： 如果您更改事务之内的 SET EXPLAIN 设置，则将新的值传播回到本地环境以及所有随后的新的或恢复的事务。

SET EXPLAIN 输出的示例

下列 SQL 语句导致数据库服务器将 UPDATE 语句（及其子查询）的查询计划写到缺省的输出文件： 
DATABASE stores_demo;
SET EXPLAIN ON;
UPDATE orders SET ship_charge = ship_charge + 2.00 
   WHERE customer_num IN
      (SELECT orders.customer_num FROM orders 
          WHERE orders.ship_weight < 50);
CLOSE DATABASE;
在结果的输出中显示下列信息： 
QUERY:
------
update orders set ship_charge = ship_charge + 2.00 
where customer_num in
(select orders.customer_num from orders where
        orders.ship_weight < 50)

Estimated Cost: 4
Estimated # of Rows Returned: 8

  1) gbasedbt.orders: INDEX PATH

    (1) Index Keys: customer_num   (Serial, fragments: ALL)
        Lower Index Filter: gbasedbt.orders.customer_num = ANY 

    Subquery:
    ---------
    Estimated Cost: 2
    Estimated # of Rows Returned: 8
    (Temp Table For Subquery)

      1) gbasedbt.orders: SEQUENTIAL SCAN

            Filters: gbasedbt.orders.ship_weight < 50.00

下一示例基于下列 SQL 语句，其中包括 DELETE 操作：

DATABASE stores_demo;
SET EXPLAIN ON;
DELETE FROM catalog WHERE stock_num IN 
 (SELECT stock.stock_num FROM stock, catalog WHERE
        stock.stock_num = catalog.stock_num 
        AND stock.unit_price < 50);
CLOSE DATABASE;

以下是结果输出：

QUERY:
------
DELETE FROM catalog WHERE stock_num IN
 (SELECTstock.stock_num from stock, catalog 
    WHERE stock.stock_num = catalog.stock_num 
    AND stock.unit_price < 50);

Estimated Cost: 19
Estimated # of Rows Returned: 37

  1) ajay.catalog: INDEX PATH

    (1) Index Keys: stock_num manu_code   (Serial, fragments: ALL)
        Lower Index Filter: ajay.catalog.stock_num = ANY    

    Subquery:
    ---------
    Estimated Cost: 12
    Estimated # of Rows Returned: 44
    (Temp Table For Subquery)

      1) ajay.stock: SEQUENTIAL SCAN

            Filters: ajay.stock.unit_price < $50.00

      2) ajay.catalog: INDEX PATH

        (1) Index Keys: stock_num manu_code   
            (Key-Only)  (Serial, fragments: ALL)
            Lower Index Filter: 
            ajay.stock.stock_num = ajay.catalog.stock_num

NESTED LOOP JOIN
父主题： SET EXPLAIN 输出