bulk collect and forall in oracle pl/sql

Using of BULK COLLECT and FORALL for better performance
PL/SQL engine sends SQL statements to the SQL engine, which returns results to the PL/SQL engine.
The communication between the PL/SQL and SQL engines is called a context switch. A certain amount of performance overhead is associated with these context switches. However, the PL/SQL language has a number of features that can minimize the performance overhead known as bulk SQL. Generally, if a SQL
statement affects four or more rows, bulk SQL may improve performance significantly. Bulk SQL supports batch processing of SQL statements and their results. It consists of two features—the FORALL statement and the BULK COLLECT clause.


FORALL is:

"The keyword FORALL instructs the PL/SQL engine to bulk-bind input collections before sending them to the SQL engine. Although the FORALL statement contains an  iteration scheme, it is not a FOR loop.

Its syntax follows:

FORALL index IN lower_bound..upper_bound
   sql_statement;

The index can be referenced only within the FORALL statement and only as a collection subscript. The SQL statement must be an INSERT, UPDATE, or DELETE statement that
references collection elements. And, the bounds must specify a valid range of consecutive index numbers. The SQL engine executes the SQL statement once for each index
number in the range."

DECLARE
 TYPE NumList IS VARRAY(20) OF NUMBER;
 Depts   NumList := NumList(10, 30, 70); -- department numbers
 BEGIN
 FORALL i IN depts.FIRST..depts.LAST
 DELETE FROM emp WHERE deptno = depts(i);
 END;

BULK COLLECT is:

"The keywords BULK COLLECT tell the SQL engine to bulk-bind output collections before returning them to the PL/SQL engine. You can use these keywords in the SELECT  INTO, FETCH INTO, and RETURNING INTO clauses.

Here is the syntax:

... BULK COLLECT INTO collection_name[, collection_name] ..."
and FORALL is defined as





DECLARE
TYPE NumTab IS TABLE OF emp.empno%TYPE;
TYPE NameTab IS TABLE OF emp.ename%TYPE;
enums NumTab; -- no need to initialize
names NameTab;
BEGIN
SELECT empno, ename BULK COLLECT INTO enums, names FROM emp;

END;
…….
DECLARE
TYPE SalList IS TABLE OF emp.sal%TYPE;
sals SalList;
BEGIN
SELECT sal BULK COLLECT INTO sals FROM emp
WHERE ROWNUM <= 100;
...
END;
………
Example of a Bulk Fetch from a Cursor
The following example shows that you can bulk-fetch from a cursor into one or
more collections:
DECLARE
TYPE NameList IS TABLE OF emp.ename%TYPE;
TYPE SalList IS TABLE OF emp.sal%TYPE;
CURSOR c1 IS SELECT ename, sal FROM emp WHERE sal > 1000;
names NameList;
sals SalList;
BEGIN
OPEN c1;
FETCH c1 BULK COLLECT INTO names, sals;

END;
…………………
Retrieving DML Results into a Collection with the RETURNING INTO Clause
You can use the BULK COLLECT clause in the RETURNING INTO clause of an
INSERT, UPDATE, or DELETE statement, as the following example shows:
DECLARE
TYPE NumList IS TABLE OF emp.empno%TYPE;
enums NumList;

BEGIN
DELETE FROM emp WHERE deptno = 20
RETURNING empno BULK COLLECT INTO enums;
-- if there were five employees in department 20,
-- then enums contains five employee numbers
END;

Using FORALL and BULK COLLECT Together

FORALL j IN depts.FIRST..depts.LAST
DELETE FROM emp WHERE empno = depts(j)
RETURNING empno BULK COLLECT INTO enums;

You cannot use the SELECT ... BULK COLLECT statement in a FORALL statement.
Otherwise, you get the error implementation restriction: cannot use FORALL and BULK
COLLECT INTO together in SELECT statements.




SQL> create table t_all_objects as select * from all_objects;

Table created.

SQL> insert into t_all_objects select * from t_all_objects;

3332 rows created.

SQL> r
1* insert into t_all_objects select * from t_all_objects

6664 rows created.

---replicated a couple of times

SQL> select count(*) from t_all_objects;

COUNT(*)
----------
213248

SQL> declare
cursor c1 is select object_name from t_all_objects;
2    3  rec1 c1%rowtype;
4  begin
5  open c1;
6  loop
7     fetch c1 into rec1;
8     exit when c1%notfound;
9
10     null;
11
12  end loop;
13  end;
14  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:44.75

SQL> declare
2  cursor c1 is select object_name from t_all_objects;
3  type c1_type is table of c1%rowtype;
4  rec1 c1_type;
5  begin
6  open c1;
7
8     fetch c1 bulk collect into rec1;
9
10
11  end;
12  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:05.32
As can be clearly seen, bulk collecting the rows shows a huge performance improvement over fetching row by row.

The above method (which fetched all the rows) may not be applicable to all cases. When there are many rows to process, we can limit the number of rows to bulk collect, process those rows and fetch again. Otherwise process memory gets bigger and bigger as you fetch the rows.
SQL> declare
2  cursor c1 is select object_name from t_all_objects;
3  type c1_type is table of c1%rowtype;
4  rec1 c1_type;
5  begin
6  open c1;
7  loop
8     fetch c1 bulk collect into rec1 limit 200;
9     for i in 1..rec1.count loop
10             null;
11     end loop;
12     exit when c1%notfound;
13  end loop;
14
15
16  end;
17  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.07

INDEX in oracle

INDEX
An index is an optionally created database object used primarily to increase query performance
Similar to a book index, a database index stores the column value of interest along
with its row identifier (ROWID). The ROWID contains the physical location of the table row on disk that
stores the column value. With the ROWID in hand, Oracle can efficiently retrieve table data with a
minimum of disk reads. In this way, indexes function like a shortcut to the table data. If there is no
available index, then Oracle reads each row in the table to determine if the row contains the desired
information.
Improving Performance with Indexes----
When a SQL select statement executes, the Oracle query optimizer quickly calculates a
step-by-step execution plan detailing how it will retrieve column values specified in the query. In
calculating the plan, the optimizer determines which tables and indexes will be used to retrieve data.
When no index exists, the table itself is the only access path available to satisfy the results of the
query. In this scenario, Oracle has no choice but to inspect every row within every used block in the
table(this is known as a full table scan).
SQL> SET AUTOTRACE ON EXPLAIN
SQL> SELECT Ename, Sal, Job
2 FROM Emp
3 WHERE Ename = 'ALLEN';
ENAME SAL JOB
---------- ---------- ---------
ALLEN 1600 SALESMAN
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=26)
1 0 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=1 Bytes=
26)

SQL> CREATE INDEX EmpEnameIDX
2 ON Emp(Ename);
Index created.
SQL> SELECT Ename, Sal, Job
2 FROM Emp
3 WHERE Ename = 'ALLEN';
ENAME SAL JOB
---------- ---------- ---------
ALLEN 1600 SALESMAN
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=26)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Car
d=1 Bytes=26)
2 1 INDEX (RANGE SCAN) OF 'EMPENAMEIDX' (INDEX) (Cost=1 Card
=1)

SQL> ed
Wrote file afiedt.buf
1 SELECT Ename, Sal, Job
2 FROM Emp
3* WHERE Empno = 7839 OR Ename = 'KING'
SQL> /
ENAME SAL JOB
---------- ---------- ---------
KING 5000 PRESIDENT
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=39)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=3 Car
d=1 Bytes=39)
2 1 BITMAP CONVERSION (TO ROWIDS)
3 2 BITMAP OR
4 3 BITMAP CONVERSION (FROM ROWIDS)
5 4 INDEX (RANGE SCAN) OF 'EMP_PRIMARY_KEY' (INDEX (UN
IQUE)) (Cost=1)
6 3 BITMAP CONVERSION (FROM ROWIDS)
7 6 INDEX (RANGE SCAN) OF 'EMPENAMEIDX' (INDEX) (Cost=

1)
SQL> SELECT TABLE_NAME, INDEX_NAME
2 FROM USER_INDEXES
3 WHERE TABLE_NAME = 'EMP';
SQL> SELECT TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE, SEARCH_CONDITION
2 FROM USER_CONSTRAINTS
3 WHERE TABLE_NAME = 'EMP';
SQL> DROP INDEX EMPENAMEIDX;
When the SELECT clause columns are all contained with an index, this is known
as a covering index. These indexes are particularly efficient because only the index blocks need to be
read
• Indexes are optional objects defined on a table and one or more columns.
• Indexes consume resources.
• A B-tree index is the default index type in Oracle.
• A fairly unique column value compared to all other rows in a table results in a
more efficient B-tree index.
• When appropriately created, indexes improve performance
• In some scenarios, the query optimizer will choose not to use an index. In other
words, the query optimizer calculates that the cost of a full table scan is less than
the cost when using an index.
• In some situations, Oracle can retrieve data for a query by only accessing the
index; the table doesn’t have to be accessed
Determining Which Type of Index to Use-----

Guidelines for When to Index
You want to consider indexing columns frequently used in the WHERE clause of SQL statements and
foreign key columns. Oracle automatically creates a unique index to
enforce the primary key constraint and the unique constraint. The following are some general guidelines
when an index is typically useful.
Frequently accessed columns containing highly selective data for B-tree indexes.
Columns frequently accessed with a small range of values for bitmap indexes.
Columns that are frequently accessed and that contain many null values, but the query is looking for the
NOT NULL values.
Frequent queries against large tables retrieving less than 5 to 15 percent of the rows. The percentage
may vary, depending on a number of factors, including the size of the table.
Building an index is often useless if:
The table is small, but you should nevertheless create unique and primary constraints to enforce
business rules.
The query retrieves more than 5 to 15 percent of the rows.
The indexed column is part of an expression. In this case, consider creating a function-based index
instead.
Keep in mind that adding indexes may increase the time required for data manipulation operations, such
as INSERT, UPDATE, and DELETE. If you primarily query the table,
then creating the index may offset the disadvantage of additional time required for DML statements
B-tree Indexes
the default index type in Oracle is a B-tree index. This index type is very efficient for high cardinality column values.
Prerequisites
To create an index in your own schema, one of the following conditions must be true:
The table or cluster to be indexed must be in your own schema.
You must have the INDEX object privilege on the table to be indexed.
You must have the CREATE ANY INDEX system privilege.
Without specifying any options, a B-tree is created with the CREATE INDEX statement; all you need to
provide is the index name, table name, and column(s).

create index cust_idx2
on cust(first_name);
SQL> SELECT TABLE_NAME, INDEX_NAME
2 FROM USER_INDEXES
3 WHERE TABLE_NAME = 'EMP';

There are several subtypes of B-tree indexes.
􀀀Index-organized table
􀀀Unique
􀀀Reverse key
􀀀Key compressed
􀀀Descending
Index-Organized Table
An index-organized table (IOT) stores the entire contents of the table’s row in a B-tree index structure.
An IOT provides fast access for queries that have exact matches and/or range searches on the primary
key.
create table prod_sku
(prod_sku_id number
,sku varchar2(256),
constraint prod_sku_pk primary key(prod_sku_id, sku)
) organization index;
Unique Indexes
When creating a B-tree index you can define it to be a unique index. In this regard it acts like a unique

key constraint. When inserting into the corresponding table, the unique index will guarantee that any
non-null values inserted into the table are distinct. For this reason, unique indexes are commonly used
in conjunction with primary key and unique key constraints
A unique index is specified via the CREATE UNIQUE INDEX statement.
create unique index cust_uidx1
on cust(last_name, first_name);
Reverse Key Indexes
Reverse key indexes are useful to balance I/O in an index that has many sequential inserts. These
indexes can perform better in scenarios where you need a way to evenly distribute index data that would
otherwise have similar values clustered together. Thus, when using a reverse-key index, you avoid
having I/O concentrated in one physical disk location within the index during large inserts of sequential
values
create index cust_ridx1
on cust(cust_id) reverse;
NULLs and Indexes
NULL values are not stored in a B-tree index, unless it is a composite index where at least the first
column of the index contains a value. The following query does not make use of the single-column index
on the FIRST_NAME column.
SELECT student_id, first_name
FROM student
WHERE first_name IS NULL
Specialized Index Types
Sometimes a B-tree index isn’t enough to provide the desired performance improvement. The following
are indexes that should be used under specialized circumstances:
􀀀Bitmap
􀀀Function-based
􀀀Invisible
Bitmap Index
Bitmap indexes are commonly used in data-warehouse environments. These indexes are recommended
for columns with a relatively low number of distinct values (low cardinality)
efficient for SQL statements that use multiple AND or OR join operators in the WHERE clause (which is
typical in a data warehouse environment).
You should not use bitmap indexes in OLTP databases with high INSERT/UPDATE/DELETE activities.
This is because the structure of the bitmap index results in many locked rows during singular DML
operations (which results in locking problems for high-transaction OLTP systems).
A bitmap index is created using the keyword BITMAP. For completeness, we also show the table
creation script upon which the bitmap index is built.
create table f_sales(
sales_amt number
,d_date_id number
,d_product_id number
,d_customer_id number);
create bitmap index f_sales_fk1
on f_sales(d_date_id);

The following statement creates a bitmap index on the GENDER column of a CUSTOMER table.
CREATE BITMAP INDEX customer_bm_gender_i
ON customer(gender)
Function-Based Indexes
Function-based indexes are created with SQL functions or expressions in their definitions. Functionbased
indexes allow index lookups on columns referenced by SQL functions in the WHERE clause of a
query. Here’s an example of creating a function-based index:
create index cust_fidx1
on cust(upper(last_name));
These types of indexes are necessary because Oracle won’t use a normal B-tree index when a query
references a column with a SQL function applied to it.
SQL> CREATE INDEX EmpTotSalIDX
2 ON Emp(Sal + NVL(Comm, 0));
Index created.
SQL> SELECT Ename, Sal, Comm, Sal + NVL(Comm, 0) TotSal, Deptno, Job
2 FROM Emp
3 WHERE Sal + NVL(Comm, 0) > 2500;
ENAME SAL COMM TOTSAL DEPTNO JOB
---------- ---------- ---------- ---------- ---------- ---------
MARTIN 1250 1400 2650 30 SALESMAN
BLAKE 2850 2850 30 MANAGER
JONES 2975 2975 20 MANAGER
FORD 3000 3000 20 ANALYST
SCOTT 3000 3000 20 ANALYST
KING 5000 5000 10 PRESIDENT
6 rows selected.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=6 Bytes=312
)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Car
d=6 Bytes=312)
2 1 INDEX (RANGE SCAN) OF 'EMPTOTSALIDX' (INDEX) (Cost=1 Car
d=1)
Invisible Index
An invisible index means the optimizer doesn’t use the index when retrieving data for a query. However,
the index structure is still maintained as the underlying table has records inserted, updated, or deleted.
This feature is used when you want to test the viability of an index without impacting existing
application code. Use the INVISIBLE keyword to create an invisible index.
create index cust_iidx1
on cust(last_name) invisible;
Note Invisible indexes are only available in Oracle Database 11g and higher.
Indexes and Constraints
When you create a primary key constraint or a unique constraint, Oracle creates the index automatically
unless a suitable index already exists
The index NEW_TERM_PK is created as part of the CREATE TABLE statement and is associated with the
primary key constraint.
sql>CREATE TABLE new_term
(term_no NUMBER(8) NOT NULL PRIMARY KEY USING INDEX
(CREATE INDEX new_term_pk ON new_term(term_no)
STORAGE (INITIAL 100 K NEXT 100K)),
season_tx VARCHAR2(20),
sequence_no NUMBER(3))
If you already have an existing index and you want to associate a constraint with it, you can use a
statement similar to the following. It assumes an existing index called
SEMESTER_SEMESTER_ID_I, based on the SEMESTER_ID column.
ALTER TABLE semester
ADD CONSTRAINT semester_pk PRIMARY KEY (semester_id)
USING INDEX semester_semester_id_i
The next statement shows an example of a unique constraint that is associated with a unique index.

CREATE TABLE semester
(semester_id NUMBER(8),
semester_name VARCHAR2(8) NOT NULL,
year_no NUMBER(4) NOT NULL,
CONSTRAINT semester_uk UNIQUE (semester_name, year_no)
USING INDEX
(CREATE UNIQUE INDEX semester_sem_yr_uk
ON semester(semester_name, year_no)))
note--When disabling a unique or primary key, you can keep the index if you specify the KEEP INDEX
clause in an ALTER TABLE statement
Indexes and Foreign Keys
You should almost always index foreign keys because they are frequently used in joins.
Dropping an Index
To drop an index, use the DROP INDEX command. You might drop an index if queries in your applications
do not utilize the index. You find out which indexes are used by
querying the V$OBJECT_USAGE data dictionary view.
>DROP INDEX sect_location_i
Index dropped.
---------When you drop a table, all associated indexes are dropped automatically
Loading Large Amounts of Data
When you insert or update large amounts of data, you might want to consider dropping certain indexes
not used for the DML operation’s WHERE clause to improve performance. After the operation is
complete, you can re-create the appropriate indexes.
One fast way to re-create indexes is by using the NOLOGGING option. It avoids writing to the redo log,
which keeps track of all the database changes. If you incur a fatal database error and you need to
recover from the redo log, the index will not be recovered. This may be fine because an index can always
be re-created.

You can also create an index by using the PARALLEL option. This allows parallel scans of the table to
create the index and can make index creation much faster, provided that you have the appropriate
hardware configuration, such as multiple CPUs.

Autonomous Transactions in oracle

Autonomous Transactions
Autonomous transactions allow you to leave the context of the calling transaction, perform an independant transaction, and return to the calling transaction without affecting it's state. The autonomous transaction has no link to the calling transaction, so only commited data can be shared by both transactions.
The following types of PL/SQL blocks can be defined as autonomous transactions:
•    Stored procedures and functions.
•    Local procedures and functions defined in a PL/SQL declaration block.
•    Packaged procedures and functions.
•    Type methods.
•    Top-level anonymous blocks.
The easiest way to understand autonomous transactions is to see them in action. To do this, we create a test table and populate it with two rows. Notice that the data is not commited.
CREATE TABLE at_test (
  id           NUMBER       NOT NULL,
  description  VARCHAR2(50) NOT NULL
);

INSERT INTO at_test (id, description) VALUES (1, 'Description for 1');
INSERT INTO at_test (id, description) VALUES (2, 'Description for 2');

SELECT * FROM at_test;

        ID DESCRIPTION
---------- --------------------------------------------------
         1 Description for 1
         2 Description for 2

2 rows selected.

SQL>
Next, we insert another 8 rows using an anonymous block declared as an autonomous transaction, which contains a commit statement.
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  FOR i IN 3 .. 10 LOOP
    INSERT INTO at_test (id, description)
    VALUES (i, 'Description for ' || i);
  END LOOP;
  COMMIT;
END;
/

PL/SQL procedure successfully completed.

SELECT * FROM at_test;

        ID DESCRIPTION
---------- --------------------------------------------------
         1 Description for 1
         2 Description for 2
         3 Description for 3
         4 Description for 4
         5 Description for 5
         6 Description for 6
         7 Description for 7
         8 Description for 8
         9 Description for 9
        10 Description for 10

10 rows selected.

SQL>
As expected, we now have 10 rows in the table. If we now issue a rollback statement we get the following result.
ROLLBACK;
SELECT * FROM at_test;

        ID DESCRIPTION
---------- --------------------------------------------------
         3 Description for 3
         4 Description for 4
         5 Description for 5
         6 Description for 6
         7 Description for 7
         8 Description for 8
         9 Description for 9
        10 Description for 10

8 rows selected.

SQL>
The 2 rows inserted by our current session (transaction) have been rolled back, while the rows inserted by the autonomous transactions remain. The presence of the PRAGMA AUTONOMOUS_TRANSACTION compiler directive made the anonymous block run in its own transaction, so the internal commit statement did not affect the calling session. As a result rollback was still able to affect the DML issued by the current statement.
Autonomous transactions are commonly used by error logging routines, where the error messages must be preserved, regardless of the the commit/rollback status of the transaction. For example, the following table holds basic error messages.
CREATE TABLE error_logs (
  id             NUMBER(10)     NOT NULL,
  log_timestamp  TIMESTAMP      NOT NULL,
  error_message  VARCHAR2(4000),
  CONSTRAINT error_logs_pk PRIMARY KEY (id)
);

CREATE SEQUENCE error_logs_seq;
We define a procedure to log error messages as an autonomous transaction.
CREATE OR REPLACE PROCEDURE log_errors (p_error_message  IN  VARCHAR2) AS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO error_logs (id, log_timestamp, error_message)
  VALUES (error_logs_seq.NEXTVAL, SYSTIMESTAMP, p_error_message);
  COMMIT;
END;
/
The following code forces an error, which is trapped and logged.
BEGIN
  INSERT INTO at_test (id, description)
  VALUES (998, 'Description for 998');

  -- Force invalid insert.
  INSERT INTO at_test (id, description)
  VALUES (999, NULL);
EXCEPTION
  WHEN OTHERS THEN
    log_errors (p_error_message => SQLERRM);
    ROLLBACK;
END;
/

PL/SQL procedure successfully completed.

SELECT * FROM at_test WHERE id >= 998;

no rows selected

SELECT * FROM error_logs;

        ID LOG_TIMESTAMP
---------- ---------------------------------------------------------------------------
ERROR_MESSAGE
----------------------------------------------------------------------------------------------------
         1 28-FEB-2006 11:10:10.107625
ORA-01400: cannot insert NULL into ("TIM_HALL"."AT_TEST"."DESCRIPTION")


1 row selected.

SQL>
From this we can see that the LOG_ERRORS transaction was separate to the anonymous block. If it weren't, we would expect the first insert in the anonymous block to be preserved by the commit statement in the LOG_ERRORS procedure.
Be careful how you use autonomous transactions. If they are used indiscriminately they can lead to deadlocks, and cause confusion when analyzing session trace. To hammer this point home, here's a quote from Tom Kyte.
"... in 999 times out of 1000, if you find yourself "forced" to use an autonomous transaction - it likely means you have a serious data integrity issue you haven't thought about.
Where do people try to use them?
•    in that trigger that calls a procedure that commits (not an error logging routine). Ouch, that has to hurt when you rollback.
•    in that trigger that is getting the mutating table constraint. Ouch, that hurts *even more*
Error logging - OK.
Almost everything else - not OK."

The MERGE Statement in oracle

The MERGE Statement
The MERGE statement is a single command that combines the ability to update or insert rows into a table
by conditionally deriving the rows to be updated or inserted from one or more sources
This allows you to avoid issuing multiple INSERT, UPDATE, and DELETE statements.

The syntax for the MERGE statement is:

MERGE <hint>
INTO <table_name>
USING <table_view_or_query>
ON (<condition>)
WHEN MATCHED THEN <update_clause>
DELETE <where_clause>
WHEN NOT MATCHED THEN <insert_clause>
[LOG ERRORS <log_errors_clause> <reject limit <integer | unlimited>];

 MERGE Statement Example


SQL> create table dept60_bonuses
2 (employee_id number
3 ,bonus_amt number);
Table created.
SQL> insert into dept60_bonuses values (103, 0);
1 row created.
SQL> insert into dept60_bonuses values (104, 100);
1 row created.
SQL> insert into dept60_bonuses values (105, 0);
1 row created.
SQL> commit;
Commit complete.
SQL> select employee_id, last_name, salary
2 from employees
3 where department_id = 60 ;
EMPLOYEE_ID LAST_NAME SALARY
--------------- ------------------------- ---------------
103     Hunold     9000
104     Ernst     6000
105     Austin     4800
106     Pataballa  4800
107     Lorentz        4200

SQL> select * from dept60_bonuses;
EMPLOYEE_ID BONUS_AMT
--------------- ---------------
103     0
104     100
105     0

3 rows selected.


SQL> merge into dept60_bonuses b
2 using (
3 select employee_id, salary, department_id
4 from employees
5 where department_id = 60) e
6 on (b.employee_id = e.employee_id)
7 when matched then
8 update set b.bonus_amt = e.salary * 0.2
9 where b.bonus_amt = 0
10 delete where (e.salary > 7500)
11 when not matched then
12 insert (b.employee_id, b.bonus_amt)
13 values (e.employee_id, e.salary * 0.1)
14 where (e.salary < 7500);

4 rows merged.
SQL> select * from dept60_bonuses;
EMPLOYEE_ID BONUS_AMT
--------------- ---------------
104     100
105     960
106     480
107     420

4 rows selected.

SQL> rollback;
Rollback complete.

The MERGE accomplished the following:
• Two rows were inserted (employee_ids 106 and 107).
• One row was updated (employee_id 105).
• One row was deleted (employee_id 103).
• One row remained unchanged (employee_id 104).
Without the MERGE statement, you would have had to write at least three different statements to
complete the same work.

Materialized Views in oracle

Materialized Views
materialized views are mainly used in complex data warehousing environments, where the tables
grow so big that the data volume causes unacceptable performance problems. An important
property of data warehousing environments is that you don’t change the data very often.
Typically, there is a separate Extraction, Transformation, Loading (ETL)
process that updates the data warehouse contents.
Materialized views are also often used with distributed databases. In such
environments, accessing data over the network can become a performance bottleneck. You can
use materialized views to replicate data in a distributed database Materialized views are a data
warehousing/decision support system tool that can increase by many orders of magnitude the
speed of queries that access a large number (maybe many hundreds of thousands or millions) of
records. In basic terms, they allow a user to query potentially terabytes of detail data in seconds
(or less). They accomplish this by transparently using pre‐computed summarizations and joins
of data. These pre‐computed summaries would typically be very small compared to the original
source data
Say, for example, your company has a sales database loaded with the details of a million orders,
and you want to get a breakdown of sales by region (a common enough query).
Each and every record would be scanned, the data aggregated to the region level, and the
calculation performed. Using a materialized view, we can store a summary of the sales data by
region, and have the system maintain these summaries for us. If you have ten sales regions, this
summary will have ten records, so instead of sifting through a million detail
records, we􀀀ll query only ten. Furthermore, if someone asks a slightly different question,
say for the sales in a specific region, then that query can also obtain the answer from the
materialized view.
You will need a user account with the following privileges (at least):
• GRANT CREATE SESSION
• GRANT CREATE TABLE
• GRANT CREATE MATERIALIZED VIEW
• GRANT QUERY REWRITE
An Example
We􀀀ll start with a large table that contains a list of owners of objects, and the objects they own.
This table is based on the ALL_OBJECTS data dictionary
view:
> create table my_all_objects
2 nologging
3 as
4 select * from all_objects
5 union all
6 select * from all_objects
7 union all
8 select * from all_objects
9 /
Table created.
> insert /*+ APPEND */ into my_all_objects
2 select * from my_all_objects;
65742 rows created.
> commit;
Commit complete.
> insert /*+ APPEND */ into my_all_objects
2 select * from my_all_objects;
131484 rows created.
> commit
> commit;
Commit complete.
> analyze table my_all_objects compute statistics;
Table analyzed
> set autotrace on
> set timing on
> select owner, count(*) from my_all_objects group by owner;
OWNER COUNT(*)
‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
‐‐‐‐‐‐‐‐‐‐
A 36
B 24
CTXSYS 2220
DBSNMP 48
DEMO 60
DEMO11 36
DEMO_DDL 108
MDSYS 2112
MV_USER 60
ORDPLUGINS 312
ORDSYS 2472
OUR_TYPES 12
OUTLN 60
PERFSTAT 636
PUBLIC 117972
SCHEDULER 36
SCOTT 84
SEAPARK 36
SYS 135648
SYSTEM 624
TESTING 276
TKYTE 12
TTS_USER 48
TYPES 36
24 rows selected.
Elapsed: 00:00:03.35
> set timing off
> set autotrace traceonly
> select owner, count(*) from my_all_objects group by owner;
24 rows selected.
Execution Plan
‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2525 Card=24 Bytes=120)
1 0 SORT (GROUP BY) (Cost=2525 Card=24 Bytes=120)
2 1 TABLE ACCESS (FULL) OF 􀀀MY_ALL_OBJECTS􀀀 (Cost=547 Card=262968
Statistics
‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
0 recursive calls
27 db block gets
3608 consistent gets
3516 physical reads
0 redo size
1483 bytes sent via SQL*Net to client
535 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
24 rows processed
In order to get the aggregate count, we must count 250000+ records on over 3600 blocks.
Unfortunately, in our system we ask this question frequently, dozens of times every day.
We are scanning almost 30MB of data. We could avoid counting the details each and every
time by creating a materialized view of the data. The following demonstrates the basic
steps needed to perform this operation. We􀀀ll discuss the GRANT and ALTER statements
> grant query rewrite to tkyte;
Grant succeeded.
> alter session set query_rewrite_enabled=true;
Session altered.
> alter session set query_rewrite_integrity=enforced;
Session altered.
> create materialized view my_all_objects_aggs
2 build immediate
3 refresh on commit
4 enable query rewrite
5 as
6 select owner, count(*)
7 from my_all_objects
8 group by owner
9 /
Materialized view created.
> analyze table my_all_objects_aggs compute statistics;
Table analyzed.
Creating a Complete-Refreshable Materialized View
Complete refreshes are appropriate for MVs that have base tables in which significant portions of the rows change
from one refresh interval to the next. Complete refreshes are also required in situations where a fast refresh isn’t
possible (due to restrictions imposed by Oracle .
Note To create an MV, you need both the CREATE MATERIALIZED VIEW system privilege and the CREATE
TABLE system privilege.
The example in this section uses the following base table named SALES:
create table sales(
sales_id number
,sales_amt number
,region_id number
,sales_dtt timestamp
,constraint sales_pk primary key(sales_id)
);
Insert some sample data into the SALES table:
insert into sales values(1,101,100,sysdate-50);
insert into sales values(2,511,200,sysdate-20);
insert into sales values(3,11,100,sysdate);
commit;
The next step is to create the MV, using a CREATE MATERIALIZED VIEW...AS SELECT statement. This
statement names the MV, specifies its attributes, and defines the SQL query on which the MV is based:
create materialized view sales_mv
segment creation immediate
refresh
complete
on demand
as
select
sales_amt
,sales_dtt
from sales;
The SEGMENT CREATION IMMEDIATE clause is available with Oracle 11g release 2 and higher. It instructs
Oracle to create the segment and allocate an extent when you create the MV. This was the behavior in
previous versions of Oracle. If you don’t want immediate segment creation, use the SEGMENT CREATION
DEFERRED clause. If the newly created MV has any rows in it, then segments are created and extents are
allocated regardless of whether you use SEGMENT CREATION DEFERRED.
Let’s look at the USER_MVIEWS data dictionary to verify that the MV was created as expected. Here’s
the query to run:
select
mview_name
,refresh_method
,refresh_mode
,build_mode
,fast_refreshable
from user_mviews
where mview_name = 'SALES_MV';
Here’s the output for this MV:
MVIEW_NAME REFRESH_ REFRESH_MODE BUILD_MOD FAST_REFRESHABLE
------------ -------- ------------ --------- --------------------
SALES_MV COMPLETE DEMAND IMMEDIATE NO
It’s also informative to inspect the USER_OBJECTS and USER_SEGMENTS views to see what has been
created. When you query USER_OBJECTS, notice that several objects have been created:
select
object_name
,object_type
from user_objects
where object_name like 'SALES%'
order by object_name;
Here’s the corresponding output:
OBJECT_NAME OBJECT_TYPE
-------------------- --------------------
SALES TABLE
SALES_MV MATERIALIZED VIEW
SALES_MV TABLE
SALES_PK INDEX
The MV is a logical container that stores data in a regular database table. Querying the
USER_SEGMENTS view shows the base table, its primary-key index, and the table that stores data returned
by the MV query:
select
segment_name
,segment_type
from user_segments
where segment_name like 'SALES%'
order by segment_name;
Here’s the output for this example:
SEGMENT_NAME SEGMENT_TYPE
-------------------- --------------------
SALES TABLE
SALES_PK INDEX
SALES_MV TABLE
Note Oracle sometimes automatically creates an index on the MV. In this scenario, no index for the MV
was created
Next, insert some additional data into the base SALES table:
SQL> insert into sales values(4,99,200,sysdate);
SQL> insert into sales values(5,127,300,sysdate);
Now you attempt to initiate a fast refresh of the MV using the REFRESH procedure of the DBMS_MVIEW
package. This example passes two parameters to the REFRESH procedure: the name and the refresh
method. The name is SALES_MV, and the parameter is F (for fast):
SQL> exec dbms_mview.refresh('SALES_MV','F');
Because this MV wasn’t created in conjunction with an MV log, a fast refresh isn’t possible. The
following error is thrown:
ORA-12004: REFRESH FAST cannot be used for materialized view "MV"."SALES_MV"

Creating a Fast-Refreshable Materialized View
When you create a fast-refreshable MV, it initially populates the MV table with the entire result set of the
MV query. After the initial result set is in place, only data modified (in the base table) since the last
refresh needs to be applied to the MV. In other words, any updates, inserts, or deletes from the master
table that have occurred since the last refresh are copied over. This feature is appropriate when you have
a small amount of changes to a base table over a period of time compared to the total number of rows in
the table.
Here are the steps to implement a fast-refreshable MV:
1. Create a base table (if it’s not already created).
2. Create an MV log on the base table.
3. Create an MV as fast-refreshable.
This example first creates a base table. In most environments, you already have a base table in
place. For illustrative purposes, here’s the table-creation script and some sample data:
create table sales(
sales_id number
,sales_amt number
,region_id number
,sales_dtt timestamp
,constraint sales_pk primary key(sales_id)
);
--
insert into sales values(1,101,100,sysdate-50);
insert into sales values(2,511,200,sysdate-20);
insert into sales values(3,11,100,sysdate);
commit;
A fast-refreshable MV requires an MV log on the base table. When a fast refresh occurs, the MV log
must have a unique way to identify which records have been modified and thus need to be refreshed.
You can do this two different approaches. One method is to specify the PRIMARY KEY clause when you
create the MV log, and the other is to specify the ROWID clause. If the underlying base table has a primary
key, then use the primary key–based MV log. If the underlying base table has no primary key, then you
have to create the MV log using ROWID. In most cases, you’ll probably have a primary key defined for
every base table. However, the reality is that some systems are poorly designed or have some rare reason
for a table not to have a primary key.
In this example, a primary key is defined on the base table, so you create the MV log with the
PRIMARY KEY clause:
SQL> create materialized view log on sales with primary key;
If there was no primary key defined on the base table, this error is thrown when attempting to create
the MV log:
ORA-12014: table does not contain a primary key constraint
If the base table has no primary key, and you don’t have the option to add one, you must specify
ROWID when you create the MV log:
SQL> create materialized view log on sales with rowid;
Now that you’ve created the MV log, you can create the fast-refreshable MV. There are a couple of
interesting architectural considerations to point out. If there is no MV log on the base table, then Oracle
doesn’t allow you to create a fast-refreshable MV. This error is thrown:
ORA-23413: table does not have a materialized view log.
Also, when you create the fast-refreshable MV, you must specify whether it’s refreshed via the
PRIMARY KEY (which is the default) or via the ROWID. This example uses a table with a primary key and an
MV log created with a primary key. In this example, the MV is fast-refreshed via a primary key. When you
use a primary key–based fast-refreshable MV, the primary-key column(s) of the base table must be part
of the fast-refreshable MV SELECT statement:
create materialized view sales_mv
segment creation immediate
refresh
with primary key
fast
on demand
as
select
sales_id
,sales_amt
,sales_dtt
from sales;
At this point, it’s useful to inspect the objects that are associated with the MV. The following query
selects from USER_OBJECTS:
select
object_name
,object_type
from user_objects
where object_name like '%SALES%'
order by object_name;
Here are the objects that have been created:
OBJECT_NAME OBJECT_TYPE
-------------------- --------------------
MLOG$_SALES TABLE
RUPD$_SALES TABLE
SALES TABLE
SALES_MV MATERIALIZED VIEW
SALES_MV TABLE
SALES_PK INDEX
SALES_PK1 INDEX
SQL> select sales_amt, to_char(sales_dtt,'dd-mon-yyyy') from sales_mv;
Here’s the output:
SALES_AMT TO_CHAR(SALES_DTT
---------- -----------------
101 12-jun-2010
511 12-jul-2010
11 01-aug-2010
Let’s add two records to the base SALES table:
insert into sales values(4,99,200,sysdate);
insert into sales values(5,127,300,sysdate);
commit;
At this point, it’s instructional to inspect the M$LOG table. You should see two records that identify
how the data in the SALES table has changed:
SQL> select count(*) from mlog$_sales;
There are two records:
COUNT(*)
----------
2
Next, let’s refresh the MV. This MV is fast-refreshable, so you call the REFRESH procedure of the
DBMS_MVIEW package with the F (for fast) parameter:
SQL> exec dbms_mview.refresh('SALES_MV','F');
A quick inspection of the MV shows two new records:
SQL> select sales_amt, to_char(sales_dtt,'dd-mon-yyyy') from sales_mv;
SALES_AMT TO_CHAR(SALES_DTT
---------- -----------------
101 12-jun-2010
511 12-jul-2010
11 01-aug-2010
99 01-aug-2010
127 01-aug-2010
In addition, the count of the MLOG$ has dropped to zero. After the MV refresh is complete, those
records are no longer required:
SQL> select count(*) from mlog$_sales;
Here’s the output:
COUNT(*)
----------
0
You can verify the last method in which an MV was refreshed by querying the USER_MVIEWS view:
select
mview_name
,last_refresh_type
,last_refresh_date
from user_mviews
order by 1,3;
Here’s some sample output:
MVIEW_NAME LAST_REF LAST_REFR
------------------------------ -------- ---------
SALES_MV FAST 01-AUG-10

html code for header ,sidebar,footer,containt

<html>
  <head>
        <style>
            #header{
                background-color: lightblue;
                width:100%;
                height:50px;
                text-align: center;
            }
            #sidebar-left{
                float:left;
                width:15%;
                background-color: red;
            }
            #main{
                float:left;
                width:70%;
                background-color: lightgray;
            }
            #sidebar-right{
                float:left;
                width:15%;
                background-color: red;
            }
            #footer{
                clear:both;
                height: 50px;
                width: 100%;
                text-align: center;
                background-color: lightblue;
            }
            #sidebar-left, #main, #sidebar-right{
                min-height: 600px               
            }
        </style>
    </head>
    <body>
        <div id="header">ITGENIE4U</div>
                <div id="sidebar-left">Left</div>
        <div id="main">
        <p><strong>CONTENT</strong>This is a test. This is a test. This is a test. This is a test.
This is a test. This is a test. This is a test. This is a test. This is a test.
This is a test. This is a test. This is a test. This is a test. This is a test.
This is a test. This is a test. This is a test. This is a test. This is a test.

</p>

<p>This is a test. This is a test. This is a test. This is a test.
This is a test. This is a test. This is a test. This is a test. This is a test.
This is a test. This is a test. This is a test. This is a test. This is a test.
This is a test. This is a test. This is a test. This is a test. This is a test. This is a test.
This is a test. This is a test. This is a test. This is a test. This is a test.
</p>
</div>
       
        <div id="sidebar-right">Right</div>
        <div id="footer">Footer</div>
    </body>
</html>

LOGICAL & PHYSICAL STRUCTURE OF DATABASE

 

       BLOCK
Block is smallest storage unit into which we can store data is called block.
Block size is 8kb.
„Ï 1 Byte = 8 Bit 1024 MB = 1 GB 2k Windows
„Ï 1 Kb = 1024 Bytes 1024 KB = 1 MB 4k Solaris
„Ï 1 Mb = 1048576 Bytes 8k Linux
„Ï 1 Gb = 1073741824 Bytes 16k Hp ¡V UX
# Mount show all mounted files. 32k AIX
# df ¡Vk (show free space in all TS)
There are two phases in block
„Ï One is for feature usage. We call it as pctfree.
„Ï One is for current usage. We call it as pctuse.
Block has two STORAGE PARAMETER called pctfree and pctuse.
Pctuse: it is the amount of space reserved for session.
Pctfree: it is the amount of free space left in the block for feature updating.
A database block is a combination of OS blocks. Suppose if the size of the OS block
is 4kb then 2 OS blocks combined to form database block.
„Ï Up oracle 9i minimum block size as 2k.
„Ï At oracle 10g minimum block size as 8k.
Physical structure
Collection of data files in operating system is called physical structure. Or, Set of
operating system files in database.
Logical Structure
The logical structure of the oracle architecture how the physical space of a
database is to be used.



In create table – table name is table segment
Tablespaces: A tablespace may consist of one more segments.
Segments: A segment made up of extents.
Extents: An extent is made up of blocks
Block: A block is the smallest storage unit of read and writes operations.
STRUCTURE OF A BLOCK
Header
Contains information regarding the types of block (table, block, index etc)
transaction information regarding active and post transaction on the block.
Table Directory
Contains information about the tables that store rows in this block.
Row Directory
Contains information describing the rows that are to be found on the block. This
is an array of pointers to where the rows are to be found in the data portion of the
block.
Block Over Head
The three above pieces are known as the block overhead and are used by oracle
to manage the block itself.