Ques: 1. What is an Index? Explain the different types of index.
Answer:
An index is a performance
enhancement method that allows faster retrieval of records from the table. An
index creates an entry for each value thus making data retrieval faster.
While creating an index, we should remember the columns which will be used to
make SQL queries and create one or more indexes on those columns.
Following are the available indexes.
a. Clustered index:
It sorts and stores the
rows of data in the table or view, based on its keys. These are the columns
included in the index definition. There can be only one clustered index per
table because sorting of data rows can be done only in one order.
b. Non-clustered index:
It contains the
non-clustered index key value and each key value entry, in turn, has a pointer
to the data row. Thus a non-clustered index contains a pointer to the physical
location of the record. Each table can have 999 non-clustered indexes.
c. Unique Index:
This indexing does not
allow the field to have duplicate values if the column is unique indexed. It
can be applied automatically when a primary key is defined.
Oracle
Fusion Applications interview Questions and Answers
Ques: 2. What
are Constraints? Explain the different Constraints available in SQL?
Answer:
These are the set of rules
that determine or restrict the type of data that can go into a table, to
maintain the accuracy and integrity of the data inside the table. Following are
the most frequent used constraints, applicable to a table:
- <NOT NULL> It restricts a
column from holding a NULL value. It does not work on a table.
- <UNIQUE> It ensures that a
field or column will only have unique values. It is applicable to both
column and table.
- <PRIMARY KEY> uniquely
identifies each record in a database table and it cannot contain NULL
values.
- <FOREIGN KEY> It is used to
relate two tables. The FOREIGN KEY constraint is also used to restrict
actions that would destroy links between tables.
- <CHECK CONSTRAINT> It is
used to restrict the value of a column between a range. It performs a
check on the values, before storing them into the database. It’s like condition
checking before saving data into a column.
- <DEFAULT> It is used to
insert a default value into a column.
Oracle Accounts Payables Interview Questions and Answers
Ques: 3. What
are Triggers? What are its benefits? Can we invoke a trigger explicitly?
Answer:
The trigger is a type of
stored program, which gets fired automatically when some event occurs. We write
a Trigger as a response to either of the following event:
- A database manipulation (DML)
statement (DELETE, INSERT, or UPDATE).
- A database definition (DDL)
statement (CREATE, ALTER, or DROP).
- A database operation (SERVERERROR,
LOGON, LOGOFF, STARTUP, or SHUTDOWN).
- SQL allows defining Trigger on the
table, view, schema, or database associated with the event.
Following are its benefits:
- Generating some derived column
values automatically.
- Enforcing referential integrity.
- Event logging and storing
information on table access.
- Auditing.
- Synchronous replication of tables.
- Imposing security authorizations.
- Preventing invalid transactions.
It is not possible to
invoke a trigger explicitly. It gets invoked automatically if an event gets
executed on the table having an association with the trigger.
Oracle ADF Interview Questions and Answers
Ques: 4. What
is the purpose of isolation levels in SQL?
Answer:
Transactions use an
isolation level that specifies the extent to which a transaction must be
isolated from any data modifications caused by other transactions. These
also help in identifying which concurrency side-effects are permissible.
Please refer the below list
for more clarity on the different type of levels.
i. Read Committed.
It ensures that SELECT
query will use committed values of the table only. If there is any active
transaction on the table in some other session, then the SELECT query will wait
for any such transactions to complete. Read Committed is the default
transaction isolation level.
ii Read Uncommitted.
There is a transaction to
update a table. But, it is not able to reach to any of these states like
complete, commit or rollback. Then these values get displayed (as Dirty Read)
in SELECT query of “Read Uncommitted” isolation transaction.
iii. Repeatable Read.
This level doesn’t
guarantee that reads are repeatable. But it does ensure that data won’t
change for the life of the transaction once.
iv. Serializable.
It is similar to Repeatable
Read level. The only difference is that it stops Phantom Read and utilizes the
range lock. If the table has an index, then it secures the records based on the
range defined in the WHERE clause (like where ID between 1 and 3). If a table
does not have an index, then it locks complete table.
v. Snapshot.
It is similar to Serializable
isolation. The difference is that Snapshot does not hold a lock on a table
during the transaction. Thus allowing the table to get modified in other
sessions. Snapshot isolation maintains versioning in Tempdb for old data. In
case any data modification happens in other sessions then existing transaction
displays the old data from Tempdb.
Oracle Access Manager Interview Questions and Answers
Ques:
5. How do we Tune the Queries?
Answer:
Queries can be tuned by
Checking the logic (table joins), by creating Indexes on objects in the where
clause, by avoiding full table scans. Finally use the trace utility to generate
the trace file, use the TK-Prof utility to generate a statistical a nalysis
about the query using which appropriate actions can be taken.
Oracle Fusion HCM Interview Questions and Answers
Ques: 6. What is the difference between BEFORE and AFTER in Database Triggers?
Answer:
BEFORE triggers, are
usually used when validation needs to take place before accepting the change.
They run before any change is made to the database. Let’s say you run a
database for a bank. You have a table accounts and a table transaction. If a
user makes a withdrawal from his account, you would want to make sure that the
user has enough credits in his account for his withdrawal. The BEFORE trigger
will allow to do that and prevent the row from being inserted in transactions
if the balance in accounts is not enough.
Oracle SCM Interview Questions and Answers
AFTER triggers, are usually used when information needs to be updated in a separate table due to a change. They run after changes have been made to the database (not necessarily committed). Let’s go back to our back example. After a successful transaction, you would want balance to be updated in the accounts table. An AFTER trigger will allow you to do exactly that.
More on Oracle:
Oracle Financials Interview questions and Answers
Oracle Cloud Interview Questions and Answers
Oracle PL/SQL Interview Questions and Answers
Oracle SQL Interview Questions and Answers
BI
Publisher Interview Questions and Answers
Oracle 10g
Interview Questions and Answers