I am looking into an issue with concatenated index
(also known as multi-column, composite or combined index).
So, by the current design, I have 2 columns KEY1 and KEY2, which produce my PRIMARY KEY PK1.
CREATE TABLE "myTable"
( "KEY1" VARCHAR2(7 CHAR) NOT NULL ENABLE,
"KEY2" NUMBER(7,0) NOT NULL ENABLE,
"INFO1" VARCHAR2(1 CHAR),
"INFO2" VARCHAR2(2 CHAR)
CONSTRAINT "PK1" PRIMARY KEY ("KEY1", "KEY2") ENABLE
)
K1 |K2
------|-----
12345 |1254
564874|521
6689 |76758
So the PK1 would be something like this
PK1
---------
123451254
564874521
668976758
Now what I want to do is searching directly for the concatenated PK1 like
SELECT * FROM myTable WHERE PK1 = 564874521
Is there any possibility to do that in Oracle directly, instead of using constructs like
SELECT * FROM myTable WHERE K1 = 564874 AND K2 = 521
You can create a virtual column that concatenates the two. And index that:
CREATE TABLE t (
"KEY1" VARCHAR2(7 CHAR) NOT NULL ENABLE,
"KEY2" NUMBER(7,0) NOT NULL ENABLE,
"INFO1" VARCHAR2(1 CHAR),
"INFO2" VARCHAR2(2 CHAR),
CONSTRAINT "PK1" PRIMARY KEY ("KEY1", "KEY2") ENABLE
);
insert into t ( key1, key2 ) values (12345 ,1254);
insert into t ( key1, key2 ) values (564874,521);
insert into t ( key1, key2 ) values (6689 ,76758);
commit;
alter table t
add pk as ( key1 || key2 );
create index i
on t ( pk );
select * from t
where pk = '564874521';
KEY1 KEY2 INFO1 INFO2 PK
564874 521 <null> <null> 564874521
Note: the search value is a string. If you use a number here, the database to_number()'s the virtual column. Meaning it won't use the index.
Though this requirement looks fishy to me. You can easily end up with rows with a different primary key. But the same concatenated key:
insert into t ( key1, key2 ) values (56487,4521);
select * from t
where pk = '564874521';
KEY1 KEY2 INFO1 INFO2 PK
564874 521 <null> <null> 564874521
56487 4521 <null> <null> 564874521
Unless you can also add a unique constraint on the concatenated string, this method is bound to bring you problems eventually.