External Table Operations in SET EXPLAIN Output

The Query Statistics section of SET EXPLAIN output provides information on operations that are loading data from or unloading data to an external table.

The following codes in the Query Statistics section of the SET EXPLAIN output file provides information on external tables:

  • xlcnv identifies an operation that is loading data from an external table and inserting the data into a base table. Here x = external table, l = loading, and cnv = converter
  • xucnv identifies an operation that is reading data from the base table and writing to the file that the external table is pointing to. Here x = external table, u = unloading, and cnv = converter

Examples

The following example shows a query in which an operating is loading data from an external table and inserting the data into a base table:

QUERY: (OPTIMIZATION TIMESTAMP: 11-11-2009 12:55:20)
------
insert into items select * from ext_items

Estimated Cost: 5
Estimated # of Rows Returned: 68

  1) informix.ext_items: SEQUENTIAL SCAN

Query statistics:
-----------------
  Table map :
  ----------------------------
  Internal name     Table name
  ----------------------------
  t1                items

  type     it_count   time
  ----------------------------
  xlread   1          00:00.00

  type     it_count   time
  ----------------------------
  xlcnv    67         00:00.00

  type     table  rows_ins   time
  -----------------------------------
  insert   t1     67         00:00.00

The following example shows a query in which an operating is loading data from an external table and inserting the data into a base table:

QUERY: (OPTIMIZATION TIMESTAMP: 11-11-2009 12:47:55)
------
select * from orders into external ord_ext
using (datafiles ('disk:/tmp/ord'))


Estimated Cost: 2
Estimated # of Rows Returned: 23

  1) informix.orders: SEQUENTIAL SCAN


Query statistics:
-----------------

  Table map :
  ----------------------------
  Internal name     Table name
  ----------------------------
  t1                orders

  type     table  rows_prod  est_rows  rows_scan  time       est_cost
  -------------------------------------------------------------------
  scan     t1     23         23        23         00:00.00   3

  type     it_count   time
  ----------------------------
  xucnv    23         00:00.00

  type     it_count   time
  ----------------------------
  xuwrite  23         00:00.00