Using LIKE with index over GUID columns in Oracle-CodePudding

I'm building system, in which I decided to use GUIDs as primary keys in many tables. I took this decision in order to be able to develop tool for integration tests. If I have primary key generated once during some occasion, I can be sure that I will never get same GUID generated by chance so that means that I can be sure that data I will prepare for some test case will be unique and easily transferable into any other database, because it will be unique also there. So I use for my primary keys RAW(16) datatype.

CREATE TABLE "VMCRM"."THO_USERS" (
  "VMUSERGUID" RAW(16),
  "STATUS" VARCHAR2(2 BYTE),
  "EMAIL" VARCHAR2(255 BYTE),
  "TEL" VARCHAR2(50 BYTE)
);
CREATE UNIQUE INDEX "VMCRM"."THO_USER_PK" ON "VMCRM"."THO_USERS" ("VMUSERGUID") ;
ALTER TABLE "VMCRM"."THO_USERS" MODIFY ("VMUSERGUID" NOT NULL ENABLE);

Problem that I'm facing right now is, that I would like to prepare functionality for my employees which would allow them to filter for example users of application by they GUID. In most cases it would be efficient for them to enter into field just first 8 characters of GUID - which would be sufficient in most cases to find the exact user. However I'm struggling with Oracle index engine. I would need to perform following query and expect, that it will use index I have on column. But it is not happening.

select * from tho_users where vmuserguid like '0001EF16%'

The query runs fine, except the fact that index is not used - full scan is performed.

Does anybody have any clue how to achieve usage of index in such query over RAW(16) column?

CodePudding user response：

You are comparing a raw value with a string, which is causing implicit conversion. That prevents your index being used for your query- the data types aren't suitable any more.

You can add a function-based index that uses the string representation of the raw value:

create index ix_guid_2 on tho_users(rawtohex(vmuserguid));

db<>fiddle

That could be unique still, but can't really be used for the PK itself - it will need to be an additional index. This db<>fiddle is the same except the raw value is a real PK.

LIKE compares strings, so if you're comparing a non-string value to a string you're forcing the implicit conversion, which prevents the raw index being used. A function-based index is creating an index on that converted value, which can be used.