Oracle Interview Question and Answers

6. What is the difference between clustered and a non-clustered index?
  A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.
A Nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk.
7. What is a Tablespace?
  A database is divided into Logical Storage Unit called tablespace. A tablespace is used to grouped related logical structures together.
8. Why use materialized view instead of a table?
  Materialized views are basically used to increase query performance since it contains results of a query. They should be used for reporting instead of a table for a faster execution.
9. What does ROLLBACK do?
  ROLLBACK retracts any of the changes resulting from the SQL statements in the transaction.
10. Compare and contrast TRUNCATE and DELETE for a table?
  Both the truncate and delete command have the desired outcome of getting rid of all the rows in a table. The difference between the two is that the truncate command is a DDL operation and just moves the high water mark and produces a now rollback. The delete command, on the other hand, is a DML operation, which will produce a rollback and thus take longer to complete.
Page 2 of 12 1 23456789101112