MySql有一个很有意思的索引类型，叫做前缀索引，它可以给某个文本字段的前面部分单独做索引，从而降低索引的大小。
其实，Oracle也有类似的实现，对于文本，它可以通过substr的函数索引，实现同样甚至更多的功能。另外，经过探索，我们发现，原来数字和时间字段，在Oracle也可以实现类似的功能。
MySQL的前缀索引
MySQL的前缀索引指的是对指定的栏位的前面几位建立的索引。
或者
建立前缀索引后，可以直接当做普通索引进行过滤。
前缀索引的***的好处是降低索引的大小。另外，由于InnoDB单列索引长度不能超过767bytes，如果是text或者blob字段，直接建立索引可能会报错，而前缀索引可以绕过这一限制。
做个测试看一下。
首先，创建一个生成超过1000长度的随机字符串的函数。
创建测试表
插入10000行记录：
尝试在类型为varchar(1000)的LONG_STR创建索引
成功了，但是Sub_part显示为767,表示系统自动创建了前缀长度为767的前缀索引；
看看大小: 8992k
尝试在TEXT和BLOB的栏位上直接创建索引
在TEXT和BLOB栏位上建立索引，必须指定前缀长度。
看看大小，528k(9520-8992), 远远小于LONG_STR的8992k.
看看大小，544k(10064-9520)。
看看几个表的前缀长度和大小。前缀长度显著降低了索引的大小。
看看查询是否能正常进行:
可以使用上索引。
前缀索引长度的选择
对于一个可能挺长的栏位，怎么判断合适的前缀索引呢？
简单做法:
炫一点的写法，通过一些小技巧，可以在同一个SQL里遍历多个值，同时查看多个值的选择度。
对于这个表，由于数据是随机的，所以，前5位已经足够好。
我们创建一个前缀长度为5的前缀索引。
看看大小，仅仅258k(10320-10064),远低于最早创建的8992k。
测试一下性能，有前缀索引时：
删除索引后，性能差距很明显：
Oracle的类似实现
从前面的做法中，我们可以发现，前缀索引本质上就是把栏位的前N位作为索引，这个看起来，很像Oracle的函数索引。类似于:
对于Oracle的函数索引，我们一个比较深的印象就是，where条件必须和函数索引里的表达式一致，才能利用上函数索引。但既然MySQL可以用前缀索引，作为老前辈的Oracle, 似乎应该也能实现才对。
我们来看看，在Oracle里面，是否能够实现同样的功能。
创建表格：
创建substr的函数索引：
看看执行计划：
神奇的事情发生了，的确走了索引，Oracle也支持前缀索引~~
我们可以看到，找谓词中，增加了一个原来语句中没有的东西:
换成绑定变量看看：
可以看到，谓词中变成了：
为什么多了这个东西？因为，从逻辑上来说：
和
是***等价的。Oracle相当于自动做了语义上的优化。
有兴趣的，可以做个10053。Oracle内部实际进行执行计划解析的，就是这样一个SQL。
看看如果创建普通索引，空间占用是多少。
大小分别是7M和256K.
但Oracle仅止于此吗？我们在来试试看另一个SQL, 这次，我们在条件上也使用substr,但是长度不为5。
果然还是可以的。因为逻辑上来说
在N>=5的时候，
还是等价的。所以优化器还是可以继续加一个谓词。
当然，如果把WHERE条件中substr换成小于5的值,就不再能用得上索引。因为无法直接换为等价的、又带有substr(object_name,1,5)的语句。
Oracle时间、数字上的前缀索引
仅仅就这样吗？除了字符类型之外，数字类型和时间类型是否也支持？
我们再看看。
在刚才的表的基础上，创建时间类型上的trunc函数索引。
看看执行计划:
没问题，还是可以的。
创建数字类型上的trunc函数索引:
实际上，问题的关键在于等价与优化器的内部改写。
我们***再看另一个例子。
建另一个表，相当栏位长度最长为5。
来看看这个语句的执行效果
神奇的事情再次发生，autotrace中db block gets/consistent gets都为0，这代表数据库根本就没去访问表。
原因很简单，‘DBA_TABLES’这个值长度大于5, 超出了表定义中的varchar2(5)了。object_name = ‘DBA_TABLES’就等价于恒否的条件了。这个，在10053里也找不到，但的确存在。
求一sql批量插入数据库的语句
declare @回i intset @i=0while(@i<1000)begin答insert into a(url) values(XXXXXX_+cast(@i as varchar))set @i=@i+1end
SQL中怎么删除表的前10行或后10行
后10行delete from 表明where id in(select top 10 id from 表明 order by id desc)前10行delete from 表明where id in(select top 10 id from 表明 order by id)
oracle的SQL索引使用
1，第一次查询慢，以后就快了，主要是因为第一次要进行磁盘操作，以后数据被cache到内存中了，不在操作磁盘，所以就快了。 2，对于你说的这四种查询，where条件中的a=a估计你是举例子这样写的吧。 实际上应该是a=变量A。 其他的b，c，d也是这样。 那么这种语句都是可以利用你说的复合索引的。 如果是RBO优化器，这四句都应该用索引。 但是oracle现在推荐的CBO优化器不能保证你都走索引。 3，到底用没用索引，你可以从v$sqlaera中找到你的语句对应的hash_value，然后从v$sql_plan中找到语句的执行计划，通过执行计划确认你的语句是不是使用了索引。 具体语句你可以类似如下写法：select hash_value,sql_text from v$sqlarea where upper(sql_text) like %你需要查找的sql语句的特征片段%select * from v$sql_plan where hash_value = 上一句查到的hash_value
发表评论