#sql #performance #oracle #exception-handling #plsql
#sql #Производительность #Oracle #исключение #plsql
Вопрос:
у меня есть процедура, которая заполняет таблицу следующим sql
SELECT NVL(SUM(COL1), 0),
NVL(SUM(COL2), 0)
INTO v_mytable.COLUMN1,
v_mytable.COLUMN2
FROM t1, t2
WHERE t1.id = t2.id
AND t1.date = t2.date
кроме того, для 99% строк таблицы эти столбцы = 0, и выполнение этого запроса занимает много времени, хотя в большинстве случаев он возвращает 0 для обоих столбцов.
Лучше ли использовать обработку исключений следующим образом :
BEGIN
SELECT SUM(COL1),
SUM(COL2)
INTO v_mytable.COLUMN1,
v_mytable.COLUMN2
FROM t1, t2
WHERE t1.id = t2.id
AND t1.date = t2.date
EXCEPTION WHEN NO_DATA_FOUND THEN
v_mytable.COLUMN1 := 0 ;
v_mytable.COLUMN2 := 0 ;
END;
Спасибо.
Комментарии:
1. Теоретически COALESCE должен быть быстрее, чем NVL, потому что первый выполняется SQL engine, а второй — pl / sql. Но я сомневаюсь, что вы могли бы измерить разницу, тем более что она вызывается только в конце запроса, а не по одному разу для каждой строки. Кроме того, смотрите Ответ Джастина Кейва, потому что ваш код никогда не вызовет никаких найденных данных.
Ответ №1:
Эти два блока выполняют совершенно разные функции. Ваш оператор SELECT не выдал бы NO_DATA_FOUND ошибку, если бы COL1 и / или COL2 всегда были равны нулю. Это просто поместило бы НУЛЬ в v_mytable.COLUMN1 и v_mytable.COLUMN2 .
Вы могли бы сделать
SELECT SUM(COL1),
SUM(COL2)
INTO v_mytable.COLUMN1,
v_mytable.COLUMN2
FROM t1, t2
WHERE t1.id = t2.id
AND t1.date = t2.date
v_mytable.COLUMN1 := NVL( v_mytable.COLUMN1, 0 );
v_mytable.COLUMN2 := NVL( v_mytable.COLUMN2, 0 );
Однако я бы не ожидал, что это будет быстрее.
Ответ №2:
Учитывая выбор между этими двумя, я бы выбрал первое.
Я предпочитаю использовать обработчики исключений для подлинных исключений / ошибок, а не для потока управления.
YMMV.
Комментарии:
1. Согласен с вашей точкой зрения, но в этом случае использование обработчика исключений даже не приводит к тому же результату.
Ответ №3:
NO_DATA_FOUND было бы выдано, если бы не было возвращено ни одной строки, а НЕ если бы в фактических строках, которые возвращаются из запроса, были возвращены значения null. Это привело бы к возникновению NO_DATA_FOUND:
select sysdate
into myVariable
from dual
where 1=0;
Это НЕ привело бы к возникновению NO_DATA_FOUND:
select null
into myVariable
from dual;
Тем не менее, если вы просто хотите игнорировать строки, где col1 и col2 равны нулю, то вы можете рассмотреть возможность использования коллекций в pl / sql и использовать массовую сборку into, что-то вроде:
select sum(col1) as sum_col1, sum(col2) as sum_col2, col3
bulk collect into v_mytable
FROM t1, t2
WHERE t1.id = t2.id
AND t1.date = t2.date
AND col1 is not null
AND col2 is not null
GROUP by col3;
Никаких циклов, выполняйте одним махом. К вашему сведению, вы бы настроили v_mytable что-то вроде:
declare
type t_rec is record
(col1_sum number,
col2_sum number,
col3 number);
v_rec t_rec;
type t_tab is table of v_rec%type;
v_mytable t_tab;
begin
...
Позже вы можете выполнить цикл через v_mytable, который будет составлять всего 1% от исходного результата соединения t1, t2 (из-за дополнительных предложений not null в запросе).
Надеюсь, это поможет.
Ответ №4:
Ваш SQL будет выполняться намного быстрее, если вы прекратите объединять строки, для которых значения col равны 0. Ниже приведен небольшой тест, подтверждающий мою точку зрения.
Сначала создайте две таблицы по 100 000 строк, где 99% строк имеют значение col, равное 0:
SQL> create table t1 (id,date1,col1)
2 as
3 select level
4 , trunc(sysdate)
5 , case mod(level,100) when 42 then 42 else 0 end
6 from dual
7 connect by level <= 100000
8 /
Table created.
SQL> create table t2 (id,date2,col2)
2 as
3 select level
4 , trunc(sysdate)
5 , case mod(level,100) when 42 then 84 else 0 end
6 from dual
7 connect by level <= 100000
8 /
Table created.
Приведите статистику таблицы оптимизатора на основе затрат:
SQL> exec dbms_stats.gather_table_stats(user,'t1')
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats(user,'t2')
PL/SQL procedure successfully completed.
И собирать статистику при выполнении запросов:
SQL> set serveroutput off
SQL> alter session set statistics_level = all
2 /
Session altered.
Теперь ваш запрос выполняется следующим образом:
SQL> SELECT NVL(SUM(t1.COL1), 0)
2 , NVL(SUM(t2.COL2), 0)
3 FROM t1
4 , t2
5 WHERE t1.id = t2.id
6 AND t1.date1 = t2.date2
7 /
NVL(SUM(T1.COL1),0) NVL(SUM(T2.COL2),0)
------------------- -------------------
42000 84000
1 row selected.
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'))
2 /
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------
SQL_ID 6q5h7h8ht5232, child number 0
-------------------------------------
SELECT NVL(SUM(t1.COL1), 0) , NVL(SUM(t2.COL2), 0) FROM t1 , t2 WHERE t1.id = t2.id AND
t1.date1 = t2.date2
Plan hash value: 446739472
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.37 | 560 | | | |
|* 2 | HASH JOIN | | 1 | 100K| 100K|00:00:00.24 | 560 | 4669K| 1437K| 7612K (0)|
| 3 | TABLE ACCESS FULL| T1 | 1 | 100K| 100K|00:00:00.01 | 280 | | | |
| 4 | TABLE ACCESS FULL| T2 | 1 | 100K| 100K|00:00:00.01 | 280 | | | |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."ID"="T2"."ID" AND "T1"."DATE1"="T2"."DATE2")
21 rows selected.
Вы можете видеть, что ХЭШ-соединению необходимо объединить 100 тысяч строк, и именно на это тратится большая часть времени. Теперь исключите значения 0:
SQL> SELECT NVL(SUM(t1.COL1), 0)
2 , NVL(SUM(t2.COL2), 0)
3 FROM t1
4 , t2
5 WHERE t1.id = t2.id
6 AND t1.date1 = t2.date2
7 and t1.col1 != 0
8 and t2.col2 != 0
9 /
NVL(SUM(T1.COL1),0) NVL(SUM(T2.COL2),0)
------------------- -------------------
42000 84000
1 row selected.
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'))
2 /
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------
SQL_ID bjr7wrjx5tjvr, child number 0
-------------------------------------
SELECT NVL(SUM(t1.COL1), 0) , NVL(SUM(t2.COL2), 0) FROM t1 , t2 WHERE t1.id = t2.id AND
t1.date1 = t2.date2 and t1.col1 != 0 and t2.col2 != 0
Plan hash value: 446739472
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.02 | 560 | | | |
|* 2 | HASH JOIN | | 1 | 25000 | 1000 |00:00:00.02 | 560 | 1063K| 1063K| 1466K (0)|
|* 3 | TABLE ACCESS FULL| T1 | 1 | 50000 | 1000 |00:00:00.01 | 280 | | | |
|* 4 | TABLE ACCESS FULL| T2 | 1 | 50000 | 1000 |00:00:00.01 | 280 | | | |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."ID"="T2"."ID" AND "T1"."DATE1"="T2"."DATE2")
3 - filter("T1"."COL1"<>0)
4 - filter("T2"."COL2"<>0)
23 rows selected.
И вы можете видеть, что ХЭШ-СОЕДИНЕНИЮ теперь нужно объединить только 1000 строк, что приводит к гораздо более быстрому выводу.
Надеюсь, это поможет.
С уважением,
Роб.
Комментарии:
1. Кроме того, индекс в col1 / col2 может ускорить работу еще больше.
2. Нет, это было бы не так: download.oracle.com/docs/cd/E11882_01/server.112/e16638 /…
3. Только если вы знаете, что значения всегда будут неотрицательными, тогда вы можете переписать предикаты в col1 > 0 и col2 > 0, и в этом случае вы можете использовать индекс.
4. Хорошо, я не рассматривал отрицательные значения.