GBase 8s 分析SQL

分析SQL

查看 SQL 执行时间

>time dbaccess store_demo sel_join.sql 
Database closed.


real	0m1.043s
user	0m0.002s
sys	0m0.007s

查看执行计划

SET EXPLAIN ON AVOID_EXECUTE;
    SELECT C.CUSTOMER_NUM, C.LNAME, C.FNAME, C.PHONE, O.ORDER_DATE
        FROM CUSTOMER C, ORDERS O
            WHERE C.CUSTOMER_NUM = O.CUSTOMER_NUM AND C.LNAME = 'Watson';
SET EXPLAIN OFF;

生成 sqlexplain.out 输出文件

QUERY: (OPTIMIZATION TIMESTAMP: 12-11-2009 20:28:42) ------
SELECT C.CUSTOMER_NUM, C.LNAME, C.FNAME, C.PHONE, O.ORDER_DATE
FROM CUSTOMER C, ORDERS O
WHERE C.CUSTOMER_NUM = O.CUSTOMER_NUM
AND C.LNAME = 'Watson’
Estimated Cost: 1014294
Estimated # of Rows Returned: 4
  1) informix.c: SEQUENTIAL SCAN  (Serial, fragments: ALL)
        Filters: informix.c.lname = 'Watson’
  2) informix.o: INDEX PATH
    (1) Index Name: informix. 112_23
        Index Keys: customer_num   (Serial, fragments: ALL)
        Lower Index Filter: informix.c.customer_num = informix.o.customer_num
NESTED LOOP JOIN
Internal name     Table name
  ----------------------------
  type     table  rows_prod  est_rows  rows_scan  time       est_cost
  -------------------------------------------------------------------
  scan     t1     1          1      10000041   01:44.91                1014293

  type     table  rows_prod  est_rows  rows_scan  time       est_cost
  -------------------------------------------------------------------
  scan     t2     14         50000000  14         00:00.28            1

  type     rows_prod  est_rows  time       est_cost
  -------------------------------------------------
  nljoin   14         5         01:45.19            1014294

基本原则是 避免循序读取 SEQUENTIAL SCAN (Serial, fragments: ALL)
检查估计所需的时间 －est_cost
检查所需扫描的行数 － est_rows