sys_connect_by_path(rownum) с фильтрацией / Oracle

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / Oracle [игнор отключен] [закрыт для гостей] / sys_connect_by_path(rownum) с фильтрацией

54 сообщений из 54, показаны все 3 страниц

все

sys_connect_by_path(rownum) с фильтрацией

#34953434

Elic

Участник

Сообщения: 30 360

Рейтинг: 0 / 0

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.

select * from
( select sys_connect_by_path(rownum, '~') as path from dual
    where mod(level,  3 ) =  0 
    connect by level <=  10 
);

PATH
----------------------
~ 1 ~ 1 ~ 1 
~ 1 ~ 1 ~ 1 ~ 2 ~ 2 ~ 2 
~ 1 ~ 1 ~ 1 ~ 2 ~ 2 ~ 2 ~ 3 ~ 3 ~ 3 

 3  rows selected.

Парадоксально прикольное сочленение несочленимого :)

...

Рейтинг:

0 / 0

20.11.2007, 20:00

| Ответить | Цитировать | Написать

sys_connect_by_path(rownum) с фильтрацией

#34953873

SQLap

Участник

Сообщения: 34 800

Рейтинг: 0 / 0

клевая конструкция))

...

Рейтинг:

0 / 0

21.11.2007, 07:43

| Ответить | Цитировать | Написать

sys_connect_by_path(rownum) с фильтрацией

#34954623

тобобетобор

Участник

Откуда: Москва

Сообщения: 284

Рейтинг: 0 / 0

хм, а как это работает ?

...

Рейтинг:

0 / 0

21.11.2007, 11:39

| Ответить | Цитировать | Написать

sys_connect_by_path(rownum) с фильтрацией

#34954640

bI!

Гость

тобобетоборхм, а как это работает ?
По сети... но может и локально :)

...

Рейтинг:

0 / 0

21.11.2007, 11:42

| Ответить | Цитировать | Написать

sys_connect_by_path(rownum) с фильтрацией

#34954641

Jannny

Участник

Откуда: Спб

Сообщения: 6 413

Рейтинг: 0 / 0

тобобетоборхм, а как это работает ? ElicПарадоксально

...

Рейтинг:

0 / 0

21.11.2007, 11:42

| Ответить | Цитировать | Написать

sys_connect_by_path(rownum) с фильтрацией

#34954658

xymbo

Участник

Откуда: Донской --> Москва

Сообщения: 2 071

Рейтинг: 0 / 0

ltrim :)
Только для меня пока неясно, почему "несочленимого"?

...

Рейтинг:

0 / 0

21.11.2007, 11:46

| Ответить | Цитировать | Написать

sys_connect_by_path(rownum) с фильтрацией

#34954720

Elic

Участник

Сообщения: 30 360

Рейтинг: 0 / 0

xymboltrim :)Ты не в теме.
xymboТолько для меня пока неясно, почему "несочленимого"?Путь должен быть сформирован на наборе данных до фильтрации.
Отсюда парадокс: нам нужен rownum до фильтрации, но вычислен rownum может быть только после фильтрации, когда тех строк уже нет :)
Oracle любопытно из этой ситуации вышел. А мог(должен?) бы и выругаться :)

...

Рейтинг:

0 / 0

21.11.2007, 11:58

| Ответить | Цитировать | Написать

sys_connect_by_path(rownum) с фильтрацией

#34954754

andreymx

Участник

Откуда: Запорожье

Сообщения: 56 486

Рейтинг: 0 / 0

ElicПуть должен быть сформирован на наборе данных до фильтрации.
Отсюда парадокс: нам нужен rownum до фильтрации, но вычислен rownum может быть только после фильтрации, когда тех строк уже нет :)
Oracle любопытно из этой ситуации вышел. А мог(должен?) бы и выругаться :)Момент вычисления Ораклом результата функции для вывода в поле запроса всегда был для меня загадкой - обычно он откладывался на самый последний момент. Ну и количество обращений к этой же функции

...

Рейтинг:

0 / 0

21.11.2007, 12:04

| Ответить | Цитировать | Написать

sys_connect_by_path(rownum) с фильтрацией

#34954801

xymbo

Участник

Откуда: Донской --> Москва

Сообщения: 2 071

Рейтинг: 0 / 0

Elic xymboltrim :)Ты не в теме.
xymboТолько для меня пока неясно, почему "несочленимого"?Путь должен быть сформирован на наборе данных до фильтрации.
Отсюда парадокс: нам нужен rownum до фильтрации, но вычислен rownum может быть только после фильтрации, когда тех строк уже нет :)
Oracle любопытно из этой ситуации вышел. А мог(должен?) бы и выругаться :)
по идее путь должен был быть таким?

Код: plaintext

1.
2.

 1 ~ 2 ~ 3 
 1 ~ 2 ~ 3 ~ 4 ~ 5 ~ 6 
 1 ~ 2 ~ 3 ~ 4 ~ 5 ~ 6 ~ 7 ~ 8 ~ 9

Спасибо за объяснение.

...

Рейтинг:

0 / 0

21.11.2007, 12:12

| Ответить | Цитировать | Написать

sys_connect_by_path(rownum) с фильтрацией

#34954812

OraDen

Участник

Сообщения: 740

Рейтинг: 0 / 0

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

select * from
( select rownum, level, sys_connect_by_path(rownum, '~') as pathr, sys_connect_by_path(level, '~') as pathl from dual
    where mod(level,  3 ) =  0 
    connect by level <=  10 
);

    ROWNUM      LEVEL PATHR                          PATHL
---------- ---------- ------------------------------ ------------------------------
          1            3  ~ 1 ~ 1 ~ 1                          ~ 1 ~ 2 ~ 3 
          2            6  ~ 1 ~ 1 ~ 1 ~ 2 ~ 2 ~ 2                    ~ 1 ~ 2 ~ 3 ~ 4 ~ 5 ~ 6 
          3            9  ~ 1 ~ 1 ~ 1 ~ 2 ~ 2 ~ 2 ~ 3 ~ 3 ~ 3              ~ 1 ~ 2 ~ 3 ~ 4 ~ 5 ~ 6 ~ 7 ~ 8 ~ 9

...

Рейтинг:

0 / 0

21.11.2007, 12:15

| Ответить | Цитировать | Написать

sys_connect_by_path(rownum) с фильтрацией

#34954814

ModelR

Участник

Откуда: Нижний Новгород

Сообщения: 1 802

Рейтинг: 0 / 0

тобобетоборхм, а как это работает ?
Похоже по принципу тихо сам с собою...
Для каждой строки, удовлетворяющей start with
level щелкает (топает вглубь), соответсвенно path копиться,
where ( без джойнов работает после иерархии) сгенрированные данные отсеивает - соответсвенно rownum не щелкает.

...

Рейтинг:

0 / 0

21.11.2007, 12:16

| Ответить | Цитировать | Написать

sys_connect_by_path(rownum) с фильтрацией

#34954832

OraDen

Участник

Сообщения: 740

Рейтинг: 0 / 0

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.

select * from
( select rownum, level, sys_connect_by_path(rownum, '~') as pathr, sys_connect_by_path(level, '~') as pathl from dual
    connect by level <=  10 
);

    ROWNUM      LEVEL PATHR                          PATHL
---------- ---------- ------------------------------ ------------------------------
          1            1  ~ 1                              ~ 1 
          2            2  ~ 1 ~ 2                            ~ 1 ~ 2 
          3            3  ~ 1 ~ 2 ~ 3                          ~ 1 ~ 2 ~ 3 
          4            4  ~ 1 ~ 2 ~ 3 ~ 4                        ~ 1 ~ 2 ~ 3 ~ 4 
          5            5  ~ 1 ~ 2 ~ 3 ~ 4 ~ 5                      ~ 1 ~ 2 ~ 3 ~ 4 ~ 5 
          6            6  ~ 1 ~ 2 ~ 3 ~ 4 ~ 5 ~ 6                    ~ 1 ~ 2 ~ 3 ~ 4 ~ 5 ~ 6 
          7            7  ~ 1 ~ 2 ~ 3 ~ 4 ~ 5 ~ 6 ~ 7                  ~ 1 ~ 2 ~ 3 ~ 4 ~ 5 ~ 6 ~ 7 
          8            8  ~ 1 ~ 2 ~ 3 ~ 4 ~ 5 ~ 6 ~ 7 ~ 8                ~ 1 ~ 2 ~ 3 ~ 4 ~ 5 ~ 6 ~ 7 ~ 8 
          9            9  ~ 1 ~ 2 ~ 3 ~ 4 ~ 5 ~ 6 ~ 7 ~ 8 ~ 9              ~ 1 ~ 2 ~ 3 ~ 4 ~ 5 ~ 6 ~ 7 ~ 8 ~ 9 
         10           10  ~ 1 ~ 2 ~ 3 ~ 4 ~ 5 ~ 6 ~ 7 ~ 8 ~ 9 ~ 10           ~ 1 ~ 2 ~ 3 ~ 4 ~ 5 ~ 6 ~ 7 ~ 8 ~ 9 ~ 10

...

Рейтинг:

0 / 0

21.11.2007, 12:20

| Ответить | Цитировать | Написать

sys_connect_by_path(rownum) с фильтрацией

#34954844

andrey_anonymous

Участник

Откуда: Москва

Сообщения: 18 719

Рейтинг: 0 / 0

OraDen[src oracle][/src]
И что? Фильтрация-то где?

...

Рейтинг:

0 / 0

21.11.2007, 12:22

| Ответить | Цитировать | Написать

sys_connect_by_path(rownum) с фильтрацией

#34954847

ModelR

Участник

Откуда: Нижний Новгород

Сообщения: 1 802

Рейтинг: 0 / 0

Код: plaintext

1.
2.
3.
4.
5.
6.

select * from
( select level, sys_connect_by_path(rn, '~') as path1,
         sys_connect_by_path(rownum, '~') as path2
  from (select rownum rn from all_objects where rownum<= 2 ) tb
   where level in ( 3 , 5 )
      connect by level <=  5 
);

LEVELPATH1PATH23~1~1~1~1~1~15~1~1~1~1~1~1~1~1~2~25~1~1~1~1~2~1~1~1~2~35~1~1~1~2~1~1~1~1~4~45~1~1~1~2~2~1~1~1~4~53~1~1~2~1~1~65~1~1~2~1~1~1~1~6~7~75~1~1~2~1~2~1~1~6~7~85~1~1~2~2~1~1~1~6~9~95~1~1~2~2~2~1~1~6~9~103~1~2~1~1~11~115~1~2~1~1~1~1~11~11~12~125~1~2~1~1~2~1~11~11~12~135~1~2~1~2~1~1~11~11~14~145~1~2~1~2~2~1~11~11~14~153~1~2~2~1~11~165~1~2~2~1~1~1~11~16~17~175~1~2~2~1~2~1~11~16~17~185~1~2~2~2~1~1~11~16~19~195~1~2~2~2~2~1~11~16~19~203~2~1~1~21~21~215~2~1~1~1~1~21~21~21~22~225~2~1~1~1~2~21~21~21~22~235~2~1~1~2~1~21~21~21~24~245~2~1~1~2~2~21~21~21~24~253~2~1~2~21~21~265~2~1~2~1~1~21~21~26~27~275~2~1~2~1~2~21~21~26~27~285~2~1~2~2~1~21~21~26~29~295~2~1~2~2~2~21~21~26~29~303~2~2~1~21~31~315~2~2~1~1~1~21~31~31~32~325~2~2~1~1~2~21~31~31~32~335~2~2~1~2~1~21~31~31~34~345~2~2~1~2~2~21~31~31~34~353~2~2~2~21~31~365~2~2~2~1~1~21~31~36~37~375~2~2~2~1~2~21~31~36~37~385~2~2~2~2~1~21~31~36~39~395~2~2~2~2~2~21~31~36~39~40

...

Рейтинг:

0 / 0

21.11.2007, 12:24

| Ответить | Цитировать | Написать

sys_connect_by_path(rownum) с фильтрацией

#34954959

Elic

Участник

Сообщения: 30 360

Рейтинг: 0 / 0

ModelR

Код: plaintext

1.
2.
3.
4.
5.
6.

select * from
( select level, sys_connect_by_path(rn, '~') as path1,
         sys_connect_by_path(rownum, '~') as path2
  from (select rownum rn from all_objects where rownum<= 2 ) tb
   where level in ( 3 , 5 )
      connect by level <=  5 
);

И что ты этим хотел сказать? Всё то же самое: count(*)=max(rownum from path2). Только у тебя всё гораздо туманней.

ModelRДля каждой строки, удовлетворяющей start with
level щелкает (топает вглубь)

xymboпо идее путь должен был быть таким?Нет. А правильнее: не знаю. В том-то и парадокс: мы просим у Oracle-а невозможного :) И любой его ответ - неправильный

IMHO, пока просто удачное стечение обстоятельств у него внутрях. Когда-нибудь оно кончится с ORA-07445.
Интересно, как это в 11g? :)

...

Рейтинг:

0 / 0

21.11.2007, 12:47

| Ответить | Цитировать | Написать

sys_connect_by_path(rownum) с фильтрацией

#34955032

ModelR

Участник

Откуда: Нижний Новгород

Сообщения: 1 802

Рейтинг: 0 / 0

ElicИ что ты этим хотел сказать? Показать, как перебираются записи исходной таблицы. Вглубь сколько можно _без_ обращения к данным, лишь затем к следующей записи. ElicВсё то же самое: count(*)=max(rownum from path2). Было бы очень странно ожидать, что ORACLE нумерует строки результата как-то иначе. ElicТолько у тебя всё гораздо туманней.Э?

...

Рейтинг:

0 / 0

21.11.2007, 13:01

| Ответить | Цитировать | Написать

sys_connect_by_path(rownum) с фильтрацией

#34955038

Timm

Участник

Откуда: Moscow, Ё-burg

Сообщения: 3 678

Рейтинг: 0 / 0

ElicИнтересно, как это в 11g? :)
та же фигня.

...

Рейтинг:

0 / 0

21.11.2007, 13:03

| Ответить | Цитировать | Написать

sys_connect_by_path(rownum) с фильтрацией

#34955122

Elic

Участник

Сообщения: 30 360

Рейтинг: 0 / 0

ModelR ElicИ что ты этим хотел сказать? Показать, как перебираются записи исходной таблицы. Вглубь сколько можно _без_ обращения к данным, лишь затем к следующей записи.Ты не просёк тему. Не об этом речь. На тебе "чистое" дерево:

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.

select * from
( select sys_connect_by_path(rownum, '~') as path 
    from (select rownum as rn from all_objects where rownum <=  10 )
    where mod(level,  3 ) =  0 
    start with rn =  1 
    connect by rn = prior rn +  1 
);

PATH
--------------------
~ 1 ~ 1 ~ 1 
~ 1 ~ 1 ~ 1 ~ 2 ~ 2 ~ 2 
~ 1 ~ 1 ~ 1 ~ 2 ~ 2 ~ 2 ~ 3 ~ 3 ~ 3

А теперь выслушаю :)

...

Рейтинг:

0 / 0

21.11.2007, 13:22

| Ответить | Цитировать | Написать

sys_connect_by_path(rownum) с фильтрацией

#34955186

в ту же степь

Гость

Код: plaintext

1.
2.
3.
4.
5.
6.
7.

SELECT	LEVEL,
		ROWNUM,
		SYS_CONNECT_BY_PATH(LEVEL, '=') pathl,
		SYS_CONNECT_BY_PATH(ROWNUM, '=') pathr,
		SYS_CONNECT_BY_PATH(LEVEL||ROWNUM, '=') pathlr
FROM	dual
WHERE LEVEL IN ( 3 , 6 , 9 )
CONNECT BY LEVEL <  10

Row#LEVELROWNUMPATHLPATHRPATHLR131=1=2=3=1=1=1=11=21=31262=1=2=3=4=5=6=1=1=1=2=2=2=11=21=31=42=52=62393=1=2=3=4=5=6=7=8=9=1=1=1=2=2=2=3=3=3=11=21=31=42=52=62=73=83=93

...

Рейтинг:

0 / 0

21.11.2007, 13:35

| Ответить | Цитировать | Написать

sys_connect_by_path(rownum) с фильтрацией

#34955243

ModelR

Участник

Откуда: Нижний Новгород

Сообщения: 1 802

Рейтинг: 0 / 0

ElicНа тебе "чистое" деревоДык какое ж оно дерево? деревья они с ветками:)

Код: plaintext

1.
2.
3.
4.
5.
6.

select * from
( select sys_connect_by_path(rownum, '~') as path 
    from (select  1  as rn  from dual union all select  2  as rn  from dual union all select  3  as rn  from dual  union all select  3  as rn  from dual )
    where mod(level, 3 ) =  0 
    start with rn =  1 
    connect by rn = prior rn +  1 
);

PATH ~1~1~1 ~1~1~2

...

Рейтинг:

0 / 0

21.11.2007, 13:46

| Ответить | Цитировать | Написать

sys_connect_by_path(rownum) с фильтрацией

#34955289

Elic

Участник

Сообщения: 30 360

Рейтинг: 0 / 0

ModelRДык какое ж оно дерево? деревья они с ветками:)Не хочешь погружаться в тему - бог с тобой, я не настаиваю.

...

Рейтинг:

0 / 0

21.11.2007, 13:54

| Ответить | Цитировать | Написать

sys_connect_by_path(rownum) с фильтрацией

#34955948

kosour

Гость

Код: 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.
26.
27.
28.
29.
30.
31.
32.

SQL> explain plan for
   2   select * from
   3   ( select sys_connect_by_path(rownum, '~') as path from dual t
   4   where mod(level,  3 ) =  0  and t.dummy <> 'Q5'
   5    connect by level <=  10 );

Объяснено.

SQL>  select * from table(dbms_xplan.display());

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

-----------------------------------------------------------------------------
| Id  | Operation                       | Name | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
|    0  | SELECT STATEMENT                |      |      1  |   2002  |      2    ( 0 )|
|    1  |  VIEW                           |      |      1  |   2002  |      2    ( 0 )|
|    2  |   COUNT                         |      |       |       |            |
|*   3  |    FILTER                       |      |       |       |            |
|*   4  |     CONNECT BY WITHOUT FILTERING|      |       |       |            |
|    5  |      TABLE ACCESS FULL          | DUAL |      1  |      2  |      2    ( 0 )|
-----------------------------------------------------------------------------

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

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

    3  - filter(MOD(LEVEL, 3 )= 0  AND "T"."DUMMY"<>'Q5')
    4  - filter(LEVEL<= 10 )

и что еще хуже:

Код: plaintext

1.
2.
3.
4.
5.
6.

select * from
( select * from dba_objects t
    where  t.owner ='UKNOWN_USER'
    start with t.object_id =  0 
    connect by  t.object_id = t.data_object_id
)

по базе, в которой нет пользователя UNKOWN_USER
выдает тот же
CONNECT BY WITHOUT FILTERING

Код: 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.
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.

SQL>   select * from table(dbms_xplan.display());

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

--------------------------------------------------------------------------------------
| Id  | Operation                         | Name        | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------------
|    0  | SELECT STATEMENT                  |             |  57164  |  9880K|    173    ( 6 )|
|    1  |  VIEW                             |             |  57164  |  9880K|    173    ( 6 )|
|*   2  |   FILTER                          |             |       |       |            |
|*   3  |    CONNECT BY WITHOUT FILTERING   |             |       |       |            |
|*   4  |     VIEW                          | DBA_OBJECTS |  57164  |    12M|    173    ( 6 )|
|    5  |      UNION-ALL                    |             |       |       |            |
|*   6  |       FILTER                      |             |       |       |            |

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
|*   7  |        HASH JOIN                  |             |  62674  |  5814K|    167    ( 6 )|
|    8  |         TABLE ACCESS FULL         | USER$       |    132  |   1980  |      4    ( 0 )|
|*   9  |         TABLE ACCESS FULL         | OBJ$        |  62674  |  4896K|    161    ( 5 )|
|*  10  |        TABLE ACCESS BY INDEX ROWID| IND$        |      1  |     26  |      1    ( 0 )|
|*  11  |         INDEX UNIQUE SCAN         | I_IND1      |      1  |       |      1    ( 0 )|
|*  12  |       HASH JOIN                   |             |     12  |    552  |      7   ( 15 )|
|   13  |        TABLE ACCESS FULL          | LINK$       |     12  |    372  |      2    ( 0 )|
|   14  |        TABLE ACCESS FULL          | USER$       |    132  |   1980  |      4    ( 0 )|
|   15  |     VIEW                          | DBA_OBJECTS |  57164  |  9880K|    173    ( 6 )|
|   16  |      UNION-ALL                    |             |       |       |            |
|*  17  |       FILTER                      |             |       |       |            |

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
|*  18  |        HASH JOIN                  |             |  62674  |  5263K|    167    ( 6 )|
|   19  |         TABLE ACCESS FULL         | USER$       |    132  |   1980  |      4    ( 0 )|
|*  20  |         TABLE ACCESS FULL         | OBJ$        |  62674  |  4345K|    161    ( 5 )|
|*  21  |        TABLE ACCESS BY INDEX ROWID| IND$        |      1  |      8  |      1    ( 0 )|
|*  22  |         INDEX UNIQUE SCAN         | I_IND1      |      1  |       |      1    ( 0 )|
|*  23  |       HASH JOIN                   |             |     12  |    552  |      7   ( 15 )|
|   24  |        TABLE ACCESS FULL          | LINK$       |     12  |    372  |      2    ( 0 )|
|   25  |        TABLE ACCESS FULL          | USER$       |    132  |   1980  |      4    ( 0 )|
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

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

    2  - filter("T"."OWNER"='UKNOWN_USER')
    3  - filter("T"."OBJECT_ID"="T"."DATA_OBJECT_ID")
    4  - filter("T"."OBJECT_ID"= 0 )
    6  - filter("O"."TYPE#"<> 1  AND "O"."TYPE#"<> 10  OR "O"."TYPE#"= 1  AND
              (SELECT /*+ */  1  FROM "SYS"."IND$" "I" WHERE "I"."OBJ#"=:B1 AND
              ("I"."TYPE#"= 1  OR "I"."TYPE#"= 2  OR "I"."TYPE#"= 3  OR "I"."TYPE#"= 4  OR
              "I"."TYPE#"= 6  OR "I"."TYPE#"= 7  OR "I"."TYPE#"= 9 ))= 1 )
    7  - access("O"."OWNER#"="U"."USER#")
    9  - filter("O"."LINKNAME" IS NULL AND "O"."NAME"<>'_NEXT_OBJECT' AND

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
              "O"."NAME"<>'_default_auditing_options_')
   10  - filter("I"."TYPE#"= 1  OR "I"."TYPE#"= 2  OR "I"."TYPE#"= 3  OR
              "I"."TYPE#"= 4  OR "I"."TYPE#"= 6  OR "I"."TYPE#"= 7  OR "I"."TYPE#"= 9 )
   11  - access("I"."OBJ#"=:B1)
   12  - access("L"."OWNER#"="U"."USER#")
   17  - filter("O"."TYPE#"<> 1  AND "O"."TYPE#"<> 10  OR "O"."TYPE#"= 1  AND
              (SELECT  1  FROM "SYS"."IND$" "I" WHERE "I"."OBJ#"=:B1 AND ("I"."TYPE#"= 1  OR
              "I"."TYPE#"= 2  OR "I"."TYPE#"= 3  OR "I"."TYPE#"= 4  OR "I"."TYPE#"= 6  OR
              "I"."TYPE#"= 7  OR "I"."TYPE#"= 9 ))= 1 )
   18  - access("O"."OWNER#"="U"."USER#")
   20  - filter("O"."LINKNAME" IS NULL AND "O"."NAME"<>'_NEXT_OBJECT' AND

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
              "O"."NAME"<>'_default_auditing_options_')
   21  - filter("I"."TYPE#"= 1  OR "I"."TYPE#"= 2  OR "I"."TYPE#"= 3  OR
              "I"."TYPE#"= 4  OR "I"."TYPE#"= 6  OR "I"."TYPE#"= 7  OR "I"."TYPE#"= 9 )
   22  - access("I"."OBJ#"=:B1)
   23  - access("L"."OWNER#"="U"."USER#")

Note
-----
   - 'PLAN_TABLE' is old version

 64  строк выбрано.

SQL>

Игры с

Код: plaintext

1.
2.

alter session Set "_old_connect_by_enabled"=true|false;
alter session Set "_optimizer_connect_by_cost_based"=true|false;

к успеху не привели, хотя ORA-600 и получил. Кроме того, оракл сказал, что не знает функции sys_connect_by_path :)

...

Рейтинг:

0 / 0

21.11.2007, 16:29

| Ответить | Цитировать | Написать

sys_connect_by_path(rownum) с фильтрацией

#34955973

Jannny

Участник

Откуда: Спб

Сообщения: 6 413

Рейтинг: 0 / 0

kosourпо базе, в которой нет пользователя UNKOWN_USER
выдает тот же
CONNECT BY WITHOUT FILTERINGА что в этом странного?

kosour

Код: plaintext

alter session Set "_old_connect_by_enabled"=true;

оракл сказал, что не знает функции sys_connect_by_path :)Вас это удивляет?

kosourИгры с
...
к успеху не привелиА какого успеха в данном случае Вы добивались???

...

Рейтинг:

0 / 0

21.11.2007, 16:35

| Ответить | Цитировать | Написать

sys_connect_by_path(rownum) с фильтрацией

#34956304

kosour

Гость

Jannny
А какого успеха в данном случае Вы добивались???

Чтобы дерево строилось на основе моих данных (т.е. я хочу построить дерево только на основе объектов пользователя UNKOWN_USER). То, что этот запрос выполняется долго - это полбеды, а вот то, что он возвращает неверные данные...

Код: 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.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.

drop table a;
create table a as
select owner,object_name,object_type from dba_objects
union all
select distinct owner, object_type,null from dba_objects;

create index i#a on a(owner);

begin 
dbms_stats.gather_table_stats(ownname => user, tabname => 'A',estimate_percent =>  100 ,cascade => true); 
end;
/

select count(*) from (
  select LEVEL, ROWNUM, sys_connect_by_path(OBJECT_NAME, '->'), a.* from a
      where   a.owner ='QS_WS'
      start with A.OBJECT_TYPE IS NULL
      connect by  PRIOR A.OBJECT_NAME = A.OBJECT_TYPE
 );

SQL> /

  COUNT(*)
----------
       2255 

 select count(*) from (
  select LEVEL, ROWNUM, sys_connect_by_path(OBJECT_NAME, '->'), a.* from (select * from a
      where   a.owner ='QS_WS' ) a
      start with A.OBJECT_TYPE IS NULL  
      connect by  PRIOR A.OBJECT_NAME = A.OBJECT_TYPE
  );

  COUNT(*)
----------
         59 

SQL>

...

Рейтинг:

0 / 0

21.11.2007, 18:02

| Ответить | Цитировать | Написать

sys_connect_by_path(rownum) с фильтрацией

#34956351

Elic

Участник

Сообщения: 30 360

Рейтинг: 0 / 0

kosourЧтобы дерево строилось на основе моих данных (т.е. я хочу построить дерево только на основе объектов пользователя UNKOWN_USER). То, что этот запрос выполняется долго - это полбеды, а вот то, что он возвращает неверные данные...

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.

      where   a.owner ='QS_WS'
      start with A.OBJECT_TYPE IS NULL
      connect by  PRIOR A.OBJECT_NAME = A.OBJECT_TYPE
----------
       2255 

      where   a.owner ='QS_WS' ) a
      start with A.OBJECT_TYPE IS NULL  
      connect by  PRIOR A.OBJECT_NAME = A.OBJECT_TYPE
----------
         59

Это разные запросы, что говорит о том, что ты не понимаешь иерархических запросов, но зачем-то лезешь в чужую тему со своими блохами.
В твоей теме я, может быть, бы объяснил тебе, в чём ты лопух.

...

Рейтинг:

0 / 0

21.11.2007, 18:14

| Ответить | Цитировать | Написать

sys_connect_by_path(rownum) с фильтрацией

#34956362

Jannny

Участник

Откуда: Спб

Сообщения: 6 413

Рейтинг: 0 / 0

Ничего не понимаю:
1) какая связь с темой топика??
2) а что Вы хотите - в одном случае Вы говорите построить дерево, потом отфильровать, а во втором случае наоборот??

ЗЫ: и зачем Вам ROWNUM и sys_connect_by_path? Чтобы было чем ответить на вопрос 1?

...

Рейтинг:

0 / 0

21.11.2007, 18:19

| Ответить | Цитировать | Написать

sys_connect_by_path(rownum) с фильтрацией

#34956792

Apex

Участник

Откуда: Made in USSR

Сообщения: 3 841

Рейтинг: 0 / 0

kosourпо базе, в которой нет пользователя UNKOWN_USER
выдает тот же
CONNECT BY WITHOUT FILTERING
Hierarchical Queries

...

Рейтинг:

0 / 0

21.11.2007, 21:43

| Ответить | Цитировать | Написать

sys_connect_by_path(rownum) с фильтрацией

#34957338

ModelR

Участник

Откуда: Нижний Новгород

Сообщения: 1 802

Рейтинг: 0 / 0

2 Elic
Извини, если достаю. ElicОтсюда парадокс: нам нужен rownum до фильтрации, но вычислен rownum может быть только после фильтрации, Разве возможность (не обязательно в иерархическом) запросе написать ...where rownum... уже не свидетельствует о том, что rownum получает значения до фильтрации? В частности, пока не отобрано ни одной записи, rownum=1?

...

Рейтинг:

0 / 0

22.11.2007, 10:17

| Ответить | Цитировать | Написать

sys_connect_by_path(rownum) с фильтрацией

#34957358

Apex

Участник

Откуда: Made in USSR

Сообщения: 3 841

Рейтинг: 0 / 0

ModelR 2 Elic
Извини, если достаю. ElicОтсюда парадокс: нам нужен rownum до фильтрации, но вычислен rownum может быть только после фильтрации, Разве возможность (не обязательно в иерархическом) запросе написать ...where rownum... уже не свидетельствует о том, что rownum получает значения до фильтрации? В частности, пока не отобрано ни одной записи, rownum=1?
Этот фильтр (по rownum) применяется в последнюю очередь, когда все остальные уже отработали.

...

Рейтинг:

0 / 0

22.11.2007, 10:24

| Ответить | Цитировать | Написать

sys_connect_by_path(rownum) с фильтрацией

#34957368

Jannny

Участник

Откуда: Спб

Сообщения: 6 413

Рейтинг: 0 / 0

ModelRРазве возможность (не обязательно в иерархическом) запросе написать ...where rownum... уже не свидетельствует о том, что rownum получает значения до фильтрации? В частности, пока не отобрано ни одной записи, rownum=1?Т.е. Вы думаете, что Оракл может условие rownum >= выполнить до фильтрации? Т.е. получение Вами набора данных будет иметь случайный характер, зависящий от того попали ли "пронумерованные вначале" потом в фильтрацию? Сами подумайте - бред ведь :)

...

Рейтинг:

0 / 0

22.11.2007, 10:27

| Ответить | Цитировать | Написать

sys_connect_by_path(rownum) с фильтрацией

#34957411

ModelR

Участник

Откуда: Нижний Новгород

Сообщения: 1 802

Рейтинг: 0 / 0

Код: plaintext

1.
2.
3.
4.
5.

with t as
(select  10  nom,  'A10' name from dual union ALL
 select  40  nom,  'A40' name from dual union ALL
 select  30  nom,  'A30' name from dual union ALL
 select  20  nom,  'A20' name from dual )
select name from t where rownum*nom =  40

NAME A40 A20
и

Код: plaintext

1.
2.
3.
4.
5.

with t as
(select  10  nom,  'A10' name from dual union
 select  40  nom,  'A40' name from dual union
 select  30  nom,  'A30' name from dual union
 select  20  nom,  'A20' name from dual )
select name from t where rownum*nom =  40

NAME A40
Как это можно понять иначе чем, грубо говоря
rownum = 1
Цикл по фильтруемым данным
if where then {Выдать результат;rownum +=1 }
Конец цикла

...

Рейтинг:

0 / 0

22.11.2007, 10:41

| Ответить | Цитировать | Написать

sys_connect_by_path(rownum) с фильтрацией

#34957433

Elic

Участник

Сообщения: 30 360

Рейтинг: 0 / 0

ModelRуже не свидетельствует о том, что rownum получает значения до фильтрации?Если бы в данном случае это было так, то какой результат должен бы по-твоему получиться? :)
Попробуй развить свою мысль.

...

Рейтинг:

0 / 0

22.11.2007, 10:47

| Ответить | Цитировать | Написать

sys_connect_by_path(rownum) с фильтрацией

#34957693

ModelR

Участник

Откуда: Нижний Новгород

Сообщения: 1 802

Рейтинг: 0 / 0

Elicто какой результат должен бы по-твоему получиться? :)Ровно какой получился.
Я был бы удивлен результату типа

Код: plaintext

~NULL~NULL~ 1

Мысль развил чуть выше. Ошибаюсь?

...

Рейтинг:

0 / 0

22.11.2007, 11:55

| Ответить | Цитировать | Написать

sys_connect_by_path(rownum) с фильтрацией

#34957788

Elic

Участник

Сообщения: 30 360

Рейтинг: 0 / 0

ModelRМысль развил чуть выше. Ошибаюсь?Найди-ка место для sys_connect_by_path в своём псевдокоде :)

...

Рейтинг:

0 / 0

22.11.2007, 12:15

| Ответить | Цитировать | Написать

sys_connect_by_path(rownum) с фильтрацией

#34957926

ModelR

Участник

Откуда: Нижний Новгород

Сообщения: 1 802

Рейтинг: 0 / 0

ElicНайди-ка место для sys_connect_by_path в своём псевдокоде :)
по фильтруемым данным == по итератору дерева (в котором щелкают level и sys_coonect_by_xxxx)

...

Рейтинг:

0 / 0

22.11.2007, 12:45

| Ответить | Цитировать | Написать

sys_connect_by_path(rownum) с фильтрацией

#34958011

Elic

Участник

Сообщения: 30 360

Рейтинг: 0 / 0

ModelR ElicНайди-ка место для sys_connect_by_path в своём псевдокоде :)по фильтруемым данным == по итератору дерева (в котором щелкают level и sys_coonect_by_xxxx)Всё. Спасибо. Теперь логику увидел.

...

Рейтинг:

0 / 0

22.11.2007, 13:06

| Ответить | Цитировать | Написать

sys_connect_by_path(rownum) с фильтрацией

#34958046

$RA\/EN$

RA\/EN

Участник

Сообщения: 2 941

Рейтинг: 0 / 0

Продолжаем извращаться:

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.

select * from
( select sys_connect_by_path(rownum, '~') as path ,
         row_number() over (order by rownum) rno,
         rownum rnum,
         sys_connect_by_path(row_number() over (order by rownum), '~') path2
    from dual
    where mod(level,  3 ) =  0 
    connect by level <=  10 
)

PATHRNORNUMPATH21122 33

...

Рейтинг:

0 / 0

22.11.2007, 13:14

| Ответить | Цитировать | Написать

sys_connect_by_path(rownum) с фильтрацией

#34960588

$RA\/EN$

RA\/EN

Участник

Сообщения: 2 941

Рейтинг: 0 / 0

Ну как, это поведение можно объяснить?

...

Рейтинг:

0 / 0

23.11.2007, 11:31

| Ответить | Цитировать | Написать

sys_connect_by_path(rownum) с фильтрацией

#34960640

Jannny

Участник

Откуда: Спб

Сообщения: 6 413

Рейтинг: 0 / 0

RA\/ENНу как, это поведение можно объяснить?Это поведение объясняется просто: аналитика и sys_connect_by_path вместе не живут :)

...

Рейтинг:

0 / 0

23.11.2007, 11:44

| Ответить | Цитировать | Написать

sys_connect_by_path(rownum) с фильтрацией

#34961015

Elic

Участник

Сообщения: 30 360

Рейтинг: 0 / 0

RA\/ENНу как, это поведение можно объяснить?Ну тут гарантированный парадокс: аналитика считается после фильтрации, её в путь никак не вставить :)

...

Рейтинг:

0 / 0

23.11.2007, 13:10

| Ответить | Цитировать | Написать

sys_connect_by_path(rownum) с фильтрацией

#34961808

kosour

Гость

RA\/ENНу как, это поведение можно объяснить?

Похоже на Bug 3564507
SYS_CONNECT_BY_PATH() RETURNS NO VALUE WHEN ANALYTIC SQL FN IN SAME QUERY

...

Рейтинг:

0 / 0

23.11.2007, 16:09

| Ответить | Цитировать | Написать

sys_connect_by_path(rownum) с фильтрацией

#34962297

kosour

Гость

JannnyНичего не понимаю:
1) какая связь с темой топика??
2) а что Вы хотите - в одном случае Вы говорите построить дерево, потом отфильровать, а во втором случае наоборот??

ЗЫ: и зачем Вам ROWNUM и sys_connect_by_path? Чтобы было чем ответить на вопрос 1?

Нет, не для этого. Показалась нелогичным отложенная фильтрация строк запроса. Спасибо Apex за
ссылку на TFM :) В моих запросах практически не используются иерархические запросы, зато очень
болезнены вопросы фильтрации.

На табличке в 10-100 млн строк промахнешься - мало не покажется.
Поэтому мне казалось (хотелось :) ) чтобы оракл отбросил лишние строки как можно быстрее.
Почитав FM понял что это частный случай, поэтому оракл реализовал другой подход.

Все дальнейшее написано потому, что на форуме читателей гораздо больше писателей, поэтому
может быть кому-то будет полезно. Для тех, кому это все тривиально - можно не читать :)

Надеюсь, что ошибок мало :)

Собственно, на бытовом языке эти два алгоритма звучат так:
1.(Хотелось) Возьми исходные строки, отфильтруй лишнее, построй дерево и выдай результат.
2.(На самом деле) Возьми исходные строки, построй дерево, отфильтруй лишнее, выдай результат.

Причем, выдать результат надо как можно раньше. Т.е. если строка уже готова к выдаче(прошла фильтрацию),
и все колонки могут быть вычислены - то они вычисляются, строка отправляются пользователю и идет дальнейшее
построение дерева.

В первом алгоритме результат может быть неполным, потому что при фильтрации строк могут быть
потеряны некоторые промежуточные строки, из-за которых нижележащие в дереве уровни пропадут,
хотя будут удовлетворять условиям фильтра.

Например, Пусть есть таблица сотрудников компании из стандартного примера SCOTT.EMP. И нужно
выбрать сотрудников, чье имя начинается на букву "J" .

Код: plaintext

1.
2.
3.
4.

select empno, ename, level, sys_connect_by_path(ename,'->') as p from scott.emp
where ename like 'J%'
start with mgr is null
connect by mgr = prior empno

Если бы оракл действовал по этому алгритму, то он бы потерял JAMES, у которого менеджером
является BLAKE, чье имя не начинается на букву J.

собственно, правильный алгоритм описан в доке, на которую указал APEX.

Теперь собственно, к теме топика :)
В принципе, ModelR правильно указал на алгоритм, который приводит к такому неочевидному результату.
Мне захотелось потдвердить это на тестовом примере.
Кстати, он же помог понять как работает этот запрос в случае аналитических функций.

Для того, чтобы понять в каком порядке выполняется запрос, нужна вспомогательная функция,
которая будет давать всякий раз новое значение. Sequence для этого не подходит, потому что в
переделах строки не изменяется.

Код: 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.
26.
27.
28.

create or replace package a#h 
is
function get_id return integer;
procedure reset_id;
end a#h;

create or replace package body a#h 
is
cnt integer;
function get_id return integer
is
begin 
cnt:= cnt +  1 ;
return cnt;
end get_id;

/*
Сбрасываем переменную в 0, чтобы получать воспроизводимый результат
в тестовых запросах 
*/
procedure reset_id is
begin
  cnt :=  0 ;
end  reset_id; 

begin
cnt :=  0 ;
end a#h;

перед выполнением запросов будем вызывать a#h.reset_id;

Я немного дополнил исходный запрос Elic'a чтобы видно было этапы выполнения запроса:

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.

select begin_id, end_id, rnum, path_id, path_r from (
select a#h.get_id as end_id, a.* from
( select b.*, rownum as rnum
          , sys_connect_by_path(a#h.get_id, '~') as path_id
          , sys_connect_by_path(rownum, '~') as path_r
          from (select a#h.get_id as begin_id from dual ) b
    where mod(level,  3 ) = 0  and a#h.get_id  > - 1 
     start with a#h.get_id = 1 
    connect by level <=  10  and a#h.get_id  > - 2 
) a
)

В колонке PATH_ID хранится путь к корню дерева, причем, номер отражает реальный номер строки источника - до фильтрации.

Он возвращает следующий результат:
BEGIN_IDEND_IDRNUMPATH_IDPATH_R891~2~4~6~1~1~117182~2~4~6~11~13~15~1~1~1~2~2~226273~2~4~6~11~13~15~20~22~24~1~1~1~2~2~2~3~3~3

Теперь можно видеть, что запрос выполняется следующим образом:

Первая строка - номер, с которым строка будет выдана пользователю. Как и говорил ModelR, rownum
всякий раз увеличивается после выдачи строки пользователю. Т.е. rownum в процесс выполнения
запроса можно трактовать как "Сейчас готовится к выдаче строка с номером Rownum"

Цифры в ячейках означают номер шага выполнения алгоритмы. Цифра в скобках означает сколько
раз с начала запроса была вызвана функция a#h.get_id

Rownum11111111222222223333333344Level1122333344556666778899991010select begin_id, end_id, rnum, path_id, path_r from (10(8)11(9)21(17)22(18)32(26)33(27)select a#h.get_id as end_id, a.* from( select b.*, rownum as rnumВзять упорядоченный список родителей и добавить туда себя для функции sys_connect_by_path(a#h.get_id, '~')2(2)5(4)8(6)13(11)16(13)19(15)24(20)27(22)30(24)35(29) , sys_connect_by_path(a#h.get_id, '~') as path_id , sys_connect_by_path(rownum, '~') as path_r from (select a#h.get_id as begin_id from dual ) b where mod(level, 3) =0 and a#h.get_id > -13(2)6(4)9(7)14(11)17(13)20(16)25(20)28(22)31(25)36(29) start with a#h.get_id =11(1) connect by level <= 10 and a#h.get_id > -24(3)7(5)12(10)15(12)18(14)23(19)26(21)29(23)34(28)) a)

Несколько комментариев:
1. На шагах 3,6 счетчик вызовов функции a#h.get_id не меняется, так как имеет место быть быстрое
вычисление логического выражения: так как mod(level, 3) =0 - FALSE, то вторую часть вычислять не надо. На шаге 9, mod(level, 3) =0 - TRUE и поэтому вычисляем второе условие and a#h.get_id > -1
2. Судя по всему, Оракл ленив, и вызывает вычисление функции только при крайней необходимости.
Поэтому, например, begin_id и end_id вычисляются для строки самыми последними -
непосредственно, перед выдачей строки клиенту.
3. Из приведенного примера неясно в какой момент вызывается функция sys_connect_by_path - в
процессе обработки каждой строки, в виде, например выражения:

result := result ||delimiter||current_expression, где current_expression - значение аргумента функции для этой строки.

Либо при проходе по дереву формируется как-то список родителей, включая себя, а
непосредственно перед выдачей уже присходит обработка этого списка и превращение в строку
результата.

4. Как видно из пример, фильтрация происходит после обработки строки и в случае успеха, строка
отдается на формирование внешнего вида и выдачи, а в случае неуспеха - просто идет переход к
следующей строки в дереве. Поэтому такие странные значения стоят в выводе функции
sys_connect_by_path(rownum, '~')

Для аналитической функции механизм обработки немного изменяется.

...

Рейтинг:

0 / 0

23.11.2007, 18:50

| Ответить | Цитировать | Написать

Период между сообщениями больше года.

sys_connect_by_path(rownum) с фильтрацией

#39421031

dbms_photoshop

Участник

Откуда: sqlmdx.net

Сообщения: 5 140

Рейтинг: 0 / 0

ElicПуть должен быть сформирован на наборе данных до фильтрации.
Отсюда парадоксЕсли не делать озвученного допущения, то и парадоксов не будет.
Не совсем понятно из чего следует, что он должен? sys_connect_by_path не может быть использован в where.

Другое дело, пример хорош тем, что показывает, что, вероятно, не стоит разделять герерацию rownum/level и выполнение разных частей запроса.

В доке сказано
http://docs.oracle.com/cd/E11882_01/server.112/e41084/queries003.htm A join, if present, is evaluated first, whether the join is specified in the FROM clause or with WHERE clause predicates.

The CONNECT BY condition is evaluated.

Any remaining WHERE clause predicates are evaluated.Тут стоит уточнить, что если в where есть rownum/level то это выясиляется не перед/после, а во время постоения иерархии.

level генерится при переходе на новый уровень
rownum генерится попадании новой строки в результирующий recordset

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.

select rownum rn,
       level lvl,
       replace(sys_connect_by_path(rownum, '~'),'~') as path_rn,
       replace(sys_connect_by_path(level, '~'),'~') as path_lvl
  from dual
 where mod(level, 3) = 0
connect by level < 10;
select rownum rn,
       level lvl,
       replace(sys_connect_by_path(rownum, '~'),'~') as path_rn,
       replace(sys_connect_by_path(level, '~'),'~') as path_lvl
  from dual
 where mod(level, 3) = 0
connect by rownum < 10;

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.

        RN        LVL PATH_RN                        PATH_LVL                                          
---------- ---------- ------------------------------ --------------------------------------------------
         1          3 111                            123                                               
         2          6 111222                         123456                                            
         3          9 111222333                      123456789                                         

3 rows selected.

        RN        LVL PATH_RN                        PATH_LVL                                          
---------- ---------- ------------------------------ --------------------------------------------------
         1          3 111                            123                                               
         2          6 111222                         123456                                            
         3          9 111222333                      123456789                                         
         4         12 111222333444                   123456789101112                                   
         5         15 111222333444555                123456789101112131415                             
         6         18 111222333444555666             123456789101112131415161718                       
         7         21 111222333444555666777          123456789101112131415161718192021                 
         8         24 111222333444555666777888       123456789101112131415161718192021222324           
         9         27 111222333444555666777888999    123456789101112131415161718192021222324252627     

9 rows selected.

Если в условии соединения использовать prior и связь parent - child, то все работает по той же схеме.
Например для дерева, состоящего из двух веток, выходящих из null.

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.

with t as
(
select rownum id, rownum - 1 id_parent from dual connect by level <= 10
union all
select 1000 + rownum id, 1000 + rownum - 1 id_parent from dual connect by level <= 10
union all
select 0, null from dual
union all
select 1000, null from dual
)
select rownum rn,
       level lvl,
       replace(sys_connect_by_path(rownum, '~'), '~') as path_rn,
       replace(sys_connect_by_path(level, '~'), '~') as path_lvl,
       sys_connect_by_path(id, '~') path_id
  from t
 where mod(level, 3) = 0
start with id_parent is null
connect by prior id = id_parent;

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

        RN        LVL PATH_RN         PATH_LVL        PATH_ID                                           
---------- ---------- --------------- --------------- --------------------------------------------------
         1          3 111             123             ~0~1~2                                            
         2          6 111222          123456          ~0~1~2~3~4~5                                      
         3          9 111222333       123456789       ~0~1~2~3~4~5~6~7~8                                
         4          3 444             123             ~1000~1001~1002                                   
         5          6 444555          123456          ~1000~1001~1002~1003~1004~1005                    
         6          9 444555666       123456789       ~1000~1001~1002~1003~1004~1005~1006~1007~1008     

6 rows selected.

...

Рейтинг:

0 / 0

16.03.2017, 18:41

| Ответить | Цитировать | Написать

sys_connect_by_path(rownum) с фильтрацией

#39421045

stax..

Участник

Сообщения: 6 197

Рейтинг: 0 / 0

dbms_photoshop,

сортировка выполняется до "where"

ps
при написании запроса надо учитывать что оракля считает Any remaining WHERE clause predicates are evaluated.

.....
stax

...

Рейтинг:

0 / 0

16.03.2017, 19:09

| Ответить | Цитировать | Написать

sys_connect_by_path(rownum) с фильтрацией

#39421049

dbms_photoshop

Участник

Откуда: sqlmdx.net

Сообщения: 5 140

Рейтинг: 0 / 0

stax..сортировка выполняется до "where" Что?

...

Рейтинг:

0 / 0

16.03.2017, 19:17

| Ответить | Цитировать | Написать

sys_connect_by_path(rownum) с фильтрацией

#39421057

stax..

Участник

Сообщения: 6 197

Рейтинг: 0 / 0

dbms_photoshopstax..сортировка выполняется до "where" Что?
а когда по твоему ?

соответственно и ровнум вычисляется после "иерархичной" сортировки

.....
stax

...

Рейтинг:

0 / 0

16.03.2017, 19:45

| Ответить | Цитировать | Написать

sys_connect_by_path(rownum) с фильтрацией

#39421085

dbms_photoshop

Участник

Откуда: sqlmdx.net

Сообщения: 5 140

Рейтинг: 0 / 0

stax..,

Никакой сортировки по умолчанию не выполняется.
Выполняется построение дерева методом обхода в глубину.

Более конкретным языком
http://docs.oracle.com/cd/B19306_01/server.102/b14200/queries003.htm Oracle returns the rows in the order shown in Figure 9-1. In the diagram, children appear below their parents. For an explanation of hierarchical trees, see Figure 3-1, "Hierarchical Tree".
Тут можно привести следующий интересный пример.

Если в "генераторе" не одна строка, а больше, то при использовании level в условии соединения результат вполне ожидаем.

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.

with t as (select 1 id from dual union select 0 from dual)
select rownum rn,
       level lvl,
       replace(sys_connect_by_path(rownum, '~'), '~') as path_rn,
       replace(sys_connect_by_path(level, '~'), '~') as path_lvl,
       t.*
  from t
connect by level <= 2;

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

        RN        LVL PATH_RN         PATH_LVL                ID
---------- ---------- --------------- --------------- ----------
         1          1 1               1                        0
         2          2 12              12                       0
         3          2 13              12                       1
         4          1 4               1                        1
         5          2 45              12                       0
         6          2 46              12                       1

6 rows selected.

А при использовании rownum на первый взягляд может показаться неожиданным, но по факту все довольно закономерно.
Оракл сначала опускается на указанное число строк (и так получается, что уровней) из одного корня, потом переходит на другой корень и условие соединения сразу же становится ложным.

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.

with t as (select 1 id from dual union select 0 from dual)
select rownum rn,
       level lvl,
       replace(sys_connect_by_path(rownum, '~'), '~') as path_rn,
       replace(sys_connect_by_path(level, '~'), '~') as path_lvl,
       t.*
  from t
connect by rownum <= 5;
with t as (select 1 id from dual union select 0 from dual)
select rownum rn,
       level lvl,
       replace(sys_connect_by_path(rownum, '~'), '~') as path_rn,
       replace(sys_connect_by_path(level, '~'), '~') as path_lvl,
       t.*
  from t
connect by rownum <= 6;

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.

        RN        LVL PATH_RN         PATH_LVL                ID
---------- ---------- --------------- --------------- ----------
         1          1 1               1                        0
         2          2 12              12                       0
         3          3 123             123                      0
         4          4 1234            1234                     0
         5          5 12345           12345                    0
         6          1 6               1                        1

6 rows selected.

        RN        LVL PATH_RN         PATH_LVL                ID
---------- ---------- --------------- --------------- ----------
         1          1 1               1                        0
         2          2 12              12                       0
         3          3 123             123                      0
         4          4 1234            1234                     0
         5          5 12345           12345                    0
         6          6 123456          123456                   0
         7          1 7               1                        1

7 rows selected.

...

Рейтинг:

0 / 0

16.03.2017, 22:10

| Ответить | Цитировать | Написать

sys_connect_by_path(rownum) с фильтрацией

#39421088

Участник

Откуда: Middlebury, CT USA

Сообщения: 10 588

Рейтинг: 0 / 0

dbms_photoshop
Никакой сортировки по умолчанию не выполняется.
Выполняется построение дерева методом обхода в глубину.

Не путай DEPTH/BREADTH FIRST c ORDER SIBLINGS BY:

Код: 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.

with t as
(
select rownum id, rownum - 1 id_parent from dual connect by level <= 10
union all
select 1000 + rownum id, 1000 + rownum - 1 id_parent from dual connect by level <= 10
union all
select 0, null from dual
union all
select 1000, null from dual
)
select rownum rn,
       level lvl,
       replace(sys_connect_by_path(rownum, '~'), '~') as path_rn,
       replace(sys_connect_by_path(level, '~'), '~') as path_lvl,
       sys_connect_by_path(id, '~') path_id
  from t
 where mod(level, 3) = 0
start with id_parent is null
connect by prior id = id_parent;


        RN        LVL PATH_RN         PATH_LVL        PATH_ID
---------- ---------- --------------- --------------- ---------------------------------------------
         1          3 111             123             ~0~1~2
         2          6 111222          123456          ~0~1~2~3~4~5
         3          9 111222333       123456789       ~0~1~2~3~4~5~6~7~8
         4          3 444             123             ~1000~1001~1002
         5          6 444555          123456          ~1000~1001~1002~1003~1004~1005
         6          9 444555666       123456789       ~1000~1001~1002~1003~1004~1005~1006~1007~1008

6 rows selected.

Код: 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.

with t as
(
select rownum id, rownum - 1 id_parent from dual connect by level <= 10
union all
select 1000 + rownum id, 1000 + rownum - 1 id_parent from dual connect by level <= 10
union all
select 0, null from dual
union all
select 1000, null from dual
)
select rownum rn,
       level lvl,
       replace(sys_connect_by_path(rownum, '~'), '~') as path_rn,
       replace(sys_connect_by_path(level, '~'), '~') as path_lvl,
       sys_connect_by_path(id, '~') path_id
  from t
 where mod(level, 3) = 0
start with id_parent is null
connect by prior id = id_parent
order siblings by id desc;

        RN        LVL PATH_RN         PATH_LVL        PATH_ID
---------- ---------- --------------- --------------- ---------------------------------------------
         1          3 111             123             ~1000~1001~1002
         2          6 111222          123456          ~1000~1001~1002~1003~1004~1005
         3          9 111222333       123456789       ~1000~1001~1002~1003~1004~1005~1006~1007~1008
         4          3 444             123             ~0~1~2
         5          6 444555          123456          ~0~1~2~3~4~5
         6          9 444555666       123456789       ~0~1~2~3~4~5~6~7~8

6 rows selected.

SY.

...

Рейтинг:

0 / 0

16.03.2017, 22:27

| Ответить | Цитировать | Написать

sys_connect_by_path(rownum) с фильтрацией

#39421091

Участник

Откуда: Middlebury, CT USA

Сообщения: 10 588

Рейтинг: 0 / 0

И для сравнения ORDER SIBLINGS BY c ORDER BY в плане ROWNUM:

Код: 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.

with t as
(
select rownum id, rownum - 1 id_parent from dual connect by level <= 10
union all
select 1000 + rownum id, 1000 + rownum - 1 id_parent from dual connect by level <= 10
union all
select 0, null from dual
union all
select 1000, null from dual
)
select rownum rn,
       level lvl,
       id,
       replace(sys_connect_by_path(rownum, '~'), '~') as path_rn,
       replace(sys_connect_by_path(level, '~'), '~') as path_lvl,
       sys_connect_by_path(id, '~') path_id
  from t
 where mod(level, 3) = 0
start with id_parent is null
connect by prior id = id_parent
order by id desc;

        RN        LVL         ID PATH_RN         PATH_LVL        PATH_ID
---------- ---------- ---------- --------------- --------------- ---------------------------------------------
         6          9       1008 444555666       123456789       ~1000~1001~1002~1003~1004~1005~1006~1007~1008
         5          6       1005 444555          123456          ~1000~1001~1002~1003~1004~1005
         4          3       1002 444             123             ~1000~1001~1002
         3          9          8 111222333       123456789       ~0~1~2~3~4~5~6~7~8
         2          6          5 111222          123456          ~0~1~2~3~4~5
         1          3          2 111             123             ~0~1~2

6 rows selected.

SQL>

SY.

...

Рейтинг:

0 / 0

16.03.2017, 22:32

| Ответить | Цитировать | Написать

sys_connect_by_path(rownum) с фильтрацией

#39421100

dbms_photoshop

Участник

Откуда: sqlmdx.net

Сообщения: 5 140

Рейтинг: 0 / 0

SYdbms_photoshopНикакой сортировки по умолчанию не выполняется.
Выполняется построение дерева методом обхода в глубину.

Не путай DEPTH/BREADTH FIRST c ORDER SIBLINGS BYА почему ты решил, что я путаю и с каким из двух предложений ты не согласен?
SYИ для сравнения ORDER SIBLINGS BY c ORDER BY в плане ROWNUMЭто тоже для меня? :)
На основании чего сделан вывод, что мне это не очевидно?

PS. Лучше б ты Станиславу отвечал на его некорректную реплику.

...

Рейтинг:

0 / 0

16.03.2017, 23:35

| Ответить | Цитировать | Написать

sys_connect_by_path(rownum) с фильтрацией

#39421141

Elic

Участник

Сообщения: 30 360

Рейтинг: 0 / 0

dbms_photoshoprownum генерится попадании новой строки в результирующий recordset!0 лет прошло :)
20295979

...

Рейтинг:

0 / 0

17.03.2017, 07:19

| Ответить | Цитировать | Написать

sys_connect_by_path(rownum) с фильтрацией

#39421698

Участник

Откуда: Middlebury, CT USA

Сообщения: 10 588

Рейтинг: 0 / 0

dbms_photoshopЭто тоже для меня? :)
На основании чего сделан вывод, что мне это не очевидно?

PS. Лучше б ты Станиславу отвечал на его некорректную реплику.

Тебе, тебе:

stax..dbms_photoshopпропущено...
Что?
а когда по твоему ?

соответственно и ровнум вычисляется после "иерархичной" сортировки

.....
stax

"иерархичнaя" сортировкa это тот случай когда ROWNUM присваивается после сортиpовки. Так-что мой пост это ответ на твой "Что?".

SY.

...

Рейтинг:

0 / 0

17.03.2017, 16:09

| Ответить | Цитировать | Написать

sys_connect_by_path(rownum) с фильтрацией

#39421702

stax..

Участник

Сообщения: 6 197

Рейтинг: 0 / 0

dbms_photoshopstax..,

Никакой сортировки по умолчанию не выполняется.
Выполняется построение дерева методом обхода в глубину.

Более конкретным языком
http://docs.oracle.com/cd/B19306_01/server.102/b14200/queries003.htm Oracle returns the rows in the order shown in Figure 9-1. In the diagram, children appear below their parents. For an explanation of hierarchical trees, see Figure 3-1, "Hierarchical Tree".
А при использовании rownum на первый взягляд может показаться неожиданным, но по факту все довольно закономерно.

......

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

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

теперь насчет rownum, он присваивается в последнюю очередь (после Any remaining WHERE clause predicates are evaluated.)
если есть сортировка то условие НЕ сразу становится ложным.

kosourВ моих запросах практически не используются иерархические запросы, зато очень
болезнены вопросы фильтрации.

а от к Any remaining WHERE надо относится с уважением

казалось бы "одинаковые" запрсы (с тотожнимы планами выполнения)
select e.ename from emp e,dept d
where e.deptno=d.deptno and DNAME<>'ACCOUNTING'
и
select e.ename from emp e,(select * from dept d where DNAME<>'ACCOUNTING') d
where e.deptno=d.deptno

поведут себя по разному, если добавить connect by

.....
stax

...

Рейтинг:

0 / 0

17.03.2017, 16:12

| Ответить | Цитировать | Написать

sys_connect_by_path(rownum) с фильтрацией

#39423382

dbms_photoshop

Участник

Откуда: sqlmdx.net

Сообщения: 5 140

Рейтинг: 0 / 0

SY"иерархичнaя" сортировкa это тот случай когда ROWNUM присваивается после сортиpовки. Так-что мой пост это ответ на твой "Что?"Нет никакой "иерархичной" сортировки под капотом.
Да, order siblings влияет на порядок выдачи детей каждого конкретного родителя (яное дело это имеет смысл если детей более одного для конкретного родителя) но создание этого порядка - это не отдельная сортировка над полученным набором.
Просто в рекурсии вызывается что-то типа GetNextChild(), который может выдавать детей в разном порядке в зависимости от order siblings.
Stax..если siblings нет то конечно упорядочивать не надо, и пункт пропускаемКак сказавно выше в этом же сообщении,
Во-первых, siblings имеет смысл при наличии узлов с более чем одним потомком.
Во-вторых, никего не упорядочевается, если понимать под упорядочеванием сортировку набора данных.
Может изменяться порядок обхода (перебора дочерних записей).
Stax..теперь насчет rownum, он присваивается в последнюю очередь (после Any remaining WHERE clause predicates are evaluated.)Абсолютно очевидно, что rownum инкрементируется только если строка прошла все проверки на попадание в результат. Об этом написал выше и я и Элик.
Stax..если есть сортировка то условие НЕ сразу становится ложным.Это замечание относилось к конкретному примеру. Просьба еще раз пересмотреть его и если не согласен - привести контрпример.
Что-то додумывать или подразумевать чего я не говорил и потом оспаривать - не очень конструктивный подход.

Итого, если я правильно вас двоих понял, ваши утверждения, то выполняется обход дерева, выполняется сортировка, выполняется генерация rownum - это неверно.
Выполняется только рекурсивный код и инкремент определенных псевдостобцов в запивимости от наступления некоторых условий.
Это я написал в первом своем сообщении в этой теме.
Ошибочно рассматривать генерацию псевдостолбцов в отрыве от построения иерархии.

Stax..казалось бы "одинаковые" запрсы (с тотожнимы планами выполнения)Совсем плохой тон делать соединения и connect by на одном уровне. 10053 проясняет почему.

...

Рейтинг:

0 / 0

20.03.2017, 16:44

| Ответить | Цитировать | Написать

54 сообщений из 54, показаны все 3 страниц

все

Форумы / Oracle [игнор отключен] [закрыт для гостей] / sys_connect_by_path(rownum) с фильтрацией

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?all=1&fid=52&tid=1886258]:	0ms
get settings:	9ms
get forum list:	17ms
check forum access:	3ms
check topic access:	3ms
track hit:	180ms
get topic data:	10ms
get forum data:	2ms
get page messages:	90ms
get tp. blocked users:	1ms
others:	226ms

total:	541ms

	Необходимые cookie
	Cookie для сбора статистики
	Cookie для маркетинга и рекламы