Using sequences with mutiple partitions

From: Alex <dead.man.walking_at_gmx.de>
Date: Tue, 22 Apr 2008 00:50:03 -0700 (PDT)
Message-ID: <179294e3-9c4f-498d-9242-5a0bea77b19b@t54g2000hsg.googlegroups.com>

Hello NG,

I'm stuck with the following problem and need the help of someone with more oracle-exp. than me (which isn't that difficult)

Having a stored procedure which creates a multi-partitioned table using an "AS SELECT" statement:

CREATE TABLE TMP_STG_IMPRESSION
TABLESPACE TS_AXNN_DATA
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
PARTITION BY HASH (hour_id)
PARTITIONS 16
STORE IN (TS_AXNN_DATA, TS_AXNN_DATA, TS_AXNN_DATA, TS_AXNN_DATA,

TS_AXNN_DATA, TS_AXNN_DATA, TS_AXNN_DATA, TS_AXNN_DATA, TS_AXNN_DATA,
TS_AXNN_DATA, TS_AXNN_DATA, TS_AXNN_DATA, TS_AXNN_DATA, TS_AXNN_DATA,
TS_AXNN_DATA, TS_AXNN_DATA)

NOLOGGING
NOCOMPRESS
NOCACHE
PARALLEL (DEGREE 4)
AS
SELECT
seq_AXNN_imp.nextval pk,
a.*
FROM
(SELECT ... FROM ... GROUP BY ...) a

The function of this statement is the building of a partitioned staging-table to optimize (speed & space) further processing of the data.

When executing this SP, the I cannot get any sequence-numbers. They simply won't appear. Even though the sequences' counter is duly raised.
All I got is the data from a.*

When dropping the PARTITION-clauses from the table-def, everything works fine
When executing only the "SELECT seq_xenion_imp.nextval pk, a.* FROM
(SELECT ... FROM ... GROUP BY ...) a" everything works fine: I get all
the data from a + the values for "pk"

Sadly, I'm in need of the partitioning to prevent "monster-joins" which are know to flood all of the TEMP-tablespace.

Does anyone know the reason for this behaviour? Does anyone know a fix?

DB: Oracle 10g
Seq.-def.:
CREATE SEQUENCE AXNN.SEQ_AXNN_IMP
  START WITH 2

  MAXVALUE 999999999999999999999999999

  MINVALUE 1
  CYCLE
  CACHE 100000
  NOORDER; Any help will be appreciated!
TIA
Alex Sauer Received on Tue Apr 22 2008 - 02:50:03 CDT