Oracle Analytic Functions

Oracle Analytic Functions compute an aggregate value based on a group of rows. It opens up a whole new way of looking at the data. They differ from aggregate functions in that they return multiple rows for each group. The group of rows is called a window and is defined by the analytic clause. For each row, a sliding window of rows is defined. The window determines the range of rows used to perform the calculations for the current row.

Oracle provides many Analytic Functions such as
AVG, CORR, COVAR_POP, COVAR_SAMP, COUNT, CUME_DIST, DENSE_RANK, FIRST, FIRST_VALUE, LAG, LAST, LAST_VALUE, LEAD, MAX, MIN, NTILE, PERCENT_RANK, PERCENTILE_CONT, PERCENTILE_DISC, RANK, RATIO_TO_REPORT, STDDEV, STDDEV_POP, STDDEV_SAMP, SUM, VAR_POP, VAR_SAMP, VARIANCE.

The Syntax of analytic functions:
Analytic-Function(Column1,Column2,...)
OVER (
[Query-Partition-Clause]
[Order-By-Clause]
[Windowing-Clause]
)

Analytic functions take 0 to 3 arguments.

An Example:
SELECT ename, deptno, sal,
SUM(sal)
OVER (ORDER BY deptno, ename) AS Running_Total,
SUM(sal)
OVER ( PARTITION BY deptno
ORDER BY ename) AS Dept_Total,
ROW_NUMBER()
OVER (PARTITION BY deptno
ORDER BY ename) As Sequence_No
FROM emp
ORDER BY deptno, ename;



For more details visit:

www.dwbiconcepts.com

Why RDBMS against traditional File System

We can store as well as retrieve data from traditional file system. We go for Relational Database Management System to avoid the problems in file system handling which are as follows:

1. Data Redundancy and Inconsistency
2. Difficulty in Accessing data
3. High Complexity in Updating of data
4. Concurrent Access Anomalies
5. Security problem
6. Integrity problem

These clearly shows why we should choose DBMS.

Oracle External Tables - Loading XML file

The Oracle external tables feature allows us to access data in external sources as if it is a table in the database. An XML file is an Extensible Markup Language file with markup and content. It consists of markup constructs like Tag, Attribute and Contents or Element.
Oracle can parse a XML file in many ways like the XML as a datatype, DOM parser .

Now to load a xml file in oracle database with the concept of External Table is shown below:

Suppose we take a xml file namely addressbook.xml which contains address information in xml file format as follows:

<?xml version="1.0" encoding="UTF-8"?>
<addressbook>
<contact>
<contact_name>Sam Disuza </contact_name>
<address>Jurong Point, Singapore</address>
<phone>87654321</phone>
<dob>01121980</dob>
</contact>
<contact>
<contact_name>Suzy Disuza</contact_name>
<address>Jurong West, Singapore</address>
<phone>12345678</phone>
<dob>15081983</dob>
</contact>
</addressbook>

Now the external table definition goes here:

CREATE TABLE addressbook_ext
(contact_name VARCHAR2(2000),
address VARCHAR2(2000),
phone VARCHAR2(2000),
dob VARCHAR2(2000)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY ext_tab_dir
ACCESS PARAMETERS
(
records delimited by "</contact>"
fields
(
dummy1 char(2000) terminated by "<contact>",
contact_name char(2000) enclosed by "<contact_name>" and "</contact_name>",
address char(2000) enclosed by "<address>" and "</address>",
phone char(2000) enclosed by "<phone>" and "</phone>",
dob char(2000) enclosed by "<dob>" and "</dob>"
)
)
LOCATION ('addressbook.xml')
)
PARALLEL
REJECT LIMIT UNLIMITED;

Now we make a query on the external table:
select * from addressbook_ext;

CONTACT_NAME ADDRESS PHONE DOB
Sam Disuza Jurong Point, Singapore 87654321 01121980
Suzy Disuza Jurong West, Singapore 12345678 15081983