11 Replies Latest reply: Mar 19, 2012 9:11 PM by Pnauduri-Oracle RSS

    Migration from Postgresql to Oracle

    922027
      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
          mxallen-Oracle
          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
            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
              Pnauduri-Oracle
              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
                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
                  Pnauduri-Oracle
                  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
                    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
                      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
                        Pnauduri-Oracle
                        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
                          Thanks for your reply its really very helpful...
                          • 10. Re: Migration from Postgresql to Oracle
                            922027
                            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
                              Pnauduri-Oracle
                              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