After loading these files into the staging area of the data warehouse, the files were moved to an archive directory on the file system. One of the developers fortunately it was not me started a schema cleanup script on the wrong database and dropped all fact tables in the production database instead of the test environment. So, the DBA had to restore the last full backup, and then we had to reload all data since the last backup from the archived flat files.
The accident happened on a Thursday evening, the whole night and Friday was used to restore the database from the backup tapes of Tuesday. On Saturday morning we were able to start reloading the missing data Tuesday to Saturday , and on Monday morning, the system was online again.
So, we were lucky that the system was offline only one working day Friday. But even when it is possible to reload the data from files or any other sources, there is still another restriction with NOLOGGING; When Oracle Data Guard is in place, the redo log information is used to apply all data changes from the primary database to the standby database. Details about this scenario are described in the Oracle Data Guard Concepts and Administration manual.
When I read the sparse information in the Oracle Although it is possible to apply the non-logged blocks on a physical standby database, a lot of details have to be considered to avoid bad surprises in case of a media failure or a switchover. So, if you plan to use this feature in your data warehouse, it is important to evaluate the pros and cons of this configuration.
You are commenting using your WordPress. You are commenting using your Google account. You are commenting using your Twitter account. That's why delete is mostly the heaviest operation for the database. It needs to log all the row data. Husqvik - I agree that a table of 3,5bn rows ought to be partitioned on date. However, Partitioning is a chargeable extra to the Enterprise Edition license, and hence not cheap. You should not make the recommendation without mentioning the licensing issue.
APC - Ok, thanks for mentioning that Show 3 more comments. Active Oldest Votes. Improve this answer. Florin Ghita Florin Ghita Add a comment. Sign up or log in Sign up using Google. Sign up using Facebook. Sign up using Email and Password. Post as a guest Name. Email Required, but never shown. Maybe gig? Oh dear, thats really too good. Let me reitterate my case: My table schema is something like this: SubscriberVsQtyTable: int client-id int year int month int quantity primary key - client-id, year,month This table has 5M client ids.
Now every day, I have update the quantity field with the txns made for that day. Note that, I have to update one subscriber at a time since the number of txns made by each subscriber if different.
So, even now do you believe you can do this in couple of minutes? My app takes about 30 to 50 mins even on higher machines like 8 CPU. We are using OCI. If you do that work in a single update, no, I don't see it taking more then a couple of minutes. It is my understanding that the time for update depends on the following factors. There may be something else also involved. The where cluase is used to select the rows to be updated. Percentage rows updated in a block.
The more the better. So just the number of records is not that matters. I was answering their direct question -- they update every row, on every block. The biggest factor is 2 here. Verifing if neccesary to update, before update juan carlos reyes, July 29, - am UTC. Hi Tom, I had seen several times that you can increase the performance by evaluating if you really need to update, or maybe it was only my impression.
As you are always thinking how to increase performance, tt could be a good idea in newer version of Oracle, that if Oracle verifies that the value was set to that value, not update :.
A reader, July 29, - am UTC. July 29, - pm UTC. I agree -- most things CAN be done in a single sql statement. Hi Tom, I agree you may think getting a disk is cheaper, that is only when u are considering getting up or just going to the store and getting a disk, In real life conditions specially corporates this is not as easy as it seems nor is it less costly. A Simple purchase of disk has to first be justified which is quite difficuult, i dont think i have justification as yet then be approved then be purchased by purchase dept then be installed or fixed by the server teams, server team may want to take the system down which means more dollars much more.
These are just things off my head, when we acutually inititate the purchase process , there will be definitly some more steps and processes. Coming back to my question, am i right in saying that we can't partion an existing table without copy the data to some temp location,Let me know if there is any other way. Thanks, Stu and rest for your updates.
Taking this buit more further, since I am confused : [Sri] "Note that, I have to update one subscriber at a time since the number of txns made by each subscriber if different. Followup: I agree -- most things CAN be done in a single sql statement.
How would you manage this in a single SQL? Like this there would be 5M rows to be updated. Btw, my table is indexed on clientid,year and month. July 30, - am UTC. Using numbers to hold a year and a month isn't a good practice. I fail to see how this relates to this page. It is coming from an OCI application after processing some files. August 01, - am UTC. Or, put the data where it belongs - not in files, in tables -- and process the data in the database rather then in C.
Yes, that is what we do. It takes about 60 mins to update 5M records on a 2CPU machine. Is this much expected? If I do the same in multiple threads I do not see any performance improvement i. Any idea why? August 06, - am UTC. Explicit commit! Nathan, August 07, - am UTC. Tom, Why do we commit explicitly after submitting jobs? August 09, - pm UTC. How many of us have sat there for minutes going "when is the stupid thing going to run" and until we exit sqlplus it does nothing :.
Apologies, I must withdraw my previous question I'm still pondering why the commit should be there. Sorry for the trouble. Regards Nathan. I have to take issue Your response about justification for buying disk space is outdated Tom is out till weekend.. As usual, Tom can confirm.. August 10, - pm UTC. I want to create indexes on y that have the exact storage parameter, tablespace, maybe partition, etc.
By the way, the database version is 8. August 14, - pm UTC. Suppose some records of block1 is updated first in the beginning and next after updating some other blocks, then block1 will be written twice by DBWR and LGWR.
After the first update block1 may get flushed out by LRU algorithm. If the update statement is doing a full table scan then bloacks are updated in order. But the order can be different when the is using an index for selecting the rows. August 28, - am UTC. I would never never never use your approach. Hi Tom and thank you again and again for all your work.
My problem is that I cannot simply replace update with creating a new table. The table I need to update - I'll call it 'pension table' - has about 1. I have to update "amount" fields for , , etc. This function I call a "box of cockroaches". As you alredy guessed, updates take long time - about an hour for each year. I wonder what approach would you use in a situation like this?
Thank you By the way, what is a status of your new book? August 29, - am UTC. You have no indexes to update, no constraints to validate and you generate almost no redo.
I reenable the triggers, the constraints with or without validation, as you wish. And by the way, use LMTs with uniform size extents. In other cases I do exactly the same thing you do on large tables - create new table rather than update old one and then rename tables.
Does parallel insert just imply direct? Dennis, September 08, - pm UTC. Tom or anyone , Does a parallel insert imply direct append insert? I read something in the documentation or is that forbidden territory ; awhile back about the append hint not being needed on a parallel insert because it was implied. I was wondering if that was because parallel insert was synonymous with direct insert, or did I mess up the syntax somehow? Is the insert in fact parallel, and it's just 'hidden' from us?
Thanks, Dennis. September 08, - pm UTC. In order to run in parallel DML mode, the following requirements must be met My bad was that I didn't alter session. Thanks Tom for your valuable insight. I guess I was just too afraid to read down a few more pages when I was in the documentation, with that being forbidden territory and all ; Well maybe not forbidden BULK collect records at a time to the collection array. Create seperate collection arrays to store values that are computed or obtained after searching the database.
Search this site using key words "bulk insert" or "bulk collect" or "bulk update" for more examples. Other things are 1. Improve query performance by proper indexing 2. Remove fragmentation by "alter table move" command 3. Rebuild indexes 4. Size rollback segments and rollback tablespaces Mohan. September 11, - am UTC. Kailash, September 11, - pm UTC. Can you explain which of the 3 operations will be faster. If I want to update millions of rows, 1.
Can you advise as to why the method you had suggested will be faster than 1 and 2. Can you explain why updating millions of rows is not a good idea. Is it based on the amount of redo generated and what if the columns I update are not part of the index?
Which operation will be the fastest and I request you to explain the various DML operations and the amount of redo they generate. I would appreciate if you could explain me which DML generates most redo, followed by the second followed by the 3rd which generates the least redo.
Currently we have an application that checks for a condition in a table A and obtains the unique id. Is this a good approach? If so, what would be the ideal approach?
September 11, - pm UTC. I can say yes to all three -- each is the fastest. If you have a large bulk update that is done once and hits most of the of rows -- you may be best off by doing a CTAS, drop old and rename new. Hi, The approach of updating the view instead of a table works great. But here i want to update multiple colums of the same table based on different table queries. We run oralce 9i. October 04, - am UTC.
Left as an exercise for you -- see if you cannot merge the above 4 separate query into a single query. I have a situation where I have a unique key on 3 columns c1,c2,c3 on a table T and I want to update c2 from A to B for some records that match certain criteria.
There are a total of 30 million records in the table and there will be about 1 million records in the table that match the criteria to be updated. The table is not partitioned. Your suggestion to drop the old table and to re-create a new table and re-naming it to the old table may not be possible because I am updating only a subset of the table. Is there any other method to acheive this?
Which one of the following will be a better solution? Drop index and update the rows and recreate the index b. My DBA says that this will create fragmentation and is not a better approach. Is that true? Do you suggest any better solution under these scenarios since I keep getting these type of questions from my manager often.
Your suggestion in this regard is highly appreciated. October 22, - pm UTC. Elapsed: How about rollback segment problem when updating million rows. Ramasamy, October 22, - pm UTC. Tom , Thanks lot for your service. This table has got 30 million rows and i have to update around 17 million rows each night.
I came across the rollback segment issue. It is taking around 3 hrs to update. Your input is greatly appreciated to solve the above issues. Thanking You, Ramasamy. October 23, - am UTC. Hi Tom, I have a table A and soon after creating it 1million records are put into it. Then it being in a real time environment its size goes on increasing atleast half million each day. However each day a cleanup application cleans half million every day.
After inserting 1 million at the creation of table its put in the realtime environment. When ever a new record is inserted a contuniously listening application detects it and applies a select operation on table A. Then it send the selected items to some table B and then updates the table A. The listening operation may be 3 or 4 in number.
How ever the select and update operations are taking a lot of time and the initial insertion of 1 million records is taking 11 hours to complete so what can be the possible architecture.
But still its taking a lot of time. Plz suggest some method. Thanks and regards, Sandy.. October 23, - pm UTC. Select and update of a table Having Millions of Records.
Jack, October 23, - am UTC. Hi Tom, I have the following queries which execute on a table of million records. That table has a PK but its not used in both the queries given below. If I make index on cmtime then it takes longer. Presently i dont have a index field. Can you plz tellme how i can optimize these or where the performance bottleneck is? Yes Tom. Thanks, Ramasamy. I tried to update 30 million records using Tom's solution.
It works fine. Using approximately 1. George Broadway Photo Supply Ltd. October 24, - am UTC. No jack, October 27, - am UTC. We are not using any cost based optimizers. It's a simple query which we are using to retrieve data from a table having millions of records and it's performance is not satisfactory.
How to optimize it so that it runs faster? October 27, - am UTC. I have a fact table being partitioned by month. The indexes are built local to partition. The data population is working fine. But the rebuild index fails at the end. I don't know how to rebuild indexes in the partitions. Can you help me? Dear Tom, Yes, I should provide enough information to you. Sorry for that. November 13, - am UTC. A reader, December 11, - pm UTC. Hi Tom, I am running below update statement from past 24 hr and it's still running..
It's doing full table scan of both table sinace I am using function in where clause.. We using RULE base optimizer and oracle 8. December 11, - pm UTC. A reader, December 12, - pm UTC. Please help Thanks. December 13, - am UTC. Can you tell me why the physical reads increase so much using parallel hint in the DML sql,thanks. But -- to answer your questions the cost, well the cost is affected by thousands of things.
Ignore the cost of two queries that are not the same, they are NOT comparable. That and parallel query prefers to checkpoint -- and do direct io many times you might find PQ doing a global checkpoint before the query begins to get current images onto disk in order to just all hit slam the data as fast as it can without messing with the buffer cache but for so few records -- it would seem that regular sql is what you want.
A reader, December 13, - pm UTC. Hi Tom, Followed you advice Hi Tom Iwant to update a table with one go on online system. A table has 20 lakh records and has columns. I don't know why it is taking so much time even index is created on that particular field. By defualt is unapproved. Suggest the best solution pkr. March 06, - pm UTC. You want there to be NO index on the column you are updating. March 07, - am UTC. You'll want to keep trying until you get a chance to drop it. Hi Tom, I have a query as: I have two tables Table1 with around records max and table2 actually the GL code combination master table with around , records.
I need to update three fields in table1 which is a temp processing table with a unique value from table2. When I check the table locks the table remains locked Row Xclusively. I am committing immediately after the update statement in the procedure.
Cud u pls tell me why is this happening? March 09, - pm UTC. If so, my understanding is : 1. March 15, - pm UTC. I "might", "probably" if it was most of the records Tom, In the above discussion you mention : 1. Is it correct? Is it correct. If both of the above understanding is not correct, please explain. Also, please let me know how we could track the movement of the blocks on and off the freelist. I'm using 9. Has this behavior changed in the later versions?
How could I speed it up? March 16, - am UTC. Hi I am working in 8. I want to delete 30 million rows of 60 million so I am doing this 1. I searched the doco but it seems that I can only specify using index tablespace clause or I am missing something?
March 21, - am UTC. Hi Tom, I read your response to Murali's question above and believe, there will be a downtime window for the application. This is because if I want to keep the index names same as before, I will have to create the new table, drop the old table, rename the new table to old table name and then create the required indexes on the same.
Wondering, how if we can create the indexes by some other name on the new table and rename the indexes after dropping the old table. As always your valuable advice helps a lot! March 25, - pm UTC. A reader, March 25, - pm UTC. The process is divided in 10 steps 1. Disable constraints 3. Truncate original table 4.
Insert into original table from TEMP table 6. Drop TEMP table 7. Create PK and UK 8. Enable PK and UK 9. Enable FKs Rebuild indexes I want to make this process restartble, i. How can we achiveve this? Any suggestions :-? What I see is that I will hav quite a few redundant codes That would do it yes. Hi Tom Could you provide more information about that procedure and how to drop indexes and rebuild with nologging. We are using informatica etl tool before loading bulk data into target drop the indexes pre-session and after load the data rebuilt the indexes with nologging post-session , it takes less amount of time because its generates less undo Regards Mohan.
April 07, - am UTC. Hi Tom, This update is not working properly. How to correct this? April 08, - am UTC. Hi Tom, i was just going through the discussion in this subject. In this you have described that you will create a table instead of going for updates of million records and this update will be peroformed as part of creation of new table. Don't you think that the new table will also occupy the same amount of space and after dropping the table we will have to do the reorg for that tablespace.
Hi Tom, Its very useful and I used this saved lots of time by creating new table. I got another issue similar to this. We are using 9i. Is there any option with out rebuilding table as data already exist. Can you please suggest on this. Thanks in Advance. Hi Tom, Thanks for your reply but Still it's not working properly.
You say about another way in Oracle 10G. How does that work? Could you please provide an example. Please do reply. April 09, - am UTC. Hi Tom, Thanks for your reply. I found out the way of doing it as follows and Do you have any other option to do it in a better way?
To Mr. Hi Ram and All, May I request you all not to pester Tom with inane posts requesting Tom to debug and program on our behalf. This forum is not to learn coding. Tom, we use non-intelligent primary key to update the table through a stored procedure. April 16, - am UTC. Once assigned a row would have a rowid assigned to it and that rowid would live with that row until you deleted it.
Starting in 8i with support for updates to partition keys that would cause a row to move from partition A to partition B -- that is no longer true and then there are IOT's In 10g, there are even more options for 'row movement' -- an online segment shrink for example. So, rowids can change and are changing in more circumstances as time goes on.
What I mean is -- you a select a. If that returns 0 rows, someone changed the data or reorg'ed it and moved it. You need to requery to get the current values before you let the user even think about modifying it.
If that returns a row -- you got it. If that returns ora, something has it locked, you have to decide what you want to do about that. April 19, - am UTC. But - if we inserted the same exact values and they got the same exact rowid -- then 1 would make this "safe". If the newly inserted row doesn't match column by column the values we expect, well, then we'll reject it won't lock it and all will be well. I have a very large partitioned table with DATE column, I would like to take out the time portion from the date column I did the following and it is taking lot of time and failing with unable to extend the rollback segments.
April 23, - am UTC. Hi We want to delete several tables of several million of rows. The problem is, the delete statement is quite simple but if we want to this faster we would insert to temp table, truncate original and insert back the good data. Is there a better approach? April 26, - pm UTC. Hi create table new as select rows to keep; drop table old; rename new to old; do the first in parallel, with nologging.
That is exactly I want to do, the problem is until now we have always done the other way round, use plain DELETE and it takes a week to delete everything!
For example how would you change delete tab1 where exists select null from tab2, tab3 where tab2. April 27, - am UTC. Negate the conditions for the where exists.
April 28, - pm UTC. Thank you. Therefore we would keep that row, IF that was "not" satisfied. Same logic for the second part. Updating Table having millions of records taking lot of Time.. Hi Tom, I hd a table having millions of record in which two of its cols are Null.
Nologging - how does it impact recovery? Naresh, April 30, - am UTC. Hi Tom, This is a great chain of discussion. I especially liked the "outer join to replace the not exists". I am really looking forward to my copy of your first book that I oredered recently on it's way from amazon. One question regarding making the table nologging: Does it not have implications for recovery?
What am I missing? April 30, - pm UTC. I use: LOOP 1. Bulk select rows at a time from Table A, C with rowids from C 2. Bulk insert 3. Wait Total Waited Waited db file sequential read 4 0. Wait Total Waited Waited db file sequential read 0. WHERE c. Wait Total Waited Waited db file sequential read 1. Please tell me a way to make this faster. May 15, - am UTC. If they are not in the case, well, we'll need to read them into there.
Thanks Tom, The hint in the update was there by a Developer, it has been rightly disabled. The insert is not a bottleneck so didn't look into it. This is a development machine, there are no other jobs running, asynchronous IO is enabled, the machine is on RAID 0 no fault tolerance - being a development one.
Please guide me next. May 17, - am UTC. If it is not cached, we'll have to read it. Tom, I need to update a table that has 50 million rows but the number of rows affected are only 1 million rows. I have a single update statement to do this. Can you please suggest me if there is anything better that I can do on this SQL to run it faster. Thanks Sar. May 28, - pm UTC.
0コメント