[20140829]在vim中使用align.txt

--经常上一些网站,发现一些帖子贴出的sql的执行计划出现参差不齐的情况,很难看懂.
--有一次我通过QQ解决问题,对方贴出执行计划,也是这种情况,本来这些执行计划就很复杂,加上不对齐,确实影响工作与解决问题的心情.

--我想起在vim下格式化sql语句时,需要安装align插件,应该使用它能够解决问题.

--下载安装align插件很简单。

1.ALign 插件:
http://www.vim.org/scripts/script.php?script_id=294
http://www.vim.org/scripts/download_script.php?src_id=19633

安装很简单:
vim Align.vba.gz
:so %
:q

--注意要删除旧的AlignMaps.vim文件，不要map会冲突！新的改名为AlignMapsPlugin.vim。

2.查看相关文档:
:help align

3.文档写的很复杂,而我的需求很简单,就是显示执行计划要美观.通过例子来讲解:

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  cj135jhhjjzsd, child number 0
-------------------------------------
select dept.dname,emp.* from dept,emp where dept.deptno=emp.deptno and
dept.deptno=10

Plan hash value: 568005898

---------------------------------------------------------------------------------------------------------------
|Id|Operation|Name|Starts|E-Rows|Cost(%CPU)|A-Rows|A-Time|Buffers|
---------------------------------------------------------------------------------------------------------------
|0|SELECT STATEMENT||1||5(100)|3|00:00:00.01|9|
|1|NESTED LOOPS||1|3|5(0)|3|00:00:00.01|9|
|2|TABLE ACCESSBY INDEX ROWID|DEPT|1|1|2(0)|1|00:00:00.01|2|
|*3|INDEX UNIQUE SCAN|PK_DEPT|1|1|1(0)|1|00:00:00.01|1|
|*4|TABLE ACCESS FULL|EMP|1|3|3(0)|3|00:00:00.01|7|
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("DEPT"."DEPTNO"=10)
   4 - filter("EMP"."DEPTNO"=10)

a.操作如下,移动id那一行.ma,做标记a.
b.移动到*4那一行.
c.定义格式化参数:
:AlignCtrl lrllrrrrrrrrrrr
--说明实际上控制格式化显示样式,很容易猜测,l表示left靠左边显示,r表示right靠右边显示,另外注意执行计划开始就是分隔符号|
--*2 实际上是第2个字段.这里就是r,表示这列靠右边显示.还有:表示后面的列不格式化.

d.格式化:

:'a,.Align |

--结合我重新映射删除空格操作很快的. 或者开始操作前删除仅仅有横线那一行。
--链接:http://blog.itpub.net/267265/viewspace-1244321/

4.效果如下:

---------------------------------------------------------------------------------------------------------------
| Id | Operation                  | Name    | Starts | E-Rows | Cost(%CPU) | A-Rows |      A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT           |         |      1 |        |     5(100) |      3 | 00:00:00.01 |       9 |
|  1 | NESTED LOOPS               |         |      1 |      3 |       5(0) |      3 | 00:00:00.01 |       9 |
|  2 | TABLE ACCESSBY INDEX ROWID | DEPT    |      1 |      1 |       2(0) |      1 | 00:00:00.01 |       2 |
| *3 | INDEX UNIQUE SCAN          | PK_DEPT |      1 |      1 |       1(0) |      1 | 00:00:00.01 |       1 |
| *4 | TABLE ACCESS FULL          | EMP     |      1 |      3 |       3(0) |      3 | 00:00:00.01 |       7 |
---------------------------------------------------------------------------------------------------------------

--这样就美观了.以下是实际的显示效果：
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |      1 |        |     5 (100)|      3 |00:00:00.01 |       9 |
|   1 |  NESTED LOOPS                |         |      1 |      3 |     5   (0)|      3 |00:00:00.01 |       9 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |      1 |     2   (0)|      1 |00:00:00.01 |       2 |
|*  3 |    INDEX UNIQUE SCAN         | PK_DEPT |      1 |      1 |     1   (0)|      1 |00:00:00.01 |       1 |
|*  4 |   TABLE ACCESS FULL          | EMP     |      1 |      3 |     3   (0)|      3 |00:00:00.01 |       7 |
---------------------------------------------------------------------------------------------------------------

--对比主要差别在与operation没有缩进。其他基本一样。

5.最后补充AlignCtrl中的:
Statistics
----------------------------------------------------------
0  recursive calls
0  db block gets
9  consistent gets
0  physical reads
0  redo size
1240  bytes sent via SQL*Net to client
520  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
3  rows processed

:AlignCtrl r:
:'a,.Align " "

--显示如下,这样仅仅对第一个空格前的列格式化.

Statistics
----------------------------------------------------------
   0   recursive calls
   0   db block gets
   9   consistent gets
   0   physical reads
   0   redo size
1240   bytes sent via SQL*Net to client
520   bytes received via SQL*Net from client
   2   SQL*Net roundtrips to/from client
   0   sorts (memory)
   0   sorts (disk)
   3   rows processed

6.还有许多功能,但是上面的这些对我已经足够了.做一个记录,需要了解更多:
:help align