LiveLabs & Workshops

Add an index on target_table (id, create_date)
Then try this...

delete from target_table 
where create_date >= trunc(sysdate) and 
      create_date < trunc(sysdate) + 1 and 
      id in (select id from source_table) ;

This will facilitate index usage as you won't be applying a function on create_date

Hi, Uday_N
Always try to use the raw column from your table rather than some expression based on the column. As Paulzip coded it above,

where create_date >= trunc(sysdate) and 
      create_date < trunc(sysdate) + 1

is likely to be a lot more efficient than

where trunc(create_date ) = trunc(sysdate) 

because it allows the optimizer to use an index on create_date. Also, it requires fewer function calls. TRUNC (SYSDATE) only needs to be evaluated once, no matter how big the table is, but TRUNC (create_date) needs to be called for every row. If you have over 200,000 rows in the table, that could be 200,000 function calls.

Total records in the target table is 228997 and to be deleted is 108444
The table (number,number,date) will consume 1-10MB, 50% rows to be deleted. The index on the target_table is not needed.

Hi Frank,
Thanks for the suggestions . Your guidance always helped me . Will henceforth apply it.
Hi H37ju,
Thanks for your suggestion . It was kind of you to reply to me

Regards,
Uday

If you want help, you must give accurate information. When you go to the doctor, you don't tell him your back is hurting when you have a leg injury, do you?
"I need to delete a million records" vs "volume of data to be deleted is 108444". Which one should we believe? We can't believe both, obviously.
Then - you say the tables are "examples". That's bad. What is the structure of your ACTUAL tables? "Something went over my leg, for example a bicycle". When in reality it was a truck. Sorry, can't give you good answers if we have incomplete (or wrong) information.
Before you jump to any solutions, try to diagnose the problem. Find out what is slowing down your DELETE. It may be that "selecting" the rows to delete is taking a long time. You didn't tell us anything about the SOURCE table; if it is very large, checking the id's may take a very long time. An index on the id column in THAT table may help.
Much more likely, when you delete rows in a simple scenario like you have, what is taking a long time is the actual "delete" part - writing the undo segments and the redo logs. Not so much if the table structure is as you showed us, but more likely if the tables have many more columns.
Another thing to keep an eye on is whatever may be blocking / delaying your operation. Rows locked by other operations, waiting for those other operations to complete. (So, your own DELETE is not slow; it just must wait in line.) If that's the issue, then you are barking up the wrong tree. Figure out what else is going on in the database at the same time. (But, first, find out if THAT is the issue and not something else.) If you really only have to delete about 100k rows from a table with about 200k rows, then this would be the most likely cause IMO.
You also didn't tell us about the location of different things. Is this all in one database, running on one server? Or are you working over a slow network, with the two tables in different databases on different servers, etc.?
Perhaps you can also explain the difference between Query 1 and Query 2; I didn't see any.

Hi Math ,
sure . Thanks for your detail information . I will check what is on causing the issue . I am also very sorry for not able to give the details of the actual tables . I mean it as I have huge respect for the people in this group . I will look what causing the delay and will apprise in the group . Thanks a lot .

@mathguy Hi, I'm an old dinosaur from Oracle 6 days, and I sympathize with both you and the asker. The message came across to me as derisive, probably my misunderstanding and no offense intended. What I'd like to contribute is that there are numerous circumstances in which accuracy may be illegal: HIPPA and US DoD work specifically, and non-disclosure or confidentiality agreements in general. As Uday discovers redaction techniques I trust his message will improve.
@Frank Kulash In my outmoded ways I still think of the compound condition (A AND B) as requiring two passes against the result set. If that's still true, I speculate that the condition drops to one I/O pass if the condition becomes "x BETWEEN trunc(sysdate) AND trunc(sysdate) + 1". If I recall correctly, the BETWEEN conditions are also inclusive, which eliminates the extra I/O required for the GE qualifier. Food for thought.

@Dave_VZ In my outmoded ways I still think of the compound condition (A AND B) as requiring two passes against the result set.
No, there will be a single pass where each row is read once and condition A (or B - optimizer decides on predicate order) is checked first and if result is TRUE then second condition is checked. That's if we do table full scan. In case of index one or both conditions will be checked against index.
Operator BETWEEN is nothing but a "shortcut" and X BETWEEN Y AND Z results in X>= Y AND X <= Z. Just check the plan:

SQL> explain plan for select * from emp where sal between 1000 and 2000;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     6 |   228 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     6 |   228 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

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

   1 - filter("SAL"<=2000 AND "SAL">=1000)

13 rows selected.

SQL>

Now to OP's "Volume of data in target table to be deleted is 108444 . It takes a lot of time more than 2-3 hours". I see two possible causes:
Some other session(s) lock(s) to be deleted row(s) causing DELETE to wait.
Some other session(s) are modifying SOURCE_TABLE causing DELETE to spend time digging into UNDO.
OP needs to run AWR (or trace DELETE).
SY.

Actually I missed another rather simple possibility - table SOURCE_TABLE is very large and there is no index on column ID. In such case optimizer most likely will do hash join of TARGET_TABLE and SOURCE_TABLE which can take a while. OP needs to provide explain plan.
SY.

Hi, Dave_VZ
In my outmoded ways I still think of the compound condition (A AND B) as requiring two passes against the result set.
No, only one pass is required. (By the way, it's not a pass through the result set, it's a pass through the data to produce the resu;lt set.)
If that's still true, I speculate that the condition drops to one I/O pass if the condition becomes "x BETWEEN trunc(sysdate) AND trunc(sysdate) + 1". If I recall correctly, the BETWEEN conditions are also inclusive, which eliminates the extra I/O required for the GE qualifier. 
I'm not sure what you mean by " the extra I/O required for the GE qualifier". You're absolutely correct that x BETWEEN y AND z includes both y and z, so

x >= TRUNC (SYSDATE)  AND  x < TRUNC (SYSDATE) + 1

is NOT equivalent to

x   BETWEEN  TRUNC (SYSDATE)  AND  TRUNC (SYSDATE) + 1.

If x is exactly 00:00:00 tomorrow, then the former is FALSE, but the latter is TRUE.

Hi All,
Thanks for all your wonderful analysis. It was really enriching me . Thanks once again . I use the below query which was given by paul which takes time . There is no lock as well . I have used the below query .

delete from target_table 
where create_date >= trunc(sysdate) and 
      create_date < trunc(sysdate) + 1 and 
      id in (select id from source_table) ;

Plan:

-------------------------------------------------------------------------------------------------------------------------
| Id | Operation               | Name              | Rows | Bytes | Cost (%CPU)| Time   |
-------------------------------------------------------------------------------------------------------------------------
|  0 | DELETE STATEMENT            |                | 12215 |  644K|  360  (1)| 00:00:01 |
|  1 | DELETE                | TARGET_TABLE           |    |    |      |     |
|* 2 |  FILTER                |                |    |    |      |     |
|* 3 |  HASH JOIN SEMI           |                | 12215 |  644K|  360  (1)| 00:00:01 |
|  4 |   TABLE ACCESS BY INDEX ROWID BATCHED| TARGET_TABLE          | 12215 |  536K|   5  (0)| 00:00:01 |
|* 5 |   INDEX RANGE SCAN         | I_TARGET_TABLE_CREATE_DATE   |   1 |    |   4  (0)| 00:00:01 |
|  6 |   TABLE ACCESS FULL         | SOURCE_TABLE          | 109K|  960K |  354  (1)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
  2 - filter(TRUNC(SYSDATE@!)+1>TRUNC(SYSDATE@!))
  3 - access("C"."ID"="B"."ID")
  5 - access(TRUNC(INTERNAL_FUNCTION("CREATE_DT"))>=TRUNC(SYSDATE@!) AND 
       TRUNC(INTERNAL_FUNCTION("CREATE_DT"))<TRUNC(SYSDATE@!)+1)
Is there anything i should look into on what causing the performance issue in delete ? Please advise

How long is "takes time"?

Hi Paul,
It takes more than 2 hours

Are your table stats up to date?
Is there an index on source_table.id?
Are other processes updating / deleting on this table?
Is the row size large on target_table?
Try exclusively locking the target_table before the delete.

Hi Paul ,
thanks for the answers . There is no index on the source table and stats are upto date . No process are on the table . Will exclusively lock the table as per your advice . Really Thanks for the advice you have given . It was really helpful . Thanks once again

@Uday_N There is no lock as well
OK. Then statement level write consistency (a.k.a. mini-rollback) could be the cause. If DELETE runs into a row that changed since DELETE started the DELETE will rollback and start over. So lock table exclusively, as you mentioned, and test again.
SY.

Hi Solomon ,

Sure . I will try . Thanks for your valuable advise . It’s always helps me . Thanks once again