精华内容
下载资源
问答
  • 函数索引使用

    2008-04-06 21:25:18
    函数索引,函数索引使用
  • 基于函数索引 ...3如果查询条件包含与索引相同的函数,就可以使用基于函数索引,提高查询速度 举例测试表 create table student2( sno varchar2(10) primary key, sname varchar2(20), sage number

    基于函数的索引
    1基于函数的索引是常规的B树索引。
    2该索引存放的数据是由表中的数据应用函数后得到的,而不是直接存放表中的数据本身。
    3如果查询条件包含与索引相同的函数,就可以使用基于函数的索引,提高查询速度
    举例

    测试表
     create  table student2(
    sno varchar2(10) primary key,
    sname varchar2(20),
    sage number(2),
    cno  varchar2(2)
    );
    
    insert into zhou.student2 values('1','LILIA',18,'1');
    insert into zhou.student2 values('2','LILIB',18,'1');
    
    
    这个时候select sname  from student2 where sname='lilia';
    是查不来数据的,要想忽略大小写,那么可以这样写
    select sname  from student2 where upper(sname)=upper('lilia');
    但是这个会引发一个问题?
    这样实现的话,某一列的普通B树索引还有用吗?没有用
    用户使用这种查询方式,用户不是基于表中的存储记录进行搜索的,即使在某一列上建立了索引,但是oracle会被破执行全表搜索,为遇到的每一行都计算upper()函数。
    如果解决?使用基于函数的索引

    创建基于函数的索引
    1可以提高在查询条件中使用函数和表达式时查找的执行速度
    2如果用户在自己模式中创建基于函数的索引,那么必须具有query rewrite系统权限;
    如果用户想要在其他模式中创建基于函数的索引,需要create any index和global query rewrite权限
    3创建基于函数的索引时,oracle会首先对包含索引列的函数值或者表达式值进行求值,然后对求职后的结果进行排序,最后存储到索引中。

    举例

    create index sname_index on zhou.student2(upper(sname))
    tablespace MYSPACE;
    这里写图片描述

    查询条件包含与索引相同的函数
    select sname from zhou.student2 where upper(sname)=upper(‘lilia’);
    这里写图片描述

    select ….upper(sname):对该列使用的函数与基于函数的索引使用的函数一样,查询的时候就使用基于函数的索引,不全表查询.

    展开全文
  • 使用索引的误区之三:基于函数索引使用基于函数的索引(BFI, Based Function Index):从Oracle 8i开始,可以使用基于函数的索引来提高查询性能, 使用基于函数的索引,需要几个条件:1, 用户需要有create ...

    使用索引的误区之三:基于函数的索引

    使用基于函数的索引(BFI, Based Function Index):

    Oracle 8i开始,可以使用基于函数的索引来提高查询性能,

     

    使用基于函数的索引,需要几个条件:

    1,  用户需要有create index或者create any index权限

    2,  用户需要有query rewrite或者global query rewirte权限

    3,  设置系统参数 query_rewrite_enabled=TRUE

    query_rewrite_integrity=enforced

    4,  设置系统参数 :COMPATIBLE= 8.1.0 .0.0 或者更高

    5,创建了BFI后,需要对表进行分析

     

    请看下面的例子:

    首先,在没有建立函数索引的情况下,我们看到查询没有如我们想想一样使用单列(dname)索引:

    SQL> set autotrace traceonly

    SQL> select * from dept where substr(dname,1,5)='aaa';

     

    未选定行

     

    已用时间:  00: 00: 00.00

     

    Execution Plan

    ----------------------------------------------------------

       0      SELECT STATEMENT Optimizer=CHOOSE

       1    0   TABLE ACCESS (FULL) OF 'DEPT'

     

     

     

     

    Statistics

    ----------------------------------------------------------

            134  recursive calls

              0  db block gets

             20  consistent gets

              0  physical reads

              0  redo size

            323  bytes sent via SQL*Net to client

            372  bytes received via SQL*Net from client

              1  SQL*Net roundtrips to/from client

              2  sorts (memory)

              0  sorts (disk)

              0  rows processed

     

     

     

    下面直接建立基于函数的索引,看看是否查询是否可以使用我们建立的索引

    SQL> create index dept_id5 on dept(substr(dname,1,5));

    create index dept_id5 on dept(substr(dname,1,5))

                                                 *

    ERROR 位于第 1 :

    ORA-01031: 权限不足

     

     

    已用时间:  00: 00: 00.00

    SQL> set autotrace off

    SQL> col username format a10

    SQL> col privilege format a20

    SQL> select username,privilege from user_sys_privs;

     

    USERNAME   PRIVILEGE

    ---------- --------------------

    DEMO       UNLIMITED TABLESPACE

    PUBLIC     SELECT ANY TABLE

     

    已用时间:  00: 00: 00.00

    SQL> select username, granted_role from user_role_privs;

     

    USERNAME   GRANTED_ROLE

    ---------- ------------------------------

    DEMO       CONNECT

    DEMO       RESOURCE

    PUBLIC     PLUSTRACE

     

    已用时间:  00: 00: 00.01

     

    我们看到,虽然用户有connectresource角色,但是仍然没有建立函数索引的权限。

     

    我们使用sysdba身份登陆,给demo用户授create any index global query rewrite权限:

    SQL> conn lunar/lunar@test1 as sysdba

    已连接。

    SQL> grant create any index to demo;

     

    授权成功。

     

    已用时间:  00: 00: 00.00

    SQL> grant global query rewrite to demo;

     

    授权成功。

     

    已用时间:  00: 00: 00.00

    SQL> conn demo/demo@test1

    已连接。

    SQL> select username,privilege from user_sys_privs;

     

    USERNAME   PRIVILEGE

    ---------- --------------------

    DEMO       CREATE ANY INDEX

    DEMO       GLOBAL QUERY REWRITE

    DEMO       UNLIMITED TABLESPACE

    PUBLIC     SELECT ANY TABLE

     

    已用时间:  00: 00: 00.00

    SQL> select username, granted_role from user_role_privs;

     

    USERNAME   GRANTED_ROLE

    ---------- ------------------------------

    DEMO       CONNECT

    DEMO       RESOURCE

    PUBLIC     PLUSTRACE

     

    已用时间:  00: 00: 00.00

     

    再修改系统参数,将query_rewrite_enabled设置为true,这个参数是动态参数,设置后可以有立杆见影的效果:

    SQL> conn /@test1 as sysdba

    已连接。

    SQL> show parameter query

     

    NAME                                 TYPE        VALUE

    ------------------------------------ ----------- ------------------------------

    query_rewrite_enabled                string      FALSE

    query_rewrite_integrity              string      enforced

    SQL> alter system set query_rewrite_enabled=true;

     

    系统已更改。

     

    已用时间:  00: 00: 00.00

    SQL> show parameter query

     

    NAME                                 TYPE        VALUE

    ------------------------------------ ----------- ------------------------------

    query_rewrite_enabled                string      TRUE

    query_rewrite_integrity              string      enforced

     

    好了,再使用demo用户登陆,创建函数索引

    SQL> conn demo/demo@test1

    已连接。

    SQL>  create index dept_id5 on dept(substr(dname,1,5));

     

    索引已创建。

     

    已用时间:  00: 00: 00.00

    SQL> select index_type,index_name from user_indexes where table_name='DEPT';

     

    INDEX_TYPE                  INDEX_NAME

    --------------------------- ------------------------------

    FUNCTION-BASED NORMAL        DEPT_ID5

     

    已用时间:  00: 00: 00.00

     

    可见已经创建成功了。

     

    下面,我们看看查询是否会使用我们创建的函数索引:

    SQL> set autotrace traceonly

    SQL> select * from dept where substr(dname,1,5)='aaa';

     

    未选定行

     

    已用时间:  00: 00: 00.00

     

    Execution Plan

    ----------------------------------------------------------

       0      SELECT STATEMENT Optimizer=CHOOSE

       1    0   TABLE ACCESS (FULL) OF 'DEPT'

     

     

     

     

    Statistics

    ----------------------------------------------------------

             29  recursive calls

              0  db block gets

             10  consistent gets

              0  physical reads

              0  redo size

            323  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> analyze table dept compute statistics

      2  for table

      3  for all indexes

      4  for all indexed columns;

     

    表已分析。

     

    已用时间:  00: 00: 00.02

    SQL> select * from dept where substr(dname,1,5)='aaa';

     

    未选定行

     

    已用时间:  00: 00: 00.02

     

    Execution Plan

    ----------------------------------------------------------

       0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=23)

       1    0   TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (Cost=2 Card=1 Byt

              es=23)

     

       2    1     INDEX (RANGE SCAN) OF 'DEPT_ID5' (NON-UNIQUE) (Cost=1 Ca

              rd=1)

     

     

     

     

     

    Statistics

    ----------------------------------------------------------

              0  recursive calls

              0  db block gets

              1  consistent gets

              0  physical reads

              0  redo size

            323  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>

     

    通过所有的statistics,我们可以清楚的看到,适当的使用索引会是性能提高几倍甚至更多。

    展开全文
  • 索引分类】基于函数索引

    千次阅读 2013-04-11 14:53:59
    如果没有基于函数索引,任何在列上执行了函数的查询都不能使用这个列的索引。例如,下面的查询就不能使用JOB列上的索引,除非它是基于函数索引: select *from emp whereUPPER(job) = 'MGR'; 下面的查询使用...

    基于函数的索引

    可以在表中创建基于函数的索引。如果没有基于函数的索引,任何在列上执行了函数的查询都不能使用这个列的索引。例如,下面的查询就不能使用JOB列上的索引,除非它是基于函数的索引:

    select *from emp whereUPPER(job) = 'MGR';

    下面的查询使用JOB列上的索引,但是它将不会返回JOB列具有Mgr或mgr值的行:

    select *from emp wherejob = 'MGR';

    可以创建这样的索引,允许索引访问支持基于函数的列或数据。可以对列表达式UPPER(job)创建索引,而不是直接在JOB列上建立索引,如:

    create indexEMP$UPPER_JOB on emp(UPPER(job));

     

    尽管基于函数的索引非常有用,但在建立它们之前必须先考虑下面一些问题:

    能限制在这个列上使用的函数吗?如果能,能限制所有在这个列上执行的所有函数吗?

    是否有足够应付额外索引的存储空间?

    在每列上增加的索引数量会对针对该表执行的DML语句的性能带来何种影响?

     

    基于函数的索引非常有用,但在实现时必须小心。在表上创建的索引越多,INSERTUPDATEDELETE语句的执行就会花费越多的时间。

    注意:对于优化器所使用的基于函数的索引来说,必须把初始参数QUERY _REWRITE _ ENABLED设定为TRUE

    示例:

    select count(*) from sample where ratio(balance,limit)>.5;

    Elapsed time: 20.1 minutes

    create index ratio_idx1 on sample (ratio(balance,limit));

    select  count(*)from  sample where ratio(balance,limit) >.5;

    Elapsed time: 7 seconds!!!

    展开全文
  • 因为都要做模糊查询,like不可以走索引,导致查询效率实在底下,想看看instr这个函数能 不能走索引,或者建立函数索引,我建立成功了。是instr(name,'xx')。但是好像没效果,做模糊查询的话,怎么样效率高一点
  • 视频地址: python之使用enumerate函数获取索引和元素 更多精彩内容,敬请扫码关注(HelloWorld小码农):  

    视频地址:

    python之使用enumerate函数获取索引和元素

    更多精彩内容,敬请扫码关注(HelloWorld小码农):

     

    展开全文
  • 函数使得索引列失效

    万次阅读 2011-10-08 11:48:25
    索引列上使用函数使得索引失效的是常见的索引失效原因之一,因此尽可能的避免在索引列上使用函数。尽管可以使用基于函数索引来 解决索引失效的问题,但如此一来带来的比如磁盘空间的占用以及列上过多的索引导致...
  • VB 指令、函数 索引表.VB 指令、函数 索引表.VB 指令、函数 索引表.VB 指令、函数 索引表.VB 指令、函数 索引表.
  • 在某些场景下查询的时候要根据字段的某一部分做查询或者经过某种计算之后做查询,如果使用函数或者其他方式作用在字段上之后,就会限制到索引的使用,不过我们可以间接地实现类似于函数索引的功能。 另外一个就是...
  • 使用Oracle函数索引 提高查询效率 2010-10-25 10:55 佚名 互联网 我要评论(0) 字号:T | T 想要提高Oracle数据库的查询检索的效率,使用函数索引无疑是一个非常好的方法。下文对Oracle函数索引的...
  • Oracle基于函数索引

    千次阅读 2017-02-27 23:54:08
    比如执行如下一条SQL语句:select * from emp where upper(ename) = ‘KING’,...如果我们建立一个基于函数索引,比如:create index emp_upper_idx on emp(upper(ename)); 这个时候,我们只需要按区间扫描小部分数
  • oracle 函数索引

    万次阅读 2018-09-03 16:41:31
    oracle中创建函数索引即是 你用到了什么函数就建什么函数索引,比如substr select * from table where 1=1 and substr(field,0,2) in ('01') 创建索引的语句就是 create index indexname on table(substr(fileld...
  • OpenCV 3.0函数索引

    2016-07-26 14:44:51
    OpenCV 3.0函数索引
  • 函数索引

    千次阅读 2009-06-08 12:19:00
    函数索引的适用场合 函数索引用适用于这样的情况:被索引的列或列集合包含在某个表达式或是函数中(后面简称表达式),而这个表达式又出现在WHERE子句或ORDER BY子句中。 假设表some_table包含a,b,c三个字段,...
  • MySQL 8.0 新特性之函数索引

    千次阅读 2018-12-19 11:25:16
    MySQL 8.0.13 以及更高版本支持函数索引(functional key parts),也就是将表达式的值作为索引的内容,而不是列值或列值前缀。 将函数作为索引键可以用于索引那些没有在表中直接存储的内容。
  • mysql建立函数索引

    万次阅读 2018-06-02 17:12:57
    mysql版本需要是5.7及以上版本才支持建立函数索引建立函数索引需要两步1.创建虚拟列alter table t_log add column create_time_index datetime GENERATED ALWAYS AS (date_format(create_time,'%Y-%m-%d'));上面这条...
  • 今天在做SQL Tuning的时候遇到一个典型的example,立个Flag,梳理知识点,在这里也做个分享,就是我们在写SQL的时候如果对索引字段使用函数炒作,则导致该SQL不走索引扫描查询,导致SQL性能下降。 SQL如下: SELECT ...
  • 一、物化视图 ...物化视图有很多方面和索引很相似:使用物化视图的目的是为了提高查询性能;物化视图对应用透明,增加和删除物化视图不会影响应用程序中SQL语句的正确性和有效性;物化视图需要占用存储
  • oracle函数索引

    2011-10-26 14:22:22
    如果一个函数(function)或表达式...函数索引能够计算出函数或表达式的值, 并将其保存在索引中。用户创建的函数索引既可以是平衡树类型(B-tree index)的,也可以是位图类型(bitmap index)的。 用于创
  • 对列/行索引重新指定索引(删除/增加:行/列):reindex函数 reindex的method选项: #重新指定索引及NaN填充值 x= Series([4,7,5],index= ['a','b','c']) y= x.reindex(['a','b','c','d']) print(y) """ a 4.0 b ...
  • Oracle中普通索引和函数索引

    千次阅读 2019-08-11 20:07:32
    1. 创建普通索引的语法: create index <index_name> on namespace.<table_name>(<column_name>) 例如: create index example_index_name on crm.user(name) 说明: <index_name> 创建的...
  • 1.出现的问题如下: ...2.explain查看是否使用索引? 原SQL语句: ( 只贴出部分语句 ) explain select id from user where DATE_FORMAT(createDate,'%Y-%m-%d') >= '2020-01-12' 执行分析如下:...
  • Postgresql中创建函数索引

    千次阅读 2018-05-30 14:34:26
    1、创建函数索引的语法,如下表示为表的指定字段的upper函数创建了一个函数索引 CREATE INDEX 索引名 ON 表名 (upper(字段名)); 2、使用场景: 如果某张表的数据量很大,而其中某个字段在当前数据库中保存大小写...
  • 函数索引跟普通索引谁的效率高

    千次阅读 2018-02-08 21:11:07
    Substr和 'xxx%'谁的查询效率好 函数索引与普通索引谁的效率高
  • 函数索引的原理及好处

    千次阅读 2013-12-30 10:29:14
    函数索引:就是把列值通过函数计算的结果放入索引。    好处一:可以把一些数值排除在索引外面,即部分行建立索引     好处二:减少递归调用的次数。例如function(column1) 如果表中有1000行,即便column1列...
  • 使用Oracle的instr函数索引配合提高模糊查询的效率 一般来说,在Oracle数据库中,我们对tb表的name字段进行模糊查询会采用下面两种方式: 1.select * from tb where name like '%XX%'; 2.select * from tb where ...
  • mysql不提供函数索引的创建,mysql5.7版本之后提供Generated Column类似的功能,如: alter table RP_RONGHE_PROXY_OPR_MER_DETAIL add column substr_through_date varchar(8) generated always as (SUBSTR(THROUGH_...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 913,140
精华内容 365,256
关键字:

函数如何使用索引