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.
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 Question | Next Question |
What special Oracle feature allows you to specify how the cost based system treats a SQL statement? | What is a Cartesian product? |