oracle柱状压缩语句,oracle柱状分析，实现sql性能优化

最近在做优化的时候，遇见项目中的一条sql语句buffer get特高，重建表后进行统计更新，发现还是很大消耗，最后做了柱状分析就很好改善。

SQL> select ALARMNUMBER,BELONGEQUIP,BELONGFRAME,BELONGSLOT,BELONGPACK

2  ,BELONGPORT,STARTTIME,ALARMLEVEL from yujing.ALARMINFO where ALARMOBJECT = '00000000000100980563' and OBJCLASS = 'port' and ALARMTYPE =

3  2 and PROBABLECAUSE = '7' and ALARMTEXT='复用段远端误码指示' and ISCLEARED = 0;

未选定行

Execution Plan

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

0      SELECT STATEMENT Optimizer=CHOOSE (Cost=9 Card=1 Bytes=127)

1    0   TABLE ACCESS (BY INDEX ROWID) OF 'ALARMINFO' (Cost=9 Card=

1 Bytes=127)

2    1     AND-EQUAL

3    2       INDEX (RANGE SCAN) OF 'ALARMINFO2' (NON-UNIQUE)

4    2       INDEX (RANGE SCAN) OF 'ALARMINFO3' (NON-UNIQUE)

Statistics

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

0  recursive calls

0  db block gets

294276  consistent gets

0  physical reads

0  redo size

766  bytes sent via SQL*Net to client

461  bytes received via SQL*Net from client

1  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

0  rows processed

SQL> exec dbms_stats.gather_table_stats(ownname=>'YUJING',tabname=>'ALARMINFO',estimate_percent=>20,method_opt=>'for all indexed columns size skewonly',cascade=>true,degree=>2);

PL/SQL 过程已成功完成。

SQL> select ALARMNUMBER,BELONGEQUIP,BELONGFRAME,BELONGSLOT,BELONGPACK

2  ,BELONGPORT,STARTTIME,ALARMLEVEL from yujing.ALARMINFO where ALARMOBJECT = '00000000000100980563' and OBJCLASS = 'port' and ALARMTYPE =

3  2 and PROBABLECAUSE = '7' and ALARMTEXT='复用段远端误码指示' and ISCLEARED = 0;

未选定行

Execution Plan

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

0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1381 Card=1 Bytes=13

3)

1    0   TABLE ACCESS (BY INDEX ROWID) OF 'ALARMINFO' (Cost=1381 Ca

rd=1 Bytes=133)

2    1     INDEX (RANGE SCAN) OF 'ALARMINFO_I_ISCLEARED' (NON-UNIQU

E) (Cost=39 Card=18660)

Statistics

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

0  recursive calls

0  db block gets

9803  consistent gets

0  physical reads

0  redo size

766  bytes sent via SQL*Net to client

461  bytes received via SQL*Net from client

1  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

0  rows processed