SQL Model Clause


bkd@Oracle>select employee_id,
  2               first_name,
  3               salary
  4        from employees
  5        where department_id=20;

EMPLOYEE_ID FIRST_NAME               SALARY
----------- -------------------- ----------
        201 Michael                   13000
        202 Pat                        6000

Using Model Clause,
bkd@Oracle>select  employee_id,
  2     first_name,
  3     salary
  4  from employees
  5  where department_id=20
  6  model
  7     dimension by (employee_id)
  8     measures( first_name, salary)
  9     rules()
 10  /

EMPLOYEE_ID FIRST_NAME               SALARY
----------- -------------------- ----------
        201 Michael                   13000
        202 Pat                        6000

bkd@Oracle>select  employee_id,
  2     first_name,
  3     salary
  4  from employees
  5  where department_id=20
  6  model
  7     dimension by (employee_id)
  8     measures( first_name, salary)
  9     rules(first_name[employee_id]='BKD')
 10  /

EMPLOYEE_ID FIRST_NAME               SALARY
----------- -------------------- ----------
        201 BKD                       13000
        202 BKD                        6000

bkd@Oracle>---aah!!!!
bkd@Oracle>ed
Wrote file afiedt.buf

  1  select  employee_id,
  2     first_name,
  3     salary
  4  from employees
  5  where department_id=20
  6  model
  7     dimension by (employee_id)
  8     measures( first_name, salary)
  9*    rules(first_name[9999]='BKD')
bkd@Oracle>/

EMPLOYEE_ID FIRST_NAME               SALARY
----------- -------------------- ----------
        201 Michael                   13000
        202 Pat                        6000
       9999 BKD

bkd@Oracle>ed
Wrote file afiedt.buf

  1  select  employee_id,
  2     first_name,
  3     salary
  4  from employees
  5  where department_id=20
  6  model
  7     dimension by (employee_id)
  8     measures( first_name, salary)
  9*    rules(first_name[9999]='BKD', salary[8888]=1425000)
bkd@Oracle>/

EMPLOYEE_ID FIRST_NAME               SALARY
----------- -------------------- ----------
        201 Michael                   13000
        202 Pat                        6000
       8888                         1425000
       9999 BKD

Here, we can see that if employee id is already exists then it overwrites the row.     

--creating new row with rule
bkd@Oracle>ed
Wrote file afiedt.buf
  1  select  employee_id,
  2     first_name,
  3     salary
  4  from employees
  5  where department_id=20
  6  model
  7     dimension by (employee_id)
  8     measures( first_name, salary)
  9*    rules(first_name[9999]='BKD', salary[9999]=1425000)
bkd@Oracle>/

EMPLOYEE_ID FIRST_NAME               SALARY
----------- -------------------- ----------
        201 Michael                   13000
        202 Pat                        6000
       9999 BKD                     1425000

Use 'return updated rows'  to return only the new and updated ones:

bkd@Oracle>select  employee_id,
  2     first_name,
  3     salary
  4  from employees
  5  where department_id=20
  6  model
  7     return updated rows
  8     dimension by (employee_id)
  9     measures( first_name, salary)
 10     rules(first_name[9999]='BKD', salary[9999]=1425000)
 11  /

EMPLOYEE_ID FIRST_NAME               SALARY
----------- -------------------- ----------
       9999 BKD                     1425000

Now let's see what happened when we remove the filter on department and add the department_id to

measures.. 

bkd@Oracle>select  employee_id,
  2     first_name,
  3     salary,
  4     department_id
  5  from employees
  6  --where department_id=20
  7  model
  8     return updated rows
  9     dimension by (employee_id)
 10     measures( first_name, salary,department_id )
 11     rules(first_name[9999]='BKD', salary[9999]=1425000)
 12  order by department_id
 13  /

EMPLOYEE_ID FIRST_NAME               SALARY DEPARTMENT_ID
----------- -------------------- ---------- -------------
       9999 BKD                     1425000 

Using the department_id in partition by results...

bkd@Oracle>ed
Wrote file afiedt.buf

  1  select  employee_id,
  2          first_name,
  3          salary,
  4          department_id
  5  from employees
  6  --where department_id=20
  7  model
  8     return updated rows
  9     partition by (department_id)
 10     dimension by (employee_id)
 11     measures( first_name, salary )
 12     rules(first_name[9999]='BKD', salary[9999]=1425000)
 13* order by department_id
bkd@Oracle>/

EMPLOYEE_ID FIRST_NAME               SALARY DEPARTMENT_ID
----------- -------------------- ---------- -------------
       9999 BKD                     1425000            10
       9999 BKD                     1425000            20
       9999 BKD                     1425000            30
       9999 BKD                     1425000            40
       9999 BKD                     1425000            50
       9999 BKD                     1425000            60
       9999 BKD                     1425000            70
       9999 BKD                     1425000            80
       9999 BKD                     1425000            90
       9999 BKD                     1425000           100
       9999 BKD                     1425000           110

EMPLOYEE_ID FIRST_NAME               SALARY DEPARTMENT_ID
----------- -------------------- ---------- -------------
       9999 BKD                     1425000

12 rows selected. 

Regards,

BKD

Creating Object type and usage : Example-I

You  can create object types with the CREATE TYPE and the CREATE TYPE BODY statements. The CREATE TYPE statement specifies the name of the object type, its attributes, methods, and other properties. The CREATE TYPE BODY statement contains the code for the methods that implement the type.

bkd@Oracle>CREATE OR REPLACE TYPE Studentobj as object (
  2  id number,
  3  dob date,
  4  member function getage return number);
  5  /

Type created.

If the a type contains member functions/procedures, the the procedural work needs to be defined in the TYPE BODY(similar to create specification and package BODY)

bkd@Oracle>ed
Wrote file afiedt.buf

  1  create or replace type body studentobj as
  2     member function getage return number as /* subprogram associated with the object type that is referenced as an attribute*/
  3     begin
  4     return trunc(months_between(sysdate , dob)/12);
  5     end;
  6* end;
bkd@Oracle>/

Type body created.

bkd@Oracle>create table student
  2  ( roll number,
  3    studentinf studentobj);

Table created.

bkd@Oracle>insert into student values( 1, studentobj( 123,sysdate-365*5));

1 row created.

bkd@Oracle>select p.id , 

                              p.studentinf.roll, 

                              p.studentinf.dob, 

                              p.studentinf.getage() /* method constructor  studentinf.getage()*/
                     from student p;

        ID STUDENTINF.ROLL STUDENTIN P.STUDENTINF.GETAGE()
---------- ------------- --------- ------------------------------------------------------------------
         1                      123       25-AUG-07                     4

Regards

BKD

Getting distinct values from a column of data type CLOB

-- What I have  learnt today:  
bkd@Oracle>drop table t;

Table dropped.

bkd@Oracle>create table t
  2  as
  3
bkd@Oracle>create table t (col clob);

Table created.

bkd@Oracle>insert into t
  2  (select 'A' from dual
  3  UNION ALL
  4  select 'B' from dual
  5  union all
  6  select 'C' from dual
  7  union all
  8  select 'A' from dual );

4 rows created.

bkd@Oracle>select col from t;

COL
--------------------------------------------------------------------------------
A
B
C
A

bkd@Oracle>--Remove duplicate--
bkd@Oracle>select distinct col from t;
select distinct col from t
                *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CLOB
You can not use DISTINCT in column of data type CLOB.So, how we are going to get the distinct values out of it?

Workaround:

bkd@Oracle>select a.col from t a
  2  where not exists ( select 1 from t b
  3                     where a.rowid < b.rowid
  4                     and dbms_lob.compare(a.col,b.col)=0);

COL
--------------------------------------------------------------------------------
B
C
A

Regards
BKD

Analytical Function Example -II 

RANGE Windows

The general syntax of the <window_clause> is

[ROW or RANGE] BETWEEN <start_expr> AND <end_expr>

<start_expr> can be any one of the following

    UNBOUNDED PECEDING
    CURRENT ROW
    <sql_expr> PRECEDING or FOLLOWING.

    <end_expr> can be any one of the following
    UNBOUNDED FOLLOWING or
    CURRENT ROW or
    <sql_expr> PRECEDING or FOLLOWING.

For RANGE type windows the definition is in terms of values before or after the current ORDER. We will take this up in details latter.
The ROW or RANGE window cannot appear together in one OVER clause.


RANGE Windows

For RANGE windows the general syntax is same as that of ROW:

Function( ) OVER (PARTITION BY <expr1> ORDER BY <expr2> RANGE BETWEEN <start_expr> AND <end_expr>)
or
Function( ) OVER (PARTITION BY <expr1> ORDER BY <expr2> RANGE [<start_expr> PRECEDING or UNBOUNDED PRECEDING]


SQL> select * from t100 order by deptid,sal;

     EMPID        SAL     DEPTID
---------- ---------- ----------
         1       1000         10
         3       1000         10
         2       3000         10
         8       3000         20
         9       4000         20
         3       5000         20
         9       6000         20
         4       7000         20
         5       1000         30

9 rows selected.

SQL> select empid, deptid,
  2      sal,
  3      count(sal) over ( partition by deptid order by sal range between unbounded preceding and (sal/2) preceding ) cnt1,
  4      sum(sal) over ( partition by deptid order by sal range between unbounded preceding and (sal/2) preceding ) val1,
  5      count(sal) over ( partition by deptid order by sal range between 1 preceding and (sal/2) following ) cnt2,
  6      sum(sal) over ( partition by deptid order by sal range between  1 preceding and (sal/2) following ) val2
  7   from
  8   t100
  9  order by deptid
 10  /

     EMPID     DEPTID        SAL       CNT1       VAL1       CNT2       VAL2
---------- ---------- ---------- ---------- ---------- ---------- ----------
         1         10       1000          0                     2       2000
         3         10       1000          0                     2       2000
         2         10       3000          2       2000          1       3000
         8         20       3000          0                     2       7000
         9         20       4000          0                     3      15000
         3         20       5000          0                     3      18000
         9         20       6000          1       3000          2      13000
         4         20       7000          1       3000          1       7000
         5         30       1000          0                     1       1000

9 rows selected.


select  l,
    sum(l) over (  order by l range between 1 preceding and (l/2) preceding) val1,
    count(l) over (  order by l range between 1 preceding and (l/2) preceding) cnt1,
    sum(l) over (  order by l range between 1 preceding and (l/2) following) val2,
    count(l) over (  order by l range between 1 preceding and (l/2) following) cnt2,
    sum(l) over (  order by l range between unbounded preceding and (l/2) preceding) val3,
    count(l) over (  order by l range between unbounded preceding and (l/2) preceding) cnt3,
    sum(l) over (  order by l range between 1 preceding and (l/3) following) val4,
    count(l) over (  order by l range between 1 preceding and (l/2) following) cnt4,
    sum(l) over (  order by l range between 1 following and (l/3) following) val5,
    count(l) over (  order by l range between 1 following and (l/2) following) cnt5
from
g10
/
Output:

         L       VAL1       CNT1       VAL2       CNT2       VAL3       CNT3       VAL4       CNT4       VAL5       CNT5
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1                     0          1          1                     0          1          1                     0
         2          1          1          6          3          1          1          3          3                     1
         3                     0          9          3          1          1          9          3          4          1
         4                     0         18          4          3          2         12          4          5          2
         5                     0         22          4          3          2         15          4          6          2
         6                     0         35          5          6          3         26          5         15          3
         7                     0         40          5          6          3         30          5         17          3
         8                     0         34          4         10          4         34          4         19          2
         9                     0         27          3         10          4         27          3         10          1
        10                     0         19          2         15          5         19          2                     0

Regards,
BKD

Example: Analytical Function -I

SQL> ed
Wrote file afiedt.buf

  1  create table t100
  2  as(
  3  select 1 empid, 1000 sal, 10 deptid from dual
  4  union all
  5  select 2 empid, 3000 sal, 10 deptid from dual
  6  union all
  7  select 3 empid, 5000 sal, 20 deptid from dual
  8  union all
  9  select 4 empid, 7000 sal, 20 deptid from dual
 10  union all
 11  select 5 empid, 1000 sal, 30 deptid from dual
 12* )
SQL> /

Table created.

Elapsed: 00:00:00.68
SQL> select * from t100;

     EMPID        SAL     DEPTID
---------- ---------- ----------
         1       1000         10
         2       3000         10
         3       5000         20
         4       7000         20
         5       1000         30
SQL> insert into t100 values(3,1000,10);

1 row created.

Elapsed: 00:00:00.09
SQL> select * from t100;

     EMPID        SAL     DEPTID
---------- ---------- ----------
         1       1000         10
         2       3000         10
         3       5000         20
         4       7000         20
         5       1000         30
         3       1000         10

6 rows selected.

 select
   row_number() over (order by empid) sl,
   n.*  ,
   row_number() over ( partition by deptid order by  empid) rn,
   max(sal) over() maxsal,
   max(sal) over ( partition by deptid ) maxsal_dept,
   max(sal) keep(dense_rank last order by sal) over ( partition by deptid ) deptmaxsal,
   first_value(sal ignore nulls)  over ( partition by deptid order by sal desc) fvalue,
   last_value(sal ignore nulls)  over ( partition by deptid order by sal desc) lvalue,
   dense_rank()   over (  order by sal) assignedrank,
   rank() over(order by sal) rank,
   sum(sal) over (order by sal rows between current row and 1 following) sum_1,
   sum(sal) over (order by sal rows between 1 preceding and 1 following) sum_2,
   sum(sal) over (order by sal rows between 1 preceding and current row) sum_3
 from
 t100 n
/
  Useful links:
  http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions001.htm#SQLRF06174
  http://psoug.org/reference/analytic_functions.html      
        

Reading Explain Plan  -  Example 1

SQL> ED

Wrote file afiedt.buf

  1  select /*+ GATHER_PLAN_STATISTICS */ department_id,count(*)

  2  from employees

  3  where salary <3000

  4  and job_id='PU_CLERK'

  5* group by department_id

SQL> /

DEPARTMENT_ID   COUNT(*)

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

           30          4

SQL> SET LINESIZE 130

SQL> SELECT *

  2  FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT

select /*+ GATHER_PLAN_STATISTICS */ department_id,count(*) from

employees where salary <3000 and job_id='PU_CLERK' group by

department_id

Plan hash value: 1062631129

--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |      1 |        |      1 |00:00:00.01 |       2 |       |       |          |
|   1 |  HASH GROUP BY               |            |      1 |     11 |      1 |00:00:00.01 |       2 |   888K|   888K|  393K (0)|
|*  2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES  |      1 |   1920 |      4 |00:00:00.01 |       2 |       |       |          |
|*  3 |    INDEX RANGE SCAN          | EMP_JOB_IX |      1 |  46729 |      5 |00:00:00.01 |       1 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

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

   2 - filter("SALARY"<3000)

   3 - access("JOB_ID"='PU_CLERK')

1. Operations 1 and 2 have a single child each (2 and 3, respectively);  the execution starts with operation 3 as child feeds parent ( of course there is some exception Rules) and starts with the lowest level
2. Operation 3, scans the index EMP_JOB_IX  by applying the access predicate
"JOB_ID"='PU_CLERK‘.
It found 5 rowids (see column A-Rows) from the index and passes them to its parent operation (2).
3. Operation 2 accesses the table employees through the 5 rowids passed from operation 3.
For each rowid, a row is read. Then, it applies the filter predicate "SALARY"<3000. This filter leads to the exclusion of one row. The data of the remaining 4 rows are passed to its parent operation (1).
4. Operation 1 performs a GROUP BY on the rows passed from operation 2. The resulting
set is reduced to 1. Since this is the last operation, the data is sent to the caller.

Regards
BKD

ED:  Formatted