Hello, all

I need help to get an ascending serial number (INT8)
and a primary key in sorted order into a helper table.

As long as intermediate tables are not to be fragmented,
a solution along these lines did work:

SELECT PK from source INTO TEMP helper ORDER by 1;
Insert INTO raw_helper

CREATE raw_helper table
with serial and column for the PK of source
as a RAW table to make it faster.

INSERT INTO raw_helper .... SELECT from temp table.

But as soon as the temp table is fragmented and the
RAW table has FRAGMENT BY EXPRESSION
there is no more ascending order in the PK copies, if selected
ORDER BY serial_column.

I therefore believe, that the sort order with small amounts of rows
was a side effekt, one can not relay on.

I posted this very same subject a few days ago and the
question I _should_ ask is rather:

ow to simulate the forbidden
INSERT INTO .... SELECT FROM
and ORDER BY at the same time.

All help appreciated.

dic_K

--
Richard Kofler
SOLID STATE EDV
Dienstleistungen GmbH
Vienna/Austria/Europe

Richard Kofler schrieb:
> Hello, all
>
> I need help to get an ascending serial number (INT8)
> and a primary key in sorted order into a helper table.
>
> As long as intermediate tables are not to be fragmented,
> a solution along these lines did work:
>
> SELECT PK from source INTO TEMP helper ORDER by 1;
> Insert INTO raw_helper
>
> CREATE raw_helper table
> with serial and column for the PK of source
> as a RAW table to make it faster.
>
> INSERT INTO raw_helper .... SELECT from temp table.
>
> But as soon as the temp table is fragmented and the
> RAW table has FRAGMENT BY EXPRESSION
> there is no more ascending order in the PK copies, if selected
> ORDER BY serial_column.
>
> I therefore believe, that the sort order with small amounts of rows
> was a side effekt, one can not relay on.
>
> I posted this very same subject a few days ago and the
> question I _should_ ask is rather:
>
> ow to simulate the forbidden
> INSERT INTO .... SELECT FROM
> and ORDER BY at the same time.
>
> All help appreciated.
>
> dic_K
>

just to keep this thread going:
For now I have written a stored procedure, and this
works fine.

In a FOREACH ... WITH HOLD ... INTO I select the values of the
primary keys in sorted order into a variable, using ORDER BY.

Then I insert into the helper table, which has a serial8 column
and a column of a suitable type to hold the values of the PKs.

I then can calculate aequidistant values of the serial column,
along with min and max, select the values of the column holding
the copies of the source table's PKs at these values of the
serial column, and am able to return lines of the type
need after FRAGMENT BY EXPRESSION, like so:
((x_id >= 1) AND (x_id < 10056000)) IN dbs_P096
, ((x_id >= 10056000) AND (x_id < 43133900)) IN dbs_P097
and so on.
Using that lines, I can distribute a compound index evenly
among the fragments, i.e. every fragment holds the same
number of index entries.

I have been able to verify, that there is a considerable
speed up coming along with the now parallel index scans,
whenever the result set is biggish, and all columns from
the projection list are contained in the index.

I also can see that there ist no I/O on datapages.


But I still have not found a way to do this preparation
of low and high bounds for the rule lines in one SQL-statement.

dic_k
--
Richard Kofler
SOLID STATE EDV
Dienstleistungen GmbH
Vienna/Austria/Europe