Tom van Maanen

Create dimensions in Oracle

Oracle has the concept of a dimension. A dimension can be seen as metadata that describe the relation within an existing table. Such metadata can subsequently be used to improve query performance. This implies that a dimension itsself does not contain the data. The data remain in the table. Only the relationsship between the data is described.
The relationship in the table can be described as attributes that determine other attributes. As an example, one may think of a customer_id that determines the customer name, his address, his telephone etc.
It is also possible that some attributes have a child-parent relationship. Think of a town (=child) that lies in a country (parent).
The concept of dimension is linked to the concept of a materialized view. The materialized view can be used in a query rewrite to retrieve the outcomes from a query directly from the materialized view in stead of underlying large tables.
The dimension then makes the optimizer aware of possible synonymns that may extend the usage of data from a materialized view.
As an example, let us start with a query that retrieves outcomes from two large tables. Whenever attributes are used that are also used in a materialized view, the optimizer may decide to retrieve the data from the materialized view instead.
It can also be true that attributes are used in the query that are described in the dimension. The optimizer then translates the attributes thru the dimension into attributes that are used in a materialized view. The result is that a query that is formulated in other attributes as the materialized view can nevertheless take advantage of the materialized view.
A very nice example can be found on swiss website . The author then shows that optimzer really uses a materialized view after the formulation of a dimension.

Writing the contents of a table to a file

In this article, I give an example on how to write the contents of a table to a flat file. I use Oracle as the DBMS. I assume that in this Oracle DBMS, we have a directory that allows flat files to be an integral part of the Oracle DBMS. I have a directory "SAMPLEDATA" that may contain such flat files.

I open the flat file with the statement: utl_file.fopen('SAMPLEDATA','sample1.txt','W').
I can write strings to the flat file with: utl_file.put_line(f,s).
Once the file is opened, I open a table. The table contents is first stored in a so-called cursor, that can be opened with: OPEN T1Cursor.
When the table / cursor is opened, I start a loop that reads every record. Whenever a record is read, the contents is transferred to a string (with FETCH T1Cursor into x_DEPTNO, x_DNAME, x_LOC and s:= '"'||x_DEPTNO||'"'||';'||'"'|| x_DNAME||'"'||';'||'"'|| x_LOC||'"'. Once the string is filled, it is written to the external file.
The programme:

 
CREATE OR REPLACE PROCEDURE SCOTT.SCHRIJF IS 
f utl_file.file_type;
s varchar2(200):='DEPTNO; DNAME; LOC';
CURSOR T1Cursor IS
   SELECT DEPTNO, DNAME, LOC 
FROM 
DEPT;
x_DEPTNO DEPT.DEPTNO%TYPE;
x_DNAME DEPT.DNAME%TYPE;
x_LOC DEPT.LOC%TYPE;
x_DATUM DATE:= SYSDATE;

begin
f := utl_file.fopen('SAMPLEDATA','sample1.txt','W');
utl_file.put_line(f,s);
   OPEN T1Cursor;
     LOOP
       FETCH T1Cursor into x_DEPTNO, x_DNAME, x_LOC;
       EXIT WHEN T1Cursor%NOTFOUND;
       s:= '"'||x_DEPTNO||'"'||';'||'"'|| x_DNAME||'"'||';'||'"'|| x_LOC||'"';
       utl_file.put_line(f,s);       
     END LOOP;
   CLOSE T1Cursor;
utl_file.put_line(f,x_DATUM);
utl_file.fclose(f);
end;
/

Reading from flat files in Oracle

It is possible to read flat files by the Oracle DBMS.
Oracle advises to create a directory to store the files on. This can be one by the command "CREATE DIRECTORY SAMPLEDATA AS 'c:\temp';" that can be issued from the sqlplus command line. The necessary privilegs are set by "GRANT read, write ON DIRECTORY sampledata TO PUBLIC;". Once that is done, we may create a file on the directory - known as c:\temp on the operating system. Let us assume, we have created a file 'sample1.txt' with the brilliant text 'onzin'.

This can be shown from Oracle by the exceution of the PL/SQL procedure below:
First the programme:

 
CREATE OR REPLACE PROCEDURE SCOTT.SCHRIJF IS 
f utl_file.file_type;
s varchar2(200);
begin
f := utl_file.fopen('SAMPLEDATA','sample1.txt','R');
utl_file.get_line(f,s);
utl_file.fclose(f);
dbms_output.put_line(s);
end;
/
The procedure can be issued by:

SQL> exec schrijf
onzin

Maintenance of Fact tables in a data warehouse

In this article, I will present a small PL/SQL programme that maintains a Fact table in a data warehouse.
I assume that we live in a data warehouse where every time a new batch of data is sent to the data warehouse. The new batch of data must be appended to the existing fact table. However, we are not sure if the batch has not been processed before. If we would append the data without checking, the data would be loaded twice. Hence we must introduce a check on a previous load. This is labeled cleaning: delete the records that may belong to a previous data load.
We also realize that the data in the batch have business keys. As we use surrogate keys, the business keys must be translated into surrogate keys. This can done via a look-up to the dimension table.
The program does 3 things:
1: Clean the data warehouse table with records that refer to the same period as the period for which new records are sent to the data warehouse. This will be used if the same data set is reloaded into the data warehouse. In that case, old records must first be deleted from the dat warehouse before loading can be started.
2: Translate business keys into surrogate keys. Such translation is derived thru a lookup in the dimension table
3: append the newly delivered data into the data warehouse.

First the programme:

 
CREATE OR REPLACE PROCEDURE SCOTT.INVOEG (VERWERKDATUM IN VARCHAR) IS 
tmpVar NUMBER;
D_VERWERKDATUM DATE;
CURSOR T1Cursor IS
   SELECT PRIMKEY AS SURKEY_VRUCHT, AANTAL_VERKOCHT, DATUM  
FROM 
(SELECT * FROM INVOER_FEIT WHERE to_CHAR(DATUM,'YYYYMMDD') = to_CHAR(D_VERWERKDATUM,'YYYYMMDD')) A,
(SELECT * FROM DIM_VRUCHT WHERE GELDIG='Y') B
WHERE A.NUMMER=B.NUMMER;
x_DATUM INVOER_FEIT.DATUM%TYPE;
x_AANTAL_VERKOCHT INVOER_FEIT.AANTAL_VERKOCHT%TYPE;
x_SURKEY_VRUCHT DIM_VRUCHT.PRIMKEY%TYPE;
x_BOEL INTEGER :=0;

    
BEGIN
   tmpVar := 0;
   -- Opschomingsprocedure
   D_VERWERKDATUM:=to_DATE(VERWERKDATUM,'YYYYMMDD');
   DELETE FROM SCOTT.FEIT_VRUCHT 
      WHERE 
      to_CHAR(DATUM,'YYYYMMDD') = to_CHAR(D_VERWERKDATUM,'YYYYMMDD') ;
   --Gegevens toevoegen aan Feitentabel
   OPEN T1Cursor;
     LOOP
       FETCH T1Cursor into x_SURKEY_VRUCHT, x_AANTAL_VERKOCHT, x_DATUM;
       EXIT WHEN T1Cursor%NOTFOUND;
       INSERT INTO SCOTT.FEIT_VRUCHT(SURKEY_VRUCHT, AANTAL_VERKOCHT, DATUM) VALUES (x_SURKEY_VRUCHT, x_AANTAL_VERKOCHT, x_DATUM);
     END LOOP;
   CLOSE T1Cursor;
   COMMIT;
END INVOEG;
/

Maintenance of Dimension tables in a data warehouse

In this article, I will present a small PL/SQL programme that maintains a dimension table in a data warehouse. This program uses the logic that is commonly known as a SCD type 2 dimension table.
This program can be copied directly into your Oracle client. After compilation, it can be run by issuing the EXEC command.

The program assumes a dimension that is loaded with data with two attributes: a/ a business key and b/ a description
The business key stems from the operational system. It can be seen as a primary key that is unique in the operational system. However in the data warehouse, we can not assume that the business key can be used. We need to translate with an own key - the surrogate key that is used as a primary key in the data warehouse system.
We also assume that the data is always delivered as a full load: all data available are delivered.
Hence, the first situation is that a complete new record is delivered: the business key is delivered but is not yet known in the data warehouse. In that case the record is inserted in the data warehouse table.
The second situation is that the record exists in the data warehouse but the description is different from the one that is used in the data delivery. In that case, a record in the data warehouse is closed and a new record with new data is inserted.
The third situation is that the record is not delivered but the record still exists in the datawarehouse.
First the programme:

 
CCREATE OR REPLACE PROCEDURE SCOTT.WERK 
IS
CURSOR T1Cursor_Inserts IS
   SELECT VRUCHT, NUMMER FROM SCOTT.INVOER_DIM_VRUCHT WHERE NUMMER NOT in (SELECT NUMMER FROM DIM_VRUCHT WHERE DATUM_UITGANG is NULL);
CURSOR T1Cursor_Updates IS
   SELECT A.VRUCHT, A.NUMMER
   FROM SCOTT.INVOER_DIM_VRUCHT A, SCOTT.DIM_VRUCHT B
   WHERE A.NUMMER=B.NUMMER
   AND A.VRUCHT != B.VRUCHT
   AND B.GELDIG='Y';
   x_VRUCHT EXT_TABLE_CSV.VRUCHT%TYPE DEFAULT 'Manadarijn';
   x_NUMMER EXT_TABLE_CSV.NUMMER%TYPE DEFAULT -9999;
BEGIN
-- Toevoegen nwe records
   OPEN T1Cursor_Inserts;
     LOOP
       FETCH T1Cursor_Inserts into x_VRUCHT,x_NUMMER;
       EXIT WHEN T1Cursor_Inserts%NOTFOUND;
       INSERT INTO DIM_VRUCHT (VRUCHT, NUMMER,PRIMKEY,DATUM_INGANG,GELDIG)  VALUES(x_VRUCHT,x_NUMMER,SEQMAND.NEXTVAL,SYSDATE,'Y');
     END LOOP;
   COMMIT;
   CLOSE T1Cursor_Inserts;
-- Afsluiten oude records
   OPEN T1Cursor_Updates;
   UPDATE  DIM_VRUCHT
   SET GELDIG = 'N', DATUM_UITGANG=SYSDATE
   WHERE NUMMER IN
   (SELECT A.NUMMER FROM SCOTT.INVOER_DIM_VRUCHT A, SCOTT.DIM_VRUCHT B
   WHERE A.NUMMER=B.NUMMER
   AND A.VRUCHT != B.VRUCHT
   AND B.GELDIG='Y');
--Toevoegen geupdate records
     LOOP
       FETCH T1Cursor_Updates into x_VRUCHT,x_NUMMER;
       EXIT WHEN T1Cursor_Updates%NOTFOUND;
       INSERT INTO DIM_VRUCHT (VRUCHT, NUMMER,PRIMKEY,DATUM_INGANG,GELDIG)  VALUES(x_VRUCHT,x_NUMMER,SEQMAND.NEXTVAL,SYSDATE,'Y');
     END LOOP;
   COMMIT;
   CLOSE T1Cursor_Updates;
--verwerken niet aangeleverde dimensierecords
   UPDATE  DIM_VRUCHT
   SET  DATUM_UITGANG=SYSDATE
   WHERE NUMMER NOT IN
   (SELECT A.NUMMER FROM SCOTT.INVOER_DIM_VRUCHT A)
   AND GELDIG='Y' AND DATUM_UITGANG IS NULL;
   COMMIT;
END WERK;
/

	
DROP TABLE SCOTT.INVOER_DIM_VRUCHT CASCADE CONSTRAINTS;

CREATE TABLE SCOTT.INVOER_DIM_VRUCHT
(
  VRUCHT  VARCHAR2(20 BYTE),
  NUMMER  NUMBER
)
ORGANIZATION EXTERNAL
  (  TYPE ORACLE_LOADER
     DEFAULT DIRECTORY EXT_DIR
     ACCESS PARAMETERS 
       ( records delimited  by newline
    fields  terminated by ';'
    missing field values are null
(
VRUCHT,
NUMMER
)
    )
     LOCATION (EXT_DIR:'Invoer_dim_vrucht.csv')
  )
REJECT LIMIT UNLIMITED
NOPARALLEL
NOMONITORING;


-- the Invoer_dim_vrucht.csv looks like:


appel;1;
dadel;10;
kiwi;9;

	
DROP TABLE SCOTT.DIM_VRUCHT CASCADE CONSTRAINTS;

CREATE TABLE SCOTT.DIM_VRUCHT
(
  VRUCHT         VARCHAR2(20 BYTE)              NOT NULL,
  NUMMER         NUMBER                         NOT NULL,
  PRIMKEY        NUMBER,
  DATUM_INGANG   DATE                           NOT NULL,
  DATUM_UITGANG  DATE,
  GELDIG         CHAR(1 BYTE)                   NOT NULL
)

SET DEFINE OFF;
Insert into DIM_VRUCHT
   (VRUCHT, NUMMER, PRIMKEY, DATUM_INGANG, DATUM_UITGANG, 
    GELDIG)
 Values
   ('appel', 1, 43, TO_DATE('10/11/2008 22:25:27', 'MM/DD/YYYY HH24:MI:SS'), NULL, 
    'Y');
Insert into DIM_VRUCHT
   (VRUCHT, NUMMER, PRIMKEY, DATUM_INGANG, DATUM_UITGANG, 
    GELDIG)
 Values
   ('peer', 9, 44, TO_DATE('10/11/2008 22:25:27', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/11/2008 23:30:09', 'MM/DD/YYYY HH24:MI:SS'), 
    'N');
Insert into DIM_VRUCHT
   (VRUCHT, NUMMER, PRIMKEY, DATUM_INGANG, DATUM_UITGANG, 
    GELDIG)
 Values
   ('druif', 9, 46, TO_DATE('10/11/2008 22:52:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/11/2008 23:30:09', 'MM/DD/YYYY HH24:MI:SS'), 
    'N');
Insert into DIM_VRUCHT
   (VRUCHT, NUMMER, PRIMKEY, DATUM_INGANG, DATUM_UITGANG, 
    GELDIG)
 Values
   ('banaan', 10, 47, TO_DATE('10/11/2008 23:05:34', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/11/2008 23:13:52', 'MM/DD/YYYY HH24:MI:SS'), 
    'N');
Insert into DIM_VRUCHT
   (VRUCHT, NUMMER, PRIMKEY, DATUM_INGANG, DATUM_UITGANG, 
    GELDIG)
 Values
   ('dadel', 10, 48, TO_DATE('10/11/2008 23:13:52', 'MM/DD/YYYY HH24:MI:SS'), NULL, 
    'Y');
Insert into DIM_VRUCHT
   (VRUCHT, NUMMER, PRIMKEY, DATUM_INGANG, DATUM_UITGANG, 
    GELDIG)
 Values
   ('druif', 9, 49, TO_DATE('10/11/2008 23:18:33', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/11/2008 23:30:09', 'MM/DD/YYYY HH24:MI:SS'), 
    'N');
Insert into DIM_VRUCHT
   (VRUCHT, NUMMER, PRIMKEY, DATUM_INGANG, DATUM_UITGANG, 
    GELDIG)
 Values
   ('kiwi', 9, 52, TO_DATE('10/11/2008 23:30:49', 'MM/DD/YYYY HH24:MI:SS'), NULL, 
    'Y');
COMMIT;

Copy data via PL/SQL

In this small article, I will present a small Oracle program that copies a table from a table to another table. I like this small program: it is easy / small / directly implementable. The program can be copied and directly copied into sqplus. After compilation, it can be run with "EXEC program". If the procedure is called L_DM_DIM_ORDER_STS_E, we may start the progra with EXEC L_DM_DIM_ORDER_STS_E.

I will shortly discuss the program. First the program is installed in the database with the statement "CREATE PROCEDURE". Within the program, two variables are used (l_start_time and l_execution_id. These variable are used to store the time and the procedurename in an audit table. The latter is implemented with an INSERT INTO statement.

After that each row is read into an object (r_src). Within this object, we store the attributes from each row. Once the values are stored in r_src, we may write the values as an INSERT statement.

This type of procedures can be used when we load a data warehouse. A certain file is sent to a data warehouse. Once it is received, it will be copied into the data warehouse environment. This latter could be done via such a copy procedure.

 
CREATE OR REPLACE
PROCEDURE L_DM_DIM_ORDER_STS_E
     IS
     l_start_time            CONSTANT DATE := SYSDATE ;
     l_execution_id          VARCHAR2(50) := 'L_DM_DIM_ORDER_STATUS';
BEGIN
    execute immediate ('truncate table SCOTT.DEPTKOPIE');
    INSERT INTO SCOTT.AUDITTABEL(TIJD, PROCJE) VALUES( l_start_time, l_execution_id);
    For r_src IN (
        SELECT DEPTNO FROM  SCOTT.DEPT
         ) LOOP
            BEGIN
              insert into SCOTT.DEPTKOPIE (DEPTNO) values (r_src.DEPTNO);
            END;
           END LOOP;
    COMMIT;
END L_DM_DIM_ORDER_STS_E;
/

Profiling data

Profiling data is an important part in the analysis of ETL flows. Roughly stated: ETL is all about getting data from a sourcing ystem to a target system. When the anlysis is undertaken, we need to know the contents of the sourcing system. We need to know for sure if the data comply to business rules. An example of such business rule is that a payment date always occurs after delivery date. We need to verify if that is true or not. Another example: the date of birth may never happen in the future. This must also be verified. During the analysis, we then get an idea whether the data are of good quality or not. If the data always are compliant, the future ETL does not need a modification. If the business rules are not followed, the ETL flow must deal with it, somehow.

In principle, such analysis can be done by a series of SQL statements. One could write per attribute in each table a series of SQL that verify each business rule. For each attribute, one writes a SQL that calculates the maximum, the minimum, the avarage, the median etc etc. This could then be compared to the ideas that one has with the business rules in the background. Again as an example a birth date: the maximum can on only be now (the SYSDATE); the minumum can only be a hundred years back. The average should be about 30-40 years in the past.

However, if thousands of attributes must be gone thru, this becomes cumbersome. One needs to look for an autmated procedure that could help you out. 

 Such programmes exist. Fortunately. Let me discuss three examples.

 Example 1 is the wizard that resides in Oracle Warehouse Builder. This wizard allows profiling on so-called external tables that are imported in Oracle Warehouse Builder.

Example 2 is SQL Server 2008 that has profiling in it.

Example 3 is the freeware program "Profiler" that can be downloaded from www.arrah.in . It has a fully functional version that rolls for free for a limited period. After that, one should either turn the system clock back or pay for the product. The product is easy to install but two remarks are in order (1) the JDK version should be strictly obeyed en (2) it runs with the command "java Profiler". Important to know and no indication on it in the documentation. 

Generate a report from Oracle

Below, I give a script that enables us to generate reports directly from Oracle.

I admit: the lay-out is simple.

I admit: you have to do hand-coding yourself.

But, but, the handcoding is relatively simple. The results are directly visible. You may use Excel to view the reports. And they may be sent to all customers thru conventional methods of bursting reports. One may schedule to mail these reports to any customer you may.

 To start, one must declare a directory from within SQL plus: create directory reports as ‘C:\reports’. This is a one-time declaration.

After that, the script below may be run. This creates a function within the scott schema. Subsequently the function may be run.
 create or replace procedure print_reports_simple is

 cursor c_direct_reports  is
    select
      empno,
      ename,
      job,
      hiredate,
      sal
    from
      onzin;  — view op table emp

  wfile_handle utl_file.file_type;
  v_wstring varchar2 (100);
  v_header varchar2(100);
  v_file varchar2(100);
  v_date varchar2(20);

begin

  v_header :=’empno’||chr(9)||’ename’||chr(9)||’job’||chr(9)||’hiredate’||chr(9)||’sal’;
  v_file := ‘onzin.xls’;
  wfile_handle := utl_file.fopen (’REPORTS’,v_file, ‘W’);
  utl_file.put_line(wfile_handle,v_header);
  for r in c_direct_reports loop
    v_wstring := r.empno||chr(9)||r.ename||chr(9)||r.job||chr(9)||to_char(r.hiredate,’dd/mm/yyyy’)||chr(9)||r.sal;
    utl_file.put_line(wfile_handle,v_wstring);
  end loop;
  utl_file.fclose (wfile_handle);

end print_reports_simple;

To calculate the difference in business days

Below, you will find a script that calculates the number of business days between two dates in Oracle.

Unfortunately, Oracle does not know a function that automatically calculates the number of business days. I can imagine that Oracle does not have such a function. It is really dependant on local customs, regional settings and changing attitude. A small example: this year has the Christmas Eve on Monday, December 24. Most people would assume that Monday is not a normal business day and it should therefore be excluded from such calculation. I wish the programmer good luck to program such logic.

 So you need to prgram such a function yourself. You might use the script below. Take care!

The scripts runs as follows:

create or replace FUNCTION       bdays(start_date IN DATE, end_date IN DATE, region IN CHAR)
RETURN NUMBER
IS
  retval NUMBER(15,7);
  new_start_date date;
  new_end_date date;
  bdaystart number(15,15);
  bdayend number(15,15);
BEGIN
new_start_date := start_date;
new_end_date := end_date;
– set defaults for business day start and end. Can be overridden per region
bdaystart := 7/24;
bdayend := 17/24;
if region=’Europe’ then
  new_start_date := new_start_date + 9/24;
  new_end_date := new_end_date + 9/24;
  bdaystart := 9/24;
  bdayend := 18.5/24;
end if;
if region=’Asia-Pac’ then
  new_start_date := new_start_date + 15/24;
  new_end_date := new_end_date + 15/24;
end if;
–Start After end of day, make start be start of next day
if new_start_date-trunc(new_start_date)>bdayend then
  new_start_date := TRUNC(new_start_date+1)+bdaystart;
end if;
–Start before start of day, make start be start of same day
if new_start_date-trunc(new_start_date) < bdaystart then
  new_start_date := TRUNC(new_start_date) + bdaystart;
end if;
--Start Saturday, make start be Monday start of day
if to_char(new_start_date,'D')=7 THEN
  new_start_date := TRUNC(new_start_date+2)+bdaystart; 
END IF;
--Start Sunday, make start be Monday start of day
if to_char(new_start_date,'D')=1 THEN
  new_start_date := TRUNC(new_start_date+1)+bdaystart;
END IF;
-- end after end of day, make end be end of day same day
if new_end_date-trunc(new_end_date) > bdayend then
  new_end_date := trunc(new_end_date) + bdayend;
end if;
– end before start of day, make end be start of day the same day
if new_end_date-trunc(new_end_date) < bdaystart then
  new_end_date := trunc(new_end_date) + bdaystart;
end if;
–end on Saturday, make it be the end of the day on Friday
if to_char(new_end_date,’D')=7 then
  new_end_date := trunc(new_end_date-1) + bdayend;
end if;
–end on Sunday, make it be the end of the day on Friday
if to_char(new_end_date,’D')=1 then
  new_end_date := trunc(new_end_date-2) + bdayend;
end if;
–factor out weekend days
retval := new_end_date - new_start_date -
((TRUNC(new_end_date,’D') - TRUNC(new_start_date,’D'))/7)*2;
– if holidays were to be calculated, the calculation would go here
– if end is during nonbusiness hours, difference could be negative
if retval < 0 then
  retval := 0;
end if;
RETURN(retval);
END;

Oracle analytical function

I stumbled across a wonderfull website ( http://www.orafusion.com ) that gives us a lot of nice advises on how you might create reports on an Oracle database. How often do we get a request: please give me a pivot table on table xyz in scheme scott, or please give me YTD figures on turn over.

This wonderfull website gives advise on the sql to tackle such questions. Brilliantly written, clear examples. This is the secret toolkit that everybody wants.

 Just 2 examples from this website.

I created a table from:

CREATE TABLE “SCOTT”.”KUBUS”
   ( ”region” VARCHAR2(50 BYTE),
 ”year” VARCHAR2(20 BYTE),
 ”value” VARCHAR2(20 BYTE)
   );
  
–REM INSERTING into KUBUS
Insert into KUBUS (region,year,value) values (’gouda’,'1970′,’100′);
Insert into KUBUS (region,year,value) values (’gouda’,'1971′,’110′);
Insert into KUBUS (region,year,value) values (’gouda’,'1972′,’120′);
Insert into KUBUS (region,year,value) values (’haasdrecht’,'1970′,’20′);
Insert into KUBUS (region,year,value) values (’haasdrecht’,'1971′,’22′);
Insert into KUBUS (region,year,value) values (’haasdrecht’,'1972′,’21′);
Then, I created two sql statements. The first is a statement that creates a pivot table:

select
  t1.”region”,
  t1.”year”,
  sum(t1.”value”)
from
  KUBUS t1
group by
  cube(t1.”region”, t1.”year”)
order by 1, 2
The second one creates a series of YTD figures:

select
  t1.”region”,
  t1.”year”,
  sum(t1.”value”) over (partition by t1.”region”
    order by t1.”year” rows unbounded preceding) YTD_sales
from
  kubus t1
I derived these statements from the http://www.orafusion.com website. Sure: I will be back on this website to get a better understanding of Oracle analytical functions!

Transferring tables between Oracle instances

Oracle knows several ways to transfer tables between Oracle instances. At least three ways are worth considering.

The first way to transfer tables is to create insert statements, like:
Insert into SCOTT.DEPT DEPTNO, DNAME, LOC)
Values (10, ‘ACCOUNTING’, ‘NEW YORK’);

If such statements are collected in a file, one one subsequently execute the script in another Oracle environment.

The second manner is to use the export utility. This is stored somewhere in client applications (mostly under product\99.99.99\db_1\bin) where a small programme is hidden that allows to export a table. The syntax is something like: exp scott/tiger file=emp.dmp tables=(emp,dept). The file stores a binary formatted table.

The table can subsequently be restored by imp scott/tiger file=emp.dmp fromuser=scott touser=scott tables =dept.

The third way is to use the data pump utility. This is done in two steps. First a general step that creates a so-called directory within Oracle (create directory dpump_dir1 as ‘c:\prive\external’;) with its privileges (grant read, write on directory dpump_dir1 to scott;). This step is taken within sqlplus. Having done this, the pump utility can be used:

expdp scott/tiger directory=dpump_dir1 dumpfile=scott.dmp tables=scott.emp.

Just like the earlier export utility, we get a binary file.

It can be imported by impdp hr/tiger tables=scott.emp directory=dpump_dir1 dumpfile=scott.dmp remap_schema=scott:hr

There are many other ways to exchange tables. Sometimes, I store a table in an Access database, that can easily be transported to another environment.
Anyway: many things to choose from!

Quick overview of Oracle tables

One may wish to get a quick overview of Oracle tables in a database. One may be interested in an overview of table_names and number of rows. This can be accomplished with the following script:

• DEFINE table_criteria = “table_name = table_name”
• DEFINE dblink = “d552″
• DEFINE owner_criteria = “owner IN (’DWEM’, ‘STO’)”
• SET SERVEROUTPUT ON SIZE 1000000
• SET VERIFY OFF
• DECLARE
• CURSOR c_tables IS
• SELECT   table_name
• FROM     all_tables@&dblink
• WHERE    &table_criteria
• AND &owner_criteria
• ORDER BY table_name;
• v_count1          INTEGER;
• v_rows_fetched    INTEGER;
• v_cursor          INTEGER := dbms_sql.open_cursor;
• BEGIN
• FOR r1 IN c_tables LOOP
• dbms_sql.parse
• (v_cursor,’SELECT COUNT(*) FROM “‘ || r1.table_name ||’”@&dblink’,dbms_sql.native);
• dbms_sql.define_column (v_cursor, 1, v_count1);
• v_rows_fetched := dbms_sql.execute_and_fetch (v_cursor);
• dbms_sql.column_value (v_cursor, 1, v_count1);
• dbms_output.put_line (r1.table_name || ‘ - ‘ ||LTRIM (TO_CHAR (v_count1, ‘999,999,990′)) ||’ rows on database ‘);
• END LOOP;
• dbms_sql.close_cursor (v_cursor);
• END;
• /

The script is rather straightforward. A cursor is defined that contains all table names that must be investigated.

Then a loop is created that calculates the number of records for each table name involved. In this loop we see some methods. The OPEN_CURSOR function stems from the DBMS_SQL package which opens a cursor and returns a handle. Then the PARSE function checks the syntax of the SQL statement end it associates the SQL statement with the cursor. The EXECUTE_AND_FETCH  statement actually executes the SQL statement. As the query returns results, the DEFINE_COLUMN function is executed to contain the results from the query. Finally, the CLOSE function frees the memory.
       

Protect the data in Oracle

A recent security feature in Oracle is the TDE (transparent data encryption). Its purpose is to create time slots in which the data are available to everyone. At the other side, during the period that the slot is off, the data can not be seen. It looks a bit like a wallet: when the wallet is open, everyone may take a look. When the wallet is closed, nobody may see the data.

The granularity is at column level: when a column is defined, it can also be stated that the column is encrypted ( SAL NUMBER(7,2) ENCRYPT USING’AES192′SALT iso the ordinary SAL NUMBER(7,2) ).

The time slot during which the data can be seen are switched on by:

alter system set encryption wallet open identified by “[password]”; 

Later, the time slot is switched off by:

alter system set wallet close;

Hi that looks convenient! First switch on the wallet, do whatever is needed and then close the wallet. In the off- period, the non-encrypted data can be selected and updated. Only encrypted data are hidden. When an attempt is made to access the data, Oracle returns with an error.

 How to create a wallet. This takes 3 steps:

1: Verify that TDE is enabled. This can be assessed with the query “SELECT * FROM V$OPTION ORDER BY VALUE;”. This displays all options available, including TDE.

2: Create a directory on OS level in which the key wallet can be stored. In my case, I created the directory “C:\oracle\product\10.2.0\admin\TomLaptop\wallet”. This is situated alongside the adump directory.

3: Create a password encryption by alter system set encryption key identified by “[password]”; 

 That is all.

Pumping data in Oracle

There are cases when you would like to access data in another database as the one you are in. For example to load data from database A into database B. Or to query two tables: one from database A and another from database B. This can be accomplished in two steps:

1: Create a database link. This can be created via next command: CREATE DATABASE LINK tom10 CONNECT TO scott IDENTIFIED BY bunvegin USING ‘tom10′; In this command “tom10″ “scott” and “bunvegin” must be replaced by your connect string, userID and password. After the invocation of the command, we are able to access data in the database that is meant by the connect string. We can now access the data just as if we would we logged in in the tom10-database as scott/bunvegin. However, we are still in the original database.

2: The tables in the tom10 database can accessed via {table name}@tom10. Example: select count(*) from bonus@tom10.

The queries are run at remarkable speed. I pumped some data from database A to database B. I noticed that the speed was far above the speed that can be gained in the export / subsequent import statements. In short: a nice feauture from Oracle.

Compress the data in Oracle

The size of a table can be assessed with next statement:

select bytes/1024/1024 “Size in MB” from user_segments where segment_name=’FEIT’;

where ‘FEIT’ is the name of a table. It may happen that the size of the tables become too large. A response might be to compress the table. Fortunately, this can easily be accomplished with the compression command:

 alter table feit move compress;

This command is fairly straight forward. The effect on the size of the table is significant. At the same time, I did not notice any effect on queries. I would have expected that the queries would take longer as data must be uncompressed, but apparently this effect seems to be outweighted by the fact that the number of bytes that are transferred is lower.

I found a nice article( http://www.oracle.com/technology/oramag/oracle/04-mar/o24tech_data.html ) that sums up the pros and cons of compression. In this article, it is mentioned that data loading would take longer. This can be understood as we have the additional time needed to compress the data.

If one would like to decompress the table, one has the command:

alter table feit move nocompress;

This command then returns table “feit” to its original status of non compressed data.

Explain plans in Oracle

Sometimes, you would like to know what is going on in Oracle. In principle such information is revealed if you switch on the autotrace. This is accomplished by “set autotrace on”.

Once a query is run, the underlying actions are revealed. This shows how may read actions are undertaken, how many bytes are sent etc. Comparison of several such outputs displays why a certain query involves more time than another. If one also activates the time (set timing on), one gets an idea why a certain query takes more time than another.

 It is also possible to store information on how a qery will be undertaken in the plan table. This is done via: explain plan for select * from dimensie;

 The information is then revealed via:

select
substr ( lpad(’ ‘, level-1) || operation || ‘ (’ || options || ‘)’,1,30) “Operation”,
object_name “Object”
from plan_table
start with id = 0
connect by prior id=parent_id; 

This command provides us with next results

Operation                      Object
—————————— ———————–
SELECT STATEMENT ()
 TABLE ACCESS (FULL)           DIMENSIE

data dumps

Moving around with large datasets is made possible within Oracle with their datadumps. The idea is that we create a binary file with the table definitions and their data. This file can be moved to another machine on which Oracle runs.

There, on the other machine, the binary file is imported. The table is then recreated and it is loaded with the data.

As an example, we start on the machine that holds two tables that are of interest (emp and dept). First the directory on which the binary is created is declared on Oracle. This is done with the command “create directory”. This command is issued from sqlplus:

in sqlplus:
CREATE OR REPLACE DIRECTORY test_dir AS ‘/u01/app/oracle/oradata/’;
GRANT READ, WRITE ON DIRECTORY test_dir TO scott;

Then the export thru datadump is done with:
on command line:
expdp scott/tiger@tom10 tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log

The binary file holds the table definitions and the dta. This file can be moved to another machine. On the other machine the directory is created with:
in sqlplus:
CREATE OR REPLACE DIRECTORY test_dir AS ‘c:\oradata’;
GRANT READ, WRITE ON DIRECTORY test_dir TO scott;

This is followed by an import on the command line:
impdp scott/tiger@ora10 directory=test_dir dumpfile=scott.dmp

 That is it.