Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / connect by + distinct / 25 сообщений из 36, страница 1 из 2
  1  все
25.07.2017, 07:55
    #39494331
nxx
nxx
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
connect by + distinct
есть вьюха с distinct

как будет работать если к ней сделать запрос с connect by ?
я правильно понял, что делается свой distinct на каждой итерации ?

план выходит такой

Код: plaintext
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.
Plan hash value: 2304563227
 
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                         |    85 |  2210 |    73   (7)| 00:00:01 |
|*  1 |  CONNECT BY WITH FILTERING      |                         |       |       |            |          |
|   2 |   VIEW                          | TEST_V                  |     8 |   208 |    11  (10)| 00:00:01 |
|   3 |    HASH UNIQUE                  |                         |     8 |    96 |    11  (10)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID | MTL_OBJECT_GENEALOGY    |    11 |   132 |    10   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN           | MTL_OBJECT_GENEALOGY_N1 |    11 |       |     3   (0)| 00:00:01 |
|   6 |   VIEW                          | TEST_V                  |    77 |  2002 |    60   (4)| 00:00:01 |
|   7 |    SORT UNIQUE                  |                         |    77 |  1925 |    60   (4)| 00:00:01 |
|   8 |     NESTED LOOPS                |                         |       |       |            |          |
|   9 |      NESTED LOOPS               |                         |    77 |  1925 |    59   (2)| 00:00:01 |
|  10 |       CONNECT BY PUMP           |                         |       |       |            |          |
|* 11 |       INDEX RANGE SCAN          | MTL_OBJECT_GENEALOGY_N2 |    10 |       |     2   (0)| 00:00:01 |
|  12 |      TABLE ACCESS BY INDEX ROWID| MTL_OBJECT_GENEALOGY    |    10 |   120 |     6   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("G"."PARENT_OBJECT_ID"=PRIOR "G"."OBJECT_ID")
   5 - access("G"."OBJECT_ID"=12036688)
  11 - access("connect$_by$_pump$_002"."prior g.object_id "="G"."PARENT_OBJECT_ID")
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.07.2017, 09:23
    #39494371
stax..
stax..
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
connect by + distinct
nxx,


выполняется distinct, потом строится дерево

а вот скоко раз distinct, вопрос
імхо, один раз
.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.07.2017, 09:29
    #39494374
Sayan Malakshinov
Sayan Malakshinov
Модератор форума
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
connect by + distinct
nxxя правильно понял, что делается свой distinct на каждой итерации ?

план выходит такой
да
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.07.2017, 11:02
    #39494434
stax..
stax..
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
connect by + distinct
xtendernxxя правильно понял, что делается свой distinct на каждой итерации ?

план выходит такой
да
не вижу смысла каждый раз сортировать

имхо distinct делает раз
Код: 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.
SQL> select * from t;

    ID_REF         CH N
---------- ---------- --
         1            r
         2          1 d1
         2          1 d1
         2          1 d2
         3          2 v1
         3          2 v1
         3          2 v1

7 rows selected.

SQL> create or replace function stax_log (p_id in number)
  2  return varchar2
  3  as
  4  begin
  5    DBMS_APPLICATION_INFO.SET_CLIENT_INFO (userenv('client_info')||p_id||'#');
  6   return p_id;
  7  end;
  8  /

Function created.
SQL> create or replace view tv as select distinct id_ref,ch,n,stax_log(id_ref) sss
  2  from t
  3  /

View created.
SQL> select * from tv;

    ID_REF         CH N  SSS
---------- ---------- -- ------------------------------
         1            r  1
         2          1 d1 2
         2          1 d2 2
         3          2 v1 3

SQL> select
  2   id_ref, level l
  3  ,stax_log(id_ref) sss
  4  from tv
  5  start with id_ref=3
  6  connect by prior ch=id_ref
  7  /

    ID_REF          L SSS
---------- ---------- ------------------------------
         3          1 3
         2          2 2
         1          3 1
         2          2 2
         1          3 1

SQL> select userenv('client_info') from dual;

USERENV('CLIENT_INFO')
----------------------------------------------------------------
3#3#3#2#2#2#1#3#2#1#2#1#

SQL>



вызовы для построения вью 3#3#3#2#2#2#1#
#3#2#1#2#1# -строим дерево

.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.07.2017, 23:49
    #39495635
Sayan Malakshinov
Sayan Malakshinov
Модератор форума
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
connect by + distinct
stax..,

Посмотри внимательно на план у тс.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.07.2017, 09:11
    #39495732
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
connect by + distinct
xtenderstax..,

Посмотри внимательно на план у тс.

судя по плану да, но план ето одно, а как на самом деле хз

мож есть какой-то евент которий покажет разницу на больших обьемах, не знаю

не вижу смысла на каждом уровне делать distinct
построили "in line" вю и на ее основе строим дерево

но ораклятам виднее

.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.07.2017, 09:23
    #39495741
Добрый Э - Эх
Добрый Э - Эх
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
connect by + distinct
[offtop]Stax,

чёй-то под синей учеткой? Даже непривычно как-то...
[/offtop]
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.07.2017, 09:34
    #39495746
Добрый Э - Эх
Добрый Э - Эх
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
connect by + distinct
Добрый Э - Эх[offtop]Stax,

чёй-то под синей учеткой? Даже непривычно как-то...
[/offtop]

не знаю как с дублями бороться


ps
на счет connect by + distinct план (и xtender) наверное правильно показывает

не могу придумать пример когда сначала distinct, а потом дерево

.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.07.2017, 15:42
    #39496133
SY
SY
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
connect by + distinct
Добрый Э - Эхне могу придумать пример когда сначала distinct, а потом дерево


Аналитику дoбавь во view:

Код: 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.
SQL> CREATE OR REPLACE
  2    VIEW V1
  3      AS
  4        SELECT  DISTINCT DENSE_RANK() OVER(PARTITION BY JOB ORDER BY DEPTNO) RNK,
  5                         DEPTNO,
  6                         JOB
  7          FROM  EMP
  8  /

View created.

SQL> SELECT  V1.*,
  2          LEVEL
  3    FROM  V1
  4    START WITH RNK = 1
  5    CONNECT BY JOB = PRIOR JOB
  6           AND RNK = PRIOR RNK + 1
  7  /

       RNK     DEPTNO JOB            LEVEL
---------- ---------- --------- ----------
         1         20 ANALYST            1
         1         10 CLERK              1
         2         20 CLERK              2
         3         30 CLERK              3
         1         10 MANAGER            1
         2         20 MANAGER            2
         3         30 MANAGER            3
         1         10 PRESIDENT          1
         1         30 SALESMAN           1

9 rows selected.

SQL> SELECT  DISTINCT DENSE_RANK() OVER(PARTITION BY JOB ORDER BY DEPTNO) RNK,
  2                   DEPTNO,
  3                   JOB
  4    FROM  EMP
  5    CONNECT BY JOB = PRIOR JOB
  6           AND RNK = PRIOR RNK + 1
  7  /
         AND RNK = PRIOR RNK + 1
                         *
ERROR at line 6:
ORA-00904: "RNK": invalid identifier


SQL>



SY.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.07.2017, 15:49
    #39496141
Sayan Malakshinov
Sayan Malakshinov
Модератор форума
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
connect by + distinct
Staxне вижу смысла на каждом уровне делать distinctответ-подсказка в самом плане ТС - зачем делать дистинкт на миллионах записей, если тебе нужно только 85?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.07.2017, 16:03
    #39496158
Sayan Malakshinov
Sayan Malakshinov
Модератор форума
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
connect by + distinct
Staxсудя по плану да, но план ето одно, а как на самом деле хзсобственно план - на то и план выполнения, чтобы знать что там "на самом деле"... В твоем же примере план скорее всего такой:
Код: 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.
SQL> explain plan for
  2  select
  3   id_ref, level l
  4  ,stax_log(id_ref) sss
  5  from tv
  6  start with id_ref=3
  7  connect by prior ch=id_ref;

Explained.

SQL> @xplan typical

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------
Plan hash value: 2608622911

------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |      |     6 |   156 |     5  (40)| 00:00:01 |
|*  1 |  CONNECT BY NO FILTERING WITH START-WITH|      |       |       |            |          |
|   2 |   VIEW                                  | TV   |     7 |   182 |     4  (25)| 00:00:01 |
|   3 |    HASH UNIQUE                          |      |     7 |    63 |     4  (25)| 00:00:01 |
|   4 |     TABLE ACCESS FULL                   | T    |     7 |    63 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("ID_REF"=PRIOR "CH")
       filter("ID_REF"=3)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.07.2017, 16:15
    #39496163
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
connect by + distinct
SYДобрый Э - Эхне могу придумать пример когда сначала distinct, а потом дерево


Аналитику дoбавь во view:

SY.

я несколько о другом
я считал что distinct выполняется один раз (аля in-line вью если грубо то напр материализуется)
и на основе полученного набора строим дерево

план показывет другое, distinct будет выполняться много раз (на "каждом" уровне)

.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.07.2017, 16:34
    #39496184
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
connect by + distinct
xtenderStaxне вижу смысла на каждом уровне делать distinctответ-подсказка в самом плане ТС - зачем делать дистинкт на миллионах записей, если тебе нужно только 85?
допустим на первом уровне 85, на дальше уже Хмлн-85 и их надо distinct
не вижу выиграша

не могу понять
почему для
2 1 2 d1
2 1 2 d2

третий уровень стоится один раз
я ожидал что он два раза (для d1 и d2) быдет выбирать тройку


Код: 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.
SQL> select * from t;

    ID_REF         CH N
---------- ---------- --
         1            r
         2          1 d1
         2          1 d1
         2          1 d2
         0          1 0a
         4          0 4a
         3          2 v1
         3          2 v1
         3          2 v1

9 rows selected.

SQL> select * from tv;

    ID_REF         CH N  SSS
---------- ---------- -- -----
         0          1 0a 0
         4          0 4a 4
         1            r  1
         2          1 d1 2
         2          1 d2 2
         3          2 v1 3

6 rows selected.

SQL> exec DBMS_APPLICATION_INFO.SET_CLIENT_INFO ('');

PL/SQL procedure successfully completed.

SQL> select ID_REF,ch,level,n
  2  from tv
  3  start with ch is null
  4  connect by ch=prior id_ref
  5  /

    ID_REF         CH      LEVEL N
---------- ---------- ---------- --
         1                     1 r
         0          1          2 0a
         4          0          3 4a
         2          1          2 d1
         3          2          3 v1
         2          1          2 d2
         3          2          3 v1

7 rows selected.

SQL> select userenv('client_info') from dual;

USERENV('CLIENT_INFO')
----------------------------------------------------------------
1#2#2#2#0#4#3#3#3#

SQL>




.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.07.2017, 16:45
    #39496201
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
connect by + distinct
xtenderStaxсудя по плану да, но план ето одно, а как на самом деле хзсобственно план - на то и план выполнения, чтобы знать что там "на самом деле"... В твоем же примере план скорее всего такой:
Код: 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.
SQL> explain plan for
  2  select
  3   id_ref, level l
  4  ,stax_log(id_ref) sss
  5  from tv
  6  start with id_ref=3
  7  connect by prior ch=id_ref;

Explained.

SQL> @xplan typical

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------
Plan hash value: 2608622911

------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |      |     6 |   156 |     5  (40)| 00:00:01 |
|*  1 |  CONNECT BY NO FILTERING WITH START-WITH|      |       |       |            |          |
|   2 |   VIEW                                  | TV   |     7 |   182 |     4  (25)| 00:00:01 |
|   3 |    HASH UNIQUE                          |      |     7 |    63 |     4  (25)| 00:00:01 |
|   4 |     TABLE ACCESS FULL                   | T    |     7 |    63 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("ID_REF"=PRIOR "CH")
       filter("ID_REF"=3)



такой план меня влаштовует (одна сортировка --> вью--> дерево)
но план как у nxx
Код: 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.
Execution Plan
----------------------------------------------------------
Plan hash value: 2254541599

----------------------------------------------------------------------------------
| Id  | Operation                 | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |      |     4 |   116 |     5  (20)| 00:00:01 |
|*  1 |  CONNECT BY WITH FILTERING|      |       |       |            |          |
|   2 |   VIEW                    | TV   |     1 |    29 |     5  (20)| 00:00:01 |
|   3 |    HASH UNIQUE            |      |     1 |     9 |     5  (20)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL     | T    |     1 |     9 |     4   (0)| 00:00:01 |
|   5 |   VIEW                    | TV   |     4 |   116 |     5  (20)| 00:00:01 |
|   6 |    HASH UNIQUE            |      |     4 |    36 |     5  (20)| 00:00:01 |
|*  7 |     HASH JOIN             |      |       |       |            |          |
|   8 |      CONNECT BY PUMP      |      |       |       |            |          |
|   9 |      TABLE ACCESS FULL    | T    |     4 |    36 |     4   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("CH"=PRIOR "ID_REF")
   4 - filter("CH" IS NULL)
   7 - access("CH"=PRIOR "ID_REF")

SQL>




.........
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.07.2017, 17:24
    #39496232
nxx
nxx
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
connect by + distinct
SY
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
SQL> SELECT  DISTINCT DENSE_RANK() OVER(PARTITION BY JOB ORDER BY DEPTNO) RNK,
  2                   DEPTNO,
  3                   JOB
  4    FROM  EMP
  5    CONNECT BY JOB = PRIOR JOB
  6           AND RNK = PRIOR RNK + 1
  7  /
         AND RNK = PRIOR RNK + 1
                         *
ERROR at line 6:
ORA-00904: "RNK": invalid identifier


SQL>



SY.

хм. а почему так ?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.07.2017, 17:25
    #39496235
nxx
nxx
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
connect by + distinct
nxxхм. а почему так ?

сорри, туплю. всё щас сам понял
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.07.2017, 17:35
    #39496246
Sayan Malakshinov
Sayan Malakshinov
Модератор форума
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
connect by + distinct
Staxдопустим на первом уровне 85, на дальше уже Хмлн-85 и их надо distinct
nxx
Код: 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.
Plan hash value: 2304563227
 
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                         |    85 |  2210 |    73   (7)| 00:00:01 |
|*  1 |  CONNECT BY WITH FILTERING      |                         |       |       |            |          |
|   2 |   VIEW                          | TEST_V                  |     8 |   208 |    11  (10)| 00:00:01 |
|   3 |    HASH UNIQUE                  |                         |     8 |    96 |    11  (10)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID | MTL_OBJECT_GENEALOGY    |    11 |   132 |    10   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN           | MTL_OBJECT_GENEALOGY_N1 |    11 |       |     3   (0)| 00:00:01 |
|   6 |   VIEW                          | TEST_V                  |    77 |  2002 |    60   (4)| 00:00:01 |
|   7 |    SORT UNIQUE                  |                         |    77 |  1925 |    60   (4)| 00:00:01 |
|   8 |     NESTED LOOPS                |                         |       |       |            |          |
|   9 |      NESTED LOOPS               |                         |    77 |  1925 |    59   (2)| 00:00:01 |
|  10 |       CONNECT BY PUMP           |                         |       |       |            |          |
|* 11 |       INDEX RANGE SCAN          | MTL_OBJECT_GENEALOGY_N2 |    10 |       |     2   (0)| 00:00:01 |
|  12 |      TABLE ACCESS BY INDEX ROWID| MTL_OBJECT_GENEALOGY    |    10 |   120 |     6   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("G"."PARENT_OBJECT_ID"=PRIOR "G"."OBJECT_ID")
   5 - access("G"."OBJECT_ID"=12036688)
  11 - access("connect$_by$_pump$_002"."prior g.object_id "="G"."PARENT_OBJECT_ID")
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.07.2017, 17:36
    #39496247
Sayan Malakshinov
Sayan Malakshinov
Модератор форума
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
connect by + distinct
Staxтакой план меня влаштовует (одна сортировка --> вью--> дерево)
но план как у nxx
Код: 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.
Execution Plan
----------------------------------------------------------
Plan hash value: 2254541599

----------------------------------------------------------------------------------
| Id  | Operation                 | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |      |     4 |   116 |     5  (20)| 00:00:01 |
|*  1 |  CONNECT BY WITH FILTERING|      |       |       |            |          |
|   2 |   VIEW                    | TV   |     1 |    29 |     5  (20)| 00:00:01 |
|   3 |    HASH UNIQUE            |      |     1 |     9 |     5  (20)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL     | T    |     1 |     9 |     4   (0)| 00:00:01 |
|   5 |   VIEW                    | TV   |     4 |   116 |     5  (20)| 00:00:01 |
|   6 |    HASH UNIQUE            |      |     4 |    36 |     5  (20)| 00:00:01 |
|*  7 |     HASH JOIN             |      |       |       |            |          |
|   8 |      CONNECT BY PUMP      |      |       |       |            |          |
|   9 |      TABLE ACCESS FULL    | T    |     4 |    36 |     4   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("CH"=PRIOR "ID_REF")
   4 - filter("CH" IS NULL)
   7 - access("CH"=PRIOR "ID_REF")

SQL>

разберись в своих тестах и планах. Приложенный план у тебя от другого запроса, нежели в показанном тесте.

зы. хз что такое "влаштовует"
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.07.2017, 17:39
    #39496250
Sayan Malakshinov
Sayan Malakshinov
Модератор форума
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
connect by + distinct
 
полуоффтопиквообще оракл плохо считает кардинальности для "connect by", т.к. ошибается в кол-ве итераций
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.07.2017, 17:45
    #39496251
env
env
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
connect by + distinct
xtenderхз что такое "влаштовует"
устраивает, судя по контексту, но могу ошибаться
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.07.2017, 18:10
    #39496263
SY
SY
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
connect by + distinct
Staxя несколько о другом
я считал что distinct выполняется один раз (аля in-line вью если грубо то напр материализуется)
и на основе полученного набора строим дерево


Это как оптимизтор решит. Если есть distinct без выражений вычисляемых над resultset (аналитика, model,...) то естественно гораздо оптимальней делать distinct на каждом уровне, но тогда мы увидим что-то типа CONNECT BY WITHOUT FILTERING (UNIQUE) . А например с аналитикой нет:

Код: 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.
SQL> EXPLAIN PLAN FOR
  2  SELECT  DISTINCT DEPTNO,
  3                   JOB
  4    FROM  EMP
  5    CONNECT BY JOB = PRIOR JOB
  6           AND DEPTNO > PRIOR DEPTNO
  7  /

Explained.

SQL> SELECT  *
  2    FROM  TABLE(DBMS_XPLAN.DISPLAY)
  3  /

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
Plan hash value: 730824693

-----------------------------------------------------------------------------------------------
| Id  | Operation                              | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |      |    11 |   121 |     4  (25)| 00:00:01 |
|   1 |  HASH UNIQUE                           |      |    11 |   121 |     4  (25)| 00:00:01 |
|*  2 |   CONNECT BY WITHOUT FILTERING (UNIQUE)|      |       |       |            |          |
|   3 |    TABLE ACCESS FULL                   | EMP  |    14 |   154 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------


PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("JOB"=PRIOR "JOB")
       filter("DEPTNO">PRIOR "DEPTNO")

16 rows selected.

SQL> EXPLAIN PLAN FOR
  2  SELECT  DISTINCT DENSE_RANK() OVER(PARTITION BY JOB ORDER BY DEPTNO) RNK,
  3                   DEPTNO,
  4                   JOB
  5    FROM  EMP
  6    CONNECT BY JOB = PRIOR JOB
  7           AND DEPTNO > PRIOR DEPTNO
  8  /

Explained.

SQL> SELECT  *
  2    FROM  TABLE(DBMS_XPLAN.DISPLAY)
  3  /

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
Plan hash value: 2864607417

---------------------------------------------------------------------------------------
| Id  | Operation                      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |      |    11 |   121 |     5  (40)| 00:00:01 |
|   1 |  HASH UNIQUE                   |      |    11 |   121 |     5  (40)| 00:00:01 |
|   2 |   WINDOW SORT                  |      |    11 |   121 |     5  (40)| 00:00:01 |
|*  3 |    CONNECT BY WITHOUT FILTERING|      |       |       |            |          |
|   4 |     TABLE ACCESS FULL          | EMP  |    14 |   154 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("JOB"=PRIOR "JOB")
       filter("DEPTNO">PRIOR "DEPTNO")

17 rows selected.

SQL>



SY.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.07.2017, 18:10
    #39496264
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
connect by + distinct
xtender
разберись в своих тестах и планах. Приложенный план у тебя от другого запроса, нежели в показанном тесте.

зы. хз что такое "влаштовует"

Код: 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.
SQL> select * from t;

    ID_REF         CH N
---------- ---------- --
         1            r
         2          1 d1
         2          1 d1
         2          1 d2
         3          2 v1
         3          2 v1
         3          2 v1

7 rows selected.

SQL> set autotrace on exp
SQL> select
  2     id_ref, level l
  3    ,stax_log(id_ref) sss
  4    from tv
  5    start with id_ref=3
  6    connect by prior ch=id_ref
  7  /

    ID_REF          L SSS
---------- ---------- -----
         3          1 3
         2          2 2
         1          3 1
         2          2 2
         1          3 1


Execution Plan
----------------------------------------------------------
Plan hash value: 2254541599

----------------------------------------------------------------------------------
| Id  | Operation                 | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |      |     2 |    52 |     5  (20)| 00:00:01 |
|*  1 |  CONNECT BY WITH FILTERING|      |       |       |            |          |
|   2 |   VIEW                    | TV   |     2 |    52 |     5  (20)| 00:00:01 |
|   3 |    HASH UNIQUE            |      |     2 |    18 |     5  (20)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL     | T    |     3 |    27 |     4   (0)| 00:00:01 |
|   5 |   VIEW                    | TV   |     2 |    52 |     5  (20)| 00:00:01 |
|   6 |    HASH UNIQUE            |      |     2 |    18 |     5  (20)| 00:00:01 |
|*  7 |     HASH JOIN             |      |       |       |            |          |
|   8 |      CONNECT BY PUMP      |      |       |       |            |          |
|   9 |      TABLE ACCESS FULL    | T    |     2 |    18 |     4   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("ID_REF"=PRIOR "CH")
   4 - filter("ID_REF"=3)
   7 - access("ID_REF"=PRIOR "CH")



завязиваю с темой, будет время мож что придумаю с тестом

зы
"влаштовует" очетятка, правильно "устраивает"

.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.07.2017, 18:23
    #39496268
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
connect by + distinct
SY,
імхо
речь несколько о другом distinct, во фразе from (c вьюшки с distinct)
типа select ... from (select distinct ...) start with ...

речь о втором селекте (может вернуть дубликаты)
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
SQL> select distinct id_ref,ch,level from t
  2  start with ch is null connect by ch=prior id_ref
  3  /

    ID_REF         CH      LEVEL
---------- ---------- ----------
         1                     1
         2          1          2
         3          2          3

SQL> select id_ref,ch,level from (select distinct * from t ) tt
  2  start with ch is null connect by ch=prior id_ref
  3  /

    ID_REF         CH      LEVEL
---------- ---------- ----------
         1                     1
         2          1          2
         3          2          3
         2          1          2
         3          2          3



.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.07.2017, 10:07
    #39496484
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
connect by + distinct
вот придумал тест
не выходит каменный цветок
Код: 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.
create or replace function stax_log (p_id in number)
  return varchar2
  as
  begin
    DBMS_APPLICATION_INFO.SET_CLIENT_INFO
    (userenv('client_info')||p_id||'/'||to_char(sysdate,'ss')||' ');
   return p_id;
end;
/

create or replace function stax_log2 (p_id in number)
  return varchar2
  as
begin
  dbms_output.put(to_char(p_id)||' '||to_char(sysdate,'mi:ss'));
  dbms_lock.sleep(1);
  dbms_output.put_line(to_char(sysdate,'"/"ss'));
  return p_id;
end;
/
SQL> select * from t;

    ID_REF         CH N
---------- ---------- --
         1            r
         2          1 d1
         2          1 d1
         2          1 d2
         3          2 v1
         3          2 v1
         3          2 v1

7 rows selected.

SQL> select * from tv;

    ID_REF         CH N  SSS
---------- ---------- -- -----
         1            r  1
         2          1 d1 2
         2          1 d2 2
         3          2 v1 3

SQL> exec DBMS_APPLICATION_INFO.SET_CLIENT_INFO ('');

PL/SQL procedure successfully completed.

SQL> select id_ref,ch,level,stax_log2(id_ref) sss
  2  from tv
  3  start with ch is null
  4  connect by ch=prior id_ref
  5  /

    ID_REF         CH      LEVEL SSS
---------- ---------- ---------- -----
         1                     1 1
         2          1          2 2
         3          2          3 3
         2          1          2 2
         3          2          3 3

1 01:00/01
2 01:01/02
3 01:02/03
2 01:03/04
3 01:04/05

Execution Plan
----------------------------------------------------------
Plan hash value: 2254541599

----------------------------------------------------------------------------------
| Id  | Operation                 | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |      |     4 |   104 |     5  (20)| 00:00:01 |
|*  1 |  CONNECT BY WITH FILTERING|      |       |       |            |          |
|   2 |   VIEW                    | TV   |     1 |    26 |     5  (20)| 00:00:01 |
|   3 |    HASH UNIQUE            |      |     1 |     9 |     5  (20)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL     | T    |     1 |     9 |     4   (0)| 00:00:01 |
|   5 |   VIEW                    | TV   |     4 |   104 |     5  (20)| 00:00:01 |
|   6 |    HASH UNIQUE            |      |     4 |    36 |     5  (20)| 00:00:01 |
|*  7 |     HASH JOIN             |      |       |       |            |          |
|   8 |      CONNECT BY PUMP      |      |       |       |            |          |
|   9 |      TABLE ACCESS FULL    | T    |     4 |    36 |     4   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("CH"=PRIOR "ID_REF")
   4 - filter("CH" IS NULL)
   7 - access("CH"=PRIOR "ID_REF")

SQL> set autotrace off
SQL> select userenv('client_info') from dual;

USERENV('CLIENT_INFO')
----------------------------------------------------------------
1/00 2/00 2/00 2/00 3/00 3/00 3/00

SQL>



если БЫ select distinct вычислялся на каждом шаге то и время в client_info БЫ поплыло
stax_log выпонилась за раз в момет старта 01:00

критикуйте

.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.07.2017, 12:30
    #39496594
Sayan Malakshinov
Sayan Malakshinov
Модератор форума
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
connect by + distinct
Stax,

Вместо autotrace делай dbms_xplan.display_cursor c параметром +projection - имхо по плану у тебя функция вызывается только после hash unique на шаге View.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
  1  все
Форумы / Oracle [игнор отключен] [закрыт для гостей] / connect by + distinct / 25 сообщений из 36, страница 1 из 2
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 545ms
Закрыть
start [/forum/topic.php?fid=52&tablet=1&tid=1885524]:
 0ms
get settings:
 10ms
get forum list:
 14ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 215ms
get topic data:
 8ms
get forum data:
 2ms
get page messages:
 56ms
get tp. blocked users:
 1ms
others: 233ms
total:  545ms
x
x
Закрыть


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