1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

PARALLEL query with ROWNUM

Discussion in 'SQL PL/SQL' started by jaytav001, May 14, 2009.

  1. jaytav001

    jaytav001 Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    hi friends,

    i am using following parallel query to insert 100 million data from sTable to dTable

    Code (SQL):
    INSERT /*+ PARALLEL(dTable,24) */ INTO dTable
    (col6,col1,col2,col3,col4,col5)
    SELECT /*+ PARALLEL(sTable,24) FULL(sTable) */
            ROWNUM,
            col1,
            col2,
            col3,
            col4,
            col5
    FROM sTable;
    Here, sTable and dTable both are partition (24) table.
    Normally this query takes 40 mins.

    Now if i remove ROWNUM from above query such as

    Code (SQL):
    INSERT /*+ PARALLEL(dTable,24) */ INTO dTable
    (col1,col2,col3,col4,col5)
    SELECT /*+ PARALLEL(sTable,24) FULL(sTable) */
            col1,
            col2,
            col3,
            col4,
            col5
    FROM sTable s;
    Now it takes 15 mins to insert data, which means insert query with ROWNUM is very slow.

    I want to use ROWNUM in my query. Is there any other alternative to do it ???
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Just try it with APPEND + PARALLEL hint for insert statement and PARALLEL hint for Select with minimal Parallelism (say 4) instead of 24.
     
  3. simply_dba

    simply_dba Forum Advisor

    Messages:
    95
    Likes Received:
    5
    Trophy Points:
    140
    Location:
    Kolkata, India
    Why do you want to use rownum? what purpose will it serve you ?
     
  4. jaytav001

    jaytav001 Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Need of ROWNUM
    ==============

    Here in dTable i need ROWNUM for running number (record_id) column of dTable, which is my primary key.
    also dTable is partitioned by range using this record_id column.
    To genrate this running number i am using ROWNUM into select query of sTable.