并不是因为完全为空的条目不被记录到索引中,就坚决不能使用空值,相反,有时候合理使用oracle的空值会为我们的查询带来几倍甚至几十倍的效率提高。
举个例子,加入有一个表,里面有个字段是“处理时间”,如果没有处理的事务,该列就为空,并且在大部分情况下,处理的事务总是在总记录数的10%或者更少,而等待处理的记录(“处理时间”这列为空)总是绝大多数的记录,那么在“等待时间”这列上建立索引,索引中就总是会保存很少的记录,我们希望的访问方式是,当访问表中所有代处理的记录(即10%或者更多的记录数目)时,我们希望通过全表扫描的方式来检索;然而,当我们希望访问已经处理的事务(即5%或者更少的记录数目)时,我们希望通过索引来访问,因为索引中的记录数目很少,请看下面的例子:
SQL> create table tt as select * from sys.dba_objects;
Table created
Executed in 0.601 seconds
SQL> alter table tt add (t int);
Table altered
Executed in 0.061 seconds
SQL> select count(*) from tt;
COUNT(*)
----------
6131c
Executed in 0.01 seconds
SQL> UPDATE tt set t=1 where owner='DEMO';
10 rows updated
Executed in 0.03 seconds
SQL> COMMIT;
Commit complete
Executed in 0 seconds
SQL> select count(*) from tt where OWNER='DEMO';
COUNT(*)
----------
10 ――――――――――――――已经处理的数目
Executed in 0.08 seconds
s
SQL> select count(*) from tt;
COUNT(*)
----------
6131 ――――――――――――――总记录数目
Executed in 0.01 seconds
下面的查询因为访问表中的大多数记录(代处理的记录,即10%以上的记录数目),可以看见,它如我们所希望的那样使用了全表扫描:
select object_name from tt where t is null;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
|* 1 | TABLE ACCESS FULL | TT | | | |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("TT"."T" IS NULL)
Note: rule based optimization
14 rows selected
Executed in 0.05 seconds
下面的查询因为要访问表中的少数记录,我们希望通过索引来访问:
select object_name from tt where t=1;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
|* 1 | TABLE ACCESS FULL | TT | | | |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("TT"."T"=1)
Note: rule based optimization
14 rows selected
Executed in 0.06 seconds
请注意,这里并没有如我们所希望的那样使用索引,而是使用了全表扫描,这里有一个结论:
建立了索引后,要想在CBO下合理的使用索引,一定要定期的更新统计信息
下面我们分析一下索引,看看有什么效果:
SQL> analyze index tt_idx validate structure;
Index analyzed
Executed in 0 seconds
SQL> select lf_rows from index_stats;
LF_ROWS
----------
10 ――――――――――索引中总共有10行
Executed in 0.05 seconds
SQL> exec dbms_stats.gather_index_stats('DEMO','TT_IDX');
PL/SQL procedure successfully completed
Executed in 0.03 seconds
SQL> SELECT DISTINCT_KEYS FROM USER_INDEXES;
DISTINCT_KEYS
-------------
1 ――――――――――只有一个键值
Executed in 0.05 seconds
SQL> select * from tt where t is null;
已选择6121行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'TT'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
485 consistent gets
0 physical reads
0 redo size
355012 bytes sent via SQL*Net to client
4991 bytes received via SQL*Net from client
410 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6121 rows processed
SQL> select * from tt where t=5;
未选定行
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TT'
2 1 INDEX (RANGE SCAN) OF 'TT_IDX' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
964 bytes sent via SQL*Net to client
372 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> select * from tt where t=1;
已选择10行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TT'
2 1 INDEX (RANGE SCAN) OF 'TT_IDX' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1639 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
SQL> update tt set t=2 where t=1;
已更新10行。
Execution Plan
----------------------------------------------------------
0 UPDATE STATEMENT Optimizer=CHOOSE
1 0 UPDATE OF 'TT'
2 1 INDEX (RANGE SCAN) OF 'TT_IDX' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
14 db block gets
1 consistent gets
0 physical reads
3216 redo size
616 bytes sent via SQL*Net to client
527 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
10 rows processed
SQL> set autotrace traceonly
SQL> update tt set t=3 where t is null;
6121 rows updated.
Execution Plan
----------------------------------------------------------
0 UPDATE STATEMENT Optimizer=CHOOSE
1 0 UPDATE OF 'TT'
2 1 TABLE ACCESS (FULL) OF 'TT'
Statistics
----------------------------------------------------------
0 recursive calls
18683 db block gets
80 consistent gets
0 physical reads
2583556 redo size
618 bytes sent via SQL*Net to client
533 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
6121 rows processed
SQL>
本文地址:http://com.8s8s.com/it/it19843.htm