Gary Kjos
garykjos at gmail.com
Mon Mar 12 17:14:01 CDT 2012
I wouldn't fear making an index as a big time issue. At least not on tables up to about 8 million which is what i deal with. A minute or two maybe. Depends on hardware I suppose. But if you have a lot of indexes I suppose it might be a different story. Most of my stuff is low frequency of use - One time, product the end result and start over again the next time. So I tend to only put indexes on things needed for the queries at hand rather than anything anyone might ever be querying on. Doing large volume of updates with several million rows to be updated if it's an indexed row being updated might make a difference to drop the index, do the update and then recreate after instead of whiping out the entire table. GK On Mon, Mar 12, 2012 at 4:55 PM, Benson, William (GE Global Research, consultant) <Benson at ge.com> wrote: > From what I remember, it took Access a while to build indexes, and it sure took a long time to build large tables, but updates happened lickety split. Been awhile since I did anything major. > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gary Kjos > Sent: Monday, March 12, 2012 4:20 PM > To: Access Developers discussion and problem solving > Subject: Re: [AccessD] Creating new tables as opposed to updating records in an existing indexed table > > There is a lot of housekeeping and logging of changes that would be happening. Appending rows to a brand new table with NOLOGGING option set and then renaming the table and re-establishing the indexes afterward could make sense. You save all the overhead of the logging and maintaining of the indexes while the updates are happening. If you are dealing with 100 million row tables in an Access database you might very well experience the same issue. > > GK > > On Mon, Mar 12, 2012 at 2:54 PM, Benson, William (GE Global Research, > consultant) <Benson at ge.com> wrote: >> Long time, no post, didn't want people to think I have stopped thinking about databases entirely. >> >> So here is a question, I was reading this thread (which related to Oracle of course, not Access) and wondering if the "truths" in this post are relative to Oracle only. >> >> It struck me as odd that the recommendation was to create a new table from a multi-million record table, with whatever updated values certain fields required - rather than update the fields directly when the table was indexed. Even though it meant duplicating the table, building all the indexes in the successor table, and sunsetting dropping the predecessor table. >> >> It just boggles my mind that this is so - and I wondered if it is regardless of platform. >> >> http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:64 >> 07993912330 >> >> >> -- >> AccessD mailing list >> AccessD at databaseadvisors.com >> http://databaseadvisors.com/mailman/listinfo/accessd >> Website: http://www.databaseadvisors.com > > > > -- > Gary Kjos > garykjos at gmail.com > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com -- Gary Kjos garykjos at gmail.com