There are several things in play here. A table that has a self-referential key is said to have a reflexive foreign key, though that doesn't look like it applies here because you'll have missing values.
What you're looking for is a hierarchical query, which can be achieved in Oracle by using a CONNECT BY clause.
They can be a bit difficult to get your head around at first, but there are plenty of good examples on the internet. A good one to start with is here. For once, the official documentation is quite clear too!
Something like:
SELECT id, bar, reference_id, LEVEL
FROM yourtable
CONNECT BY PRIOR reference_id = id;
... will get you started on your data.
I like to use rownum. Using a CTE to get the current maximum PK value, you can easily double-up your data.
Example below.
Test table:
create table yourperftest
( pk number,
col1 number,
col2 number,
col3 number
);
Test data:
insert into yourperftest values (1,1,1,1);
insert into yourperftest values (2,2,2,2);
insert into yourperftest values (3,3,3,3);
insert into yourperftest values (4,4,4,4);
insert into yourperftest values (5,5,5,5);
Query to "double up" the table:
insert into yourperftest
with maxvalue as (
select max(pk) as maxvalue
from yourperftest
)
select rownum+maxvalue, col1, col2, col3
from yourperftest,maxvalue;
I'd probably use an /*+APPEND*/ hint too, plus make sure you gather stats on the table afterwards.
Having said that, stats play a big part in testing - you want to make sure that your data/performance test environment is as close to production as possible. Going forward, the stats are the key to this. Stats that are different between test and production will cause different query plans to be picked by the optimiser, which may make your performance testing invalid when production is going tits-up.
EDIT:
Since you ask about doing this for more than 1 column, here's another example:
insert into yourperftest
with maxvalue as (
select max(pk) as maxvalue
from yourperftest
), col1tab as (
select max(col1) as col1maxvalue
from yourperftest
)
select rownum+maxvalue, col1maxvalue+rownum, col2, col3
from yourperftest,maxvalue,col1tab ;
CTEs are brilliant.
Best Answer
This can be done with a partial index. A bit complicated in Oracle because it doesn't allow a
whereclause in thecreate indexstatement (as e.g. Postgres does).But you can still trick Oracle in only indexing a sub-set of the rows by exploiting the fact that entries where all index columns are null are not put into the index. So you create a a unique index on an expression that is only not-null if the status is
'ENABLE':The expression will yield
nullfor all rows where the status is something else thanenableand those rows won't be put into the index. Which in turn means than only the IDACCOUNT values for rows are put into the index where the status isENABLENote that
'enable'and'ENABLE'are two different values for Oracle. You probably also want a check constraint on your column to make sure you only put uppercase values in there (or use a trigger),Unrelated but: you most probably do not want to use
CHAR(10)butVARCHAR(10)due to the padding that happens when using the dreadedchardata type.