Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / что лучше scalar subquery или PL/SQL с RESULT_CACHE / 18 сообщений из 18, страница 1 из 1
15.08.2017, 10:29
    #39505455
Андрей Панфилов
Андрей Панфилов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
что лучше scalar subquery или PL/SQL с RESULT_CACHE
11.2.0.4.0 EE

есть куча запросов подобного рода:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
SELECT fact_t.id,
       dict1.name,
       dict2.name,
       dict3.name
  FROM fact_t,
       dict dict1,
       dict dict2,
       dict dict3
 WHERE     fact_t.dict1_id = dict1.id(+)
       AND fact_t.dict2_id = dict2.id(+)
       AND fact_t.dict3_id = dict3.id(+);



в силу некоторых особенностей БД есть идея переписать их на scalar subquery, типа так:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
SELECT fact_t.id,
       (SELECT dict1.name
          FROM dict dict1
         WHERE fact_t.dict1_id = dict1.id),
       (SELECT dict2.name
          FROM dict dict1
         WHERE fact_t.dict2_id = dict2.id),
       (SELECT dict3.name
          FROM dict dict3
         WHERE fact_t.dict3_id = dict3.id)
  FROM fact_t



а можно на PL/SQL с RESULT_CACHE, т.е.:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
CREATE OR REPLACE FUNCTION get_dict_name (p_dict_id VARCHAR2)
   RETURN VARCHAR2
   RESULT_CACHE
AS
   result   VARCHAR2 (512);
BEGIN
   SELECT name
     INTO result
     FROM dict
    WHERE id = p_dict_id;

   RETURN result;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      RETURN result;
END;
/

SELECT fact_t.id,
       get_dict_name (fact_t.dict1_id),
       get_dict_name (fact_t.dict2_id),
       get_dict_name (fact_t.dict3_id)
  FROM fact_t



пользователи в результатах запросов обычно видят строк 200 от силы, в таблице dict примерно 2 млн. строк
какой вариант предпочтительнее (конечная цель на стороне приложения справочник кешировать, но это долгосрочная перспектива)?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.08.2017, 10:34
    #39505457
env
env
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
что лучше scalar subquery или PL/SQL с RESULT_CACHE
Андрей Панфилов,

dict - это универсальное измерение в архитектуре EAV ?

Изменения в dict случаются?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.08.2017, 10:39
    #39505459
Elic
Elic
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
что лучше scalar subquery или PL/SQL с RESULT_CACHE
Андрей Панфилов
Код: plsql
1.
2.
3.
4.
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      RETURN result;

Здесь это не обязательно.Андрей Панфиловв таблице dict примерно 2 млн. строкВообще-то, это плохой кандидат на кэширование, особенно, если аргументы равновероятны.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.08.2017, 10:39
    #39505460
Cheese)))
Cheese)))
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
что лучше scalar subquery или PL/SQL с RESULT_CACHE
В зависимости от данных 1 запрос может выиграть у второго.
scalar subquery - кэш на уровне запроса, и будет бесмысленным если у тебя fact_t.dict1_id, fact_t.dict2_id, fact_t.dict3_id разные.
RESULT_CACHE - кросс запросный кэш.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.08.2017, 10:57
    #39505465
Андрей Панфилов
Андрей Панфилов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
что лучше scalar subquery или PL/SQL с RESULT_CACHE
envdict - это универсальное измерение в архитектуре EAV ?Можно сказать и так, но в реальности все несколько сложнее :)
envИзменения в dict случаются?Довольно редко, но случаются
ElicВообще-то, это плохой кандидат на кэширование, особенно, если аргументы равновероятны.Из 2 млн. измерений "равновероятных" только тысяч 50-100, остальные хорошо коррелируют с данными из таблицы фактов, т.е. при определенных условиях на таблицу фактов (что всегда соблюдается) остальных измерений будет до 1000.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.08.2017, 14:21
    #39505626
Fogel
Fogel
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
что лучше scalar subquery или PL/SQL с RESULT_CACHE
Андрей Панфиловкакой вариант предпочтительнее
относительно выше перечисленного предпочтительней:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
SELECT f.id,
       decode(d.id, f.dict1_id, d.name,null) as dict1_name,
       decode(d.id, f.dict2_id, d.name,null) as dict2_name,
       decode(d.id, f.dict3_id, d.name,null) as dict3_name
  FROM fact_t f,
       dict d
 WHERE    coalesce(f.dict1_id,f.dict2_id,f.dict3_id) = d.id;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.08.2017, 14:29
    #39505641
env
env
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
что лучше scalar subquery или PL/SQL с RESULT_CACHE
Fogel,

Т.е. не заполнять все измерения, если есть идентификатор для одного?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.08.2017, 14:31
    #39505645
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
что лучше scalar subquery или PL/SQL с RESULT_CACHE
Fogel,

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
SQL> ed
Wrote file afiedt.buf

  1  with fact_t (id,dict1_id,dict2_id,dict3_id) as
  2  (select 'a',1,2,3 from dual)
  3  ,dict (id,name) as(
  4  select 1,'one' from dual union all
  5  select 2,'two' from dual union all
  6  select 3,'три' from dual )
  7  SELECT fact_t.id,
  8         (SELECT dict1.name
  9            FROM dict dict1
 10           WHERE fact_t.dict1_id = dict1.id),
 11         (SELECT dict2.name
 12            FROM dict dict2
 13           WHERE fact_t.dict2_id = dict2.id),
 14         (SELECT dict3.name
 15            FROM dict dict3
 16           WHERE fact_t.dict3_id = dict3.id)
 17*   FROM fact_t
SQL> /

I (SE (SE (SE
- --- --- ---
a one two три


SQL> ed
Wrote file afiedt.buf

  1  with fact_t (id,dict1_id,dict2_id,dict3_id) as
  2  (select 'a',1,2,3 from dual)
  3  ,dict (id,name) as(
  4  select 1,'one' from dual union all
  5  select 2,'two' from dual union all
  6  select 3,'три' from dual )
  7  SELECT f.id,
  8         decode(d.id, f.dict1_id, d.name,null) as dict1_name,
  9         decode(d.id, f.dict2_id, d.name,null) as dict2_name,
 10         decode(d.id, f.dict3_id, d.name,null) as dict3_name
 11    FROM fact_t f,
 12         dict d
 13*  WHERE    coalesce(f.dict1_id,f.dict2_id,f.dict3_id) = d.id
SQL> /

I DIC DIC DIC
- --- --- ---
a one

SQL>



.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.08.2017, 15:11
    #39505694
Андрей Панфилов
Андрей Панфилов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
что лучше scalar subquery или PL/SQL с RESULT_CACHE
ElicАндрей Панфилов
Код: plsql
1.
2.
3.
4.
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      RETURN result;

Здесь это не обязательно.

С точки зрения возвращаемых данных может и не обязательно, однако с точки зрения RESULT_CACHE разница-таки есть
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
SQL> CREATE OR REPLACE FUNCTION get_dict_name (p_dict_id VARCHAR2)
   RETURN VARCHAR2
   RESULT_CACHE
AS
   result   VARCHAR2 (512);
BEGIN
   SELECT name
     INTO result
     FROM (SELECT 1 AS id, 'name' AS name FROM DUAL)
    WHERE id = p_dict_id;

   RETURN result;
END;
/

Function created.

SQL> set autot traceonly
SQL> select get_dict_name(1) from dual;


Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------


Statistics
----------------------------------------------------------
         35  recursive calls
          0  db block gets
         28  consistent gets
          0  physical reads
          0  redo size
        356  bytes sent via SQL*Net to client
        364  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> /


Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------


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

SQL> select get_dict_name(2) from dual;


Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------


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

SQL> /


Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------


Statistics
----------------------------------------------------------
          2  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        352  bytes sent via SQL*Net to client
        364  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.08.2017, 17:14
    #39505849
Fogel
Fogel
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
что лучше scalar subquery или PL/SQL с RESULT_CACHE
Stax, env
я поторопился, не coalesce - (вернулся, чтобы поправиться, а вы уже тут, как тут), а вот так
Код: plsql
1.
2.
3.
4.
5.
6.
7.
SELECT f.id,
       decode(d.id, f.dict1_id, d.name,null) as dict1_name,
       decode(d.id, f.dict2_id, d.name,null) as dict2_name,
       decode(d.id, f.dict3_id, d.name,null) as dict3_name
  FROM fact_t f,
       dict d
 WHERE d.id in (f.dict1_id,f.dict2_id,f.dict3_id);
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.08.2017, 19:06
    #39505917
Озо
Озо
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
что лучше scalar subquery или PL/SQL с RESULT_CACHE
Андрей Панфилов,

А почему нельзя сделать одно обращение к dict через OR? ))
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.08.2017, 20:58
    #39505949
Андрей Панфилов
Андрей Панфилов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
что лучше scalar subquery или PL/SQL с RESULT_CACHE
ОзоА почему нельзя сделать одно обращение к dict через OR? ))
OR будет множить строки как в 20725999 , а значит нужно еще group by прилепить, а учитывая что в реальности запросы несколько сложнее, да еще и в представления спрятаны с активным использованием join elimination ... решил что так делать не нужно
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.08.2017, 08:43
    #39506044
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
что лучше scalar subquery или PL/SQL с RESULT_CACHE
ОзоАндрей Панфилов,

А почему нельзя сделать одно обращение к dict через OR? ))

можете на тестовом примере показать что Вы имеете в виду

.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.08.2017, 09:18
    #39506063
env
env
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
что лучше scalar subquery или PL/SQL с RESULT_CACHE
ОзоАндрей Панфилов,
А почему нельзя сделать одно обращение к dict через OR? ))
И получить, что-то в таком вот духе?
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
with fact_t (id,dict1_id,dict2_id,dict3_id) as
(select 'a',1,2,3 from dual)
,dict (id,name) as(
select 1,'one' from dual union all
select 2,'two' from dual union all
select 3,'три' from dual )
SELECT f.id,
       listagg(decode(d.id, f.dict1_id, d.name,null),',') within group (order by null desc) as dict1_name,
       min(decode(d.id, f.dict2_id, d.name,null)) as dict2_name,
       max(decode(d.id, f.dict3_id, d.name,null)) as dict3_name
  FROM fact_t f,
       dict d
 WHERE    f.dict1_id = d.id or f.dict2_id = d.id or f.dict3_id = d.id
 group by f.id
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.08.2017, 09:25
    #39506070
Elic
Elic
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
что лучше scalar subquery или PL/SQL с RESULT_CACHE
Андрей Панфиловоднако с точки зрения RESULT_CACHE разница-таки естьСогласен.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.08.2017, 09:58
    #39506095
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
что лучше scalar subquery или PL/SQL с RESULT_CACHE
env,

а если ид совпадают?

.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.08.2017, 10:00
    #39506096
env
env
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
что лучше scalar subquery или PL/SQL с RESULT_CACHE
Stax,

В таблице фактов? Исхожу из того, что fact и dimension являются указанием на схему звезда или снежинка. И идентификатор у строки факта уникален.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.08.2017, 10:03
    #39506099
env
env
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
что лучше scalar subquery или PL/SQL с RESULT_CACHE
Stax,

И такой "один" проход по dict размножит строки перед агрегацией пропорционально связям с этим справочником. И можно влететь на нехватку памяти/темпа на получение всех агрегатов, до получения ожидаемых первых строк фетча.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / что лучше scalar subquery или PL/SQL с RESULT_CACHE / 18 сообщений из 18, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 529ms
Закрыть
start [/forum/topic.php?fid=52&tablet=1&tid=1885422]:
 0ms
get settings:
 8ms
get forum list:
 17ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 194ms
get topic data:
 12ms
get forum data:
 3ms
get page messages:
 72ms
get tp. blocked users:
 2ms
others: 215ms
total:  529ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]