i have a table which I populate daily (approx 3mm records every day). I need to go back at the end of each month and populate one field, which I don't populate daily at all.
It is a numeric field in range 38000-45000
I want to make the update as fast as possible. I was thinking to populate the field with value 99999 every day, so when I change to to real value, oracle won't allocate new space.....
is 0 going to be the same as 99999 ? (I would rather use 0 than 99999)
if I use CHAR, i can easily reserve the space, is there something like this for numeric?
I can also set PERCENT_FREE, but it is not 100% what I'm looing for....
any better solution than 99999 ??
jiri
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
@playernovis :
You are talking about updating very large #of records in millions. You said growth rate is 3 millions records / Day , means you will have 3*30 = 90 millions / month.
Lets say you have already existing data in this table,which hold 6 months of data.Your table size( in #of rows) will 6*90 mill = 540 millions.Now at the end of 7th month you need to update 90 millions records of 7th month data in table which can contain 540+90= 630 millions.
So you may be doing some thing like ...Updating one column for 90 million rows out of 630 million rows...
1. It doesn't matter you pre-populate with some dummy # in the column,its not going to help you at all.Your update may run very slow and may never finish also in some case.
2. we have similar kind of Data warehouse table.Initially we took the similar approach of updating the table at the end of month for few columns (because daily loads don't have info for these columns).Initially the update process seems to be ok ( 3hrs to complete) ,but once the table size increased to 0.25 billion rows,it starts taking 11hrs and update never finished on 0.50 billion rows table.Every month end , we need to update three columns for at least 100 million rows.
we re-designed the process ,in a way ,so that we completely avoided the update part.Let me know,if you are interested in details on how we achieved this a simply change table design..
-Raj
I will second raj - you need some way to do massive updates. I would suggest looking a bulk binds for this - see this question:
http://www.experts-exchang
Raj,
If I keep PCTFREE=0 and keep the field with null value, UPDATE will chain the row. It makes the update slower and final queries are slower too.
does PCTFREE = 1 % the same job as pre-populating the field with dummy value? As annamalai mention, pre-populating will create bigger rollback segment.... so PCTFREE is I guess way to go.
yeah, I was thinking about BULK BIND.....
if I twist this discussion, I was even thinking to run INSERT INTO staging table, DROPPING parittion and re-INSERTING data back..... on 8 processor server, it will take 20 minutes...
jiri
@ playernovis : Sorry for long text....:)..
Yes!. I agree if the PCTFREE=0 and update a null column to value will chain the rows,which will effect the update performance , which is kind of theoretical talk.But I am talking about , despite you pre-populate and/or have enough PCTFREE , in your case ,it doesn't really help much ,because bottom-line your update may not finish or may take long time , if your table grows very large in billions.Let me share quick summary of some figures and method we tried in our shop.
HP-9000 with HP-UX11.00 : 16 cpus with 32GB ram dedicate server with Oracle 8.1.7.4 total database size ~ 3TB.
Storage system : EMC frame
- Table contain 63 columns holds ~900 million rows and size in bytes ~82 GB non-partition table
- daily load will populate 3-4 millions rows only populated 60 columns and 3 columns will not have info while doing daily loads
- 3 columns information for each row will be available after 45days from the date of new row load.
- Business requirement once the 3 columns information is provided by business,it should be available for users with-in 30 days.
- so we do update every 30 days /end of month all rows which we have info on these 3 column.
Method 1: - with Pre-populate the column (actually in my case , by default value in these 3 columns will be defaulted due to column default value defined in table definition ).So the idea of pre-populate to help avoid any chaining we already taken care.Note : All values in 3 columns are numeric.
- One of 3 columns have bitmap index
- single update statement ( 80-120 million rows) with cursor resultset - rollback generate : 12 GB ; redo Generate : 6GB ( noarchivelog database) ; Drop bitmap index , update , create bitmap index
- ~9 hours to complete
- Update ( select a.col1,b.col1..... from a,b where a.col10.. ... ) set a.col1 = b.col2
Method 2 : Bulk Binding Update
- PL/SQL Bulk Binding : with LIMIT rows : 1500 rows to 1 million ( due to free memory limit on server )
- Drop bitmap index , execute pl/sql bulk update . Little
- ~ 7 hours ( Super its much better , but its not sufficient )
Method 3 : Parallel Direct Update
- Parallel Direct Update not possible in my case because the table in non-partition table
- may be will be help you ,if you table is partition table.
- I think its will give you better response time compared to Method 2
Method 4 : Don't Update , do CTAS or Insert select : This you already know Possible in most situations...
- we did tested on Full blow with CTAS and Insert select , created all 11 bitmap indexes
- with 14 parallel slaves : CTAS/insert select : finished with in ~ 9 minutes....Yes!. 9 minutes.
- But 11 bitmap indexes creation took : ~ 2hours including analyzing the table and indexes
- Over-all time : ~ 2 hrs to 2.5 hours ( Great )
Method 5 : Don't Update Nor Don't Do CTAS /Insert Select , But ... "Tune the Question Before Tuning the SQL "
- Business requirement in this case ,is 3 columns information need to be provided for users for select purpose.They ( users) never update nor delete information in this table.
- So here we did which is completely transparent to Users..
- Maintain a separate table which holds information on these 3 columns with the Primary key : this is very small in size (bytes) and hold more or less same # of rows.
- create a view Joining this separate table with original table on primary ,which get information on 3 column from small table.
- give access on this view for users.Query performance against view when compared to base is all most same ,which users can notice.
- Big advantage is the information on three column is available right-away. Its LIVE information as soon as the separate table is loaded with new information.
More details on how-to :
- Table Name : ACCOUNT_ORIGINAL with 63 columns ( Primary key : COL1+COl2 ) : Large table same as Method 1,where daily loads populate the column COL1 to COL60.
- Create new table : ACCOUNT_ADDITIONAL with columns ( primary key :COL1+COL2) and columns COL61,62,63 : This will simply hold more or less same rows ,but every small in size ( ~ 600 MB in my case)
- Create a view : ACCOUNT with 63 columns joining ACCOUNT_ORIGINAL and ACCOUNT_ADDITIONAL by Primary key.
Create or replace view ACCOUNT
as
select
a.col1,a.col2,............
b.col61,
b.col62,
b.col63
from
ACCOUNT_ORIGINAL A,
ACCOUNT_ADDITIONAL B
where
a.col1 = b.col2(+)
and
a.col2 = b.col2(+)
/
- Users will use the view ACCOUNT instead of table,so that they will get live-information for all columns including COL63.The performance of the view will be same as quering the base table and users loved it and business is happy and IT is happy ( Cut 8% over-all resource utilization on the server due to no update/ctas ).
Good Luck ,
-Raj
Business Accounts
Answer for Membership
by: bvanderveenPosted on 2004-05-13 at 15:03:34ID: 11063824
Just leave as 0 or -1, it will take up the same amount of space. Typically, updates don't take long because of space allocation. If that's a problem, create an additional extent before updating.
Numeric fields take up a defined amount of space, depending on their type.
HTH,
Bill Vanderveen