• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 345
  • Last Modified:

Index not used when > operator is used

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
sakthikumar
Asked:
sakthikumar
  • 6
  • 4
  • 3
  • +1
1 Solution
 
SurranoCommented:
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
 
Wasim Akram ShaikCommented:
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
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
SurranoCommented:
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
 
SurranoCommented:
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
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
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
 
SurranoCommented:
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
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
 
SurranoCommented:
Yes, that's true. My comment on exchange partition referred to the time needed, not the licenses.
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
I know and in fact I wasn't referring to your comment ;-)
0
 
sakthikumarAuthor Commented:
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
 
sakthikumarAuthor Commented:
Is this operation is because of writing into logs.

if in that case, suppressing writing logs through hints will improve performance.
0
 
sakthikumarAuthor Commented:
if in that case, suppressing writing logs through hints will improve performance. ?
0
 
SurranoCommented:
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

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

  • 6
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now