Archive for January, 2013

Store all consistent states of a table: FLASHBACK ARCHIVE

January 25, 2013

Before Oracle 11g it was great challenge to keep previous consistent states of a table.
One of the available method was to store the state (eg. rows of the table) In the UNDO segment. Using the following statements  or similar to them:

 ALTER SYSTEM SET UNDO_RETENTION = 3600;
 ALTER TABLESPACE UNDOTBS1 RETENTION GUARANTEE ;

we can solve the problem, but the solution is VERY EXPENSIVE! Why?
Because we have to store and keep the previous data for each table that will consume huge amount of memory and unnecessary.
The solution is the FLASHBACK ARCHIVE which was introduced in Oracle11g.
Let’s consider the following scenario.
We want to keep all consistent state of our – very important – employees table.
1. Having the FLASHBACK ARCHIVE ADMINISTER system privilege we can create an archive with the following statement:

CREATE FLASHBACK ARCHIVE  archive_cl
 TABLESPACE EXAMPLE QUOTA 1 M RETENTION 1 DAY;

Sorry, one day is not enough!

ALTER FLASHBACK ARCHIVE archive_cl MODIFY RETENTION 1 MONTH;

2. We create a copy of the original employees table:

CREATE TABLE emp3
AS
SELECT * FROM employees;
SELECT COUNT(*) FROM emp3;
COUNT(*)
----------
107

Note, if we had an archived table we have to remove the archive from the table before drop it:

ALTER TABLE EMP3 NO FLASHBACK ARCHIVE ;
DROP TABLE emp3 PURGE;

3. Now we assign an archive object to the table:

ALTER TABLE EMP3  FLASHBACK ARCHIVE archive_cl;

4. It is well known  if we issue  DML statements against a table then the “old” data
will be available in the undo segment temporarly (before COMMIT;).
When the commit is processed these data must be transferred into the desgnated achive
object. We must allow  row movement for this table:

ALTER TABLE EMP3 ENABLE ROW MOVEMENT;

5. We can query the archived tables:

SELECT * FROM DBA_FLASHBACK_ARCHIVE_TABLES;
TABLE_NAME                     OWNER_NAME                     FLASHBACK_ARCHIVE_NAME                 ARCHIVE_TABLE_NAME                                    STATUS
------------------------------ ------------------------------ -------------------------------------- ----------------------------------------------------- --------
EMP3                           ORA1                           ARCHIVE_CL                             SYS_FBA_HIST_185850                                   ENABLED

6. Let’s implement a very simple transaction:

DELETE EMP3 WHERE EMPLOYEE_ID<200;
COMMIT;
SELECT * FROM emp3;
EMPLOYEE_ID FIRST_NAME LAST_NAME   EMAIL      PHONE_NUMBER        HIRE_DATE          JOB_ID     SALARY  COMM MANAGER_ID DEPARTMENT_ID
----------- ---------- ----------- ---------- ------------------- ------------------ ---------- ------ ----- ---------- -------------
203 Susan      Mavris      SMAVRIS    515.123.7777        07-JUN-94          HR_REP       6500              101            40
206 William    Gietz       WGIETZ     515.123.8181        07-JUN-94          AC_ACCOUNT   8300              205           110
202 Pat        Fay         PFAY       603.123.6666        17-AUG-97          MK_REP       6000              201            20
201 Michael    Hartstein   MHARTSTE   515.123.5555        17-FEB-96          MK_MAN      13000              100            20
205 Shelley    Higgins     SHIGGINS   515.123.8080        07-JUN-94          AC_MGR      12000              101           110
204 Hermann    Baaa        HBAER      515.123.8888        07-JUN-94          PR_REP      10000              101            70
200 Jennifer   Whalen      JWHALEN    515.123.4444        17-SEP-87          AD_ASST      4400              101            10

7 rows selected.

7. We can check (with a flashback query) what timestamp is suitable for us and after with FLASHBACK TABLE statement  we can withdraw a previous state of this table:

SELECT * FROM emp3 as of TIMESTAMP
SYSTIMESTAMP - INTERVAL '&Couple_of_minutes' MINUTE;
FLASHBACK TABLE EMP3 TO  TIMESTAMP
 SYSTIMESTAMP - INTERVAL '&Couple_of_minutes' MINUTE;
SELECT * FROM emp3;
.
.
172 Elizabeth  Bates       EBATES     011.44.1343.529268  24-MAR-99          SA_REP       7300   .15        148            80

EMPLOYEE_ID FIRST_NAME LAST_NAME   EMAIL      PHONE_NUMBER        HIRE_DATE          JOB_ID     SALARY  COMM MANAGER_ID DEPARTMENT_ID
----------- ---------- ----------- ---------- ------------------- ------------------ ---------- ------ ----- ---------- -------------
173 Sundita    Kumar       SKUMAR     011.44.1343.329268  21-APR-00          SA_REP       6100   .10        148            80
174 Ellen      Abel        EABEL      011.44.1644.429267  11-MAY-96          SA_REP      11000   .30        149            80
175 Alyssa     Hutton      AHUTTON    011.44.1644.429266  19-MAR-97          SA_REP       8800   .25        149            80
176 Jonathon   Taylor      JTAYLOR    011.44.1644.429265  24-MAR-98          SA_REP       8600   .20        149            80
177 Jack       Livingston  JLIVINGS   011.44.1644.429264  23-APR-98          SA_REP       8400   .20        149            80
178 Kimberely  Grant       KGRANT     011.44.1644.429263  24-MAY-99          SA_REP       7000   .15        149
179 Charles    Johnson     CJOHNSON   011.44.1644.429262  04-JAN-00          SA_REP       6200   .10        149            80

107 rows selected.

Let’s suppose we create an other transaction:

DELETE EMP3 WHERE EMPLOYEE_ID<200;
COMMIT;
SELECT * FROM emp3;

EMPLOYEE_ID FIRST_NAME LAST_NAME   EMAIL      PHONE_NUMBER        HIRE_DATE          JOB_ID     SALARY  COMM MANAGER_ID DEPARTMENT_ID
----------- ---------- ----------- ---------- ------------------- ------------------ ---------- ------ ----- ---------- -------------
201 Michael    Hartstein   MHARTSTE   515.123.5555        17-FEB-96          MK_MAN      13000              100            20
204 Hermann    Baaa        HBAER      515.123.8888        07-JUN-94          PR_REP      10000              101            70
205 Shelley    Higgins     SHIGGINS   515.123.8080        07-JUN-94          AC_MGR      12000              101           110
100 Steven     King        SKING      515.123.4567        17-JUN-87          AD_PRES     24000                             90
101 Neena      Kochhar     NKOCHHAR   515.123.4568        21-SEP-89          AD_VP       17000              100            90
102 Lex        De Haan     LDEHAAN    515.123.4569        13-JAN-93          AD_VP       17000              100            90
108 Nancy      Greenberg   NGREENBE   515.124.4569        17-AUG-94          FI_MGR      12000              101           100
114 Den        Raphaely    DRAPHEAL   515.127.4561        07-DEC-94          PU_MAN      11000              100            30
145 John       Russell     JRUSSEL    011.44.1344.429268  01-OCT-96          SA_MAN      14000   .40        100            80
146 Karen      Partners    KPARTNER   011.44.1344.467268  05-JAN-97          SA_MAN      13500   .30        100            80
147 Alberto    Errazuriz   AERRAZUR   011.44.1344.429278  10-MAR-97          SA_MAN      12000   .30        100            80
148 Gerald     Cambrault   GCAMBRAU   011.44.1344.619268  15-OCT-99          SA_MAN      11000   .30        100            80
149 Eleni      Zlotkey     EZLOTKEY   011.44.1344.429018  29-JAN-00          SA_MAN      10500   .20        100            80
150 Peter      Tucker      PTUCKER    011.44.1344.129268  30-JAN-97          SA_REP      10000   .30        145            80
151 David      Bernstein   DBERNSTE   011.44.1344.345268  24-MAR-97          SA_REP       9500   .25        145            80
156 Janette    King        JKING      011.44.1345.429268  30-JAN-96          SA_REP      10000   .35        146            80
157 Patrick    Sully       PSULLY     011.44.1345.929268  04-MAR-96          SA_REP       9500   .35        146            80
162 Clara      Vishney     CVISHNEY   011.44.1346.129268  11-NOV-97          SA_REP      10500   .25        147            80
163 Danielle   Greene      DGREENE    011.44.1346.229268  19-MAR-99          SA_REP       9500   .15        147            80
168 Lisa       Ozer        LOZER      011.44.1343.929268  11-MAR-97          SA_REP      11500   .25        148            80
169 Harrison   Bloom       HBLOOM     011.44.1343.829268  23-MAR-98          SA_REP      10000   .20        148            80
170 Tayler     Fox         TFOX       011.44.1343.729268  24-JAN-98          SA_REP       9600   .20        148            80
174 Ellen      Abel        EABEL      011.44.1644.429267  11-MAY-96          SA_REP      11000   .30        149            80

23 rows selected.

From DBA_FLASHBACK_ARCHIVE_TABLES we now the name of the archive table, let’s see the content of it:

SELECT rid, STARTSCN,ENDSCN ,xid,OPERATION, EMPLOYEE_ID , SALARY,lAST_NAME, DEPARTMENT_ID FROM SYS_FBA_HIST_185850;

RID                    STARTSCN     ENDSCN XID              O EMPLOYEE_ID SALARY LAST_NAME   DEPARTMENT_ID
-------------------- ---------- ---------- ---------------- - ----------- ------ ----------- -------------
AAAtX6AAEAAAPSTAAJ                33254659                            100  24000 King                   90
AAAtX6AAEAAAPSTAAK                33254659                            101  17000 Kochhar                90
AAAtX6AAEAAAPSTAAL                33254659                            102  17000 De Haan                90
AAAtX6AAEAAAPSTAAM                33254659                            103   9000 Hunold                 60
AAAtX6AAEAAAPSUAAV     33256562   33328409 09001C00AB5F0000 I         103   9000 Hunold                 60
AAAtX6AAEAAAPSTAAN                33254659                            104   6000 Ernst                  60
AAAtX6AAEAAAPSUAAW     33256562   33328409 09001C00AB5F0000 I         104   6000 Ernst                  60
AAAtX6AAEAAAPSTAAO                33254659                            105   4800 Austin                 60
AAAtX6AAEAAAPSUAAX     33256562   33328409 09001C00AB5F0000 I         105   4800 Austin                 60
AAAtX6AAEAAAPSTAAP                33254659                            106   4800 Pataballa              60
AAAtX6AAEAAAPSUAAY     33256562   33328409 09001C00AB5F0000 I         106   4800 Pataballa              60
AAAtX6AAEAAAPSTAAQ                33254659                            107   4200 Lorentz                60
AAAtX6AAEAAAPSUAAZ     33256562   33328409 09001C00AB5F0000 I         107   4200 Lorentz                60
AAAtX6AAEAAAPSTAAR                33254659                            108  12000 Greenberg             100
AAAtX6AAEAAAPSTAAS                33254659                            109   9000 Faviet                100
<pre>

As we can see that – according to different transactions – some rows appear two times
(eg. the employee_id <200 and later salary<10000) with different SCN’s  and some rows
apear with one SCN.
We you want to use th flashback archive feature, please always visit the
flashback archive table (in our case SYS_FBA_HIST_185850)

LISTAGG function versus Tom Kyte’s STRAGG (UDF)

January 18, 2013

LISTAGG  (Oracle defined ) versus
Tom Kyte’s STRAGG (User Defined Aggregate Function)

As we know Oracle implemented the LISTAGG aggregate function in 11gR2.
With this function you can concatenate member row’s value in one grouped row.
The structure of the LISTAGG function is similar to other group functions,
but we have an extra WITHIN GROUP clause in the function definition.
Of course we can use this group function with other group functions:

SELECT department_id "Dept",SUM(salary),COUNT(*),
LISTAGG(last_name||':'||salary, ' | ')
WITHIN GROUP (ORDER By salary, last_name) list
FROM employees
WHERE department_id NOT IN (50,80,100,110)
GROUP BY  department_id;

Dept SUM(SALARY)   COUNT(*) LIST
---------- ----------- ---------- --------------------------------------------------------------------------------------
10        4400          1 Whalen:4400
20       19000          2 Fay:6000 | Hartstein:13000
30       24900          6 Colmenares:2500 | Himuro:2600 | Tobias:2800 | Baida:2900 | Khoo:3100 | Raphaely:11000
40        6500          1 Mavris:6500
60       28800          5 Lorentz:4200 | Austin:4800 | Pataballa:4800 | Ernst:6000 | Hunold:9000
70       10000          1 Baer:10000
90       58000          3 De Haan:17000 | Kochhar:17000 | King:24000

7 rows selected.

Oracle used a simple INDEX FULL SCAN operator with SORT GROUP BY.
Other example: we want to see all direct worker’s name in one row for each manager:

SELECT m.last_name mgr_name,
LISTAGG(e.last_name, ' | ')
WITHIN GROUP (ORDER By e.salary, e.last_name) workers
FROM employees e JOIN employees m
ON e.manager_id=m.employee_id
WHERE e.manager_id IS NOT NULL
GROUP BY m.last_name;
MGR_NAME                  WORKERS
------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------
Cambrault                 Kumar | Bates | Smith | Fox | Bloom | Ozer
De Haan                   Hunold
Errazuriz                 Banda | Ande | Lee | Marvins | Greene | Vishney
Fripp                     Olson | Marlow | Atkinson | Cabrio | Bissot | Dellinger | Bull | Sarchand
Greenberg                 Popp | Sciarra | Urman | Chen | Faviet
Hartstein                 Fay
Higgins                   Gietz
Hunold                    Lorentz | Austin | Pataballa | Ernst
Kaufling                  Philtanker | Gee | Perkins | Gates | Rogers | Mallin | Dilly | Chung
King                      Mourgos | Vollman | Kaufling | Weiss | Fripp | Zlotkey | Cambrault | Raphaely | Errazuriz | Hartstein | Partners | Russell | De Haan | Kochhar
Kochhar                   Whalen | Mavris | Baer | Greenberg | Higgins
Mourgos                   Vargas | Grant | Matos | Oconnell | Feeney | Davies | Walsh | Rajs
Partners                  Sewall | Doran | Smith | Mcewen | Sully | King
Raphaely                  Colmenares | Himuro | Tobias | Baida | Khoo
Russell                   Tuvault | Cambrault | Olsen | Hall | Bernstein | Tucker
Vollman                   Patel | Seo | Jones | Mccain | Stiles | Ladwig | Everett | Bell
Weiss                     Markle | Landry | Sullivan | Mikkilineni | Geoni | Fleaur | Nayer | Taylor
Zlotkey                   Johnson | Grant | Livingston | Taylor | Hutton | Abel

18 rows selected.

We can extend the LISTAGG function with analytic clause:

 SELECT department_id "Dept", last_name "Name",
LISTAGG(last_name, '; ')
WITHIN GROUP (ORDER By department_id, last_name)
OVER (PARTITION BY department_id ) emp
FROM employees
WHERE department_id NOT IN (50,80,100,110);
Dept Name                   EMP
---- ---------------------- ------------------------------------------------------
10   Whalen                 Whalen
20   Fay                    Fay; Hartstein
20   Hartstein              Fay; Hartstein
30   Baida                  Baida; Colmenares; Himuro; Khoo; Raphaely; Tobias
30   Colmenares             Baida; Colmenares; Himuro; Khoo; Raphaely; Tobias
30   Himuro                 Baida; Colmenares; Himuro; Khoo; Raphaely; Tobias
30   Khoo                   Baida; Colmenares; Himuro; Khoo; Raphaely; Tobias
30   Raphaely               Baida; Colmenares; Himuro; Khoo; Raphaely; Tobias
30   Tobias                 Baida; Colmenares; Himuro; Khoo; Raphaely; Tobias
40   Mavris                 Mavris
60   Austin                 Austin; Ernst; Hunold; Lorentz; Pataballa
60   Ernst                  Austin; Ernst; Hunold; Lorentz; Pataballa
60   Hunold                 Austin; Ernst; Hunold; Lorentz; Pataballa
60   Lorentz                Austin; Ernst; Hunold; Lorentz; Pataballa
60   Pataballa              Austin; Ernst; Hunold; Lorentz; Pataballa
70   Baer                   Baer
90   De Haan                De Haan; King; Kochhar
90   King                   De Haan; King; Kochhar
90   Kochhar                De Haan; King; Kochhar

19 rows selected.
 

The result is fine but we have to face some restrictions:

1. The maximum length is 4000 characters.
2. The ORDER BY clause and the window clause  can not be used in the OVER clause. We can specify the query partition clause only!
From the second restriction follows that we can not implement a
cumulative aggregation” for the argument(s) of the LISTAGG!
On of the nicest solution might be  Tom Kyte’s STRAGG implementation.
(See http://www.sqlsnippets.com/en/topic-11591.html)
As we know we can add an analytic clause to a user defined aggregation function!
Now we want to see the all previous names –according to the salary descending order – for each employee in each selected groups:

 SELECT d.department_id deptno ,d.department_name,
 stragg(e.last_name)
 OVER(PARTITION BY d.department_id order by e.last_name ROWS  UNBOUNDED PRECEDING ) names
 FROM departments d, employees e
 WHERE e.department_id=d.department_id
 and e.department_id NOT IN (50,80,100,110);
DEPTNO     DEPARTMENT_NAME      NAMES
---------- -------------------- -----------------------------------------------
10         Administration       Whalen
20         Marketing            Fay
20         Marketing            Fay,Hartstein
30         Purchasing           Baida
30         Purchasing           Baida,Colmenares
30         Purchasing           Baida,Colmenares,Himuro
30         Purchasing           Baida,Colmenares,Himuro,Khoo
30         Purchasing           Baida,Colmenares,Himuro,Khoo,Raphaely
30         Purchasing           Baida,Colmenares,Himuro,Khoo,Raphaely,Tobias
40         Human Resources      Mavris
60         IT                   Austin
60         IT                   Austin,Ernst
60         IT                   Austin,Ernst,Hunold
60         IT                   Austin,Ernst,Hunold,Lorentz
60         IT                   Austin,Ernst,Hunold,Lorentz,Pataballa
70         Public Relations     Baer
90         Executive            De Haan
90         Executive            De Haan,King
90         Executive            De Haan,King,Kochhar

19 rows selected.

You combine the STRAGG function with other analytic functions:

 SELECT d.department_id deptno,last_name,salary,
 ROW_NUMBER() OVER(PARTITION BY d.department_id order by e.SALARY DESC ) COUNTER,
 SUM(salary) OVER (PARTITION BY e.department_id ORDER BY salary DESC
 ROWS UNBOUNDED PRECEDING) cum_sum,
 stragg(e.last_name||': '||SALARY)
 OVER(PARTITION BY d.department_id ORDER BY e.salary DESC ROWS  UNBOUNDED PRECEDING ) EMP_DATA
 FROM departments d, employees e
 WHERE e.department_id=d.department_id
 AND  e.department_id NOT IN (50,80,100,110)
 ORDER BY d.department_id;

DEPTNO LAST_NAME   SALARY    COUNTER    CUM_SUM   EMP_DATA
------- ----------- ------ ---------- ----------  ---------------------------------------------------------------------------------------
10      Whalen        4400          1       4400   Whalen: 4400
20      Hartstein    13000          1      13000   Hartstein: 13000
20      Fay           6000          2      19000   Hartstein: 13000,Fay: 6000
30      Raphaely     11000          1      11000   Raphaely: 11000
30      Khoo          3100          2      14100   Raphaely: 11000,Khoo: 3100
30      Baida         2900          3      17000   Raphaely: 11000,Khoo: 3100,Baida: 2900
30      Tobias        2800          4      19800   Raphaely: 11000,Khoo: 3100,Baida: 2900,Tobias: 2800
30      Himuro        2600          5      22400   Raphaely: 11000,Khoo: 3100,Baida: 2900,Tobias: 2800,Himuro: 2600
30      Colmenares    2500          6      24900   Raphaely: 11000,Khoo: 3100,Baida: 2900,Tobias: 2800,Himuro: 2600,Colmenares: 2500
40      Mavris        6500          1       6500   Mavris: 6500
60      Hunold        9000          1       9000   Hunold: 9000
60      Ernst         6000          2      15000   Hunold: 9000,Ernst: 6000
60      Austin        4800          3      19800   Hunold: 9000,Ernst: 6000,Austin: 4800
60      Pataballa     4800          4      24600   Hunold: 9000,Ernst: 6000,Austin: 4800,Pataballa: 4800
60      Lorentz       4200          5      28800   Hunold: 9000,Ernst: 6000,Austin: 4800,Pataballa: 4800,Lorentz: 4200
70      Baer         10000          1      10000   Baer: 10000
90      King         24000          1      24000   King: 24000
90      De Haan      17000          2      41000   King: 24000,De Haan: 17000
90      Kochhar      17000          3      58000   King: 24000,De Haan: 17000,Kochhar: 17000

19 rows selected.

Before 11gR2 -based on Tom Kyte’s solution- we can solve this problem with an hierarchical query.
(http://tkyte.blogspot.co.uk/2007/03/stringing-them-up.html,
The original query caused an Oracle error for me).

The modified query is the following:

SELECT department_id,
COUNT(SUBSTR(sys_connect_by_path(last_name, ',' ),2)) counter,
MAX(SUBSTR(sys_connect_by_path(last_name, ',' ),2)) emp_name
FROM
(SELECT department_id, last_name,
ROW_NUMBER() OVER(PARTITION BY department_id ORDER BY last_name) rn
FROM employees)
WHERE department_id not in (50,80,100,110)
START WITH rn = 1
connect by
PRIOR rn = rn-1 AND PRIOR department_id = department_id
GROUP BY department_id
ORDER BY department_id;
DEPARTMENT_ID    COUNTER EMP_NAME
------------- ---------- -----------------------------------------------
10                     1 Whalen
20                     2 Fay,Hartstein
30                     6 Baida,Colmenares,Himuro,Khoo,Raphaely,Tobias
40                     1  Mavris
60                     5 Austin,Ernst,Hunold,Lorentz,Pataballa
70                     1 Baer
90                     3 De Haan,King,Kochhar

7 rows selected.

 

I like the LIKE operator!

January 16, 2013

Let suppose the we want to find specific workers using their names from the employees table.
First we want to list the available indexes for that table:

SELECT i.index_name,c.column_position,c.column_name
FROM USER_INDEXES I, USER_IND_COLUMNS C
WHERE i.index_name=c.index_name AND i.table_name='EMPLOYEES'
ORDER BY i.index_name,c.column_position;

LIKE8

As we can observe that the EMP_NAME_IX is compound index
which consists of two columns:
– LAST_NAME
– FIRST_NAME
in this order.
Now we want to find those people whose name starts with capital ‘K’:

SELECT * FROM employees  WHERE  LAST_NAME like 'K%';

like1
We can observe that Oracle used the index with INDEX RANGE SCAN operation which is generally is a good choice.
However, if we add another – similar – predicate into the WHERE clause:

SELECT * FROM employees  WHERE  LAST_NAME LIKE 'K%' OR LAST_NAME LIKE 'L%';

like12

The optimizer has chosen  the FTS (Full Table Scan).
But when we put the indexed columns  into the column list only:

SELECT FIRST_NAME,LAST_NAME FROM employees
WHERE LAST_NAME like 'K%' OR LAST_NAME like 'L%';

like13

The optimizer used the INDEX FULL SCAN operation!
What about if we want to find those people whose name contains a lower ‘k’?

SELECT * FROM employees  WHERE  LAST_NAME LIKE '%k%';

like7

As you can see an FTS (Full Table Scan) was used by the optimizer, because
the the literal was inside the mask (‘%k%’) and all columns were projected.
However, if we are looking for the LAST_NAME first :

SELECT LAST_NAME FROM employees  WHERE  LAST_NAME LIKE '%k%';

like9

we can see that the optimizer used the INDEX FULL SCAN
which is probably faster than the FTS.
Recommendation: If you know only substring of the name,
please use the INDEX FULL SCAN operation,
after – having the full names- you can retrieve all columns using
INDEX RANGE SCAN with ‘literal%’ template.
Observe that if we use the second (not prefixed) column of the index in the WHERE clause and put either the first or other components(only!) in the column list, the optimizer will use an INDEX FULL SCAN operation.

SELECT LAST_NAME FROM employees  WHERE  FIRST_NAME LIKE 'K%';

LIKE10

Generally we can say that using the LIKE operator with well formatted mask and putting the necessary column in the select list we have a good chance for using the suitable index(es).
Please do not use SUBSTR function instead of  LIKE operator even if it is applicable!
(Only if you have a function based index for that expression)