Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2003
    Posts
    34

    Unanswered: a simple question

    Hi Friends,

    I have a little and simple problem (for you but not for me).
    I'm working with ORACLE 9i.

    I want to insert rows from table a into table b (millions of rows).

    If I use : "insert x,y into b (select x,y from a);" then I have RBS problem.

    Now , can I "partially commit" this istruction?
    or can I have an alternative method ? (no explicit cursor ,naturally)!!

    Thank you in advance and excuse my English.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: a simple question

    The best answer is: use a rollback segment that is big enough for the transaction and do the INSERT...SELECT in one go. Get your DBA to create one if none exists.

    You can specify a particular rollback segment before beginning the transaction like this:

    SET TRANSACTION USE ROLLBACK SEGMENT <name>

    If you go down the partial commit route, then you face the danger of ending up with some of your data copied and some not, and having to work out what's what.

  3. #3
    Join Date
    Oct 2002
    Location
    greenwich.ct.us
    Posts
    279

    Re: a simple question

    Originally posted by andrewst
    The best answer is: use a rollback segment that is big enough for the transaction and do the INSERT...SELECT in one go. Get your DBA to create one if none exists.

    You can specify a particular rollback segment before beginning the transaction like this:

    SET TRANSACTION USE ROLLBACK SEGMENT <name>

    If you go down the partial commit route, then you face the danger of ending up with some of your data copied and some not, and having to work out what's what.
    And, you will most likely end up with the dreaded ORA-1555 (snapshot too old) error message because your RBS aren't big enough.

    You may want to look at the SQL*Plus "COPY" command...

  4. #4
    Join Date
    Jan 2003
    Posts
    34
    Thank you for advices, Friend

    The reason i use INSERT..SELECT FROM is I want to
    use PARALLEL option .

    So I don't belive COPY is has this option,
    Can I use "Function Table"?


    Pappagone1

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by pappagone1
    Thank you for advices, Friend

    The reason i use INSERT..SELECT FROM is I want to
    use PARALLEL option .

    So I don't belive COPY is has this option,
    Can I use "Function Table"?


    Pappagone1
    I've never heard of a "Function Table", what is that?
    By the way, have you considered using NOLOGGING to reduce redo on your INSERT..SELECT? You would need to backup afterwards.

  6. #6
    Join Date
    Jan 2003
    Posts
    34
    this is an extract:


    Transformation Using Table Functions
    Oracle9i’s table functions provide the support for pipelined and parallel execution
    of transformations implemented in PL/SQL, C, or Java. Scenarios as mentioned
    earlier can be done without requiring the use of intermediate staging tables, which
    interrupt the data flow through various transformations steps.
    What is a Table Function?
    A table function is defined as a function that can produce a set of rows as output.
    Additionally, table functions can take a set of rows as input. Prior to Oracle9i,
    PL/SQL functions:
    n Could not take cursors as input
    n Could not be parallelized or pipelined
    Starting with Oracle9i, functions are not limited in these ways. Table functions
    extend database functionality by allowing:
    n Multiple rows to be returned from a function
    n Results of SQL subqueries (that select multiple rows) to be passed directly to
    functions
    n Functions take cursors as input
    n Functions can be parallelized
    n Returning result sets incrementally for further processing as soon as they are
    created. This is called incremental pipelining
    Table functions can be defined in PL/SQL using a native PL/SQL interface, or in
    Java or C using the Oracle Data Cartridge Interface (ODCI).
    See Also: PL/SQL User’s Guide and Reference for further
    information and Oracle9i Data Cartridge Developer’s Guide

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Oh I see. You said "Function Tables" not "Table Functions", but then I had not heard them called that before anyway! But, in any case: no, I don't see how these are going to help with what you are trying to do, which is insert data from one table into another. Whether you SELECT the data direct, or get it via a function call that returns many rows of data, you are still going to have to INSERT the data and COMMIT it at some point, and the same rollback space issues will apply.

    What about our previous suggestions - have you tried any of those? I'm not even sure whether you actually have a problem running the INSERT..SELECT statement, or whether you just fear that you might. The simplest, fastest and best way to do it is with a single INSERT..SELECT followed by a COMMIT, with a rollback segment big enough for the job.

Posting Permissions

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