Home » SQL & PL/SQL » SQL & PL/SQL » Faster deletion (oracle 10g)
Faster deletion [message #398641] Fri, 17 April 2009 01:11 Go to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
I have TEST table with 266915178 rows inside it.
Table size is 40GB
Database is in noarchivelog mode.

Please find the table structure

CREATE TABLE TEST
(
  ID                 NUMBER(12)                 PRIMARY KEY,
  DB_ID        NUMBER(12)                 NOT NULL,
  TESTNO             VARCHAR2(15 BYTE),
  FIRSTNAME          VARCHAR2(255 BYTE),
  LASTNAME           VARCHAR2(255 BYTE),
  TITLE              VARCHAR2(255 BYTE),
  ADDRESS1           VARCHAR2(255 BYTE),
  EMAIL              VARCHAR2(50 BYTE),
  COMPANY            VARCHAR2(255 BYTE),
  GLOBAL_ID	     VARCHAR2(255 BYTE)
);

CREATE INDEX TEST_IDX_7 ON TEST (UPPER("EMAIL"))

CREATE INDEX TEST_IDX_1 ON TEST (FIRSTNAME)

CREATE INDEX TEST_IDX_2 ON TEST (LASTNAME)

CREATE INDEX TEST_IDX_3 ON TEST (COMPANY)

CREATE INDEX TEST_IDX_5 ON TEST (PHONE1)

CREATE UNIQUE INDEX TESTS_PK ON TEST(ID)

CREATE INDEX TEST_IDX_6 ON TEST (GLOBAL_ID)

CREATE INDEX TEST_DATABASE_FK_I ON TEST (DATABASE_ID)

CREATE INDEX TEST_IDX_4 ON TEST (EMAIL)

ALTER TABLE TEST ADD FOREIGN KEY (DB_ID) REFERENCES DBS (ID);


When i want to delete records from the table it takes so much time.
i.e when i want to delete 2.00.000 records that time it takes 38 minutes.

I use statement as
SQL> delete from TEST where de_id =      408834  ;

198012 rows deleted.

Elapsed: 00:38:42.22


How i can speed up the deletion?

Thanks,
Re: Faster deletion [message #398642 is a reply to message #398641] Fri, 17 April 2009 01:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Index de_id.

Regards
Michel
Re: Faster deletion [message #398647 is a reply to message #398641] Fri, 17 April 2009 01:20 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Actually i mispelled 1 index

CREATE INDEX TEST_DATABASE_FK_I ON TEST (DB_ID)

And statement is 


SQL> delete from TEST where db_id =      408834  ;

198012 rows deleted.

Elapsed: 00:38:42.22


Then how i can speed up the query.

Thanks,

[Updated on: Fri, 17 April 2009 01:23]

Report message to a moderator

Re: Faster deletion [message #398650 is a reply to message #398647] Fri, 17 April 2009 01:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you fake your output, which part of your posts can we trust to answer you?

Have a look at execution plan and statistics.

Regards
Michel

[Updated on: Fri, 17 April 2009 01:30]

Report message to a moderator

Re: Faster deletion [message #398658 is a reply to message #398641] Fri, 17 April 2009 01:42 Go to previous messageGo to next message
browd@whl.co.uk
Messages: 5
Registered: February 2009
Location: UK
Junior Member
Try using a bit of PL/SQL and a BULK COLLECT/FORALL combination.

It may speed things up:

DECLARE
   TYPE id_tab
   IS
	  TABLE OF pls_integr
		 INDEX BY PLS_INTEGER;

   v_ids id_tab;

   CURSOR test_cur
   IS
	  SELECT id
		FROM test
	   WHERE db_id = 408834;
   
BEGIN
   OPEN test_cur;

   FETCH test_cur BULK COLLECT INTO v_ids;

   CLOSE test_cur;

   FORALL i IN v_ids.FIRST .. v_ids.LAST
	  DELETE FROM test
			WHERE id = v_ids (i);
END;
Re: Faster deletion [message #398661 is a reply to message #398658] Fri, 17 April 2009 01:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
PL/SQL is NEVER faster than SQL when you can do it in one statement (unless you have screwed statistics).

Regards
Michel
Re: Faster deletion [message #398662 is a reply to message #398650] Fri, 17 April 2009 01:49 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Please find the Execution plan.

OPERATION	OPTIONS	OBJECT_NAME	OBJECT_TYPE	OPTIMIZER	SEARCH_COLUMNS	COST	CARDINALITY	BYTES
DELETE STATEMENT				ALL_ROWS		1,934.00	59,211.00	4,026,348.00
DELETE		TEST						
INDEX	RANGE SCAN	TEST_DATABASE_FK_I	INDEX	ANALYZED	1.00	181.00	59,211.00	4,026,348.00


[Updated on: Fri, 17 April 2009 01:52]

Report message to a moderator

Re: Faster deletion [message #398665 is a reply to message #398662] Fri, 17 April 2009 01:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How can Oracle use index on database_id when there is ONLY a condition on db_id AND database_id IS NOT PART OF THE TABLE?
You want help and you're lying... I give up.

Regards
Michel
Re: Faster deletion [message #398666 is a reply to message #398661] Fri, 17 April 2009 01:54 Go to previous messageGo to next message
browd@whl.co.uk
Messages: 5
Registered: February 2009
Location: UK
Junior Member
True... perhaps the OP could be suffering from the rollback segment slowing things down.

I remember crashing a 24-hour live database because I left a job Nullifying columns running over night. Shocked

Using a simple

UPDATE table SET a = NULL;

statement. It took forever and crashed because it filled up the ROLLBACK segment on the server.

Perhaps the op could do something like the following:

DECLARE
   TYPE id_tab
   IS
	  TABLE OF pls_integr
		 INDEX BY PLS_INTEGER;
   v_ids id_tab;

   CURSOR test_cur
   IS
	  SELECT id
		FROM test
	   WHERE db_id = 408834;

   v_count PLS_INTEGER;
BEGIN
   OPEN test_cur;

   FETCH test_cur BULK COLLECT INTO v_ids;

   CLOSE test_cur;

   v_count := 1;

   FOR i IN v_ids.FIRST .. v_ids.LAST
   LOOP
	  DELETE FROM test
			WHERE id = v_ids (i);

	  v_count := v_count + 1;

	  IF v_count > 10000
	  THEN
		 COMMIT;
		 v_count := 1;
	  END IF;
   END LOOP;
END;


I have found that using this type of approach can speed things up.
Re: Faster deletion [message #398668 is a reply to message #398665] Fri, 17 April 2009 01:56 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
I am not lying.
But for the sake of simplisity i used DB_ID in place of DATABASE_ID
Nothing else.

So assume DB_ID and DATABASE_ID same.

Thanks,
Re: Faster deletion [message #398671 is a reply to message #398668] Fri, 17 April 2009 02:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL*Plus and dbms_xplan.display, your plan is unreadable, columns are not even aligned.
But for what I can see, statistics are not up to date (or you are also faking the figures you gave).

Regards
Michel
Re: Faster deletion [message #398675 is a reply to message #398668] Fri, 17 April 2009 02:04 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
can you disable other indexes except index on db_id and run the delete statement? After delete statement is over you can use rebuild other indexes in parallel.


Re: Faster deletion [message #398730 is a reply to message #398641] Fri, 17 April 2009 05:02 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
I cannot disable the indexes because it is the production environment.

Thanks,
Re: Faster deletion [message #398732 is a reply to message #398641] Fri, 17 April 2009 05:06 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
PLAN_TABLE_OUTPUT
-------------------------------------
Plan hash value: 2635424432

----------------------------------------------------------------------------------------
| Id  | Operation         | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT  |                    | 59211 |  3931K|  1934   (1)| 00:00:24 |
|   1 |  DELETE           | TEST               |       |       |            |          |
|*  2 |   INDEX RANGE SCAN| TEST_DATABASE_FK_I | 59211 |  3931K|   181   (2)| 00:00:03 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
----------------------------------
----------------------------------

   2 - access("DB_ID"=347310)

14 rows selected.

Re: Faster deletion [message #398739 is a reply to message #398732] Fri, 17 April 2009 05:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
rows=59211
198012 rows deleted.

Once again, your statistics are not up to date.

Regards
Michel
Re: Faster deletion [message #399022 is a reply to message #398739] Mon, 20 April 2009 05:22 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Are there any tables that have FK links to this table? If so, and if the FK column on those child tables i sbnot indexed, that could slow things down.

Are there any ON-DELETE triggers on this table?
Previous Topic: Merge join cartesian
Next Topic: help in sql (merged 3)
Goto Forum:
  


Current Time: Thu Dec 08 12:37:44 CST 2016

Total time taken to generate the page: 0.10621 seconds