PARALLEL query with ROWNUM
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: PARALLEL query with ROWNUM

Hybrid View

  1. #1
    Join Date
    May 2009
    Posts
    5

    Question PARALLEL query with ROWNUM

    hi friends,

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

    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

    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. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    The question would be why you would use rownum? rownum pseudocolumn would only show you the order in which the query retrieves the rows but not the actual location of the row.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    May 2009
    Posts
    5
    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.

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    The correct way of generating a surrogate PK sequence number is by using a SEQUENCE object.

    Described partitioning strategy is pretty unusual, in most cases tables get partitioned either by a range of dates or a range/list of codes of some sort -don't remember seeing too many cases of a table partitioned by a sequence range. I'm not saying it is wrong, just a little unusual.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    May 2009
    Posts
    5
    sequence
    =======
    I have already tried sequence, but it is also taking same time.


    Range partition
    ===========
    other any record_id (generated by running no) i do not have any other column by which i can create range partition.

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    -Surrogate Key

    Assuming the application is going to insert more rows in the affected table I would go with a SEQUENCE.


    - Partitioning

    Tables are partitioned for one of two reasons -if you get lucky both of them; a) to help improve performance during quering and, b) to help improve performance during purging.

    Ask yourself if your partitioning strategy matches any one of these cases.
    If your honest answers are: No/No then partitioning strategy has to be revised.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

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