-
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 ???
-
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.
-
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.
-
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.
-
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.
-
-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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|