SQL 如何在 Oracle 10+ 中对包含 NULL 的列使用基于函数的索引？

How to use a function-based index on a column that contains NULLs in Oracle 10+?

提问by cletus

Lets just say you have a table in Oracle:

假设您在 Oracle 中有一个表：

CREATE TABLE person (
  id NUMBER PRIMARY KEY,
  given_names VARCHAR2(50),
  surname VARCHAR2(50)
);

with these function-based indices:

使用这些基于函数的索引：

CREATE INDEX idx_person_upper_given_names ON person (UPPER(given_names));
CREATE INDEX idx_person_upper_last_name ON person (UPPER(last_name));

Now, given_names has no NULL values but for argument's sake last_name does. If I do this:

现在， given_names 没有 NULL 值，但为了论证， last_name 有。如果我这样做：

SELECT * FROM person WHERE UPPER(given_names) LIKE 'P%'

the explain plan tells me its using the index but change it to:

解释计划告诉我它使用索引但将其更改为：

SELECT * FROM person WHERE UPPER(last_name) LIKE 'P%'

it doesn't. The Oracle docs say that to use the function-based index will only be used when several conditions are met, one of which is ensuring there are no NULL values since they aren't indexed.

它没有。Oracle 文档说，只有在满足多个条件时才会使用基于函数的索引，其中之一是确保没有 NULL 值，因为它们没有被索引。

I've tried these queries:

我试过这些查询：

SELECT * FROM person WHERE UPPER(last_name) LIKE 'P%' AND UPPER(last_name) IS NOT NULL

and

和

SELECT * FROM person WHERE UPPER(last_name) LIKE 'P%' AND last_name IS NOT NULL

In the latter case I even added an index on last_name but no matter what I try it uses a full table scan. Assuming I can't get rid of the NULL values, how do I get this query to use the index on UPPER(last_name)?

在后一种情况下，我什至在 last_name 上添加了一个索引，但无论我尝试什么，它都使用全表扫描。假设我无法摆脱 NULL 值，如何让这个查询使用 UPPER(last_name) 上的索引？

回答by Tony Andrews

The index can be used, though the optimiser may have chosen not to use it for your particular example:

可以使用索引，但优化器可能选择不将它用于您的特定示例：

SQL> create table my_objects
  2  as select object_id, object_name
  3  from all_objects;

Table created.

SQL> select count(*) from my_objects;
  2  /

  COUNT(*)
----------
     83783


SQL> alter table my_objects modify object_name null;

Table altered.

SQL> update my_objects
  2  set object_name=null
  3  where object_name like 'T%';

1305 rows updated.

SQL> create index my_objects_name on my_objects (lower(object_name));

Index created.

SQL> set autotrace traceonly

SQL> select * from my_objects
  2  where lower(object_name) like 'emp%';

29 rows selected.


Execution Plan
----------------------------------------------------------

------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |    17 |   510 |   355   (1)|
|   1 |  TABLE ACCESS BY INDEX ROWID| MY_OBJECTS      |    17 |   510 |   355   (1)|
|*  2 |   INDEX RANGE SCAN          | MY_OBJECTS_NAME |   671 |       |     6   (0)|
------------------------------------------------------------------------------------

The documentation you read was presumably pointing out that, just like any other index, all-null keys are not stored in the index.

您阅读的文档大概指出，就像任何其他索引一样，全空键不存储在索引中。

回答by CaptainPicard

In your example you've created the same index twice - this would give an error so I'm assuming that was a mistake in pasting, not the actual code you tried.

在您的示例中，您创建了两次相同的索引 - 这会产生错误，所以我假设这是粘贴错误，而不是您尝试的实际代码。

I tried it with

我试过了

CREATE INDEX idx_person_upper_surname ON person (UPPER(surname));

SELECT * FROM person WHERE UPPER(surname) LIKE 'P%';

and it produced the expected query plan:

它产生了预期的查询计划：

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=67)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'PERSON' (TABLE) (Cost=1
          Card=1 Bytes=67)

   2    1     INDEX (RANGE SCAN) OF 'IDX_PERSON_UPPER_SURNAME' (INDEX)
           (Cost=1 Card=1)

To answer your question, yes it should work. Try double checking that you do have the second index created correctly.

要回答您的问题，是的，它应该可以工作。尝试仔细检查您是否正确创建了第二个索引。

Also try an explicit hint:

还可以尝试一个明确的提示：

SELECT /*+INDEX(PERSON IDX_PERSON_UPPER_SURNAME)*/ * 
FROM person 
WHERE UPPER(surname) LIKE 'P%';

If that works, but only with the hint, then it is likely related to CBO statistics gone wrong, or CBO related init parameters.

如果这有效，但只有提示，那么它可能与 CBO 统计出错有关，或者与 CBO 相关的初始化参数有关。

回答by CaptainPicard

Are you sure you want the index to be used? Full table scans are not bad. Depending on the size of the table, it might be more efficient to do a table scan than use an index. It also depends on the density and distribution of the data, which is why statistics are gathered. The cost based optimizer can usually be trusted to make the right choice. Unless you have a specific performance problem, I wouldn't worry too much about it.

您确定要使用索引吗？全表扫描也不错。根据表的大小，进行表扫描可能比使用索引更有效。它还取决于数据的密度和分布，这就是收集统计数据的原因。通常可以信任基于成本的优化器做出正确的选择。除非您有特定的性能问题，否则我不会太担心。

回答by David Aldridge

You can circumvent the problem of null values being unindexed in this or other situations by also indexing based on a literal value:

您还可以通过基于文字值进行索引来规避在这种或其他情况下未索引空值的问题：

CREATE INDEX idx_person_upper_surname ON person (UPPER(surname),0);

This allows you to use the index for such queries as:

这允许您将索引用于以下查询：

Select *
From   person
Where  UPPER(surname) is null;

This query would normally not usa an index, except for bitmap indexes or indexes including a nonnullable real column other than surname.

此查询通常不会使用索引，除了位图索引或包含除姓氏以外的不可为空的实列的索引。

回答by Nick Pierpoint

Oracle will still use a function-based indexes with columns that contain null - I think you misinterpreted the documentation.

Oracle 仍将使用包含空值的列的基于函数的索引 - 我认为您误解了文档。

You need to put a nvl in the function index if you want to check for this though.

如果你想检查这个，你需要在函数索引中放置一个 nvl 。

Something like...

就像是...

create index idx_person_upper_surname on person (nvl(upper(surname),'N/A'));

You can then query using the index with

然后，您可以使用索引进行查询

select * from person where nvl(upper(surname),'N/A') = 'PIERPOINT'

Although, all a bit ugly. Since most people have surnames, perhaps a "not null" is appropriate :-).

虽然，都有点难看。由于大多数人都有姓氏，也许“非空”是合适的:-)。