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

On Apr 22, 12:50*am, Alex <dead.man.walk...@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/B...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/fm...hive;%20Databa

On 22 Apr., 19:53, joel garry <joel-ga...@home.com> wrote:
> On Apr 22, 12:50*am, Alex <dead.man.walk...@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/B...2/b14231/views....
>
> 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/fm...ata_Integr...- Zitierten Text ausblenden -
>
> - Zitierten Text anzeigen -

Thanks for your reply, but I dealt with this problem by optimizing the
statement's code and a request of more disk-space.
Because of me having to little time to evaluate this problem right
now, I had to circumvent it.
Maybe later on, I'll get back to it, but that's not likely to
happen...

So I just put down a "developer's remark" in the documentation


THX
Alex