Solved

Index not used when > operator is used

Posted on 2014-01-22
14
336 Views
Last Modified: 2014-02-15
This is the delete statement iam executing.

DELETE FROM et_amend_rec_log WHERE rec_time < (SYSDATE - lv_log_days);

I am having index in rec_time column. Table is having 10 million rows matching the above condition. While deleting it is taking more than 5 hours.

1. Should I create something like bulk delete by putting in forall loop and commit for every 10 thousand rows?

2. Can I do something to make index usable?
0
Comment
Question by:sakthikumar
  • 6
  • 4
  • 3
  • +1
14 Comments
 
LVL 8

Expert Comment

by:Surrano
ID: 39802418
1. Why do you think that index was not used? Did you try explain?
http://docs.oracle.com/cd/B10500_01/server.920/a96533/ex_plan.htm
Brief summary for the impatient:

@$ORACLE_HOME/RDBMS/ADMIN/UTLXPLAN.SQL 
EXPLAIN PLAN FOR
  DELETE FROM et_amend_rec_log WHERE rec_time < (SYSDATE - lv_log_days);

Open in new window


2. Did you try optimizer hint to force indexing to see if it improves the situation? e.g. if index is called myindex

EXPLAIN PLAN FOR
  DELETE /*+ index(et_amend_rec_log myindex) */ FROM et_amend_rec_log WHERE rec_time < (SYSDATE - lv_log_days);

Open in new window


3. If the number of rows to keep is significantly less than those to be deleted consider doing the inverse:

create table temp_earl as select * FROM et_amend_rec_log WHERE rec_time >= (SYSDATE - lv_log_days);
truncate table et_amend_rec_log;
insert into et_amend_rec_log select * from temp_earl;
drop table temp_earl;

Open in new window


4. If you need to do it a lot (e.g. 10M records per day and you have to keep only last N days) then create a partitioned table with N+1 partitions where the partkey rotates around the days. Deleting the oldest partition is as simple as truncate (should be no more than 1 second but there are some Oracle versions with bugs that make it last 10 seconds ;) )
0
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 39802478
Agree with the expert surrano, also to add

 check whether there is a functional index on the respective column and you might not be using it in your delete query..

check which index exists in your table column, whether its a normal index or functional index like trunc(), if thats a functional one, then you need to modify your query accordingly
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39802495
I agree with the comments above, just want to add:

1. I suppose your statistics & histograms are "good" and up-to-date?! If not, consider running dbms_stats.gather_table_stats with appropriate parameters...

2. Another solution would be to process this delete operation "bulk-wise" by adding "... and rownum <= 10000" or whatever packet size suites. Regarding log or staging tables tables, this is a quite stable way to wipe "old" and unused data/rows. But this within a dbms_job or use scheduler and let it run once every day, every hour, every 10 minutes or whatever fits (regarding perf. impact).
But even if you choose this "permanant" way of cleaning your tables, you have to do it (delete the 10mio rows) once, either way.
0
 
LVL 8

Accepted Solution

by:
Surrano earned 500 total points
ID: 39802564
But even if you choose this "permanant" way of cleaning your tables, you have to do it (delete the 10mio rows) once, either way.

Not if you do it the partitioned way:
- partition 1 shall contain all current records
- partitions 2 .. N+1 will contain records for the next N days
- at the end of the Nth day you'll truncate partition 1 and write records there on day N+1

Although, thinking about it, the partitioning would need an extra field (partkey) which means that an "alter table add column" must be executed on the current *big* table. I dunno how much time that'd take though.
0
 
LVL 8

Expert Comment

by:Surrano
ID: 39802572
PS. You don't have to use 1-day partitions. E.g. if you need to store data for a year and you are OK with cleaning up once per week you can create 53 partitions and change the actual partkey to use every 7 days. Or 12 partitions and change every month. Partkey can be the month part of rec_time. I'll try to figure out if you can use such a computed value as partkey since in that case you don't need that "alter table add column"...
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39802588
Not if you do it the partitioned way:

Partitioning the table (and perhaps the indexes) will take almost as long as performing the "initial" delete posted in his question.

Btw: What version/edition do you use?! Partitioning will not work in all of them (like SE or SE1)....
0
 
LVL 8

Expert Comment

by:Surrano
ID: 39802665
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39802946
Partitioning is only available with Oracle Enterprise Edition as a cost option (you need to buy licenses before you can use it).
from http://www.orafaq.com/wiki/Partitioning_FAQ

or

look for "Oracle Partitioning" in chapter "Feature and Option Availability Matrix" here:
http://www.oracle.com/technetwork/database/enterprise-edition/database-11g-product-family-technic-133664.pdf
0
 
LVL 8

Expert Comment

by:Surrano
ID: 39802985
Yes, that's true. My comment on exchange partition referred to the time needed, not the licenses.
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39803085
I know and in fact I wasn't referring to your comment ;-)
0
 

Author Comment

by:sakthikumar
ID: 39809637
Below is the version I am using. Should we need to get separate license.


Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE      11.2.0.3.0      Production
TNS for HPUX: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
0
 

Author Comment

by:sakthikumar
ID: 39809643
Is this operation is because of writing into logs.

if in that case, suppressing writing logs through hints will improve performance.
0
 

Author Comment

by:sakthikumar
ID: 39809925
if in that case, suppressing writing logs through hints will improve performance. ?
0
 
LVL 8

Expert Comment

by:Surrano
ID: 39811239
Do you mean the "NOLOGGING" clause of a table, index or partition? Unfortunately that won't help with delete operations:
Only the following operations support the NOLOGGING mode:

DML:  
    Direct-path INSERT (...)
    Direct Loader (SQL*Loader)

See details here:
http://docs.oracle.com/cd/B19306_01/server.102/b14200/clauses005.htm
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now