OraFAQ Forum: Performance Tuning » Oracle partition doesn't work for select which scans entire table

Home » RDBMS Server » Performance Tuning » Oracle partition doesn't work for select which scans entire table (Oracle10gEE,10.2.0.1.0, RHEL4)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Oracle partition doesn't work for select which scans entire table [message #321940]

Wed, 21 May 2008 20:53

bbmonster
Messages: 47
Registered: June 2007
Location: UB

Member

Dear all,

I have table T5001 which is daily partitioned. I think the following query delete duplicates from T5000 after comparing T5000 with T5001's partition P20080514.

DELETE FROM t5000
   WHERE ROWID IN (SELECT a.ROWID
              FROM t5000 a, t5001 PARTITION (p20080514) b
              WHERE a.c4 = b.c4
                AND a.c5 = b.c5
                AND a.c6 = b.c6
                AND a.c15 = b.c15)

But it tooks long time and scan's every blocks of entire T5001 table. It can be seen from Oracle Enterprise Manager Console.
See the attached file.

Please give me any idea?

Thanks,

BB

Attachment: 1.JPG
(Size: 30.60KB, Downloaded 713 times)

Report message to a moderator

Re: Oracle partition doesn't work for select which scans entire table [message #321942 is a reply to message #321940]

Wed, 21 May 2008 21:03

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

http://www.orafaq.com/forum/t/84315/74940/
Which of the above suggestions have you tried & what were the results?

Why do you expects folks to be able to tune any SQL statement simply by looking at it?

How many rows are in the partition (p20080514)?
How many rows are deleted by this statement?

What type of indexes (GLOBAL/LOCAL) exist on which columns of this table?

Report message to a moderator

Re: Oracle partition doesn't work for select which scans entire table [message #321945 is a reply to message #321942]

Wed, 21 May 2008 21:17

bbmonster
Messages: 47
Registered: June 2007
Location: UB

Member

Thank you very much for quick reply,

T5001 partition P20080514 has ~3.000.000 rows.
T5001 table has local unique index, the following fields are indexed (C4, C5, C7, C15)

T5000 table has ~10.000 rows. & which has no index.

Shocked

I just found out that the delete duplicate query was wrong, C6 should be replaced by C7. Because C7 is partition key column.

DELETE FROM t5000
   WHERE ROWID IN (SELECT a.ROWID
              FROM t5000 a, t5001 PARTITION (p20080514) b
              WHERE a.c4 = b.c4
                AND a.c5 = b.c5
                AND a.c7 = b.c7
                AND a.c15 = b.c15)

Thank you again

Report message to a moderator

Re: Oracle partition doesn't work for select which scans entire table [message #321951 is a reply to message #321940]

Wed, 21 May 2008 22:39

bbmonster
Messages: 47
Registered: June 2007
Location: UB

Member

Even the query was fixed, it is still taking much time.

I also create index on T5000, but no effect.

set pagesize 0 feedback off verify off heading off echo off;
set serveroutput on;
set linesize 150;
CREATE INDEX T5000_TMP_C4C5C7 ON T5000 (C4, C5, C7) LOGGING NOPARALLEL;
commit;
DELETE T5000 WHERE ROWID IN (SELECT a.ROWID FROM T5000 a, T5001 PARTITION(P20080514) b WHERE a.C4 = b.C4 AND a.C5 = b.C5 AND a.C7 = b.C7 AND a.C15 = b.C15);
commit;
DROP INDEX T5000_TMP_C4C5C7;
commit;
INSERT /*+ APPEND NOLOGGING PARELLEL */ INTO T5001 PARTITION(P20080514) SELECT * FROM T5000;
commit;
DELETE T5000;
commit;
/

Any help greatly appreciate

Report message to a moderator

Re: Oracle partition doesn't work for select which scans entire table [message #321954 is a reply to message #321940]

Wed, 21 May 2008 22:47

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

If you do not know where time is being spent, then making trial & error changes while hoping for for performnce improvements is like:
Ready, Fire, Aim.

http://www.orafaq.com/forum/t/84315/74940/
Which of the above suggestions have you tried & what were the results?

Report message to a moderator

Previous Topic:	Finding HWM
Next Topic:	Performance issue

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jun 22 22:21:34 CDT 2024