problem with oracle datablocks migrating data into table

[karteek]

Hi Everyone,

i am migrating around 30 million records into oracle table,my datastage configuration is 4 node.
i have two tables
1. Physical_item(parent table):
Physical_item_id [primary_key]

data:
physical_item_id
1
2
3
4
5


2. PM_IMAGE_SECT(child table) which contains 30 million records.
Image_sect_id [primary_key]
Physical_item_id [FK to Physical_item table]

physical_item_id image_section_id
1 1
1 2
1 3
1 4
1 5
1 6
1 7
1 8
1 9
1 10
2 11
2 12
2 13
2 14
2 15
2 16
2 17
2 18
2 19
2 20
3 21
3 22
3 23
3 24
3 25
3 26
3 27
4 28
4 29
4 30
4 31
4 32
4 33
4 34
4 35
4 36
4 37
.....
........
.........
for a given parent Physical_item there are multiple rows in child PM_IMAGE_SECT table.
there is an issue while retrieving data from child tablePM_IMAGE_SECT table still we have appropriate indexes for this table.

while migrating data into this table

Code: Select all

src[dataset]-------------Tfm------Oracle Connector stage

here i am using HASH PARTITION key: Physical_item_id(Sorting,Partitioning) Ascending
image_section_id(Sorting) Ascending



when i check the data in table :
sql> SELECT PHYSICAL_ITEM_ID, IMAGE_SECT_ID, DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) "BLOCK" FROM PM_IMAGE_SECT;

physical_item_id image_section_id BLOCK
1 1 1250635
1 2 1250635
1 3 1250635
1 4 1250635
1 5 1250635
1 6 1250635
1 7 536254
1 8 536254
1 9 536254
1 10 536254
2 11 536250
2 12 536250
2 13 536250
2 14 536250
2 15 536250
2 16 536250
2 17 536250
2 18 536250
2 19 536251
2 20 536251
3 21 1000
3 22 1000
3 23 1000
3 24 1000
3 25 1000
3 26 1000
3 27 1000
4 28 535660
4 29 535660
4 30 535660
4 31 535660
4 32 535660
4 33 535660
4 34 535660
4 35 535660
4 36 535660
4 37 535672
4 38 535672
4 39 535672
4 40 535672
4 41 535672
4 42 535672
4 43 535672
4 44 535672
4 45 535672
4 46 535672
4 47 535672
4 48 535672
4 49 535672


for physical_item_id 1 there are 10 image_sections , from 1 to 6 image_sections oarcle block is 1250635, comming to 7 to 10 image_section_id's it stores in different block 536254
this is causing problem these section id's not even in the previous or next block
For physical_item 2, image_section_id [11 -18 ] are in block 536250, 19,20 are in block 536251 it is acceptable.
For physical item 3, all rows are in the same block. Perfect.
For physical_item 4, image_section_id [28 -36 ] are in block 535660, image_section_id [37 -49 ] are in block 535672 this is not even in the previous or next block.

i want to migrate data in such a way that for a physical_item_id all records [oracle blocks] should store in sequential one. physical_item_id [2,3] are correct
in same manner i want to achieve this for all other records

Please advise on this.

[karteek]

Any suggestions on this issue.

[chulett]

I would suggest you not care. Have you talked to your DBA about this? You have absolutely not control over what 'block' Oracle decides to use and I honestly don't know why you would even be worrying about it.

Otherwise, could you please explain how 'this is causing problem'?