SQL/MP, descending order indexes, and the FIRST KEY () clause

[Brian Ray]

Just trying to get my head around SQL partitions and advise a client on how
to CREATE one of their (partitioned) indexes. The first partition is going
to be an empty one, just for management purposes. Secondary partitions hold
the data. The index's first column is DESCending. So the FIRST KEY () clause
for the first data-holding partition should be something like this, no?:

PARTITION (
$D15.APDATA.STUFF02
CATALOG \NODE.$D01.APCAT
EXTENT(40000, 80000)
FIRST KEY (<some very large value>)
etc ...

To keep rows from "bleeding" into the empty first partition, they should
have the theoretical maximum value of the first column in that FIRST KEY ()
clause, correct? Not the run of the mill lowest value for a primary key
column in "regular old" ASCending order. That's how I'm reading the SQL/MP
Reference Manual, but it may be tricking my tiny brain into delusion.

Thanks much,

Brian Ray

[herma...@gmail.com]

Basically, the keys for the first partition must be BEFORE the lowest
values of the second partition. For example, if you expect your keys
of your data to be 1 or greater, then the partitions must be something
like this (assuming you expect only key values 1-10)

PARTITION 0 (ANCHOR): FIRST KEY (0)
PARTITION 1: FIRST KEY (1)
PARTITION 2: FIRST KEY (2)
PARTITION 3: FIRST KEY (3)
PARTITION 4: FIRST KEY (4)
PARTITION 5: FIRST KEY (5)
PARTITION 6: FIRST KEY (6)
PARTITION 7: FIRST KEY (7)
PARTITION 8: FIRST KEY (8)
PARTITION 9: FIRST KEY (9)

[Keith Dick]

herma...@gmail.com wrote:
> Basically, the keys for the first partition must be BEFORE the lowest
> values of the second partition.

> <snip>

>
> On Jul 17, 5:34 pm, "Brian Ray" <be_...@sbcglobal.net> wrote:
>

>> <snip>

>>The index's first column is DESCending.
>>

>> <snip>

Since he says the index is descending, is your answer correct?

[LorenH]

On Jul 20, 4:31 am, Keith Dick <kdic...@comcast.net> wrote:

Brian,

SQL/MP Reference Manual - Chapter P, heading "Partition Clause" (page
P-17 in the G06.28 online version).

Under the discussion of FIRST KEY parameter, it says FIRST KEY
"specifies the lowest value for the partition if the column for the
value has an ascending collating sequence; it specifies the highest
value for the partition if the column has a descending collating
sequence."

[herma...@gmail.com]

First of all, my apologies. I misread the message, and missed the
"descending". Just for record, I had a confirmation from Tandem/HP
last year about anchor partitions. I asked them how I would create an
anchor partition with a table with key values of 1-9, and another with
AA-ZZ. Their answer was "For the table with data 1-9, set the first
key of the partition you want to remain empty to 0 or a negative
number. For the table with keys AA through ZZ set the first key of the
empty partition to a character that has an ASCII value of less than
the letter A. You could use any number for the first key value, since
numbers are lower than letters on the ASCII chart, or you could use a
symbol such as #."

On Jul 20, 3:31 am, Keith Dick <kdic...@comcast.net> wrote: