-
Insert data into a table while preserving the order
Hi All,
I have a table with ID values from A thro' S with some values
in between A and S missing. Now, after the table has been in
production for a while, I need to insert more rows. However,
this is disturbing the alphabetic order I have originally on
the ID field. Just looking for ways to see if I can insert the
values at the appropriate locations.
Here are the SQL statements that I used.
CREATE TABLE PARTS (ID VARCHAR2(10), DESCRIPTION VARCHAR2(25));
INSERT INTO PARTS VALUES ('A', 'HARDWARE');
INSERT INTO PARTS VALUES ('B', 'SOFTWARE');
INSERT INTO PARTS VALUES ('D', 'HARDWARE');
INSERT INTO PARTS VALUES ('J', 'SOFTWARE');
INSERT INTO PARTS VALUES ('M', 'HARDWARE');
INSERT INTO PARTS VALUES ('P', 'HARDWARE');
INSERT INTO PARTS VALUES ('Q', 'HARDWARE');
INSERT INTO PARTS VALUES ('S', 'SERVICES');
SELECT * FROM PARTS;
ID DESCRIPTION
== ===========
A HARDWARE
B SOFTWARE
D HARDWARE
J SOFTWARE
M HARDWARE
P HARDWARE
Q HARDWARE
S SERVICES
Now, I need to enter the following values into the table.
INSERT INTO PARTS VALUES ('AL', 'ADJUSTMENT');
INSERT INTO PARTS VALUES ('xx', 'UNKNOWN');
INSERT INTO PARTS VALUES ('H', 'HARDWARE');
INSERT INTO PARTS VALUES ('N', 'SUBSCRIPTION');
INSERT INTO PARTS VALUES ('O', 'OTHER');
If I do a select * from this table, the id values are not sorted alpahbetically any more. I cannot afford to drop the table
and create again. Is there anyway I can enter these values in the
alphabetic sequence so the ID values will show up as A, B, D, H,
J, M, N, O, P, Q, S, Al, xx?
I can get AL and xx as the last two values if I have those 2 inserts
at the end in that order. My question is about inserting the values
H, N and O in the appropriate places.
Oracle version is 9.2.0.2.0 and I am running on Solaris 9.
Any help would be greatly appreciated.
Many Thanks.
Sankar
Last edited by sankar6254; 05-16-2005 at 08:26 PM.
Sankar B. Mandalika
-
you can sort table by id
SELECT * FROM PARTS ORDER BY ID
-
Re: Insert data into a table while preserving the order
Originally posted by sankar6254
Hi All,
I have a table with ID values from A thro' S with some values
in between A and S missing. Now, after the table has been in
production for a while, I need to insert more rows. However,
this is disturbing the alphabetic order I have originally on
the ID field. Just looking for ways to see if I can insert the
values at the appropriate locations.
Here are the SQL statements that I used.
CREATE TABLE PARTS (ID VARCHAR2(10), DESCRIPTION VARCHAR2(25));
INSERT INTO PARTS VALUES ('A', 'HARDWARE');
INSERT INTO PARTS VALUES ('B', 'SOFTWARE');
INSERT INTO PARTS VALUES ('D', 'HARDWARE');
INSERT INTO PARTS VALUES ('J', 'SOFTWARE');
INSERT INTO PARTS VALUES ('M', 'HARDWARE');
INSERT INTO PARTS VALUES ('P', 'HARDWARE');
INSERT INTO PARTS VALUES ('Q', 'HARDWARE');
INSERT INTO PARTS VALUES ('S', 'SERVICES');
SELECT * FROM PARTS;
ID DESCRIPTION
== ===========
A HARDWARE
B SOFTWARE
D HARDWARE
J SOFTWARE
M HARDWARE
P HARDWARE
Q HARDWARE
S SERVICES
Now, I need to enter the following values into the table.
INSERT INTO PARTS VALUES ('AL', 'ADJUSTMENT');
INSERT INTO PARTS VALUES ('xx', 'UNKNOWN');
INSERT INTO PARTS VALUES ('H', 'HARDWARE');
INSERT INTO PARTS VALUES ('N', 'SUBSCRIPTION');
INSERT INTO PARTS VALUES ('O', 'OTHER');
If I do a select * from this table, the id values are not sorted alpahbetically any more. I cannot afford to drop the table
and create again. Is there anyway I can enter these values in the
alphabetic sequence so the ID values will show up as A, B, D, H,
J, M, N, O, P, Q, S, Al, xx?
I can get AL and xx as the last two values if I have those 2 inserts
at the end in that order. My question is about inserting the values
H, N and O in the appropriate places.
Oracle version is 9.2.0.2.0 and I am running on Solaris 9.
Any help would be greatly appreciated.
Many Thanks.
Sankar
why do you want to do this at all ?
-
It's part of the concept: The rows in a table are not ordered . . . FULL STOP. You may find them in different orders depending on practically anything that is going on in the database.
If you want ordered output you MUST use "order by".
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
-
Or.....You could create the table as "ORAGANIZATION INDEX"
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
-
I wondered about IOT . . . and came to the conclusion that even then you couldn't be sure about the order e.g. if it was being read by parallel query. Was I wrong?
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
-
I beleive there should be no problem, IOT rows are retieved in the order of the KEY, this behaviour matches that of retrieving table rows using an index.
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
-
> I beleive there should be no problem
So much for belief.
Code:
Personal Oracle Database 10g Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> CREATE TABLE table_name
2 (column_name VARCHAR2 (3),
3 CONSTRAINT constraint_name
4 PRIMARY KEY (column_name))
5 ORGANIZATION INDEX
6 PARALLEL 4;
Table created.
SQL> INSERT INTO table_name
2 WITH alpha AS (
3 SELECT CHR (64 + column_value) chr
4 FROM TABLE (many (26)))
5 SELECT a.chr || b.chr || c.chr
6 FROM alpha a, alpha b, alpha c;
17576 rows created.
SQL> SELECT *
2 FROM table_name
3 WHERE ROWNUM <= 10;
COL
---
IWU
IWV
IWW
IWX
IWY
IWZ
IXA
IXB
IXC
IXD
10 rows selected.
SQL> SELECT *
2 FROM (SELECT *
3 FROM table_name
4 ORDER BY column_name)
5 WHERE ROWNUM <= 10;
COL
---
AAA
AAB
AAC
AAD
AAE
AAF
AAG
AAH
AAI
AAJ
10 rows selected.
SQL>
-
Ooops, Busted...
Then the IOT solution (or some other index solution) would require the use of a HINT:
Code:
SQL>SELECT /*+ INDEX(table_name, constraint_name ) */ *
2 FROM table_name
3 WHERE ROWNUM <= 10;
COL
---
AAA
AAB
AAC
AAD
AAE
AAF
AAG
AAH
AAI
AAJ
PS: It seemed to work very well for small tables, but how small is small?
Last edited by LKBrwn_DBA; 05-18-2005 at 12:14 PM.
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
-
Fast full index scan certainly wouldn't be expected to return IOT rows in any order, and I wouldn't be trusting a hint.
What's the problem with an ORDER BY clause?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|