Using sequences with mutiple partitions
in [Oracle Help]
|
Prev: comp.databases.oracle.misc charter
Next: Regular expressions: splitting with REGEXP_SUBSTR and "null"
From: Alex on 22 Apr 2008 03:50 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
From: joel garry on 22 Apr 2008 13:53 On Apr 22, 12:50 am, Alex <dead.man.walk...(a)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
From: Alex on 24 Apr 2008 04:42
On 22 Apr., 19:53, joel garry <joel-ga...(a)home.com> wrote: > On Apr 22, 12:50 am, Alex <dead.man.walk...(a)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.... > > 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_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 |