Home » RDBMS Server » Performance Tuning » Oracle partition doesn't work for select which scans entire table (Oracle10gEE,10.2.0.1.0, RHEL4)
Oracle partition doesn't work for select which scans entire table [message #321940] Wed, 21 May 2008 20:53 Go to next message
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 151 times)
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 Go to previous messageGo to next message
BlackSwan
Messages: 25038
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?
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 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
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
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 Go to previous message
BlackSwan
Messages: 25038
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?

Previous Topic: Finding HWM
Next Topic: Performance issue
Goto Forum:
  


Current Time: Mon Dec 05 14:51:04 CST 2016

Total time taken to generate the page: 0.10361 seconds