PARALLEL query with ROWNUM

[jaytav001]

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 ???

[PAVB]

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.

[jaytav001]

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.

[PAVB]

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.

[jaytav001]

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.

[PAVB]

-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.