view and mview

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

its a virtual table

its a snapshot of a query

wen we executing view the result comes from base table

but mv stores data physically while querying mv , it gives data from mv

view query result not stored in disk

mv allow to store query result in disk or database

wen we create view using any table ,the rowid of the table and view is same
but mv rowid is differnet

view will always give latest data

but in case of mv we need to refresh to get latest data

performance of view is less than mv

wen we delete original table view will get invalid

but mv wont.

aggregate and analytical

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

anlaytic
there is no row collapsing while
processing rows. therefore, return
same number of rows as the input

aggregate
aggregate take many rows collapse them into one, therfore , return single
summary value.

analytic
analytic queries also generally run faster and
use fewer resourses

aggregate slower than analytical functions

analytic
the groups of rows on which an analytic function operates are defined by window
partitioning and window frame clauses.

aggregate
the groups of rows on which an agggregate function operates are defined by the sql
group by clause.

Types of Triggers in Oracle

------------------------------
Triggers can be classified based on the following parameters.
•Classification based on the timing◦BEFORE Trigger: It fires before the specified
event has occurred.
◦AFTER Trigger: It fires after the specified event has occurred.
◦INSTEAD OF Trigger: A special type. You will learn more about the further topics.
(only for DML )
•Classification based on the level◦STATEMENT level Trigger: It fires one time for
the specified event statement.
◦ROW level Trigger: It fires for each record that got affected in the specified
event. (only for DML)

•Classification based on the Event◦DML Trigger: It fires when the DML event is
specified (INSERT/UPDATE/DELETE)
◦DDL Trigger: It fires when the DDL event is specified (CREATE/ALTER)
◦DATABASE Trigger: It fires when the database event is specified
(LOGON/LOGOFF/STARTUP/SHUTDOWN)

mutating error and how to solve it

---------------------------------------
When a table is mutating, it is changing. If the changing is taking place and you
try to make another change in the middle of the first change, Oracle will issue a
mutating table error with the error code ORA-04091.

real option is to rewrite the trigger as statement level trigger

use an after or instead of trigger
and use pragma autonomous trigger-You can avoid the mutating table error by marking
your trigger as an autonomous transaction, making it independent from the table
that calls the procedure.

cursor and refcursor

-----------------------
cursor works sql private area
refcursor is a datatype

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.

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.

same cursor cannot used in two select statements whereas same refcursor can be used
in two different select statement.

ursor is static one , refcursor is dynamic one.

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

joins and subqueries

-----------------------
Joins and subqueries are both used to combine data from different tables into a
single result. They share many similarities and differences.

A common use for a subquery may be to calculate a summary value for use in a query.
For instance we can use a subquery to help us obtain all products have a greater
than average product price.
Contrast this with a join whose main purpose of a join is to combine rows from one
or more tables based on a match condition. For example we can use a join display
product names and models

Note that he join is an integral part of the select statement. It can not stand on
its own as a subquery can.
A subquery is used to run a separate query from within the main query. In many
cases the returned value is displayed as a column or used in a filter condition
such as where or having clause

Joins versus Subqueries. Joins and subqueries are both used to combine data from
different tables into a single result. ... Subqueries can be used to return either
a scalar (single) value or a row set; whereas, joins are used to return rows. A
common use for a subquery may be to calculate a summary value for use in a
query ...

diff b/w procedure and functions

--------------------------------------
Stored Procedures User Defined Functions
1 Stored Procedure cannot be used in a Select statement
User Defined Function can be used in a Select statement
2 Stored procedure supports Deferred Name Resolution
User Defined Function does not support Deferred Name Resolution
3 Stored Procedures are generally used for performing Business Logic
User Defined Functions are generally used for Computations
4 Stored Procedure need not return a value
User Defined Functions should return a value
5 Stored Procedures can return any datatype
User Defined Functions cannot return Image
6 Stored Procedures can accept more number of input parameters than User Defined
Functions. Stored Procedures can have upto 21000 input parameters
User Defined Functions accept lesser number of input parameters than Stored
Procedures. UDF can have upto 1023 input parameters
7 Stored Procedures can use Temporary Tables & table variables.
Temporary Tables cannot be used in a User Defined Function it uses table variables.
8 Stored Procedures can execute Dynamic SQL
User Defined Functions cannot execute Dynamic SQL

diff case and decode?

---------------------------
complies with ansi sql , whereas decode is oracle proprietary
can work with logical operators other than =,where as decode works only with =
operator
can work with many columns inside single case statement .whereas decode works with
single expression only
needs data consistency whereas decode dont needd data consistency
null=null returns false ,whereas in decode null is null returns true
can be used in plsqlblock(standalone case statement inside a block) and sql .
statements .but decode can b only used in sql statements
can b used in parameters while calling procedure where as decode cannot called in
as parameter in procedure
CAN HAVE UNLIMITED COMPONENTS ,WHERE AS DECODE ACCEPT ONLY 255 COMPONENTS

constraints and triggers

-------------------------
Constraints are the rules enforced on data columns on table. These are used to
limit the type of data that can go into a table. This ensures the accuracy and
reliability of the data in the database. Contraints could be column level or table
level. Column level constraints are applied only to one column where as table level
constraints are applied to the whole table.

Trigger are different from Constraints.

Both supports data integrity
Triggers can be used to take table backups and changes logs. Like what was the old
value, who updated (user) and when (update time) ...
You can write triggers that fire whenever one of the following operations occurs:

1.DML statements (INSERT, UPDATE, DELETE) on a particular table or view, issued by

any user
2.DDL statements (CREATE or ALTER primarily) issued either by a particular
schema/user or by any schema/user in the database
3.Database events, such as logon/logoff, errors, or startup/shutdown, also issued
either by a particular schema/user or by any schema/user in the database

diff b/w primary key and unique key?

------------------------------------------
S.No Primary Key Unique Key
1 Creates Clustered index Creates Non-Clustered index
2 Null values are not allowed. Allows only one null value.
3 We can have only one Primary key in a table.
We can have more than one unique key in a table.
4 Primary key can be made foreign key into another table.
Unique key cannot be made foreign key into another table

diff b/w rank and dense_rank?

----------------------------------
RANK: This gives you the ranking within your ordered partition. Ties are assigned
the same rank, with the next ranking(s) skipped. ... DENSE_RANK: This gives you the
ranking within your ordered partition, but the ranks are consecutive in it. Also,
no ranks are skipped if there are ranks with multiple items

diff b/w delete and truncate?

-----------------------------------
Truncate Delete
1 Truncate is faster Delete is comparatively slower
2 Removes all rows from a table Can remove specific rows with Where clause
3 Is DDL Command Is DML Command
4 Resets identity of the table Does not reset identity of the table
5 Removes the data by deallocating the data pages and logs the deallocation.
Removes one row at a time and records an entry in the transaction log for each
deleted row.
6 Cannot be rolled back Can be rolled back

How Triggers Are Used

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

Triggers supplement the standard capabilities of Oracle to provide a highly

customized database management system. For example, a trigger can restrict DML
operations against a table to those issued during regular business hours. You can
also use triggers to:

•Automatically generate derived column values

•Prevent invalid transactions
•Enforce complex security authorizations
•Enforce referential integrity across nodes in a distributed database
•Enforce complex business rules
•Provide transparent event logging
•Provide auditing
•Maintain synchronous table replicates
•Gather statistics on table access
•Modify table data when DML statements are issued against views
•Publish information about database events, user events, and SQL statements to
subscribing applications

Benefits of Triggers
---------------------------
Following are the benefits of triggers.
•Generating some derived column values automatically
•Enforcing referential integrity
•Event logging and storing information on table access
•Auditing
•Synchronous replication of tables
•Imposing security authorizations
•Preventing invalid transactions

what is set operators? and where it is used?

-------------------------------------------------
Set operators are used to join the results of two (or more) SELECT statements.
Same number of columns must be selected by all participating SELECT
statements.Column names used in the display are taken from the first query.
datatypes of the each select statements must be same as everything.
UNION ALL shows better performance as compared to UNION because resources are not
wasted in filtering duplicates and sorting the result set
Set operators can be the part of sub queries.
Set operators can't be used in SELECT statements containing TABLE collection
expressions.
column number of first query must b match with second query and placing of the
column must match and datatypes too.
i used set operators in my project is to join values of different tables simply we
dont take join conidtion ..if data volume is lesser

merge and it's advantages?

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

The MERGE statement was introduced in Oracle 9i to conditionally insert or update

data depending on its presence, a process also known as an "upsert". The MERGE
statement reduces table scans and can perform the operation in parallel if
required.

Syntax

Consider the following example where data from the HR_RECORDS table is merged into
the EMPLOYEES table.
MERGE INTO employees e
USING hr_records h
ON (e.id = h.emp_id)
WHEN MATCHED THEN
UPDATE SET e.address = h.address
WHEN NOT MATCHED THEN
INSERT (id, address)
VALUES (h.emp_id, h.address);

How will you refresh mview and which one is default refresh
-------------------------------------------------------------------
-- Normal
CREATE MATERIALIZED VIEW view-name
BUILD [IMMEDIATE | DEFERRED]
REFRESH [FAST | COMPLETE | FORCE ]
ON [COMMIT | DEMAND ]
[[ENABLE | DISABLE] QUERY REWRITE]
AS
SELECT ...;

CREATE MATERIALIZED VIEW MVW_TEST

REFRESH FORCE ON DEMAND
START WITH TO_DATE('01-01-2009 00:01:00', 'DD-MM-YYYY HH24:MI:SS') NEXT SYSDATE +
1/1152
As select * from TEST12

Create Materialized view temp_mv

refresh complete
start with (sysdate) next (sysdate+1/1440) with rowid
as select * from temp;

what is sequence and it's types?

----------------------------------
•A sequence is a user defined schema bound object that generates a sequence of
numeric values.
•Sequences are frequently used in many databases because many applications require
each row in a table to contain a unique value and sequences provides an easy way to
generate them.
•The sequence of numeric values is generated in an ascending or descending order at
defined intervals and can be configured to restart when exceeds max_value.

package and it's advantages?

----------------------------------
A package is a schema object that groups logically related PL/SQL types, variables,
constants, subprograms, cursors, and exceptions. A package is compiled and stored
in the database, where many applications can share its contents

A package is a collection of related PL/SQL objects. The package contains a body

and a specification. The package specification has the declaration which is public
and can be used in the PL/SQL sub programs inside the package.
The package body holds the implementation of all the PL/SQL objects declared in the
specification.

advantages
------------
organized code management.
Packages are easier for application designing, encapsulating data, additional
functionality and better performance. An application has various modules which can
be placed in packages and handled easier.

reasons to use packages

modularity
easier application design
hidden implimentation details
added functionality
better performance
easier to grant roles

dbms_mview.refresh('hr',mv_name);