Indexing NULL table column values for fast SQL performance
来源:互联网 发布:开源软件社区 编辑:程序博客网 时间:2024/06/06 00:44
Indexing NULL table column values for fast SQL performance
Oracle Tips by Burleson Consulting
September 21, 2003 - Revised January 1, 2007
One problem with allrelational databases is having the optional ability to index on a NULLcolumn. By default, relational databases ignore NULL values (because therelational model says that NULL means "not present"). Hence,Oracle indexes will not include NULL values.
For example, thisindex definition would not index on "open positions", new employeepositions that are stored with a NULL employee name:
create index
emp_ename_idx
on
emp
(ename)
;
Whenever a SQL queryasks for the open position employee slots "where ename is NULL",there will be no index entries for NULLS in emp_name_idx and Oraclewould perform an unnecessary large-table full-table scan.
Execution Plan
---------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=6)
1 0 TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=1 Bytes=6)
To get around theoptimization of SQL queries that choose NULL column values, we can create afunction-based index using the null value built-in SQL function to index onlyon the NULL columns.
Note that the"null value" (NVL) function replaces NULL values with the characterstring "null', a real value that can participate in an index:
-- create an FBI on ename column with NULL values
create index
emp_null_ename_idx
on
emp
(nvl(ename,'null'));
analyze index emp_null_ename_idx compute statistics;
You canalso do this techniques with NULL numeric values. This syntax replacesNULL values with a zero:
-- create an FBI on emp_nbr column with NULL values
create index
emp_null_emp_nbr_idx
on
emp
(nvl(ename,o));
analyze index emp_null_ename_idx compute statistics;
Now we can use theindex and greatly improve the speed of any queries that require access to theNULL columns. Note that we must make one of two changes:
1-Add a hint to force the index
2 -Change the WHERE predicate to match the function
Here is an example ofusing an index on NULL column values:
-- insert a NULL row
insert into emp (empno) values (999);
set autotrace traceonly explain;
-- test the index access (change predicate to use FBI)
select /*+ index(emp_null_ename_idx) */
ename
from
emp e
where
nvl(ename,'null') = 'null'
;
Reader Comments:
I wasreading "Indexing NULL table column values for fast SQL performance"article, where you have mentioned that if the column is having null values (ofvery less amount) and you want to select where column is null then to use theindex, create function based index and changed your query to use that index.
I think,it can be done without changing query as well......
SQL> select count(1) from t where n is null;
COUNT(1)
----------
334
Execution Plan
---------------------------------------------------------
0 SELECT STATEMENTOptimizer=CHOOSE (Cost=3 Card=1 Bytes=3)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL)OF 'T' (Cost=3 Card=334 Bytes=1002)
SQL> create index tind on t(n, 1); ----> here 1 isjust any arbitary value.
Index created.
SQL> execdbms_stats.gather_table_stats(user,'t',cascade=>true);
PL/SQL procedure successfully completed.
SQL> select count(1) from t where n is null;
COUNT(1)
----------
334
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENTOptimizer=CHOOSE (Cost=2 Card=1 Bytes=4)
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN)OF 'TIND' (NON-UNIQUE) (Cost=2 Card=3
34 Bytes=1336)
SandeepRedkar
- Indexing NULL table column values for fast SQL performance
- Indexing NULL table column values for fast SQL performance
- Performance Considerations for Elasticsearch Indexing
- Optimizing table views for performance
- Indexing Performance Tipse
- java.sql.SQLSyntaxErrorException: ORA-01747: user.table.column, table.column
- SQL NOT IN constraint and NULL values
- Ubuntu using Ramdisk for better performance and fast response
- cannot change NEW values for this column type in trigger
- Fast Indexing: Support for Size-Changing Algorithms in Stackable File Systems
- selectOneChoice table filter for LOV column
- ABAP Internal Table Performance for STANDARD, SORTED and HASHED Table
- SQL Server 索引基础知识(7)----Indexing for AND
- SQL Server 索引基础知识(9)----Indexing for OR
- SQL Server 索引基础知识(7)----Indexing for AND
- SQL Server 索引基础知识(9)----Indexing for OR
- SQL Server 索引基础知识(7)----Indexing for AND
- SQL Server 索引基础知识(9)----Indexing for OR
- poj 3982
- BD2 SQL错误代码释义
- HP-UX系统日志文件
- js获取String的实际长度
- MyEclipse优化
- Indexing NULL table column values for fast SQL performance
- 实现选择全部单选框的实例
- App数据格式之解析Json
- 面向接口设计
- OAUTH协议
- 【转载】最短路径之SPFA算法
- 数据结构之线性结构(顺序表和链表的比较)【五】
- 没有正确安装gnome电源管理的默认配置
- HTTP协议header头域