More design advice, please

Raymond Bell

More design advice, please
Just simple (and brief) this time.

I'm changing some read-only tables to be partitioned and introducing a new
partitioning key column. Sound familiar? Anyway, some of the tables have
>1 index. Question: should I add the new partitioning key column to the
front of these indexes too?

My feeling is no. It doesn't have/need it now, why add it? The
partitioning key value (which has values 1-9) will be hidden from view (pun
intended) via a view selecting all the other pre-existing columns 'where
partitioning_column in 1,2,3...9' which gives matchcols=2 when searching on
a specific value for the (now) second column in the index. But should I
bother? The primary key indexes have had this new column added because
they're the partitioning indexes and must have it. But all secondary
indexes don't need it, but could have it.

I'm gonna create the index without the new column and add it later if anyone
here can think of a reason to.


Raymond
PS. partitioning_key is char(1) with values '1' to '9'. Reasoning (behind
putting numeric values in a char field): a) aids 'readability' of unload
files (minor benefit). b) increases filter factor for any specific value of
partitioning_key. Will probably be quite important for my 'hide the
partitioning_key' view. Is this logic sound?



Sanjeev (CTS) S

Re: More design advice, please
(in response to Raymond Bell)
Raymond,
It seems you hate NPIs without Partitioning key columns or you just want to
see everything balanced. IT DEPENDS will be something the answer could be.
I think this is a good idea to add this column in every index if we have
the index without this column with less cluster ratio.
It could be very dangerous if it gives the matchcols = 0 to some of your
queries which are starting with the 2nd column of the index i.e the first
column without the partitioning ket value. Are you always able to specify
the partitioning key ranges ? If yes, then it looks a good trick.
If your view do not have any other where clause then using any one of the
index which cotains partitioning key as the one columns will definitely help
but i am sure that index will be paritioned,cluster index and that has to
exists when partitioning key is defined.

Does it make some sense ?

Regards,
Sanjeev

> -----Original Message-----
> From: Bell, Raymond W [SMTP:[login to unmask email]
> Sent: Thursday, January 11, 2001 5:36 AM
> To: [login to unmask email]
> Subject: More design advice, please
>
> Just simple (and brief) this time.
>
> I'm changing some read-only tables to be partitioned and introducing a new
> partitioning key column. Sound familiar? Anyway, some of the tables have
> >1 index. Question: should I add the new partitioning key column to the
> front of these indexes too?
>
> My feeling is no. It doesn't have/need it now, why add it? The
> partitioning key value (which has values 1-9) will be hidden from view
> (pun
> intended) via a view selecting all the other pre-existing columns 'where
> partitioning_column in 1,2,3...9' which gives matchcols=2 when searching
> on
> a specific value for the (now) second column in the index. But should I
> bother? The primary key indexes have had this new column added because
> they're the partitioning indexes and must have it. But all secondary
> indexes don't need it, but could have it.
>
> I'm gonna create the index without the new column and add it later if
> anyone
> here can think of a reason to.
>
>
> Raymond
> PS. partitioning_key is char(1) with values '1' to '9'. Reasoning
> (behind
> putting numeric values in a char field): a) aids 'readability' of unload
> files (minor benefit). b) increases filter factor for any specific value
> of
> partitioning_key. Will probably be quite important for my 'hide the
> partitioning_key' view. Is this logic sound?
>
>
>
>
>
-----------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------
This e-mail and any files transmitted with it are for the sole use
of the intended recipient(s) and may contain confidential and privileged information.
If you are not the intended recipient, please contact the sender by reply e-mail and
destroy all copies of the original message. Any unauthorised review, use, disclosure,
dissemination, forwarding, printing or copying of this email or any action taken in
reliance on this e-mail is strictly prohibited and may be unlawful.

Visit us at http://www.cognizant.com
----------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------



Edward Vassie

Re: More design advice, please
(in response to Sanjeev (CTS) S)
I dislike having the partition number coded in the data. In most cases it
adds nothing to the design and usability except the need for more disk
space. It also means that if you want/need to re-partition at some stage in
the future, you need to change the partition number in the affected row.

I would have no hesitation in deciding to increase the number of partitions
in a table in order to keep physical dataset size within a given limit - I
use about 200Mb if possible. (Finding time in the business cycle to
implement such a change is the hard part...)

There are only a few situations where coding a partition number in the data
is useful. Otherwise it is far better to form the partitioning index out of
some of your existing columns, and use the limit keys to distribute data
across the partitions.

The normal way to decide what is the best partitioning index is to look at
the queries that will run against the table. If you can identify a set of
business-critical queries that would benefit from having the data clustered
in a given sequence, then the columns involved give the prototype design for
your partitioning index. Also examine less critical queries to see if your
design can be modified to satisfy some of those, but without badly affecting
your critical queries.

The next stage is to look at unique constraints, especially the primary
index. If you can work out an elegant way to combine both your prototype
index and your primary index, then the partitioning key design issue is just
about settled. If there is no way to reconcile the two indexes, then it is
normally best to make the primary key a NPI, and to implement your prototype
index as the partitioning key.

From Edward Vassie...


-----Original Message-----
From: S, Sanjeev (CTS) [mailto:[login to unmask email]
Sent: 11 January 2001 05:32
To: [login to unmask email]
Subject: Re: More design advice, please


Raymond,
It seems you hate NPIs without Partitioning key columns or you just want to
see everything balanced. IT DEPENDS will be something the answer could be.
I think this is a good idea to add this column in every index if we have
the index without this column with less cluster ratio.
It could be very dangerous if it gives the matchcols = 0 to some of your
queries which are starting with the 2nd column of the index i.e the first
column without the partitioning ket value. Are you always able to specify
the partitioning key ranges ? If yes, then it looks a good trick.
If your view do not have any other where clause then using any one of the
index which cotains partitioning key as the one columns will definitely help
but i am sure that index will be paritioned,cluster index and that has to
exists when partitioning key is defined.

Does it make some sense ?

Regards,
Sanjeev

> -----Original Message-----
> From: Bell, Raymond W [SMTP:[login to unmask email]
> Sent: Thursday, January 11, 2001 5:36 AM
> To: [login to unmask email]
> Subject: More design advice, please
>
> Just simple (and brief) this time.
>
> I'm changing some read-only tables to be partitioned and introducing a new
> partitioning key column. Sound familiar? Anyway, some of the tables have
> >1 index. Question: should I add the new partitioning key column to the
> front of these indexes too?
>
> My feeling is no. It doesn't have/need it now, why add it? The
> partitioning key value (which has values 1-9) will be hidden from view
> (pun
> intended) via a view selecting all the other pre-existing columns 'where
> partitioning_column in 1,2,3...9' which gives matchcols=2 when searching
> on
> a specific value for the (now) second column in the index. But should I
> bother? The primary key indexes have had this new column added because
> they're the partitioning indexes and must have it. But all secondary
> indexes don't need it, but could have it.
>
> I'm gonna create the index without the new column and add it later if
> anyone
> here can think of a reason to.
>
>
> Raymond
> PS. partitioning_key is char(1) with values '1' to '9'. Reasoning
> (behind
> putting numeric values in a char field): a) aids 'readability' of unload
> files (minor benefit). b) increases filter factor for any specific value
> of
> partitioning_key. Will probably be quite important for my 'hide the
> partitioning_key' view. Is this logic sound?
>
>
>
>
>
----------------------------------------------------------------------------
-------------------------------------------------------------
----------------------------------------------------------------------------
-------------------------------------------------------------
This e-mail and any files transmitted with it are for the sole use
of the intended recipient(s) and may contain confidential and privileged
information.
If you are not the intended recipient, please contact the sender by reply
e-mail and
destroy all copies of the original message. Any unauthorised review, use,
disclosure,
dissemination, forwarding, printing or copying of this email or any action
taken in
reliance on this e-mail is strictly prohibited and may be unlawful.

Visit us at http://www.cognizant.com
----------------------------------------------------------------------------
------------------------------------------------------------
----------------------------------------------------------------------------
------------------------------------------------------------








Michael McMorrow

Re: More design advice, please
(in response to Edward Vassie)
Probably leave it out of the other indexes if the partitioning key value
has absolutely no business meaning / sql use....If the partitioning key
column has some meaning (eg. time cycle or business division) then you
might consider it on that basis for sql interested in specific
partitions....If NPI is currently on 'custno' with corresponding rows
scattered randomly across partitions, then remember that range
processing/non-matching-index-scans will be fundamentally impacted by
sticking the partitition key column at the start of the index (do you want
index on 'custno' or on 'custno within partition')....Michael.

----------
> From: Bell, Raymond W <[login to unmask email]>
> To: [login to unmask email]
> Subject: More design advice, please
> Date: Thursday, January 11, 2001 12:06 AM
>
> Just simple (and brief) this time.
>
> I'm changing some read-only tables to be partitioned and introducing a
new
> partitioning key column. Sound familiar? Anyway, some of the tables
have
> >1 index. Question: should I add the new partitioning key column to the
> front of these indexes too?
>
> My feeling is no. It doesn't have/need it now, why add it? The
> partitioning key value (which has values 1-9) will be hidden from view
(pun
> intended) via a view selecting all the other pre-existing columns 'where
> partitioning_column in 1,2,3...9' which gives matchcols=2 when searching
on
> a specific value for the (now) second column in the index. But should I
> bother? The primary key indexes have had this new column added because
> they're the partitioning indexes and must have it. But all secondary
> indexes don't need it, but could have it.
>
> I'm gonna create the index without the new column and add it later if
anyone
> here can think of a reason to.
>
>
> Raymond
> PS. partitioning_key is char(1) with values '1' to '9'. Reasoning
(behind
> putting numeric values in a char field): a) aids 'readability' of unload
> files (minor benefit). b) increases filter factor for any specific value
of
> partitioning_key. Will probably be quite important for my 'hide the
> partitioning_key' view. Is this logic sound?
>
>
>





**********************************************************************
This document is strictly confidential and is intended for use by
the addressee unless otherwise indicated.

Allied Irish Banks
**********************************************************************



Raymond Bell

Re: More design advice, please
(in response to Michael McMorrow)
Jeff, Michael Mc, etc.

Some good comments, guys. The partitioning key has no value whatsoever,
other than carving up my loads. As such, it has no business value at all.
However you both - I think - pointed out something I hadn't thought of. If
range processing - or a now 2nd IN list process - is involved I could be in
trouble. Checked around, and fortunately all existing processes look for
specific values of the now 2nd column. So I should be OK with not adding
the partitioning column to the front of my NPIs.

Thanks for the warning, though.


Raymond


> -----Original Message-----
> From: Jeff A L'Italien [SMTP:[login to unmask email]
> Sent: Friday, 12 January 2001 5:31 am
> To: [login to unmask email]
> Subject: Re: More design advice, please
>
>
> Raymond,
>
> If you are adding the partitioning key column to your table solely for the
> purpose of being able to partition I am in complete agreement with you
> that it
> really doesn't need to be contained within the NPIs. However, one thing
> that
> you might want to watch out for within your applications deals with you
> statement regarding hiding this column within a view via an "IN" list. If
> there are any applications using an "IN" list against the column which
> will
> become your second key column of your partitioning index, then what will
> happen
> following the index change is that what used to be an access type of "N"
> with
> matchcols = 1 using the former key column, will probably remain an access
> type
> of "N" but instead of performing a 2 column match, it will perform only a
> 1
> column match based on your new partitioning key column. I've seen this
> happen
> here many times in the case where you have more than one "IN" list in a
> predicate. Even though you have specified values for your significant key
> columns, when the optimizer detects the second "IN" list, it will stop the
> matching at the preceding column. Hopefully, all of the applications
> which now
> are accessing the primary index are using an equal predicate.
>
> Regards,
> Jeff L'Italien
> American Express