[AccessD] Creating new tables as opposed to updating records in an existing indexed table

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
>


More information about the AccessD mailing list