| Moving Data from one table to other base on records size. [message #620891] |
Wed, 06 August 2014 20:35  |
 |
Yuvraaj
Messages: 122
Registered: January 2011
Location: California, USA
|
Senior Member |
|
|
Hello,
I have a huge tables(200GB) say TestYA and another table TestYB with same structure with less size(10GB), I need to move say 100GB(may vary)from Table TestYA to TestYB.
SQL> SQL> Create Table TestYA ( EmpID Number, DeptID Number );
Table created.
SQL> Create Table TestYB ( EmpID Number, DeptID Number );
Table created.
SQL> BEGIN
FOR I IN 1..25 LOOP
INSERT INTO TESTYA VALUES (I, 20);
END LOOP;
END; 2 3 4 5 6 7 8 9
10 /
PL/SQL procedure successfully completed.
SQL>
SQL> BEGIN
FOR I IN 1..25 LOOP
INSERT INTO TESTYA VALUES (I*25, 40);
END LOOP;
END; 2 3 4 5 6 7 8 9
10 /
PL/SQL procedure successfully completed.
Now table TestYA have 25 Employes for Dept No. 20 and Dept No. 40.
Assume, there are 5000 departments and each depart can have n number of employes.
I need to move employes group by department which is equal or approx size to my target size(100 GB) of movement.
i.e if i use a temp table for the transaction, it should have records like below
Test_Temp
Dept Size
-----------
20 -- 40 GB
40 -- 30 GB
50 -- 20 GB
90 -- 10 GB
From above list employes from dept 20, 40, 50, 90 will be moved to TestYB table.
Note: TestYA and TestYB are Indexed Organized Tables. (For time being i created a normal table to explain the scenario).
SQL> SELECT SUM(bytes) FROM USER_SEGMENTS WHERE SEGMENT_NAME = 'TESTYA';
SUM(BYTES)
----------
1048576
SQL> SELECT COUNT(*) FROM TESTYA;
COUNT(*)
----------
50
SQL> SELECT NUM_ROWS, AVG_ROW_LEN FROM USER_TABLES WHERE TABLE_NAME='TESTYA';
NUM_ROWS AVG_ROW_LEN
---------- -----------
SQL>
I stuck with calculating the size and updating my Temp_Table.
How to calculate the record size? How to select records for the a given size.
Any help is appreciated.
Thank You.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Moving Data from one table to other base on records size. [message #621024 is a reply to message #621021] |
Thu, 07 August 2014 14:20  |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Say your table has 100 blocks, you want to move 50 blocks in 5 databases, so you have to move 10 block per database.
Choose the range of blocks for each database.
Say for one db the blocks range is from block 10 to block 20 of data file 3 and your table segment id is 1000 then use
rowid between dbms_rowid.rowid_create(1000,3,10,0) and dbms_rowid.rowid_create(1000,3,20,9999)
[Updated on: Thu, 07 August 2014 14:25] Report message to a moderator |
|
|
|
| Re: Moving Data from one table to other base on records size. [message #621026 is a reply to message #621020] |
Thu, 07 August 2014 14:22 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Yuvraaj wrote on Thu, 07 August 2014 20:00
Thanks for the logic am use the same to find the row size. But would like to know can't i use NUM_ROWS FROM USER_TABLES to get the number of records and AVG_ROW_LEN for row size?
Observed Sometimes select count(*) from table is not matching with NUM_ROWS.
Those columns in user_tables are set when stats are collected. If a lot of rows are inserted/deleted since last time stats are gathered then num_rows will be significantly different from the true count.
Count(*) is always accurate at a given point in time, as far as I'm aware user_segments is always accurate. So why not just use them for the calculation.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
