完整连接级别设置和输出示例
SET EXPLAIN 语句支持完整连接级别设置。
SET EXPLAIN 语句支持完整连接级别设置。这意味着在连接时将本地会话环境中的值传播到所有下列类型的新的或恢复的事务:
- 本地数据库之内的事务
- 跨同一服务器实例的数据库的分布式事务
- 跨两个或多个数据库服务器实例的数据库的分布式事务
- 带有在本地数据库中注册的符合 XA 的数据源的全局事务
如果您更改事务之内的 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 输出中的外部表操作
SET EXPLAIN 的 Query Statistics 部分提供关于从外部表加载数据或将数据卸载到外部表的操作的信息。
SET EXPLAIN 输出文件的 Query Statistics 部分中的下列代码提供关于外部表的信息:
- xlcnv 标识从外部表加载数据并将数据插入基础表的操作。此处,x = 外部表,l = 加载,且 cnv = 转换器
- xucnv 标识从基础表读数据并写到外部表正指向的文件的操作。此处,x = 外部表,u = 卸载,且 cnv = 转换器
示例
下列示例展示一查询,其中的操作是从外部表加载数据并将数据插入到基础表内:
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) gbasedbt.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
下列示例展示一查询,其中的操作是从基础表读数据并写到外部表指向的文件:
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) gbasedbt.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