Generowanie liczb i dat w Oracle

Aby wygenerować kolejne liczby z pewnego zakresu, albo kolejne daty, które przykładowo potrzebne są nam do raportu o sprzedaży albo aktywności pracowników chcąc uniknąć pisania funkcji PL/SQL musimy w Oracle zastosować pewną sztuczkę. Trzeba bowiem wykorzystać tabelę, która ma dużo rekordów - najlepiej do tego celu nadaje się all_tables lub all_objects:

SQL> select rownum from all_objects where rownum < 10;

    ROWNUM
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9

9 rows selected

W Oracle w wersji 10g i nowszych istnieje również metoda wykorzystująca connect by. Connect by zostało wprowadzone w celu umożliwienia wykonywania zapytań tworzących pewne hierarchie (przykładowo hierarchia pracowników i przełożonych). Korzystając z tej klauzuli można wykorzystać pseudokolumnę level (dodatkowo są dostępne CONNECT_BY_ISCYCLE i CONNECT_BY_ISLEAF) i wygenerować dowolna liczbę wierszy:

SQL> select level from dual connect  by  level < 10;

     LEVEL
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9

9 rows selected

Porównanie planów zapytań oraz oczywiste ograniczenia jakie niesie za sobą użycie all_objects skłania do korzystania z connect by. w przykładzie wygenerujemy kolene dni roku pomiedzy 2006-10-01 a 2007-10-22:
Zapytanie i plan dla all_objects

SELECT to_date('2006-10-01', 'YYYY-MM-DD'  + rownum - 1 day,
                to_char(to_date('2006-10-01', 'YYYY-MM-DD'  + rownum - 1, 'DD'  dd,
                to_char(to_date('2006-10-01', 'YYYY-MM-DD'  + rownum - 1, 'MM'  mm FROM all_objects
                WHERE rownum < (to_date( '2007-10-22', 'YYYY-MM-DD' 
                  - to_date('2006-10-01', 'YYYY-MM-DD'  + 1)
-------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |               |   174 | 51504 |     6  (17)| 00:00:01 |
|*  1 |  COUNT STOPKEY                  |               |       |       |            |          |
|*  2 |   FILTER                        |               |       |       |            |          |
|*  3 |    HASH JOIN                    |               |   385 |   111K|     6  (17)| 00:00:01 |
|   4 |     INDEX FULL SCAN             | I_USER2       |    90 |   360 |     1   (0)| 00:00:01 |
|*  5 |     HASH JOIN                   |               |   385 | 71995 |     5  (20)| 00:00:01 |
|   6 |      INDEX FULL SCAN            | I_USER2       |    90 |  2070 |     1   (0)| 00:00:01 |
|*  7 |      TABLE ACCESS FULL          | OBJ$          |   385 | 31570 |     3   (0)| 00:00:01 |
|*  8 |    TABLE ACCESS BY INDEX ROWID  | IND$          |     1 |     8 |     2   (0)| 00:00:01 |
|*  9 |     INDEX UNIQUE SCAN           | I_IND1        |     1 |       |     1   (0)| 00:00:01 |
|* 10 |    HASH JOIN                    |               |     1 |    24 |     3  (34)| 00:00:01 |
|* 11 |     INDEX RANGE SCAN            | I_OBJAUTH1    |     1 |    11 |     2   (0)| 00:00:01 |
|  12 |     FIXED TABLE FULL            | X$KZSRO       |   100 |  1300 |     0   (0)| 00:00:01 |
|* 13 |    FIXED TABLE FULL             | X$KZSPR       |     1 |    26 |     0   (0)| 00:00:01 |
|* 14 |    FIXED TABLE FULL             | X$KZSPR       |     1 |    26 |     0   (0)| 00:00:01 |
|* 15 |    HASH JOIN                    |               |     1 |    24 |     3  (34)| 00:00:01 |
|* 16 |     INDEX RANGE SCAN            | I_OBJAUTH1    |     1 |    11 |     2   (0)| 00:00:01 |
|  17 |     FIXED TABLE FULL            | X$KZSRO       |   100 |  1300 |     0   (0)| 00:00:01 |
|* 18 |    FIXED TABLE FULL             | X$KZSPR       |     1 |    26 |     0   (0)| 00:00:01 |
|  19 |    NESTED LOOPS                 |               |     2 |    48 |     2   (0)| 00:00:01 |
|* 20 |     INDEX RANGE SCAN            | I_OBJAUTH1    |     1 |    11 |     2   (0)| 00:00:01 |
|* 21 |     FIXED TABLE FULL            | X$KZSRO       |     2 |    26 |     0   (0)| 00:00:01 |
|  22 |    NESTED LOOPS                 |               |       |       |            |          |
|  23 |     NESTED LOOPS                |               |     1 |    77 |     8   (0)| 00:00:01 |
|  24 |      NESTED LOOPS               |               |     1 |    67 |     5   (0)| 00:00:01 |
|  25 |       NESTED LOOPS              |               |     1 |    56 |     4   (0)| 00:00:01 |
|  26 |        MERGE JOIN CARTESIAN     |               |     1 |    52 |     3   (0)| 00:00:01 |
|* 27 |         INDEX RANGE SCAN        | I_OBJ5        |     1 |    39 |     3   (0)| 00:00:01 |
|  28 |         BUFFER SORT             |               |   100 |  1300 |     0   (0)| 00:00:01 |
|  29 |          FIXED TABLE FULL       | X$KZSRO       |   100 |  1300 |     0   (0)| 00:00:01 |
|* 30 |        INDEX RANGE SCAN         | I_USER2       |     1 |     4 |     1   (0)| 00:00:01 |
|* 31 |       INDEX RANGE SCAN          | I_OBJAUTH1    |     1 |    11 |     1   (0)| 00:00:01 |
|* 32 |      INDEX RANGE SCAN           | I_DEPENDENCY1 |     3 |       |     2   (0)| 00:00:01 |
|* 33 |     TABLE ACCESS BY INDEX ROWID | DEPENDENCY$   |     1 |    10 |     3   (0)| 00:00:01 |
|* 34 |    FIXED TABLE FULL             | X$KZSPR       |     1 |    26 |     0   (0)| 00:00:01 |
|  35 |    NESTED LOOPS                 |               |     2 |    76 |     2   (0)| 00:00:01 |
|  36 |     NESTED LOOPS                |               |     1 |    25 |     2   (0)| 00:00:01 |
|* 37 |      TABLE ACCESS BY INDEX ROWID| TRIGGER$      |     1 |    14 |     1   (0)| 00:00:01 |
|* 38 |       INDEX UNIQUE SCAN         | I_TRIGGER2    |     1 |       |     0   (0)| 00:00:01 |
|* 39 |      INDEX RANGE SCAN           | I_OBJAUTH1    |     1 |    11 |     1   (0)| 00:00:01 |
|* 40 |     FIXED TABLE FULL            | X$KZSRO       |     2 |    26 |     0   (0)| 00:00:01 |
|* 41 |    FIXED TABLE FULL             | X$KZSPR       |     1 |    26 |     0   (0)| 00:00:01 |
|  42 |    NESTED LOOPS                 |               |       |       |            |          |
|  43 |     NESTED LOOPS                |               |     1 |    77 |     8   (0)| 00:00:01 |
|  44 |      NESTED LOOPS               |               |     1 |    67 |     5   (0)| 00:00:01 |
|  45 |       NESTED LOOPS              |               |     1 |    56 |     4   (0)| 00:00:01 |
|  46 |        MERGE JOIN CARTESIAN     |               |     1 |    52 |     3   (0)| 00:00:01 |
|* 47 |         INDEX RANGE SCAN        | I_OBJ5        |     1 |    39 |     3   (0)| 00:00:01 |
|  48 |         BUFFER SORT             |               |   100 |  1300 |     0   (0)| 00:00:01 |
|  49 |          FIXED TABLE FULL       | X$KZSRO       |   100 |  1300 |     0   (0)| 00:00:01 |
|* 50 |        INDEX RANGE SCAN         | I_USER2       |     1 |     4 |     1   (0)| 00:00:01 |
|* 51 |       INDEX RANGE SCAN          | I_OBJAUTH1    |     1 |    11 |     1   (0)| 00:00:01 |
|* 52 |      INDEX RANGE SCAN           | I_DEPENDENCY1 |     3 |       |     2   (0)| 00:00:01 |
|* 53 |     TABLE ACCESS BY INDEX ROWID | DEPENDENCY$   |     1 |    10 |     3   (0)| 00:00:01 |
|* 54 |    FIXED TABLE FULL             | X$KZSPR       |     1 |    26 |     0   (0)| 00:00:01 |
|* 55 |    FIXED TABLE FULL             | X$KZSPR       |     1 |    26 |     0   (0)| 00:00:01 |
|* 56 |    FIXED TABLE FULL             | X$KZSPR       |     1 |    26 |     0   (0)| 00:00:01 |
|* 57 |    FIXED TABLE FULL             | X$KZSPR       |     1 |    26 |     0   (0)| 00:00:01 |
|* 58 |    FIXED TABLE FULL             | X$KZSPR       |     1 |    26 |     0   (0)| 00:00:01 |
|* 59 |    FIXED TABLE FULL             | X$KZSPR       |     1 |    26 |     0   (0)| 00:00:01 |
|* 60 |    FIXED TABLE FULL             | X$KZSPR       |     1 |    26 |     0   (0)| 00:00:01 |
|* 61 |    FIXED TABLE FULL             | X$KZSPR       |     1 |    26 |     0   (0)| 00:00:01 |
|* 62 |    FIXED TABLE FULL             | X$KZSPR       |     1 |    26 |     0   (0)| 00:00:01 |
|* 63 |    FIXED TABLE FULL             | X$KZSPR       |     1 |    26 |     0   (0)| 00:00:01 |
|* 64 |    FIXED TABLE FULL             | X$KZSPR       |     1 |    26 |     0   (0)| 00:00:01 |
|* 65 |    FIXED TABLE FULL             | X$KZSPR       |     1 |    26 |     0   (0)| 00:00:01 |
|* 66 |    FIXED TABLE FULL             | X$KZSPR       |     1 |    26 |     0   (0)| 00:00:01 |
|* 67 |    FIXED TABLE FULL             | X$KZSPR       |     1 |    26 |     0   (0)| 00:00:01 |
|* 68 |    FIXED TABLE FULL             | X$KZSPR       |     1 |    26 |     0   (0)| 00:00:01 |
|* 69 |    FIXED TABLE FULL             | X$KZSPR       |     1 |    26 |     0   (0)| 00:00:01 |
|* 70 |    FIXED TABLE FULL             | X$KZSPR       |     1 |    26 |     0   (0)| 00:00:01 |
|* 71 |    FIXED TABLE FULL             | X$KZSPR       |     1 |    26 |     0   (0)| 00:00:01 |
|  72 |    VIEW                         |               |     1 |    13 |     2   (0)| 00:00:01 |
|  73 |     FAST DUAL                   |               |     1 |       |     2   (0)| 00:00:01 |
|  74 |    NESTED LOOPS                 |               |     1 |    28 |     2   (0)| 00:00:01 |
|* 75 |     INDEX FULL SCAN             | I_USER2       |     1 |    20 |     1   (0)| 00:00:01 |
|* 76 |     INDEX RANGE SCAN            | I_OBJ4        |     1 |     8 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        134  consistent gets
          0  physical reads
          0  redo size
       9930  bytes sent via SQL*Net to client
        695  bytes received via SQL*Net from client
         27  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        386  rows processed

Zapytanie i plan dla connect by

select to_date('2006-10-01', 'YYYY-MM-DD'  + rownum - 1 day,
                    to_char(to_date('2006-10-01', 'YYYY-MM-DD'  + rownum - 1, 'DD'  dd,
                    to_char(to_date('2006-10-01', 'YYYY-MM-DD'  + rownum - 1, 'MM'  mm FROM dual
                    connect  by  level < (to_date( '2007-10-22', 'YYYY-MM-DD' 
                                         - to_date('2006-10-01', 'YYYY-MM-DD'  + 1)

------------------------------------------------------------------------------
| Id  | Operation                     | Name | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      |     1 |     2   (0)| 00:00:01 |
|   1 |  COUNT                        |      |       |            |          |
|*  2 |   CONNECT BY WITHOUT FILTERING|      |       |            |          |
|   3 |    FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
       9930  bytes sent via SQL*Net to client
        695  bytes received via SQL*Net from client
         27  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        386  rows processed

Dodatkowe źródła:

• Hierarchical Queries @ Oracle® Database SQL Language Reference

PL/SQL w Oracle 11g: Function Result Cache

Jedną z nowości w systemie zarządzania bazą danych Oracle 11g jest “Function Result Cache”. Podczas tworzenia funkcji w języku PL/SQL można dodać klauzulę ‘RESULT_CACHE’ określającą, że wynik funkcji ma być przechowywany w pamięci podręcznej (dokładniej w Shared Global Area, SGA) w celu ponownego wykorzystania w przyszłości. Przy ponownym wywołaniu funkcji dla tych samych argumentów co poprzednio wynik będzie pobrany z pamięci zamiast wyliczany jeszcze raz. Ma to wpływ głównie na czas wykonywania skomplikowanych i czasochłonnych funkcji.

Umieszczenie wyniku funkcji w obszarze SGA umożliwia dostęp do wyników dla wszystkich sesji - nie tylko dla sesji, która wykonała funkcję z klauzulą ‘RESULT_CACHE’.

Przykład:

CREATE OR REPLACE FUNCTION test_result_cache(p_employee_id IN NUMBER) RETURN NUMBER AS
v_salary NUMBER;
BEGIN
sys.dbms_lock.sleep(10);
select salary into v_salary from EMPLOYEES where employee_id = p_employee_id;
RETURN(v_salary);
END;
/
SQL> set timing on;
SQL> select test_result_cache(110) from dual;
TEST_RESULT_CACHE(110)
———————-
8200

Elapsed: 00:00:10.21
SQL> select test_result_cache(110) from dual;
TEST_RESULT_CACHE(110)
———————-
8200

Elapsed: 00:00:10.02

Teraz zmienimy deklarację funkcji dodając ‘RESULT_CACHE’:

SQL> CREATE OR REPLACE FUNCTION test_result_cache(p_employee_id IN NUMBER) RETURN NUMBER RESULT_CACHE AS
v_salary NUMBER;
BEGIN
sys.dbms_lock.sleep(10);
select salary into v_salary from EMPLOYEES where employee_id = p_employee_id;
RETURN(v_salary);
END;
/

SQL> select test_result_cache(110) from dual;
TEST_RESULT_CACHE(110)
———————-
8200

Elapsed: 00:00:10.10
SQL> select test_result_cache(110) from dual;
TEST_RESULT_CACHE(110)
———————-
8200

Elapsed: 00:00:00.00

Widać, że pierwsze wywołanie nadal zajmuje 10 sekund, jednak przy kolejnym wartość funkcji pobierana jest z pamięci a nie wyliczana ponownie.

W przypadku, gdy funkcja podczas wyznaczania wartości korzysta z tabel lub perspektyw warto wykorzystać dodatkowo klauzulę ‘RELIES_ON’. Umożliwia ona uniknięcie błędnych wyników w przypadku, gdy na tabeli wykonywane są operacje DML. Kolejny przykład:

SQL> update EMPLOYEES set salary = 16000 where employee_id = 110;

1 row updated.

Elapsed: 00:00:00.20
SQL> select test_result_cache(110) from dual;
TEST_RESULT_CACHE(110)
———————-
8200

Elapsed: 00:00:00.01

Aby usunąć błąd z niewłaściwą wartością dodajemy RELIES_ON (EMPLOYEES):

SQL> CREATE OR REPLACE FUNCTION test_result_cache(p_employee_id IN NUMBER) RETURN NUMBER RESULT_CACHE RELIES_ON (EMPLOYEES) AS
2 v_salary NUMBER;
BEGIN
sys.dbms_lock.sleep(10);
select salary into v_salary from EMPLOYEES where employee_id = p_employee_id;
RETURN(v_salary);
END;
/

SQL> select test_result_cache(110) from dual;
TEST_RESULT_CACHE(110)
———————-
16000

Elapsed: 00:00:10.04
SQL> select test_result_cache(110) from dual;
TEST_RESULT_CACHE(110)
———————-
16000

Elapsed: 00:00:00.01
SQL> update EMPLOYEES set salary = 20000 where employee_id = 110;

1 row updated.

Elapsed: 00:00:00.01
SQL> select test_result_cache(110) from dual;
TEST_RESULT_CACHE(110)
———————-
20000

Elapsed: 00:00:10.01

Innym ciekawym przykładem wykorzystania RESULT_CACHE jest funkcja wyznaczająca wartość n-tego wyrazu ciągu fibonnaciego:

CREATE OR REPLACE FUNCTION fibonacci (n NUMBER)
RETURN NUMBER RESULT_CACHE IS
BEGIN
IF (n =0) OR (n =1) THEN
RETURN 1;
ELSE
RETURN fibonacci(n - 1) + fibonacci(n - 2);
END IF;
END;
/

Dla funkcji z klauzula ‘RESULT_CACHE’ istnieją pewne ograniczenia - przede wszystkim nie może ona mieć parametrów typu OUT i IN OUT. Dodatkowo zarówno parametry jak i wartość wynikowa nie mogą być typu BLOB, CLOB, NCLOB, REF CURSOR, Collection (tylko parametry), Object, Record(w tym przypadku Record jest dopuszczalny dla wartości wynikowej pod warunkiem, że nie zawiera w sobie innych zabronionych typów).

Administrator Oracle 11g w celu zarządzania ‘RESULT_CACHE’ może korzystać:

* parametry inicjalizacyjne (RESULT_CACHE_MAX_SIZE, RESULT_CACHE_MAX_RESULT)
* pakiet DBMS_RESULT_CACHE
* perspektywy: [G]V$RESULT_CACHE_STATISTICS, [G]V$RESULT_CACHE_MEMORY, [G]V$RESULT_CACHE_OBJECTS, [G]V$RESULT_CACHE_DEPENDENCY

Korzystając z klauzuli ‘RESULT_CACHE’ musimy pamiętać, aby nie modyfikowała ona stanu bazy danych (brak operacji DML i DDL) i nie modyfikowała sesji - od wykonania lub niewykonania (czyli pobrania wartości zwracanej z SGA) tej funkcji nie mogą więc zależeć żadne procesy biznesowe.

Linki zewnętrzne:

Using the Cross-Session PL/SQL Function Result Cache