Oracle performance after removing duplicate records

Question

I have a table in Oracle 11g R2 that contains approximately 90,000 spatial (geographic) records. Hundreds of the records are duplicated due to bad practice of the users.

Is there anyway to measure the performance of the database/table before and after removing the duplicates?

Answer 1

• i, create a temp table from the source table(with the indexes of course)
• ii, after it delete the duplicated rows from the temp table (or the source, its egal)
• iii, see the explain plans both of these tables and you will get the answer

Answer 2

A table with 90000 records is quite little table. Hundreds of duplicates is less then 1% - it is also quite little amount of a "garbage". This amount can't make big performance problems (if your application have a good design). I don't think that you can create tests that shows any significant difference in performance between "before" and "after".

Also you can delete duplicates and then create unique constraint to prevent such situation in future.

Answer 3

One way to measure global performance of an Oracle database is via the facilities of the Grid Control (aka Enterprise Manager) that shows a number of measurements (CPU, IOs, memory, etc).

Another way is to run some typical queries in sqlplus (with SET TIMING ON) and compare their response times before the removal and after the removal. That is assuming that by "performance" you mean the elapsed time for those queries.

Like Dmitry said 90,000 rows is a very small table, with a tiny fraction of duplicate rows. The presence or absence of those duplicates is unlikely to make any noticeable difference.