By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table tho_users (vmuserguid raw(16))
create index ix_guid_1 on tho_users(vmuserguid);
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 |
|---|
| 0xDF96C067EED7BFF0E0537D04A8C02E5E |
| 0x0001EF1601020304 |
select * from tho_users where vmuserguid like '0001EF16%'
| VMUSERGUID |
|---|
| 0x0001EF1601020304 |
select * from tho_users where vmuserguid like 'DEE7%'
explain plan for select * from tho_users where vmuserguid like '0001EF16%'
select * from table(dbms_xplan.display);
| PLAN_TABLE_OUTPUT |
|---|
| Plan hash value: 2858187039 |
| ------------------------------------------------------------------------------- |
| | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | |
| ------------------------------------------------------------------------------- |
| | 0 | SELECT STATEMENT | | 1 | 10 | 3 (0)| 00:00:01 | |
| |* 1 | TABLE ACCESS FULL| THO_USERS | 1 | 10 | 3 (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: 2858187039 |
| ------------------------------------------------------------------------------- |
| | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | |
| ------------------------------------------------------------------------------- |
| | 0 | SELECT STATEMENT | | 1 | 10 | 3 (0)| 00:00:01 | |
| |* 1 | TABLE ACCESS FULL| THO_USERS | 1 | 10 | 3 (0)| 00:00:01 | |
| ------------------------------------------------------------------------------- |
| Predicate Information (identified by operation id): |
| --------------------------------------------------- |
| 1 - filter(RAWTOHEX("VMUSERGUID") LIKE '0001EF16%') |
| Hint Report (identified by operation id / Query Block Name / Object Alias): |
| Total hints for statement: 1 (U - Unused (1)) |
| --------------------------------------------------------------------------- |
| 1 - SEL$1 / "TU"@"SEL$1" |
| U - INDEX(tu) |
| 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) |