Complete-Connection Level Settings and Output Examples

The SET EXPLAIN statement supports complete-connection level settings.

The SET EXPLAIN statement supports complete-connection level settings. This means that values in the local session environment at the time of connection are propagated to all new or resumed transactions of the following types:
  • transactions within the local database
  • distributed transactions across databases of the same server instance
  • distributed transactions across databases of two or more database server instances
  • global transactions with XA-compliant data sources that are registered in the local database
If you change the SET EXPLAIN setting within a transaction, the new value is propagated back to the local environment and also to all subsequent new or resumed transactions.

Examples of SET EXPLAIN Output

The following SQL statements cause the database server to write the query plans of the UPDATE statement (and of its subquery) to the default explain output file:
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;
The following information is displayed in the resulting output:
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) informix.orders: INDEX PATH

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

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

      1) informix.orders: SEQUENTIAL SCAN

            Filters: informix.orders.ship_weight < 50.00

The next example is based on the following SQL statements, which include a DELETE operation: 

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;

Below is the resulting output:

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