William Benson
vbacreations at gmail.com
Mon Mar 12 23:13:34 CDT 2012
Thank you! On Mar 12, 2012 6:15 PM, "Gary Kjos" <garykjos at gmail.com> wrote: > 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 > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com >