On Fri, 2008-01-11 at 02:28 +0100, Gavin Sherry wrote:
On Thu, Jan 10, 2008 at 09:30:10PM +, Simon Riggs wrote:
We cannot perform partition exclusion using this type of WHERE clause at
planning time because the CURRENT DATE function is STABLE.
We can do the exact same thing --
On Fri, Jan 11, 2008 at 08:07:18AM +, Simon Riggs wrote:
On Fri, 2008-01-11 at 02:28 +0100, Gavin Sherry wrote:
On Thu, Jan 10, 2008 at 09:30:10PM +, Simon Riggs wrote:
We cannot perform partition exclusion using this type of WHERE clause
at
planning time because the
Gavin,
I've responded to the technical questions you raise here:
On Thu, 2008-01-10 at 02:22 +0100, Gavin Sherry wrote:
After we note that a segment is read-only we can scan the segment and
record min/max values for all columns. These are then implicit
constraints, which can then be used
On Fri, 2008-01-11 at 10:25 +0100, Gavin Sherry wrote:
Of course. It's an identical situation for both. Regrettably, none of
your comments about dynamic partitioning and planning were accurate as a
result.
That's not true. We will still have planning drive the partition
selection
On Mon, 2008-01-07 at 12:53 -0800, Ron Mayer wrote:
Is my understanding right that these Segment Visibility Maps could
help this case as well?
Yes, I think so.
--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com
---(end of
I've kept a list of requests for improvement that I can share with
you;
I've always been loathe to publish a list of bad points.
I think it would help understand the proposal if you also present the
shortcomings.
When you presented the positive and negative points, the negative list
did look
On Fri, Jan 11, 2008 at 11:49:50AM +, Simon Riggs wrote:
On Fri, 2008-01-11 at 10:25 +0100, Gavin Sherry wrote:
Of course. It's an identical situation for both. Regrettably, none of
your comments about dynamic partitioning and planning were accurate as a
result.
That's not
On Fri, 2008-01-11 at 17:31 +0100, Zeugswetter Andreas ADI SD wrote:
I've kept a list of requests for improvement that I can share with
you;
I've always been loathe to publish a list of bad points.
The list of bad points doesn't refer to shortcomings in my proposal,
which I would never hide.
On Fri, 2008-01-11 at 20:03 +0100, Gavin Sherry wrote:
Okay, it's good that you want the planner to look at those. Did you
consider the point I made about the sheer amount of data the planner
would have to consider for large cases?
Sorry, thought I had somewhere in all those emails...
If you
Simon Riggs wrote:
Happy New Year, everybody.
This proposal follows on from previous thinking about partitioning,
where I've taken up Andrew Sullivan's suggestion to re-examine the
current partitioning concept of using tables as partitions. So I've come
up with an alternative concept to allow
On Thu, 2008-01-10 at 03:06 +0100, Gavin Sherry wrote:
If people with large tables like partitioning why is Oracle moving
towards automated partitioning in 11g? Automated partitioning was one of
Have you used Oracle's partitioning?
Since you ask, yep, certified on it, plus DB2, Teradata
hris Browne wrote:
_On The Other Hand_, there will be attributes that are *NOT* set in a
more-or-less chronological order, and Segment Exclusion will be pretty
useless for these attributes.
Short summary:
With the appropriate clustering, ISTM Segment Exclusion
can be useful on all columns
On Thu, Jan 10, 2008 at 07:25:00AM +, Simon Riggs wrote:
On Thu, 2008-01-10 at 03:06 +0100, Gavin Sherry wrote:
If the exclusion is executor driven, the planner cannot help but
create a seq scan plan. The planner will think you're returning 100X
rows when really you end up returning X
On Thu, Jan 10, 2008 at 04:51:04PM +, Simon Riggs wrote:
On Thu, 2008-01-10 at 03:06 +0100, Gavin Sherry wrote:
If people with large tables like partitioning why is Oracle moving
towards automated partitioning in 11g? Automated partitioning was one of
Have you used Oracle's
On Thu, 2008-01-10 at 21:43 +0100, Gavin Sherry wrote:
On Thu, Jan 10, 2008 at 07:25:00AM +, Simon Riggs wrote:
On Thu, 2008-01-10 at 03:06 +0100, Gavin Sherry wrote:
If the exclusion is executor driven, the planner cannot help but
create a seq scan plan. The planner will think you're
On Thu, 2008-01-10 at 21:49 +0100, Gavin Sherry wrote:
So, I get the message that you really want the DDL approach and agree
that you've demonstrated there are use cases that need it that you are
interested in. That's fine by me as long as we can each work on parts of
it to get it done.
On Thu, Jan 10, 2008 at 09:30:10PM +, Simon Riggs wrote:
We cannot perform partition exclusion using this type of WHERE clause at
planning time because the CURRENT DATE function is STABLE.
We can do the exact same thing -- if it's a direction people want to
take. In fact, we can
On Sat, 2008-01-05 at 16:30 -0500, Robert Treat wrote:
I'm not following this. If we can work out a scheme, I see no reason not to
allow a single table to span multiple tablespaces.
That seems to be something we might want anyway, so yes.
The difference is that, if I currently have a
On Sun, 2008-01-06 at 11:39 +0100, Markus Schiltknecht wrote:
I think this has to do with SE not being of much use for index scans.
Hmmm. I think it fits rather neatly with BitmapIndexScans. It would be
easy to apply the index condition and/or filters to see which segments
are excluded and
On Mon, 2008-01-07 at 14:20 +0100, Markus Schiltknecht wrote:
AFAIUI, Segment Exclusion combines perfectly well with
clustering.
Yes, seems like it would be possible to have a segment-aware CLUSTER, so
it was actually usable on large tables. Not planning that initially
though.
--
Simon
Simon Riggs wrote:
Hmmm. I think it fits rather neatly with BitmapIndexScans. It would be
easy to apply the index condition and/or filters to see which segments
are excluded and then turn off bits in the bitmap appropriately.
Yeah, good point.
Not fully sure about IndexScans yet. I don't
On Mon, 2008-01-07 at 12:14 +0100, Csaba Nagy wrote:
On Wed, 2008-01-02 at 17:56 +, Simon Riggs wrote:
Like it?
Very cool :-)
Thanks. As ever, a distillation of various thoughts, not all mine.
One additional thought: what about a kind of segment fill factor ?
Meaning: each segment
On Sat, 2008-01-05 at 16:42 +0100, Markus Schiltknecht wrote:
Simon Riggs wrote:
On Fri, 2008-01-04 at 22:26 +0100, Markus Schiltknecht wrote:
I'm still puzzled about how a DBA is expected to figure out which
segments to mark. Simon, are you assuming we are going to pass on
Gavin and all,
This is quite a long reply, so apologies for that.
On Wed, 2008-01-09 at 07:28 +0100, Gavin Sherry wrote:
On Wed, Jan 02, 2008 at 05:56:14PM +, Simon Riggs wrote:
This technique would be useful for any table with historical data keyed
by date or timestamp. It would also
[EMAIL PROTECTED] (Simon Riggs) writes:
I think we have an opportunity to bypass the legacy-of-thought that
Oracle has left us and implement something more usable.
This seems like a *very* good thing to me, from a couple of
perspectives.
1. I think you're right on in terms of the issue of the
Chris Browne wrote:
_On The Other Hand_, there will be attributes that are *NOT* set in a
more-or-less chronological order, and Segment Exclusion will be pretty
useless for these attributes.
Really?I was hoping that it'd be useful for any data
with long runs of the same value repeated -
On Wed, Jan 09, 2008 at 11:47:31AM -0500, Chris Browne wrote:
[EMAIL PROTECTED] (Simon Riggs) writes:
I think we have an opportunity to bypass the legacy-of-thought that
Oracle has left us and implement something more usable.
This seems like a *very* good thing to me, from a couple of
On Wed, 2008-01-09 at 20:03 +0100, Gavin Sherry wrote:
I think Simon's approach is
probably more complex from an implementation POV.
Much of the implementation is exactly the same, and I'm sure we agree on
more than 50% of how this should work already. We just need to close in
on the
Ron Mayer [EMAIL PROTECTED] writes:
Chris Browne wrote:
_On The Other Hand_, there will be attributes that are *NOT* set in a
more-or-less chronological order, and Segment Exclusion will be pretty
useless for these attributes.
Really?I was hoping that it'd be useful for any data
with
On Tue, 2008-01-08 at 02:12 +, Gregory Stark wrote:
I also don't understand how this proposal deals with the more common use case
of unloading and loading data. Normally in partitioned tables we build the
data in a side table until the data is all correct then load it as a
partition. If
On Wed, Jan 09, 2008 at 02:38:21PM -0500, Chris Browne wrote:
Ron Mayer [EMAIL PROTECTED] writes:
Or am I missing something?
Well, this can head in two directions...
1. Suppose we're not using an organize in CLUSTER order approach.
If the data is getting added in roughly by order of
On Wed, Jan 09, 2008 at 08:17:41PM +, Simon Riggs wrote:
On Wed, 2008-01-09 at 20:03 +0100, Gavin Sherry wrote:
I think Simon's approach is
probably more complex from an implementation POV.
Much of the implementation is exactly the same, and I'm sure we agree on
more than 50% of how
Hi Simon,
On Wed, Jan 02, 2008 at 05:56:14PM +, Simon Riggs wrote:
Segment Exclusion
-
After we note that a segment is read-only we can scan the segment and
record min/max values for all columns. These are then implicit
constraints, which can then be used for segment
Hi Simon,
On Wed, Jan 09, 2008 at 03:08:08PM +, Simon Riggs wrote:
Do people really like running all that DDL? There is significant
manpower cost in implementing and maintaining a partitioning scheme,
plus significant costs in getting it wrong.
Well... that's impossible for me to say.
On Thu, 2008-01-10 at 03:06 +0100, Gavin Sherry wrote:
If the exclusion is executor driven, the planner cannot help but
create a seq scan plan. The planner will think you're returning 100X
rows when really you end up returning X rows. After that, all
decisions made by the planner are totally
On Tue, Jan 08, 2008 at 01:08:52AM +0100, Markus Schiltknecht wrote:
Uh, which key are you talking about? AFAIU Simon's proposal, he suggests
maintaining min/max values for all columns of the table.
Right, but I think that's just because that approach is automatable. Only
some use cases are
On Tue, Jan 08, 2008 at 02:12:28AM +, Gregory Stark wrote:
Yes: it doesn't solve the problem I have, which is that I don't want to
have to manage a whole bunch of tables. I want one table, and I want to
be able to say, That section is closed.
That's not your problem, that's the
On Wed, Jan 02, 2008 at 05:56:14PM +, Simon Riggs wrote:
This technique would be useful for any table with historical data keyed
by date or timestamp. It would also be useful for data where a
time-of-insert component is implicit, such as many major entity tables
where the object ids are
On Wed, 2008-01-02 at 17:56 +, Simon Riggs wrote:
Like it?
Very cool :-)
One additional thought: what about a kind of segment fill factor ?
Meaning: each segment has some free space reserved for future
updates/inserts of records in the same range of it's partitioning
constraint. And when
Hi Csaba,
Csaba Nagy wrote:
One additional thought: what about a kind of segment fill factor ?
Meaning: each segment has some free space reserved for future
updates/inserts of records in the same range of it's partitioning
constraint. And when inserting/updating you put the new record into the
On Mon, 2008-01-07 at 13:59 +0100, Markus Schiltknecht wrote:
However, for tables which don't fit the use case of SE, people certainly
don't want such a fill factor to bloat their tables.
Sure, but it could be configurable and should only be enabled if the
table is marked as partitioned on
On Mon, 2008-01-07 at 14:20 +0100, Markus Schiltknecht wrote:
Why is that? AFAIUI, Segment Exclusion combines perfectly well with
clustering. Or even better, with an upcoming feature to maintain
clustered ordering. Where do you see disadvantages such an optimization
for sequential scans?
Hi,
Csaba Nagy wrote:
Sure, but it could be configurable and should only be enabled if the
table is marked as partitioned on some condition...
As I'm regarding SE as an optimization, I disagree here.. As all
optimizations, SE should conceptually be reasonably close to cost-less
when
On Sat, Jan 05, 2008 at 08:02:41PM +0100, Markus Schiltknecht wrote:
Well, management of relations is easy enough, known to the DBA and most
importantly: it already exists. Having to set up something which is
*not* tied to a relation complicates things just because it's an
additional
Hi,
Andrew Sullivan wrote:
On Sat, Jan 05, 2008 at 08:02:41PM +0100, Markus Schiltknecht wrote:
Well, management of relations is easy enough, known to the DBA and most
importantly: it already exists. Having to set up something which is
*not* tied to a relation complicates things just because
On Mon, Jan 07, 2008 at 07:16:35PM +0100, Markus Schiltknecht wrote:
Does anything speak against letting the DBA handle partitions as relations?
Yes: it doesn't solve the problem I have, which is that I don't want to have
to manage a whole bunch of tables. I want one table, and I want to be
Andrew Sullivan wrote:
On Mon, Jan 07, 2008 at 07:16:35PM +0100, Markus Schiltknecht wrote:
...the requirements: no single tuple in the segment may be
significantly out of the average bounds. Otherwise, the min/max gets
pretty useless and the segment can never be excluded.
The segment can
Hi,
Andrew Sullivan wrote:
On Mon, Jan 07, 2008 at 07:16:35PM +0100, Markus Schiltknecht wrote:
Does anything speak against letting the DBA handle partitions as relations?
Yes: it doesn't solve the problem I have, which is that I don't want to have
to manage a whole bunch of tables. I want
Andrew Sullivan [EMAIL PROTECTED] writes:
On Mon, Jan 07, 2008 at 07:16:35PM +0100, Markus Schiltknecht wrote:
Does anything speak against letting the DBA handle partitions as relations?
Yes: it doesn't solve the problem I have, which is that I don't want to have
to manage a whole bunch of
Gregory Stark wrote:
I also don't understand how this proposal deals with the more common use case
of unloading and loading data. Normally in partitioned tables we build the
data in a side table until the data is all correct then load it as a
partition. If you treat it as a lower-level object
On Jan 6, 2008 3:00 AM, Robert Treat [EMAIL PROTECTED] wrote:
On Saturday 05 January 2008 14:02, Markus Schiltknecht wrote:
To satisfy all the different requirements of partitioning with
segments
based partitioning, we'd have to allow a table to span multiple table
spaces. I'm not very
On Jan 6, 2008 11:27 AM, [EMAIL PROTECTED] wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Sun, Jan 06, 2008 at 01:12:32AM +0530, Gokulakannan Somasundaram wrote:
On Jan 5, 2008 6:15 PM, [EMAIL PROTECTED] wrote:
One thought I had back then, with partitioned tables was gee --
Hi,
Gokulakannan Somasundaram wrote:
On Jan 5, 2008 6:15 PM, [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote:
One thought I had back then, with partitioned tables was gee -- B-tree
index is already doing a partition; why do a manual partition on top of
that?.
Can you please
Hi,
Robert Treat wrote:
On Saturday 05 January 2008 14:02, Markus Schiltknecht wrote:
To satisfy all the different requirements of partitioning with segments
based partitioning, we'd have to allow a table to span multiple table
spaces. I'm not very keen on going that way.
Why?
Uh.. if a
On Jan 6, 2008 4:09 PM, Markus Schiltknecht [EMAIL PROTECTED] wrote:
Hi,
Gokulakannan Somasundaram wrote:
On Jan 5, 2008 6:15 PM, [EMAIL PROTECTED] mailto:[EMAIL PROTECTED]
wrote:
One thought I had back then, with partitioned tables was gee --
B-tree
index is already
On Sunday 06 January 2008 05:48, Markus Schiltknecht wrote:
What I'm saying is, that SE doesn't partition the segments into
different table spaces. Thus I don't consider it database partitioning
in the first place. As I currently understand it, it's:
table -- 1:1 -- table space -- 1:n --
On Fri, 2008-01-04 at 22:31 -0500, Robert Treat wrote:
Not to be negative, but istm how this feature would be managed is as
important
as the bits under the hood.
Agreed. On this part of the thread, we've been discussing an extension
to the basic proposal, which is why I have not been
Andrew Sullivan wrote:
On Fri, Jan 04, 2008 at 10:26:54PM +0100, Markus Schiltknecht wrote:
I'm still puzzled about how a DBA is expected to figure out which
segments to mark.
I think that part might be hand-wavy still. But once this facility is
there, what's to prevent the current active
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Sat, Jan 05, 2008 at 09:33:45AM +, Simon Riggs wrote:
[...]
The main proposal deliberately has few, if any, knobs and dials. That's
a point of philosophy that I've had views on previously: my normal
stance is that we need some knobs to
Hi,
[EMAIL PROTECTED] wrote:
The main proposal deliberately has few, if any, knobs and dials. That's
a point of philosophy that I've had views on previously: my normal
stance is that we need some knobs to allow the database to be tuned to
individual circumstances.
One thought I had back
Hi,
Simon Riggs wrote:
On Fri, 2008-01-04 at 22:26 +0100, Markus Schiltknecht wrote:
I'm still puzzled about how a DBA is expected to figure out which
segments to mark. Simon, are you assuming we are going to pass on
segment numbers to the DBA one day?
No Way!
Ah, I'm glad ;-)
Simon
On Saturday 05 January 2008 10:42, Markus Schiltknecht wrote:
The main proposal deliberately has few, if any, knobs and dials. That's
a point of philosophy that I've had views on previously: my normal
stance is that we need some knobs to allow the database to be tuned to
individual
Hi,
Robert Treat wrote:
Personally I cant say it complicates things, because it isn't clear how it
will be managed. :-)
Well, management of relations is easy enough, known to the DBA and most
importantly: it already exists. Having to set up something which is
*not* tied to a relation
On Jan 5, 2008 6:15 PM, [EMAIL PROTECTED] wrote:
One thought I had back then, with partitioned tables was gee -- B-tree
index is already doing a partition; why do a manual partition on top of
that?.
Can you please explain more on what you are trying to say here?
Thanks,
Gokul.
On Saturday 05 January 2008 14:02, Markus Schiltknecht wrote:
To satisfy all the different requirements of partitioning with segments
based partitioning, we'd have to allow a table to span multiple table
spaces. I'm not very keen on going that way.
Why?
Uh.. if a table
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Sun, Jan 06, 2008 at 01:12:32AM +0530, Gokulakannan Somasundaram wrote:
On Jan 5, 2008 6:15 PM, [EMAIL PROTECTED] wrote:
One thought I had back then, with partitioned tables was gee -- B-tree
index is already doing a partition; why do a
Simon Riggs wrote:
We would keep a dynamic visibility map at *segment* level, showing which
segments have all rows as 100% visible. No freespace map data would be
held at this level.
Small dumb-user question.
I take it you've considered some more flexible consecutive-run-of-blocks
unit of
On Fri, 2008-01-04 at 10:22 +, Richard Huxton wrote:
Simon Riggs wrote:
We would keep a dynamic visibility map at *segment* level, showing which
segments have all rows as 100% visible. No freespace map data would be
held at this level.
Small dumb-user question.
I take it you've
On Fri, 2008-01-04 at 13:06 +0530, Gokulakannan Somasundaram wrote:
a) This proposal would work for the kind of table organizations which
are currently partitioned and maintained based on some kind of
timestamp. Consider one of the use-case. A large Retail firm has a lot
of stores. DBA
Simon Riggs wrote:
On Fri, 2008-01-04 at 10:22 +, Richard Huxton wrote:
Simon Riggs wrote:
We would keep a dynamic visibility map at *segment* level, showing which
segments have all rows as 100% visible. No freespace map data would be
held at this level.
Small dumb-user question.
I take
On Fri, 2008-01-04 at 13:29 +0100, Markus Schiltknecht wrote:
Given that we are operating on segments here, to which the DBA has very
limited information and access, I prefer the term Segment Exclusion. I
think of that as an optimization of sequential scans on tables with the
above
Hello Simon,
Simon Riggs wrote:
I've come
up with an alternative concept to allow us to discuss the particular
merits of each. ISTM that this new proposal has considerable potential.
Hm.. interesting idea.
If we were able to keep track of which sections of a table are now
read-only then we
Hi,
Simon Riggs wrote:
- any Fact table where measurements/observations/events are accumulated
e.g.
Web Hits (any Internet events)
Call Detail Records
Sales
Security Events
Scientific Measurements
Process Control
- any Major Entity where new entities are created from a sequence
e.g.
Orders,
Hi,
Simon Riggs wrote:
The smaller the partition size the greater the overhead of managing it.
Also I've been looking at read-only tables and compression, as you may
know. My idea was that in the future we could mark segments as either
- read-only
- compressed
- able to be shipped off to
On Fri, Jan 04, 2008 at 01:29:55PM +0100, Markus Schiltknecht wrote:
Agreed. Just a minor note: I find marked read-only too strong, as it
implies an impossibility to write. I propose speaking about mostly-read
segments, or optimized for reading or similar.
I do want some segments to be
On Fri, 2008-01-04 at 13:06 -0500, Andrew Sullivan wrote:
On Fri, Jan 04, 2008 at 01:29:55PM +0100, Markus Schiltknecht wrote:
Agreed. Just a minor note: I find marked read-only too strong, as it
implies an impossibility to write. I propose speaking about mostly-read
segments, or
On Fri, Jan 04, 2008 at 10:26:54PM +0100, Markus Schiltknecht wrote:
I'm still puzzled about how a DBA is expected to figure out which
segments to mark.
I think that part might be hand-wavy still. But once this facility is
there, what's to prevent the current active segment (and the rest)
Hi,
Simon Riggs wrote:
On Fri, 2008-01-04 at 13:06 -0500, Andrew Sullivan wrote:
On Fri, Jan 04, 2008 at 01:29:55PM +0100, Markus Schiltknecht wrote:
Agreed. Just a minor note: I find marked read-only too strong, as it
implies an impossibility to write. I propose speaking about mostly-read
On Fri, 2008-01-04 at 22:26 +0100, Markus Schiltknecht wrote:
I'm still puzzled about how a DBA is expected to figure out which
segments to mark. Simon, are you assuming we are going to pass on
segment numbers to the DBA one day?
No Way!
That would stop Richard's idea to make the segment
On Friday 04 January 2008 17:01, Simon Riggs wrote:
On Fri, 2008-01-04 at 22:26 +0100, Markus Schiltknecht wrote:
I'm still puzzled about how a DBA is expected to figure out which
segments to mark. Simon, are you assuming we are going to pass on
segment numbers to the DBA one day?
No Way!
On Thu, 2008-01-03 at 00:41 +, Sam Mason wrote:
On Wed, Jan 02, 2008 at 05:56:14PM +, Simon Riggs wrote:
Like it?
Sounds good. I've only given it a quick scan though. Would read-only
segments retain the same disk-level format as is currently?
Yes, no changes at all to the
Hi Simon,
Looks like a novel idea. I just want to confirm my
understanding of the proposal.
a) This proposal would work for the kind of table organizations which are
currently partitioned and maintained based on some kind of timestamp.
Consider one of the use-case. A large Retail firm
Happy New Year, everybody.
This proposal follows on from previous thinking about partitioning,
where I've taken up Andrew Sullivan's suggestion to re-examine the
current partitioning concept of using tables as partitions. So I've come
up with an alternative concept to allow us to discuss the
On Wed, Jan 02, 2008 at 05:56:14PM +, Simon Riggs wrote:
Like it?
Sounds good. I've only given it a quick scan though. Would read-only
segments retain the same disk-level format as is currently? It seems
possible to remove the MVCC fields and hence get more tuples per page---
whether this
84 matches
Mail list logo
