SQL (Structured Query Language) Question:
Download Questions PDF

You want to determine the location of identical rows in a table before attempting to place a unique index on the table, how can this be done?

Answer:

Oracle tables always have one guaranteed unique column, the rowid column. If you use a min/max function against your rowid and then select against the proposed primary key you can squeeze out the rowids of the duplicate rows pretty quick. For example:
select rowid from emp e
where e.rowid > (select min(x.rowid)
from emp x
where x.emp_no = e.emp_no);
In the situation where multiple columns make up the proposed key, they must all be used in the where clause.

Download SQL Interview Questions And Answers PDF

Previous QuestionNext Question
What special Oracle feature allows you to specify how the cost based system treats a SQL statement?What is a Cartesian product?