What is Cursor Advantages and Disadvantages?

Advantages of using Cursor: 

1. Using Cursor we can perform row by row processing so we can perform row wise validation or operations on each row.

2.Cursors can provide the first few rows before the whole result set is assembled. Without using cursors, the entire result set must be delivered before any rows are displayed by the application. So using cursor, better response time is achieved. 

3. If we make updates to our without using cursors in your application then we must send separate SQL statements to the database server to apply the changes. This can cause the possibility of concurrency problems if the result set has changed since it was queried by the client. In turn, this raises the possibility of lost updates. So using cursor, better concurrency Control can be achieved.

4.Cursors can be faster than a while loop but at the cost of more overhead.

Disadvantages of using Cursor: 

Cursor in SQL is temporary work area created in the system memory, thus it occupies memory from your system that may be available for other processes. So occupies more resources and temporary storage. 

Each time when a row is fetched from the cursor it may result in a network round trip. This uses much more network bandwidth than the execution of a single SQL statement like SELECT or DELETE etc that makes only one round trip. 

Repeated network round trips can degrade the speed of the operation using the cursor.

What is the difference between implicit and explicit cursors in Oracle?

Implicit Cursor:

Implicit cursors are automatically created and used by Oracle each time a select statement is issued. If an implicit cursor is used, the Database Management System (DBMS) will perform the open, fetch and close operations automatically. Implicit cursors should be used only with SQL statements that return a single row. If the SQL statement returns more than one row, using an implicit cursor will introduce an error. An implicit cursor is automatically associated with each Data Manipulation Language (DML) statements, namely INSERT, UPDATE and DELETE statements. Also, an implicit cursor is used to process SELECT INTO statements. When fetching data using implicit cursors NO_DATA_FOUND exception can be raised when the SQL statement returns no data. Furthermore, implicit cursors can raise TOO_MANY_ROWS exceptions when the SQL statement returns more than one row.

Explicit Cursor:

 An explicit cursor can be thought of as a pointer to a set of records and the pointer can be moved forward within the set of records. Explicit cursors provide the user the complete control over opening, closing and fetching data. Also, multiple rows can be fetched using an explicit cursor. Explicit cursors can also take parameters just like any function or procedure so that the variables in the cursor can be changed each time it is executed. In addition, explicit cursors allow you to fetch a whole row in to a PL/SQL record variable. When using an explicit cursor, first it needs to be declared using a name. Cursor attributes can be accessed using the name given to cursor. After declaring, cursor needs to be opened first. Then fetching can be started. If multiple rows need to be fetched, the fetching operation needs to be done inside a loop. Finally, the cursor needs to be closed.

Difference Between Explicit Cursor and Implicit Cursor

The main difference between the implicit cursor and explicit cursor is that an explicit cursor needs to be defined explicitly by providing a name while implicit cursors are automatically created when you issue a select statement. Furthermore, multiple rows can be fetched using explicit cursors while implicit cursors can only fetch a single row. Also NO_DATA_FOUND and TOO_MANY_ROWS exceptions are not raised when using explicit cursors, as opposed to implicit cursors. In essence, implicit cursors are more vulnerable to data errors and provide less programmatic control than explicit cursors. Also, implicit cursors are considered less efficient than explicit cursors.

Difference between SYS_REFCURSOR and REF CURSOR

SYS_REFCURSOR is Weak reference cursor whereas ref cursor is strong reference cursor.

SYS_REFCURSOR do not have return type whereas ref cursor have return type.

We need to delcare variable of that type in ref cursor .In sys_refcursor no need of variable delclaration.

Example of Weak Ref Cursor

declare    

    c1 SYS_REFCURSOR;

    ename varchar2(10);

    sal number;

begin

    open c1 for select ename, sal from scott.emp;

    LOOP 

        FETCH c1 into ename, sal;

            EXIT WHEN c1%NOTFOUND;

        dbms_output.put_line('Ename: ' || ename || ', Salary: ' || sal);

    END LOOP;

    close c1;    

end;

/

Example of Strong Ref Cursor

Declare

Type Refcursor is ref cursor return emp%rowtype;

C1 refcursor;

E Emp%rowtype;

Begin

 Open C1 for select * from emp

 loop

 fetch c1 ino  E;

 exit when c1%NOTFOUND;

 dbms_output_put_line(e.first_name||e.sal);

 end loop;

END;

Difference Between Cursor And Ref Cursor

Lets first check the basic example of Cursor and Ref Cursors. In this post Cursor means PL/SQL Cursors only.

Example of Cursor:

declare    

    cursor c1 is select ename, sal from scott.emp;

begin    

    for c in c1 

    loop

        dbms_output.put_line('Ename: ' || c.ename || ', Salary: ' || c.sal);

    end loop;

end;

/

Example of Ref Cursor

declare    

    c1 SYS_REFCURSOR;

    ename varchar2(10);

    sal number;

begin

    open c1 for select ename, sal from scott.emp;

    LOOP 

        FETCH c1 into ename, sal;

            EXIT WHEN c1%NOTFOUND;

        dbms_output.put_line('Ename: ' || ename || ', Salary: ' || sal);

    END LOOP;

    close c1;    

end;

/

Technically, They are both cursors and can be processed in the same fashion and at the most basic level, they both are same. There are some important differences between regular cursors and ref cursors which are following: 

1) A ref cursor can not be used in CURSOR FOR LOOP, it must be used in simple CURSOR LOOP statement as in example.

2) A ref cursor is defined at runtime and can be opened dynamically but a regular cursor is static and defined at compile time.

3) A ref cursor can be passed to another PL/SQL routine (function or procedure) or returned to a client. A regular cursor cannot be returned to a client application and must be consumed within same routine.

4) A ref cursor incurs a parsing penalty because it cannot cached but regular cursor will be cached by PL/SQL which can lead to a significant reduction in CPU utilization.

5) A regular cursor can be defined outside of a procedure or a function as a global package variable. A ref cursor cannot be; it must be local in scope to a block of PL/SQL code.

6) A regular cursor can more efficiently retrieve data than ref cursor. A regular cursor can implicitly fetch 100 rows at a time if used with CURSOR FOR LOOP. A ref cursor must use explicit array fetching.

My recommendation on ref cursors:

Use of ref cursors should be limited to only when you have a requirement of returning result sets to clients and when there is NO other efficient/effective means of achieving the goal.

Explain briefly about Cursor :

Cursor is  private area which hold information of SQL statement.

A cursor can hold more than    one row, but can process only one row at a time. The set of rows the cursor holds is called the active    set.For processing SQL statement ,oracle create area of memory know as context area. A cursor in nothing  but pointer to active data set. When Cursor is declared ,a pointer is returned which initially point to nothing. when cursor is opened , appropriate memory is allocated.When cursor is closed the memory allocated for cursor is released.

Difference between cursor for loop and for loop, which is faster?

Cursor for loop is faster than for loop.

Cursor for loop implicitly declare %rowtype variables,its used index,

open cursor ,fetch records from active dataset and load records into loop variables and close cursor

automatically.

In For we need to explicitly declare %rowtype,open cursor,fetch reocords and load records into loop variables

How can you declare the cursor in proc.?

Cursor <Cursor_Name> Is

Select <Column_Name)  From <Table_Name>

How do we process records in cursor?

We Declare cursor,Open ,Fetch cursor records into Variable and Close the cursor

How to find how many records it fetched?

%rowcount attribute of cursor tells how many records fteched.

Tell me some restrictions on cursor variables?

Cursor variables are subject to the following restrictions; note that Oracle may remove some of these in future releases.

Cursor variables cannot be declared in a package because they do not have a persistent state.

You cannot use RPCs (remote procedure calls) to pass cursor variables from one server to another.

If you pass a cursor variable as a bind variable or host variable to PL/SQL, you will not be able to fetch from it from within the server unless you also open it in that same server call.

The query you associate with a cursor variable in an OPEN FOR statement cannot use the FOR UPDATE clause. (This is allowed in Oracle9i and above.)

You cannot test for cursor variable equality, inequality, or nullity using comparison operators.

You cannot assign NULLs to a cursor variable. Attempts to do so will result in a PLS-00382 Expression is of wrong type error message.

Database columns cannot store cursor variable values. You will not be able to use REF CURSOR types to specify column types in statements to CREATE TABLEs.

The elements in a nested table, associative array, or VARRAY cannot store the values of cursor variables. You will not be able to use REF CURSOR types to specify the element type of a collection.

What are cursor attributes?

1.%Found

2.%notfound.

3.%isopen

4.%rowcount

What is the purpose of FOR UPDATE?

The SELECT FOR UPDATE statement allows you to lock the records in the cursor result set.

You are not required to make changes to the records in order to use this statement. 

The record locks are released when the next commit or rollback statement is issued.

CURSOR cursor_name

IS

   select_statement

   FOR UPDATE [OF column_list] [NOWAIT];

   

   WHERE CURRENT OF & FOR UPDATE

The WHERE CURRENT OF clause is used in some UPDATE and DELETE statements.

The WHERE CURRENT OF clause in an UPDATE or DELETE statement states that the most recent row fetched from the table should be updated or deleted. We must declare the cursor with the FOR UPDATE clause to use this feature.

Inside a cursor loop, WHERE CURRENT OF allows the current row to be directly updated.

When the session opens a cursor with the FOR UPDATE clause, all rows in the return set will hold row-level exclusive locks. Other sessions can only query the rows, but they cannot update, delete, or select with FOR UPDATE.

Oracle provides the FOR UPDATE clause in SQL syntax to allow the developer to lock a set of Oracle rows for the duration of a transaction.

The syntax of using the WHERE CURRENT OF clause in UPDATE and DELETE statements follows:

WHERE [CURRENT OF cursor_name | search_condition]

The following example opens a cursor for employees and updates the commission, if there is no commission assigned based on the salary level.

PL/SQL is a procedural language that Oracle developed as an extension to standard SQL to provide a way to execute procedural logic on the database. It was developed by Oracle Corporation in the early 90's to enhance the capabilities of SQL.PL/SQL program units are compiled by the Oracle Database server and stored inside the database.  And at run-time, both PL/SQL and SQL run within the same server process, bringing optimal efficiency. 

               PL/SQL automatically inherits the robustness, security, and portability of the Oracle Database.You can use PL/SQL to encapsulate business rules and other complicated logic. It provides for modularity and abstraction. You can use it in database triggers to code complex constraints, which enforce database integrity; to log changes; and to replicate data. PL/SQL can also be used with stored procedures and functions to provide enhanced database security

PLSQL Block Structure:

The anonymous block has three basic sections that are the declaration, execution, and exception handling. Only the execution section is mandatory and the others are optional.

                                The declaration section allows you to define data types, structures, and variables. You often declare variables in the declaration section by giving them names, data types, and initial values.

                            The execution section is required in a block structure and it must have at least one statement. The execution section is the place where you put the execution code or business logic code. You can use both procedural and SQL statements inside the execution section.

                              The exception handling section is starting with the EXCEPTION keyword. The exception section is the place that you put the code to handle exceptions. You can either catch or handle exceptions in the exception section.

Notice that the single forward slash (/) is a signal to instruct SQL*Plus to execute the PL/SQL block

Syntax:

[DECLARE]

   Declaration statements;

BEGIN

   Execution statements;

  [EXCEPTION]

      Exception handling statements;

END;

/

Nested Block

To nest a block means to embed one or more PL/SQL blocks inside another PL/SQL block that provide you with a better control over program execution and exception handling.

Let’s take a look at the following example:

SET SERVEROUTPUT ON SIZE 1000000;

DECLARE
  n_emp_id EMPLOYEES.EMPLOYEE_ID%TYPE := &emp_id1;
BEGIN
  DECLARE
    n_emp_id employees.employee_id%TYPE := &emp_id2;
    v_name   employees.first_name%TYPE;
  BEGIN
    SELECT first_name
    INTO v_name
    FROM employees
    WHERE employee_id = n_emp_id;

    DBMS_OUTPUT.PUT_LINE('First name of employee ' || n_emp_id || 
                                       ' is ' || v_name);
    EXCEPTION
      WHEN no_data_found THEN
        DBMS_OUTPUT.PUT_LINE('Employee ' || n_emp_id || ' not found');
  END;
END;
/
We have a PL/SQL block that is nested inside another PL/SQL block in the above example. The outer PL/SQL block is called parent block or enclosing block and the inner PL/SQL block is known as child block, nested block or enclosed block.

If you take a look at the code carefully, you will see that we have two variables with the same name n_emp_id in the declaration section of both parent and child blocks. This is allowed in this scenario. The question here is which variable does the SELECT statement accepts? If you execute the code in SQL*PLUS you will see that the SELECT statement will accept the variable in the child block. Why? Because PL/SQL gives the first preference to the variable inside its own block. If the variable is not found, PL/SQL will search for the variable in the parent block and resolve it. If no such variable exists, PL/SQL will issue an error. In this case, the  v_emp_id variable in the child block overrides the variable in the parent block.

Notice that it is not good practice to have several variables that have the same name in different blocks. We take this example for the sake of demonstration only.

PL/SQL Block Label

So what if you want to refer to the variable in the parent block inside the child block in the above example? PL/SQL provides you with a feature called block label that you can qualify all references to variables inside the block via a label.

To label a block, you just need to provide a label name before the declaration section as follows:

<<block_label>>
DECLARE
...
BEGIN
...
END;

Then, you can refer to a variable inside the block by using a dot notation ( .) as below:

block_label.variable_name;

The following is a simple example of using block label:

SET SERVEROUTPUT ON SIZE 1000000;
<<label>>
DECLARE
  v_name varchar2(25) := 'Maria';
BEGIN
  DBMS_OUTPUT.PUT_LINE(label.v_name);
END;
/

Index are created on one or more column of a table.After it is created,an index is automatically maintained and used by oracle
Oracle server uses two method to locate the desired information.
1.Table scan: In table scan oracle scans the entire table to locate desired information.
2.Index Scan: An index scan oracle uses index to locate place that hold desired information and then jump to that place to get required
 information.This is much Faster.

Can you create an index on frequently updated column?

No We can not Create index on Frequently update Column.
It  reduce Performance.

Difference between btree and bitmap index

A Internally, a bitmap and a btree indexes are very different, but functionally 
they are identical in that they serve to assist Oracle in retrieving rows faster than a full-table scan. 
The basic differences between b-tree and bitmap indexes include:

1.The bitmap index includes the "bitmap" keyword.  The btree index does not say "bitmap".
2.The bitmap index is generally for columns with lots of duplicate values (low cardinality), while b-tree indexes are best for high cardinality columns.
3.A b-tree index has index nodes (based on data block size).A bitmap index looks like this, a two-dimensional array with zero and one (bit) values
4.B-tree Index is userful for OLTP, where as Bitmap Index is useful for Dataware Housing.
5.B-tree index updates on key values has relatively inexpensive, where as Bitmap index has more expensive.

Difference between Clustered Index and Non Clustered Index?

The concept behind indexes is to change the order of the data (clustered index) or to add metadata (non-clustered index) for improving the performance of queries.

Clustered indexes

1. Physically stored in order (ascending or descending)
2. Only one per table
3. When a primary key is created a clustered index is automatically created as well.
4. If the table is under heavy data modifications or the primary key is used for searches, a clustered index on the primary key is recommended.
5. Columns with values that will not change at all or very seldom, are the best choices.
6. For use on columns that are frequently searched for ranges of data
7. For use on columns with low selectivity

Non-clustered indexes

1. Up to 249 nonclustered indexes are possible for each table or indexed view.
2. The clustered index keys are used for searching therefore clustered index keys should be chosen with a minimal length.
3. Covered queries (all the columns used for joining, sorting or filtering are indexed) should be non-clustered.
4. Foreign keys should be non-clustered.
5. If the table is under heavy data retrieval from fields other than the primary key, one clustered index and/or one or more non-clustered indexes should be created for the column(s) used to retrieve the data.
6. For use on columns that are searched for single values
7. For use on columns with high selectivity 

Have you ever worked on indexes, what is the default index? Why we can create a Function based index?

There  are times when even though an index exits,oracle doesn't use it and instead follows table scan.
This is usually happen when index created on a column,but the SQL query references that column with Function
or arithmetic expression.
For example, an index is created on the city column of the customer table and following query is executed.

select * from customer where upper(city)='MUMBAI';

above query is references the city column along with upper function and hence oracle doesn't use index So we create function based index.

Create index I4 on customer(upper(City));

What are the disadvantages of indexes in Oracle?

1.They increase the disk space requirements of your database
2.They slow down dml (i.e. inserts, updates and deletes)
3.They increase the maintenance requirements of your Oracle database
4.They may make your queries slower instead of faster
How many types of indexes?

  1.B-tree Index:
     a.Simple Index
     b.Composite Index
     c.Unique Index
     d.Function Based Index
  2.Bitmap Index:
   
How does index work internally?

1.When an Index is created on table, Oracle internally forms a two dimensional matrix that contain Data extracted from the column on which index is created and physical address of the record(rowid).

2.When an SQL query that has a where clause based on the column on which index is fired,oracle the find value in index and locate the record in the table using ROWID.
   
 When index is created on table??

 1.Column frequently access in where clause or in a join condition.
 2.Column contain Wide range of values.
 3.Contain Large number of values.
 4.Two or more columns are frequently used together in where clause or join condition
  
 How to know oracle use my index or not?

 We can EXPLAIN PLAN to check oracle used Index or not

How to rebuild index, syntax of rebuild index?

ALTER INDEX index_name REBUILD

How to see invalid indexes?

select * from user_objects
where object_type = 'INDEX'
and status ='INVALID

Some columns having indexes, How to know which columns having indexes in a table?

SELECT index_name, column_name, column_position 
FROM user_ind_columns
WHERE table_name='MYTABLENAME' 
ORDER BY index_name, column_position

What is global and local index?

Global and Local Index partitioning with Oracle
The first partitioned index method is called a LOCAL partition. A local partitioned index creates a one-for-one match between the indexes and the partitions in the table. Of course, the key value for the table partition and the value for the local index must be identical. The second method is called GLOBAL and allows the index to have any number of partitions.

The partitioning of the indexes is transparent to all SQL queries. The great benefit is that the Oracle query engine will scan only the index partition that is required to service the query, thus speeding up the query significantly. In addition, the Oracle parallel query engine will sense that the index is partitioned and will fire simultaneous queries to scan the indexes.

Local partitioned indexes

Local partitioned indexes allow the DBA to take individual partitions of a table and indexes offline for maintenance (or reorganization) without affecting the other partitions and indexes in the table.

In a local partitioned index, the key values and number of index partitions will match the number of partitions in the base table.


CREATE INDEX year_idx
on all_fact (order_date)
LOCAL
(PARTITION name_idx1),
(PARTITION name_idx2),
(PARTITION name_idx3);

Oracle will automatically use equal partitioning of the index based upon the number of partitions in the indexed table. For example, in the above definition, if we created four indexes on all_fact, the CREATE INDEX would fail since the partitions do not match. This equal partition also makes index maintenance easier, since a single partition can be taken offline and the index rebuilt without affecting the other partitions in the table.

Global partitioned indexes

A global partitioned index is used for all other indexes except for the one that is used as the table partition key. Global indexes partition OLTP (online transaction processing) applications where fewer index probes are required than with local partitioned indexes. In the global index partition scheme, the index is harder to maintain since the index may span partitions in the base table.

For example, when a table partition is dropped as part of a reorganization, the entire global index will be affected. When defining a global partitioned index, the DBA has complete freedom to specify as many partitions for the index as desired.

Now that we understand the concept, let's examine the Oracle CREATE INDEX syntax for a globally partitioned index:

CREATE INDEX item_idx
on all_fact (item_nbr)
GLOBAL
(PARTITION city_idx1 VALUES LESS THAN (100)),
(PARTITION city_idx1 VALUES LESS THAN (200)),
(PARTITION city_idx1 VALUES LESS THAN (300)),
(PARTITION city_idx1 VALUES LESS THAN (400)),
(PARTITION city_idx1 VALUES LESS THAN (500));

Here, we see that the item index has been defined with five partitions, each containing a subset of the index range values. Note that it is irrelevant that the base table is in three partitions. In fact, it is acceptable to create a global partitioned index on a table that does not have any partitioning


A global Index in a single partition may correspond to multiple table partitions. They are created with the GLOBAL keyword and this the global clause allows you to create a non-partitioned index.
Global indexes may perform uniqueness checks faster than local (partitioned) indexes.Also you cannot create global indexes for hash partitions or subpartitions.
Ex:
SQL> CREATE INDEX invoices_idx
ON COST_TABLE(invoice_date) 
GLOBAL PARTITION BY RANGE (invoice_date)
(PARTITION invoices01 VALUES LESS THAN 
(TO_DATE('01/04/2001', 'DD/MM/YYYY')) TABLESPACE users,
PARTITION invoices02 VALUES LESS THAN 
(TO_DATE('01/07/2001', 'DD/MM/YYYY')) TABLESPACE users);

Similar to table partitions, it is possible to move them from one device to another. But unlike table partitions, movement of index partitions requires individual reconstruction of the index or each partition (only in the case of global index).
Ex:
SQL> alter index stud_ind rebuild partition p2
Index partitions cannot be dropped manually.They are dropped implicitly when the data they refer to is dropped from the partitioned table.

What is local index


Local partitioned indexes are easier to manage than other types of partitioned indexes. They also offer greater availability and are common in DSS environments.
In the local prefixed index the partition key is specified on the left prefix. Local keyword tells oracle to create a separte index for each partition.Local prefixed indexes can be unique or non unique and is easier to manage
Ex:CREATE INDEX invoices_idx ON invoices (invoice_date)
LOCAL (PARTITION invoices_q1 TABLESPACE users,  
PARTITION invoices_q2 TABLESPACE users);

What is reverse key index?

In the Index leaf block,Oracle store the index key value and rowid.
Assume that there is unique index created on custid,suppose 3 individual conurrently hit the database so insert row with customer id 102,103,104 then index entries store in same leaf block which cause buffer busy wait.If index is reverse unique index then the entries will be stored in different block

Create index ind on customer(cust_id) reverse;

What is the syntax of organization table index?

Create table temp1(Slno number(3) Not null,
             constraint pk_temp1
        primary key(Slno))
            organization index; 

What is unique index?

Unique index doesn't allow duplicate values into the column on which index is created.
Unique index is created using keyword UNIQUE.

When do we create bitmap ?

Bitmap indexes are meant to be used on low cardinality columns. A low cardinality column just means that the column has relatively few unique values. For example, a column called Sex which has only “Male” and “Female” as the two possible values is considered low cardinality because there are only two unique values in the column.Bitmap index useful when data is not modified by concurrent transaction.
It used is data warehouse.

Oracle uses integrity constraint to prevent invalid data entry into base tables of the database.
You can define integrity constraint to enforce business rule that are associated with information in a database.
If any one of the result of a DML statement execution violate an integrity constraint,oracle rollback the statement and return error.
Business rule specify condition and relationship that must always be true or false
For example each company define its own policy about salaries,employee number.inventory  tracking
and so on.For example Salary Column should not have numeric value greter than 10,0000.

Type of Constraints
1.Not Null constraint
2.Unique Key Constraint
3.Primary Key constraint
4.Foreign Key Constraint
5.Check Constraint

Can foreign key column accept null values and duplicate values?

 YES 

Can we create a primary key constraint on 2 columns and foreign key on other column is it possible?

 We can create composite Primary key on 2 columns and foreign key on other  column.

Can we use truncate and delete command to disable the referential integrity constraint?

 No We can not use tru and delete command to disable the referential integrity
   
Can you modify a null column to not null column, if table has some data?
      
 YES,We can use alter table command.
 ALTER TABLE mytable MODIFY mycolumn NOT NULL
     
Can you put more than one constraint to a column?

 YES.We can put more than one constraint on column
    
Difference between column level constraints & table level constraints:

 Column Level: In this method we are defining constraint for individual column  That is when ever  we are creating  a column we are specify the constraint  type Composite key can not be defined at column level
  
 syntax:  Create table Sample (SR_NO number primary key);
   
 Table Level: In this method we are defining constraint on group of columns that  is first we defining all columns and at the last we must constraint type on group  of column.Not null constraint can not be defined at table level
 
 Create table Sample (SR_NO number, primary key(SR_NO));
      
 Can you use sysdate in check constraints?Why? 

 No, you can't use sysdate in check constraints. 

 All rows in a table for an enabled constraint must return true for its expression. 
 But sysdate is non-deterministic. You can get a different result every time you  call it. So the outcome (true/false) can (will) change over time. So Oracle can't  guarantee that the expression is always true for every row. 

Define primary key and foreign key in Oracle

Primary Key

• Primary key does't allow duplicate and null Values.
• Primary ley is combination of UNIQUE and NOT NULL.
• ONLY ONE PRIMARY key is allowed per tabel.
• Primary key can be declared for combination of columns then it is called Composite Primary Key.
• Composite Key Consist of 32 Column.
• A table can have only one primary Key.
• Primary can not be implemented on column having long,lob,long raw,varray,nested table,object,bfile,timestamp with timezone

Syntax; 

<column name ><data type>(<size>) primary key  

Foreign Key:

• Foreign Key is used to refer the parent table primary key column which does not allow null value.
• The foreign key constraint provide referential integrity rules (either within table or between table) i.e we can place value in table B if value exist in table A
• Foreign key is attached to Child table.

      Foreign Key(Column name1,column name2)  references parent table(                 column1,column2);

Difference between primary and unique 

1,We can create  one primary key to table whereas we can create more then one unique key on table.
2.Primary key do not allow null values whereas unique allow null value
3.Primary key created using  keyword PRIMARY whereas unique key created using UNIQUE.

Have you ever create sequence on primary key column?

No.

How to add a Foreign key in existing table?

We can add foreign key on existing table using ALTER TABLE Command.

ALTER TABLE <table name> add foreign key(child_column_name) references parent table(primary key column);

How to add foreign key in a table after the creation?

Createt table emp(eno number,ename varchar2(100),sal number(5),foreign key(deptno) references dept1(depno);

Explain integrity constraint violated, and there are no records in child table then can we delete parent table records?

Oracle uses integrity constraint to prevent invalid data entry into base tables of the database.
You can define integrity constraint to enforce business rule that are associated with information in a database.
If any one of the result of a DML statement execution violate an integrity constraint,oracle rollback the statement and return error.
Business rule specify condition and relationship that must always be true or false
For example each company define its own policy about salaries,employee number.inventory  tracking
and so on.For example Salary Column should not have numeric value greter than 10,0000.

If there are no records in child table then can we delete parent table records.

I have create a composite primary key on two columns and inserting on value and null value, is it possible, and create a unique index on other columns, inserting 3 rows and inserting 3 null value  on unique column, is it possible

Its not Possible when  when we try insert  value and null value on composite column.
we can insert null values into unique key column

If you created primary key which index created and if you created index which constraint created automatically?.

When  we create Primary Key on column,Unique index automatically created.
When We create index on column, Unique key create automatically 

Hello Everyone,

Thank you for visiting my blog. I hope you will find my blog useful and your valuable feedback would be most welcome.

I am Rahul Hari Panari a "Certified Oracle Developer" with master degree in computer application.
I have 2+ years of experience in the field of Oracle Development, 
Database on Linux as well as Windows Environment.
Currently working in Indus Software Pvt. Ltd. as an Analyst.
  
Through this Blog, I am trying to post mostly asked Oracle SQL and PLSQL interview questions.

What is Materialized View?

Materialized View is a database object that contain result of query.Materialized view are usually choice when creating summary
tables on aggregate of data.Materialized view does not contain up-to-the-minute information. When ordinary view is queried the data retrieved include changes made up to the last committed transaction.However,when an materialized view is queried the date retried would be at  a state when view was created or last refreshed.

A materialized View is static view that hold data in it.
Materialized view does not support DML operations on it.
To create materialized view you should have permission in schema.
It is used to maintain historic data
It is used for data analysis and reporting purpose
Materialized are mainly created to over come the performance issue and to store historic data.
It is equivalent to snapshot
Materialized views are used in data warehouse
They are used to increase speed of queries on very large database.

Syntax:

     Create Materialized view <schema-Name>
     Refresh[Fast|Complete|FORCE] [ON DEMAND|COMMIT] 
     Start with date
     next date
     with [primary key|rowid]



I have created a Materialized View after then rename a one column on base table, Materialized View updated or not.

 Materialized view is not updated.

I have created a simple view but there is no base table, what happens.
  
   ERROR at line 1:
   ORA-00942: table or view does not exist

   
I have one table I created view and materialized view on a table  If I delete a table can I see the view and materialized view?
  
   We can not see View but we can see materialized view.
   Ordinary View is not stored in Database but Materialized view Stored in
   Database  



When m_views are created mainly?If base table deleted what happen m_views? 

M_view mainly created for overcome  performance issue and to historic data.
It used in data warehouse for data analysis and Reporting Purpose.
If we delete base table, we can access Materialized View.

After inserting data into materialized view and refresh materialized view then what happens on materialized view?

Any Changes made on base tables will reflect  on Materialized view.


What is the diff between dblink & materialized view?(incase of accessing)?

Recently one of my colleague  had a weird error. He was trying to build a materialized view over some tables in his local database and some tables in his remote database using database links the sql to create the view ran fine and provided the results as expected, but when put inside a materialized view statement complained with ORA-00942 errors. Lets say the two databases in question are local and remote, so the sql to create the materialized view to load immediately and refresh everyday is


CREATE MATERIALIZED VIEW MV_CUSTOMERBALANCE
BUILD IMMEDIATE
REFRESH FORCE START WITH ROUND(SYSDATE) + 23/24
NEXT SYSDATE + 1
AS
SELECT customer.name , account.balance, accounttype.name
FROM customer , account@remotedb account, accounttype@remotedb accounttype
WHERE
customer.id = account.customerid
AND account.accounttyppeid = accounttype.id

Oracle started to complain when creating the above materialized view issuing an error ORA-00942: table or view does not exist, but the SQL without the create materialized view command ran fine giving the expected results.


SELECT customer.name , account.balance, accounttype.name
FROM customer , account@remotedb account, accounttype@remotedb accounttype
WHERE
customer.id = account.customerid
AND account.accounttyppeid = accounttype.id
/
After some searching around and experimenting I found, in the create materialized view statement the database link name can be used only once, which meant we can only use the “remotedb” name once, we got around this restriction by creating two database links to the remote database as REMOTEACCOUNT and REMOTEACCOUNTTYPE and using them in the creation of the materialized view as shown below.


CREATE MATERIALIZED VIEW MV_CUSTOMERBALANCE
BUILD IMMEDIATE
REFRESH FORCE START WITH ROUND(SYSDATE) + 23/24
NEXT SYSDATE + 1
AS
SELECT customer.name , account.balance, accounttype.name
FROM customer , account@remoteaccount account, accounttype@remoteaccounttype accounttype
WHERE
customer.id = account.customerid
AND account.accounttyppeid = accounttype.id
/

What are  types of refreshing methods.
  
 Refresh can be done in two ways :

 1.Complete Refresh:

  By default materialized view use complete refresh method,in this method           rowid's are recreated When we are refreshing materialized view.This method     degrade performance of application.

   Create Materialized view mv1
   refresh complete
   AS
   Select deptno,sum(sal) from emp group by deptno

   We can refresh materialized view using below package
   exec dbms_mview.refresh('view_name');


 2.Fast Refresh  :
 
   Fast Refresh means since last refresh i.e since last refresh changes made in        base tables are applied to materialized but its not completely recreated.In this    method rowid's are not created 
 
    Before creating fast refresh,we must create materialized log on base tables
           
     Create materialized view logs on emp

     Create Materialized view mv1
     refresh fast
     AS
     Select deptno,sum(sal) from emp group by deptno
     

Define view

View is Virtual table derived from one or more base tables.View is not stored in database so its do not occupy any space .It just logical representation of data. Whatever DML operations  performed on view it affect base tables.

       

Can we Create index on view?

       No

How To check view exits or not?
     

 select  * from user_views where view_name='View_Name';

Can we update complex view?if yes Then How to Update Complex View?
           

An instead of triggers transparent way of modifying a view that can not be modified directly trough DML statement such insert,update,delete.
An instead of trigger defined on view is fired when an insert ,update and delete statement executed against view.
When fired,the instead of triggers modify underlying base tables appropriately.
An instead of trigger fired on view is always row level trigger that is fired when for each row of view 

Example : 

Create view EmpDpt as
select e.empno,e.ename,e.sal,d.deptno,d.dname from emp e,dept d
from emp e,dept d where e.deptno=d.deptno;

We have created complex view EmpDept.Now We will create trigger to insert values into  EmpDept view


Create Or replace trigger UpdateComplexView
instead of insert  on EmpDept
for each row 
begin

insert into Emp(empno,ename,esal)values (:NEW.empno,:NEW.ename,:NEW.esal); 

 insert into Dept(deptno,dname) values (:NEW.deptno,:NEW.dname):  
    
end;

Difference between View vs Materialized View in database

Based upon on our understanding of View and Materialized View, Let’s see, some short difference between them :

1) First difference between View and materialized view is that, In Views query result is not stored in the disk or database but Materialized view allow to store query result in disk or table.

2) Another difference between View vs materialized view is that, when we create view using any table,  rowid of view is same as original table but in case of Materialized view rowid is different.

3) One more difference between View and materialized view in database is that, In case of View we always get latest data but in case of Materialized view we need to refresh the view for getting latest data.

4) Performance of View is less than Materialized view.

5) This is continuation of first difference between View and Materialized View, In case of view its only the logical view of table no separate copy of table but in case of Materialized view we get physically separate copy of table

6) Last difference between View vs Materialized View is that, In case of Materialized view we need extra trigger or some automatic method so that we can keep MV refreshed, this is not required for views in database.

When to Use View vs Materialized View in SQL

Mostly in application we use views because they are more feasible,  only logical representation of table data no extra space needed. We easily get replica of data and we can perform our operation on that data without affecting actual table data but when we see performance which is crucial for large application they use materialized view where Query Response time matters so Materialized views are used mostly with data ware housing or business intelligence application.

What is Force View?

We can  create view without base tables these type of views known as force view. Force view is created using Keyword Force.Force View created with Compilation Error.

Syntax : 

  Create Or replace  Force View  test_View  

  As select * from employees;

If table dropped, then what happen view?

  View become invalid state