ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / что лучше scalar subquery или PL/SQL с RESULT_CACHE
18 сообщений из 18, страница 1 из 1
что лучше scalar subquery или PL/SQL с RESULT_CACHE
    #39505455
Андрей Панфилов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Андрей Панфилов
Участник
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:29
| Ответить | Цитировать | Написать  
что лучше scalar subquery или PL/SQL с RESULT_CACHE
    #39505457
Фотография env
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
env
Участник
Андрей Панфилов,

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

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

Здесь это не обязательно.Андрей Панфиловв таблице dict примерно 2 млн. строкВообще-то, это плохой кандидат на кэширование, особенно, если аргументы равновероятны.
...
Рейтинг: 0 / 0
15.08.2017, 10:39
| Ответить | Цитировать | Написать  
что лучше scalar subquery или PL/SQL с RESULT_CACHE
    #39505460
Cheese)))
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Cheese)))
Участник
В зависимости от данных 1 запрос может выиграть у второго.
scalar subquery - кэш на уровне запроса, и будет бесмысленным если у тебя fact_t.dict1_id, fact_t.dict2_id, fact_t.dict3_id разные.
RESULT_CACHE - кросс запросный кэш.
...
Рейтинг: 0 / 0
15.08.2017, 10:39
| Ответить | Цитировать | Написать  
что лучше scalar subquery или PL/SQL с RESULT_CACHE
    #39505465
Андрей Панфилов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Андрей Панфилов
Участник
envdict - это универсальное измерение в архитектуре EAV ?Можно сказать и так, но в реальности все несколько сложнее :)
envИзменения в dict случаются?Довольно редко, но случаются
ElicВообще-то, это плохой кандидат на кэширование, особенно, если аргументы равновероятны.Из 2 млн. измерений "равновероятных" только тысяч 50-100, остальные хорошо коррелируют с данными из таблицы фактов, т.е. при определенных условиях на таблицу фактов (что всегда соблюдается) остальных измерений будет до 1000.
...
Рейтинг: 0 / 0
15.08.2017, 10:57
| Ответить | Цитировать | Написать  
что лучше scalar subquery или PL/SQL с RESULT_CACHE
    #39505626
Фотография Fogel
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Fogel
Участник
Андрей Панфиловкакой вариант предпочтительнее
относительно выше перечисленного предпочтительней:
Код: 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:21
| Ответить | Цитировать | Написать  
что лучше scalar subquery или PL/SQL с RESULT_CACHE
    #39505641
Фотография env
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
env
Участник
Fogel,

Т.е. не заполнять все измерения, если есть идентификатор для одного?
...
Рейтинг: 0 / 0
15.08.2017, 14:29
| Ответить | Цитировать | Написать  
что лучше scalar subquery или PL/SQL с RESULT_CACHE
    #39505645
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
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, 14:31
| Ответить | Цитировать | Написать  
что лучше scalar subquery или PL/SQL с RESULT_CACHE
    #39505694
Андрей Панфилов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Андрей Панфилов
Участник
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, 15:11
| Ответить | Цитировать | Написать  
что лучше scalar subquery или PL/SQL с RESULT_CACHE
    #39505849
Фотография Fogel
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Fogel
Участник
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, 17:14
| Ответить | Цитировать | Написать  
что лучше scalar subquery или PL/SQL с RESULT_CACHE
    #39505917
Озо
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Озо
Гость
Андрей Панфилов,

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

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

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

.....
stax
...
Рейтинг: 0 / 0
16.08.2017, 08:43
| Ответить | Цитировать | Написать  
что лучше scalar subquery или PL/SQL с RESULT_CACHE
    #39506063
Фотография env
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
env
Участник
ОзоАндрей Панфилов,
А почему нельзя сделать одно обращение к 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:18
| Ответить | Цитировать | Написать  
что лучше scalar subquery или PL/SQL с RESULT_CACHE
    #39506070
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
Андрей Панфиловоднако с точки зрения RESULT_CACHE разница-таки естьСогласен.
...
Рейтинг: 0 / 0
16.08.2017, 09:25
| Ответить | Цитировать | Написать  
что лучше scalar subquery или PL/SQL с RESULT_CACHE
    #39506095
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
env,

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

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

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

И такой "один" проход по dict размножит строки перед агрегацией пропорционально связям с этим справочником. И можно влететь на нехватку памяти/темпа на получение всех агрегатов, до получения ожидаемых первых строк фетча.
...
Рейтинг: 0 / 0
16.08.2017, 10:03
| Ответить | Цитировать | Написать  
18 сообщений из 18, страница 1 из 1
Форумы / Oracle [игнор отключен] [закрыт для гостей] / что лучше scalar subquery или PL/SQL с RESULT_CACHE
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&msg=39506099&tid=1885422]:
 0ms
get settings:
 7ms
get forum list:
 13ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 412ms
get topic data:
 10ms
get forum data:
 3ms
get page messages:
 50ms
get tp. blocked users:
 1ms
others: 242ms
total:  744ms
созд. / загр.: 744ms
x
x
Закрыть


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