Technical Sample Questions : C |
Java | Unix |
Operating Systems |
Oracle Sample Questions ›› Oracle General Questions
Technical Sample Questions
Oracle Sample Questions : Oracle General Questions
« Previous || Next »
Oracle General Sample Question Number: 1-10|11-20|21-30|31-40|41-50|51-56
- What is a cursor for loop?
Answer: A cursor FOR loop is a loop that is associated with (actually defined by) an explicit cursor or a SELECT statement incorporated directly within the loop boundary.Use the cursor FOR loop whenever (and only if) you need to fetch and process each and every record from a cursor, which is a high percentage of the time with cursors.
- What are cursor attributes?
Answer: Cursor attributes are used to get the information about the current status of your cursor.Both explicit and implicit cursors have four attributes, as shown:
- %FOUND : Returns TRUE if record was fetched successfully, FALSE otherwise.
- %NOTFOUND : Returns TRUE if record was not fetched successfully, FALSE otherwise.
- %ROWCOUNT : Returns number of records fetched from cursor at that point in time.
- %ISOPEN : Returns TRUE if cursor is open, FALSE otherwise.
- Difference between an implicit & an explicit cursor.
Answer: The implicit cursor is used by Oracle server to test and parse the SQL statements and the explicit cursors are declared by the programmers.
- What is a cursor?
Answer: A cursor is a mechanism by which you can assign a name to a "select statement" and manipulate the information within that SQL statement.
- What is the purpose of a cluster?
Answer: A cluster provides an optional method of storing table data.A cluster is comprised of a group of tables that share the same data blocks, which are grouped together because they share common columns and are often used together.For example, the EMP and DEPT table share the DEPTNO column.When you cluster the EMP and DEPT, Oracle physically stores all rows for each department from both the EMP and DEPT tables in the same data blocks.You should not use clusters for tables that are frequently accessed individually.
- How do you find the number of rows in a Table ?
Answer: select count(*) from table, or from NUM_ROWS column of user_tables if the table statistics has been collected.
- What is a pseudo column.Give some examples?
Answer: Information such as row numbers and row descriptions are automatically stored by Oracle and is directly accessible, ie.not through tables.This information is contained within pseudo columns.These pseudo columns can be retrieved in queries.These pseudo columns can be included in queries which select data from tables.
Available Pseudo Columns
- ROWNUM - row number.Order number in which a row value is retrieved.
- ROWID - physical row (memory or disk address) location, ie.unique row identification.
- SYSDATE - system or today's date.
- UID - user identification number indicating the current user.
- USER - name of currently logged in user.
- How you will avoid your query from using indexes?
Answer: By changing the order of the columns that are used in the index, in the Where condition, or by concatenating the columns with some constant values.
- What is a OUTER JOIN?
Answer: An OUTER JOIN returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition.
- Which is more faster - IN or EXISTS?
Answer: Well, the two are processed very differently.
Select * from T1 where x in ( select y from T2 )
is typically processed as:
from t1, ( select distinct y from t2 ) t2
where t1.x = t2.y;
The sub query is evaluated, distinct'ed, indexed (or hashed or sorted) and then joined to the original table - typically.As opposed to
select * from t1 where exists ( select null from t2 where y = x )
That is processed more like:
for x in ( select * from t1 )
if ( exists ( select null from t2 where y = x.x )
OUTPUT THE RECORD
It always results in a full scan of T1 whereas the first query can make use of an index on T1(x).So, when is where exists appropriate and in appropriate? Lets say the result of the sub query ( select y from T2 ) is "huge" and takes a long time.But the table T1 is relatively small and executing ( select null from t2 where y = x.x ) is very fast (nice index on t2(y)).Then the exists will be faster as the time to full scan T1 and do the index probe into T2 could be less then the time to simply full scan T2 to build the sub query we need to distinct on.
Lets say the result of the sub query is small - then IN is typically more appropriate.If both the sub query and the outer table are huge - either might work as well as the other - depends on the indexes and other factors.