SQL Query Tuning

Here are some very simple yet powerful SQL tips to remember.

Avoid using the following:

 Boolean operators >, <, >=, <=, is null,is not null.

 Not in, !=

 Like '%pattern', not exists

 Calculations on unindexed columns or (use union instead)

 Having (use a WHERE clause instead)

 Do use the following:

• Enable aliases to prefix all columns

• Place indexed columns higher in the WHERE clause

• Use SQL Joins instead of using sub-queries

• Make the table with the least number of rows the driving table by making it first in the FROM clause

Other important points for SQL Tuning:

• Establish a tuning environment that reflects your production  database.

• Establish performance expectations before you begin

• Always Design and develop with performance in mind

• Create Indexes to support selective WHERE clauses and join conditions.

• Use concatenated indexes where appropriate

• Consider indexing more than you think you should, to avoid table lookups.

• Pick the best join method.

• Nested loops joins are best for indexed joins of subsets.

• Hash joins are usually the best choice for "big" joins.

• Pick the best join order.

• Pick the best "Driving" table.

• Eliminate rows as early as possible in the join order

• Use bind variables. Bind variables are key to application scalability.

• Use Oracle hints where appropriate.

• Compare performance between alternative syntax for your SQL statement.

• Consider utilizing PL/ SQL to overcome difficult SQL tuning issues.

• Consider using third party tools to make the job of SQL tuning easier.

• Never do a calculation on an Indexed column (e.g., WHERE salary*5 > :myvalue).

• Whenever possible, use the UNION statement instead of OR conditions.

• Avoid the use of NOT IN or HAVING in the WHERE clause. Instead, use the NOT EXISTS clause.

• Always specify numeric values in numeric form and character values in character form (e.g., WHERE

emp_number = 565, WHERE emp_name = ‘Jones’).

• Avoid specifying NULL in an indexed column.

• Avoid the LIKE parameter if = will suffice. Using any Oracle function will invalidate the index, causing a full-table scan.

• Never mix data types in Oracle queries, as it will invalidate the index. If the column is numeric,

remember not to use quotes (e.g., salary = 50000). For char index columns, always use single quotes (e.g., name = ‘BURLESON’).

• Avoid using subqueries when a JOIN will do the job.

• Use the Oracle “decode” function to minimize the number of times a table has to be selected.

• If your query will return more than 20 percent of the rows in the table, a full-table scan may be better than an index scan.

• Always use table aliases when referencing columns.

• To turn off an index you do not want to use (only with a cost-based optimizer), concatenate a null string to the index column name (e.g., name||') or add zero to a numeric column name (e.g., salary+0).

With the rule-based optimizer, this allows you to manually choose the most selective index to service your query. These rules may seem simplistic but following them in a diligent manner will generally relieve more than half of the SQL tuning problems that are experienced:

Remember that Oracle’s rule-based optimizer looks at the order of table names in the FROM clause to determine the driving table. Always make sure that the last table specified in the FROM clause is the table that will return the smallest number of rows. In other words, specify multiple tables with the largest result set table specified first in the FROM clause.

SQL Query Tuning Tips

Efficient SQL statements play a very important role in maintaining the database. They not only improve the performance of the database but also help in reducing the network traffic. The following can be stated as some of the tips for tuning SQL statements used to retrieve data from the database:

1. Know your application and business data well: Before writing our SQL statements, we need to familiarize ourselves with different business data sources. We must be aware of the data volume and its distribution in the database along with thorough understanding of the business data model – such as relationships among business entities.

2. Test your queries with realistic data: After writing an SQL statement, we must test it in an environment which reflects the production database. The behavior of the SQL statement depends a lot on the environment wherein it is tested.

3. Write identical SQL statements in your applications: Use bind variables, stored procedures and packages whenever possible. The benefits of using identical SQL statements include reduced memory use on the database server and faster execution, as parsing is not done.

4. Use indexes carefully on the table: Be sure of creating the necessary indexes on the table used in the SQL statement. But creating too many of them may result in performance degradation of the server. Try to follow the below rules in creating indexes on a table:

1. Create indexes on the columns which are frequently used in the WHERE clause of the application SQL or the queries given by the end users.

2. Index those columns which are frequently used in joining tables in SQL statements.

3. Use only index columns that select small percent of rows with the same value.

4. Do not index the columns which are used with functions and operators in the WHERE clause of the SQL statement.

5. Do not index those columns which get modified frequently as the index maintenance will become an issue during execution of DML statements on the table.

6. Unique indexes are better than non-unique indexes due to better selectivity. Create unique indexes on PK columns and non-unique indexes on FK columns and the columns frequently used in the WHERE clause.

7. Create index so that the column used in the WHERE clause make up a leading portion of the index.

5. Make an indexed path available to SQL: Sometimes even after creating the indexes, they won’t get used in the execution of the query. One of the different ways of making the indexed path available to the optimizer is to use SQL hints.

6. Use Explain Plan and TKPROF wherever possible: These are the tools that come along with Oracle server and help in understanding the execution of our SQL statements. Explain Plan – helps us to know the access path the optimizer opted. TKPROF – shows the actual performance statistics of the SQL statement.

7. Understand the Optimizer: SQL statement can be executed using rule-based optimizer or cost-based optimizer. In older applications, Oracle has used rule-based approach whereas in newer applications, oracle is promoting more of cost-based approach. If you opt to use cost-based approach, then it is necessary to run ANALYZE schema regularly as doing that stores the recent database statistics in the data dictionary views which are used by the cost-based optimizer. The SQL query can tuned only if cost based approach is opted for.

8. Think globally when acting locally: Keep in mind that the modifications you do to improve the

performance of one SQL statement affects other SQL statements used by applications or other users.

9. The WHERE clause is crucial: The following can be said to be some of the cases where the WHERE

clause will not use index even if it is created on a column. The index is created on column COL1 but it

wont be used in the below illustrated examples.

1. COL1 < COL2

2. COL1 > COL2

3. COL1 <= COL2

4. COL1 >= COL2

5. COL1 is null – The index does not store null values hence when queries for null values index won’t get

used

6. COL1 is not null – The index does not store null values hence when queries for null values index won’t

get used.

7. COL1 not in (value1, value2...)

8. COL1 != expression1

9. COL1 like ‘%pattern1’ – index is not used in this case as the leading edge of the index is suppressed;

but whereas if queried as COL1 like ‘pattern1%’, then index is used as they would result in a bounded

range index scan

10. Not exists (subquery)

11. Expression1 = expression2 – All expressions, functions or calculations involving indexes column

would prohibit using the index on that column.

10. Use WHERE clause instead of HAVING clause to filter the records : Using an indexed column in

HAVING clause of the query to filter records will result in full table scan and avoid the usage on the index

on that column. Instead first filter the records using WHERE clause to filter the records which makes use

of the index on the column. Below queries illustrate the usage of index, if an index is created on DEPTID

column of EMP table.

1. Select dept_id, sum(salary) from emp group by dept_id having dept_id = 100;

2. Select dept_id, sum(salary) from emp where  dept_id = 100 group by dept_id;

In case a) Query will not use the index created on dept_id column whereas case b) query makes use of

it.

11. Specify the leading index columns: In case of a compound index, it is used in an SQL query only if the

leading column of it is used. Below queries are used to illustrate the same with an assumption of a

compound index (PART_NUM, PRODUCT_ID) existing on the table PARTS.

1. Select * from parts where part_num = 2;

2. Select * from parts where product_id = 1002;

Case a) would use the index as the column used in the query WHERE clause is a leading column of the

compound index; whereas case b) query would not use the index. If incase of case b), you want to make

use of index we can re-write the query in the following way:

Select * from party where part_num > 0 and product_id = 1002;

12. Evaluate index scan v/s full table scans: If selecting more than 15 percent of the records from a table, it is better to use/force a full table scan on the table for such query. When using an index does more harm than good to the query, we can use techniques the following to suppress the usage of

index.

1. Select /*+ full(a)*/ * from emp where salary = 5000;

A full hint forces the full table scan on the table and avoids the usage of index on salary column.

2. Select * from emp where salary + 0 = 5000;

The salary column in WHERE clause is modified to an expression which results in avoiding the usage of index on the column.

3. Select * from emp where salary = ‘5000’;

An implicit conversion on the indexed column results in avoiding the index usage.

4. Select * from emp where ss# ||’  ‘|| = ‘111-222-333’;

When the percentage of table rows accessed is 15 percent or less, an index scan will work better because it results in multiple logical reads per row accessed, whereas a full table scan can read all the rows in a block in one logical read. Thus, the performance of full table scan is better when accessing a large percentage of rows from a table.

            To illustrate this point, say the ANALYZE command is issued against the EMP table and all its indexes. Oracle generates the following statistics in the data dictionary table USER_TABLES and

USER_INDEXES:

          Table Statistics:

NUM_ROWS = 1000

BLOCKS = 100

Index Statistics:

BLEVEL = 2

AVG_LEAF_BLOCKS_PER_KEY = 1

AVG_DATA_BLOCKS_PER_KEY = 1

            Based on these statistics, the following would be the logical reads (block accessed) for different types of scans:       

  Use of index to return one row = 3

(BLEVEL+(AVG_LEAF_BLOCKS_PER_KEY - 1) + AVG_DATA_PER_KEY)

Use of index to return all rows = 3000

                                        (NUM_ROWS * Blocks accessed to return one row using index)

 Full table scan = 100(BLOCKS)

13. Use an ORDER BY clause for index scan: Oracle optimizer uses an index scan if the ORDER BY clause is used on an indexed column. The query would retrieve ROWID from index and access the table using the ROWID.

14. Know thy data: You need to know the data and its distribution in your database server in order to understand the usage of indexes on the columns and their execution. You need to keep in mind that the SQL query performance varies as the database grows and data distributions changes.

15. Know when to use large-table scans: A full table scan provides better performance when compared to an index scan on a very small table or a very large table. An index scan on a very large table may require scanning of many indexes and table blocks. When these blocks are brought to database buffer cache, they are kept as long as possible. But as these are not needed for other queries, the hit ratio of the database buffer declines resulting in performance degradation. The blocks read by a full table scan are removed from database buffer cache much earlier does not hamper the performance as an index scan on a very large table.

16.   Minimize the table access (passes): Usually by reducing the number of table passes in a SQL statement improves the performance.

     Here’s an example to illustrate the same:

            The STUDENT table has four columns named NAME, STATUS, PARENT_INCOME, and SELF_INCOME. The name is the primary key. The values of the STATUS column are 0 for independent students and 1 for dependent students.

            The following query returns the name and income for dependent as well as independent

students. It results in two passes through the STUDENT table, creates a temporary table for processing, and initiates a sort to eliminate duplicates:

          SELECT NAME, PARENT_INCOME

FROM STUDENT

WHERE STATUS = 1

UNION

SELECT NAME, SELF_INCOME

FROM STUDENT

WHERE STATUS = 0;

The same request can be fulfilled by this query, which results in only one pass through the table:

          SELECT NAME, PARENT_INCOME*STATUS + SELF_INCOME (1-STATUS)

FROM STUDENT;

17. Join tables in the proper order: The order in which the tables are joined in a multiple table join is crucial. Perform the most restrictive search first in order to filter out the maximum number of rows and only a few records are processed in further levels. You need to make sure that the driving table (master table in a master-detail join) returns the least number of rows. For cost-based optimizer, the order in which the tables appear in WHERE clause is irrelevant as it picks the best execution plan on its own. We can use the ORDERED hint to control the order in which

the tables are accessed in the execution plan.

18. Use index-only searches whenever possible: If possible, write the queries that use index-only searches. The optimizer will have to search only index and not the full table to satisfy the SQL resulting in improving the performance. For example, a compound index exists on L_NAME and F_NAME columns

in EMP table.

1. Select f_name from emp where l_name = ‘Smith’;

2. Select f_name, salary from emp where l_name = ‘Smith’;

In Case a) query, the optimizer performs an index-only search whereas in case b) query, the optimizer performs a table search in order to get the data for a column – salary which is not a part of the compound index. Hence we need to observe the column list considered in the SELECT clause as well while writing a query.

19. Redundancy is good: Provide as much information as possible in the WHERE clause of the query. If in

a WHERE clause of a query, there is COL1 = Col2 and Col1 = 10 then the optimizer infers that COL2 = 10.

But at if there is a condition like COL1 = COL2 and COL2 = COL3, then the optimizer does not infer it as

COL1 = COL3.

20. Keep it simple, stupid: Make your SQL statements as simple as possible as simple statements yield better performance than a single complex SQL statement. The lower the Explain Plan Cost of an SQL, the better the performance of the query. Hence we need to try to tune our queries to reduce the cost as much as possible.

21. You can reach the same destination in different ways: In many cases, more than one SQL can provide with the same desired results. Each query uses a different plan and performs differently. For example,

the use of MINUS operator may perform faster than the use of NOT IN or NOT EXISTS predicates. If an

SQL involves an OR in the WHERE clause, we can re-write the same by using a UNION. Hence we need to evaluate all the possible queries for a requirement and choose the best access path query.

22. Use the special columns: Make use of the special columns like ROWID and ROWNUM in your queries. ROWID searches are faster than searches performed on any columns. A ROWID is not a constant in the database; hence do not hard-coded the value in your SQLs and applications.

23. Use explicit cursors over implicit cursors: Implicit cursors are opened by Oracle for DELETE, UPDATE,

SELECT and INSERT and use an extra fetch. Explicit cursors are opened by developers using DECLARE,

PEN, FETCH and CLOSE cursor statements.

24. Explore and take advantage of the oracle parallel query option: Using the parallel query option, you can execute SQL in parallel for faster performance. This parallel query option can be used only in SMP and MPP systems containing multiple disk drivers. The Oracle database has to be configured for this feature to be used and SQLs specific for that feature should be written.

25. Reduce the network traffic and increase throughput: Using array processing and PL/SQL blocks can achieve better performance and reduce the network traffic. Array processing allows a single SQL to process multiple rows. Using arrays in an insert, we can insert 1000 rows in a table. Significance performance gain can be achieved in client/servers and batch systems using this technique. Multiple

SQL statements can cause heavy network traffic. However, if the SQL statements are in a  single PL/SQL block, the entire block can be sent to Oracle server, processed there and results returned to the application running on the client.

5 ways to delete duplicate records Oracle

In Oracle there are many ways to delete duplicate records. Note that below example are described to just explain the different possibilities.

Consider the EMP table with below rows

create table emp(
EMPNNO  integer,
EMPNAME varchar2(20),
SALARY  number);

10    Bill    2000
11    Bill    2000
12    Mark    3000
12    Mark    3000
12    Mark    3000
13    Tom    4000
14    Tom    5000
15    Susan    5000

1. Using rowid

SQL > delete from emp
where rowid not in
(select max(rowid) from emp group by empno);

2. Using self-join

SQL > delete from emp e1
where rowid not in
(select max(rowid) from emp e2
where e1.empno = e2.empno );

3. Using row_number()

SQL > delete from emp where rowid in
(
select rid from
(
select rowid rid,
row_number() over(partition by empno order by empno) rn
from emp
)
where rn > 1
);
This is another efficient way to delete duplicates

4. Using dense_rank()

SQL > delete from emp where rowid in
(
select rid from
(
select rowid rid,
dense_rank() over(partition by empno order by rowid) rn
from emp
)
where rn > 1
);
Here you can use both rank() and dens_rank() since both will give unique records when order by rowid.

5. Using group by

Consider the EMP table with below rows
10    Bill    2000
11    Bill    2000
12    Mark    3000
13    Mark    3000

SQL > delete from emp where
(empno,empname,salary) in
(
select max(empno),empname,salary from emp
group by empname,salary
);

This technique is only applicable in few scenarios.
Always take extra caution while deleting records. 
1. First identify the duplicates using select.
2. Double verify those are actual  ‘duplicates’ or not
3. Take backup if necessary
4. Apply commit only if you are sure.

Create a new schema/new user in oracle 11g

Generally a schema in oracle is the same as an user. Oracle Database automatically creates a schema when you create a user. A file with the DDL file extension is an SQL Data Definition Language file.

Show already created tablespaces:

SQL> select tablespace_name from dba_tablespaces;

 Create tablespace:

SQL> create tablespace edcfc_tabspace
     datafile 'edcfc_tabspace.dat'
     size 10M autoextend on;
   

Create temporary tablespace (Temporaty tablespace is an allocation of 
space in the database that can contain transient data that persists only
for the duration of a session. This transient data cannot be recovered 
after process or instance failure.):

Create temporary tablespace :-

SQL> create temporary tablespace edcfc_tabspace_temp
     tempfile 'edcfc_tabspace_temp.dat'
     size 5M autoextend on;

Create User:-

 SQL > create user eDCFC
       identified by edcfc
       default tablespace EDCFC_TABSPACE
       temporary tablespace EDCFC_TABSPACE_TEMP; 

Grant some privileges:- 

  SQL > grant create session to edcfc;
        grant create table to edcfc;
        grant unlimited tablespace to edcfc;

Login as edcfc and check what privileges he has:- 

  SQL > select * from session_privs;

       PRIVILEGE
     ----------------------------------------
      CREATE SESSION
      UNLIMITED TABLESPACE
      CREATE TABLE 

With create table privilege the user can create tables:

SQL > CREATE TABLE "EMP" (emp_id .............);

SQL > INSERT INTO EMP VALUES (...........) ;

More information:

DDL

• http://docs.oracle.com/cd/B12037_01/server.101/b10759/statements_1001.htm

DBMS_METADATA

• http://www.dba-oracle.com/t_1_dbms_metadata.htm
• http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_metada.htm#ARPLS026
• http://docs.oracle.com/cd/B28359_01/server.111/b28310/general010.htm#ADMIN11562

Schema objects

• http://docs.oracle.com/cd/B19306_01/server.102/b14220/schema.htm

Differences between schema and user

• http://dba.stackexchange.com/questions/37012/difference-between-database-vs-user-vs-schema
• difference between a User and a Schema in Oracle?

Privileges

• http://docs.oracle.com/cd/E11882_01/timesten.112/e21642/privileges.htm#TTSQL338

Creating user/schema

• http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_8003.htm
• http://www.techonthenet.com/oracle/schemas/create_schema.php

Creating tablespace

• http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7003.htm#i2231734

SQL Plus commands

• http://ss64.com/ora/syntax-sqlplus.html

JOINS IN ORACLE-different joins in oracle with examples

The purpose of a join is to combine the data across tables. 

A join is actually performed by the where clause which combines the specified rows of tables.

If a join involves in more than two tables then oracle joins first two tables based on the joins condition and then compares the result with the next table and so on.

TYPES

1. Equi join  

2. Non-equi join   

3. Self join   

4. Natural join   

5. Cross join

6. Outer join 

     1. Left outer 

     2. Right outer 

     3.Full outer 

7. Inner join  

8. Using clause     

9. On clause

Assume that we have the following tables.

SQL> select * from dept;

          DEPTNO DNAME        LOC

          ------         ----------         ----------

          10            mkt               hyd

          20            fin                 bang

          30            hr                 Bombay

SQL> select * from emp;

       EMPNO   ENAME      JOB       MGR     DEPTNO

      ----------     ----------      ----------   ---------- ----------

       111          saketh      analyst           444         10

       222          sudha      clerk                333         20

       333          jagan       manager         111         10

       444          madhu     engineer         222         40

EQUI JOIN

A join which contains an ‘=’ operator in the joins condition.

Ex: SQL> select empno,ename,job,dname,loc from emp e,dept d where e.deptno=d.deptno;

          EMPNO     ENAME      JOB    DNAME      LOC

          ----------      ----------     ---------- ----------    ----------

            111           saketh    anal yst    mkt        hyd

            333           jagan      manager  mkt        hyd

            222           sudha     clerk        fin         bang

USING CLAUSE

SQL> select empno,ename,job ,dname,loc from emp e join dept d using(deptno);

          EMPNO     ENAME      JOB    DNAME      LOC

          ----------      ----------     ----------   ---------- ----------

            111           saketh    analyst     mkt          hyd

            333           jagan      manager  mkt         hyd

            222           sudha     clerk        fin          bang

ON CLAUSE

SQL>select empno,ename,job,dname,loc from emp e join dept d on(e.deptno=d.deptno);

          EMPNO     ENAME      JOB    DNAME      LOC

          ---------- ---------- ---------- ---------- ----------

            111           saketh    analyst    mkt        hyd

            333           jagan      manager  mkt        hyd

            222           sudha      clerk        fin        bang

NON-EQUI JOIN

A join which contains an operator other than ‘=’ in the joins condition.

Ex: SQL>select empno, ename, job, dname, loc from emp e,dept d where   e.deptno > d.deptno;

          EMPNO     ENAME    JOB      DNAME      LOC

          ---------- ---------- ---------- ---------- ----------

       222    sudha      clerk          mkt        hyd

       444    madhu     engineer   mkt        hyd

       444    madhu     engineer   fin          bang

       444    madhu     engineer   hr           bombay

SELF JOIN

Joining the table itself is called self join.

Ex:

     SQL> select e1.empno,e2.ename,e1.job,e2.deptno from emp e1,emp e2 where

             e1.empno=e2.mgr;

     EMPNO     ENAME    JOB      DEPTNO

     ---------- ---------- ---------- ----------

       111          jagan      analyst         10

       222          madhu      clerk           40

       333          sudha      manager      20

       444          saketh     engineer      10

NATURAL JOIN

Natural join compares all the common columns.

Ex:

     SQL> select empno,ename,job,dname,loc from emp natural join dept;

     EMPNO   ENAME      JOB      DNAME    LOC

    ---------- ---------- ---------- ---------- ----------

       111          saketh     analyst     mkt        hyd

       333          jagan      manager   mkt        hyd

       222          sudha      clerk         fin          bang

CROSS JOIN

This will gives the cross product.

Ex:

     SQL> select empno,ename,job,dname,loc from emp cross join dept;

 EMPNO  ENAME    JOB        DNAME      LOC

---------- ---------- ---------- ---------- ----------

       111     saketh   analyst      mkt        hyd

       222     sudha    clerk          mkt        hyd

       333     jagan     manager   mkt        hyd

       444     madhu   engineer   mkt        hyd

       111     saketh   analyst      fin          bang

       222     sudha    clerk          fin          bang

       333     jagan     manager   fin          bang

       444     madhu   engineer   fin          bang

       111     saketh   analyst      hr           bombay

       222     sudha    clerk          hr           bombay

       333     jagan     manager   hr           bombay

       444     madhu   engineer   hr           bombay

OUTER JOIN

Outer join gives the non-matching records along with matching records.

LEFT OUTER JOIN

This will display the all matching records and the records which are in left hand side table those that are not in right hand side table.

Ex:

     SQL> select empno,ename,job,dname,loc from emp e left outer join dept d

             on(e.deptno=d.deptno);

Or

     SQL> select empno,ename,job,dname,loc from emp e,dept d where

             e.deptno=d.deptno(+);

                             EMPNO     ENAME   JOB       DNAME      LOC

     ---------- ---------- ---------- ---------- ----------

       111          saketh    analyst       mkt        hyd

       333          jagan      manager    mkt        hyd

       222          sudha     clerk           fin          bang

       444          madhu    engineer

RIGHT OUTER JOIN

This will display the all matching records and the records which are in right hand side table those that are not in left hand side table.

Ex:

     SQL> select empno,ename,job,dname,loc from emp e right outer join dept d

              on(e.deptno=d.deptno);

Or

      SQL> select empno,ename,job,dname,loc from emp e,dept d where e.deptno(+) =

             d.deptno;

     EMPNO    ENAME     JOB      DNAME      LOC

     ---------- ---------- ---------- ---------- ----------

       111          saketh     analyst      mkt        hyd

       333          jagan       manager   mkt        hyd

       222          sudha      clerk          fin          bang

                                                       hr           bombay

FULL OUTER JOIN

This will display the all matching records and the non-matching records from both tables.

Ex:

     SQL> select empno,ename,job,dname,loc from emp e full outer join dept d

              on(e.deptno=d.deptno);

 EMPNO   ENAME    JOB        DNAME      LOC

---------- ---------- ---------- ---------- ----------

       333     jagan     manager    mkt        hyd

       111     saketh   analyst       mkt        hyd

       222     sudha    clerk           fin        bang

       444     madhu   engineer     hr         bombay

INNER JOIN

This will display all the records that have matched.

Ex:

     SQL> select empno,ename,job,dname,loc from emp inner join dept using(deptno);

     EMPNO     ENAME   JOB        DNAME    LOC

     ---------- ---------- ---------- ---------- ----------

       111          saketh     analyst      mkt       hyd

       333          jagan       manager   mkt       hyd

       222          sudha      clerk          fin         bang

What is the order of execution?

select, from, join, where, group by, having, order by

When a query is submitted to the database, it is executed in the following order:

1.FROM clause
2.WHERE clause
3.GROUP BY clause
4.HAVING clause
5.SELECT clause
6.ORDER BY clause

So why is it important to understand this?
When a query is executed,
First all the tables and their join conditions are executed filtering out invalid references between them.
Then the WHERE clause is applied which again filters the records based on the condition given.
Now you have handful of records which are GROUP-ed
And HAVING clause is applied on the result.
As soon as it is completed, the columns mentioned are selected from the corresponding tables.
And finally sorted using ORDER BY clause.
So when a query is written it should be verified based on this order, otherwise it will lead wrong result sets.

What are indexes used for ?

Index stores only physical adress not only data ,indexe is a data base object,when u created either primary key and unique key in columns index is created automatically same name of constraint name like what ever name u created on constarint that name.and main use of index is at time of retriving the data  performance is very fast i mean index column used in where clause .

PL/SQL Interview Questions for Experienced

1. What is difference between TRUNCATE & DELETE

1. Truncate is a DDL command

2. We can remove bulk amount of records at a time

3. We can't rollback the records

4. Release the space in database

5. Truncate reset the high water mark

6. Truncate explicitly commit
                                                 1. Delete is a DML command

                                                 2. We can delete record by record

                                                 3. We can rollback the records

                                                 4. Can’t release the memory in database

                                                 5. Delete can’t reset the water mark

                                                 6. Delete implicitly commit
                                              (OR)

Ans: Differences:

TRUNCATE commits after deleting entire table i.e., cannot be rolled back.

Database triggers do not fire on TRUNCATE DELETE allows the filtered deletion.

Deleted records can be rolled back or committed.Database triggers fire on DELETE.

2. Difference between view and materialized view

Difference

View is a logical table

View can hold the query

We can’t create indexes on view

View will create security purpose

                                                     Mv is a physical table

                                                     Mv can hold the query with refresh data

                                                     We can create indexes on mv

                                                     Mv will create performance issues

3. Difference between procedure and function?

Procedure:
Procedure allow the DML statements without any restrictions

We can’t call procedure in sql language

We can store images in stored procedure

Function:
Function not allow the DML statements (If you need to use we can use pragma)

We can call Function in sql language

Function can’t store images

4. What is cursor?

Cursor is private sql area which is used to execute sql statements and store processing information

5. What is explicit and implicit cursor and examples?

The implicit cursor is automatically declared by oracle every time an sql statement is executed whenever you issue a sql statement, the oracle server opens an area of memory in which the command is parsed and executed. Every implicit cursor attribute start with sql%.

An explicit cursor is created and managed by the user. And used for multi row select statement.     

6.What do u understand by database and what is objects in oracle

Ans: A database is defined as a collection of meaningful data. Objects in oracle means Table, Views, Procedures, Triggers, Synonym etc

7.What is a table, view, snapshot?

Table: A table is the basic unit of data storage in an Oracle database. The tables of a database hold all of the user accessible data. Table data is stored in rows and columns.a

Views: A view is a virtual table. Every view has a query attached to it. (The query is a SELECT statement that identifies the columns and rows of the table(s) the view uses.)

Snapshot: A Snapshot is a recent copy of a table from database or in some cases ,a subset of rows/columns of a table. It is also known as Materialized view.

8.Do a view contain data?            

Ans: Views do not contain or store data

9. What are the advantages of views?

Ans: Provide an additional level of table security, by restricting access to a predetermined set of rows and columns of a table.

- Hide data complexity.

- Simplify commands for the user.

- Present the data in a different perspective from that of the base table.

- Store complex queries.

10.What is an Oracle sequence?

Ans: A Sequence generates a serial list of unique numbers for numerical columns of a database's tables.

11.What is a synonym?

Ans: A synonym is an alias for a table, view, sequence or program unit.

12.What are the types of synonyms?

Ans: There are two types of synonyms private and public.

13.What is a private synonym?

Ans: Only its owner can access a private synonym.

14.What is a public synonym?

Ans: Any database user can access a public synonym

15.What is an Oracle index?

Ans: An index is an optional structure associated with a table to have direct access to rows, which can be created to increase the performance of data retrieval. Index can be created on one or more columns of a table. Index may also be considered as a ordered list of content of a column.

16.What is a schema?

Ans: The set of objects owned by user account is called the schema.

17.What is a join? Explain the different types of joins?

Ans: Join is a query, which retrieves related columns or rows from multiple tables.

Self Join          - Joining the table with itself.

Equi Join         - Joining two tables by equating two common columns.

Non-Equi Join - Joining two tables by not equating two common columns.

Outer Join      - Joining two tables in such a way that query can also retrieve rows that do not have corresponding join value in the other table.

18.Difference between SUBSTR and INSTR?

Ans: INSTR (String1, String2 (n, (m)), INSTR returns the position of the m-th occurrence of the string 2 in string1. The search begins from nth position of string1.

SUBSTR (String1 n, m) SUBSTR returns a character string of size m in string1, starting from n-th position of string1.

19.What is difference between CHAR and VARCHAR2? What is the maximum SIZE allowed for each type?

Ans: CHAR pads blank spaces to the maximum length. VARCHAR2 does not pad blank spaces. For CHAR the maximum length is 255 and 2000 for VARCHAR2

20.How to access the current value and next value from a sequence?

Ans: Current Value : Sequence name.CURRVAL

Next Value sequence name.NEXTVAL.

21.What are the components of physical database structure of Oracle database?

Ans: Oracle database is comprised of three types of files. One or more datafiles, two are more redo log files, and one or more control files.

21.Query to delete duplicate row from a table

Ans: Delete from emp where rowid not in (Select min(rowid) from emp Groupby emp_dept)

23.What is a cursor its attribute and types?

Ans: The Oracle Engine uses a work area for its internal processing in order to execute an SQL statement. This work area is private to SQL operation and is called Cursor.

Types of Cursor:

Implicit Cursor: If the Oracle engine opened a cursor for its internal processing then it is know as implicit cursor. It is invoked implicitly.

Explicit Cursor: A cursor which is opened for processing data through a PL/SQL block is know as Explicit Cursor.

Attributes Of a Implicit Cursor:

%ISOPEN —returns TRUE if cursor is open else FALSE.

Syntax is SQL%ISOPEN

%ROWCOUNT--- returns number of records processed from cursor syntax is SQL %ROWCOUNT %FOUND---- returns TRUE if record is fetched successfully else FALSE, syntax is SQL%FOUND %NOTFOUND-- returns TRUE if record is not fetched successfully else FALSE syntax is SQL%NOTFOUND Attributes Of a Explicit Cursor %ISOPEN—returns TRUE if cursor is open else FALSE. Syntax is cursorname%ISOPEN %ROWCOUNT--- returns number of records processed from cursor syntax is cursorname %ROWCOUNT %FOUND---- returns TRUE if record is fetched successfully else FALSE, syntax is cursorname %FOUND %NOTFOUND-- returns TRUE if record is not fetched successfully else FALSE syntax is cursorname %NOTFOUND

24.What are inline views?

Ans: Inline view is Sub-query(queries written in a where clause of SQL statements.). It is a query whose return values are used in filtering conditions of the main query.

25.How can we refresh a snapshot?

Ans: Refreshing Snapshots: A snapshot can be refreshed automatically or manually. If a snapshot has to be automatically refreshed then refresh clause must be specified in the CREATE SNAPSHOT. The FAST, COMPLETE or FORCE specifies the type of REFRESH used for automatic refresh. For automatic refresh we can specify the START WITH and NEXT parameter to decide the time interval for the next update.COMPLETE refresh: In complete refresh the snapshot query is executed and places the result in the snapshot.FAST refresh : In this only the changes made to the master table will be updated to the snapshot. The corresponding log file is used to update. Fast refresh will be done only if * The snapshot is a simple snapshot. * The snapshot's master table has a snapshot log \ * The snapshot log was created before the snapshot was last refreshed or created.FORCE refresh : In this ORACLE decides how to refresh the snapshot at the scheduled refresh time. If a fast refresh is possible it performs a fast refresh else it does a complete refresh.

26.What is a tablespace?

Ans: A database is divided into Logical Storage Unit called tablespaces. A tablespace is used to grouped related logical structures together.

27.Is sequence cyclic?

Ans: Yes

28.Select nth highest value from a list of values ?

Ans: SELECT a.emp_name,a.sal FROM emp a WHERE &n - 1= (SELECT COUNT(DISTINCT sal) FROM emp b WHERE b.sal > a.sal )

29.What are triggers and its types?

Ans: A trigger is a piece of code attached to a table that is executed after specified DML statements executed on that table. There are 12 types of triggers in PL/SQL that consist of combinations of the BEFORE, AFTER, ROW, STATEMENT, TABLE, INSERT, UPDATE, DELETE and ALL key words: For eg: BEFORE ALL ROW INSERT AFTER ALL ROW INSERT BEFORE INSERT AFTER INSERT

30.What is the maximum number of triggers, can apply to a single table?

Ans: 12 triggers(Oracle).

31.Difference between rowid and rownum?

Ans: ROWID is pseudo column in every table. The physical address of the rows is use to for the ROWID.IN HEXADECIMAL representation, ROWID is shown as 18 character string of the following format BBBBBBBBB.RRRR.FFFF (block, row, file) FFFF is the fileid of the datafile that contains the row. BBBBBBBBB is the address of the datablock within the datafile that contains the row. RRRR is the ROW NUMBER with the data block that contains the row. They are unique identifiers for the any row in a table. They are internally used in the construction of indexes.
Rownum is the sequential number of rows in the result set object.

32.What is the fastest query method for a table?

Ans: By rowid

33.What is the difference of a LEFT JOIN and an INNER JOIN statement?

Ans: A LEFT JOIN will take ALL values from the first declared table and matching values from the second declared table based on the column the join has been declared on. An INNER JOIN will take only matching values from both tables

34.How can I avoid a divide by zero error?

Ans: Use the DECODE function. This function is absolutely brilliant and functions like a CASE statement, and can be used to return different columns based on the values of others.

35.Is view updatable?

Ans: Only if the view is a simple horizontal slice through a single table.

36.What is Dual ?

Ans: The DUAL table is a table with a single row and a single column used where a table is syntactically required.

37.What is the difference between CHAR and VARCHAR ?

Ans: CHAR is fixed length character type at storage level, and that VARCHAR will be variable length.

38.Do we use commit in triggers.

Ans: No

39.How will the fetch the last inserted record in any table ?

Ans: select column 1, column 2.... From where rowid = (select max(rowid) from table);

40.What are constraints and its types?
Integrity Constraint : An integrity constraint is a declarative way to define a business rule for a column of a table. An integrity constraint is a statement about a table's data that is always true.

Types of integrity constraints : The following integrity constraints are supported by ORACLE:

1. NOT NULL : disallows nulls (empty entries) in a table's column

2. UNIQUE : disallows duplicate values in a column or set of columns

3. PRIMARY KEY : disallows duplicate values and nulls in a column or set of columns

4. FOREIGN KEY : requires each value in a column or set of columns match a value in a related table's UNIQUE or PRIMARY KEY.

5. CHECK : disallows values that do not satisfy the logical expression of the constraint

What is Referential Integrity and Referential integrity constraint ?

Ans: Referential Integrity : Referential integrity defines the relationships among different columns and tables in a relational database. It’s called referential integrity because the values in one column or set of columns refer to or must match the values in a related column or set of columns.

A referential integrity constraint requires that for each row of a table, the value in the foreign key matches a value in a parent key.

What is groups by and having clause? Explain with example

Ans: Group by clause tells oracle to group rows based on distinct values that exists for specified columns. The group by clause creates a data set , containing several sets of records grouped together based on condition.

Having Clause: Having clause can be used with GROUP BY clause. Having imposes a condition on the group by clause which further filters the group created by the GROUP BY clause. Select ename,empno From Empl Group by empno having empno > 10;

What are LOCKS? What are types of different types of Lock?

Ans: Locks are mechanisms intended to prevent destructive interaction between users accessing ORACLE data. ORACLE uses locks to control concurrent access to data. Locks are used to achieve two important database goals : Consistency : Ensures that the data a user is viewing or changing is not changed (by other users) until the user is finished with the data. Integrity : Ensures that the database's data and structures reflect all changes made to them in the correct sequence.

Types of Locks :

1. Data Locks (DML)

2. Dictionary Locks (DDL)

3. Internal Locks and Latches

4. Distributed Locks

5. Parallel Cache Management Locks

Data Locks : Row Level and Table Level Row Level : Exclusive Locks Table Level

1. Row Share Table Locks (RS)

2. Row Exclusive Table Locks (RX)

3. Share Table Locks (S)

4. Share Row Exclusive Table Locks (SRX)

5. Exclusive Table Locks (X)

Dictionary Locks :

1. Exclusive DDL Locks

2. Share DDL Locks

3. Breakable Parse Locks Restrictiveness of Locks : In general, two levels of locking can be used in a multi-user database: • Exclusive Locks : An exclusive lock prohibits the sharing of the associated resource. The first transaction to exclusively lock a resource is the only transaction that can alter the resource until the exclusive lock is released. • Share Locks : A share lock allows the associated resource to be shared, depending on the operations involved (e.g., several users can read the same data at the same time). Several transactions can acquire share locks on the same resource. Share locks allow a higher degree of data concurrency than exclusive locks.

Difference between unique key,primary key and foreign key ?

Ans: Foreign key: A foreign key is one or more columns whose values are based on the primary or candidate key values from another table. Unique key can be null; Primary key cannot be null.

What are Advantages of TRUNCATE Command over DELETE/DROP TABLE Command ?

Ans: The TRUNCATE command provides a fast, efficient method for deleting all rows from a table or cluster.

1. A TRUNCATE statement does not generate any rollback information and it commits immediately; it is a DDL statement and cannot be rolled back.

2. A TRUNCATE statement does not affect any structures associated with the table being truncated (constraints and triggers) or authorizations (grants).

3. A TRUNCATE statement also specifies whether space currently allocated for the table is returned to the containing tablespace after truncation.

4. As a TRUNCATE statement deletes rows from a table (or clustered table), triggers associated with the table are not fired.

5. Also, a TRUNCATE statement does not generate any audit information corresponding to DELETE statements if auditing is enabled. Instead, a single audit record is generated for the TRUNCATE statement being issued.

What are steps involved in Execution of SQL statements?

Ans: STEPS IN EXECUTION OF SQL STATEMENTS :

1. Create a cursor

2. Parse the statement

3. Describe Results

4. Defining outputs

5. Bind any variables

6. Execute the statement

7. Fetch rows of a query result

What do you mean by Parsing?

Ans: Parsing : Parsing is the process of: 1. Translating a SQL statement, verifying it to be a valid statement 2. Performing data dictionary lookups to check table and column definitions 3. Acquiring parse locks on required objects so that their definitions do not change during the statement's parsing 4. Checking privileges to access referenced schema objects 5. Determining the execution plan to be used when executing the statement 6. Loading it into a shared SQL area 7. For distributed statements, routing all or part of the statement to remote nodes that contain referenced data

What is a HINT and what are types HINT?

Ans: Hints are suggestions that you give the optimizer for optimizing a SQL statement. Hints allow you to make decisions usually made by the optimizer.

 TYPES OF HINTS :

 ALL_ROWS : The ALL_ROWS hint explicitly chooses the cost-based approach to optimize a statement block with a goal of best throughput.

FIRST_ROWS : The FIRST_ROWS hint explicitly chooses the cost-based approach to optimize a statement block with a goal of best response time.

 FULL : The FULL hint explicitly chooses a full table scan for the specified table.

ROWID : The ROWID hint explicitly chooses a table scan by ROWID for the specified table.

CLUSTER : The CLUSTER hint explicitly chooses a cluster scan to access the specified table.

 HASH : The HASH hint explicitly chooses a hash scan to access the specified table.

INDEX : The INDEX hint explicitly chooses an index scan for the specified table.

AND_EQUAL: The AND_EQUAL hint explicitly chooses an execution plan that uses an access path that merges the scans on several single-column indexes. (You can specify multiple indexes through this hint) INDEX_ASC: The INDEX_ASC hint explicitly chooses an index scan for the specified table. If the statement uses an index range scan, ORACLE scans the index entries in ascending order of their indexed values.

INDEX_DESC: The INDEX_DESC hint explicitly chooses an index scan for the specified table. If the statement uses an index range scan, ORACLE scans the index entries in descending order of their indexed values.

 ORDERED : The ORDERED hint causes ORACLE to join tables in the order in which they appear in the FROM clause.

USE_NL : The USE_NL hint causes ORACLE to join each specified table to another row source with a nested loops join using the specified table as the inner table.

USE_MERGE : The USE_MERGE hint causes ORACLE to join each specified table with another row source with a sort-merge join.

What do u mean by EXCEPTION_INIT Pragma ?

Ans: EXCEPTION_INIT Pragma : To handle unnamed internal exceptions, you must use the OTHERS handler or the pragma EXCEPTION_INIT. A "pragma" is a compiler directive, which can be thought of as a parenthetical remark to the compiler. Pragmas (also called "pseudoinstructions") are processed at compile time, not at run time. They do not affect the meaning of a program; they simply convey information to the compiler. The predefined pragma EXCEPTION_INIT tells the PL/SQL compiler to associate an exception name with an Oracle error number. That allows you to refer to any internal exception by name and to write a specific handler for it. You code the pragma EXCEPTION_INIT in the declarative part of a PL/SQL block, subprogram, or package

using the syntax PRAGMA EXCEPTION_INIT(exception_name, Oracle_error_number); where "exception_name" is the name of a previously declared exception. For internal exceptions, SQLCODE returns the number of the associated Oracle error. The number that SQLCODE returns is negative unless the Oracle error is "no data found," in which case SQLCODE returns +100. SQLERRM returns the message associated with the Oracle error that occurred. The message begins with the Oracle error code. For user-defined exceptions, SQLCODE returns +1 and SQLERRM returns the message “User-Defined Exception” unless you used the pragma EXCEPTION_INIT to associate the exception name with an Oracle error number, in which case SQLCODE returns that error number and SQLERRM returns the corresponding error message. The maximum length of an Oracle error message is 512 characters including the error code, nested messages, and message inserts such as table and column names.

What do u mean by JSP query?

Ans: JSP Query : The JSP Query is a standard query for number to words conversion, used especially for converting amount in number into equivalent amount in words. The query is as follows : Select to_char ( to_date ( ‘&no’, ‘J’ ), ‘JSP’ ) words from dual; For eg : Select to_char ( to_date ( '23949','j' ), 'JSP' ) "words" from dual; The value that can pass to &no cannot exceed 7 digits.

Describe Oracle database’s physical and logical structure ?

Ans: Physical: Data files, Redo Log files, Control file. Logical : Tables, Views, Tablespaces, etc.

What is “Check Constraints” and “with check options” and “Default Specification”?

Ans: CHECK Integrity Constraints: A CHECK integrity constraint on a column or a set of columns requires that a specified condition be true or unknown (ie. Not false) for every row of the table. If a DML statement is issued so that the condition of the CHECK constraint evaluates to false, the statement is rolled back. With check Option: With Check option restricts inserts and updates performed through the view to prevent them from creating rows that the view cannot itself select .based on where clause of the create view statement. For eg: Create or replace view Women As select name from Employee Where Sex= ‘Female’ With Check Option; Default Specification It supplies a default value if column value is not specified on INSERT It can contain literals (constants) and SQL functions, USER, SYSDATE, sequence It cannot include references to any columns.

What is the maximum no. Of columns a table can have ?

Ans: 254(Oracle)

Can a trigger written for a view ?

Ans: No

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 an rollback and again issue a nextval. What will the output be ?

Ans: 3

Can you create index on view ?

Ans: No

What is the difference between alias and synonym ?

Ans: Alias is temporary and used with one query. Synonym is permanent and not used as alias.

What’s the length of SQL integer ?

Ans: 32 bit length

What is tkprof and how is it used?

Ans: The tkprof tool is a tuning tool used to determine cpu and execution times for SQL statements. You use it by first setting timed_statistics to true in the initialization file and then turning on tracing for either the entire database via the sql_trace parameter or for the session using the ALTER SESSION command. Once the trace file is generated you run the tkprof tool against the trace file and then look at the output from the tkprof tool . This can also be used to generate explain plan output.

What is explain plan and how is it used?

Ans: The EXPLAIN PLAN command is a tool to tune SQL statements. To use it you must have an explain_table generated in the user you are running the explain plan for. This is created using the utlxplan.sql script. Once the explain plan table exists you run the explain plan command giving as its argument the SQL statement to be explained. The explain_plan table is then queried to see the execution plan of the statement. Explain plans can also be run using tkprof.

What is The Dynamic Performance Tables?

Ans: Throughout its operation, ORACLE maintains a set of "virtual" tables that record current database activity. These tables are called Dynamic performance tables. Because dynamic performance tables are not true tables, they should not be accessed by most users. However, database administrators can query these tables and can create views on the tables and grant access to those views to other users. The dynamic performance tables are owned by SYS and their names all begin with V_$. Views are created on these tables, and then synonyms are created for the views. The synonym names begin with V$.

What is Savepoint ?

Ans: Savepoints are intermediate markers that can be declared in long transactions that contain many SQL statements. By using savepoints, you can arbitrarily mark your work at any point within a long transaction. This allows you the option of later rolling back all work performed from the current point in the transaction to a declared savepoint within the transaction.

What is Deadlocks?

Ans: A deadlock is a situation that can occur in multi-user systems that causes some number of transactions to be unable to continue work. A deadlock can occur when two or more users are waiting for data locked by each other. It typically happens when each of two or more users are waiting to access a resource that another user has already locked. This creates a deadlock situation because each user is waiting for resources held by the other user. Eg Transaction 1 Time Point Transaction 2 UPDATE emp 1 UPDATE emp SET sal = sal*1.1 SET sal = 1342 WHERE empno = 1000; WHERE empno = 2000; UPDATE emp 2 UPDATE emp SET sal = sal*1.1 SET sal = 1342 WHERE empno = 2000; WHERE empno = 1000; ORA-00060 3 deadlock detected while waiting for resource

What is Privilege ?

Ans: A privilege is a right to execute a particular type of SQL statement or to access another user's object. Types of privileges : • system privileges • object privileges System Privileges : System privileges allow users to perform a particular systemwide action, or to perform a particular action on a particular type of object. E.g. Create Tablespace, Delete the row of any table, etc. Object Privileges : Object privileges allow users to perform a particular action on a specific object. E.g. Delete row of specific table, etc. Roles : Roles are named groups of related privileges that are granted to users or other roles. Advantages of Roles : 1. Reduced granting of privileges 2. Dynamic privilege management (Changing of privileges) 3. Selective availability of privileges (Enalbling/Disabling roles) 4. Application awareness (Enalbling/Disabling of roles by application)

What is Two Phase Commit ?

Ans: Two Phase Commit is a mechanism wherein ORACLE automatically controls and monitors the commit or rollback of a distributed transaction and maintains the integrity of the global database. The Phases of the Two-Phase Commit Mechanism :
• Prepare phase : The global co-ordinator (initiating node) asks participants to prepare (to promise to commit or rollback the transaction, even if there is a failure).
• Commit phase : If all participants respond to the co-ordinator that they are prepared, the co-ordinator asks all nodes to commit the transaction; if all participants cannot prepare, the co-ordinator asks all nodes to roll back the transaction.

Explain about snapshots in detail?

Ans: Snapshots are read-only copies of a master table (or multiple tables) located on a remote node. A snapshot can be queried, but not updated; only the master table can be updated. A snapshot is periodically refreshed to reflect changes made to the master table. A snapshot is a full copy of a table or a subset of a table that reflects a recent state of the master table. A snapshot is defined by a distributed query that references one or more master tables, view, or other snapshots. Simple vs. Complex Snapshots : Each row in a simple snapshot is based on a single row in a single remote table. Therefore, a simple snapshot's defining query has no GROUP BY or CONNECT BY clauses, or subqueries, joins, or set operations. If a snapshot's defining query contains any of these clauses or operations, it is referred to as a complex snapshot. Internals of Snapshot Creation: When a snapshot is created, several operations are performed internally by ORACLE: • ORACLE (at the snapshot node) creates a table to store the rows retrieved by the snapshot's defining query; this is the snapshot's base table. • ORACLE creates a read-only view on the SNAP$ table (base table) for queries issued against the snapshot. • ORACLE creates a second local view on the remote master table. It uses this view when it refreshes the snapshot. • Additionally, if the snapshot is a simple snapshot, ORACLE creates an index on the SNAP$ table. All of these internal objects are created in the schema of the snapshot. Do not alter, change data in, or delete these objects manually.

What is Ref Cursor?

Ans: A REF CURSOR is basically a data type. A variable created based on such a data type is generally called a cursor variable. A cursor variable can be associated with different queries at run-time. The primary advantage of using cursor variables is their capability to pass result sets between sub programs (like stored procedures, functions, packages etc.).

What is row chaining, how does it happen?

Ans: Row chaining occurs when a VARCHAR2 value is updated and the length of the new value is longer than the old value and won’t fit in the remaining block space. This results in the row chaining to another block. It can be reduced by setting the storage parameters on the table to appropriate values. It can be corrected by export and import of the effected table.

Describe hit ratio as it pertains to the database buffers. What is the difference between instantaneous and cumulative hit ratio and which should be used for tuning?

Ans: The hit ratio is a measure of how many times the database was able to read a value from the buffers verses how many times it had to re-read a data value from the disks. A value greater than 80-90% is good, less could indicate problems. If you simply take the ratio of existing parameters this will be a cumulative value since the database started. If you do a comparison between pairs of readings based on some arbitrary time span, this is the instantaneous ratio for that time span. An instantaneous reading gives more valuable data since it will tell you what your instance is doing for the time it was generated over.

What is a Cartesian product?

Ans: A Cartesian product is the result of an unrestricted join of two or more tables. The result set of a three table Cartesian product will have x * y * z number of rows where x, y, z correspond to the number of rows in each table involved in the join.

What is a mutating table error and how can you get around it?

Ans: This happens with triggers. It occurs because the trigger is trying to update a row it is currently using. The usual fix involves either use of views or temporary tables so the database is selecting from one while updating the other.

What are SQLCODE and SQLERRM and why are they important for PL/SQL developers?

Ans: SQLCODE returns the value of the error number for the last error encountered. The SQLERRM returns the actual error message for the last error encountered. They can be used in exception handling to report, or, store in an error log table, the error that occurred in the code. These are especially useful for the WHEN OTHERS exception.

What are Transactional Triggers ? Give the uses of Transational Trigger ?

Ans: Transactional Triggers fire in response to transaction processing events. These events represent points during application processing at which Oracle Forms needs to interact with the data source. Examples of such events include updating records, rolling back to savepoints, and committing transactions. By default, Oracle Forms assumes that the data source is an ORACLE database, and issues the appropriate SQL statements to optimize transaction processing accordingly. However, by defining

transactional triggers and user exits, you can build a form to interact with virtually any data source, including even non-relational databases and flat files. Calling User Exits When you define transactional triggers to interact with a non-ORACLE data source, you will usually include a call to a user exit in the appropriate triggers. The code in your user exit interacts with the non-ORACLE data source. Once the user exit has performed the appropriate function (as indicated by the trigger from which it was called), it returns control to Oracle Forms for subsequent processing. For example, a user exit called from an On-Fetch trigger might be responsible for retrieving the appropriate number of records from the non-ORACLE data source. Once the records are retrieved, Oracle Forms takes over the display and management of those records in the form interface, just as it would if the records had been fetched from an ORACLE database. Uses for Transactional Triggers • Transactional triggers, except for the commit triggers, are primarily intended to access certain data sources other than Oracle. • The logon and logoff transactional triggers can also be used with Oracle databases to change connections at run time.

What is Autonomous transaction ? Where do we use it?

Ans: In Oracle's database products, an autonomous transaction is an independent transaction that is initiated by another transaction. It must contain at least one Structured Query Language (SQL) statement. Autonomous transactions allow a single transaction to be subdivided into multiple commit/rollback transactions, each of which will be tracked for auditing purposes. When an autonomous transaction is called, the original transaction (calling transaction) is temporarily suspended. The autonomous transaction must commit or roll back before it returns control to the calling transaction. Once changes have been made by an autonomous transaction, those changes are visible to other transactions in the database. Autonomous transactions can be nested. That is, an autonomous transaction can operate as a calling transaction, initializing other autonomous transactions within itself.

What is a package, procedure and function?

Ans: Package : A package is a group of related program objects stored together as a unit in the database. A package is an encapsulated collection of related program objects stored together in the database. Program objects are: procedures, functions, variables, constants, cursors, exceptions. Procedure/Function : A procedure or function is a set of SQL and PL/SQL statements grouped together as an executable unit to perform a specific task. The main difference between a procedure and function is functions return a single variable by value whereas procedures do not return any variable by value. Rather they return multiple variables by passing variables by reference through their OUT parameter.

What do u mean by overloading?

Ans: Function Overloading : Packages allow you to overload procedures or functions. Overloading a procedure means creating multiple procedures with the same name in the same package, each taking arguments of different number or datatype.

What are the constructs of a procedure, function or a package ?

Ans: The constructs of a procedure, function or a package are : • variables and constants • cursors • exceptions

What are cascading triggers? What is the maximum no of cascading triggers at a time?

Ans: When a statement in a trigger body causes another trigger to be fired, the triggers are said to be cascading. Max = 32

What is the significance of the & and && operators in PL/SQL ?

Ans: The & operator means that the PL SQL block requires user input for a variable. The && operator means that the value of this variable should be the same as inputted by the user previously for this same variable.

If all the values from a cursor have been fetched and another fetch is issued, the output will be?

Ans: Last Record

What is a forward declaration ? What is its use ?

Ans: PL/SQL requires that you declare an identifier before using it. Therefore, you must declare a subprogram before calling it. This declaration at the start of a subprogram is called forward declaration. A forward declaration consists of a subprogram specification terminated by a semicolon.

Any three PL/SQL Exceptions?

Ans: Too_many_rows, No_Data_Found, Value_Error, Zero_Error, Others

Describe the use of %ROWTYPE and %TYPE in PL/SQL

Ans: %ROWTYPE allows you to associate a variable with an entire table row. The %TYPE associates a variable with a single column type.

How can you call a PL/SQL procedure from SQL?

Ans: By use of the EXECUTE (short form EXEC) command.

What are the various types of Exceptions ?

Ans: User defined and Predefined Exceptions.

What is RAISE_APPLICATION_ERROR ?

Ans: DBMS_STANDARD provides a procedure named raise_application_error, which lets you issue user-defined error messages. That way, you can report errors to an application and avoid returning unhandled exceptions. The calling syntax is : raise_application_error(error_number, error_message); where error_number is a negative integer in the range -20000...-20999 and error_message is a character string up to 2048 bytes in length. An application can call raise_application_error only from an executing stored subprogram. When called, raise_application_error ends the subprogram, rolls back any database changes it made, and returns a user-defined error number and message to the application. The error number and message can be trapped like any ORACLE error. The calling application gets a PL/SQL exception, which it can process using the error-reporting functions SQLCODE and SQLERRM in an OTHERS handler. • The statement Raise_Application_Error can be called either from a procedure body or from an exception handler. • Irrespective of whether an error occurred or not, a raise_application_error command always raises an exception in the calling program (eg a forms trigger). If an exception handler is not written in that forms trigger, then a forms error occurs.