我有表
CREATE TABLE THIS_ALARM (
BIANHAO VARCHAR2 (4) NOT NULL,
UNIT NUMBER (10) NOT NULL,
RECTIME DATE NOT NULL,
ALARM NUMBER (1) NOT NULL,
FREQ NUMBER (4,2) NOT NULL,
AU0 NUMBER (6,3) NOT NULL,
AUP0 NUMBER (4,1) NOT NULL,
AI0 NUMBER (6,3) NOT NULL,
AIP0 NUMBER (4,1) NOT NULL,
AU1 NUMBER (6,3) NOT NULL,
AUP1 NUMBER (4,1) NOT NULL,
AI1 NUMBER (6,3) NOT NULL,
AIP1 NUMBER (4,1) NOT NULL,
AU2 NUMBER (6,3) NOT NULL,
AUP2 NUMBER (4,1) NOT NULL,
AI2 NUMBER (6,3) NOT NULL,
AIP2 NUMBER (4,1) NOT NULL,
AU3 NUMBER (6,3) NOT NULL,
AUP3 NUMBER (4,1) NOT NULL,
AI3 NUMBER (6,3) NOT NULL,
AIP3 NUMBER (4,1) NOT NULL,
AU4 NUMBER (6,3) NOT NULL,
AUP4 NUMBER (4,1) NOT NULL,
AI4 NUMBER (6,3) NOT NULL,
AIP4 NUMBER (4,1) NOT NULL,
AU5 NUMBER (6,3) NOT NULL,
AUP5 NUMBER (4,1) NOT NULL,
AI5 NUMBER (6,3) NOT NULL,
AIP5 NUMBER (4,1) NOT NULL,
AU6 NUMBER (6,3) NOT NULL,
AUP6 NUMBER (4,1) NOT NULL,
AI6 NUMBER (6,3) NOT NULL,
AIP6 NUMBER (4,1) NOT NULL,
AU7 NUMBER (6,3) NOT NULL,
AUP7 NUMBER (4,1) NOT NULL,
AI7 NUMBER (6,3) NOT NULL,
AIP7 NUMBER (4,1) NOT NULL,
AU8 NUMBER (6,3) NOT NULL,
AUP8 NUMBER (4,1) NOT NULL,
AI8 NUMBER (6,3) NOT NULL,
AIP8 NUMBER (4,1) NOT NULL,
AU9 NUMBER (6,3) NOT NULL,
AUP9 NUMBER (4,1) NOT NULL,
AI9 NUMBER (6,3) NOT NULL,
AIP9 NUMBER (4,1) NOT NULL,
AU10 NUMBER (6,3) NOT NULL,
AUP10 NUMBER (4,1) NOT NULL,
AI10 NUMBER (6,3) NOT NULL,
AIP10 NUMBER (4,1) NOT NULL,
AU11 NUMBER (6,3) NOT NULL,
AUP11 NUMBER (4,1) NOT NULL,
AI11 NUMBER (6,3) NOT NULL,
AIP11 NUMBER (4,1) NOT NULL,
AU12 NUMBER (6,3) NOT NULL,
AUP12 NUMBER (4,1) NOT NULL,
AI12 NUMBER (6,3) NOT NULL,
AIP12 NUMBER (4,1) NOT NULL,
AU13 NUMBER (6,3) NOT NULL,
AUP13 NUMBER (4,1) NOT NULL,
AI13 NUMBER (6,3) NOT NULL,
AIP13 NUMBER (4,1) NOT NULL,
AU14 NUMBER (6,3) NOT NULL,
AUP14 NUMBER (4,1) NOT NULL,
AI14 NUMBER (6,3) NOT NULL,
AIP14 NUMBER (4,1) NOT NULL,
AU15 NUMBER (6,3) NOT NULL,
AUP15 NUMBER (4,1) NOT NULL,
AI15 NUMBER (6,3) NOT NULL,
AIP15 NUMBER (4,1) NOT NULL,
AU16 NUMBER (6,3) NOT NULL,
AUP16 NUMBER (4,1) NOT NULL,
AI16 NUMBER (6,3) NOT NULL,
AIP16 NUMBER (4,1) NOT NULL,
AU17 NUMBER (6,3) NOT NULL,
AUP17 NUMBER (4,1) NOT NULL,
AI17 NUMBER (6,3) NOT NULL,
AIP17 NUMBER (4,1) NOT NULL,
AU18 NUMBER (6,3) NOT NULL,
AUP18 NUMBER (4,1) NOT NULL,
AI18 NUMBER (6,3) NOT NULL,
AIP18 NUMBER (4,1) NOT NULL,
AU19 NUMBER (6,3) NOT NULL,
AUP19 NUMBER (4,1) NOT NULL,
AI19 NUMBER (6,3) NOT NULL,
AIP19 NUMBER (4,1) NOT NULL,
AU20 NUMBER (6,3) NOT NULL,
AUP20 NUMBER (4,1) NOT NULL,
AI20 NUMBER (6,3) NOT NULL,
AIP20 NUMBER (4,1) NOT NULL,
AU21 NUMBER (6,3) NOT NULL,
AUP21 NUMBER (4,1) NOT NULL,
AI21 NUMBER (6,3) NOT NULL,
AIP21 NUMBER (4,1) NOT NULL,
AU22 NUMBER (6,3) NOT NULL,
AUP22 NUMBER (4,1) NOT NULL,
AI22 NUMBER (6,3) NOT NULL,
AIP22 NUMBER (4,1) NOT NULL,
AU23 NUMBER (6,3) NOT NULL,
AUP23 NUMBER (4,1) NOT NULL,
AI23 NUMBER (6,3) NOT NULL,
AIP23 NUMBER (4,1) NOT NULL,
AU24 NUMBER (6,3) NOT NULL,
AUP24 NUMBER (4,1) NOT NULL,
AI24 NUMBER (6,3) NOT NULL,
AIP24 NUMBER (4,1) NOT NULL,
AU25 NUMBER (6,3) NOT NULL,
AUP25 NUMBER (4,1) NOT NULL,
AI25
---------------------------------------------------------------
如果你经常访问的数据都包含在 索引中
并且不为 null
优化器是可以选择 只扫描索引的，但需要做 analyze
如果
IOT 并不是一个特别好的选择
特别是数据变化频繁的话将造成很大的麻烦
窃以为使用索引比较好
SQL> select * from t;
SNO GRADE ADDRESS
---------- ---------- --------------------------------------------------
20 97215 珠海翠香路松林街2号7栋101
6 97217 珠海翠香路松林街2号7栋101
7 97219 珠海翠香路松林街2号7栋101
8 97221 珠海翠香路松林街2号7栋101
10 97229 珠海翠香路松林街2号7栋101
20 97215 珠海翠香路松林街2号7栋101
6 97217 珠海翠香路松林街2号7栋101
7 97219 珠海翠香路松林街2号7栋101
8 97221 珠海翠香路松林街2号7栋101
8 97223 珠海翠香路松林街2号7栋101
8 97225 珠海翠香路松林街2号7栋101
SNO GRADE ADDRESS
---------- ---------- --------------------------------------------------
9 97227 珠海翠香路松林街2号7栋101
10 97229 珠海翠香路松林街2号7栋101
20 97215 珠海翠香路松林街2号7栋101
6 97217 珠海翠香路松林街2号7栋101
7 97219 珠海翠香路松林街2号7栋101
8 97221 珠海翠香路松林街2号7栋101
8 97223 珠海翠香路松林街2号7栋101
8 97225 珠海翠香路松林街2号7栋101
9 97227 珠海翠香路松林街2号7栋101
已选择20行。
SQL> analyze table t compute statistics;
表已分析。
SQL> set autotrace on
SQL> select sno from t;
SNO
----------
10
10
20
20
20
6
6
6
7
7
7
SNO
----------
8
8
8
8
8
8
8
9
9
已选择20行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=20 Bytes=40)
1 0 INDEX (FULL SCAN) OF 'T1' (NON-UNIQUE) (Cost=1 Card=20 Byt
es=40)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
1111 bytes sent via SQLNet to client
536 bytes received via SQLNet from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
20 rows processed
SQL> analyze table t delete statistics;
表已分析。
SQL> select sno from t;
SNO
----------
20
6
7
8
10
20
6
7
8
8
8
SNO
----------
9
10
20
6
7
8
8
8
9
已选择20行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'T'
Statistics
----------------------------------------------------------
0 recursive calls
12 db block gets
7 consistent gets
0 physical reads
0 redo size
1174 bytes sent via SQLNet to client
536 bytes received via SQLNet from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
20 rows processed
SQL>