注册 登录  
 加关注
查看详情
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

Oracle、MySQL资料及经验

.

 
 
 

日志

 
 

某列的部分记录建索引  

2014-05-12 13:25:27|  分类: sql与pl/sql |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

--以下内容主要来自梁敬彬的sql优化课

drop table t purge;
set autotrace off
create table t (id int ,status varchar2(2));

--建立普通索引
create index id_normal on t(status);
insert into t select rownum ,'Y' from dual connect by rownum<=1000000;
insert into t select 1 ,'N' from dual;
commit;
analyze table t compute statistics for table for all indexes for all indexed columns;
exec dbms_stats.gather_table_stats(user,'t',estimate_percent=>100,no_invalidate=>false,method_opt=>'for all indexed columns size skewonly',cascade=>true)


set linesize 1000
set autotrace traceonly
select * from t where status='N';
执行计划
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |    10 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T         |     1 |    10 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | ID_NORMAL |     1 |       |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
   2 - access("STATUS"='N')
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        483  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
         
--看索引情况
set autotrace off
analyze index id_normal validate structure;
select name,btree_space,lf_rows,height from index_stats;
NAME                           BTREE_SPACE    LF_ROWS     HEIGHT
------------------------------ ----------- ---------- ----------
ID_NORMAL                         22960352    1000001          3

select bytes/1024 K from dba_segments where segment_name='ID_NORMAL';
         K
----------
     23552

--建函数索引
--drop index id_normal;
create index id_status on  t (case when status= 'N' then 'N' end);
analyze table t compute statistics for table for all indexes for all indexed columns;

/*以下这个select * from t where (case when status='N' then 'N' end)='N'
写法不能变,如果是select * from t where status='N'将无效!我见过有些人设置了选择性索引,却这样调用的,结果根本起不到任何效果!*/

set autotrace traceonly
select * from t where (case when status='N' then 'N' end)='N';
执行计划
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |    10 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T         |     1 |    10 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | ID_STATUS |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
   2 - access(CASE "STATUS" WHEN 'N' THEN 'N' END ='N')
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        479  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


--接着观察id_status(即函数索引)索引的情况
set autotrace off
analyze index id_status validate structure;
select name,btree_space,lf_rows,height from index_stats;
NAME                           BTREE_SPACE    LF_ROWS     HEIGHT
------------------------------ ----------- ---------- ----------
ID_STATUS                          8000          1          1

select bytes/1024 K from dba_segments where segment_name='ID_STATUS';
         K
----------
        64

  评论这张
 
阅读(59)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018