Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 39: оптимизация производительности с помощью хранимых процедур
Концепция представленного решения основана на этой рекомендации26. Итак, мы должны будем получить список всех таблиц, а затем для каждой из них сначала разрешить перемещение рядов, потом выполнить компактификацию и, наконец, снова запретить перемещение рядов. В отличие от MS SQL Server все запросы здесь совершенно тривиальны.
Запустить полученную хранимую процедуру можно следующим образом.
Oracle |
Решение 5.2.2.b (запуск и проверка работоспособности) |
1SET SERVEROUTPUT ON;
EXECUTE OPTIMIZE ALL TABLES;
Теперь остаётся создать событие, запускаемое планировщиком задач Oracle по определённому расписанию. Это реализуется следующим кодом.
Oracle |
Решение 5.2.2.b (установка запуска по расписанию) |
|
1 |
BEGIN |
|
2 |
DBMS SCHEDULER.CREATE JOB ( |
|
3 |
j ob_name |
=> ' daily_optimize_all_tables' , |
4 |
j ob type |
=> 'STORED PROCEDURE', |
5 |
job action |
=> 'OPTIMIZE ALL TABLES', |
6 |
start date |
=> '25-APR-16 4.00.00 PM', |
7 |
repeat_interval |
=> 'FREQ=DAILY;INTERVAL=1', |
8 |
auto_drop |
=> FALSE |
9 |
enabled |
=> TRUE); |
10 |
END; |
|
|
|
|
Убедиться, что событие добавлено, можно следующим запросом.
Oracle Решение 5.2.2.b (просмотр расписания)
1 SELECT * FROM ALL_SCHEDULER_JOBS WHERE OWNER=USER
Если рассмотреть избранные поля результата такого запроса, получается следующая картина. Здесь также представлено событие, активирующее раз в час хранимую процедуру, созданную в процессе решения{388} задачи 5.2.2.a{388}.
JOB_NAME |
JOB_STY |
JOB_TYPE |
JOB_ACTION |
START_DATE |
REPEAT_INTER- |
ENA |
STATE |
|
LE |
|
|
|
VAL |
BLED |
|
|
|
|
|
|
|
|
|
DAILY_OPTI- |
REGU |
STORED_PR |
OPTI- |
25-APR-27 |
FREQ=DAILY;IN |
TRU |
SCHE |
MIZE_ALL_TA- |
LAR |
OCEDURE |
MIZE_ALL_TA- |
04.00.00.000000 |
TERVAL=1 |
E |
DULE |
BLES |
|
|
BLES |
000 PM -03:00 |
|
|
D |
|
|
|
|
|
|
|
|
HOURLY_UP- |
REGU |
STORED_PR |
UP- |
22-APR-27 |
FREQ=HOURLY |
TRU |
SCHE |
DATE_BOOKS_S |
LAR |
OCEDURE |
DATE_BOOKS_S |
04.00.00.000000 |
;INTERVAL=1 |
E |
DULE |
TATISTICS |
|
|
TATISTICS |
000 PM -03:00 |
|
|
D |
|
|
|
|
|
|
|
|
На этом решение данной задачи завершено.
& Задание 5.2.2.TSK.A: создать хранимую процедуру, запускаемую по расписанию каждые 12 часов и обновляющую данные в агрегирующей таблице subscriptions_ready (см. задачу 3.1.2.b{215}).
Задание 5.2.2.TSK.B: создать хранимую процедуру, запускаемую по расписанию раз в неделю и оптимизирующую (дефрагментирующую, компактифицирующую) все таблицы базы данных, в которых находится не менее одного миллиона записей.
26 https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:17312316112393#1765387500346472492
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 425/545
Пример 40: управление структурами базы данных с помощью хранимых процедур
5.2.3.Пример 40: управление структурами базы данных с помощью хранимых процедур
Задача 5.2.3.a{400}: создать хранимую процедуру, автоматически создающую и наполняющую данными агрегирующую таблицу books_statistics (см. задачу 3.1.2.a{215}).
Задача 5.2.3.b{404}: создать хранимую процедуру, автоматически создающую и наполняющую данными агрегирующую таблицу tables_rc, содержащую информацию о количестве записей во всех таблицах базы данных в формате (имя_таблицы, количество_записей).
Ожидаемый результат 5.2.3.a.
При вызове хранимой процедуры таблица books_statistics создаётся и наполняется данными. Если таблица уже существует на момент вызова хранимой процедуры, данные в ней обновляются (приводятся в актуальное состояние).
Пример содержимого таблицы books_statistics см. в задаче 3.1.2.a{215}.
Ожидаемый результат 5.2.3.b.
При вызове хранимой процедуры таблица tables_rc создаётся и наполняется данными. Если таблица уже существует на момент вызова хранимой процедуры, данные в ней обновляются (приводятся в актуальное состояние).
Пример содержимого таблицы tables_rc:
table_name |
rows_count |
authors |
7 |
books |
7 |
books statistics |
1 |
genres |
6 |
m2m_books_authors |
9 |
m2m books genres |
11 |
subscribers |
4 |
subscriptions |
11 |
tables_rc |
8 |
чг Решение 5.2.3.a{400}.
Врешении{367} задачи 5.1.1.c{352} уже содержатся готовые запросы для создания таблицы books_statistics, наполнения её данными и обновления её дан-
ных. Сейчас нам остаётся только разместить эти запросы внутри хранимой процедуры и добавить проверку существования самой таблицы books_statistics.
Решения для MySQL и MS SQL Server будут полностью идентичными, а в Oracle нам придётся все запросы выполнять через EXECUTE IMMEDIATE, т.к. в случае отсутствия таблицы books_statistics эта СУБД автоматически считает хранимую процедуру некорректной, если в ней напрямую прописаны запросы, обращающиеся к этой таблице.
Теперь остаётся только привести код.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 426/545
Пример 40: управление структурами базы данных с помощью хранимых процедур
Решение для MySQL выглядит следующим образом.
MySQL I |
Решение 5.2.3.a (код процедуры) | |
1DELIMITER $$
2CREATE PROCEDURE CREATE BOOKS STATISTICS() BEGIN
34
5IF NOT EXISTS
6(SELECT 'table name'
7 |
FROM |
'information schema' 'tables' |
|||
8 |
WHERE |
'table schema' |
= DATABASE() |
||
9 |
AND |
'table |
type' |
= |
'BASE TABLE' |
10 |
AND |
'table |
name' |
= |
'books statistics') |
11THEN
12CREATE TABLE 'books statistics'
13(
14'total' INTEGER UNSIGNED NOT NULL,
15'given' INTEGER UNSIGNED NOT NULL,
16'rest' INTEGER UNSIGNED NOT NULL
17);
18INSERT INTO 'books statistics'
19 |
('total', |
20 |
'given', |
21 |
'rest') |
22SELECT IFNULL('total', 0 ,
23IFNULL('given', 0 ,
24IFNULL('total' - 'given', 0) AS 'rest'
25 |
FROM |
(SELECT (SELECT SUM('b quantity') |
|
|
|
26 |
|
FROM |
'books') |
AS |
'total' |
27 |
|
(SELECT COUNT('sb book') |
|
|
|
28 |
|
FROM |
'subscriptions' |
|
|
29 |
|
WHERE |
'sb is active' = 'Y') AS |
'given') |
|
30AS 'prepared data';
31ELSE
32UPDATE 'books statistics'
33JOIN
34(SELECT IFNULL('total', 0) AS 'total',
35 |
|
IFNULL('given', 0) AS 'given', |
|
|
36 |
|
IFNULL('total' - 'given', 0 AS 'rest' |
|
|
37 |
FROM |
(SELECT (SELECT SUM('b quantity') |
|
|
38 |
|
FROM |
'books') |
AS 'total' |
39 |
|
(SELECT COUNT('sb book') |
|
|
40 |
|
FROM |
'subscriptions' |
|
41 |
|
WHERE |
'sb is active' = 'Y') |
'given') |
42 |
|
AS 'prepared data') AS 'src' |
|
|
43SET 'books statistics' 'total' = 'src' 'total',
44'books statistics' 'given' = 'src' 'given',
45'books statistics' 'rest' = 'src' 'rest';
46END IF;
47END;
48$$
49DELIMITER ;
Для проверки корректности полученного решения можно выполнить следующие запросы.
MySQL |
Решение 5.2.3.a (проверка работоспособности) |
DROP TABLE 'books_statistics';
2 CALL CREATE_BOOKS_STATISTICS; SELECT * FROM 'books statistics';
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 427/545
Пример 40: управление структурами базы данных с помощью хранимых процедур
Решение для MS SQL Server выглядит следующим образом.
MS SQL Решение 5.2.3.a (код процедуры)
1CREATE PROCEDURE CREATE BOOKS STATISTICS
2AS
3BEGIN
4IF NOT EXISTS
5(SELECT [name]
6 |
FROM |
sys.tables |
7 |
WHERE |
[name] = 'books statistics') |
8BEGIN
9CREATE TABLE [books statistics]
10(
11[total] INTEGER NOT NULL,
12[given] INTEGER NOT NULL,
13[rest] INTEGER NOT NULL
14);
15INSERT INTO [books statistics]
16 |
([total] |
17 |
[given] |
18 |
[rest]) |
19SELECT ISNULL([total], 0 AS [total]
20ISNULL([given], 0 AS [given]
21ISNULL([total] - [given], 0) AS [rest]
22 |
FROM |
(SELECT (SELECT SUM [b quantity] |
|
|
23 |
|
FROM |
[books]) |
AS [total] |
24 |
|
(SELECT COUNT([sb book]) |
|
|
25 |
|
FROM |
[subscriptions] |
|
26 |
|
WHERE |
[sb is active] = 'Y') AS [given] |
|
27 |
|
AS [prepared_data]; |
|
|
28 |
|
|
|
|
29END
30ELSE
31BEGIN
32UPDATE [books statistics]
33SET
34[books statistics] [total] = [src] [total],
35[books statistics] [given] = [src] [given],
36[books statistics] [rest] = [src] [rest]
37FROM [books statistics]
38JOIN
39(SELECT ISNULL [total], 0) AS [total],
40 |
|
ISNULL [given], 0) AS [given], |
||
41 |
|
ISNULL [total] - [given], 0 |
AS [rest] |
|
42 |
FROM |
(SELECT (SELECT SUM([b quantity]) |
||
43 |
|
FROM |
[books] |
AS [total], |
44 |
|
(SELECT COUNT [sb book]) |
||
45 |
|
FROM |
[subscriptions] |
|
46 |
|
WHERE |
[sb is active] = 'Y') AS [given]) |
|
47 |
|
AS [prepared data] |
|
|
48) AS [src]
49ON 1 1;
50END;
51END;
52GO
Для проверки корректности полученного решения можно выполнить следующие запросы.
MS SQL |
Решение 5.2.3.a (проверка работоспособности) |
DROP TABLE [books_statistics];
EXECUTE CREATE_BOOKS_STATISTICS;
SELECT * FROM [books statistics];
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 428/545
Пример 40: управление структурами базы данных с помощью хранимых процедур
Решение для Oracle выглядит следующим образом. Обратите внимание на то, как реализована проверка существования таблицы books_statistics: т.к. Oracle не поддерживает вариант с IF NOT EXISTS, мы вынуждены поместить в переменную количество найденных рядов и затем проверить его значение в блоке IF.
Orac |
?le 1 Решение 5.2.3.a (код процедуры) |
І |
|
1 |
|
|
|
2 |
|
|
|
3 |
|
|
|
4 |
CREATE OR REPLACE PROCEDURE CREATE_BOOKS_STATISTICS |
||
5 |
AS table_found NUMBER(1) : |
0 |
|
6 |
BEGIN |
|
|
7 |
|
|
|
8 |
SELECT COUNT 1 INTO table_found |
||
9 |
FROM |
ALL_TABLES |
|
10 |
WHERE |
OWNER=USER |
|
11 |
AND |
TABLE_NAME = 'books_statistics'; |
|
12 |
|
|
|
13IF table_found = 0)
14THEN
15EXECUTE IMMEDIATE 'CREATE TABLE "books_statistics
16(
17"total" NUMBER(10),
18"given" NUMBER(10),
19"rest" NUMBER(10)
20)' ;
45 |
END IF; |
|
|
46 |
|
|
|
END; |
|
|
|
47 |
|
|
|
/ |
|
|
|
48 |
|
|
|
21 |
EXECUTE IMMEDIATE 'INSERT INTO |
||
22 |
|
|
"books_statistics |
23 |
" |
|
|
24 |
("total", "given", "rest") |
||
25 |
SELECT "total", |
|
|
26 |
|
"given", |
|
27 |
|
("total" - "given") AS "rest" |
|
28 |
FROM |
(SELECT SUM("b_quantity") AS "total" |
|
29 |
|
FROM |
"books") |
30 |
JOIN (SELECT COUNT("sb_book") AS"given" |
||
31 |
|
FROM |
"subscriptions" |
32 |
|
WHERE |
"sb_is_active" =''Y'') |
33 |
ON 1 = 1'; |
|
|
34 |
|
|
|
35 |
ELSE |
|
|
36 |
EXECUTE IMMEDIATE 'UPDATE "books_statistics" |
||
37 |
SET ("total", "given", "rest") = (SELECT |
||
38 |
"total", "given", ("total" - "given") AS "rest" |
||
39 |
FROM |
(SELECT SUM("b_quantity") AS "total" |
|
40 |
|
FROM |
"books") |
41 |
JOIN (SELECT COUNT("sb_book") AS "given" |
||
42 |
|
FROM |
"subscriptions" |
43 |
|
WHERE |
"sb_is_active" = ''Y'') |
44 |
ON 1 = 1)'; |
|
|
|
|
||
Для проверки корректности полученного решения можно выполнить следующие запросы.
Oracle |
Решение 5.2.3.а (проверка работоспособности) |
DROP TABLE "books_statistics"
2EXECUTE CREATE_BOOKS_STATISTICS;
3SELECT * FROM "books statistics"1
На этом решение данной задачи завершено.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 429/545