Sequence-Index – ORACLE PL/SQL Chapter Wise Interview Questions
What is a sequence?
- A database object that offers high-speed access to an integer value
- Guaranteed to be unique (within that sequence).
- Used commonly to generate Primary key values
How does one get the value of a sequence into a PL/SQL variable?
select sq_$sequence.NEXTVAL into :i from dual;
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?
When to create indexes?
To be created when table is queried for less than 2% or 4% to 25% of the table rows.
How can you avoid indexes?
- To make index access path unavailable
- Use FULL hint to optimizer for full table scan
- Use INDEX or AND-EQUAL hint to optimizer to use one index
- or set to indexes instead of another.
- Use an expression in the Where Clause of the SQL.
Can null keys be entered in cluster index, normal index?
Can you create index on view