NULL 与oracle索引

[AdrianHu99]

https://blog.csdn.net/leshami/article/details/7437561

[AdrianHu99]

    无论是单列唯一索引或复合唯一索引，对于可以为null的列或复合null值，Oracle不会为其存储索引值。     故在基于单列创建B树唯一索引或多列创建B树复合唯一索引的情形下，     当列上允许为null值时         where子句使用了基于is null的情形，其执行计划走全表扫描。         where子句使用了基于is not null的情形，其执行计划走索引扫描(索引范围扫描或索引全扫描)。     当列上不允许为null值时，存在非null约束         where子句使用了基于is null的情行，其执行计划走索引扫描。         where子句使用了基于is not null的情形，其执行计划也是走索引扫描。     注：此在Oracle 10g R2(linux)下的情形，不同的优化器版本可能会有偏差。

---

作者：Leshami 来源：CSDN 原文：https://blog.csdn.net/leshami/article/details/7437561 版权声明：本文为博主原创文章，转载请附上博文链接！

[AdrianHu99]

select index_name,index_type,blevel,leaf_blocks,num_rows,status,distinct_keys 2 from user_indexes where table_name='T1';

[AdrianHu99]

scott@ORCL> set autot trace exp; scott@ORCL> select * from t1 where id is null;

[AdrianHu99]

[AdrianHu99]

a.使用临时表重写复杂的子查询 Oracle 使用全局临时表以及WITH操作符去解决那些复杂的SQL子查询。尤其是那些where子句中的子查询，SELECT 字句标量子查询， FROM 子句的内联视图。使用临时表实现SQL tuning(以及使用WITH的物化视图)能够使得性能得以惊人的提升。

b.使用MINUS 代替EXIST子查询
    使用MINUS操作代替NOT IN 或NOT EXISTS将产生更高效的执行计划(译者按：此需要测试)。

c.使用SQL分析函数
    Oracle 分析函数能够一次提取数据来做多维聚合运算(象ROLLUP,CUBE)以提高性能。

d.重写NOT EXISTS和查询作为外部连接NOT EXISTS 子查询
    在一些案例中的NOT 查询(where 中一个列被定义为NULL值)，能够将其改写这个非相关子查询到IS NULL 的外部链接。如下例：
    select book_key from book
    where
    book_key NOT IN (select book_key from sales);

    下面我们在where子句中使用了外部连接来替代原来的not exits，得到一个更高效的执行计划。

    select b.book_key from book b, sales s
    where
       b.book_key = s.book_key(+)
    and
       s.book_key IS NULL;

e.索引NULL值列
    如果你的SQL语句频繁使用到NULL值，应当考虑基于NULL值创建索引。为使该查询最优化，可以创建一个使用基于NULL值索引函数。
    (译者按，如 create index i_tb_col on tab(nvl(col,null)); create index i_tb_col on tab(col,0);)

f.避免基于索引的运算
    不要基于索引列做任何运算，除非你创建了一个相应的索引函数。或者重设设计列以使得where子句列上的谓词不需要转换。
    -->下面都是低效的SQL写法
    where salary*5            > :myvalue   
    where substr(ssn,7,4)     = "1234"
    where to_char(mydate,mon) = "january"

g.避免使用NOT IN 和HAVING 
    在合适的时候使用not exists子查询更高效。

h.避免使用LIKE谓词
    在合适地时候，如果能够使用 = 运算应尽可能避免LIKE操作。

i.避免数据类型转换
    如果一个where 子句列是数字型，则不要使用引号。而对一个字符索引列，总是使用引号。下面是数据类型混用的情形。
    where cust_nbr = "123"
    where substr(ssn,7,4) = 1234

j.使用decode与case
    使用decode 与case 函数能够最小化查询表的次数。

k.不要害怕全表扫描
    并不是所有的OLTP系统在使用索引时是最优化的。如果你的查询返回了表中的绝大部分数据，则全表扫描性能优于索引扫描。这取决于
    一些因素包括你的配置(db_file_multiblock_read_count, db_block_size)，并行查询，以及表块和索引块在buffer cache中的数量。

[AdrianHu99]

scott@CNMMBO> set autotrace traceonly stat;
scott@CNMMBO> SELECT /+ rule / COUNT( * ) FROM emp, dept;

[AdrianHu99]

https://blog.csdn.net/leshami/article/details/7406672 https://blog.csdn.net/leshami/article/details/7422254 https://blog.csdn.net/leshami/article/details/7438397