Insert data into a table while preserving the order
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Insert data into a table while preserving the order

  1. #1
    Join Date
    Nov 2003
    Posts
    31

    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

  2. #2
    Join Date
    Dec 2000
    Posts
    126
    you can sort table by id

    SELECT * FROM PARTS ORDER BY ID

  3. #3
    Join Date
    Jan 2001
    Posts
    2,828

    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 ?

  4. #4
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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

  5. #5
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,471

    Cool

    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

  6. #6
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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

  7. #7
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,471

    Cool

    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

  8. #8
    Join Date
    Jan 2004
    Posts
    162
    > 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>

  9. #9
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,471

    Thumbs down

    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

  10. #10
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width