Кое-что из аналитики

Оператор для иерархических конструкций CONNECT BY; Аналитическая функция PARTITION OVER () с агрегированием; Генерация псевдослучайных символов/чисел.

Оператор для иерархических конструкций CONNECT BY;

 create table CONNECT_BYTEST
(
ID       NUMBER(3),
EMP_NAME VARCHAR2(50),
UPPER_ID NUMBER(3),
LEV      NUMBER(1)
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);

А теперь выводим иерархию (upper_id - начальник id)

SQL> select rpad('*', 4 * level, '*') || emp_name name
2  from CONNECT_BYTEST
3  start with upper_id is null
4  connect by prior id = upper_id
5  /

NAME
--------------------------------------------------------------------------------
****John
********Smith
********Scott
************Steve
****Thomas

level - системная переменная, содержащая уровень иерархии (1,2,3..);
start with <if> - условие, задающее верхний уровень иерархии;
connect by <prior> - условие соединения. PRIOR означает, что при очередные значения Oracle будет искать среди потомков найденной записи поля id. (prior id = upper_id) - не то же самое, что (prior upper_id = id).

Аналитическая функция PARTITION OVER

Исходные данные:

create table PN_VISITORS_LT
(
ID         NUMBER(9) not null,
NAME       NVARCHAR2(255),
SALE       NUMBER not null,
GR_ID      NUMBER not null,
GR1_ID     NUMBER,
VERSION    INTEGER,
CREATETIME TIMESTAMP(6) WITH TIME ZONE,
RETIRETIME TIMESTAMP(6) WITH TIME ZONE,
NEXTVER    VARCHAR2(500),
DELSTATUS  INTEGER,
LTLOCK     VARCHAR2(100)
)

Получим-ка процент, который составляет оклад каждого из сотрудников от общей по отделам. Это - раз, с помощью аналитической функции PARTITION OVER

declare
t1 timestamp;
t2 timestamp;
p number;
begin
select systimestamp into t1 from dual;
select avg(r1) into p from (
select gr_id,
name,
sale / (sum(sale) over(partition by gr_id)) * 1000 r1,
to_char(sale / (sum(sale) over(partition by gr_id)), '0.99999') cum_sal,
to_char(sale / (sum(sale) over()), '0.99999') cum_sal1
from pn_visitors
order by name);
select systimestamp into t2 from dual;
dbms_output.put_line(t2 - t1);
end;

partition by - агрегирование идет внутри групп поля gr_id
Это - два, без использования аналитических функций:

declare
t1 timestamp;
t2 timestamp;
p number;
begin
select systimestamp into t1 from dual;
select avg(r1) from (
select v.gr_id,
v.name,
v.sale / s * 1000 r1,
to_char(v.sale / s, '0.99') cum_sal,
to_char(v.sale / s2, '0.99') cum_sal1
from pn_visitors v,
(select gr_id, sum(sale) s
from pn_visitors
group by gr_id) a1,
(select sum(sale) s2 from pn_visitors) a2
where a1.gr_id = v.gr_id
order by v.name);
select systimestamp into t2 from dual;
dbms_output.put_line(t2 - t1);
end;

Посмотрим-ка, что у нас с производительностью. В этом нам поможет штука RUNSTATS от о.Тома.

Run1 ran in 91 hsecs
Run2 ran in 75 hsecs
run 1 ran in 121,33% of the time
-----------
Name                                Run1      Run2      Diff
STAT...parse time cpu                  3         2        -1
STAT...parse time elapsed              3         2        -1
LATCH.KFK SGA context latch            2         3         1
LATCH.session timer                    1         0        -1
LATCH.library cache lock alloc         4         5         1
LATCH.redo writing                     1         2         1
LATCH.active checkpoint queue          0         1         1
STAT...session cursor cache co         1         0        -1
LATCH.KMG MMAN ready and start         1         0        -1
LATCH.OS process allocation            1         0        -1
STAT...cleanout - number of kt         1         0        -1
STAT...active txn count during         1         0        -1
LATCH.enqueues                        10         9        -1
LATCH.ksuosstats global area           1         0        -1
STAT...calls to kcmgcs                 4         5         1
STAT...cursor authentications          0         1         1
LATCH.ASM map operation hash t         4         6         2
LATCH.ASM map operation freeli         2         0        -2
LATCH.cache buffers lru chain          2         0        -2
LATCH.active service list              2         0        -2
LATCH.FOB s.o list latch               2         0        -2
STAT...sorts (memory)                  3         1        -2
STAT...rows fetched via callba         1         3         2
STAT...table fetch by rowid            9        11         2
STAT...table scans (short tabl         1         3         2
STAT...redo entries                    8        10         2
STAT...shared hash latch upgra         2         0        -2
STAT...free buffer requested           7         5        -2
STAT...db block changes               45        43        -2
STAT...physical read IO reques         2         0        -2
STAT...physical reads cache            2         0        -2
LATCH.ASM db client latch              0         2         2
STAT...physical read total IO          2         0        -2
STAT...physical reads                  2         0        -2
LATCH.KFMD SGA                         2         0        -2
LATCH.ASM network background l         0         2         2
LATCH.compile environment latc         0         3         3
STAT...consistent changes             36        32        -4
STAT...buffer is not pinned co        18        22         4
STAT...workarea executions - o        10         6        -4
LATCH.messages                        10         6        -4
LATCH.enqueue hash chains             10         6        -4
LATCH.object queue header heap         4         0        -4
STAT...enqueue releases                7         2        -5
LATCH.shared pool simulator           11        16         5
STAT...enqueue requests                8         2        -6
LATCH.session allocation              56        62         6
STAT...index fetch by key              3         9         6
STAT...consistent gets - exami         5        12         7
STAT...db block gets from cach        38        30        -8
STAT...db block gets                  38        30        -8
LATCH.object queue header oper        17         9        -8
LATCH.channel operations paren         1         9         8
LATCH.shared pool                     90       102        12
LATCH.session idle bit                 0        15        15
LATCH.global ctx hash table la         8        24        16
STAT...recursive cpu usage           100        84       -16
STAT...CPU used by this sessio       100        83       -17
STAT...calls to get snapshot s        16        33        17
LATCH.checkpoint queue latch           0        36        36
LATCH.library cache lock              66       128        62
LATCH.row cache objects              189       255        66
LATCH.SQL memory manager worka        10        77        67
LATCH.library cache pin               52       121        69
STAT...undo change vector size     2,032     2,120        88
LATCH.library cache                  185       330       145
LATCH.simulator hash latch            78       234       156
LATCH.simulator lru latch             78       234       156
STAT...redo size                   2,712     2,900       188
STAT...table scan blocks gotte     1,190     3,570     2,380
STAT...no work - consistent re     1,198     3,578     2,380
STAT...session logical reads       1,255     3,653     2,398
STAT...consistent gets             1,217     3,623     2,406
STAT...consistent gets from ca     1,217     3,623     2,406
LATCH.cache buffers chains         2,588     7,368     4,780
STAT...physical read bytes        16,384         0   -16,384
STAT...physical read total byt    16,384         0   -16,384
STAT...sorts (rows)               73,355     7,369   -65,986
STAT...session uga memory         65,560   138,512    72,952
STAT...buffer is pinned count     43,657   130,971    87,314
STAT...table scan rows gotten     62,703   188,109   125,406
STAT...session pga memory        458,752   131,072  -327,680
STAT...session uga memory max ##########         0##########
STAT...session pga memory max ##########         0##########
--------------
Run1 latches total versus runs -- difference and pct
Run1      Run2      Diff     Pct
3,503     9,080     5,577  38.58%

Нам интересно:

STAT...physical read IO reques         2         0        -2
STAT...physical read bytes        16,384         0   -16,384

видим, что все данные для запроса 2 берутся из кэша

STAT...session logical reads       1,255     3,653     2,398

3-х кратное увеличение количества операций I/O

STAT...session pga memory        458,752   131,072  -327,680

однако, запрос 1 требует в 3 раза больше места в PGA. Но главное:

Run1 latches total versus runs -- difference and pct
Run1      Run2      Diff     Pct
3,503     9,080     5,577  38.58%

почти в 3 раза больше блокировок!
Вывод: использование аналитической операции PARTITION OVER() создает меньше блокировок, а значит положительно влияет на масштабируемость системы.