delete takes a long time on table hash partitioned

ewang1205
ewang1205 used Ask the Experts™
on
emp table is  PARTITION BY HASH (emp_no)  PARTITIONS 100 and the table has 100 million rows.

The following delete takes a long time.  I think it is accessing all the 100 partitions.  So how to make the delete faster in this case?  That may be the problem with HASH partition itself.  Thanks.
Delete from emp table where year =2008 and month =1
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
Will create an index on year/month make this delete faster?  Thanks.
Senior Oracle DBA
Commented:
You are deleting based on criteria that are not part of the partition key.  It would have to go through all the partitions to do the delete.

Creating a local index on the year/month should speed up the process.

Author

Commented:
johnsone: thanks for the help.  Try the index and working great so far.  Much faster now.  Will test more.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

SujithData Architect
Commented:
I would suggest you to partition the table based on your most frequent operations.

If you are interested to access/manage the data based on the date, you may better range partition by date. That will hit the selected partitions directly.

Author

Commented:
I view data by emp_no, load data by year/month.  So I partition by emp_no to speed up the view data, but I am running into the slow loading problem.  I may think to partition by year/month and subpartition by emp_no!!  What do you think?  Thanks.
SujithData Architect
Commented:
>> I may think to partition by year/month and subpartition by emp_no
May not help. As you are viewing the data bases on emp_no, you may hit many partitions just to query one single emp_no, provided you are maintaining the historical data in the table.

How much is the retention in the table? Can records with the same emp_no be present in the table for different dates?

Author

Commented:
100 million totaly for 3 years.  Year view same emp_no for different months and that is the problem.  Thanks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial