Oracle 對(duì)某列的部分?jǐn)?shù)據(jù)創(chuàng)建索引

成都創(chuàng)新互聯(lián)從2013年成立，先為保定等服務(wù)建站，保定等地企業(yè)，進(jìn)行企業(yè)商務(wù)咨詢服務(wù)。為保定企業(yè)網(wǎng)站制作PC+手機(jī)+微官網(wǎng)三網(wǎng)同步一站式服務(wù)解決您的所有建站問題。

--- 說明：案例來自《 收獲，不止SQL 優(yōu)化 》

請(qǐng)問：Oracle 可以針對(duì)某列的部分?jǐn)?shù)據(jù)創(chuàng)建索引嗎？比如 t1 表 id 列的數(shù)據(jù)有 1,2,3,4,5 ?？梢灾会槍?duì) id=3 的數(shù)據(jù)創(chuàng)建索引嗎？

可以通過函數(shù)索引實(shí)現(xiàn)只針對(duì)id=3 的數(shù)據(jù)創(chuàng)建索引，例如 :

Create index i_t1_id on t(case when id=3 then 3 end);

請(qǐng)問：請(qǐng)舉一個(gè)具體案例，并說明在該場(chǎng)景下使用部分索引性能更好？

案例如下：

---創(chuàng)建測(cè)試表t

SQL >   create   table  t ( id   int   , status varchar2 ( 2 ));

--建立普通索引

SQL >   create   index  id_normal on  t ( status );

-- 插入數(shù)據(jù)

SQL >   insert   into  t select   rownum   , 'Y'   from  dual connect   by   rownum <= 100000 ;

SQL >   insert   into  t select   1   , 'N'   from  dual ;

SQL >   commit ;

---數(shù)據(jù)分布

SQL >   select   count (*), status from  t group   by  status ;

--- 收集統(tǒng)計(jì)信息

SQL >   analyze   table  t compute   statistics   for   table   for   all   indexes   for   all   indexed   columns ;

--- 查詢表 t ，查看執(zhí)行計(jì)劃

SQL >   set  linesize 1000

SQL >   set  autotrace traceonly

SQL >   select   *   from  t where  status = 'N' ;

-- 查 看索引 信息

SQL >   set  autotrace off

SQL >   analyze   index  id_normal validate   structure ;

SQL >   select   name , btree_space , lf_rows , height from  index_stats ;

備注 ： INDEX_STATS  存儲(chǔ)的是最近一次   ANALYZE INDEX ... VALIDATE STRUCTURE  語句的結(jié)果，最多只有 當(dāng)前會(huì)話的 一條數(shù)據(jù) 。

--- 創(chuàng)建函數(shù)索引的情況

SQL >   drop   index  id_normal ;

SQL >   create   index  id_status on   t ( Case   when  status =   'N'   then   'N'   end );

SQL >   analyze   table  t compute   statistics   for   table   for   all   indexes   for   all   indexed   columns ;

--- 再次查看執(zhí)行計(jì)劃

SQL >   set  autotrace traceonly

SQL >   select   *   from  t where   ( case   when  status = 'N'   then   'N'   end )= 'N' ;

--觀察id_status索引的情況

SQL >   set  autotrace off

SQL >   analyze   index  id_status validate   structure ;

SQL >   select   name , btree_space , lf_rows , height from  index_stats ;

--- 在對(duì)比下之前普通索引的值

結(jié)論： 普通索引改成函數(shù)索引后，索引當(dāng)前分配的空間 (BTREE_SPACE) 由 20230168 降到 7996 ， , 邏輯讀consistent gets 由 5 降到 2 ，索引葉子數(shù) (LF_ROWS) 由 100001 降到 1 ，索引高度 (HEIGHT) 由 3 降到 1 ，性能有所提升。

歡迎關(guān)注我的微信公眾號(hào)"IT小Chen"，共同學(xué)習(xí)，共同成長(zhǎng)！??！

文章題目：Oracle對(duì)某列的部分?jǐn)?shù)據(jù)創(chuàng)建索引-創(chuàng)新互聯(lián)
本文鏈接：http://weahome.cn/article/cdjhcd.html