SQL – PL/SQL I.V. questions

1- What is a stored procedure?

A stored procedure is a sequence of statements that perform specified function.

Named pl/sql block which perform an action. It is stored in db. as schema object.

2- What is SQL * Loader?

It is product to load data from external file to database. Two types of input must be provide to load: the data itself and control file which has the name and format for data file.

3- What are the 2 parts of package?

Package specification

4- What are date functions?

Add_months - last_day – next_day – months_between & sysdate.

5- What is NVL?

Convert a null value to a non-null value.

6- what is intersect? It is a product of list the matching between 2 tables.

7- how to know the last executed procedure?

select timestamp,owner,obj_name from dba_audit_trail;

8- what is minus? Minus is opposite of intersect, it list the un matches row from 2 tables.

9- what are % type and %rowtype? And advantage of use

fname employees.first_name%TYPE; for provide data type for column

emp_rec1  employees%ROWTYPE; for provide data type for all row of employees table

advantage if the column of database change the variable will also change.

10- what will be the output of this code?

Cursor c is

Select * from emp for update;

Z c1%rowtype;

Begin

Open c1 fetch c1 into z;

End;

By declaring this cursor we can update table emp through z, and by issuing coming or rollback cursor will be closed automatically.

10- What is commit?

make permanent all changes performed in the transaction

Until you commit a transaction: You can roll back (undo) any changes made during the transaction with the ROLLBACK statement.

11- Give the structure of Function.

Create or replace function function_name (arg1 in number) return number

Variables

Begin

End;

12- If the application running very slowly at what point u need to go in order to increase the performance?

We need to check the sql statement blocks, because for every sql statement execution transfer to sql engine and come back to pl engine that process takes more time.

13- What are the advantages of stored procedures?

Extensibility, Modularity, Reusability, Maintainability, one time compilation.

14 what is the difference between stored procedure and application procedure?

stored procedure are subprograms stored in the database, and can be called & executed many time, but application procedure used from particular application.

15- What is cursor and why cursor is required?

The Oracle server uses work areas, called private SQL areas, to execute SQL statements and to store processing information. You can use explicit cursors to name a private SQL area and access its stored information.

Implicit	Implicit cursors are declared by PL/SQL implicitly for all DML and PL/SQL SELECT statements.
Explicit	For queries that return more than one row, explicit cursors are declared and managed by the programmer and manipulated through specific statements

16- what to use instead of cursor and in what cases?

Just use subquery in for clause for example:

For emprec in (select * from emp)

Loop

Dbms_output.put_line(emprec.empno);

End loop;

No exit statement needed just implicit open, fetch close occur.

17- what is pl/sql?

It is a product of Oracle, it is procedure language extension of sql.

18- How to disable all triggers of table at one time?

Alter table table_name disable all trigger

How do you prevent Oracle from giving you informational messages during and after a SQL statement execution? The SET options FEEDBACK and VERIFY can be set to OFF.

How do you generate file output from SQL? By use of the SPOOL comm

What is a CO-RELATED SUBQUERY?

A correlated subquery is a subquery that contains a reference to a table that also appears in the outer query. For example:

SELECT * FROM t1

WHERE column1 = ANY (SELECT column1 FROM t2

WHERE t2.column2 = t1.column2);

Notice that the subquery contains a reference to a column of t1, even though the subquery's FROM clause does not mention a table t1. So, MySQL looks outside the subquery, and finds t1 in the outer query.

What are various joins used while writing SUBQUERIES?

Self join-Its a join foreign key of a table references the same table.

Outer Join--It's a join condition used where one can query all the rows of one of the tables in the join condition even though they don't satisfy the join condition.

Equi-join--It's a join condition that retrieves rows from one or more tables in which one or more columns in one table are equal to one or more columns in the second table.

What are various constraints used in SQL?

· A NOT NULL constraint prohibits a database value from being null.

· A unique constraint prohibits multiple rows from having the same value in the same column or combination of columns but allows some values to be null.

· A primary key constraint combines a NOT NULL constraint and a unique constraint in a single declaration. That is, it prohibits multiple rows from having the same value in the same column or combination of columns and prohibits values from being null.

· A foreign key constraint requires values in one table to match values in another table.

· A check constraint requires a value in the database to comply with a specified condition.

What are different Oracle database objects?

Clusters – Constraints - Database links - Database triggers – Dimensions - External procedure libraries - Index-organized tables – Indexes – Indextypes - Java classes, Java resources, Java sources - Materialized views
Materialized view logs - Object tables - Object types - Object views – Operators – Packages - Sequences
Stored functions, - stored procedures – Synonyms – Tables – Views

What is difference between Rename and Alias?

Rename is a permanent name given to a table or column whereas Alias is a temporary name given to a table or column.

What is a view?

A view is SQL query which is stored in the Oracle data dictionary. Views not contain any data - it is just a stored query in the database that can be executed when called. One can think of a view as a virtual table or mapping of data from one or more tables. Views are useful for security and information hiding, but can cause problems if nested too deeply. Some of the advantages of using views:

· Reduce the complexity of SQL statements

· Share only specific rows in a table with other users

· Hide the NAME and OWNER of the base table

View details can be queried from the dictionary by querying either USER_VIEWS, ALL_VIEWS or DBA_VIEWS.

View types: Views can be classified as simple or complex:

Simple views

Simple views can only contain a single base table. Examples:

CREATE VIEW emp_view AS

    SELECT * FROM emp;

CREATE VIEW dept20

    AS SELECT ename, sal*12 annual_salary

         FROM emp

        WHERE deptno = 20;

One can perform DML operations directly against simple views. These DML changes are then applied to the view's base table.

Complex views

Complex views can be constructed on more than one base table. In particular, complex views can contain:

· join conditions - a group by clause - a order by clause

One cannot perform DML operations against complex views directly. To enable DML operations on complex views one needs to write INSTEAD OF triggers to tell Oracle how the changes relate to the base table(s).

Examples:

CREATE VIEW sample_complex_view AS

    SELECT emp.empno, emp.ename, emp.job, emp.deptno, dept.dname, dept.loc

      FROM emp, dept;

CREATE VIEW sample_complex_view AS

  SELECT emp.empno, emp.ename, emp.job, emp.deptno, dept.dname, dept.loc

  FROM emp, dept

 WHERE emp.deptno = dept.deptno;

Read-only views

Users can only run SELECT and DESC statements against read only views. Examples:

READ ONLY clause on a simple view:

CREATE VIEW clerk (id_number, person, department, position)

    AS SELECT empno, ename, deptno, job

         FROM emp

         WHERE job = 'CLERK'

  WITH READ ONLY;

READ ONLY clause on a complex view:

CREATE VIEW sample_complex_view AS

    SELECT emp.empno, emp.ename, emp.job, emp.deptno, dept.dname, dept.loc

      FROM emp, dept

  WITH READ ONLY;

What are various privileges that a user can grant to another user? SELECT CONNECT RESOURCE

What is difference between UNIQUE and PRIMARY KEY constraints?

A table can have only one PRIMARY KEY whereas there can be any number of UNIQUE keys. The columns that compose PK are automatically define NOT NULL, whereas a column that compose a UNIQUE is not automatically defined to be mandatory must also specify the column is NOT NULL.

Can a primary key contain more than one columns? Yes

How you will avoid duplicating records in a query? By using DISTINCT

What is difference between SQL and SQL*PLUS?

SQL*PLUS is a command line tool where as SQL and PL/SQL language interface and reporting tool. Its a command line tool that allows user to type SQL commands to be executed directly against an Oracle database. SQL is a language used to query the relational database(DML,DCL,DDL). SQL*PLUS commands are used to format query result, Set options, Edit SQL commands and PL/SQL.

Which datatype is used for storing graphics and images?

LONG RAW data type is used for storing BLOB's (binary large objects).

How will you delete duplicating rows from a base table?

DELETE FROM table_name A WHERE rowid>(SELECT min(rowid) from table_name B where B.table_no=A.table_no);

CREATE TABLE new_table AS SELECT DISTINCT * FROM old_table;

Or DROP old_table RENAME new_table TO old_table DELETE FROM table_name A WHERE rowid NOT IN (SELECT MAX(ROWID) FROM table_name GROUP BY column_name)

What is difference between SUBSTR and INSTR

SUBSTR returns a specified portion of a string SUBSTR('BCDEF',4) output BCDE INSTR provides character position in which a pattern is found in a string. INSTR('ABC-DC-F','-',2) output 7 (2nd occurence of '-')

There is a string '120000 12 0 .125' ,how you will find the position of the decimal place

INSTR('120000 12 0 .125','.',1) output 13

There is a '%' sign in one field of a column. What will be the query to find it?.

'\' Should be used before '%'.

When you use WHERE clause and when you use HAVING clause?

HAVING clause is used when you want to specify a condition for a group function and it is written after GROUP BY clause The WHERE clause is used when you want to specify a condition for columns, single row functions except group functions and it is written before GROUP BY clause if it is used.

Which is more faster - IN or EXISTS?

EXISTS is more faster than IN because EXISTS returns a Boolean value whereas IN returns a value.

Appropriate answer will be....

Result of the subquery is small Then "IN" is typicaly more appropriate. Result of the subquery is big/large/long Then "EXIST" is more appropriate.

What is a OUTER JOIN?

A LEFT OUTER JOIN is one of the JOIN operations that allow you to specify a join clause. It preserves the unmatched rows from the first (left) table, joining them with a NULL row in the shape of the second (right) table..

How you will avoid your query from using indexes?

SELECT * FROM emp Where emp_no+' '=12345;

i.e you have to concatenate the column name with space within codes in the where condition.

SELECT /*+ FULL(a) */ ename, emp_no from emp where emp_no=1234;

i.e using HINTS

What is a pseudo column. Give some examples?

It is a column that is not an actual column in the table.

For example USER, UID, SYSDATE, ROWNUM, ROWID, NULL, AND LEVEL.

Suppose customer table is there having different columns like customer no, payments.What will be the query to select top three max payments?

SELECT customer_no, payments from customer C1

WHERE 2<=(SELECT COUNT(*) from customer C2

WHERE C1.payment <= C2.payment);

Another more simple:

SELECT customer_no, payments FROM
(SELECT customer_no, payments FROM
customer C1 ORDER BY payments DESC)
WHERE ROWNUM <4;

What is the purpose of a cluster?

In Oracle, clustering is a way of ordering the physical placement of tables on a disk drive in such a way as to speed up I/O access times. This is accomplished by sharing data blocks on the disk. Anything that minimizes I/O or reduces contention in the physical file system will improve the overall performance of the database.
A table cluster can be described as a set of tables that share the same data blocks, and that are grouped together because they share common columns that are often used together. Index cluster tables can be either multi-table or single-table.
When cluster tables are created, Oracle stores all of the rows for each of the tables in the same data blocks. The cluster key value is the value of the cluster key columns for a particular row.
An index cluster uses an index to maintain row sequence, as opposed to a hash cluster, where the symbolic key is hashed to the data block address.

Difference between an implicit & an explicit cursor?.

PL/SQL declares a cursor implicitly for all SQL data manipulation statements, including quries that return only one row. However,queries that return more than one row you must declare an explicit cursor or use a cursor FOR loop.

Explicit cursor is a cursor in which the cursor name is explicitly assigned to a SELECT statement via the CURSOR...IS statement. An implicit cursor is used for all SQL statements Declare, Open, Fetch, Close. An explicit cursors are used to process multirow SELECT statements An implicit cursor is used to process INSERT, UPDATE, DELETE and single row SELECT. .INTO statements.

What are cursor attributes

%ROWCOUNT %NOTFOUND %FOUND %ISOPEN

What is a cursor for loop?

Cursor For Loop is a loop where oracle implicitly declares a loop variable, the loop index that of the same record type as the cursor's record.

Difference between NO DATA FOUND and %NOTFOUND?

NO DATA FOUND is an exception raised only for the SELECT....INTO statements when the where clause of the querydoes not match any rows. When the where clause of the explicit cursor does not match any rows the %NOTFOUND attribute is set to TRUE instead.

What a SELECT FOR UPDATE cursor represent?

SELECT......FROM......FOR......UPDATE[OF column-reference][NOWAIT] The processing done in a fetch loop modifies the rows that have been retrieved by the cursor. A convenient way of modifying the rows is done by a method with two parts: the FOR UPDATE clause in the cursor declaration, WHERE CURRENT OF CLAUSE in an UPDATE or declaration statement.

What 'WHERE CURRENT OF ' clause does in a cursor?

If you plan on updating or deleting records that have been referenced by a SELECT FOR UPDATE statement, you can use the WHERE CURRENT OF statement.

The syntax for the WHERE CURRENT OF statement in Oracle/PLSQL is either:

UPDATE table_name

  SET set_clause

  WHERE CURRENT OF cursor_name;

DELETE FROM table_name

WHERE CURRENT OF cursor_name;

The WHERE CURRENT OF statement allows you to update or delete the record that was last fetched by the cursor.

Updating using the WHERE CURRENT OF Statement

Here is an example where we are updating records using the WHERE CURRENT OF Statement:

CREATE OR REPLACE Function FindCourse

   ( name_in IN varchar2 )

   RETURN number

IS

   cnumber number;

   CURSOR c1

IS

     SELECT course_number

     FROM courses_tbl

     WHERE course_name = name_in

     FOR UPDATE of instructor;

BEGIN

   OPEN c1;

   FETCH c1 INTO cnumber;

   if c1%notfound then

      cnumber := 9999;

   else

      UPDATE courses_tbl

        SET instructor = 'SMITH'

        WHERE CURRENT OF c1;

      COMMIT;

   end if;

   CLOSE c1;

RETURN cnumber;

END;

Deleting using the WHERE CURRENT OF Statement

Here is an example where we are deleting records using the WHERE CURRENT OF Statement:

CREATE OR REPLACE Function FindCourse

   ( name_in IN varchar2 )

   RETURN number

IS

   cnumber number;

   CURSOR c1

IS

     SELECT course_number

     from courses_tbl

     where course_name = name_in

     FOR UPDATE of instructor;

BEGIN

   open c1;

   fetch c1 into cnumber;

   if c1%notfound then

      cnumber := 9999;

   else

      DELETE FROM courses_tbl

        WHERE CURRENT OF c1;

      COMMIT;

   end if;

   close c1;

RETURN cnumber;

END;

What is use of a cursor variable? How it is defined?

A cursor variable is associated with different statements at run time, which can hold different values at run time. Static cursors can only be associated with one run time query. A cursor variable is reference type(like a pointer in C). Declaring a cursor variable: TYPE type_name IS REF CURSOR RETURN return_type type_name is the name of the reference type,return_type is a record type indicating the types of the select list that will eventually be returned by the cursor variable.

What should be the return type for a cursor variable.Can we use a scalar data type as return type?

The return type for a cursor must be a record type.It can be declared explicitly as a user-defined or %ROWTYPE can be used. eg TYPE t_studentsref IS REF CURSOR RETURN students%ROWTYPE

How you open and close a cursor variable.Why it is required?

OPEN cursor variable FOR SELECT...Statement CLOSE cursor variable In order to associate a cursor variable with a particular SELECT statement OPEN syntax is used.In order to free the resources used for the query CLOSE statement is used.

To know the name of database:

select * from global_name;

select name from v$database;

To know the version of database:

SELECT version

         FROM v$instance;

To check the privilege of current user:

select * from session_privs;

To unlock user:

ALTER USER user_name ACCOUNT UNLOCK;

Operators:

SQL> select id from test1;

ID

---------

6 rows selected.

SQL> select id from test2;

ID

---------

SQL> select id from test1

  2  union

  3  select id from test2;

ID

---------

select id from test1

 union all

 select id from test2

ID

---------

select id from test1

intersect

select id from test2

ID

---------

select id from test1

minus

select id from test2

ID

---------

n  To show primary key of table

select column_name from user_cons_columns c,user_constraints s

where c.constraint_name = s.constraint_name

and c.table_name = s.table_name

and lower(s.table_name) = lower('&tab_name')

and constraint_type = 'P'

n To see the procedure source

select text

from user_source

where name = upper('&proc_name')

order by line

n Adjust your sql+

set linesize 1200;

set serveroutput on;

set verify off;

n To see the triggers of table

select trigger_name,status

from user_triggers

where upper(table_name) = upper('&v_tabn')

n Wrap the text

CREATE PROCEDURE wraptest IS

TYPE emp_tab IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;

all_emps emp_tab;

BEGIN

SELECT * BULK COLLECT INTO all_emps FROM employees;

FOR i IN 1..10 LOOP

DBMS_OUTPUT.PUT_LINE('Emp Id: ' || all_emps(i).employee_id);

END LOOP;

END;

How you were passing cursor variables in PL/SQL 2.2?

In PL/SQL 2.2 cursor variables cannot be declared in a package.This is because the storage for a cursor variable has to be allocated using Pro*C or OCI with version 2.2,the only means of passing a cursor variable to a PL/SQL block is via bind variable or a procedure parameter.

Can cursor variables be stored in PL/SQL tables.If yes how.If not why?

No, a cursor variable points a row which cannot be stored in a two-dimensional PL/SQL table.

Difference between procedure and function?

-Function must return a value. Whereas a procedure may or may not return a value or may return more than one value using the OUT parameter (max 1024)..
-Function can be called from from sql statements where as procedure can not be called from the sql statements
-Functions are normally used for computations whereas procedures are normally used for executing business logic.
-Stored procedure returns always integer value by default zero. whereas function return type could be scalar or table or table values
-Stored procedure is precompiled execution plan whereas functions are not.
What is difference between a formal and an actual parameter?

The variables declared in the procedure and which are passed, as arguments are called actual, the parameters in the procedure declaration. Actual parameters contain the values that are passed to a procedure and receive results. Formal parameters are the placeholders for the values of actual parameters

Can the default values be assigned to actual parameters?Yes

Can a function take OUT parameters. If not why? Yes. A function return a value, but can also have one or more OUT parameters. it is best practice, however to use a procedure rather than a function if you have multiple values to return.

What is syntax for dropping a procedure and a function .Are these operations possible?

              Drop Procedure procedure_name  -          Drop Function function_name

What are ORACLE PRECOMPILERS?.

An Oracle Precompiler is a programming tool that enables you to embed SQL statements in a high-level host program. As Figure 1–1shows, the precompiler accepts the host program as input, translates the embedded SQL statements into standard Oracle runtime library calls, and generates a source program that you can compile, link, and execute in the usual way.

Difference between database triggers and form triggers?

A database trigger is fired when a DML operation is fired on some database table. On the other hand form triggers are fired when user makes some event like pressing a key or navigating between fields of a form. A database trigger can be fired from any session executing the triggering DML statements. While form trigger is specific to the form. Database triggers can cause other database triggers to fire. On the other hand, form triggers cannot cause other form triggers to fire.

What is an UTL_FILE.What are different procedures and functions associated?

with it. UTL_FILE is a package that adds the ability to read and write to operating system files Procedures associated with it are FCLOSE, FCLOSE_ALL and 5 procedures to output data to a file PUT, PUT_LINE, NEW_LINE, PUTF, FFLUSH.PUT, FFLUSH.PUT_LINE,FFLUSH.NEW_LINE. Functions associated with it are FOPEN, ISOPEN.

Can you use a commit statement within a database trigger? No

What is the maximum buffer size that can be specified using the DBMS_OUTPUT.ENABLE function? 1,000,000

When looking at the estat events report you see that you are getting busy buffer waits. Is this bad? Buffer busy waits could indicate contention in redo, rollback or data blocks. You need to check the v$waitstat view to see what areas are causing the problem. The value of the "count" column tells where the problem is, the "class" column tells you with what. UNDO is rollback segments, DATA is data base buffers.

If you see contention جدالfor library caches how can you fix it? Increase the size of the shared pool.

If you see statistics that deal with "undo" what are they really talking about? Rollback segments and associated structures.

If a tablespace has a default pctincrease of zero what will this cause (in relationship to the smon process)

The SMON process won?t automatically coalesce its free space fragments.

If a tablespace shows excessive fragmentation what are some methods to defragment the tablespace? (7.1,7.2 and 7.3 only) In Oracle 7.0 to 7.2 The use of the 'alter session set events 'immediate trace name coalesce level ts#';? command is the easiest way to defragment contiguous free space fragmentation. The ts# parameter corresponds to the ts# value found in the ts$ SYS table. In version 7.3 the ?alter tablespace coalesce;? is best. If the free space isn?t contiguous then export, drop and import of the tablespace contents may be the only way to reclaim non-contiguous free space.

How can you tell if a tablespace has excessive fragmentation? If a select against the dba_free_space table shows that the count of a tablespaces extents is greater than the count of its data files, then it is fragmented.

You have just started a new instance with a large SGA on a busy existing server. Performance is terrible, what should you check for? The first thing to check with a large SGA is that it isn?t being swapped out.

What OS user should be used for the first part of an Oracle installation (on UNIX)? You must use root first.

How many control files should you have? Where should they be located?

At least 2 on separate disk spindles. Be sure they say on separate disks, not just file systems.

What is an ERD?

An ERD is an Entity-Relationship-Diagram. It is used to show the entities and relationships for a database logical model.

Why are recursive relationships bad? How do you resolve them?

A recursive relationship (one where a table relates to itself) is bad when it is a hard relationship (i.e. neither side is a "may" both are "must") as this can result in it not being possible to put in a top or perhaps a bottom of the table (for example in the EMPLOYEE table you couldn?t put in the PRESIDENT of the company because he has no boss, or the junior janitor because he has no subordinates). These type of relationships are usually resolved by adding a small intersection entity.

What does a hard one-to-one relationship mean (one where the relationship on both ends is "must")?

Expected answer: This means the two entities should probably be made into one entity.

How should a many-to-many relationship be handled? By adding an intersection entity table

What is an artificial (derived) primary key? When should an artificial (or derived) primary key be used

A derived key comes from a sequence. Usually it is used when a concatenated key becomes too cumbersome to use as a foreign key.

Users from the PC clients are getting messages indicating ORA-06114: (Cnct err, can't get err txt. See Servr Msgs & Codes Manual) What could the problem? be The instance name is probably incorrect in their connection string. Users from the PC clients are getting the following error stack: ERROR: ORA-01034: ORACLE not available ORA-07318: smsget: open error when opening sgadef.dbf file. HP-UX Error: 2: No such file or directory

What is the probable cause The Oracle instance is shutdown that they are trying to access, restart the instance.

You attempt to add a datafile and get: ORA-01118: cannot add anymore datafiles: limit of 40 exceeded What is the problem and how can you fix it When the database was created the db_files parameter in the initialization file was set to 40. You can shutdown and reset this to a higher value, up to the value of MAX_DATAFILES as specified at database creation. If the MAX_DATAFILES is set to low, you will have to rebuild the control file to increase it before proceeding.

Your users get the following error: ORA-00055 maximum number of DML locks exceeded What is the problem and how do you fix it? The number of DML Locks is set by the initialization parameter DML_LOCKS. If this value is set to low (which it is by default) you will get this error. Increase the value of DML_LOCKS. If you are sure that this is just a temporary problem, you can have them wait and then try again later and the error should clear.

What are the various types of queries ? Normal Queries - Sub Queries - Co-related queries - Nested queries - Compound queries

What is a transaction? Transaction is a set of SQL statements between COMMIT or ROLLBACK statements.

What is implicit cursor and how is it used by Oracle? An implicit cursor is a cursor which is internally created by Oracle.It is created by Oracle for each individual SQL.

Which of the following is not a schema object : Indexes, tables, public synonyms, triggers and packages ? Public synonyms

What is PL/SQL? PL/SQL is Oracle's Procedural Language extension to SQL, the language includes object oriented programming techniques such as encapsulation, function overloading, information hiding (all but inheritance), and so, brings state-of-the-art programming to the Oracle database server and a variety of Oracle tools.

Is there a PL/SQL Engine in SQL*Plus? No, unlike Oracle Forms, SQL*Plus does not have a PL/SQL engine.Thus, all your PL/SQL are send directly to the database engine for execution.This makes it much more efficient as SQL statements are not stripped off and send to the database individually.

Is there a limit on the size of a PL/SQL block? Currently, the maximum parsed/compiled size of a PL/SQL block is 64K and the maximum code size is 100K.You can run the following select statement to query the size of an existing package or procedure.

SQL> select * from dba_object_size where name = 'procedure_name'

Can one read/write files from PL/SQL? in Oracle 7.3 is a UTL_FILE package that can read and write files.The directory you intend writing to has to be in your INIT.ORA file (see UTL_FILE_DIR=...parameter). Before Oracle 7.3 the only means of writing a file was to use DBMS_OUTPUT with the SQL*Plus SPOOL command.
DECLARE
fileHandler UTL_FILE.FILE_TYPE;
BEGIN
fileHandler := UTL_FILE.FOPEN('/home/oracle/tmp', 'myoutput','W');
UTL_FILE.PUTF(fileHandler, 'Value of func1 is %sn', func1(1));
UTL_FILE.FCLOSE(fileHandler);
END;

How can I protect my PL/SQL source code?
PL/SQL V2.2, available with Oracle7.2, implements a binary wrapper for PL/SQL programs to protect the source code.This is done via a standalone utility that transforms the PL/SQL source code into portable binary object code (somewhat larger than the original).This way you can distribute software without having to worry about exposing your proprietary algorithms and methods.SQL*Plus and SQL*DBA will still understand and know how to execute such scripts.Just be careful, there is no "decode" command available. The syntax is: wrap name=myscript.sql

oname=xxxx.yyy

Can one use dynamic SQL within PL/SQL? OR Can you use a DDL in a procedure ? How ?
From PL/SQL V2.1 one can use the DBMS_SQL package to execute dynamic SQL statements.
Eg: CREATE OR REPLACE PROCEDURE DYNSQL AS
cur integer;
rc integer;
BEGIN
cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cur,'CREATE TABLE X (Y DATE)',

DBMS_SQL.NATIVE);
rc := DBMS_SQL.EXECUTE(cur);
DBMS_SQL.CLOSE_CURSOR(cur);
END;

What are the various types of Exceptions?
There are four kinds of exceptions in PL/SQL:

· Named system exceptions. Exceptions that have been given names by PL/SQL and raised as a result of an error in PL/SQL or RDBMS processing.

· Named programmer-defined exceptions. Exceptions that are raised as a result of errors in your application code. You give these exceptions names by declaring them in the declaration section. You then raise the exceptions explicitly in the program.

· Unnamed system exceptions. Exceptions that are raised as a result of an error in PL/SQL or RDBMS processing but have not been given names by PL/SQL. Only the most common errors are so named; the rest have numbers and can be assigned names with the special PRAGMA EXCEPTION_INIT syntax.

· Unnamed programmer-defined exceptions. Exceptions that are defined and raised in the server by the programmer. In this case, the programmer provides both an error number (between -20000 and -20999) and an error message, and raises that exception with a call to RAISE_APPLICATION_ERROR. That error, along with its message, is propagated back to the client-side application.

The system exceptions (both named and unnamed) are raised by PL/SQL whenever a program violates a rule in the RDBMS (such as "duplicate value in index") or causes a resource limit to be exceeded (such as "maximum open cursors exceeded"). Each of these RDBMS errors has a number associated with it. In addition, PL/SQL predefines names for some of the most commonly encountered errors.

8.3.1 Named System Exceptions

The exceptions which are already given names by PL/SQL are declared in the STANDARD package in PL/SQL. You do not have to declare them in your own programs.[ 1 ] Each of the predefined exceptions is listed in Table 8.1 along with its Oracle error number, the value returned by a call to SQLCODE, and a brief description. SQLCODE is a PL/SQL built-in function that returns the status code of the last-executed statement. SQLCODE returns zero if the last statement executed without errors. In most, but not all, cases, the SQLCODE value is the same as the Oracle error code.

[1] If you do so, in fact, you will have declared your own local exception. It will not be raised when the internal error with that name occurs. Avoid declaring an exception with the same name as a predefined exception.

Here is an example of how you might use the exceptions table. Suppose that your program generates an unhandled exception for error ORA-6511. Looking up this error, you find that it is associated with the CURSOR_ALREADY_OPEN exception. Locate the PL/SQL block in which the error occurs and add an exception handler for CURSOR_ALREADY_OPEN, as shown below:

EXCEPTION

   WHEN CURSOR_ALREADY_OPEN

   THEN

      CLOSE my_cursor;

END;

Can we define exceptions twice in same block ? No.

Can you have two functions with the same name in a PL/SQL block ?
yes u can have two functions with same name and parameters of different data types .

Can you call a stored function in the constraint of a table ? No.

What are the various types of parameter modes in a procedure ? IN, OUT AND INOUT.

What is Over Loading and what are its restrictions? Overloading means an object performing different functions depending upon the no.of parameters or the data type of the parameters passed to it.

Can functions be overloaded? Yes.

Overloading is the idea that the functionality of a PL/SQL stored procedure of function can be changed based on the input datatype Polymorphism was a spin off of the PL/SQL concept called “overloading” Overloading a stored procedure or function refers to the ability of a programming method to perform more than one kind of operation depending upon the context in which the method is used.

For a simple example of overloading, you can write a PL/SQL function that does one thing when a numeric argument is passed to the procedure and another thing when a character string is passed as an argument.

Can 2 functions have same name & input parameters but differ only by return datatype? No.

What are the constructs of a procedure, function or a package? variables and constants, cursors, exceptions

Why Create or Replace and not Drop and recreate procedures? Preserve the object grants. Grant can't be dropped.

Can you pass parameters in packages? How? You can pass parameters to procedures or functions in a package.

What are the parts of a database trigger? A trigger event or statement - A trigger restriction - A trigger action

What are the various types of database triggers? There are 12 types of triggers, they are combination of : Insert, Delete and Update Triggers. - Before and After Triggers. - Row and Statement Triggers.

What is the advantage of a stored procedure over a database trigger?
Answer: We have control over the firing of a stored procedure but we have no control over the firing of a trigger.

What is the maximum no of statements that can be specified in a trigger statement? One.

Can views be specified in a trigger statement? Exactly one table or view can be specified in the triggering statement. If the INSTEAD OF option is used, then the triggering statement must specify a view; conversely, if a view is specified in the triggering statement, then only the INSTEAD OF option can be used.
What are the values of :new and :old in Insert/Delete/Update Triggers?
INSERT : new = new value, old = NULL
DELETE : new = NULL, old = old value
UPDATE : new = new value, old = old value

What are cascading triggers? What is the maximum no of cascading triggers at a time?
When a statement in a trigger body causes another trigger to be fired, the triggers are said to be cascading.Max = 32.

What are mutating triggers? A trigger giving a SELECT on the table on which the trigger is written.

What are constraining triggers? A trigger giving an Insert/Update on a table having referential integrity constraint on the triggering table.

Describe Oracle database's physical and logical structure?

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

Can you increase the size of a tablespace ? How ? Yes, by adding datafiles to it.

Can you increase the size of datafiles? How? No (for Oracle 7.0) Yes (for Oracle 7.3 by using the Resize clause )

What is the use of Control files? Contains pointers to locations of various data files, redo log files, etc.

What is the use of Data Dictionary? It Used by Oracle to store information about various physical and logical Oracle structures e.g.Tables, Tablespaces, datafiles, etc

What are the advantages of clusters? Access time reduced for joins.

What are the disadvantages of clusters? The time for Insert increases.

Can Long/Long RAW be clustered? No. Can null keys be entered in cluster index, normal index? Yes.

Can Check constraint be used for self referential integrity ? How ?
Yes.In the CHECK condition for a column of a table, we can reference some other column of the same table and thus enforce self referential integrity.

What are the min.extents allocated to a rollback extent? Two

What are the states of a rollback segment ? What is the difference between partly available and needs recovery? The various states of a rollback segment are :

ONLINE - OFFLINE - PARTLY AVAILABLE - NEEDS RECOVERY - INVALID.

What is the difference between unique key and primary key? Unique key can be null, Primary key can't be null.

An insert statement followed by a create table statement followed by rollback? Will the rows be inserted? No.

Can you define multiple savepoints? Yes. Can you Rollback to any savepoint? Yes.

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

What is the significance of the & and && operators in PL SQL? 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

Can you pass a parameter to a cursor? Explicit cursors can take parameters, as the example below shows.A cursor parameter can appear in a query wherever a constant can appear.

CURSOR c1 (median IN NUMBER) IS
SELECT job, ename FROM emp WHERE sal > median;

What are the various types of RollBack Segments? The types of Rollback segments are as follows :

Public Available to all instances - Private Available to specific instance

Can you use %RowCount as a parameter to a cursor ? Yes

Is the query below allowed :
Select sal, ename Into x From emp Where ename = 'KING' (Where x is a record of Number(4) and Char(15))
Answer: Yes

Is the assignment given below allowed : ABC = PQR (Where ABC and PQR are records) Answer: Yes

Is this for loop allowed : For x in &Start..&End Loop Yes

How many rows will the following SQL return : Select * from emp Where rownum < 10; 9 rows

Which symbol precedes the path to the table in the remote database? Answer: @

Are views automatically updated when base tables are updated? Answer: Yes

Can a trigger written for a view? Answer: No

If all the values from a cursor have been fetched and another fetch is issued, the output will be: error, last record or first record ? Answer: Last Record

A table has the following data [[5, Null, 10]].What wills the average function return? Answer: 7.5

Is Sysdate a system variable or a system function? Answer: System Function

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 ?
Answer: 3

Definition of relational DataBase by Dr.Codd (IBM)? A Relational Database is a database where all data visible to the user is organized strictly as tables of data values and where all database operations work on these tables.

What is Multi Threaded Server (MTA)?
Answer: In a Single Threaded Architecture (or a dedicated server configuration) the database manager creates a separate process for each database user.But in MTA the database manager can assign multiple users (multiple user processes) to a single dispatcher (server process), a controlling process that queues request for work thus reducing the databases memory requirement and resources.

Which are initial RDBMS, Hierarchical & N/w database? RDBMS - R system-Hierarchical-IMS-N/W - DBTG

What is Functional Dependency? Answer: Given a relation R, attribute Y of R is functionally dependent on attribute X of R if and only if each X-value has associated with it precisely one -Y value in R

What is Auditing ? Answer: The database has the ability to audit all actions that take place within it. a) Login attempts, b) Object Accesss, c) Database Action Result of Greatest(1,NULL) or Least(1,NULL) NULL

While designing in client/server what are the 2 imp.things to be considered ?
Answer: Network Overhead (traffic), Speed and Load of client server

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.

What is the result of the following SQL : Select 1 from dual UNION Select 'A' from dual; Answer: Error

Can database trigger written on synonym of a table and if it can be then what would be the effect if original table is accessed? Answer: Yes, database trigger would fire.

Can you alter synonym of view or view? Answer: No Can you create index on view? No.

What is the difference between a view and a synonym? Synonym is just a second name of table used for multiple link of database.View can be created with many tables, and with virtual columns and with conditions.But synonym can be on view.

What's the length of SQL integer? Answer: 32 bit length

What is the difference between foreign key and reference key?
Foreign key is the key i.e.attribute which refers to another table primary key. Reference key is the primary key of table referred by another table.

Can dual table be deleted, dropped or altered or updated or inserted? Answer: Yes

If content of dual is updated to some value computation takes place or not? Answer: Yes

If any other table same as dual is created would it act similar to dual? Answer: Yes

For which relational operators in where clause, index is not used? <> , like '%...' is NOT functions, field +constant, field||''

Assume that there are multiple databases running on one machine.How can you switch from one to another ?
Answer: Changing the ORACLE_SID

What are the advantages of Oracle ?
Portability: Oracle is ported to more platforms than any of its competitors, running on more than 100 hardware platforms and 20 networking protocols. Market Presence : Oracle is by far the largest RDBMS vendor and spends more on R & D than most of its competitors earn in total revenue.This market clout means that you are unlikely to be left in the lurch by Oracle and there are always lots of third party interfaces available. Backup and Recovery : Oracle provides industrial strength support for on-line backup and recovery and good software fault tolerence to disk failure.You can also do point-in-time recovery. Performance : Speed of a 'tuned' Oracle Database and application is quite good, even with large databases.Oracle can manage > 100GB databases. Multiple database support : Oracle has a superior ability to manage multiple databases within the same transaction using a two-phase commit protocol.

What is a forward declaration ? What is its use ? 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.

What are actual and formal parameters ? Actual Parameters : Subprograms pass information using parameters.The variables or expressions referenced in the parameter list of a subprogram call are actual parameters.For example, the following procedure call lists two actual parameters named emp_num and amount:
Eg.raise_salary(emp_num, amount);Formal Parameters : The variables declared in a subprogram specification and referenced in the subprogram body are formal parameters.For example, the following procedure declares two formal parameters named emp_id and increase:
Eg.PROCEDURE raise_salary (emp_id INTEGER, increase REAL) IS current_salary REAL;

What are the types of Notation ?

Answer: Position, Named, Mixed and Restrictions.

What all important parameters of the init.ora are supposed to be increased if you want to increase the SGA size ?
Answer: In our case, db_block_buffers was changed from 60 to 1000 (std values are 60, 550 & 3500) shared_pool_size was changed from 3.5MB to 9MB (std values are 3.5, 5 & 9MB) open_cursors was changed from 200 to 300 (std values are 200 & 300) db_block_size was changed from 2048 (2K) to 4096 (4K) {at the time of database creation}. The initial SGA was around 4MB when the server RAM was 32MB and The new SGA was around 13MB when the server RAM was increased to 128MB.

If I have an execute privilege on a procedure in another users schema, can I execute his procedure even though I do not have privileges on the tables within the procedure ? Yes

What are various types of joins ? Equijoins - Non-equijoins - self join - outer join

What is a package cursor ? A package cursor is a cursor which you declare in the package specification without an SQL statement.The SQL statement for the cursor is attached dynamically at runtime from calling procedures.

If you insert a row in a table, then create another table and then say Rollback.In this case will the row be inserted ?
Answer: Yes.Because Create table is a DDL which commits automatically as soon as it is executed.The DDL commits the transaction even if the create statement fails internally (eg table already exists error) and not syntactically.

Explain how procedures and functions are called in a PL/SQL block?

Function can be called from sql query for example 1- select empno,salary,fn_comm(salary) from emp; 2- comm = fn_comm(sal);

Function is called as part of an expression. Sal := calculate_sal('a822');

Procedure can be called from begin – end clause.

Procedure is called as a pl/sql statement calculate_bonus ('a822');

The cursor having query as select … does not get closed even after commit/rollback.

What will happen after commit statement?

Cursor c1 is Select empno,ename from emp;

Begin

Open c1; Loop

Fetch c1 into eno.ename; exit when c1%notfound;

Commit;

End loop; End;

What happens if a procedure that updates a column of table x is called in a db trigger of the same table?

Trigger will be called based on the event what trigger as to do, if trigger is also doing the same update statement then Mutating table occurs, if trigger is not doing any DML statement nothing happens.

To avoid the mutation table error, the procedure should be declared as an AUTONOMOUS TRANSACTION.

By this the procedure will be treated as a separate identity.

How many types of database triggers can be specified on a table ? What are they ?

A trigger may be a 1. DML Trigger on tables 2. Instead of triggers on views 3. System triggers on database or schema	Based on the way it executes statements triggers are of two types 1. Statement leve trigger 2. Row level trigger	A trigger fires for three actions 1. Insert 2. Delete 3.Update
Trigger cans the fired 1. Before action 2. After action.

What are bind variables in SQL? Explain in detail?

Bind variables are variables you create in SQL*Plus and then reference in PL/SQL or SQL. If you create a bind variable in SQL*Plus, u can use bind variables for such things as storing return codes or debugging your PL/SQL subprograms.

You create bind variables in SQL*Plus with the VARIABLE command. For example

VARIABLE ret_val NUMBER

This command creates a bind variable named ret_val with a datatype of NUMBER.

You reference bind variables in PL/SQL by typing a colon (:) followed immediately by the name of the variable. For example :ret_val := 1;

To change this bind variable in SQL*Plus, you must enter a PL/SQL block. For example:

VARIABLE ret_val NUMBER

BEGIN

 :ret_val:=4;

END;

PL/SQL procedure successfully completed.

This command assigns a value to the bind variable named ret_val.

To display the value of a bind variable in SQL*Plus, you use the SQL*Plus PRINT command. For example:

PRINT RET_VAL

   RET_VAL

----------

a query to retrieve one entire column data of multiple rows into one single column of single row?

SELECT deptno,

listagg (ename, ',') WITHIN GROUP (ORDER BY ename)

enames

FROM emp

GROUP BY deptno

What are the components of a PL/SQL block ?

Answered On : Oct 3rd, 2005

PL/SQL Block contains :

Declare : optional

Variable declaration

Begin : Manadatory

Procedural statements.

Exception : Optional

any errors to be trapped

End : Mandatory

DEPTNO ENAMES

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

10 CLARK,KING,MILLER

20 ADAMS,FORD,JONES,SCOTT,SMITH

30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

How can we insert into a table by fetching data from more then one table by using only single command?

INSERT INTO DYMMY (VAL)
SELECT COLUMN_VALUE val FROM table (tab_contents_nt (1, 2, 3))
UNION ALL
SELECT COLUMN_VALUE val FROM table (tab_contents_nt (4, 5, 6))
UNION ALL
SELECT COLUMN_VALUE val FROM table (tab_contents_nt (7, 8, 9))

In emp table there is a column which consists of gender (male or female) ? How should one can update the values of male to female and female to male with a single query ? Decode(gender,M,F,F,M)

There are 2 tables, a and b having 3 rows each. What will be result on executing the following query? :

SQL> 1- select * from a,b and another code Code

- SELECT  * FROM a CROSS JOIN b ;

Cross join -cartesian product :Table a no. of rows (3) * Table b no.of rows (3)= cross join query returns 9 rows

OCI means::oracle call interface.

In what ways can data be copied from one table to another?

Create table a as Select * from b;

Can we give commit or rollback within a trigger?

Using pragma autonomous_transaction we can use the commit and rollback.

Although you can't give commit or rollback in trigger directly

What is the data type of null?

Null is not a value and not a string. It is unknown it is maximum in descending order and minimum value in ascending order.

What are the advantages of bulk binding in PL/SQL?

Bulk binding reduces the context switches between SQL and pl/SQL engines. It enhances the performance but thr memory consumption would be high.

BULK COLLECT

Bulk binds can improve the performance when loading collections from a queries. The BULK COLLECT INTO construct binds the output of the query to the collection. To test this create the following table.

CREATE TABLE bulk_collect_test AS

SELECT owner,

       object_name,

       object_id

FROM   all_objects;

The following code compares the time taken to populate a collection manually and using a bulk bind.

SET SERVEROUTPUT ON

DECLARE

  TYPE t_bulk_collect_test_tab IS TABLE OF bulk_collect_test%ROWTYPE;

  l_tab    t_bulk_collect_test_tab := t_bulk_collect_test_tab();

  l_start  NUMBER;

BEGIN

  -- Time a regular population.

  l_start := DBMS_UTILITY.get_time;

  FOR cur_rec IN (SELECT *

                  FROM   bulk_collect_test)

  LOOP

    l_tab.extend;

    l_tab(l_tab.last) := cur_rec;

  END LOOP;

  DBMS_OUTPUT.put_line('Regular (' || l_tab.count || ' rows): ' ||

                       (DBMS_UTILITY.get_time - l_start));

  -- Time bulk population.

  l_start := DBMS_UTILITY.get_time;

  SELECT *

  BULK COLLECT INTO l_tab

  FROM   bulk_collect_test;

  DBMS_OUTPUT.put_line('Bulk    (' || l_tab.count || ' rows): ' ||

                       (DBMS_UTILITY.get_time - l_start));

END;

Regular (42578 rows): 66

Bulk    (42578 rows): 4

PL/SQL procedure successfully completed.

SQL>

Difference between truncate and delete

Both of them delete the data not the structure, truncate is a DDL, delete is DML, truncate can't rollback data delete can rollback data, TRUNCATE does not write into redo/undo logs which makes it faster than DELETE.

What are the types of collection in PL/SQL? What is the advantage of nested tables?

Varrays, nested tables, index by tables are collection. Index by tables also known as associative array similar to hashtables in java. Varrays are like arrays in java static in nature need to define maximum size at time of declaration.Nested tables are like nested class in java holding a table as a column of a table.
what is the use of nocopy parameter in oracle procedure.

In procedure,Function there are three types of parameter is there. eg-IN, OUT, INOUT.
IN parameter is call by reference and OUT & INOUT are call by value. Always call by reference is faster than call by value. We use NOCOPY to convert call by value to call by reference.
Can a type body be dropped without dropping the type specification? When you drop a type body, the object type specification still exists, and you can re-create the type body. Prior to re-creating the body, you can still use the object type, although you cannot call the member functions.

With what ways can we find out instance locks? v$lock contains details of locks

What is a database trigger ? Name some usages of database trigger ?

Trigger is data base object .Trigger is block of code it is executed automatically when DML operations are fired
by using trigger we can do the auditing and perform a operation (modifications) on a tables.

A database trigger is a named pl/sql block associated with a table and fires automatically when an event occurs or something happens.
why it is recommonded to use inout instead of out parameter type in a procedure?2) what happen if we will not assign anything in out parameter type in a procedure?

In parameter means it will insert the values
OUT parameters means it will print out put (pass values to output). If we not assigning any value to out parameter it will take (print) NULL value. INOUT means it take values as input and out put

How to avoid using cursors? What to use instead of cursor and in what cases to do so?

for emprec in (select * from emp)

loop

dbms_output.put_line(emprec.empno);

end loop;

no exit statement needed

implicit open,fetch,close occurs

If your select statement retruns only one value, you can avoid using cursor:
for instance:

select fname into v_fname
from emp where empno = 10;

Yes we can ..Instead of cursor we using bulk collection or bulk binding or using %type and %rowtype

Oracle locks

What is the need of primary key as opposed to using not null and unique ?

1- Only one primary key per table but not null+unique can be multiple on one table.

2- Primary key creates clustered index by default while unique key creates non-clustered index.

3- To add cascading is only possible when it follow primary key - foriegn key constraint.

4- primary key does not allow both null and duplicate values.

5- If we use null values it will allow duplicate values but it does not allow null values.
6- If we use unique key it does not allow duplicate value but it allows null values.

Explian rowid,rownum?What are the psoducolumns we have?

ROWID uniquely identifies where a row resides on disk. The information in a ROWID gives Oracle everything he needs to find your row, the disk number, the cylinder, block and offset into the block.

ROWNUM is a "pseudo-column", a placeholder that you can reference in SQL*Plus. The ROWNUM can be used to write specialized SQL and tune SQL. A pseudocolumn behaves like a table column, but is not actually stored in the table. You can select from pseudocolumns, but you can't insert, update or delete their values. Pseudocolumns such as

- CURRVAL and NEXTVAL – LEVEL - ROWID - ROWNUM

What is a global temporary table?

It is permanently created tables for a timed session. Only the data in the table is temporary but the table is permanent

How can we find out the dependencies on a table?

check the table dba_dependencies and see what other tables are affected.

What is Raise_application_error? Used to create your own error messages which can be more descriptive than named exceptions. Syntax is:- Raise_application_error (error_number,error_messages);

where error_number is between -20000 to -20999..

Analytic Functions ?

Probably the easiest way to understand analytic functions is to start by looking at aggregate functions. An aggregate function, as the name suggests, aggregates data from several rows into a single result row. For example, we might use the AVG aggregate function to give us an average of all the employee salaries in the EMP table.

  AVG(SAL)

----------

2073.21429

The GROUP BY clause allows us to apply aggregate functions to subsets of rows. For example, we might want to display the average salary for each department.




 

  
  
   

    
    
SELECT deptno, AVG(sal)
FROM   emp
GROUP BY deptno
ORDER BY deptno;





   

 
 

    DEPTNO   AVG(SAL)

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

        10 2916.66667

        20       2175

        30 1566.66667

Rounded Rectangle: SELECT empno, deptno, sal,
AVG(sal) OVER (PARTITION BY deptno) AS avg_dept_sal
FROM emp;

Analytic functions also operate on subsets of rows, similar to aggregate functions in GROUP BY queries, but they do not reduce the number of rows returned by the query. For example, the following query reports the salary for each employee, along with the average salary of the employees within the department.

     EMPNO     DEPTNO        SAL AVG_DEPT_SAL

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

      7782         10       2450   2916.66667

      7839         10       5000   2916.66667

      7934         10       1300   2916.66667




 

  
  
   

    
    
    
This time AVG is an analytic function, operating on the group of rows defined by the contents of the OVER clause. This group of rows is known as a window, which is why analytic functions are sometimes referred to as window[ing] functions. Notice how the AVG function is still reporting the departmental average, like it did in the GROUP BY query, but the result is present in each row.





   

 
 

      7566         20       2975         2175

      7902         20       3000         2175

      7876         20       1100         2175

      7369         20        800         2175

      7788         20       3000         2175

      7521         30       1250   1566.66667

      7844         30       1500   1566.66667

      7499         30       1600   1566.66667

      7900         30        950   1566.66667

      7698         30       2850   1566.66667

      7654         30       1250   1566.66667

Analytic Function Syntax: There are some variations in the syntax of the individual analytic functions, but the syntax for an analytic function is as follows. analytic_function([ arguments ]) OVER (analytic_clause)

The analytic_clause breaks down into the following optional elements.

[ query_partition_clause ] [ order_by_clause [ windowing_clause ] ]

query_partition_clause: The `query_partition_clause` divides the result set into partitions, or groups, of data. The operation of the analytic function is restricted to the boundary imposed by these partitions, similar to the way a `GROUP BY` clause affects the action of an aggregate function. If the `query_partition_clause` is omitted, the whole result set is treated as a single partition. The following query uses an empty `OVER` clause, so the average presented is based on all the rows of the result set.

     EMPNO     DEPTNO        SAL    AVG_SAL

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

      7369         20        800 2073.21429

      7499         30       1600 2073.21429

      7521         30       1250 2073.21429

      7566         20       2975 2073.21429

      7654         30       1250 2073.21429

order_by_clause: `FIRST_VALUE` function used to return the first salary reported in each department.

     EMPNO     DEPTNO        SAL FIRST_SAL_IN_DEPT

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

      7782         10       2450              2450

      7839         10       5000              2450

      7934         10       1300              2450

      7566         20       2975              2975

      7902         20       3000              2975

      7876         20       1100              2975

      7369         20        800              2975

      7788         20       3000              2975




 

  
  
   

    
    
    
Note: we not used order_by_clause and because of that the result not sorted.

Next example we will use order by clause





   

 
 

      7521         30       1250              1250

      7844         30       1500              1250

      7499         30       1600              1250

      7900         30        950              1250

      7698         30       2850              1250

      7654         30       1250              1250

     EMPNO     DEPTNO        SAL FIRST_VAL_IN_DEPT

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

      7934         10       1300              1300

      7782         10       2450              1300

      7839         10       5000              1300

      7369         20        800               800

      7876         20       1100               800

      7566         20       2975               800

      7788         20       3000               800

      7902         20       3000               800

      7900         30        950               950

      7654         30       1250               950

      7521         30       1250               950

      7844         30       1500               950

      7499         30       1600               950

      7698         30       2850               950

In this case the "ASC NULLS LAST" keywords are unnecessary as ASC is the default for an order_by_clause and NULLS LAST is the default for ASC orders. When ordering by DESC, the default is NULLS FIRST.

windowing clause

RANGE BETWEEN start_point AND end_point          ROWS BETWEEN start_point AND end_point

Possible values for "start_point" and "end_point" are:

· UNBOUNDED PRECEDING : The window starts at the first row of the partition. Only available for start points.

· UNBOUNDED FOLLOWING : The window ends at the last row of the partition. Only available for end points.

· CURRENT ROW : The window starts or ends at the current row. Can be used as start or end point.

·

value_expr PRECEDING : A physical or logical offset before the current row using a constant or

· value_expr FOLLOWING : As above, but an offset after the current row.

The following query shows one method for accessing data from previous and following rows within the current row using the windowing_clause. This can also be accomplished with LAG and LEAD.

     EMPNO     DEPTNO        SAL PREVIOUS_SAL   NEXT_SAL

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

      7369         20        800          800        950

      7900         30        950          800       1100

      7876         20       1100          950       1250

      7521         30       1250         1100       1250

      7654         30       1250         1250       1300

      7934         10       1300         1250       1500

      7844         30       1500         1300       1600

      7499         30       1600         1500       2450

      7782         10       2450         1600       2850

      7698         30       2850         2450       2975

      7566         20       2975         2850       3000

      7788         20       3000         2975       3000

      7902         20       3000         3000       5000

      7839         10       5000         3000       5000

Explain what is Simple Cursor and What is Parameterized Cursor? And Difference between both??

DECLARE

   CURSOR C1(ENO NUMBER) IS

    SELECT * FROM EMP

    WHERE EMPNO=ENO;

BEGIN

 FOR I IN C1(&ENO) LOOP

  DBMS_OUTPUT.PUT_LINE(ENAME);

  END LOOP;

 END;

How do you set table for read only access ?

GRANT SELECT ON table_name TO user_name.

How can I get recent 10 records out of 100 records

select * from employees where rownum<10;

Can we truncate some of the rows from the table instead of truncating the full table.

Yes we can truncate some of the rows from the table, But table should be partitioned & all the rows to be deleted should be present in one single partition : Here is the example:

CREATE TABLE parttab (
state VARCHAR2(2),
sales NUMBER(10,2))
PARTITION BY LIST (state) (
PARTITION northwest VALUES ('OR', 'WA')
TABLESPACE uwdata,
PARTITION southwest VALUES ('AZ', 'CA')
TABLESPACE uwdata);

INSERT INTO parttab VALUES ('OR', 100000);
INSERT INTO parttab VALUES ('WA', 200000);
INSERT INTO parttab VALUES ('AZ', 300000);
INSERT INTO parttab VALUES ('CA', 400000);
COMMIT;

SELECT * FROM parttab;

ALTER TABLE parttab
TRUNCATE PARTITION southwest;

Can we insert multiple nulls through unique constraints ? Yes we can insert multiple nulls as each nulls have different ROWID

ALTER TABLE supplier

ADD CONSTRAINT supplier_unique UNIQUE (supplier_id);

Can we write commit or rollback in trigger ?

No. Triggers run as part of transaction. In Oracle you can create an autonomous transaction inside of a trigger if you really need one.

> Select Count() from T1 where a=10
3
> Select count() from T1 where b=20
11
Now, What will b the O/P of the following..
select count(*) from T1 where a=10 or b=20

CREATE DATABASE LINK

Use the CREATE DATABASE LINK statement to create a database link. A database link is a schema object in one database that enables you to access objects on another database. The other database need not be an Oracle Database system. However, to access non-Oracle systems you must use Oracle Heterogeneous Services.

Once you have created a database link, you can use it to refer to tables and views on the other database. In SQL statements, you can refer to a table or view on the other database by appending @dblink to the table or view name. You can query a table or view on the other database with the SELECT statement. You can also access remote tables and views using any INSERT, UPDATE, DELETE, or LOCK TABLE statement.

CREATE DATABASE LINK local

CONNECT TO scott IDENTIFIED BY tiger

USING 'orcl';

What is Materialized Views in Oracle?

A materialized view, or snapshot as known before, is a table segment whose contents are periodically دوريّا‏ refreshed based on a query, either against a local or remote table. Using materialized views against remote tables is the simplest way to achieve replication of data between sites.

Basic Syntax:

CREATE MATERIALIZED VIEW view-name

BUILD [IMMEDIATE | DEFERRED]

REFRESH [FAST | COMPLETE | FORCE ]

ON [COMMIT | DEMAND ]

[[ENABLE | DISABLE] QUERY REWRITE]

[ON PREBUILT TABLE]

SELECT ...;

The BUILD clause options are shown below.

· IMMEDIATE : The materialized view is populated immediately.

· DEFERRED : The materialized view is populated on the first requested refresh.

The following refresh types are available.

· FAST : A fast refresh is attempted. If materialized view logs are not present against the source tables in advance, the creation fails.

· COMPLETE : The table segment supporting the materialized view is truncated and repopulated completely using the associated query.

· FORCE : A fast refresh is attempted. If one is not possible a complete refresh is performed.

A refresh can be triggered in one of two ways.

· ON COMMIT : The refresh is triggered by a committed data change in one of the dependent tables.

· ON DEMAND : The refresh is initiated by a manual request or a scheduled task.

REWRITE clause tells the optimizer if the materialized view should be consider for query rewrite operations.

The ON PREBUILT TABLE clause tells the database to use an existing table segment, which must have the same name as the materialized view and support the same column structure as the query.

Check Privileges

· Check the user who will own the materialized views has the correct privileges. At minimum they will require the CREATE MATERIALIZED VIEW privilege. If they are creating materialized views using database links, you may want to grant them

CREATE DATABASE
LINK

privilege also.

CONNECT sys@db2

GRANT CREATE MATERIALIZED VIEW TO scott;

GRANT CREATE DATABASE LINK TO scott;

Create Materialized View

Connect to the materialized view owner and create the database link and the materialized view itself.

CONNECT scott/tiger@db2

CREATE DATABASE LINK DB1.WORLD CONNECT TO scott IDENTIFIED BY tiger USING 'DB1.WORLD';

CREATE MATERIALIZED VIEW emp_mv

BUILD IMMEDIATE

REFRESH FORCE

ON DEMAND

AS

SELECT * FROM emp@db1.world;

Alternatively, we could have used a prebuilt table, as shown below.

-- Create the tale first. This could be populated

-- using an export/import.

CREATE TABLE emp_mv AS

SELECT * FROM emp@db1.world;

-- Build the materialized view using the existing table segment.

CREATE MATERIALIZED VIEW emp_mv

REFRESH FORCE

ON DEMAND

ON PREBUILT TABLE

AS

SELECT * FROM emp@db1.world;

Remember to gather stats after building the materialized view.

BEGIN

  DBMS_STATS.gather_table_stats(

    ownname => 'SCOTT',

    tabname => 'EMP_MV');

END;

Create Materialized View Logs

Since a complete refresh involves truncating the materialized view segment and re-populating it using the related query, it can be quite time consuming and involve a considerable amount of network traffic when performed against a remote table. To reduce the replication costs, materialized view logs can be created to capture all changes to the base table since the last refresh. This information allows a fast refresh, which only needs to apply the changes rather than a complete refresh of the materialized view.

To take advantage of the of the fast refresh, connect to the master instance and create the materialized view log.

CONNECT scott/tiger@db1

CREATE MATERIALIZED VIEW LOG ON scott.emp

TABLESPACE users

WITH PRIMARY KEY

INCLUDING NEW VALUES;

Refresh Materialized Views

If a materialized view is configured to refresh on commit, you should never need to manually refresh it, unless a rebuild is necessary. Remember, refreshing on commit is a very intensive operation for volatile base tables. It makes sense to use fast refreshes where possible.

For on demand refreshes, you can choose to manually refresh the materialized view or refresh it as part of a refresh group.

The following code creates a refresh group defined to refresh every minute and assigns a materialized view to it.

BEGIN

   DBMS_REFRESH.make(

     name                 => 'SCOTT.MINUTE_REFRESH',

     list                 => '',

     next_date            => SYSDATE,

     interval             => '/*1:Mins*/ SYSDATE + 1/(60*24)',

     implicit_destroy     => FALSE,

     lax                  => FALSE,

     job                  => 0,

     rollback_seg         => NULL,

     push_deferred_rpc    => TRUE,

     refresh_after_errors => TRUE,

     purge_option         => NULL,

     parallelism          => NULL,

     heap_size            => NULL);

END;

BEGIN

   DBMS_REFRESH.add(

     name => 'SCOTT.MINUTE_REFRESH',

     list => 'SCOTT.EMP_MV',

     lax  => TRUE);

END;

A materialized view can be manually refreshed using the DBMS_MVIEW package.

EXEC DBMS_MVIEW.refresh('EMP_MV');

Rather than using a refresh group, you can schedule DBMS_MVIEW.REFRESH called using the Oracle Scheduler

Cleaning Up

To clean up we must remove all objects.

CONNECT scott/tiger@db2

DROP MATERIALIZED VIEW emp_mv;

DROP DATABASE LINK DB1.WORLD;

BEGIN

  DBMS_REFRESH.destroy(name => 'SCOTT.MINUTE_REFRESH');

END;

CONNECT scott/tiger@db1

DROP MATERIALIZED VIEW LOG ON scott.emp;

Aggregations and Transformations

Materialized views can be used to improve the performance of a variety of queries, including those performing aggregations and transformations of the data. This allows the work to be done once and used repeatedly by multiple sessions, reducing the total load on the server.

The following query does an aggregation of the data in the EMP table.

CONN scott/tiger

SET AUTOTRACE TRACE EXPLAIN

SELECT deptno, SUM(sal)

FROM   emp

GROUP BY deptno;

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

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT   |      |     3 |    21 |     4  (25)| 00:00:01 |

|   1 |  HASH GROUP BY     |      |     3 |    21 |     4  (25)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| EMP  |    14 |    98 |     3   (0)| 00:00:01 |

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

Create a materialized view to perform the aggregation in advance, making sure you specify the ENABLE QUERY REWRITE clause.

CREATE MATERIALIZED VIEW emp_aggr_mv

BUILD IMMEDIATE

REFRESH FORCE

ON DEMAND

ENABLE QUERY REWRITE

AS

SELECT deptno, SUM(sal) AS sal_by_dept

FROM   emp

GROUP BY deptno;

EXEC DBMS_STATS.gather_table_stats(USER, 'EMP_AGGR_MV');

The same query is now rewritten to take advantage of the pre-aggregated data in the materialized view, instead of the session doing the work for itself.

--ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED;

--ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE;

SET AUTOTRACE TRACE EXPLAIN

SELECT deptno, SUM(sal)

FROM   emp

GROUP BY deptno;

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

| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT             |             |     3 |    21 |     3   (0)| 00:00:01 |

|   1 |  MAT_VIEW REWRITE ACCESS FULL| EMP_AGGR_MV |     3 |    21 |     3   (0)| 00:00:01 |

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

Considerations

Before using materialized views and materialized view logs, consider the following:

· Populating a materialized view adds load to both servers involved. The source server is queried to capture the data, which is inserted into the destination server. Be sure the additional load does not adversely affect your primary system.

· Although materialized view logs improve the performance of materialized view refreshes, they do increase the work needed to perform DDL on the base table. Check the additional work does not adversely affect performance on the primary system.

· If regular refreshes are not performed, materialized view logs can grow very large, potentially reducing the performance of their maintenance and blowing tablespace limits.

· Depending on the Oracle version and the complexity of the associated query, fast refreshes may not be possible.

· When using materialized views to improve performance of transformations and aggregations, the QUERY_REWRITE_INTEGRITY and QUERY_REWRITE_ENABLED parameters must be set or the server will not be able to automatically take advantages of query rewrites. These parameters may be set in the pfile or spfile file if they are needed permanently. Later releases have them enabled by default.

SQL Queries Best Practices

1. Always use the where clause in your select statement to narrow the number of rows returned.

2. Use EXISTS clause instead of IN clause as it is more efficient than IN and performs faster.

Ex: Replace SELECT * FROM DEPT WHERE DEPTNO IN (SELECT DEPTNO FROM EMP E) With

SELECT * FROM DEPT D WHERE EXISTS (SELECT 1 FROM EMP E WHERE D.DEPTNO = E.DEPTNO)

Note: IN checks all rows. Only use IN if the table in the sub-query is extremely small.

3. When you have a choice of using the IN or the BETWEEN clauses in your SQL, use the BETWEEN clause as it is much more efficient than IN. Depending on the range of numbers in a BETWEEN, the optimizer will choose to do a full table scan or use the index.

4. Convert multiple OR clauses to UNION ALL.

5. Use equijoins. It is better if you use with indexed column joins. For maximum performance when joining

two or more tables, the indexes on the columns to be joined should have the same data type.

6. Avoid a full-table scan if it is more efficient to get the required rows through an index.

7. Avoid using an index that fetches 10,000 rows from the driving table if you could instead use another index that fetches 100 rows and choose selective indexes.

8. Indexes can't be used when Oracle is forced to perform implicit datatype conversion.

9. Choose the join order so you will join fewer rows to tables later in the join order.

10. Setup driving table to be the one contain the filter condition that eliminates the highest percentage of the table.

11. In a where or having clause, constants or bind var should always be on the right hand side of the operator.

12. Don't use SQL functions in predicate clauses or WHERE clauses or on indexed columns, (e.g. concatenation, substr, decode, rtrim, ltrim etc.) as this prevents the use of the index. Use function based indexes where possible

SELECT * FROM EMP WHERE SUBSTR (ENAME, 1, 3) = KES

Use the LIKE function instead of SUBSTR ()

13. If you want the index used, don't perform an operation on the field. Replace

SELECT * FROM EMPLOYEE WHERE SALARY +1000 = :NEWSALARY With

SELECT * FROM EMPLOYEE WHERE SALARY = :NEWSALARY 1000

14. Minimize the use of DISTINCT because it forces a sort operation.

15. Try joins rather than sub-queries which result in implicit joins Replace

SELECT * FROM A WHERE A.CITY IN (SELECT B.CITY FROM B) With

SELECT A.* FROM A, B WHERE A.CITY = B.CITY

16. Replace Outer Join with Union if both join columns have a unique index: Replace

SELECT A.CITY, B.CITY FROM A, B WHERE A.STATE=B.STATE (+) With

SELECT A.CITY, B.CITY FROM A, B WHERE A.STATE=B.STATE

UNION

SELECT NULL, B.CITY FROM B WHERE NOT EXISTS (SELECT 'X' FROM A.STATE=B.STATE)

18. Use bind variables in queries passed from the application, the same query can be reused. This avoids parsing.

19. Use Parallel Query and Parallel DML if your system has more than 1 CPU.

20. The following operations always require a sort:

SELECT DISTINCT - SELECT UNIQUE - SELECT ....ORDER BY... - SELECT....GROUP BY...

CREATE INDEX - CREATE TABLE.... AS SELECT with primary key specification

Use of INTERSECT, MINUS, and UNION set operators Unindexed table joins Some correlated sub-queries

How to find the nth hightest record holder from a table

select level, max(sal) from scott.emp
where level = '&n'
connect by prior (sal) > sal
group by level;

When would you use Inline view in Sub query?

When we use a select query in the from clause, it is called as inline view. It is used to reduce complexity of using so many join conditions.

What is nested table in Oracle and difference between table and nested table?

NESTED TABLE is an Oracle data type used to support columns containing multivalued attributes, Examples:

CREATE OR REPLACE TYPE my_tab_t AS TABLE OF VARCHAR2(30);

CREATE TABLE nested_table (id NUMBER, col1 my_tab_t)

       NESTED TABLE col1 STORE AS col1_tab;

Insert data into table:

INSERT INTO nested_table VALUES (1, my_tab_t('A'));

INSERT INTO nested_table VALUES (2, my_tab_t('B', 'C'));

INSERT INTO nested_table VALUES (3, my_tab_t('D', 'E', 'F'));

COMMIT;

Select from nested table:

SQL> SELECT * FROM nested_table;

        ID COL1

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

         1 MY_TAB_T('A')

         2 MY_TAB_T('B', 'C')

         3 MY_TAB_T('D', 'E', 'F')

Unnesting the subtable:

SQL> SELECT id, COLUMN_VALUE FROM nested_table t1, TABLE(t1.col1) t2;

        ID COLUMN_VALUE

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

1 A

2 B

2 C

3 D

3 E

3 F

6 rows selected.




 

  
  
   

    
    
    
A more complicated multi-column nested table where customers can have multiple addresses: 





   

 
 

CREATE TYPE address_t AS OBJECT (

   street  VARCHAR2(30),

   city    VARCHAR2(20),

   state   CHAR(2),

   zip     CHAR(5) );

CREATE TYPE address_tab IS TABLE OF address_t;

CREATE TABLE customers (

   custid  NUMBER,

   address address_tab )

NESTED TABLE address STORE AS customer_addresses;

INSERT INTO customers VALUES (1,

            address_tab(

              address_t('101 First', 'Redwood Shores', 'CA', '94065'),

              address_t('123 Maple', 'Mill Valley',    'CA', '90952') )       );

Can we use commit or rollback command in the exception part of PL/SQL block? Yes

DECALRE

BEGIN

EXCEPTION

WHEN NO_DATA_FOUND THEN

INSERT INTO err_log(

err_code, code_desc)

VALUES(1403, No data found)

COMMIT;

RAISE;

END

Indexes

Indexes and Index-Organized Tables

Indexes are schema objects that can speed access to table rows, and index-organized tables, which are tables stored in an index structure. An index is an optional structure, associated with a table or table cluster, that can sometimes speed data access. By creating an index on one or more columns of a table, you gain the ability in some cases to retrieve a small set of randomly distributed rows from the table. Indexes are one of many means of reducing disk I/O. If a heap-organized table has no indexes, then the database must perform a full table scan to find a value. For example, without an index, a query of location 2700 in the hr.departments table requires the database to search every row in every table block for this value. This approach does not scale well as data volumes increase.

For an analogy, suppose an HR manager has a shelf of cardboard boxes. Folders containing employee information are inserted randomly in the boxes. The folder for employee Whalen (ID 200) is 10 folders up from the bottom of box 1, whereas the folder for King (ID 100) is at the bottom of box 3. To locate a folder, the manager looks at every folder in box 1 from bottom to top, and then moves from box to box until the folder is found. To speed access, the manager could create an index that sequentially lists every employee ID with its folder location:

ID 100: Box 3, position 1 (bottom)

ID 101: Box 7, position 8

ID 200: Box 1, position 10

Similarly, the manager could create separate indexes for employee last names, department IDs, and so on.

In general, consider creating an index on a column in any of the following situations:

· The indexed columns are queried frequently and return a small % of the total number of rows in the table.

· A referential integrity constraint exists on the indexed column or columns. The index is a means to avoid a full table lock that would otherwise be required if you update the parent table primary key, merge into the parent table, or delete from the parent table.

· A unique key constraint will be placed on the table and you want to manually specify the index and all index options.

Index Characteristics

Indexes are schema objects that are logically and physically independent of the data in the objects with which they are associated. Thus, an index can be dropped or created without physically affecting the table for the index.

Note:

If you drop an index, then applications still work. However, access of previously indexed data can be slower.

The absence or presence of an index does not require a change in the wording of any SQL statement. An index is a fast access path to a single row of data. It affects only the speed of execution. Given a data value that has been indexed, the index points directly to the location of the rows containing that value.

The database automatically maintains and uses indexes after they are created. The database also automatically reflects changes to data, such as adding, updating, and deleting rows, in all relevant indexes with no additional actions required by users. Retrieval performance of indexed data remains almost constant, even as rows are inserted. However, the presence of many indexes on a table degrades DML performance because the database must also update the indexes.

Indexes have the following properties:

· Usability

· Visibility

Indexes are visible (default) or invisible. An invisible index is maintained by DML operations and is not used by default by the optimizer. Making an index invisible is an alternative to making it unusable or dropping it. Invisible indexes are especially useful for testing the removal of an index before dropping it or using indexes temporarily without affecting the overall application.

Keys and Columns

A key is a set of columns or expressions on which you can build an index. Although the terms are often used interchangeably, indexes and keys are different. Indexes are structures stored in the database that users manage using SQL statements. Keys are strictly a logical concept.

The following statement creates an index on the customer_id column of the sample table oe.orders:

CREATE INDEX ord_customer_ix ON orders (customer_id);

Note: Primary & unique keys automatically have indexes, but you might want to create an index on a foreign key.

Composite Indexes

A composite index, also called a concatenated index, is an index on multiple columns in a table. Columns in a composite index should appear in the order that makes the most sense for the queries that will retrieve data & need not be adjacent in the table.

Composite indexes can speed retrieval of data for SELECT statements in which the WHERE clause references all or the leading portion of the columns in the composite index. Therefore, the order of the columns used in the definition is important. In general, the most commonly accessed columns go first.

For example, suppose an application frequently queries the last_name, job_id, & salary columns in the employees table. Also assume that last_name has high cardinality, which means that the number of distinct values is large compared to the number of table rows. You create an index with the following column order:

CREATE INDEX employees_ix

   ON employees (last_name, job_id, salary);

Queries that access all three columns, only the last_name column, or only the last_name & job_id columns use this index. In this example, queries that do not access the last_name column do not use the index.

Note: In some cases, such as when the leading column has very low cardinality, the database may use a skip scan of this index. Multiple indexes can exist for the same table if the permutation of columns differs for each index. You can create multiple indexes using the same columns if you specify distinctly different permutations of the columns. For example, the following SQL statements specify valid permutations:

CREATE INDEX employee_idx1 ON employees (last_name, job_id);

CREATE INDEX employee_idx2 ON employees (job_id, last_name);

Unique & Nonunique Indexes

Indexes can be unique or nonunique. Unique indexes guarantee that no two rows of a table have duplicate values in the key column or column. rowid exists for each data value. The data in the leaf blocks is sorted only by key.

Nonunique indexes permit duplicates values in the indexed column or columns. so nonunique indexes are sorted by the index key & rowid (ascending). Oracle Database does not index table rows in which all key columns are null, except for bitmap indexes or when the cluster key column value is null.

Types of Indexes

· B-tree indexes

These indexes are the standard index type. They are excellent for primary key & column with 100% unique values, indexes have the following subtypes:

o Index-organized tables

An index-organized table differs from a heap-organized because the data is itself the index.

o Reverse key indexes

In this type of index, the bytes of the index key are reversed, for example, 103 is stored as 301. Descending indexes

This type of index stores data on a particular column or columns in descending order.

o B-tree cluster indexes

Used to index a table cluster key. Instead of pointing to a row, the key points to the block that contains rows related to the cluster key.

· Bitmap & bitmap join indexes

In a bitmap index, an index entry uses a bitmap to point to multiple rows. In contrast, a B-tree index entry points to a single row. A bitmap join index is a bitmap index for the join of two or more tables.

· Function-based indexes

This type of index includes columns that are either transformed by a function, such as the UPPER function, or included in an expression. B-tree or bitmap indexes can be function-based.

· Application domain indexes

This type of index is created by a user for data in an application-specific domain. The physical index need be stored either in the Oracle database as tables or externally as a file.

B-Tree Indexes

B-tree indexes are mostly used on unique or near-unique columns. They keep a good performance during update/insert/delete operations, Note that B-tree is the default index type – if you have created an index without specifying

Description: Description of Figure 3-1 follows

Description of "Figure 3-1 Internal Structure of a B-tree Index"

Branch Blocks & Leaf Blocks

A B-tree index has two types of blocks: branch blocks for searching & leaf blocks that store values. The upper-level branch blocks of a B-tree index contain index data that points to lower-level index blocks. In Figure 3-1, the root branch block has an entry 0-40, which points to the leftmost block in the next branch level. This branch block contains entries such as 0-10 & 11-19. Each of these entries points to a leaf block that contains key values that fall in the range.

Index Scans

In an index scan, the database retrieves a row by traversing the index, using the indexed column values specified by the statement. If a SQL statement accesses only indexed columns, then the database reads values directly from the index rather than from the table. If the statement accesses columns in addition to the indexed columns, then the database uses rowids to find the rows in the table. Typically, the database retrieves table data by alternately reading an index block & then a table block.

Full Index Scan

A full index scan eliminates a sort operation, because the data is ordered by the index key. It reads all index blocks singly. The database uses a full scan in any of the following situations:
* An ORDER BY clause that meets the following requirements is present in the query:
o All of the columns in the ORDER BY clause must be in the index
o The order of the columns in the ORDER BY clause must match the order of the leading index columns
The ORDER BY clause can contain all of the columns in the index or a subset of the columns in the index.
* A GROUP BY clause is present in the query, and the columns in the GROUP BY clause are present in the index. The columns do not need to be in the same order in the index and the GROUP BY clause. The GROUP BY clause can contain all of the columns in the index or a subset of the columns in the index.

Fast Full Index Scan

A fast full scan is a full index scan in which the database reads all index blocks in no particular order. The database accesses the data in the index itself, without accessing the table.

Fast full index scans are an alternative to a full table scan when both of the following conditions are met:

· The index must contain all columns needed for the query.

· A row containing all nulls must not appear in the query result set. For this result to be guaranteed, at least one column in the index must have either:

o A NOT NULL constraint

o A predicate applied to it that prevents nulls from being considered in the query result set

Index Range Scan

In contrast to an index range scan, an index unique scan must have either 0 or 1 rowid associated with an index key. The database performs a unique scan when a predicate references all of the columns in a UNIQUE index key using an equality operator.

UNIQUE INDEX

An index unique scan stops processing as soon as it finds the first record because no second record is possible.

In other words the index must be created as a unique index either using the CREATE UNIQUE INDEX syntax or as a byproduct of the default creation (not deferrable) of a primary key or unique constraint.

CREATE INDEX nui_t on t(testcol);

SELECT COUNT(*)
FROM t
WHERE testcol = 2;

EXPLAIN PLAN FOR
SELECT COUNT(*)
FROM t
WHERE testcol = 2;

SELECT * FROM TABLE(dbms_xplan.display);
------------------------------------------------------------------------
| Id | Operation        | Name | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT |       |    1 |    13 |       1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE   |       |    1 |    13 |            |          |
|* 2 | INDEX RANGE SCAN | NUI_T |    1 |    13 |       1 (0)| 00:00:01 |
------------------------------------------------------------------------

DROP INDEX nui_t;

CREATE UNIQUE INDEX ui_t on t(testcol);

EXPLAIN PLAN FOR
SELECT COUNT(*)
FROM t
WHERE testcol = 2;

SELECT * FROM TABLE(dbms_xplan.display);

-----------------------------------------------------------------------
| Id | Operation        | Name | Rows | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT |      |    1 |    13 |       0 (0)| 00:00:01 |
| 1 | SORT AGGREGATE   |      |    1 |    13 |            |          |
|* 2 | INDEX UNIQUE SCAN| UI_T |    1 |    13 |       0 (0)| 00:00:01 |
-----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("TESTCOL"=2)

-- other ways of producing a UNIQUE SCAN
WHERE testcol IN (2)
WHERE testcol IN (2,3)
WHERE testcol IN (SELECT COUNT(*) FROM user_tables WHERE table_name = 'T')
WHERE testcol BETWEEN 3 AND 3 -- a range scan results if the values are not identical

Index Clustering Factor

The index clustering factor measures row order in relation to an indexed value such as employee last name. The more order that exists in row storage for this value, the lower the clustering factor.

The clustering factor is useful as a rough measure of the number of I/Os required to read an entire table by means of an index:

For example, assume that the employees table fits into two data blocks. Table 3-1 depicts the rows in the two data blocks (the ellipses indicate data that is not shown).

Table 3-1 Contents of Two Data Blocks in the Employees Table

Data Block 1	Data Block 2
100 Steven King SKING ... 156 Janette King JKING ... 115 Alex&er Khoo AKHOO ... . . . 116 Shelli Baida SBAIDA ... 204 Hermann Baer HBAER ... 105 David Austin DAUSTIN ... 130 Mozhe Atkinson MATKINSO ... 166 Sundar &e S&E ... 174 Ellen Abel EABEL ...	149 Eleni Zlotkey EZLOTKEY ... 200 Jennifer Whalen JWHALEN ... . . . 137 Renske Ladwig RLADWIG ... 173 Sundita Kumar SKUMAR ... 101 Neena Kochar NKOCHHAR ...

Data Block 1

Data Block 2

100   Steven    King       SKING    ...

156   Janette   King       JKING    ...

115   Alex&er Khoo       AKHOO    ...

116   Shelli    Baida      SBAIDA   ...

204   Hermann   Baer       HBAER    ...

105   David     Austin     DAUSTIN  ...

130   Mozhe     Atkinson   MATKINSO ...

166   Sundar    &e       S&E    ...

174   Ellen     Abel       EABEL    ...

149   Eleni     Zlotkey    EZLOTKEY  ...

200   Jennifer  Whalen     JWHALEN   ...

137   Renske    Ladwig     RLADWIG  ...

173   Sundita   Kumar      SKUMAR   ...

101   Neena     Kochar     NKOCHHAR ...

Rows are stored in the blocks in order of last name (shown in bold). For example, the bottom row in data block 1 describes Abel, the next row up describes &e, & so on alphabetically until the top row in block 1 for Steven King. The bottom row in block 2 describes Kochar, the next row up describes Kumar, & so on alphabetically until the last row in the block for Zlotkey.

Assume that an index exists on the last name column. Each name entry corresponds to a rowid. Conceptually, the index entries would look as follows:

Abel,block1row1

&e,block1row2

Atkinson,block1row3

Austin,block1row4

Baer,block1row5

Assume that a separate index exists on the employee ID column. Conceptually, the index entries might look as follows, with employee IDs distributed in almost r&om locations throughout the two blocks:

100,block1row50

101,block2row1

102,block1row9

103,block2row19

104,block2row39

105,block1row4

Example 3-2 queries the ALL_INDEXES view for the clustering factor for these two indexes. The clustering factor for EMP_NAME_IX is low, which means that adjacent index entries in a single leaf block tend to point to rows in the same data blocks. The clustering factor for EMP_EMP_ID_PK is high, which means that adjacent index entries in the same leaf block are much less likely to point to rows in the same data blocks.

Example 3-2 Clustering Factor

SQL> SELECT INDEX_NAME, CLUSTERING_FACTOR

  2  FROM ALL_INDEXES

  3  WHERE INDEX_NAME IN ('EMP_NAME_IX','EMP_EMP_ID_PK');

INDEX_NAME           CLUSTERING_FACTOR

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

EMP_EMP_ID_PK                       19

EMP_NAME_IX                          2

Reverse Key Indexes

It has been suggested that using reverse-key indexes will speed-up Oracle INSERT statements, especially with an increasing key, like an index on an Oracle sequence (which is used for the primary key of the target table). For large batch inserts, Oracle reverse key indexes will greatly speed-up data loads because the high-order index key has been reversed.

Ascending & Descending Indexes

In an ascending index, Oracle Database stores data in ascending order. By default, character data is ordered by the binary values contained in each byte of the value, numeric data from smallest to largest number, & date from earliest to latest value.

For an example of an ascending index, consider the following SQL statement:

CREATE INDEX emp_deptid_ix ON hr.employees(department_id);

Descending indexes are useful when a query sorts some columns ascending & others descending. For an example, assume that you create a composite index on the last_name & department_id columns as follows:

CREATE INDEX emp_name_dpt_ix ON hr.employees(last_name ASC, department_id DESC);

If a user queries hr.employees for last names in ascending order (A to Z) & department IDs in descending order (high to low), then the database can use this index to retrieve the data & avoid the extra step of sorting it.

Key Compression

Oracle Database can use key compression to compress portions of the primary key column values in a B-tree index or an index-organized table. Key compression can greatly reduce the space consumed by the index.

Bitmap Indexes

In a bitmap index, the database stores a bitmap for each index key. In a conventional B-tree index, one index entry points to a single row. In a bitmap index, each index key stores pointers to multiple rows.

Bitmap indexes are primarily designed for data warehousing or environments in which queries reference many columns in an ad hoc fashion. Situations that may call for a bitmap index include:

· The indexed columns have low cardinality, that is, the number of distinct values is small compared to the number of table rows.

· The indexed table is either read-only or not subject to significant modification by DML statements.

For a data warehouse example, the sh.customers table has a cust_gender column with only two possible values: M & F. Suppose that queries for the number of customers of a particular gender are common. In this case, the customers.cust_gender column would be a c&idate for a bitmap index.

Each bit in the bitmap corresponds to a possible rowid. If the bit is set, then the row with the corresponding rowid contains the key value. A mapping function converts the bit position to an actual rowid, so the bitmap index provides the same functionality as a B-tree index although it uses a different internal representation.

If the indexed column in a single row is updated, then the database locks the index key entry (for example, M or F) & not the individual bit mapped to the updated row. Because a key points to many rows, DML on indexed data typically locks all of these rows. For this reason, bitmap indexes are not appropriate for many OLTP applications.

Bitmap Indexes on a Single Table

Oracle's two major index types are Bitmap indexes and B-Tree indexes. B-Tree indexes are the regular type that OLTP systems make much use of, and bitmap indexes are a highly compressed index type that tends to be used primarily for data warehouses.

Characteristic of Bitmap Indexes

· For columns with very few unique values (low cardinality)

Columns that have low cardinality are good candidates (if the cardinality of a column is <= 0.1 % that the column is ideal candidate, consider also 0.2% – 1%)

· Tables that have no or little insert/update are good candidates (static data in warehouse)

· Stream of bits: each bit relates to a column value in a single row of table

create bitmap index person_region on person (region);

        Row     Region   North   East   West   South
        1       North        1      0      0       0
        2       East         0      1      0       0
        3       West         0      0      1       0
        4       West         0      0      1       0
        5       South       0      0      0       1
        6       North        1      0      0       0

Advantage of Bitmap Indexes

Fast to read and their structure makes it possible for the system to combine multiple indexes together for fast access to the underlying table.

Compressed structure is faster to read from disk but takes additional CPU cycles to decompress for access - an uncompressed structure imposes a lower CPU load but requires more bandwidth to read in a short time.

Bitmap indexes can be used very successfully for indexing columns with many thousands of different values.

Disadvantage of Bitmap Indexes

The reason for confining حصرbitmap indexes to data warehouses is that the overhead on maintaining them is enormous ضخم. A modification to a bitmap index requires a great deal more work on behalf of the system than a modification to a b-tree index. In addition, the concurrency for modifications on bitmap indexes is dreadful بغيض.

Bitmap Indexes and Deadlocks

Bitmap indexes are not appropriate for tables that have lots of single row DML operations (inserts) and especially concurrent single row DML operations. Deadlock situations are the result of concurrent inserts as the following example shows: Open two windows, one for Session 1 and one for Session 2

Session 1	Session 2
create table bitmap_index_demo ( value varchar2(20) );
insert into bitmap_index_demo select decode(mod(rownum,2),0,'M','F') from all_objects;
create bitmap index bitmap_index_demo_idx on bitmap_index_demo(value);
insert into bitmap_index_demo values ('M'); 1 row created.
	insert into bitmap_index_demo values ('F'); 1 row created.
insert into bitmap_index_demo values ('F'); ...... waiting ......
ERROR at line 1: ORA-00060: deadlock detected while waiting for resource	insert into bitmap_index_demo values ('M'); ...... waiting ...

Bitmap Join Indexes

A bitmap join index is a bitmap index for the join of two or more tables. For each value in a table column, the index stores the rowid of the corresponding row in the indexed table. In contrast, a st&ard bitmap index is created on a single table.

A bitmap join index is an efficient means of reducing the volume of data that must be joined by performing restrictions in advance. For an example of when a bitmap join index would be useful, assume that users often query the number of employees with a particular job type. A typical query might look as follows:

SELECT COUNT(*)

FROM   employees, jobs

WHERE  employees.job_id = jobs.job_id

&    jobs.job_title = 'Accountant';

The preceding query would typically use an index on jobs.job_title to retrieve the rows for Accountant & then the job ID, & an index on employees.job_id to find the matching rows. To retrieve the data from the index itself rather than from a scan of the tables, you could create a bitmap join index as follows:

CREATE BITMAP INDEX employees_bm_idx

ON     employees (jobs.job_title)

FROM   employees, jobs

WHERE  employees.job_id = jobs.job_id;

As illustrated in Figure 3-2, the index key is jobs.job_title & the indexed table is employees.

Figure 3-2 Bitmap Join Index

Description: Description of Figure 3-2 follows

Description of "Figure 3-2 Bitmap Join Index"

Conceptually, employees_bm_idx is an index of the jobs.title column in the SQL query shown in Example 3-5 (sample output included). The job_title key in the index points to rows in the employees table. A query of the number of accountants can use the index to avoid accessing the employees & jobs tables because the index itself contains the requested information.

Example 3-5 Join of employees & jobs Tables

SELECT jobs.job_title AS "jobs.job_title", employees.rowid AS "employees.rowid"

FROM   employees, jobs

WHERE  employees.job_id = jobs.job_id

ORDER BY job_title;

jobs.job_title                      employees.rowid

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

Accountant                          AAAQNKAAFAAAABSAAL

Accountant                          AAAQNKAAFAAAABSAAN

Accountant                          AAAQNKAAFAAAABSAAM

Accountant                          AAAQNKAAFAAAABSAAJ

Accountant                          AAAQNKAAFAAAABSAAK

Accounting Manager                  AAAQNKAAFAAAABTAAH

Administration Assistant            AAAQNKAAFAAAABTAAC

Administration Vice President       AAAQNKAAFAAAABSAAC

Administration Vice President       AAAQNKAAFAAAABSAAB

In a data warehouse, the join condition is an equijoin (it uses the equality operator) between the primary key columns of the dimension tables & the foreign key columns in the fact table. Bitmap join indexes are sometimes much more efficient in storage than materialized join views, an alternative for materializing joins in advance.

Function-Based Indexes

You can create indexes on functions & expressions that involve one or more columns in the table being indexed. A function-based index computes the value of a function or expression involving one or more columns & stores it in the index. A function-based index can be either a B-tree or a bitmap index.

Uses of Function-Based Indexes For example, suppose you create the following function-based index:

CREATE INDEX emp_total_sal_idx

  ON employees (12 * salary * commission_pct, salary, commission_pct);

The database can use the preceding index when processing queries such as

SELECT   employee_id, last_name, first_name,

         12*salary*commission_pct AS "ANNUAL SAL"

FROM     employees

WHERE    (12 * salary * commission_pct) < 30000

ORDER BY "ANNUAL SAL" DESC;

EMPLOYEE_ID LAST_NAME                 FIRST_NAME           ANNUAL SAL

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

        159 Smith                     Lindsey                   28800

        151 Bernstein                 David                     28500

        152 Hall                      Peter                     27000

        160 Doran                     Louise                    27000

        175 Hutton                    Alyssa                    26400

        149 Zlotkey                   Eleni                     25200

        169 Bloom                     Harrison                  24000

You create the following function-based index on the hr.employees table:

CREATE INDEX emp_fname_uppercase_idx

ON employees ( UPPER(first_name) );

The emp_fname_uppercase_idx index can facilitate queries such as the following:

SELECT *

FROM   employees

WHERE  UPPER(first_name) = 'AUDREY';

A function-based index is also useful for indexing only specific rows in a table. For example, the cust_valid column in the sh.customers table has either I or A as a value. To index only the A rows, you could write a function that returns a null value for any rows other than the A rows. You could create the index as follows:

CREATE INDEX cust_valid_idx

ON customers ( CASE cust_valid WHEN 'A' THEN 'A' END );

Optimization with Function-Based Indexes

The optimizer can use an index range scan on a function-based index for queries with expressions in WHERE clause. The range scan access path is especially beneficial when the predicate (WHERE clause) has low selectivity. In Example 3-6 the optimizer can use an index range scan if an index is built on the expression 12*salary*commission_pct.

A virtual column is useful for speeding access to data derived from expressions. For example, you could define virtual column annual_sal as 12*salary*commission_pct & create a function-based index on annual_sal.

The optimizer performs expression matching by parsing the expression in a SQL statement & then comparing the expression trees of the statement & the function-based index. This comparison is case-insensitive & ignores blank spaces.

Application Domain Indexes

An application domain index is a customized index specific to an application. Oracle Database provides extensible indexing to do the following:

· Accommodate indexes on customized, complex data types such as documents, spatial data, images, & video clips (see "Unstructured Data")

· Make use of specialized indexing techniques

You can encapsulate application-specific index management routines as an indextype schema object & define a domain index on table columns or attributes of an object type. Extensible indexing can efficiently process application-specific operators.

The application software, called the cartridge, controls the structure & content of a domain index. The database interacts with the application to build, maintain, & search the domain index. The index structure itself can be stored in the database as an index-organized table or externally as a file.

Index Storage

Oracle Database stores index data in an index segment. Space available for index data in a data block is the data block size minus block overhead, entry overhead, rowid, & one length byte for each value indexed.

The tablespace of an index segment is either the default tablespace of the owner or a tablespace specifically named in the CREATE INDEX statement. For ease of administration you can store an index in a separate tablespace from its table. For example, you may choose not to back up tablespaces containing only indexes, which can be rebuilt, & so decrease the time & storage required for backups.

Overview of Index-Organized Tables

An index-organized table is a table stored in a variation of a B-tree index structure. In a heap-organized table, rows are inserted where they fit. In an index-organized table, rows are stored in an index defined on the primary key for the table. Each index entry in the B-tree also stores the non-key column values. Thus, the index is the data, & the data is the index. Applications manipulate index-organized tables just like heap-organized tables, using SQL statements.

For an analogy of an index-organized table, suppose a human resources manager has a book case of cardboard boxes. Each box is labeled with a number—1, 2, 3, 4, & so on—but the boxes do not sit on the shelves in sequential order. Instead, each box contains a pointer to the shelf location of the next box in the sequence.

Folders containing employee records are stored in each box. The folders are sorted by employee ID. Employee King has ID 100, which is the lowest ID, so his folder is at the bottom of box 1. The folder for employee 101 is on top of 100, 102 is on top of 101, & so on until box 1 is full. The next folder in the sequence is at the bottom of box 2.

In this analogy, ordering folders by employee ID makes it possible to search efficiently for folders without having to maintain a separate index. Suppose a user requests the records for employees 107, 120, & 122. Instead of searching an index in one step & retrieving the folders in a separate step, the manager can search the folders in sequential order & retrieve each folder as found.

Index-organized tables provide faster access to table rows by primary key or a valid prefix of the key. The presence of non-key columns of a row in the leaf block avoids an additional data block I/O. For example, the salary of employee 100 is stored in the index row itself. Also, because rows are stored in primary key order, range access by the primary key or prefix involves minimal block I/Os. Another benefit is the avoidance of the space overhead of a separate primary key index. Index-organized tables are useful when related pieces of data must be stored together or data must be physically stored in a specific order. This type of table is often used for information retrieval, spatial

Index-Organized Table Characteristics

The database system performs all operations on index-organized tables by manipulating the B-tree index structure. Table 3-4 summarizes the differences between index-organized tables & heap-organized tables.

Table 3-4 Comparison of Heap-Organized Tables with Index-Organized Tables

Heap-Organized Table	Index-Organized Table
The rowid uniquely identifies a row. Primary key constraint may optionally be defined.	Primary key uniquely identifies a row. Primary key constraint must be defined.
Physical rowid in `ROWID` pseudocolumn allows building secondary indexes.	Logical rowid in `ROWID` pseudocolumn allows building secondary indexes.
Individual rows may be accessed directly by rowid.	Access to individual rows may be achieved indirectly by primary key.
Sequential full table scan returns all rows in some order.	A full index scan or fast full index scan returns all rows in some order.
Can be stored in a table cluster with other tables.	Cannot be stored in a table cluster.
Can contain a column of the `LONG` data type & columns of LOB data types.	Can contain LOB columns but not `LONG` columns.
Can contain virtual columns (only relational heap tables are supported).	Cannot contain virtual columns.

Figure 3-3 illustrates the structure of an index-organized departments table. The leaf blocks contain the rows of the table, ordered sequentially by primary key. For example, the first value in the first leaf block shows a department ID of 20, department name of Marketing, manager ID of 201, & location ID of 1800.

Figure 3-3 Index-Organized Table

Description: Description of Figure 3-3 follows

An index-organized table stores all data in the same structure & does not need to store the rowid. As shown in Figure 3-3, leaf block 1 in an index-organized table might contain entries as follows, ordered by primary key:

20,Marketing,201,1800

30,Purchasing,114,1700

Leaf block 2 in an index-organized table might contain entries as follows:

50,Shipping,121,1500

60,IT,103,1400

A scan of the index-organized table rows in primary key order reads the blocks in the following sequence:

1. Block 1

2. Block 2

To contrast data access in a heap-organized table to an index-organized table, suppose block 1 of a heap-organized departments table segment contains rows as follows:

50,Shipping,121,1500

20,Marketing,201,1800

Block 2 contains rows for the same table as follows:

30,Purchasing,114,1700

60,IT,103,1400

A B-tree index leaf block for this heap-organized table contains the following entries, where the first value is the primary key & the second is the rowid:

20,AAAPeXAAFAAAAAyAAD

30,AAAPeXAAFAAAAAyAAA

50,AAAPeXAAFAAAAAyAAC

60,AAAPeXAAFAAAAAyAAB

A scan of the table rows in primary key order reads the table segment blocks in the following sequence:

1. Block 1

2. Block 2

3. Block 1

4. Block 2

Thus, the number of block I/Os in this example is double the number in the index-organized example.

Index-Organized Tables with Row Overflow Area

When creating an index-organized table, you can specify a separate segment as a row overflow area. In index-organized tables, B-tree index entries can be large because they contain an entire row, so a separate segment to contain the entries is useful. In contrast, B-tree entries are usually small because they consist of the key & rowid.

If a row overflow area is specified, then the database can divide a row in an index-organized table into the following parts:

· The index entry

This part contains column values for all the primary key columns, a physical rowid that points to the overflow part of the row, & optionally a few of the non-key columns. This part is stored in the index segment.

· The overflow part

This part contains column values for the remaining non-key columns. This part is stored in the overflow storage area segment.

Secondary Indexes on Index-Organized Tables

A secondary index is an index on an index-organized table. In a sense, it is an index on an index. The secondary index is an independent schema object & is stored separately from the index-organized table.

As explained in "Rowid Data Types", Oracle Database uses row identifiers called logical rowids for index-organized tables. A logical rowid is a base64-encoded representation of the table primary key. The logical rowid length depends on the primary key length.

Rows in index leaf blocks can move within or between blocks because of insertions. Rows in index-organized tables do not migrate as heap-organized rows do (see "Chained & Migrated Rows"). Because rows in index-organized tables do not have permanent physical addresses, the database uses logical rowids based on primary key.

For example, assume that the departments table is index-organized. The location_id column stores the ID of each department. The table stores rows as follows, with the last value as the location ID:

10,Administration,200,1700

20,Marketing,201,1800

30,Purchasing,114,1700

40,Human Resources,203,2400

A secondary index on the location_id column might have index entries as follows, where the value following the comma is the logical rowid:

1700,*BAFAJqoCwR/+

1700,*BAFAJqoCwQv+

1800,*BAFAJqoCwRX+

2400,*BAFAJqoCwSn+

Secondary indexes provide fast & efficient access to index-organized tables using columns that are neither the primary key nor a prefix of the primary key. For example, a query of the names of departments whose ID is greater than 1700 could use the secondary index to speed data access.

Logical Rowids & Physical Guesses

Secondary indexes use the logical rowids to locate table rows. A logical rowid includes a physical guess, which is the physical rowid of the index entry when it was first made. Oracle Database can use physical guesses to probe directly into the leaf block of the index-organized table, bypassing the primary key search. When the physical location of a row changes, the logical rowid remains valid even if it contains a physical guess that is stale.

For a heap-organized table, access by a secondary index involves a scan of the secondary index & an additional I/O to fetch the data block containing the row. For index-organized tables, access by a secondary index varies, depending on the use & accuracy of physical guesses:

· Without physical guesses, access involves two index scans: a scan of the secondary index followed by a scan of the primary key index.

· With physical guesses, access depends on their accuracy:

o With accurate physical guesses, access involves a secondary index scan & an additional I/O to fetch the data block containing the row.

o With inaccurate physical guesses, access involves a secondary index scan & an I/O to fetch the wrong data block (as indicated by the guess), followed by an index unique scan of the index organized table by primary key value.

Bitmap Indexes on Index-Organized Tables

A secondary index on an index-organized table can be a bitmap index. As explained in "Bitmap Indexes", a bitmap index stores a bitmap for each index key.

When bitmap indexes exist on an index-organized table, all the bitmap indexes use a heap-organized mapping table. The mapping table stores the logical rowids of the index-organized table. Each mapping table row stores one logical rowid for the corresponding index-organized table row.

The database accesses a bitmap index using a search key. If the database finds the key, then the bitmap entry is converted to a physical rowid. With heap-organized tables, the database uses the physical rowid to access the base table. With index-organized tables, the database uses the physical rowid to access the mapping table, which in turn yields a logical rowid that the database uses to access the index-organized table. Figure 3-4 illustrates index access for a query of the departments_iot table.

Figure 3-4 Bitmap Index on Index-Organized Table

Description: Description of Figure 3-4 follows

Description of "Figure 3-4 Bitmap Index on Index-Organized Table"

Note:

Movement of rows in an index-organized table does not leave the bitmap indexes built on that index-organized table unusable.

What is Oracle Parallel Query?

Oracle Parallel Query (formerly Oracle Parallel Query Option or PQO) allows one to break-up يفكك a given SQL statement so that its parts can run simultaneously on different processors in a multi-processor machine. Typical operations that can run in parallel are: full table scans, sorts, sub-queries, data loading etc.

Parallel Query can improve performance of certain types of operations dramatically & is commonly used in Decision Support & Data Warehousing applications.

how to get minimum time stamp from a table ? for example :

select owner,max(created),object_name from all_objects

where owner='SCOTT'

group by owner,object_name

having max(created) between '23-feb-2014' and '25-feb-2014'

Dump

exp scott/tiger file=emp.dmp log=emp.log tables=emp rows=yes indexes=no

exp scott/tiger file=emp.dmp tables=(emp,dept)

imp scott/tiger file=emp.dmp full=yes

imp scott/tiger file=emp.dmp fromuser=scott touser=scott tables=dept

Using a parameter file:

exp userid=scott/tiger@orcl parfile=export.txt

... where export.txt contains:

BUFFER=10000000

FILE=account.dmp

FULL=n

OWNER=scott

GRANTS=y

COMPRESS=y

NOTE: If you do not like command line utilities, you can import and export data with the "Schema Manager" GUI that ships with Oracle Enterprise Manager (OEM).

Can one export a subset of a table?

From Oracle 8i one can use the QUERY= export parameter to selectively unload a subset of the data from a table. You may need to escape special chars on the command line, for example: query=\"where deptno=10\". Look at these examples:

exp scott/tiger tables=emp query="where deptno=10"

exp scott/tiger file=abc.dmp tables=abc query=\"where sex=\'f\'\" rows=yes

The following example shows how to export full database

$exp USERID=scott/tiger FULL=y FILE=myfull.dmp

Example of Exporting Schemas

To export Objects stored in a particular schemas you can run export utility with the following arguments

$exp USERID=scott/tiger OWNER=(SCOTT,ALI) FILE=exp_own.dmp

Exporting Individual Tables

To export individual tables give the following command

$exp USERID=scott/tiger TABLES=(scott.emp,scott.sales) FILE=exp_tab.dmp

This will export scott’s emp and sales tables.

Import schema

imp aat2012/aat2012 fromuser=aat2012 touser=aat2012 file=d:\dmp\tour11-09 log=d:\dmp\log.txt

Example Importing Individual Tables

To import individual tables from a full database export dump file give the following command

$imp scott/tiger FILE=myfullexp.dmp FROMUSER=scott TABLES=(emp,dept)

This command will import only emp, dept tables into Scott user and you will get a output similar to as shown below

How to schedule an email in oracle reports?

UTL_MAIL: type procedure in UTL_MAIL 1)SEND (send the message without attachment)

Code

1.  BEGIN

2.  UTL_MAIL.SEND(sender =>'karimuth@amazon.com',recipients=>'karimuthu.bala@gmail.com',message =>'HAPPY BIRTHDAY',subject => 'birthday wishes');

3.  END

4.  2)SEND_ATTACH_RAW(FOR message WITH binary attachment)

Code

1.  BEGIN

2.  UTL_MAIL.SEND_ATTACH_RAW(sender =>'karimuth@amazon.com',recipients=>'karimuthu.bala@gmail.com',message =>'HAPPY BIRTHDAY',subject => 'birthday wishes'

3.  mime_type => 'text/html',attachment => get_image('oracle.gif'),att_inline =>true,att_min_type =>'image/gif',att_filename =>'oracle.gif');

4.  END

3)SEND_ATTACH_VARCHAR2(for messages with text attachment)

Code

1.  UTL_MAIL.SEND_ATTACH_VARCHAR2(sender =>'karimuth@amazon.com',recipients=>'karimuthu.bala@gmail.com',message =>'HAPPY BIRTHDAY',subject => 'birthday wishes'

2.  mime_type => 'text/html',attachment => get_file('test.txt'),att_inline =>flase,att_min_type =>'text/plain',att_filename =>'test.txt');

3.  END

How to get the 25th row of a table

1.  SELECT  * FROM <Table_name> WHERE ROWID=25

How do you find current date and time in oracle?

Syntax: SELECT TO_CHAR (SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "Current_Date" FROM DUAL.

What is bulk copy or BCP in oracle?

BCP or bulk copy tool is one type of command line tool used to import or export data from tables and views oracle 8 but it will not copy structure of data same. Main advantage is fast mechanism for copying data and we can take backup of important data easily

What are the extensions used by oracle reports

Oracle reports are used to make business enable to provide information of all level. REP file and RDF file extensions used by oracle report.

What is Save Points in Oracle database?

SAVE POINTS are used to divide a transaction into smaller parts. It enables rolling back part of a transaction. Maximum of five save points are allowed. Whenever we encounter error we can rollback from the point where we set our SAVEPOINT.This is useful for multistage transaction and conditional transaction where commit and rollback depend on certain condition.

How will you convert string to a date in oracle database?

Syntax : to_date(string, format)

Example: to_date('2012/06/12', 'yyyy/mm/dd') It will return June 12, 2012

What is hash cluster in Oracle?

Hash cluster is one of the techniques to store the table in a hash cluster to improve the performance of data retrieval .we apply hash function on the table row’s cluster key value and store in the hash cluster. All rows with the same hash key value are stores together on disk.key value is same like key of index cluster ,for data retrieval Oracle applies the hash function to the row's cluster key value.

What is Snap shot in Oracle database?

Snapshots are read-only copies of a master table located on a remote node which is periodically refreshed to reflect changes made to the master table.

How to convert report 3.0 into excel use I/O package .

How will you post two independent transactions in forms 6i

Declare the PRAGMA AUTONOMUS_TRANSACTION; in Procedure,Package,Function/Anonymous Block etc

Can we use program unit in a display field to create a formula calculated item?

Yes we can, in WHEN-NEW-RECORD-INSTANCE Trigger.

What are the different types of PL/SQL program units that can be defined and stored in ORACLE database ?
Procedures and Functions,Packages and Database Triggers.

What are the advantages of having a Package ?
Increased functionality (for example,global package variables can be declared and used by any proecdure in the package) and performance (for example all objects of the package are parsed compiled, and loaded into memory once)

What are the uses of Database Trigger ?

A database trigger is procedural code that is automatically executed in response to certain events on a particular table or view in a database. The trigger is mostly used for maintaining the integrity of the information on the database. For example, when a new record (representing a new worker) is added to the employees table, new records should also be created in the tables of the taxes, vacations and salaries.

What is a Procedure ? A Procedure consist of a set of SQL and PL/SQL statements that are grouped together as a unit to solve a specific problem or perform a set of related tasks.

What is a Package ? A Package is a collection of related procedures, functions, variables and other package constructs together as a unit in the database.

Can the default values be assigned to actual parameters? Yes

Oracle forms and Reports

What are the different types of Record Groups?
Query Record Groups
NonQuery Record Groups
State Record Groups

1.	Give the Types of modules in a form?
	- Form - Menu - Library
2.	Write the Abbreviation for the following File Extension
	1. FMB 2. MMB 3. PLL FMB ----- Form Module Binary. MMB ----- Menu Module Binary. PLL ------ PL/SQL Library Module Binary.
3.	What are the design facilities available in forms 4.0?
	Default Block facility. Layout Editor. Menu Editor. Object Lists. Property Sheets. PL/SQL Editor. Tables Columns Browser. Built-ins Browser.
4.	What is a Layout Editor?
	The Layout Editor is a graphical design facility for creating and arranging items and boilerplate text and graphics objects in your application's interface.
5.	What do you mean by a block in forms4.0?
	Block is a single mechanism for grouping related items into a functional unit for storing,displaying and manipulating records.
6.	Explain types of Block in forms4.0?
	Base table Blocks. Control Blocks. 1. A base table block is one that is associated with a specific database table or view. 2. A control block is a block that is not associated with a database table.
7.	What are the options available to refresh snapshots?
	COMPLETE - Tables are completely regenerated using the snapshot's query and the master tables every time the snapshot referenced. FAST - If simple snapshot used then a snapshot log can be used to send the changes to the snapshot tables. FORCE - Default value. If possible it performs a FAST refresh; Otherwise it will perform a complete refresh.
8.	List the Types of tems?
	Text item. Chart item. Check box. Display item. Image item. List item. Radio Group. User Area item.
9.	What is a Navigable item?
	A navigable item is one that operators can navigate to with the keyboard during default navigation, or that Oracle forms can navigate to by executing a navigational built-in procedure.
10.	Can you change the color of the push button in design time?
	No.
11.	What is a Check Box?
	A Check Box is a two state control that indicates whether a certain condition or value is on or off, true or false. The display state of a check box is always either "checked" or "unchecked".
12.	What are the triggers associated with a check box?
	Only When-checkbox-activated Trigger associated with a Check box.
13.	What is a display item?
	Display items are similar to text items but store only fetched or assigned values. Operators cannot navigate to a display item or edit the value it contains.
14.	What is a list item?
	It is a list of text elements.
15.	What are the display styles of list items?
	Poplist, No text Item displayed in the list item. Tlist, No element in the list is highlighted.
16.	What is a radio Group?
	Radio groups display a fixed no of options that are mutually exclusive. User can select one out of n number of options.
17.	How many maximum number of radio buttons can you assign to a radio group?
	Unlimited no of radio buttons can be assigned to a radio group
18.	Can you change the default value of the radio button group at run time?
	No.
19.	What triggers are associated with the radio group?
	Only when-radio-changed trigger associated with radio group
20.	What is a visual attribute?
	Visual Attributes are the font, color and pattern characteristics of objects that operators see and intract with in our application.
21.	What are the types of visual attribute settings?
	Custom Visual attributes Default visual attributes Named Visual attributes.
22.	What is a window?
	A window, byitself, can be thought of as an empty frame. The frame provides a way to intract with the window, including the ability to scroll, move, and resize the window. The content of the window ie. what is displayed inside the frame is determined by the canvas View or canvas-views displayed in the window at run-time.
23.	What are the differrent types of windows?
	Root window, Secondary window.
24.	Can a root window be made modal?
	No.
25.	List the buil-in routine for controlling window during run-time?
	Find_window, get_window_property, hide_window, move_window, resize_window, set_window_property, show_View
26.	List the windows event triggers available in Forms 4.0?
	When-window-activated, when-window-closed, when-window-deactivated, when-window-resized
27.	What built-in is used for changing the properties of the window dynamically?
	Set_window_property
28.	What is a canvas-view?
	A canvas-view is the background object on which you layout the interface items (text-items, check boxes, radio groups, and so on.) and boilerplate objects that operators see and interact with as they run your form. At run-time, operators can see only those items that have been assiged to a specific canvas. Each canvas, in term, must be displayed in a specfic window.
29.	Give the equivalent term in forms 4.0 for the following. Page, Page 0?
	Page - Canvas-View Page 0 - Canvas-view null.
30.	What are the types of canvas-views?
	Content View, Stacked View.
31.	What is the content view and stacked view?
	A content view is the "Base" view that occupies the entire content pane of the window in which it is displayed. A stacked view differs from a content canvas view in that it is not the base view for the window to which it is assigned
32.	List the built-in routines for the controlling canvas views during run-time?
	Find_canvas Get-Canvas_property Get_view_property Hide_View Replace_content_view Scroll_view Set_canvas_property Set_view_property Show_view
33.	What is an Alert?
	An alert is a modal window that displays a message notifies the operator of some application condition
34.	What are the display styles of an alert?
	Stop, Caution, note
35.	Can you attach an alert to a field?
	No
36.	What built-in is used for showing the alert during run-time?
	Show_alert.
37.	Can you change the alert messages at run-time? If yes, give the name of th built-in to chage the alert messages at run-time.
	Yes. Set_alert_property.
40.	What is the built-in function used for finding the alert?
	Find_alert
41.	List the editors availables in forms 4.0?
	Default editor User_defined editors system editors.
42.	What built-in routines are used to display editor dynamicaly?
	Edit_text item show_editor
43.	What is a Lov?
	A list of values is a single or multi column selection list displayed in a pop-up window
44.	Can you attach a lov to a field at design time?
	Yes.
45.	Can you attach a lov to a field at run-time? If yes, give the build-in name.
	Yes. Set_item_proprety
46.	What is the built-in used for showing lov at runtime?
	Show_lov
47.	What is the built-in used to get and set lov properties during run-time?
	Get_lov_property Set_lov_property
48.	What is a record Group?
	A record group is an internal oracle forms data structure that has a simillar column/row frame work to a database table
49.	What are the different types of a record group?
	Query record group Static record group Non query record group
50.	Give built-in routine related to record groups?
	Create_group (Function) Create_group_from_query(Function) Delete_group(Procedure) Add_group_column(Function) Add_group_row(Procedure) Delete_group_row(Procedure) Populate_group(Function) Populate_group_with_query(Function) Set_group_Char_cell(procedure)
51.	What is the built_in routine used to count the no of rows in a group?
	Get_group _row_count
52.	List system variables available in forms 4.0, and not available in forms 3.0?
	System.cordination_operation System Date_threshold System.effective_Date System.event_window System.suppress_working
53.	System.effective_date system variable is read only True/False
	False
54.	What is a library in Forms 4.0?
	A library is a collection of Pl/SQL program units, including user named procedures, functions & packages
55.	Is it possible to attach same library to more than one form?
	Yes
56.	Explain the following file extention related to library?
	.pll,.lib,.pld The library pll files is a portable design file comparable to an fmb form file The library lib file is a plat form specific, generated library file comparable to an fmx form file The pld file is Txt format file and can be used for source controlling your library files
57.	How do you pass the parameters from one form to another form?
	To pass one or more parameters to a called form, the calling form must perform the following steps in a trigger or user named routine excute the create_parameter_list built_in function to programatically. Create a parameter list to execute the add parameter built_in procedure to add one or more parameters list. Execute the call_form, New_form or run_product built_in procedure and include the name or id of the parameter list to be passed to the called form.
58.	What are the built-in routines is available in forms 4.0 to create and manipulate a parameter list?
	Add_parameter Create_Parameter_list Delete_parameter Destroy_parameter_list Get_parameter_attr Get_parameter_list set_parameter_attr
59.	What are the two ways to incorporate images into a oracle forms application?
	Boilerplate Images Image_items
60.	How image_items can be populate to field in forms 4.0?
	A fetch from a long raw database column PL/Sql assignment to executing the read_image_file built_in procedure to get an image from the file system.
61.	What are the triggers associated with the image item?
	When-Image-activated(Fires when the operator double clicks on an image Items) When-image-pressed(fires when the operator selects or deselects the image item)
62.	List some built-in routines used to manipulate images in image_item?
	Image_add Image_and Image_subtract Image_xor Image_zoom
63.	What are the built_in used to trapping errors in forms 4?
	Error_type return character Error_code return number Error_text return char Dbms_error_code return no. Dbms_error_text return char
64.	What is a predefined exception available in forms 4.0?
	Raise form_trigger_failure
65.	What are the menu items that oracle forms 4.0 supports?
	Plain, Check,Radio, Separator, Magic

Pages

Tuesday, June 9, 2015

Oracle SQL PLSQL Interview Questions

Implicit