oracle日期减5s,oracle 一条简单sql的优化

2、建立索引：

create index i_vou_info_batch_cust_no on t_vou_XXXX( batch_no ,customer_no ) tablespace TBS_VOUCHER_IDX   local online parallel 8;

alter index i_vou_info_batch_cust_no noparallel;

3、invisible原有的索引：

alter index I_VOU_INFO_BATCH_NO invisible;

4、观察是否使用新建的索引，及前后的性能变化：

Plan hash value: 1183751283

-----------------------------------------------------------------------------------------------------------------

| Id  | Operation            | Name                     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

-----------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |                          |       |       |   179 (100)|          |       |       |

|   1 |  SORT AGGREGATE      |                          |     1 |    27 |            |          |       |       |

|   2 |   PARTITION RANGE ALL|                          |  7882 |   207K|   179   (1)| 00:00:03 |     1 |1048575|

|*  3 |    INDEX RANGE SCAN  | I_VOU_INFO_BATCH_CUST_NO |  7882 |   207K|   179   (1)| 00:00:03 |     1 |1048575|

-----------------------------------------------------------------------------------------------------------------

Peeked Binds (identified by position):

--------------------------------------

1 - :1 (VARCHAR2(30), CSID=871): 'B20160512112812105'

Predicate Information (identified by operation id):

---------------------------------------------------

3 - access("BATCH_NO"=:1)

filter("CUSTOMER_NO" IS NOT NULL)

优化后的sql，执行时间由原来的平均每次332s，到优化有的平均每次不超过2s，优化后的sql执行性能有明显的提高。

5、删除原有的索引：

drop index I_VOU_INFO_BATCH_NO;

---gt