Tableau Expert Info: 2014

OLAP Functions

Ø Some features for query processing in oracle include the use of online analytical processing (OLAP) upon the database.

Ø OLAP features are useful for data warehousing and data mart applications.

Ø The OLAP operations are performance enhancements.

Top N Queries
Group By
CUBE
ROLLUP

ROLLUP Option:

Ø It is a group by operation and is used to produce subtotals at any level of the aggregation.

Ø The generated sub totals “Rolled Up” to produce grant total.

Ø The totaling is based on a one dimensional data hierarchy of grouped information.

Syntax:

GROUP BY ROLLUP (column 1, column 2,…)

Illustrations:

Select Deptno, Sum(sal) From Emp group by Rollup(Deptno);
Select Job, Sum(sal) From Emp Group by Rollup(Job);
Select Job,Avg(Sal) From Emp Group by Rollup(Job);

Passing Multiple Columns To Rollup:

Ø When multiple columns are passed to Rollup, the Rollup, groups the rows into blocks with the same column values.

Ex:

Select Deptno, Job, Sum(sal) Salary From Emp Group by Rollup(Deptno, Job);
Select Job, Deptno, Sum(sal) Salary From Emp Group by Rollup(Job, Deptno);
Select Job, Deptno, Avg(sal) Average From Emp Group by Rollup(Job, Deptno);

Ø NULL Values in the output of Rollup operations typically mean that the row contains subtotal or grant total information.

Ø Use NVL () function for proper meaning.

CUBE Option:

Ø It is an extension similar to rollup.

Ø Cube allows specified set of grouping columns and creates sub totals for all possible combinations of them.

Ø The Result of cube is a summary that shows subtotals for every combination of columns or expressions in the group by clause.

Ø The implementation of cube is also called as N-dimensional cross tabulation.

Ex:

Select Deptno, Job, Sum(sal) Salary From Emp Group by Cube(Deptno, Job);
Select Job, Deptno, Sum(Sal) Salary From Emp Group by Cube(Job, Deptno);

Applying Grouping() Function:

Ø The Grouping() Function accepts a column and returns 0 or 1.

Ø Grouping() Function returns 1 when the column value is NULL, and returns 0 when the column value is NOT NULL.

Ø Grouping() Function is used only upon queries that use ROLLUP or CUBE.

Ex:

Select Grouping(Deptno), Deptno, Sum(sal) From Emp Group by Rollup(Deptno);
Select Grouping(Job), Job, Sum(sal) From Emp Group by Rollup(Job);

Grouping() with DECODE():

Ø The Decode() function can be used to convert 1 and 0 returned through Grouping() into a meaningful output.

Ex:

Select Decode(Grouping(deptno), 1,’ALL Departments’, Deptno) Departments, Sum(sal) From Emp Group by Rollup(deptno);
Select Decode(Grouping(Job), 1,’All Designations’, Job) Designations, Sum(sal) From Emp Group by Rollup(Job);

Decode() and Grouping() To Converting Multiple Column Values:

Ex:

Select Decode(Grouping(Deptno), 1,’All Department’, Deptno) Departments, Decode(Grouping(Job), 1,’All Designations’, Job) Designations, Sum(sal) From Emp Group by Rollup(Deptno, Job);

Grouping() with Decode() and CUBE:

Ex:

Select Decode(Grouping(Deptno), 1,’All Departments’, Deptno) Departments, Decode(Grouping(Job), 1,’All Designation’, Job) Designations, Sum(sal) From Emp Group by CUBE(Deptno, Job);

Applying Grouping SETS Clause:

Ø The Grouping Sets clause is used to get subtotal rows.

Ex:

Select Deptno, Job, Sum(sal) From Emp Group by Grouping Sets(Deptno, Job);

Grouping ID() Function:

Ø The Function is used to filter rows using a having clause to exclude rows that do not contain a subtotal or grand total.

Ø The Function accepts one or more columns and returns the decimal equivalent of the grouping bit vector.

Ø The Grouping BIT Vector is computed by combining the results or a call to the grouping() function for each column in order.

Computing the Grouping BIT Vector:

Ø Grouping() Function returns 1 when the column value is NULL, Else returns 0, based on this concept.

Ø Grouping_ID() returns 0, when deptno and job are not null’s.

Ø Grouping_ID() returns 1, If Deptno is not null and job is null.

Ø Grouping_ID() returns 2, if Deptno is NULL and Job is not null.

Ø Grouping_ID() returns 3, if Deptno is null and job is null.

Ex:

Select Deptno, Job, Grouping(deptno) GDPT, Grouping(Job) GJOB, Grouping_ID(Deptno, Job) GRPID, Sum(sal) From Emp Group by CUBE(Deptno, Job);

Grouping_ID() and Having Clause:

Ex:

Select Deptno, Job, Grouping_ID(Deptno, Job) GRPID, Sum(sal) From Emp Group by CUBE(Deptno, Job) Having Grouping_ID(Deptno, Job) > 0;

Representing Column Multiple Times in a Group by clause:

Ø A column can be represented multiple times in a group by clause.

Ex:

Select Deptno, Job, Sum(sal) From Emp Group by Deptno, Rollup(deptno, Job);
Select Deptno, Job, Sum(sal) From Emp Group by Deptno, Cube(Deptno, Job);

Applying Group_ID Function:

Ø The Group_ID() Function is used to remove the duplicate rows returned by group by clause.

Ø The Group_ID() Does not accept any parameters.

Ø If ‘N’ Duplicates exist for a particular grouping, group_ID() returns numbers in the range 0 to N-1.

Ex:

Select Deptno, Job, Group_ID(), Sum(sal) From Emp Group by Deptno, Rollup(Deptno, Job);
Select Deptno, Job, Group_ID(), Sum(sal) From Emp Group by Deptno, CUBE(Deptno, Job);
Select Deptno, Job, Group_ID(), Sum(sal) From Emp Group by Deptno, Rollup(Deptno, Job) Having Group_ID() = 0;

Hierarchical or Recursive Queries

Ø Hierarchical queries are queries that are executed upon tables that contain hierarchical data.

Ø To execute the hierarchical queries, we need the following clauses.

START WITH:

Ø It specifies the root rows of the hierarchy.

CONNECT BY:

Ø It is used to specify the relationship between parent rows and child rows of the hierarchy.

WHERE:

Ø It is used to restrict the rows returned by the query without affecting other rows of the hierarchy.

Steps Followed By Oracle:

Ø Oracle selects the root rows of the hierarchy, which satisfy the condition of the START WITH clause.

Ø Then oracle selects the child rows of each ROOT ROW.

Ø Each child row must satisfy the condition of the CONNECT BY clause, with respect to one of the ROOT ROWS.

Ø Oracle selects successive generations of child rows by identifying the relation in the CONNECT BY clause.

Ø Oracle selects children by evaluating the CONNECT BY condition with respect to the current parent row selected.

Ø If the query contains a where clause, oracle removes all rows from the hierarchy that do not satisfy the condition of the where clause.

Restrictions:

Ø They cannot be used to perform joins.

Ø They cannot select data from a view, whose query perform a join.

Ø If Order by clause is used, then the rows are returned as per the specification in the order by clause.

Ø To define hierarchical queries properly we must use the following clauses.

START WITH Clause.
CONNECT BY Clause.

START WITh Clause:

Ø It identifies the rows to be used as the root of a hierarchical query.

Ø It specifies a condition that the roots must specify.

Ø If START WITH is omitted, oracle uses all rows in the table as ROOT rows.

Ø A START WITH Condition can contain a subquery.

CONNECT BY Clause:

Ø This Clause specifies the relationship between parent and child rows, in a hierarchical query.

Ø This clause contains a condition that defines relationship.

Ø This condition can be any condition as defined by the syntax description.

Ø Within the condition, some part of the condition must use the PRIOR operator, which refers to the parent row.

Ø The format of PRIOR Operator is

PRIOR Expr Comparison operator Expr.
Expr Comparison Operator PRIOR Expr.

Ø The Clause can contain other conditions to further filter the rows selected by the query.

Ø It cannot contain a sub query.

Ex:

Select Ename, Empno, Mgr, Job From Emp CONNECT BY PRIOR Empno = MGR;
Select Ename, Empno, Mgr, Job From Emp START WITH Job =’PRESIDENT’ CONNECT BY PRIOR Empno = MGR;
Select Ename,Empno, Mgr, Job From Emp START WITH Ename =’KING’ CONNECT BY PRIOR Empno = MGR;
Select Ename, Empno, Mgr, Job, Sal From Emp START WITH Sal = 5000 CONNECT BY PRIOR Empno = MGR;
Select Ename, Empno, Mgr, Job, Sal From Emp START WITH Sal = (Select Max(Sal) From Emp ) CONNECT BY PRIOR Empno = MGR;
Select Ename, Empno, Mgr, Job, Sal From Emp START WITH Sal = (Select Max(Sal) From Emp where Deptno = (Select deptno from dept where Dname =’ACCOUNTING’ )) CONNECT BY PRIOR Empno = MGR;
Select Ename, Empno, Mgr, Job, Sal From Emp START WITH Ename =’KING’ CONNECT BY PRIOR Empno = MGR and Job =’MANAGER’;

New Features in Hierarchical Queries:

New Operator:

CONNECT_BY_ROOT

New Pseudo Columns:

CONNECT_BY_ISCYCLE

CONNECT_BY_ISLEAF

New Function:

SYS_CONNECT_BY_PATH

New Keywords:

NOCYCLE

SIBLINGS

CONNECT_BY_ROOT Operator:

Ø CONNECT_BY_ROOT is a UNARY Operator that is valid only in hierarchical queries.

Ø We should qualify a column with this operator, then oracle returns the column value using data from the ROOT Row.

Ø It extends the functionality of the CONNECT BY [PRIOR] condition of hierarchical queries.

Restriction:

Ø We cannot specify this operator in the START WITH Condition or the CONNECT BY condition.

Ex:

Select Ename Name, CONNECT_BY_ROOT(Ename) Boss From Emp START WITH Empno = 7839 CONNECT BY PRIOR Empno = MGR;

SYS_CONNECT_BY_PATH Funtion:

Ø The function returns the path or a column value from ROOT to node, with column values separated by ‘char’ for each row returned by CONNECT BY condition.

Ø Can work on any data type CHAR or VARCHAR2.

Ex:

Select Ename, SYS_CONNECT_BY_PATH(Ename, ‘/’) “path” From Emp START WITH Ename =’KING’ CONNECT BY PRIOR Empno = MGR;

NOCYCLE Keyword:

Ø Cycles are not allowed in a true tree structure. But some hierarchical data may contain cycles.

Ø To allow the “START WITH … CONNECT BY… PRIOR“ construct to work properly even if cycles are present in the data NOCYCLE is used.

Ø The NOCYCLE parameter in the CONNECT BY Condition causes oracle to return the rows in spite of the recursive loop.

Ex:

Select Ename, SYS_CONNECT_BY_PATH(Sal, ‘/’) “path” From Emp START WITH Ename =’KING’ CONNECT BY NOCYCLE PRIOR Empno = MGR;

SIBLINGS Keyword:

Ø The keyword is valid only when we specify the hierarchical query using CONNECT BY Clause.

Ø ORDER SIBLINGS BY clause preserves any ordering specified in the hierarchical query clause.

Ø The ORDER BY clause finally gets applied on the query.

Ø ORDER SIBLINGS BY clause is generally used when we want to order rows of siblings of the same parent.

Ex:

Select Ename, Empno, MGR From Emp START WITH Empno = 7839 CONNECT BY PRIOR Empno = MGR;
Select Ename, Empno, MGR From Emp START WITH Empno = 7839 CONNECT BY PRIOR Empno = MGR Order by Ename;
Select Ename, Empno, MGR From Emp START WITH Empno= 7839 CONNECT BY PRIOR Empno = MGR ORDER SIBLINGS BY Ename;

Synonyms

Ø A synonym is an alternative name for objects such as tables, views, sequences, stored procedures, and other database objects.

Ø By using a synonym, we can avoid the entry of the schema name, when referencing upon objects that belongs to other schema.

Ø The create synonym privilege is necessary to execute the creation of a synonym.

Creating or replacing a synonym

The syntax for creating a synonym is:

· create [or replace] [public] synonym [schema .] synonym_name

For [schema.] object_name;

The or replace phrase allows you to recreate the synonym (if it already exists) without having to issue a DROP synonym command.

The public phrase means that the synonym is a public synonym and is accessible to all users. Remember though that the user must first have the appropriate privileges to the object to use the synonym.

The schema phrase is the appropriate schema. If this phrase is omitted, Oracle assumes that you are referring to your own schema.

The object_name phrase is the name of the object for which you are creating the synonym. It can be one of the following:

Ø table

Ø view

Ø sequence

Ø stored procedure

Ø function

Ø package

Ø materialized view

Ø java class schema object

Ø user-defined object

Ø synonym

For Example

· create public synonym suppliers for app.suppliers;

This first example demonstrates how to create a synonym called suppliers. Now, users of other schemas can reference the table called suppliers without having to prefix the table name with the schema named app. For example:

· select * from suppliers;

If this synonym already existed and you wanted to redefine it, you could always use the or replace phrase as follows:

· create or replace public synonym suppliers for app.suppliers;

Dropping a synonym

It is also possible to drop a synonym. The syntax for dropping a synonym is:

· drop [public] synonym [schema .] synonym_name [force];

The public phrase allows you to drop a public synonym. If you have specified public, then you don't specify a schema.

The force phrase will force Oracle to drop the synonym even if it has dependencies. It is probably not a good idea to use the force phrase as it can cause invalidation of Oracle objects.

For Example

· drop public synonym suppliers;

This drop statement would drop the synonym called suppliers that we defined earlier.

List synonyms in the current schema:

· SELECT synonym_name, table_owner, table_name FROM user_synonyms;

Sequences

Sequences Schema Object:

Ø A sequence is a schema object that can generate unique sequential values.

Ø The Sequence values are often used for primary key’s and unique key’s.

Ø To refer to the current or next value of a sequence in the schema of another user, the following privileges should be available…

o Select Object Privilege

o Select any sequence

Ø For sequence in other schema the qualifying syntax is

o SchemaName.SequenceName.CURRVAL

o SchemaName.SequenceName.NEXTVAL

Ø To refer to the value of a sequence on a remote database, the sequence should be qualified with a complete or partial name of the database link.

o SchemaName.SequenceName.CURRVAL@DBLINK

o SchemaName.SequenceName.NEXTVAL@DBLINK

Ø When a sequence is created, we can define its Initial value and the increment between its values.

Ø The first reference to the NEXTVAL returns the sequence initial value.

Ø Before the CURRVAL can be used for a sequence in a section, first the sequence should be incremented with NEXTVAL.

Ø A Sequence can be accessed by many users concurrently with no waiting, no locking.

Ø CURRVAL and NEXTVAL should be qualified with the name of the sequence.

Creating Sequences:

Purpose:

Ø An Object from which multiple users may generate unique Integers.

Ø Can be used to generate primary key values automatically.

Syntax:

Create Sequence SequenceName

Increment by Integer

Start with Integer

Maxval Integer/NoMaxValue

Minval Integer/NoMinValue

Cycle/NoCycle

Cache Integer/NoCache

Order /NoOrder;

Note:

Ø Sequence can be either incremented sequence or decremented sequence.

Increment by Clause:

Ø Specifies the interval between the sequence numbers.

Ø Value can be positive or negative, but cannot be 0.

Ø If the value is positive it is incremented sequence else it is decremented sequence.

Ø If omitted defaults to increment by 1.

MINVALUE Clause:

Ø Specifies the sequence’s minimum value.

NOMINV ALUE Clause:

Ø Specifies a minimum value of 1 for an ascending sequence or –1 for descending sequence.

MAXVALUE Clause:

Ø Specifies the maximum value that can be generated.

NOMAXVALUE Clause:

Ø Specifies a maximum value of 1027 for ascending sequence OR -1 for descending sequence.

CYCLE Clause:

Ø Specifies the sequence will continue to generate values after reaching either maximum or minimum value.

NOCYCLE Clause:

Ø Specifies the sequence cannot generate more values after the targeted limit.

CACHE Clause:

Ø Specifies the pre-allocation of sequence numbers, the minimum is 2.

NOCACHE Clause:

Ø Specifies the values of a sequence are not pre-allocated.

ORDER Clause:

Ø Guarantees the sequence numbers to be generated in the order or request.

NO ORDER Clause:

Ø Does not guarantee the sequences number with order.

Note:

Ø If the above parameters are not specified by default

START WITH Will Be 1.

INCREMENT BY Will Be Positive 1.

SEQUENCE IS NOCYCLE.

The CACHE Value Will Be 20.

SEQUENCE IS ORDER Sequence.

Illustrations:

Create Table Sample

(

SampID Number(4)

Constraint SampID_PK primary key,

SampName Varchar2(25),

SampDate Date

);

Creation of Incremental Sequence:

Create Sequence SampleSeq

INCREMENT By 1

START WITH 0

MINVALUE 0

MAXVALUE 5

NOCACHE

NOCYCLE;

Activating And Attaching The Sequence To a Table:

INSERT INTO Sample(SampID, SampName, SampDate) Values (SampleSeq.NEXTVAL, ’SAMPLE’,’31-AUG-05’);

Creating A Sequence With Cycle:

Create Sequence SampleSeq

INCREMENT By 1

START WITH 0

MINVALUE 0

MAXVALUE 5

NOCACHE

CYCLE;

Creation of Decremental Sequence:

Create Sequence SampleSeq

INCREMENT By -1

START WITH 5

MAXVALUE 5

MINVALUE 0

NOCACHE

NOCYCLE;

Modifying a Sequence:

Ø The ALTER Command can be used to change the present status of a sequence.

Ø The ALTER Sequence command can be used to change…

Increment Value

Maximum Value

Minimum Value

Cycle Option

Cache Option

Syntax:

ALTER Sequence SequenceName

[INCREMENT By n]

[{MAXVALUE n/ NOMAXVALUE}]

[{MINVALUE n/ NOMINVALUE}]

[{CYCLE / NOCYCEL}]

[{CACHE n / NOCACHE}];

Illustration:

· Alter Sequence SampleSeq

MAXVALUE 10

NOCACHE

NOCYCLE;

Guidelines For Altering a Sequence:

Ø The Alter privilege should be available.

Ø Only the future sequence numbers are affected by the alter sequence statement.

Ø The start with option cannot be changed using alter sequence.

Ø To change the START WITH option, Drop the sequence and then recreate the sequence.

Ø Some validation performed, i.e A new MAXVALUE cannot be imposed that is less than the current sequence number.

Viewing the Current value of a Sequence:

· Select SampleSeq.CURRVAL From Dual;

Dropping An Existing Sequence:

Ø A sequence can be dropped at any time.

Ø Once removed, the sequence can no longer be referenced.

Ex:

· Drop Sequence SampleSeq;

Confirming Sequences:

Ø All Sequences that have been created are documented in the data dictionary.

Ø The Data dictionary in which the information of sequences are stored is USER_OBJECTS.

Ø The settings of the sequence can be confirmed by selecting on USER_SEQUENCES catalog.

Ø Select Sequence_Name, Min_Value, Max_Value, Increment_By, Last_Number From USER_SEQUENCES;

Pages

ad

OLAP Functions

Hierarchical or Recursive Queries

Synonyms

Sequences