Home » RDBMS Server » Performance Tuning » Max and delete statment talking lot of time (oracle 9i,9.2.0.8,unix)
Max and delete statment talking lot of time [message #503457] Fri, 15 April 2011 17:22 Go to next message
vickey_sagar1
Messages: 77
Registered: August 2007
Location: pune
Member

Hi ALl,

Test1 table have around 385772300 rows. below delete and select statment talking lot of time.

Select stament taking more than 1 hrs.

SELECT  TO_NUMBER(MAX(f.T3))
           --INTO    v_FISCAL_MONTH_ID
           FROM    Test1 f;


delete statment taking more than 2 hours

DELETE FROM TEST1 WHERE TRUNC(T10) < TRUNC(ADD_MONTHS(SYSDATE,-36));

CREATE TABLE Test1
(
   T1    NUMBER NOT NULL,
   T2    VARCHAR2 (7 BYTE) NOT NULL,
   T3    VARCHAR2 (6 BYTE) NOT NULL,
   T4    NUMBER NOT NULL,
   T5    NUMBER (15, 2),
   T6    NUMBER (15, 2),
   T7    VARCHAR2 (20 BYTE) NOT NULL,
   T8    VARCHAR2 (20 BYTE),
   T9    DATE NOT NULL,
   T10   DATE,
   T11   NUMBER (15, 2)
)

CREATE UNIQUE INDEX TEST1_PK ON TEST1
(T1, T2, T3);

ALTER TABLE TEST1 ADD (
  CONSTRAINT TEST1_PK
 PRIMARY KEY
 (T1, T2, T3)
   );


Can you please advice something on this.

Thanks,
Sagar



CM: rearranged code tags

[Updated on: Sat, 16 April 2011 04:00] by Moderator

Report message to a moderator

Re: Max and delete statment talking lot of time [message #503460 is a reply to message #503457] Fri, 15 April 2011 17:30 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
SELECT COUNT(*) from TEST1 WHERE TRUNC(T10) < TRUNC(ADD_MONTHS(SYSDATE,-36));
post results from SQL above;

post FORMATTED EXPLAIN PLAN
Re: Max and delete statment talking lot of time [message #503469 is a reply to message #503457] Sat, 16 April 2011 01:10 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
How many rows "TRUNC(T10) < TRUNC(ADD_MONTHS(SYSDATE,-36))" and how many rows "TRUNC(T10) >= TRUNC(ADD_MONTHS(SYSDATE,-36))"?

Regards
Michel
Re: Max and delete statment talking lot of time [message #503595 is a reply to message #503457] Mon, 18 April 2011 03:51 Go to previous messageGo to next message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
Partition?

HTh
Re: Max and delete statment talking lot of time [message #503602 is a reply to message #503595] Mon, 18 April 2011 04:21 Go to previous message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Too late the table and data already exist. Wink

Regards
Michel
Previous Topic: Oracle : Client version < Database version.
Next Topic: High CPU Utilization in RAC and Log file sequential Read Event
Goto Forum:
  


Current Time: Sun Nov 24 21:02:47 CST 2024