This discussion is archived
11 Replies Latest reply: Mar 19, 2012 7:11 PM by 84959 RSS

Migration from Postgresql to Oracle

922027 Newbie
Currently Being Moderated
Hi,
I want to migarte Array data type of Postgresql to Oracle.
I have a table with 3 columns having array data type.
Can i go with Nested Tables?
If yes then, Is there any performance loss?

I have below table in postgre and i want to migrate it in Oracle.
CREATE TABLE EMP_DETAIL
2 ( "ID"     Integer PRIMARY KEY,
3      "NAME"     Text,
4      "DESCRIPTION" ARRAY,
5 "CITY_ALLOC" ARRAY,
6 "LANGUAGE" ARRAY
)
/

Please suggest the best Approach...

Thanks in advance.
  • 1. Re: Migration from Postgresql to Oracle
    matta - oracle Explorer
    Currently Being Moderated
    Just to be clear, The migration feature of SQL Developer does not support the migration of Postgres to Oracle.

    Thanks!
    Matt
  • 2. Re: Migration from Postgresql to Oracle
    922027 Newbie
    Currently Being Moderated
    Hi,
    Yes i know SQL Developer does not support Migration from Postgre to Oracle.

    My question is for migrating Array data type in Oracle which could be the best approach?
    If i go with Nested table there is performance loss and my table contains three Array column.
    In Postgre i have four tables having 2-3 columns which are storing array data.
    Master/Details approach require changes in table structure that i can not do.

    Please suggest the best approach i can go with...


    Thanks in advance..
  • 3. Re: Migration from Postgresql to Oracle
    84959 Explorer
    Currently Being Moderated
    Hello

    Nested tables is implemented using parent/child table concept with foriegn keys. Since you have more than one column in the table that has an array data type, perhaps you can consider putting the values for those 3 columns in a child table with a foriegn key constraint to the EMP_DETAIL table?. Will be helpful if you don't access the data often or want selective retreival of data from the child table. Here are the reasons why you shouldn't use nested tables as per Tom Kyte and I quote (The related article is here: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:8135488196597):

    1) they are parent child tables in disguise but ones that add:

    a 16 byte raw with a unique constraint on the parent table. Most likely you ALREADY HAVE a primary
    key on the parent table and most likely it is smaller.

    a 16 byte raw that you need to index on the child (not auto-indexed, you need to know to do it).
    This is the foreign key and is hidden from you.

    The inability to put many types of constraints on the nested table..

    They are simple parent/child tables - except you lose the ability to access the child table
    directly.


    2) you are NOT storing anything in a "single row". Physically they are a parent child table pair,
    nothing more, nothing less.

    Regards

    Prakash
  • 4. Re: Migration from Postgresql to Oracle
    922027 Newbie
    Currently Being Moderated
    Hi,

    Thanks for your reply..

    Instead of going with nested table,if i go with relational master/details approach will it give good performance.
    Suppose
    In my example i have modified design with master/detail approach.

    CREATE TABLE NESTED_TABLE
    1 ("ID"     NUMBER(38,0) PRIMARY KEY,
    2      "NAME"     VARCHAR2(100 BYTE))
    3 /


    CREATE TABLE NESTED_TABLE_CHILD
    1 ( "ID" NUMBER (38,0),
    2      "DESCRIPTION" CLOB,
    3 "CITY_ALLOC" CLOB,
    4 "LANGUAGE" CLOB,
    5 CONSTRAINT "FKAID" FOREIGN KEY("ID") REFERENCES NESTED_TABLE ("ID"))
    /

    Is the above mentioned approach is good for my requirement?

    Thanks in advance.....
  • 5. Re: Migration from Postgresql to Oracle
    84959 Explorer
    Currently Being Moderated
    Performance willl not be an issue when using parent/child tables because they are regular tables and nothing extra-ordinary is happening. You can access the parent table without accessing the child and vice versa.

    Regards

    Prakash

    [*Migrating to the Cloud - Client/Server migrations to Oracle*|http://www.syngress.com/information-security-and-system-administrators/Migrating-to-the-Cloud/]
  • 6. Re: Migration from Postgresql to Oracle
    922027 Newbie
    Currently Being Moderated
    Hi,

    I used Sql loader utility for loading data.
    My dat file is


    1 neham {abcd,csadfasf,asfdfasdfad} {Raipur,Delhi,Kolkata} {Hindi,English}
    2 sapnam \N {Hyderabad,Mumbai,Delhi}

    If i go with the mentione approach How would be my control file ? I want to store data as in below format

    ID name Descripion City_Alloc Language
    1 Neham abcd,csadfasf,asfdfasdfad Raipur,Delhi,Kolkata Hindi,English
    2 sapnam Hyderabad,Mumbai,Delhi

    Thanks in advance....
  • 7. Re: Migration from Postgresql to Oracle
    922027 Newbie
    Currently Being Moderated
    Hi,

    Instead of implementing master/child relation ship for array data can i declare array column as clob in oracle and store data in comma separated form.

    My table structure will be


    CREATE TABLE NESTED_TABLE
    1 ("ID" NUMBER(38,0) PRIMARY KEY,
    2 "NAME" VARCHAR2(100 BYTE),
    3 "DESCRIPTION" CLOB,
    3 "CITY_ALLOC" CLOB,
    4 "LANGUAGE" CLOB
    )
    /



    Select * from NESTED_TABLE;


    ID Name DESCRIPTION CITY_ALLOC LANGUAGE
    1 neham abcd,csadfasf,asfdfasdfad Raipur,Delhi,Kolkata Hindi,english
    2 sapna Hyderabad,Mumbai,Delhi


    Thanks in advance....
  • 8. Re: Migration from Postgresql to Oracle
    84959 Explorer
    Currently Being Moderated
    Hello.

    Ofcourse you can create a clob and store the nested table's data in that column or it can be a big VARCHAR2(4000) column also if the data is not too large. The problem with this approach is that you loose the visibility into data. You will have to perform some decode/reg_exp functions to get the values out of the CLOB to do any processing in your application. The basic premise of the parent/child tables is that each value in the array will become a record in the child table with a link to the parent table and that data will be directly accessible to you as a record.

    For example:

    Source data: Name,DOB,Location, {10,20,30,40}

    Then you can transform that as:

    Parent table: ID, Name, DOB,Location (Whatever is the PK in there).
    Child Table: Parent_ID, 10
    Parent_ID, 20
    Parent_ID,30
    Parent_ID,40
    and so on.
  • 9. Re: Migration from Postgresql to Oracle
    922027 Newbie
    Currently Being Moderated
    Thanks for your reply its really very helpful...
  • 10. Re: Migration from Postgresql to Oracle
    922027 Newbie
    Currently Being Moderated
    In postgre i can select,update,delete array data based on index number..

    like

    select language_known{2} from emp where empno=2;

    update emp set language_known{2}={'gujarathi'} where empno=2;

    If i go with the master child approach as you mentioned
    can i get the same order of element after deletion or updation or Oracle may return them in any order.

    Please clarify..
  • 11. Re: Migration from Postgresql to Oracle
    84959 Explorer
    Currently Being Moderated
    Unfortunately the ordering of rows in the child table will follow the regular data storage patterns in the database i.e. you will have to use ORDER BY in SELECT statements to see data sorted in an order consistently.

    Regards

    Prakash

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points