Connor and Chris will both be at AI World from October 12 to October 17 , the premier Oracle conference of 2025. If you're in Vegas, please come say Hi or pop into our sessions
Thanks for the question, Homero.
Asked: February 05, 2018 - 10:26 pm UTC
Last updated: February 06, 2018 - 10:30 am UTC
Version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
Viewed 1000+ times
SELECT USERNAME FROM USERS WHERE USERNAME = 'MyUser' AND IS_DELETED = 0;
SELECT USERNAME FROM USERS WHERE USERNAME = 'MyUser';
USERNAME -------------------------------------------------------------------------------- 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)
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);
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 | -----------------------------------------------------
New to good database design? Check out Chris Saxon's full fundamentals class.