Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Andreas.

Asked: May 02, 2019 - 10:21 am UTC

Last updated: May 02, 2019 - 2:50 pm UTC

Version: 12

Viewed 1000+ times

You Asked

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



and Chris said...

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.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.