How to improve deletes on a non partitioned table with no indexes

Subrahmanyam Arya

Dear oracle gurus,

I have created a non partitioned table with no indexes and stuffed in
about 15 million rows. Using oracle 8.1.7.4 standard. I then deleted 1
million and it took 1 solid hour. How can i reduce the time for
deletes? Since oracle default locking behavior is row level locking, i
changed to lock the table in exclusive mode before deleting. This dint
help..

Any clues,
avsrk

Jul 19 '05 #1

Subscribe Post Reply

2943

Tim Cuthbertson

One way to improve your performance would be to avoid the delete. Create a
new table as select from the original table, excluding the rows you want to
eliminate. Then, drop the original table and rename the new one to the
original name.

Tim

"Subrahmanyam Arya" <av***@mailcity.com> wrote in message
news:25*************************@posting.google.co m...

Dear oracle gurus,

I have created a non partitioned table with no indexes and stuffed in
about 15 million rows. Using oracle 8.1.7.4 standard. I then deleted 1
million and it took 1 solid hour. How can i reduce the time for
deletes? Since oracle default locking behavior is row level locking, i
changed to lock the table in exclusive mode before deleting. This dint
help..

Any clues,
avsrk

Jul 19 '05 #2

by: SM | last post by:

/* problem: Trying to get partitioned views to "prune" unneeded partitions from select statements against the partitioned view. There are 5 partitioned tables. Each with a check constraint...

Microsoft SQL Server

Partitioned view broken after moving table to new filegroup

by: Thomas R. Hummel | last post by:

I am using SQL Server 2000, SP3. I created an updatable partitioned view awhile ago and it has been running smoothly for some time. The partition is on a DATETIME column and it is partitioned by...

Microsoft SQL Server

How do you improve SQL performance over large amount of data?

by: charlies224 | last post by:

Hi, I am using SQL 2000 and has a table that contains more than 2 million rows of data (and growing). Right now, I have encountered 2 problems: 1) Sometimes, when I try to query against this...

Microsoft SQL Server

Improve query performance on large table

by: Bing Wu | last post by:

Hi Folks, I have a very large table containing 170 million rows of coordinats: CREATE TABLE "DB2ADMIN"."COORDINATE" ( "FID" INTEGER NOT NULL , "AID" INTEGER NOT NULL , "X" REAL NOT NULL ,...

DB2 Database

Need to improve my stored procedure , your comments are welcome!!

by: Metin Esat | last post by:

The following SP removes records from table A if they exist in table B. Table A - TEST.TABLE_X has 30 million records Table B - TEST.TABLE_X_DELTA has 5 million records. Appx 4 million...

DB2 Database

How to improve deletes on a non partitioned table with no indexes

by: Subrahmanyam Arya | last post by:

Dear oracle gurus, I have created a non partitioned table with no indexes and stuffed in about 15 million rows. Using oracle 8.1.7.4 standard. I then deleted 1 million and it took 1 solid hour....

Oracle Database

Partitioned table and its indexes

by: Damir | last post by:

Hello all! I created a range-partitioned table, and noticed that indexes were created as "NOT PARTITIONED" (through db2look), even though I ran the index creating commands without this parameter....

DB2 Database

Dropping indexes on a partitioned table

by: tg | last post by:

Indexes cause inserts/load to slow down. Is there anyway to drop indexes on one partition of a partioned table? From what I read this is not possible. I know Oracle has the option to disable...

DB2 Database

Partitioned Index on a non partitioned table ?

by: eeriehunk | last post by:

Hi All, Is it possible to create a partitioned index on a table which is not partitioned? If so what is such a partition called and please explain? I have done some research on partitions and index...

Oracle Database

React to native button blinking effect

by: lllomh | last post by:

Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{

Software Development

Access Europe meeting on Wed 4 Oct - The future of Access

by: isladogs | last post by:

The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...

Microsoft Access / VBA

How to find best mobile app development company in usa

by: Aliciasmith | last post by:

In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...

Mobile Development

Can faster file sharing be achieved by connecting computers simultaneously via WiFi and LAN cables

by: tracyyun | last post by:

Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...

Networking - Hardware / Configuration

Send Current Object to PDF

by: NeoPa | last post by:

Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...

Microsoft Access / VBA

Access Europe Meeting - Wed 1 Nov - Using SQL Server skills to extend Access

by: isladogs | last post by:

The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...

Microsoft Access / VBA

VBA Class Basics (Report Class)

by: NeoPa | last post by:

Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...

Microsoft Access / VBA

Access Europe Meeting - Wed 6 Dec - Streamline Your Import/Export Spec Workflow with VBA Classes

by: isladogs | last post by:

The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...

Microsoft Access / VBA

Take a Database to the Next Level

by: GKJR | last post by:

Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...

Microsoft Access / VBA

How to improve deletes on a non partitioned table with no indexes

Similar topics