Re: Using sequences with mutiple partitions

On Apr 22, 12:50 am, Alex <dead.man.walk..._at_gmx.de> wrote:
> 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

There's a comment in the docs to the effect that a nextval used more than once in a statement will return the same value, and you can't use it in a union. I'm guessing something makes one of these so in your statement - could you post the whole thing and an explain plan? Also, your version with all the digits. Must be the CTAS is a single statement... though I would expect the same value repeated if that were the case. Any db links involved?

http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/views.htm#sthref3105

More people may give more specific help if you posted all ddl and data to recreate a simple version of the issue. Or you might even figure it out yourself and tell us, just by doing that.

jg

--
@home.com is bogus.
Just what we need, more unstructured data.
http://www.dbta.com/e-newsletters/fmbdi=5_Minute_Briefing_Data_Integration/current.html#1Oracle%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Launches%20Universal%20Online%20Archive;%20Databa