oracle 如何索引具有空值的日期列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3068730/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to index a date column with null values?
提问by Heinz Z.
How should I index a date column when some rows has null values? We have to select rows between a date range and rows with null dates.
当某些行具有空值时，我应该如何索引日期列？我们必须在日期范围和具有空日期的行之间选择行。
We use Oracle 9.2 and higher.
我们使用 Oracle 9.2 及更高版本。
Options I found
我找到的选项
- Using a bitmap index on the date column
- Using an index on date column and an index on a state field which value is 1 when the date is null
- Using an index on date column and an other granted not null column
- 在日期列上使用位图索引
- 使用日期列上的索引和状态字段上的索引，当日期为空时值为 1
- 在日期列和其他授予的非空列上使用索引
My thoughts to the options are:
我对选项的想法是：
to 1: the table have to many different values to use an bitmap index
to 2: I have to add an field only for this purpose and to change the query when I want to retrieve the null date rows
to 3: locks tricky to add an field to an index which is not really needed
到 1：表必须有许多不同的值才能使用位图索引
到 2：我必须仅为此目的添加一个字段，并在我想检索空日期行时更改查询
3：添加一个锁很棘手字段到不是真正需要的索引
What is the best practice for this case? Thanks in advance
这种情况下的最佳做法是什么？提前致谢
Some infos I have read:
我读过的一些信息：
Oracle Date Index
When does Oracle index null column values?
Edit
编辑
Our table has 300,000 records. 1,000 to 10,000 records are inserted and delete every day. 280,000 records have a null delivered_at date. It is a kind of picking buffer.
我们的表有 300,000 条记录。每天插入和删除 1,000 到 10,000 条记录。280,000 条记录的 delivery_at 日期为空。它是一种拣选缓冲区。
Our structure (translated to english) is:
我们的结构（翻译成英文）是：
create table orders
(
orderid VARCHAR2(6) not null,
customerid VARCHAR2(6) not null,
compartment VARCHAR2(8),
externalstorage NUMBER(1) default 0 not null,
created_at DATE not null,
last_update DATE not null,
latest_delivery DATE not null,
delivered_at DATE,
delivery_group VARCHAR2(9),
fast_order NUMBER(1) default 0 not null,
order_type NUMBER(1) default 0 not null,
produkt_group VARCHAR2(30)
)采纳答案by APC
"Our table has 300,000 records.... 280,000 records have a null delivered_at date. "
“我们的表有 300,000 条记录……280,000 条记录的 Deliver_at 日期为空。”
In other words almost the entire table satisfies a query which searches on where DELIVERED_AT is null. An index is completely inappropriate for that search. A full table scan is much the best approach.
换句话说，几乎整个表都满足一个查询，该查询搜索 DELIVERED_AT 在哪里为空。索引完全不适合该搜索。全表扫描是最好的方法。
If you have an Enterprise Edition license and you have the CPUs to spare, using a parallel query would reduce the elapsed time.
如果您拥有企业版许可证并且有空闲的 CPU，则使用并行查询会减少经过的时间。
回答by Rob van Wijk
In addition to Tony's excellent advice, there is also an option to index your column in such a way that you don't need to adjust your queries. The trick is to add a constant value to just your index.
除了 Tony 的出色建议之外，还有一个选项可以以无需调整查询的方式为您的列编制索引。诀窍是为您的索引添加一个常量值。
A demonstration:
演示：
Create a table with 10,000 rows out of which only 6 contain a NULL value for the a_date column.
创建一个包含 10,000 行的表，其中只有 6 行包含 a_date 列的 NULL 值。
SQL> create table mytable (id,a_date,filler)
2 as
3 select level
4 , case when level < 9995 then date '1999-12-31' + level end
5 , lpad('*',1000,'*')
6 from dual
7 connect by level <= 10000
8 /
Table created.
First I'll show that if you just create an index on the a_date column, the index is not used when you use the predicate "where a_date is null":
首先，我将说明，如果您只是在 a_date 列上创建一个索引，那么当您使用谓词“where a_date is null”时不会使用该索引：
SQL> create index i1 on mytable (a_date)
2 /
Index created.
SQL> exec dbms_stats.gather_table_stats(user,'mytable',cascade=>true)
PL/SQL procedure successfully completed.
SQL> set autotrace on
SQL> select id
2 , a_date
3 from mytable
4 where a_date is null
5 /
ID A_DATE
---------- -------------------
9995
9996
9997
9998
9999
10000
6 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=72 Card=6 Bytes=72)
1 0 TABLE ACCESS (FULL) OF 'MYTABLE' (Cost=72 Card=6 Bytes=72)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
720 consistent gets
0 physical reads
0 redo size
285 bytes sent via SQL*Net to client
234 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed
720 consistent gets and a full table scan.
720 一致获取和全表扫描。
Now change the index to include the constant 1, and repeat the test:
现在更改索引以包含常量 1，并重复测试：
SQL> set autotrace off
SQL> drop index i1
2 /
Index dropped.
SQL> create index i1 on mytable (a_date,1)
2 /
Index created.
SQL> exec dbms_stats.gather_table_stats(user,'mytable',cascade=>true)
PL/SQL procedure successfully completed.
SQL> set autotrace on
SQL> select id
2 , a_date
3 from mytable
4 where a_date is null
5 /
ID A_DATE
---------- -------------------
9995
9996
9997
9998
9999
10000
6 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=6 Bytes=72)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'MYTABLE' (Cost=2 Card=6 Bytes=72)
2 1 INDEX (RANGE SCAN) OF 'I1' (NON-UNIQUE) (Cost=2 Card=6)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
285 bytes sent via SQL*Net to client
234 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed
6 consistent gets and an index range scan.
6 个一致的获取和索引范围扫描。
Regards, Rob.
问候，罗伯。
回答by Tony Andrews
Do you mean that your queries will be like this?
你的意思是你的查询会是这样吗？
select ...
from mytable
where (datecol between :from and :to
or datecol is null);
It would only be worth indexing the nulls if they were relatively few in the table - otherwise a full table scan may be the most efficient way to find them. Assuming it is worth indexing them you could create a function-based index like this:
只有当空值在表中相对较少时才值得索引它们 - 否则全表扫描可能是找到它们的最有效方法。假设值得索引它们，您可以创建一个基于函数的索引，如下所示：
create index mytable_fbi on mytable (case when datecol is null then 1 end);
Then change your query to:
然后将您的查询更改为：
select ...
from mytable
where (datecol between :from and :to
or case when datecol is null then 1 end = 1);
You could wrap the case in a function to make it slicker:
您可以将案例包装在一个函数中以使其更流畅：
create or replace function isnull (p_date date) return varchar2
DETERMINISTIC
is
begin
return case when p_date is null then 'Y' end;
end;
/
create index mytable_fbi on mytable (isnull(datecol));
select ...
from mytable
where (datecol between :from and :to
or isnull(datecol) = 'Y');
I made sure the function returns NULL when the date is not null so that only the null dates are stored in the index. Also I had to declare the function as DETERMINISTIC. (I changed it to return 'Y' instead of 1 merely because to me the name "isnull" suggests it should; feel free to ignore my preference!)
我确保该函数在日期不为空时返回 NULL，以便只有空日期存储在索引中。此外，我必须将该函数声明为 DETERMINISTIC。（我将其更改为返回 'Y' 而不是 1 仅仅是因为对我来说，名称“isnull”表明它应该这样做；请随意忽略我的偏好！）
回答by marcuoli
Avoid the table lookup and create the index like this :
避免表查找并像这样创建索引：
create index i1 on mytable (a_date,id) ;
