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:
