Sequence-Index – ORACLE PL/SQL Chapter Wise Interview Questions

Sequence-Index – ORACLE PL/SQL Chapter Wise Interview Questions

Question 1:
What is a sequence?
Answer:

1. A database object that offers high-speed access to an integer value
2. Guaranteed to be unique (within that sequence).
3. Used commonly to generate Primary key values

Question 2:
How does one get the value of a sequence into a PL/SQL variable?
Answer:

select sq_$sequence.NEXTVAL into :i from dual;

Question 3:
Consider a sequence whose currval is 1 and gets incremented by 1 by using the nextval reference we get the next number 2. Suppose at this point we issue a rollback and again issue a nextval. What will the output be?
Answer:
3

Question 4:
When to create indexes?
Answer:
To be created when table is queried for less than 2% or 4% to 25% of the table rows.

Question 5:
How can you avoid indexes?
Answer:

1. To make index access path unavailable
2. Use FULL hint to optimizer for full table scan
3. Use INDEX or AND-EQUAL hint to optimizer to use one index
4. or set to indexes instead of another.
5. Use an expression in the Where Clause of the SQL.

Question 6:
Can null keys be entered in cluster index, normal index?
Answer:
Yes.

Question 7:
Can you create index on view
Answer:
No.