By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table tho_users (vmuserguid raw(16) primary key)
insert into tho_users (vmuserguid) values (sys_guid());
1 rows affected
insert into tho_users (vmuserguid) values (hextoraw('0001EF1601020304'));
1 rows affected
select * from tho_users
| VMUSERGUID |
|---|
| 0x0001EF1601020304 |
| 0xDEE7F692FDE47FB3E0537D04A8C03E45 |
select * from tho_users where vmuserguid like '0001EF16%'
| VMUSERGUID |
|---|
| 0x0001EF1601020304 |
select * from tho_users where vmuserguid like 'DEE7%'
| VMUSERGUID |
|---|
| 0xDEE7F692FDE47FB3E0537D04A8C03E45 |
explain plan for select * from tho_users where vmuserguid like '0001EF16%'
select * from table(dbms_xplan.display);
| PLAN_TABLE_OUTPUT |
|---|
| Plan hash value: 2535231230 |
| ---------------------------------------------------------------------------------- |
| | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | |
| ---------------------------------------------------------------------------------- |
| | 0 | SELECT STATEMENT | | 1 | 10 | 2 (0)| 00:00:01 | |
| |* 1 | INDEX FULL SCAN | SYS_C00120918 | 1 | 10 | 2 (0)| 00:00:01 | |
| ---------------------------------------------------------------------------------- |
| Predicate Information (identified by operation id): |
| --------------------------------------------------- |
| 1 - filter(RAWTOHEX("VMUSERGUID") LIKE '0001EF16%') |
| Note |
| ----- |
| - dynamic statistics used: dynamic sampling (level=2) |
explain plan for select /*+ INDEX(tu) */ * from tho_users tu where vmuserguid like '0001EF16%'
select * from table(dbms_xplan.display);
| PLAN_TABLE_OUTPUT |
|---|
| Plan hash value: 2535231230 |
| ---------------------------------------------------------------------------------- |
| | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | |
| ---------------------------------------------------------------------------------- |
| | 0 | SELECT STATEMENT | | 1 | 10 | 2 (0)| 00:00:01 | |
| |* 1 | INDEX FULL SCAN | SYS_C00120918 | 1 | 10 | 2 (0)| 00:00:01 | |
| ---------------------------------------------------------------------------------- |
| Predicate Information (identified by operation id): |
| --------------------------------------------------- |
| 1 - filter(RAWTOHEX("VMUSERGUID") LIKE '0001EF16%') |
| Note |
| ----- |
| - dynamic statistics used: dynamic sampling (level=2) |
create index ix_guid_2 on tho_users(rawtohex(vmuserguid));
explain plan for select * from tho_users where vmuserguid like '0001EF16%'
select * from table(dbms_xplan.display);
| PLAN_TABLE_OUTPUT |
|---|
| Plan hash value: 3627542164 |
| ------------------------------------------------------------------------------------------------- |
| | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | |
| ------------------------------------------------------------------------------------------------- |
| | 0 | SELECT STATEMENT | | 1 | 28 | 2 (0)| 00:00:01 | |
| | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| THO_USERS | 1 | 28 | 2 (0)| 00:00:01 | |
| |* 2 | INDEX RANGE SCAN | IX_GUID_2 | 1 | | 1 (0)| 00:00:01 | |
| ------------------------------------------------------------------------------------------------- |
| Predicate Information (identified by operation id): |
| --------------------------------------------------- |
| 2 - access(RAWTOHEX("VMUSERGUID") LIKE '0001EF16%') |
| filter(RAWTOHEX("VMUSERGUID") LIKE '0001EF16%') |
| Note |
| ----- |
| - dynamic statistics used: dynamic sampling (level=2) |