MSSQL编程:利用索引的误区之五:空值的妙用
Cluster/NDB高冗余的存储引擎,用多台数据机器联合提供服务以提高整体性能和安全性。适合数据量大,安全和性能要求高的应用索引利用索引的误区之五:空值的妙用并非由于完整为空的条目不被纪录到索引中,就坚定不克不及利用空值,相反,偶然候公道利用oracle的空值会为我们的查询带来几倍乃至几十倍的效力进步。
举个例子,到场有一个表,内里有个字段是“处置工夫”,假如没有处置的事件,该列就为空,而且在年夜部分情形下,处置的事件老是在总纪录数的10%大概更少,而守候处置的纪录(“处置工夫”这列为空)老是尽年夜多半的纪录,那末在“守候工夫”这列上创建索引,索引中就老是会保留很少的纪录,我们但愿的会见体例是,当会见表中一切代处置的纪录(即10%大概更多的纪录数量)时,我们但愿经由过程全表扫描的体例来检索;但是,当我们但愿会见已处置的事件(即5%大概更少的纪录数量)时,我们但愿经由过程索引来会见,由于索引中的纪录数量很少,请看上面的例子:
SQL>createtablettasselect*fromsys.dba_objects;
Tablecreated
Executedin0.601seconds
SQL>altertablettadd(tint);
Tablealtered
Executedin0.061seconds
SQL>selectcount(*)fromtt;
COUNT(*)
----------
6131c
Executedin0.01seconds
SQL>UPDATEttsett=1whereowner=DEMO;
10rowsupdated
Executedin0.03seconds
SQL>COMMIT;
Commitcomplete
Executedin0seconds
SQL>selectcount(*)fromttwhereOWNER=DEMO;
COUNT(*)
----------
10DDDDDDDDDDDDDD已处置的数量
Executedin0.08seconds
s
SQL>selectcount(*)fromtt;
COUNT(*)
----------
6131DDDDDDDDDDDDDD总纪录数量
Executedin0.01seconds
上面的查询由于会见表中的年夜多半纪录(代处置的纪录,即10%以上的纪录数量),能够瞥见,它如我们所但愿的那样利用了全表扫描:
selectobject_namefromttwheretisnull;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost|
--------------------------------------------------------------------
|0|SELECTSTATEMENT|||||
|*1|TABLEACCESSFULL|TT||||
--------------------------------------------------------------------
PredicateInformation(identifiedbyoperationid):
---------------------------------------------------
1-filter("TT"."T"ISNULL)
Note:rulebasedoptimization
14rowsselected
Executedin0.05seconds
上面的查询由于要会见表中的多数纪录,我们但愿经由过程索引来会见:
selectobject_namefromttwheret=1;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost|
--------------------------------------------------------------------
|0|SELECTSTATEMENT|||||
|*1|TABLEACCESSFULL|TT||||
--------------------------------------------------------------------
PredicateInformation(identifiedbyoperationid):
---------------------------------------------------
1-filter("TT"."T"=1)
Note:rulebasedoptimization
14rowsselected
Executedin0.06seconds
请注重,这里并没有如我们所但愿的那样利用索引,而是利用了全表扫描,这里有一个结论:
创建了索引后,要想在CBO下公道的利用索引,必定要按期的更新统计信息
上面我们剖析一下索引,看看有甚么效果:
SQL>analyzeindextt_idxvalidatestructure;
Indexanalyzed
Executedin0seconds
SQL>selectlf_rowsfromindex_stats;
LF_ROWS
----------
10DDDDDDDDDD索引中统共有10行
Executedin0.05seconds
SQL>execdbms_stats.gather_index_stats(DEMO,TT_IDX);
PL/SQLproceduresuccessfullycompleted
Executedin0.03seconds
SQL>SELECTDISTINCT_KEYSFROMUSER_INDEXES;
DISTINCT_KEYS
-------------
1DDDDDDDDDD只要一个键值
Executedin0.05seconds
SQL>select*fromttwheretisnull;
已选择6121行。
ExecutionPlan
----------------------------------------------------------
0SELECTSTATEMENTOptimizer=CHOOSE
10TABLEACCESS(FULL)OFTT
Statistics
----------------------------------------------------------
0recursivecalls
0dbblockgets
485consistentgets
0physicalreads
0redosize
355012bytessentviaSQL*Nettoclient
4991bytesreceivedviaSQL*Netfromclient
410SQL*Netroundtripsto/fromclient
0sorts(memory)
0sorts(disk)
6121rowsprocessed
SQL>select*fromttwheret=5;
未选定行
ExecutionPlan
----------------------------------------------------------
0SELECTSTATEMENTOptimizer=CHOOSE
10TABLEACCESS(BYINDEXROWID)OFTT
21INDEX(RANGESCAN)OFTT_IDX(NON-UNIQUE)
Statistics
----------------------------------------------------------
0recursivecalls
0dbblockgets
1consistentgets
0physicalreads
0redosize
964bytessentviaSQL*Nettoclient
372bytesreceivedviaSQL*Netfromclient
1SQL*Netroundtripsto/fromclient
0sorts(memory)
0sorts(disk)
0rowsprocessed
SQL>select*fromttwheret=1;
已选择10行。
ExecutionPlan
----------------------------------------------------------
0SELECTSTATEMENTOptimizer=CHOOSE
10TABLEACCESS(BYINDEXROWID)OFTT
21INDEX(RANGESCAN)OFTT_IDX(NON-UNIQUE)
Statistics
----------------------------------------------------------
0recursivecalls
0dbblockgets
4consistentgets
0physicalreads
0redosize
1639bytessentviaSQL*Nettoclient
503bytesreceivedviaSQL*Netfromclient
2SQL*Netroundtripsto/fromclient
0sorts(memory)
0sorts(disk)
10rowsprocessed
SQL>updatettsett=2wheret=1;
已更新10行。
ExecutionPlan
----------------------------------------------------------
0UPDATESTATEMENTOptimizer=CHOOSE
10UPDATEOFTT
21INDEX(RANGESCAN)OFTT_IDX(NON-UNIQUE)
Statistics
----------------------------------------------------------
0recursivecalls
14dbblockgets
1consistentgets
0physicalreads
3216redosize
616bytessentviaSQL*Nettoclient
527bytesreceivedviaSQL*Netfromclient
3SQL*Netroundtripsto/fromclient
2sorts(memory)
0sorts(disk)
10rowsprocessed
SQL>setautotracetraceonly
SQL>updatettsett=3wheretisnull;
6121rowsupdated.
ExecutionPlan
----------------------------------------------------------
0UPDATESTATEMENTOptimizer=CHOOSE
10UPDATEOFTT
21TABLEACCESS(FULL)OFTT
Statistics
----------------------------------------------------------
0recursivecalls
18683dbblockgets
80consistentgets
0physicalreads
2583556redosize
618bytessentviaSQL*Nettoclient
533bytesreceivedviaSQL*Netfromclient
3SQL*Netroundtripsto/fromclient
1sorts(memory)
0sorts(disk)
6121rowsprocessed
SQL>
Archive非常适合存储大量的独立的,作为历史记录的数据。因为它们不经常被读取。Archive拥有高效的插入速度,但其对查询的支持相对较差 现在是在考虑:如果写到服务器端,我一下搞他个10个存储过程导过去,那久之服务器不就成垃圾箱了吗?即便优化了我的中间层. 如果处理少量数据,比如几百条记录的数据,我不知道这两种情况哪个效率更高,如果处理大量数据呢?比如有表中有20万条记录. 如果,某一版本可以提供强大的并发响应,但是没有Oracle的相应版本稳定,或者价格较贵,那么,它就是不适合的。 从底层原理到表层引用,书籍多的很。个人认为没有什么那本书好?这样的说法。主要看和个人的学习方法是否适合。 多加的系统视图和实时系统信息这些东西对DBA挑优非常有帮助,但是感觉粒度还是不太细。 比如日志传送、比如集群。。。
页:
[1]