Hi guys,
I'm curious why the function based index is not used in the following SELECT statement.
SELECT USERNAME FROM USERS WHERE USERNAME = 'MyUser' AND IS_DELETED = 0;
Or
SELECT USERNAME FROM USERS WHERE USERNAME = 'MyUser';
Query executionUSERNAME
--------------------------------------------------------------------------------
MyUser
1 row selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer Mode=ALL_ROWS (Cost=5 Card=1 Bytes=28)
1 0 TABLE ACCESS FULL ENVTEST.USERS (Cost=5 Card=1 Bytes=28)
Schema script:CREATE TABLE USERS
(
USER_ID NUMBER(10) CONSTRAINT CHK_USERS_001 NOT NULL,
USERNAME VARCHAR2(255 BYTE) CONSTRAINT CHK_USERS_002 NOT NULL,
PASSWORD VARCHAR2(30 BYTE) CONSTRAINT CHK_USERS_003 NOT NULL,
IS_DELETED NUMBER(1)DEFAULT 0 CONSTRAINT CHK_USERS_004 NOT NULL
);
CREATE UNIQUE INDEX IDX_USERS_001
ON USERS(USER_ID);
CREATE UNIQUE INDEX IDX_USERS_002
ON USERS(CASE "IS_DELETED" WHEN 0 THEN "USERNAME" ELSE NULL END );
ALTER TABLE USERS ADD (
CONSTRAINT PK_USERS
PRIMARY KEY
(USER_ID)
USING INDEX IDX_USERS_001
ENABLE VALIDATE);
The UNIQUE Index is working properly during the INSERT avoiding duplicate USERNAMES when the flag IS_DELETED = 0. However, it’s seems that is not considered an index in the select.
Do I have a misconception or something wrong with the unique index definition?
To use a function-based index, you need to use the same function in your where clause:
create table users (
user_id number(10) constraint chk_users_001 not null,
username varchar2(255 byte) constraint chk_users_002 not null,
password varchar2(30 byte) constraint chk_users_003 not null,
is_deleted number(1)default 0 constraint chk_users_004 not null
);
create unique index idx_users_001 on users(user_id);
create unique index idx_users_002 on users (
case "IS_DELETED" when 0 then "USERNAME" else null end
);
alter table users add (
constraint pk_users primary key (user_id)
using index idx_users_001 enable validate
);
insert into users values (1, 'myuser', '*****', 0);
set serveroutput off
select username from users
where username = 'myuser' and is_deleted = 0;
USERNAME
myuser
select *
from table(dbms_xplan.display_cursor(null, null, 'BASIC LAST'));
PLAN_TABLE_OUTPUT
EXPLAINED SQL STATEMENT:
------------------------
select username from users where username = 'myuser' and is_deleted =
0
Plan hash value: 493328486
-----------------------------------
| Id | Operation | Name |
-----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| USERS |
-----------------------------------
select * from users
where case is_deleted when 0 then username else null end = 'myuser';
USER_ID USERNAME PASSWORD IS_DELETED
1 myuser ***** 0
select *
from table(dbms_xplan.display_cursor(null, null, 'BASIC LAST'));
PLAN_TABLE_OUTPUT
EXPLAINED SQL STATEMENT:
------------------------
select * from users where case is_deleted when 0 then username else
null end = 'myuser'
Plan hash value: 3799234671
-----------------------------------------------------
| Id | Operation | Name |
-----------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| USERS |
| 2 | INDEX UNIQUE SCAN | IDX_USERS_002 |
-----------------------------------------------------