Identify and fix table fragmentation in Oracle 10g - how?
Table fragmentation – when?
If a table is only subject to inserts, there will not be any fragmentation.
Fragmentation comes with when we update/delete data in table.
The space which gets freed up during non-insert DML operations is not immediately re-used (or sometimes, may not get reuse ever at all). This leaves behind holes in table which results in table fragmentation.
To understand it more clearly, we need to be clear on how oracle manages space for tables.
“High water mark” of table actually defines the border line between (ever) used and unused (never) space. While performing full table scan, Oracle will always read the data up to HWM. And if there is lot of free space with-in HWM, that is read too, and hence degrading the performance of FTS.
Now lets see how to identify HWM, unused (never used) space and free space (used but deleted/updated) and then take a call whether the concerned table is candidate for a reorganization or not.
SQL> create table test as select * from dba_tables; -- Create a table
Table created.
SQL> analyze table test compute statistics; -- Analyze it
Table analyzed.
SQL> select blocks "Ever Used", empty_blocks "Never Used", num_rows "Total rows"
2 from user_tables where table_name='TEST'; -- The number of blocks used/free
Ever Used Never Used Total rows
---------- ---------- ----------
49 6 1680
SQL> delete from test where owner='SYS'; --- Im deleting almost half the number of rows.
764 rows deleted.
SQL> commit;
Commit complete.
SQL> analyze table test compute statistics; -- Analyze it again
Table analyzed.
SQL> select blocks "Ever Used", empty_blocks "Never Used", num_rows "Total rows"
2 from user_tables where table_name='TEST'; -- No difference in blocks usage
Ever Used Never Used Total rows
---------- ---------- ----------
49 6 916
PL/SQL procedure successfully completed.
Even though you deleted almost half the rows, the above shows that table HWM is up to 49 blocks, and to perform any FTS, Oracle will go up to 49 blocks to search the data. If your application is so-written that there are many FTS on this table, you may consider, reorganizing this table.
Reasons to reorganization
a) Slower response time (from that table)
b) High number of chained (actually migrated) rows.
c) Table has grown many folds and the old space is not getting reused.
Note: Index based queries may not get that much benefited by reorg as compared to queries which does Full table scan.
How to reorganize?
Before Oracle 10g, there were mainly 2 ways to do the reorganization of the table
a) Export, drop, and import.
b) Alter table move (to another tablespace, or same tablespace).
Oracle 10g provides us a new way of reorganizing the data.
Shrink command: This command is only applicable for tables which are tablespace with auto segment space management.
Before using this command, you should have row movement enabled.
SQL> alter table test enable row movement;
Table altered.
There are 2 ways of using this command.
1. Break in two parts: In first part rearrange rows and in second part reset the HWM.
Part 1: Rearrange (All DML's can happen during this time)
SQL> alter table test shrink space compact;
Table altered.
Part 2: Reset HWM (No DML can happen. but this is fairly quick, infact goes unnoticed.)
SQL> alter table sa shrink space;
Table altered.
2. Do it in one go:
SQL> alter table sa shrink space; (Both rearrange and restting HWM happens in one statement)
Table altered.
Few advantages over the conventional methods
1. Unlike "alter table move ..", indexes are not in UNUSABLE state. After shrink command, indexes are updated also.
2. Its an online operation, So you dont need downtime to do this reorg.
3. It doesnot require any extra space for the process to complete.
http://oracle-online-help.blogspot.com/2007/02/identify-and-fix-table-fragmentation-in.html
MERGE Statement Enhancements in Oracle Database 10g
Oracle 10g includes a number of amendments to the MERGE statement making it more flexible.
Test Table
Optional Clauses
Conditional Operations
DELETE Clause
Related articles.
SQL New Features In Oracle 9i: MERGE Statement
Test Table
The following examples use the table defined below.
CREATE TABLE test1 AS
SELECT *
FROM all_objects
WHERE 1=2;
Optional Clauses
The MATCHED and NOT MATCHED clauses are now optional making all of the following examples valid.
-- Both clauses present.
MERGE INTO test1 a
USING all_objects b
ON (a.object_id = b.object_id)
WHEN MATCHED THEN
UPDATE SET a.status = b.status
WHEN NOT MATCHED THEN
INSERT (object_id, status)
VALUES (b.object_id, b.status);
-- No matched clause, insert only.
MERGE INTO test1 a
USING all_objects b
ON (a.object_id = b.object_id)
WHEN NOT MATCHED THEN
INSERT (object_id, status)
VALUES (b.object_id, b.status);
-- No not-matched clause, update only.
MERGE INTO test1 a
USING all_objects b
ON (a.object_id = b.object_id)
WHEN MATCHED THEN
UPDATE SET a.status = b.status;
Conditional Operations
Conditional inserts and updates are now possible by using a WHERE clause on these statements.
-- Both clauses present.
MERGE INTO test1 a
USING all_objects b
ON (a.object_id = b.object_id)
WHEN MATCHED THEN
UPDATE SET a.status = b.status
WHERE b.status != 'VALID'
WHEN NOT MATCHED THEN
INSERT (object_id, status)
VALUES (b.object_id, b.status)
WHERE b.status != 'VALID';
-- No matched clause, insert only.
MERGE INTO test1 a
USING all_objects b
ON (a.object_id = b.object_id)
WHEN NOT MATCHED THEN
INSERT (object_id, status)
VALUES (b.object_id, b.status)
WHERE b.status != 'VALID';
-- No not-matched clause, update only.
MERGE INTO test1 a
USING all_objects b
ON (a.object_id = b.object_id)
WHEN MATCHED THEN
UPDATE SET a.status = b.status
WHERE b.status != 'VALID';
DELETE Clause
An optional DELETE WHERE clause can be used to clean up after a merge operation. Only those rows which match both the ON clause and the DELETE WHERE clause are deleted.
MERGE INTO test1 a
USING all_objects b
ON (a.object_id = b.object_id)
WHEN MATCHED THEN
UPDATE SET a.status = b.status
WHERE b.status != 'VALID'
DELETE WHERE (b.status = 'VALID');
Set Operations:
UNION ALL
Combines the results of two SELECT statements into one result set.
UNION
Combines the results of two SELECT statements into one result set, and then eliminates any duplicate rows from that result set.
MINUS
Takes the result set of one SELECT statement, and removes those rows that are also returned by a second SELECT statement.
INTERSECT
Returns only those rows that are returned by each of two SELECT statements.
Types of SQL Statements
The tables in the following sections provide a functional summary of SQL statements and are divided into these categories:
Data Definition Language (DDL) Statements
Data Manipulation Language (DML) Statements
Transaction Control Statements
Session Control Statements
System Control Statement
Embedded SQL Statements
Data Definition Language (DDL) Statements
Data definition language (DDL) statements let you to perform these tasks:
Create, alter, and drop schema objects
Grant and revoke privileges and roles
Analyze information on a table, index, or cluster
Establish auditing options
Add comments to the data dictionary
The CREATE, ALTER, and DROP commands require exclusive access to the specified object. For example, an ALTER TABLE statement fails if another user has an open transaction on the specified table.
The GRANT, REVOKE, ANALYZE, AUDIT, and COMMENT commands do not require exclusive access to the specified object. For example, you can analyze a table while other users are updating the table.
Oracle Database implicitly commits the current transaction before and after every DDL statement.
ALTER ... (All statements beginning with ALTER)
ANALYZE
ASSOCIATE STATISTICS
AUDIT
COMMENT
CREATE ... (All statements beginning with CREATE)
DISASSOCIATE STATISTICS
DROP ... (All statements beginning with DROP)
FLASHBACK ... (All statements beginning with FLASHBACK)
GRANT
NOAUDIT
PURGE
RENAME
REVOKE
TRUNCATE
UNDROP
Data Manipulation Language (DML) Statements
Data manipulation language (DML) statements access and manipulate data in existing schema objects. These statements do not implicitly commit the current transaction. The data manipulation language statements are:
CALL
DELETE
EXPLAIN PLAN
INSERT
LOCK TABLE
MERGE
SELECT
UPDATE
The SELECT statement is a limited form of DML statement in that it can only access data in the database. It cannot manipulate data in the database, although it can operate on the accessed data before returning the results of the query.
The CALL and EXPLAIN PLAN statements are supported in PL/SQL only when executed dynamically. All other DML statements are fully supported in PL/SQL.
Transaction Control Statements
Transaction control statements manage changes made by DML statements. The transaction control statements are:
COMMIT
ROLLBACK
SAVEPOINT
SET TRANSACTION
All transaction control statements, except certain forms of the COMMIT and ROLLBACK commands, are supported in PL/SQL. For information on the restrictions, see COMMIT and ROLLBACK .
Session Control Statements
Session control statements dynamically manage the properties of a user session. These statements do not implicitly commit the current transaction.
PL/SQL does not support session control statements. The session control statements are:
ALTER SESSION
SET ROLE
System Control Statement
The single system control statement, ALTER SYSTEM, dynamically manages the properties of an Oracle Database instance. This statement does not implicitly commit the current transaction and is not supported in PL/SQL.
Embedded SQL Statements
XMLType Datatype In Oracle9i
Oracle9i has a dedicated XML datatype called XMLTYPE. It is made up of a CLOB to store the original XML data and a number of member functions to make the data available to SQL. In this article I'll present a simple example of it's use.
First we must create a table to store XML documents using the XMLTYPE datatype.
CREATE TABLE tab1 (
col1 SYS.XMLTYPE
);
The table can be populated using XML from a CLOB, VARCHAR2 or an XMLTYPE generated from a query.
DECLARE
v_xml SYS.XMLTYPE;
v_doc CLOB;
BEGIN
-- XMLTYPE created from a CLOB
v_doc := '' || Chr(10) || 'MY_TABLE ';
v_xml := sys.xmltype.createXML(v_doc);
INSERT INTO tab1 (col1) VALUES (v_xml);
-- XMLTYPE created from a query
SELECT SYS_XMLGen(table_name)
INTO v_xml
FROM user_tables
WHERE rownum = 1;
INSERT INTO tab1 (col1) VALUES (v_xml);
COMMIT;
END;
/
The data in the table can be viewed using the following query.
SET LONG 1000
SELECT a.col1.getStringVal()
FROM tab1 a;
A.COL1.GETSTRINGVAL()
----------------------------------------------------------------------------------------------------
MY_TABLE
TAB1
2 rows selected.
SQL>
We can extract the value of specific tags using the following.
SELECT a.col1.extract('//TABLE_NAME/text()').getStringVal() AS "Table Name"
FROM tab1 a
WHERE a.col1.existsNode('/TABLE_NAME') = 1;
Table Name
----------------------------------------------------------------------------------------------------
MY_TABLE
TAB1
2 rows selected.
The LOB datatypes BLOB, CLOB, NCLOB, and BFILE enable you to store and manipulate large blocks of unstructured data (such as text, graphic images, video clips, and sound waveforms) in binary or character format.
START WITH and CONNECT BY in Oracle SQL
select ... start with initial-condition connect by nocycle recurse-condition
select ... connect by recurse-condition
select ... start with initial-condition connect by nocycle recurse-condition
select ... connect by recurse-condition
The start with .. connect by clause can be used to select data that has a hierarchical relationship (usually some sort of parent->child (boss->employee or thing->parts).
It is also being used when an sql execution plan is explained.
recurse-condition can make use of the keyword prior:
connect by
prior foo = bar
This construct establishes the recursion. All records that are part of the next lower hierarchical level are found by having bar = foo. foo is a value found in the current hierarchical level.
http://www.oracle-base.com/articles/misc/rollup-cube-grouping-functions-and-grouping-sets.php
ROLLUP operation to produce subtotal values.
the ROLLUP extension produces group subtotals from right to left and a grand total. If "n" is the number of columns listed in the ROLLUP, there will be n+1 levels of subtotals.
CUBE operation to produce cross-tabulation values.
the CUBE extension will generate subtotals for all combinations of the dimensions specified. If "n" is the number of columns listed in the CUBE, there will be 2n subtotal combinations.
GROUPING function to identify the row values created by ROLLUP or CUBE.
the GROUPING function comes in. It accepts a single column as a parameter and returns "1" if the column contains a null value generated as part of a subtotal by a ROLLUP or CUBE operation or "0" for any other value, including stored null values.
GROUPING_ID function provides an alternate and more compact way to identify subtotal rows. Passing the dimension columns as arguments, it returns a number indicating the GROUP BY level.
GROUP_D It's possible to write queries that return the duplicate subtotals, which can be a little confusing. The GROUP_ID function assigns the value "0" to the first set, and all subsequent sets get assigned a higher number
GROUPING SETS syntax to define multiple groupings in the same query
Calculating all possible subtotals in a cube, especially those with many dimensions, can be quite an intensive process. If you don't need all the subtotals, this can represent a considerable amount of wasted effort.
GROUP BY clause to combine expressions in various ways:
Composite columns
Concatenated grouping sets
create table anydata_test
(id number,
content sys.anydata)
insert into anydata_test values(1, sys.anydata.convertvarchar2('this is some data'))
select id, sys.anydata.gettypename(content) from anydata_test
Using XML in SQL Statements.This section describes some of the ways you can use XMLType data in the database.
XMLType Tables: CREATE TABLE xwarehouses OF XMLTYPE;
data insert : INSERT INTO xwarehouses VALUES
(xmltype('
1
Southlake, Texas
Owned
25000
2
Rear load
true
N
Street
10
query data: SELECT e.getClobVal() FROM xwarehouses e
select case when regexp_like('Google' ,'^[[:alpha:]]{6}$') then 'Match Found' else 'No Match Found' end as output from dual
select case when regexp_like('terminator' ,'^([[:lower:]]{1,10})$') then 'Match Found' else 'No Match Found' end as output from dual
CREATE TABLE contacts
(
l_name VARCHAR2(30),
p_number VARCHAR2(30)
CONSTRAINT p_number_format
CHECK ( REGEXP_LIKE ( p_number, '^\(\d{3}\) \d{3}-\d{4}$' ) )
);
INSERT INTO contacts (p_number)
VALUES( '(650) 555-5555' )
INSERT INTO contacts (p_number)
VALUES( '(215) 555-347' )
CREATE TYPE VARCHAR2_TT AS TABLE OF VARCHAR2(100)
/
Type created.
SELECT column_value AS val
FROM TABLE(VARCHAR2_TT('Apple','Banana','Apricot'))
WHERE column_value NOT LIKE 'A%';
NESTED TABLE is an Oracle data type used to support columns containing multivalued attributes, in this case, columns that can hold an entire sub-table.
CREATE OR REPLACE TYPE my_tab_t AS TABLE OF VARCHAR2(30)
create table nested_table
(
id number,
col1 my_tab_t
)
nested table col1 store as col1_tab
begin
INSERT INTO nested_table VALUES (1, my_tab_t('A'));
INSERT INTO nested_table VALUES (2, my_tab_t('B', 'C'));
INSERT INTO nested_table VALUES (3, my_tab_t('D', 'E', 'F'));
end;
select * from nested_table
A REF CURSOR is basically a data type. A variable created based on such a data type is generally called a cursor variable. A cursor variable can be associated with different queries at run-time. The primary advantage of using cursor variables is their capability to pass result sets between sub programs (like stored procedures, functions, packages etc.).
Let us start with a small sub-program as follows:
declare
type r_cursor is REF CURSOR;
c_emp r_cursor;
en emp.ename%type;
begin
open c_emp for select ename from emp;
loop
fetch c_emp into en;
exit when c_emp%notfound;
dbms_output.put_line(en);
end loop;
close c_emp;
end;
“A collection is an ordered group of elements, all of the same type. It is a general concept that encompasses lists, arrays, and other familiar datatypes. Each element has a unique subscript that determines its position in the collection.
PL/SQL offers these collection types:
· Index-by tables, also known as associative arrays, let you look up elements using arbitrary numbers and strings for subscript values. (They are similar to hash tables in other programming languages.)
· Nested tables hold an arbitrary number of elements. They use sequential numbers as subscripts. You can define equivalent SQL types, allowing nested tables to be stored in database tables and manipulated through SQL.
· Varrays (short for variable-size arrays) hold a fixed number of elements (although you can change the number of elements at runtime). They use sequential numbers as subscripts. You can define equivalent SQL types, allowing varrays to be stored in database tables. They can be stored and retrieved through SQL, but with less flexibility than nested tables.
Although collections can have only one dimension, you can model multi-dimensional arrays by creating collections whose elements are also collections.
To use collections in an application, you define one or more PL/SQL types, then define variables of those types. You can define collection types in a procedure, function, or package. You can pass collection variables as parameters, to move data between client-side applications and stored subprograms.
To look up data that is more complex than single values, you can store PL/SQL records or SQL object types in collections. Nested tables and varrays can also be attributes of object types.”
2. Persistent and non-persistent collections
Index-by tables cannot be stored in database tables, so they are non-persistent.
You cannot use them in a SQL statement and are available only in PL/SQL blocks.
Nested tables and Varrays are persistent. You can use the CREATE TYPE statement to create them in the database, you can read and write them from/to a database column.
Nested tables and Varrays must have been initialized before you can use them.
3. Declarations
3.1 Nested tables
TYPE type_name IS TABLE OF element_type [NOT NULL];
With nested tables declared within PL/SQL, element_type can be any PL/SQL datatype except : REF CURSOR
Nested tables declared in SQL (CREATE TYPE) have additional restrictions. They cannot use the following element types:
q BINARY_INTEGER, PLS_INTEGER
q BOOLEAN
q LONG, LONG RAW
q NATURAL, NATURALN
q POSITIVE, POSITIVEN
q REF CURSOR
q SIGNTYPE
q STRING
PL/SQL
Declare
TYPE TYP_NT_NUM IS TABLE OF NUMBER ;
SQL
CREATE [OR REPLACE] TYPE TYP_NT_NUM IS TABLE OF NUMBER ;
3.2 Varrays
TYPE type_name IS {VARRAY | VARYING ARRAY} (size_limit)
OF element_type [NOT NULL];
size_limit is a positive integer literal representing the maximum number of elements in the array.
PL/SQL
Declare
TYPE TYP_V_CHAR IS VARRAY(10) OF VARCHAR2(20) ;
SQL
CREATE [OR REPLACE] TYPE TYP_V_CHAR IS VARRAY(10) OF VARCHAR2(20) ;
3.3 Index-by tables
TYPE type_name IS TABLE OF element_type [NOT NULL]
INDEX BY [BINARY_INTEGER | PLS_INTEGER | VARCHAR2(size_limit)];
INDEX BY key_type;
The key_type can be numeric, either BINARY_INTEGER or PLS_INTEGER(9i).
It can also be VARCHAR2 or one of its subtypes VARCHAR, STRING, or LONG. You must specify the length of a VARCHAR2-based key, except for LONG which is equivalent to declaring a key type of VARCHAR2(32760).
The types RAW, LONG RAW, ROWID, CHAR, and CHARACTER are not allowed as keys for an associative array.
Declare
TYPE TYP_TAB_VAR IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER ;
4. Initalization
Only Nested tables and varrays need initialization.
To initialize a collection, you use the “constructor” of the collection which name is the same as the collection.
4.1 Nested tables
Declare
TYPE TYP_NT_NUM IS TABLE OF NUMBER ;
Nt_tab TYP_NT_NUM ;
Begin
Nt_tab := TYP_NT_NUM( 5, 10, 15, 20 ) ;
End ;
4.2 Varrays
Declare
TYPE TYP_V_DAY IS VARRAY(7) OF VARCHAR2(15) ;
v_tab TYP_V_DAY ;
Begin
v_tab := TYP_NT_NUM( ‘Sunday’,’Monday’,’Tuesday’,’Wedneday’,’Thursday’,’Friday’,’Saturday’ ) ;
End ;
It is not required to initialize all the elements of a collection. You can either initialize no element. In this case, use an empty constructor.
v_tab := TYP_NT_NUM() ;
This collection is empty, which is different than a NULL collection (not initialized).
4.3 Index-by tables
Declare
TYPE TYP_TAB IS TABLE OF NUMBER INDEX BY PLS_INTEGER ;
my_tab TYP_TAB ;
Begin
my_tab(1) := 5 ;
my_tab(2) := 10 ;
my_tab(3) := 15 ;
End ;
Table fragmentation – when?
If a table is only subject to inserts, there will not be any fragmentation.
Fragmentation comes with when we update/delete data in table.
The space which gets freed up during non-insert DML operations is not immediately re-used (or sometimes, may not get reuse ever at all). This leaves behind holes in table which results in table fragmentation.
To understand it more clearly, we need to be clear on how oracle manages space for tables.
“High water mark” of table actually defines the border line between (ever) used and unused (never) space. While performing full table scan, Oracle will always read the data up to HWM. And if there is lot of free space with-in HWM, that is read too, and hence degrading the performance of FTS.
Now lets see how to identify HWM, unused (never used) space and free space (used but deleted/updated) and then take a call whether the concerned table is candidate for a reorganization or not.
SQL> create table test as select * from dba_tables; -- Create a table
Table created.
SQL> analyze table test compute statistics; -- Analyze it
Table analyzed.
SQL> select blocks "Ever Used", empty_blocks "Never Used", num_rows "Total rows"
2 from user_tables where table_name='TEST'; -- The number of blocks used/free
Ever Used Never Used Total rows
---------- ---------- ----------
49 6 1680
SQL> delete from test where owner='SYS'; --- Im deleting almost half the number of rows.
764 rows deleted.
SQL> commit;
Commit complete.
SQL> analyze table test compute statistics; -- Analyze it again
Table analyzed.
SQL> select blocks "Ever Used", empty_blocks "Never Used", num_rows "Total rows"
2 from user_tables where table_name='TEST'; -- No difference in blocks usage
Ever Used Never Used Total rows
---------- ---------- ----------
49 6 916
PL/SQL procedure successfully completed.
Even though you deleted almost half the rows, the above shows that table HWM is up to 49 blocks, and to perform any FTS, Oracle will go up to 49 blocks to search the data. If your application is so-written that there are many FTS on this table, you may consider, reorganizing this table.
Reasons to reorganization
a) Slower response time (from that table)
b) High number of chained (actually migrated) rows.
c) Table has grown many folds and the old space is not getting reused.
Note: Index based queries may not get that much benefited by reorg as compared to queries which does Full table scan.
How to reorganize?
Before Oracle 10g, there were mainly 2 ways to do the reorganization of the table
a) Export, drop, and import.
b) Alter table move (to another tablespace, or same tablespace).
Oracle 10g provides us a new way of reorganizing the data.
Shrink command: This command is only applicable for tables which are tablespace with auto segment space management.
Before using this command, you should have row movement enabled.
SQL> alter table test enable row movement;
Table altered.
There are 2 ways of using this command.
1. Break in two parts: In first part rearrange rows and in second part reset the HWM.
Part 1: Rearrange (All DML's can happen during this time)
SQL> alter table test shrink space compact;
Table altered.
Part 2: Reset HWM (No DML can happen. but this is fairly quick, infact goes unnoticed.)
SQL> alter table sa shrink space;
Table altered.
2. Do it in one go:
SQL> alter table sa shrink space; (Both rearrange and restting HWM happens in one statement)
Table altered.
Few advantages over the conventional methods
1. Unlike "alter table move ..", indexes are not in UNUSABLE state. After shrink command, indexes are updated also.
2. Its an online operation, So you dont need downtime to do this reorg.
3. It doesnot require any extra space for the process to complete.
http://oracle-online-help.blogspot.com/2007/02/identify-and-fix-table-fragmentation-in.html
MERGE Statement Enhancements in Oracle Database 10g
Oracle 10g includes a number of amendments to the MERGE statement making it more flexible.
Test Table
Optional Clauses
Conditional Operations
DELETE Clause
Related articles.
SQL New Features In Oracle 9i: MERGE Statement
Test Table
The following examples use the table defined below.
CREATE TABLE test1 AS
SELECT *
FROM all_objects
WHERE 1=2;
Optional Clauses
The MATCHED and NOT MATCHED clauses are now optional making all of the following examples valid.
-- Both clauses present.
MERGE INTO test1 a
USING all_objects b
ON (a.object_id = b.object_id)
WHEN MATCHED THEN
UPDATE SET a.status = b.status
WHEN NOT MATCHED THEN
INSERT (object_id, status)
VALUES (b.object_id, b.status);
-- No matched clause, insert only.
MERGE INTO test1 a
USING all_objects b
ON (a.object_id = b.object_id)
WHEN NOT MATCHED THEN
INSERT (object_id, status)
VALUES (b.object_id, b.status);
-- No not-matched clause, update only.
MERGE INTO test1 a
USING all_objects b
ON (a.object_id = b.object_id)
WHEN MATCHED THEN
UPDATE SET a.status = b.status;
Conditional Operations
Conditional inserts and updates are now possible by using a WHERE clause on these statements.
-- Both clauses present.
MERGE INTO test1 a
USING all_objects b
ON (a.object_id = b.object_id)
WHEN MATCHED THEN
UPDATE SET a.status = b.status
WHERE b.status != 'VALID'
WHEN NOT MATCHED THEN
INSERT (object_id, status)
VALUES (b.object_id, b.status)
WHERE b.status != 'VALID';
-- No matched clause, insert only.
MERGE INTO test1 a
USING all_objects b
ON (a.object_id = b.object_id)
WHEN NOT MATCHED THEN
INSERT (object_id, status)
VALUES (b.object_id, b.status)
WHERE b.status != 'VALID';
-- No not-matched clause, update only.
MERGE INTO test1 a
USING all_objects b
ON (a.object_id = b.object_id)
WHEN MATCHED THEN
UPDATE SET a.status = b.status
WHERE b.status != 'VALID';
DELETE Clause
An optional DELETE WHERE clause can be used to clean up after a merge operation. Only those rows which match both the ON clause and the DELETE WHERE clause are deleted.
MERGE INTO test1 a
USING all_objects b
ON (a.object_id = b.object_id)
WHEN MATCHED THEN
UPDATE SET a.status = b.status
WHERE b.status != 'VALID'
DELETE WHERE (b.status = 'VALID');
Set Operations:
UNION ALL
Combines the results of two SELECT statements into one result set.
UNION
Combines the results of two SELECT statements into one result set, and then eliminates any duplicate rows from that result set.
MINUS
Takes the result set of one SELECT statement, and removes those rows that are also returned by a second SELECT statement.
INTERSECT
Returns only those rows that are returned by each of two SELECT statements.
Types of SQL Statements
The tables in the following sections provide a functional summary of SQL statements and are divided into these categories:
Data Definition Language (DDL) Statements
Data Manipulation Language (DML) Statements
Transaction Control Statements
Session Control Statements
System Control Statement
Embedded SQL Statements
Data Definition Language (DDL) Statements
Data definition language (DDL) statements let you to perform these tasks:
Create, alter, and drop schema objects
Grant and revoke privileges and roles
Analyze information on a table, index, or cluster
Establish auditing options
Add comments to the data dictionary
The CREATE, ALTER, and DROP commands require exclusive access to the specified object. For example, an ALTER TABLE statement fails if another user has an open transaction on the specified table.
The GRANT, REVOKE, ANALYZE, AUDIT, and COMMENT commands do not require exclusive access to the specified object. For example, you can analyze a table while other users are updating the table.
Oracle Database implicitly commits the current transaction before and after every DDL statement.
ALTER ... (All statements beginning with ALTER)
ANALYZE
ASSOCIATE STATISTICS
AUDIT
COMMENT
CREATE ... (All statements beginning with CREATE)
DISASSOCIATE STATISTICS
DROP ... (All statements beginning with DROP)
FLASHBACK ... (All statements beginning with FLASHBACK)
GRANT
NOAUDIT
PURGE
RENAME
REVOKE
TRUNCATE
UNDROP
Data Manipulation Language (DML) Statements
Data manipulation language (DML) statements access and manipulate data in existing schema objects. These statements do not implicitly commit the current transaction. The data manipulation language statements are:
CALL
DELETE
EXPLAIN PLAN
INSERT
LOCK TABLE
MERGE
SELECT
UPDATE
The SELECT statement is a limited form of DML statement in that it can only access data in the database. It cannot manipulate data in the database, although it can operate on the accessed data before returning the results of the query.
The CALL and EXPLAIN PLAN statements are supported in PL/SQL only when executed dynamically. All other DML statements are fully supported in PL/SQL.
Transaction Control Statements
Transaction control statements manage changes made by DML statements. The transaction control statements are:
COMMIT
ROLLBACK
SAVEPOINT
SET TRANSACTION
All transaction control statements, except certain forms of the COMMIT and ROLLBACK commands, are supported in PL/SQL. For information on the restrictions, see COMMIT and ROLLBACK .
Session Control Statements
Session control statements dynamically manage the properties of a user session. These statements do not implicitly commit the current transaction.
PL/SQL does not support session control statements. The session control statements are:
ALTER SESSION
SET ROLE
System Control Statement
The single system control statement, ALTER SYSTEM, dynamically manages the properties of an Oracle Database instance. This statement does not implicitly commit the current transaction and is not supported in PL/SQL.
Embedded SQL Statements
XMLType Datatype In Oracle9i
Oracle9i has a dedicated XML datatype called XMLTYPE. It is made up of a CLOB to store the original XML data and a number of member functions to make the data available to SQL. In this article I'll present a simple example of it's use.
First we must create a table to store XML documents using the XMLTYPE datatype.
CREATE TABLE tab1 (
col1 SYS.XMLTYPE
);
The table can be populated using XML from a CLOB, VARCHAR2 or an XMLTYPE generated from a query.
DECLARE
v_xml SYS.XMLTYPE;
v_doc CLOB;
BEGIN
-- XMLTYPE created from a CLOB
v_doc := '' || Chr(10) || '
v_xml := sys.xmltype.createXML(v_doc);
INSERT INTO tab1 (col1) VALUES (v_xml);
-- XMLTYPE created from a query
SELECT SYS_XMLGen(table_name)
INTO v_xml
FROM user_tables
WHERE rownum = 1;
INSERT INTO tab1 (col1) VALUES (v_xml);
COMMIT;
END;
/
The data in the table can be viewed using the following query.
SET LONG 1000
SELECT a.col1.getStringVal()
FROM tab1 a;
A.COL1.GETSTRINGVAL()
----------------------------------------------------------------------------------------------------
2 rows selected.
SQL>
We can extract the value of specific tags using the following.
SELECT a.col1.extract('//TABLE_NAME/text()').getStringVal() AS "Table Name"
FROM tab1 a
WHERE a.col1.existsNode('/TABLE_NAME') = 1;
Table Name
----------------------------------------------------------------------------------------------------
MY_TABLE
TAB1
2 rows selected.
The LOB datatypes BLOB, CLOB, NCLOB, and BFILE enable you to store and manipulate large blocks of unstructured data (such as text, graphic images, video clips, and sound waveforms) in binary or character format.
START WITH and CONNECT BY in Oracle SQL
select ... start with initial-condition connect by nocycle recurse-condition
select ... connect by recurse-condition
select ... start with initial-condition connect by nocycle recurse-condition
select ... connect by recurse-condition
The start with .. connect by clause can be used to select data that has a hierarchical relationship (usually some sort of parent->child (boss->employee or thing->parts).
It is also being used when an sql execution plan is explained.
recurse-condition can make use of the keyword prior:
connect by
prior foo = bar
This construct establishes the recursion. All records that are part of the next lower hierarchical level are found by having bar = foo. foo is a value found in the current hierarchical level.
http://www.oracle-base.com/articles/misc/rollup-cube-grouping-functions-and-grouping-sets.php
ROLLUP operation to produce subtotal values.
the ROLLUP extension produces group subtotals from right to left and a grand total. If "n" is the number of columns listed in the ROLLUP, there will be n+1 levels of subtotals.
CUBE operation to produce cross-tabulation values.
the CUBE extension will generate subtotals for all combinations of the dimensions specified. If "n" is the number of columns listed in the CUBE, there will be 2n subtotal combinations.
GROUPING function to identify the row values created by ROLLUP or CUBE.
the GROUPING function comes in. It accepts a single column as a parameter and returns "1" if the column contains a null value generated as part of a subtotal by a ROLLUP or CUBE operation or "0" for any other value, including stored null values.
GROUPING_ID function provides an alternate and more compact way to identify subtotal rows. Passing the dimension columns as arguments, it returns a number indicating the GROUP BY level.
GROUP_D It's possible to write queries that return the duplicate subtotals, which can be a little confusing. The GROUP_ID function assigns the value "0" to the first set, and all subsequent sets get assigned a higher number
GROUPING SETS syntax to define multiple groupings in the same query
Calculating all possible subtotals in a cube, especially those with many dimensions, can be quite an intensive process. If you don't need all the subtotals, this can represent a considerable amount of wasted effort.
GROUP BY clause to combine expressions in various ways:
Composite columns
Concatenated grouping sets
create table anydata_test
(id number,
content sys.anydata)
insert into anydata_test values(1, sys.anydata.convertvarchar2('this is some data'))
select id, sys.anydata.gettypename(content) from anydata_test
Using XML in SQL Statements.This section describes some of the ways you can use XMLType data in the database.
XMLType Tables: CREATE TABLE xwarehouses OF XMLTYPE;
data insert : INSERT INTO xwarehouses VALUES
(xmltype('
25000
query data: SELECT e.getClobVal() FROM xwarehouses e
select case when regexp_like('Google' ,'^[[:alpha:]]{6}$') then 'Match Found' else 'No Match Found' end as output from dual
select case when regexp_like('terminator' ,'^([[:lower:]]{1,10})$') then 'Match Found' else 'No Match Found' end as output from dual
CREATE TABLE contacts
(
l_name VARCHAR2(30),
p_number VARCHAR2(30)
CONSTRAINT p_number_format
CHECK ( REGEXP_LIKE ( p_number, '^\(\d{3}\) \d{3}-\d{4}$' ) )
);
INSERT INTO contacts (p_number)
VALUES( '(650) 555-5555' )
INSERT INTO contacts (p_number)
VALUES( '(215) 555-347' )
CREATE TYPE VARCHAR2_TT AS TABLE OF VARCHAR2(100)
/
Type created.
SELECT column_value AS val
FROM TABLE(VARCHAR2_TT('Apple','Banana','Apricot'))
WHERE column_value NOT LIKE 'A%';
NESTED TABLE is an Oracle data type used to support columns containing multivalued attributes, in this case, columns that can hold an entire sub-table.
CREATE OR REPLACE TYPE my_tab_t AS TABLE OF VARCHAR2(30)
create table nested_table
(
id number,
col1 my_tab_t
)
nested table col1 store as col1_tab
begin
INSERT INTO nested_table VALUES (1, my_tab_t('A'));
INSERT INTO nested_table VALUES (2, my_tab_t('B', 'C'));
INSERT INTO nested_table VALUES (3, my_tab_t('D', 'E', 'F'));
end;
select * from nested_table
A REF CURSOR is basically a data type. A variable created based on such a data type is generally called a cursor variable. A cursor variable can be associated with different queries at run-time. The primary advantage of using cursor variables is their capability to pass result sets between sub programs (like stored procedures, functions, packages etc.).
Let us start with a small sub-program as follows:
declare
type r_cursor is REF CURSOR;
c_emp r_cursor;
en emp.ename%type;
begin
open c_emp for select ename from emp;
loop
fetch c_emp into en;
exit when c_emp%notfound;
dbms_output.put_line(en);
end loop;
close c_emp;
end;
“A collection is an ordered group of elements, all of the same type. It is a general concept that encompasses lists, arrays, and other familiar datatypes. Each element has a unique subscript that determines its position in the collection.
PL/SQL offers these collection types:
· Index-by tables, also known as associative arrays, let you look up elements using arbitrary numbers and strings for subscript values. (They are similar to hash tables in other programming languages.)
· Nested tables hold an arbitrary number of elements. They use sequential numbers as subscripts. You can define equivalent SQL types, allowing nested tables to be stored in database tables and manipulated through SQL.
· Varrays (short for variable-size arrays) hold a fixed number of elements (although you can change the number of elements at runtime). They use sequential numbers as subscripts. You can define equivalent SQL types, allowing varrays to be stored in database tables. They can be stored and retrieved through SQL, but with less flexibility than nested tables.
Although collections can have only one dimension, you can model multi-dimensional arrays by creating collections whose elements are also collections.
To use collections in an application, you define one or more PL/SQL types, then define variables of those types. You can define collection types in a procedure, function, or package. You can pass collection variables as parameters, to move data between client-side applications and stored subprograms.
To look up data that is more complex than single values, you can store PL/SQL records or SQL object types in collections. Nested tables and varrays can also be attributes of object types.”
2. Persistent and non-persistent collections
Index-by tables cannot be stored in database tables, so they are non-persistent.
You cannot use them in a SQL statement and are available only in PL/SQL blocks.
Nested tables and Varrays are persistent. You can use the CREATE TYPE statement to create them in the database, you can read and write them from/to a database column.
Nested tables and Varrays must have been initialized before you can use them.
3. Declarations
3.1 Nested tables
TYPE type_name IS TABLE OF element_type [NOT NULL];
With nested tables declared within PL/SQL, element_type can be any PL/SQL datatype except : REF CURSOR
Nested tables declared in SQL (CREATE TYPE) have additional restrictions. They cannot use the following element types:
q BINARY_INTEGER, PLS_INTEGER
q BOOLEAN
q LONG, LONG RAW
q NATURAL, NATURALN
q POSITIVE, POSITIVEN
q REF CURSOR
q SIGNTYPE
q STRING
PL/SQL
Declare
TYPE TYP_NT_NUM IS TABLE OF NUMBER ;
SQL
CREATE [OR REPLACE] TYPE TYP_NT_NUM IS TABLE OF NUMBER ;
3.2 Varrays
TYPE type_name IS {VARRAY | VARYING ARRAY} (size_limit)
OF element_type [NOT NULL];
size_limit is a positive integer literal representing the maximum number of elements in the array.
PL/SQL
Declare
TYPE TYP_V_CHAR IS VARRAY(10) OF VARCHAR2(20) ;
SQL
CREATE [OR REPLACE] TYPE TYP_V_CHAR IS VARRAY(10) OF VARCHAR2(20) ;
3.3 Index-by tables
TYPE type_name IS TABLE OF element_type [NOT NULL]
INDEX BY [BINARY_INTEGER | PLS_INTEGER | VARCHAR2(size_limit)];
INDEX BY key_type;
The key_type can be numeric, either BINARY_INTEGER or PLS_INTEGER(9i).
It can also be VARCHAR2 or one of its subtypes VARCHAR, STRING, or LONG. You must specify the length of a VARCHAR2-based key, except for LONG which is equivalent to declaring a key type of VARCHAR2(32760).
The types RAW, LONG RAW, ROWID, CHAR, and CHARACTER are not allowed as keys for an associative array.
Declare
TYPE TYP_TAB_VAR IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER ;
4. Initalization
Only Nested tables and varrays need initialization.
To initialize a collection, you use the “constructor” of the collection which name is the same as the collection.
4.1 Nested tables
Declare
TYPE TYP_NT_NUM IS TABLE OF NUMBER ;
Nt_tab TYP_NT_NUM ;
Begin
Nt_tab := TYP_NT_NUM( 5, 10, 15, 20 ) ;
End ;
4.2 Varrays
Declare
TYPE TYP_V_DAY IS VARRAY(7) OF VARCHAR2(15) ;
v_tab TYP_V_DAY ;
Begin
v_tab := TYP_NT_NUM( ‘Sunday’,’Monday’,’Tuesday’,’Wedneday’,’Thursday’,’Friday’,’Saturday’ ) ;
End ;
It is not required to initialize all the elements of a collection. You can either initialize no element. In this case, use an empty constructor.
v_tab := TYP_NT_NUM() ;
This collection is empty, which is different than a NULL collection (not initialized).
4.3 Index-by tables
Declare
TYPE TYP_TAB IS TABLE OF NUMBER INDEX BY PLS_INTEGER ;
my_tab TYP_TAB ;
Begin
my_tab(1) := 5 ;
my_tab(2) := 10 ;
my_tab(3) := 15 ;
End ;