Sounds viable to me, because it is essentially the same thing as
partitioning does anyway.
Bricklen Anderson <bricklen13_at_hotmail.com> wrote in message
news:b416ca2d.0202210805.4e91ca71_at_posting.google.com...
> I should probably clarify why I asked the question...
> My employer is, shall I say... cheap, and the chances of getting EE as
> well as the partitioning option are prettttty slim. I have used the
> EE + P.O. in my test database (as a trial), and the benefits were
> numerous. Most of the uses of PO are not necessarily crucial to our
> environment however, so I'm always looking for ways to improve
> performance. A thought of mine was to create tables based on the id
> of each client (about a 100 clients), for quicker response time when
> generating reports etc. We currently have about 60 million rows in
> our main table, which isn't huge, but within the next few months that
> is expected to increase to about 500M rows.
>
> just wonderin' if any of this sounds viable.
>
> Cheers,
>
> Brick
>
> ;-)
>
> "Keith Boulton" <kboulton_at_ntlworld.com> wrote in message
news:<No2d8.1990$hM6.129212_at_news6-win.server.ntlworld.com>...>
> > I wouldn't claim to be especially knowledgable (and I'm not sure about
my
> > spelling either!).
> >
> > But, it seems to me to depend on what you're doing.
> >
> > The advantages of a partitioned table are
> >
> > performance, especially on table scans and especially if parallel
query
> > is used
> >
> > administration, you can create and drop partitions easily.
> >
> > In my case, I've developed an end-user query program which builds the
query
> > at run time. One of the parameters is present in >90% of queries and was
the
> > partition column. It's a trivial matter for me to create N tables (just
like
> > I had N partitions) and modify the code to point at a given table based
on
> > this key. I can easily build a union all view for queries against all
> > partitions.
> >
> > I also need to modify the data load code to load data into the N tables.
> >
> > This will all take me less than 1 day.
> >
> > Compared with using Oracle partitioning, I lose the ability to do some
admin
> > activities without interruption of service which is not a big issue
because
> > the database is small enough so that I actually have two copies of the
> > schema for the current run of the data update job and the last run. This
> > means I can run the update jobs without worrying about e.g. consistency
> > between different tables.
> >
> > I also don't get a performance advantage where I could have used more
than
> > one partition of the partitioned table.
> >
> > I'm not saying that there would not be times when partitioning would be
> > useful or even essential, I'm merely questioning the costs and benefits.
Received on Thu Feb 21 2002 - 14:15:45 CST
