I hava a table like the following structure:
create table table_a
(
"ID_TABLE_A" NUMBER, --primary key column
"PARENT_ID" NUMBER,
"CONTENT_1" VARCHAR2(32),
"CONTENT_2" NUMBER,
"CONTENT_3" NUMBER,
"CONTENT_4" VARCHAR2(256),
"CONTENT_5" varchar2(128)
)
There are about 200 records in the table_a which composite
one logical collection, and is hierarchy structure. There may be many many such collection
s.
for the purpose of building a tree from such a collection which has about 200 records, I choose
manually generating primary keys, and they are continuous integers. So they can easily be ordered by sql, and built into a tree by java code(not related with oracle db).
Problems occurs when it comes that sometimes we need insert some extra records at some position into such collection, or delete some records at some position from such collection.
To keep the primary key column continuous integers. I may choose update primary key or update other content columns.
To make it clear, suppose we have records:
"ID_TABLE_A", "PARENT_ID", "CONTENT_1", "CONTENT_2", "CONTENT_3", "CONTENT_4", "CONTENT_5"
1, null, null, null, null, null, null
2, 1, null, null, null, null, null
3, 1, null, null, null, null, null
4, 1, null, null, null, null, null
5, 2, null, null, null, null, null
6, 2, null, null, null, null, null
7, 3, null, null, null, null, null
8, 3, null, null, null, null, null
9, 4, null, null, null, null, null
10, 4, null, null, null, null, null
11, 5, null, null, null, null, null
12, 6, null, null, null, null, null
13, 7, null, null, null, null, null
14, 8, null, null, null, null, null
15, 9, null, null, null, null, null
16, 10, null, null, null, null, null
the above 16 records is a collection.
now we need add another child for 1, so its "ID_TABLE_A" should be 5, which already existed.
so I can choose:
A:
update ID_TABLE_A set ID_TABLE_A = ID_TABLE_A + 1 where ID_TABLE_A >= 5;
insert into ID_TABLE_A (ID_TABLE_A) values(5);
B:
update ID_TABLE_A set PARENT_ID = #{}, CONTENT_1 = #{}, CONTENT_2 = #{}, CONTENT_3 = #{},
CONTENT_4 = #{}, CONTENT_5 = #{}; -- for every record "ID_TABLE_A" >= 5
insert into ID_TABLE_A (ID_TABLE_A, PARENT_ID, CONTENT_1, CONTENT_2, CONTENT_3, CONTENT_4,
CONTENT_5) values(17, 10, null, null, null, null, null);
way A need update primary key column, and I don't know the performance behavior.
way B need copy parent_id column and other content columns.
way A only need 2 SQL,
way B need many SQLs.
I really don't know which way is better.
Could you guys shine me some light? Sorry for my poor English.