index(,constant_value)
使用上面的格式创建的索引可以存储NULL，
但是在使用中，如果应用Rownum = 1 则返回空，这是不是个Bug？
如下：
[php]
create table test(id int,name varchar2(20));
create index idx on test(id,1);
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE10.2.0.1.0Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> create table test(id int,name varchar2(20));
Table created.
SQL> create index idx on test(id,1);
Index created.
SQL> insert into test values(14,'abc');
1 row created.
SQL> insert into test values(null,'Oracle');
1 row created.
SQL> insert into test values(3,'Microsoft');
1 row created.
SQL> insert into test values(null,null);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test;
ID NAME
---------- --------------------
14 abc
Oracle
3 Microsoft
SQL> select count(*) from test;
COUNT(*)
----------
4
SQL> analyze table test compute statistics;
Table analyzed.
SQL> analyze index idx compute statistics;
Index analyzed.
SQL> set feedback on
SQL> set autotrace on
SQL> select /*+ index(test idx) */ *
2from test where id is null;
ID NAME
---------- --------------------
Oracle
2 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4092295894
------------------------------------------------------------------------------------
| Id| Operation
| Name | Rows| Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT
|| 2 |14 | 2 (0)| 00:00:01 |
| 1 |TABLE ACCESS BY INDEX ROWID| TEST | 2 |14 | 2 (0)| 00:00:01 |
|*2 | INDEX RANGE SCAN
| IDX| 2 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID" IS NULL)
Statistics
----------------------------------------------------------
0recursive calls
0db block gets
4consistent gets
0physical reads
0redo size
501bytes sent via SQL*Net to client
385bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
2rows processed
---这里应用了索引，并返回两条记录
SQL> select /*+ index(test idx) */ * from test
2where id is null and rownum = 1;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 2182711683
-------------------------------------------------------------------------------------
| Id| Operation
| Name | Rows| Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT
|| 1 | 7 | 2 (0)| 00:00:01 |
|*1 |COUNT STOPKEY
|| | |
|
|
| 2 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 7 | 2 (0)| 00:00:01 |
|*3 |INDEX RANGE SCAN
| IDX| 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
3 - access("ID" IS NULL AND ROWNUM=1)
filter(ROWNUM=1)
Statistics
----------------------------------------------------------
1recursive calls
0db block gets
1consistent gets
0physical reads
0redo size
325bytes sent via SQL*Net to client
374bytes received via SQL*Net from client
1SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
0rows processed
---这里应用了索引但是返回null.
SQL> select /*+ index(test idx) */ * from test
2where id is null and rownum
[/php]
有metalink的同志，可以帮忙看看以下网址，方便的话把内容copy过来看看！
https://metalink.oracle.com/meta ... 98234::::P20_TARUNM,P20_CTKYCODE:6701767.922
|
