Oracle初学者入门指南-索引是干什么用的?
SQL> select sql_text
2 from v$sqltext a
3 where a.hash_value = (
4 select sql_hash_value from v$session b
5 where b.sid='&sid'
6 )
7 order by piece asc
8 /
SQL_TEXT
----------------------------------------------------------------
select * from i_cm_power t WHERE T.SJH='13911xxxxx6'
检查了一下该查询访问的数据表,居然一个索引都没有:
SQL> select index_name from dba_indexes where table_name=upper('i_cm_power');
INDEX_NAME
------------------------------
没有索引意味着,即使为了获取这一条记录,Oracle也必须对5.28G的一个表进行全表扫描,如果不慢那就怪了:
SQL> col segment_name for a20
SQL> select segment_name,bytes/1024/1024/1024
from dba_segments where segment_name=upper('i_cm_power');
SEGMENT_NAME BYTES/1024/1024/1024
-------------------- --------------------
I_CM_POWER 5.28173828125
创建一个索引再说:
SQL> create index idx_i_cm_power_sjh on i_cm_power(sjh);
Index created.
Elapsed: 00:20:50.73
SQL> col segment_name for a20
SQL> select segment_name,bytes/1024/1024 MB
2 from dba_segments where segment_name=upper('idx_i_cm_power_sjh');
SEGMENT_NAME MB
-------------------- ----------
IDX_I_CM_POWER_SJH 1360
SQL>
无疑这个索引对于这样的简单查询是大有益处的:
SQL> select * from i_cm_power t WHERE T.SJH='13911xxxxx6';
Elapsed: 00:00:00.07
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'I_CM_POWER'
2 1 INDEX (RANGE SCAN) OF 'IDX_I_CM_POWER_SJH' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
1022 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)
2 rows processed
然而在实际中,你需要考虑更多的因素。
增加索引会占用更多的存储空间;索引的维护会增加数据库的负担,如果有海量的数据加载,可能会极大影响性能...
所以事实可能总是比你想象的更复杂,你只有知道的更多...
By eygle on 2005-11-19 09:53 |
Comments (3) |
Beginner | 543 |
3 Comments
Hi,
Your website has some good articles, but why can't we see your complete articles ?
like this : 然而在实际中,你需要考虑更多的因素。
Should we install some programs ?
Thanks
增加索引会占用更多的存储空间;索引的维护会增加数据库的负担,如果有海量的数据加载,可能会极大影响性能...
这就是我说的其他因素啊.
很多东西,明白了原理之后,就是运用之妙,存乎一心了.
实际应用客户会更看重查询速度的 你想 如果用户做一个查询 让他认识很长的时间的查询 他能吗 ?很显然不能