Mmm. Max sequence number.
I'll go on the assumption that you are selecting the max for a column populated
by a sequence.
Here's one option.
1) CREATE TABLE <temp_table> AS SELECT ROWID myrowid FROM <the_active_table> WHERE <the_active_table.colum> < <max_value_for_first_query>
2) DELETE FROM <the_active_table> WHERE ROWID IN (SELECT myrowid FROM <temp_table>)
3) DROP TABLE <temp_table>
Another option:
Here's one option.
1) CREATE TABLE <temp_table> AS SELECT ROWID myrowid FROM <the_active_table> WHERE <the_active_table.colum> < <max_value_for_first_query>
2) DELETE FROM <the_active_table> WHERE ROWID IN (SELECT myrowid FROM <temp_table>)
3) DROP TABLE <temp_table>
Another option:
1) establish a maintenance window
with users
2) CREATE TABLE <temp_table> <insert DDL in the same format as the active table except indexes, triggers and contraints>
3) INSERT INTO <temp_table> SELECT * FROM <the_active_table> WHERE <the_active_table.colum> >= <max_value_for_first_query>
4) DROP TABLE <the_active_table>
5) RENAME TABLE <temp_table> to <the_active_table>
6) recreate indexes
7) recreate constraints
8) recreate triggers
9) recompile invalid objects
10) recreate the grants
Here's a slicker option that I had posted previously:
"Aponte, Tony"
<[EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
et> cc:
Sent by: Subject: RE: providing 24*7 database ---
[EMAIL PROTECTED]
om
10/22/01 09:05
AM
Please respond
to ORACLE-L
We use a modified version of your duplicate schema idea. But we don't have
the objects in different schemas. We use partitioned objects so that we
can exchange the partitions with the production tables at a scheduled time.
The voodoo is that we use a single range partition of MAXVALUE and all
indexes are LOCAL PARTITIONED. The partitioning key doesn't really matter
in this setup since we aren't using the features for its advantages, just
to be able to swap data and index segments on the fly. I've attached a
transcript showing the actual sequence but I'll give you a short
explanation first:
There are production tables/indexes that are used by the application,
whether directly or via synonyms. There is a second set of tables with a
_TEMP suffix that have duplicate structural definitions (constraints,
column names and data types, etc.) The indexes also end with a _TEMP but
are identical to the production ones. The only difference is that they are
partitioned tables/indexes. All partitioned objects have a single range
partition by a bogus column. The single partition is bounded by the
MAXVALUE keyword, so all of the data is contained in one partition.
Now you can manipulate the _TEMP tables at your convenience without
interrupting the access tot he "published" objects. Once you have
refreshed your _TEMP objects and are ready to publish the new data your
would execute a series of ALTER TABLE <tablename>_TEMP EXCHANGE PARTITION
TABLE <tablename>. That's it. No re-pointing of synonyms, revalidating of
views/stored procs./etc. The application keeps chugging along. The next
execution of SQL will use the published tables.
HTH
Tony Aponte
********************** pseudo-attachment ******************************
SQL> create table x(x1 number,x2 varchar2(50));
Table created.
SQL> create index xi1 on x(x1);
Index created.
SQL> create table y(x1 number,x2 varchar2(50))
2 partition by range (x1) (partition y values less than (maxvalue));
Table created.
SQL> create index yi1 on y(x1)
2 local (partition yi1 );
Index created.
SQL> insert into x values (1,'original data from regular table');
1 row created.
SQL> insert into y values (2,'original data from partitioned table');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from x;
X1 X2
---------- --------------------------------------------------
1 original data from regular table
SQL> select * from y;
X1 X2
---------- --------------------------------------------------
2 original data from partitioned table
SQL> alter table y exchange partition y with table x;
Table altered.
SQL> select * from x;
X1 X2
---------- --------------------------------------------------
2 original data from partitioned table
SQL> select * from y;
X1 X2
---------- --------------------------------------------------
1 original data from regular table
SQL> select * from user_indexes;
output snipped
SQL> select * from user_part_indexes;
output snipped
SQL> alter table y exchange partition y with table x;
Table altered.
SQL> select * from x;
X1 X2
---------- --------------------------------------------------
1 original data from regular table
SQL> select * from y;
X1 X2
---------- --------------------------------------------------
2 original data from partitioned table
SQL> select * from user_indexes;
output snipped
SQL> select * from user_part_indexes;
output snipped
SQL> drop table x;
Table dropped.
SQL> drop table y;
Table dropped.
SQL> spool off
-----Original Message-----
From: sonia pajerowski [mailto:[EMAIL PROTECTED]]
Sent: Thursday, February 21, 2002 10:23 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: How to make deletes faster.
I have only one cursor which selects the max sequence
number and all the records before that sequence number
are deleted.
I was just wondering if oracle 9i has a truncate like
option to delete records with nologging option.
We are in the process of partitioning but it might
take couple of weeks to implement that option
Thanks
Sonia P.
--- "Aponte, Tony" <[EMAIL PROTECTED]> wrote:
> How are you selecting the rows to be deleted? Is it
> in one cursor driving a loop with incremental
> commits or is it done via batch cycles of 10,000-row
> delete ... from ...where commit; delete ... from
> ...where commit; .............?
>
> Tony Aponte
>
> -----Original Message-----
> From: sonia pajerowski
> [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, February 21, 2002 5:13 PM
> To: Multiple recipients of list ORACLE-L
> Subject: How to make deletes faster.
>
>
> Hello All,
> I have a non-partitioned table with 20 millions
> records and growing. Every night a pl/sql stored
> procedures deletes around 1 million rows 10,000 at a
> time.Currently it is taking aroung 1 hour to delete
> 1
> million messages.
> Is there any way I can make deletes faster. I need
> good suggestions. I have already tried all the
> obvious
> init.ora parameters like make_delete_faster=true but
> they do not seem to work.:-)
>
> Thanks
> Sonia
>
>
> __________________________________________________
> Do You Yahoo!?
> Yahoo! Sports - Coverage of the 2002 Olympic Games
> http://sports.yahoo.com
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: sonia pajerowski
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
--------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
>
__________________________________________________
Do You Yahoo!?
Yahoo! Sports - Coverage of the 2002 Olympic Games
http://sports.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: sonia pajerowski
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
2) CREATE TABLE <temp_table> <insert DDL in the same format as the active table except indexes, triggers and contraints>
3) INSERT INTO <temp_table> SELECT * FROM <the_active_table> WHERE <the_active_table.colum> >= <max_value_for_first_query>
4) DROP TABLE <the_active_table>
5) RENAME TABLE <temp_table> to <the_active_table>
6) recreate indexes
7) recreate constraints
8) recreate triggers
9) recompile invalid objects
10) recreate the grants
Here's a slicker option that I had posted previously:
"Aponte, Tony"
<[EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
et> cc:
Sent by: Subject: RE: providing 24*7 database ---
[EMAIL PROTECTED]
om
10/22/01 09:05
AM
Please respond
to ORACLE-L
We use a modified version of your duplicate schema idea. But we don't have
the objects in different schemas. We use partitioned objects so that we
can exchange the partitions with the production tables at a scheduled time.
The voodoo is that we use a single range partition of MAXVALUE and all
indexes are LOCAL PARTITIONED. The partitioning key doesn't really matter
in this setup since we aren't using the features for its advantages, just
to be able to swap data and index segments on the fly. I've attached a
transcript showing the actual sequence but I'll give you a short
explanation first:
There are production tables/indexes that are used by the application,
whether directly or via synonyms. There is a second set of tables with a
_TEMP suffix that have duplicate structural definitions (constraints,
column names and data types, etc.) The indexes also end with a _TEMP but
are identical to the production ones. The only difference is that they are
partitioned tables/indexes. All partitioned objects have a single range
partition by a bogus column. The single partition is bounded by the
MAXVALUE keyword, so all of the data is contained in one partition.
Now you can manipulate the _TEMP tables at your convenience without
interrupting the access tot he "published" objects. Once you have
refreshed your _TEMP objects and are ready to publish the new data your
would execute a series of ALTER TABLE <tablename>_TEMP EXCHANGE PARTITION
TABLE <tablename>. That's it. No re-pointing of synonyms, revalidating of
views/stored procs./etc. The application keeps chugging along. The next
execution of SQL will use the published tables.
HTH
Tony Aponte
********************** pseudo-attachment ******************************
SQL> create table x(x1 number,x2 varchar2(50));
Table created.
SQL> create index xi1 on x(x1);
Index created.
SQL> create table y(x1 number,x2 varchar2(50))
2 partition by range (x1) (partition y values less than (maxvalue));
Table created.
SQL> create index yi1 on y(x1)
2 local (partition yi1 );
Index created.
SQL> insert into x values (1,'original data from regular table');
1 row created.
SQL> insert into y values (2,'original data from partitioned table');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from x;
X1 X2
---------- --------------------------------------------------
1 original data from regular table
SQL> select * from y;
X1 X2
---------- --------------------------------------------------
2 original data from partitioned table
SQL> alter table y exchange partition y with table x;
Table altered.
SQL> select * from x;
X1 X2
---------- --------------------------------------------------
2 original data from partitioned table
SQL> select * from y;
X1 X2
---------- --------------------------------------------------
1 original data from regular table
SQL> select * from user_indexes;
output snipped
SQL> select * from user_part_indexes;
output snipped
SQL> alter table y exchange partition y with table x;
Table altered.
SQL> select * from x;
X1 X2
---------- --------------------------------------------------
1 original data from regular table
SQL> select * from y;
X1 X2
---------- --------------------------------------------------
2 original data from partitioned table
SQL> select * from user_indexes;
output snipped
SQL> select * from user_part_indexes;
output snipped
SQL> drop table x;
Table dropped.
SQL> drop table y;
Table dropped.
SQL> spool off
-----Original Message-----
From: sonia pajerowski [mailto:[EMAIL PROTECTED]]
Sent: Thursday, February 21, 2002 10:23 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: How to make deletes faster.
I have only one cursor which selects the max sequence
number and all the records before that sequence number
are deleted.
I was just wondering if oracle 9i has a truncate like
option to delete records with nologging option.
We are in the process of partitioning but it might
take couple of weeks to implement that option
Thanks
Sonia P.
--- "Aponte, Tony" <[EMAIL PROTECTED]> wrote:
> How are you selecting the rows to be deleted? Is it
> in one cursor driving a loop with incremental
> commits or is it done via batch cycles of 10,000-row
> delete ... from ...where commit; delete ... from
> ...where commit; .............?
>
> Tony Aponte
>
> -----Original Message-----
> From: sonia pajerowski
> [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, February 21, 2002 5:13 PM
> To: Multiple recipients of list ORACLE-L
> Subject: How to make deletes faster.
>
>
> Hello All,
> I have a non-partitioned table with 20 millions
> records and growing. Every night a pl/sql stored
> procedures deletes around 1 million rows 10,000 at a
> time.Currently it is taking aroung 1 hour to delete
> 1
> million messages.
> Is there any way I can make deletes faster. I need
> good suggestions. I have already tried all the
> obvious
> init.ora parameters like make_delete_faster=true but
> they do not seem to work.:-)
>
> Thanks
> Sonia
>
>
> __________________________________________________
> Do You Yahoo!?
> Yahoo! Sports - Coverage of the 2002 Olympic Games
> http://sports.yahoo.com
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: sonia pajerowski
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
--------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
>
__________________________________________________
Do You Yahoo!?
Yahoo! Sports - Coverage of the 2002 Olympic Games
http://sports.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: sonia pajerowski
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
