привет всем.

описываю ситуацию.
есть таблица юзеров 2.5 млн записей. есть связанная с ней таблица с иерархией. примерно соизмеримая.
у меня тут супер запрос на руках, который строит кучу деревьев за один проход.
используется часто, потому главные тормоза на нем.
работает (вот сейчас на моей настольной машине, а не на серваке) — 5 минут. что совершенно никого не устраивает.
на серваке потом будем оптимизировать другими способами, сейчас вопрос только про оптимизацию самого запроса.

самая главная засада в TABLE ACCESS FULL для построения дерева от рута. PARENT_ID=NULL
как гласит идеология оракла, построить индекс для null значений нельзя.
при глубине в 4 уровня у меня треть таблицы с PARENT_ID=NULL.

буду признателен за идею, или предложение по обходу/изоляции/решению вот именно этой точки.
заранее спасибо.

p.s. ответ "это сделать нельзя" — я знаю. меня интересует другой ответ.

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

"bastrakov" <14284@users.rsdn.ru> writes:

> как гласит идеология оракла, построить индекс для null значений нельзя.
> при глубине в 4 уровня у меня треть таблицы с PARENT_ID=NULL.

Может function based index? В качестве функции выбрать что-то, что позволит
превращать NULL в удобную вам величину. Только со сбором статистики по
этому индексу надо будет повнимательнее быть.

Здравствуйте, bastrakov, Вы писали:

B>p.s. ответ "это сделать нельзя" — я знаю. меня интересует другой ответ.

Когда-то решал аналогичную задачу. Игрался и с function based index, и просто тупо заменяя NULL на -1. Второй вариант мне понравился больше. Просто не знаю, подойдет ли он тебе в качестве решения. У меня несколько другое было — надо было строить дреововидный список документов, имеющих родителей в разных частях базы, но в конечном итоге завязанный на единый справочник клиентов.

Здравствуйте, LuciferArh, Вы писали:

B>>p.s. ответ "это сделать нельзя" — я знаю. меня интересует другой ответ.
LA>Когда-то решал аналогичную задачу. Игрался и с function based index, и просто тупо заменяя NULL на -1.

гм... 2 человека торкнули в одну и ту же сторону.
я про это видел топики, и попробовал построить индекс по этому делу.
оракл его проигнорировал. может я чего не догоняю, но как я могу это использовать в дереве?
там нет явного фильтра по parent_id=null. он всплывает в процессе построения запроса.

если не трудно, можно пример из бессмертной схемы scott/tiger. там табличка emp.
вот можно кинуть построчно в терминал (после логина ).

set autotrace traceonly explain                                                        
                                                                                       
set linesize 500                                                                       
set pagesize 500                                                                       
                                                                                       
SELECT level as lvl, e.*                                                               
from emp e                                                                             
START WITH e.EMPNO = 7902                                                              
CONNECT BY prior e.EMPNO = e.MGR;                                                      
                                                                                       
---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |    14 |   518 |     3   (0)| 00:00:01 |
|*  1 |  CONNECT BY WITH FILTERING   |        |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP    |       |       |            |          |
|*  3 |    INDEX UNIQUE SCAN         | PK_EMP |     1 |     4 |     0   (0)| 00:00:01 |
|*  4 |   HASH JOIN                  |        |       |       |            |          |
|   5 |    CONNECT BY PUMP           |        |       |       |            |          |
|   6 |    TABLE ACCESS FULL         | EMP    |    14 |   518 |     3   (0)| 00:00:01 |
|   7 |   TABLE ACCESS FULL          | EMP    |    14 |   518 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
                                                                                       
Predicate Information (identified by operation id):                                    
---------------------------------------------------                                    
                                                                                       
   1 - filter("E"."EMPNO"=7902)                                                        
   3 - access("E"."EMPNO"=7902)                                                        
   4 - access("E"."MGR"=NULL)

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

заранее спасибо, за разжевывание. во

Здравствуйте, bastrakov, Вы писали:

B>вопрос про последнюю строку. как видите, такого условия в самом запросе нет.
B>а в плане он всплывает, потому что в данных он есть.

Всплывает — потому что да, в данных есть. И от него больше ничто не строится. Это да, задал ты задачку... У меня было несколько проще. Хотя и бил себя по рукам за столь гнусную конструкцию. Потому что сделал в конце концов piple line function, которая мне и возвращала результат. Работала долговато, конечно, но всяко быстрее, чем fullscan таблиц из-за этих трижды проклятых NULL.

B>заранее спасибо, за разжевывание. во

Да ладно тебе... Все мы тут иногда...

З.Ы. Если время вечером останется, попробую поиграться. Здесь просто сервака под рукой нет...

овощь! верни пост!!!
только закончи его фразой "предлагаю...", и допиши там ченить, плиз.
пример понравился, почему потер пост — тоже понимаю. во

Здравствуйте, bastrakov, Вы писали:

B>оракл его проигнорировал. может я чего не догоняю, но как я могу это использовать в дереве?
B>там нет явного фильтра по parent_id=null. он всплывает в процессе построения запроса.

На всякий случай уточню: статистика собрана?

В случае, когда под условие выборки подходит треть записей в таблице вполне может быть, что full table access быстрей чем доступ по индексу.
У меня не раз было такое, что оракл упорно игнорировал индексы и когда я правдами и неправдами заставлял его их использовать — становилось хуже

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

Здравствуйте, bastrakov, Вы писали:

1. Если треть таблицы от рута, то выбирать такие записи по индексу обычно дороже, чем с помощью Full Table Scan.
2. Не очень понимаю, с чего в Вашем примере вылезает условие на is null и тем более не уверен, что любой индекс как-то ему поможет.
3. Тем не менее, если очень хочется, попробуйте сделать индекс на два поля (parent_id, id). Такой индекс будет хранить null-ы, позволит сделать range scan по parent_id is null и, возможно, даст выигрыш по сравнению с получением той же информации через FTS.
4. Часто используемый деревянный запрос редко когда устраивает по скорости. Если оно действительно важно, подумайте над созданием materialized view.

Здравствуйте, bastrakov, Вы писали:

B>овощь! верни пост!!!
B>только закончи его фразой "предлагаю...", и допиши там ченить, плиз.
B>пример понравился, почему потер пост — тоже понимаю. во

Извините, совсем запутался и перестал понимать суть дела. Если подойти со стороны Scott/Tiger и твоего запроса к этой схеме, то

SQL> edit
Wrote file afiedt.buf

  1  SELECT level as lvl, e.*
  2  from scott.emp e
  3  START WITH e.EMPNO = 7902
  4* CONNECT BY prior e.EMPNO = e.MGR
SQL> set autotrace on
SQL> /

       LVL      EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
         1       7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
         2       7369 SMITH      CLERK           7902 17-DEC-80        800                    20


Execution Plan
----------------------------------------------------------
Plan hash value: 1039577921

----------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |     2 |    74 |     2   (0)| 00:00:01 |
|*  1 |  CONNECT BY WITH FILTERING    |        |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID | EMP    |       |       |            |          |
|*  3 |    INDEX UNIQUE SCAN          | PK_EMP |     1 |     4 |     0   (0)| 00:00:01 |
|   4 |   NESTED LOOPS                |        |       |       |            |          |
|   5 |    BUFFER SORT                |        |       |       |            |          |
|   6 |     CONNECT BY PUMP           |        |       |       |            |          |
|   7 |    TABLE ACCESS BY INDEX ROWID| EMP    |     2 |    74 |     2   (0)| 00:00:01 |
|*  8 |     INDEX RANGE SCAN          | SI1    |     2 |       |     1   (0)| 00:00:01 |
|*  9 |   TABLE ACCESS FULL           | EMP    |     2 |    74 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   1 - filter("E"."EMPNO"=7902)
   3 - access("E"."EMPNO"=7902)
   8 - access("E"."MGR"=NULL)
   9 - access("E"."MGR"=NULL)

SI1 — индекс по полю EMP.MGR
В плане только одно TABLE ACCESS FULL (остальное по индексам). Про него известно вот что: Operation CONNECT BY WITH FILTERING

The execution plan generated on Oracle Database 10g is slightly different.
As can be seen, the operation CONNECT BY WITH FILTERING has a third child (operation 8).
In this case, it was not executed, however. The value in the column Starts for operation 8 confirms this.
Actually, the third child is executed only when the CONNECT BY operation uses temporary space.
When that happens, performance might degrade considerably.
This problem, which is fixed as of version 10.2.0.4, is known as bug 5065418.

---------------------------------------------------------------------
| Id  | Operation                     | Name      | Starts | A-Rows |
---------------------------------------------------------------------
|*  1 |  CONNECT BY WITH FILTERING    |           |      1 |     14 |
|*  2 |   TABLE ACCESS FULL           | EMP       |      1 |      1 |
|   3 |   NESTED LOOPS                |           |      4 |     13 |
|   4 |    BUFFER SORT                |           |      4 |     14 |
|   5 |     CONNECT BY PUMP           |           |      4 |     14 |
|   6 |    TABLE ACCESS BY INDEX ROWID| EMP       |     14 |     13 |
|*  7 |     INDEX RANGE SCAN          | EMP_MGR_I |     14 |     13 |
|   8 |   TABLE ACCESS FULL           | EMP       |      0 |      0 |
---------------------------------------------------------------------

Правильно ли я понял, что в твоем "боевом" запросе индексы все равно не подхватываются (безотносительно того, что null не индексируются)?

Здравствуйте, Softwarer, Вы писали:

S>3. Тем не менее, если очень хочется, попробуйте сделать индекс на два поля (parent_id, id). Такой индекс будет хранить null-ы, позволит сделать range scan по parent_id is null и, возможно, даст выигрыш по сравнению с получением той же информации через FTS.

спасибо за идею, завтра попробую. интересно.

S>4. Часто используемый деревянный запрос редко когда устраивает по скорости. Если оно действительно важно, подумайте над созданием materialized view.

эта идея уже прозвучала, но тут такое дело... запретили мне пока.
я на sql.ru уже рассказывал. сел в чисто java-вскую команду. и они упорно толкают проект базо-независимый.
последний раз я накидал пример (обработка части логики в базе), который увеличил скорость на пару (или больше) порядков — пробил. сейчас собираюсь вот эту м-вьюху сделать — может тоже продавлю.

ну вообщем попробую завтра. расскажу о результатах. всем спасибо. во

Теперь все более-менее понятно.
Никакого поиска "is null" или "=null" там нет. Это просто банальная ошибка в отображении плана со стороны oracle.
Вот что вскользь указывает Christian Antognini в своей книжке Troubleshooting Oracle Performance (раздел Operation CONNECT BY WITH FILTERING).

SELECT level, rpad('-',level-1,'-')||ename AS ename, prior ename AS manager
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr

Caution. This query is another situation where the views v$sql_plan and v$sql_plan_statistics_
all give wrong information. In this case, the wrongly displayed predicates are the following:

1 - access("MGR"=PRIOR NULL)
2 - filter("MGR" IS NULL)
6 - access("MGR"=PRIOR NULL)

Так образом надо удостовериться, что есть индекс по полю empno (для операции поиска "корня" иерархии) и индекс для поля mgr (для поиска каждой "подчиненной" строки).
На самом деле возможно лучше сделать второй индекс составным, первым полем которого будет mgr, плюс он также будет включать ведомыми все другие столбцы, которые выбирает весь запрос целиком. По идее это приведет к тому, что все нужные данные будут хранится в самом индексе, что в свою очередь сделает не нужной операцию доступа к таблице (TABLE ACCESS BY INDEX ROWID). Это возможно немного убыстрит выполнение запроса.
Ну и заодно проапргрейдится до версии 10.2.0.4 или выше, чтобы исключить возможность бага 5065418, уже упомянутого выше.

Здравствуйте, Овощ, Вы писали:

О>Теперь все более-менее понятно.
О>Никакого поиска "is null" или "=null" там нет. Это просто банальная ошибка в отображении плана со стороны oracle.

угу. спасибо. понял. ну вообщем — утренний тест, результаты.
в прошлый раз тестирование проводил на своем десктопе, который понятно не сервак. да и к продакшну я доступа не имею.
(кстати, у меня Version 10.2.0.1.0)
сегодня накатил экспорт с того теста.
2.5 млн юзеров, 1.8 млн иерархия.
и еще одна таблица, которая join-ится, в которой и лежат данные, которые надо взять. 7.5 млн.

составной индекс оракл так и не подхватил. идея мне понравилась, потом еще по ней полажу.
зато накатывание этой кучи на нулевый tablespace показало вместо 5 минут — 20 секунд.
так что у дба-я — есть куда улучшать, только за счет оптимизации файлов данных.

там я еще в нескольких точках шаманил. вообщем итоговые... 0.2 секунды всех устоили.

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

Здравствуйте, bastrakov, Вы писали:

B>привет всем.

B>описываю ситуацию.
B>есть таблица юзеров 2.5 млн записей. есть связанная с ней таблица с иерархией. примерно соизмеримая.
B>у меня тут супер запрос на руках, который строит кучу деревьев за один проход.
B>используется часто, потому главные тормоза на нем.
B>работает (вот сейчас на моей настольной машине, а не на серваке) — 5 минут. что совершенно никого не устраивает.
B>на серваке потом будем оптимизировать другими способами, сейчас вопрос только про оптимизацию самого запроса.

B>самая главная засада в TABLE ACCESS FULL для построения дерева от рута. PARENT_ID=NULL
B>как гласит идеология оракла, построить индекс для null значений нельзя.
B>при глубине в 4 уровня у меня треть таблицы с PARENT_ID=NULL.

С точки зрения Oracle если индекс такой не селективный (терть таблицы выбирается) то пользоваться им совершенно бессмысленно.
Самый главный вопрос: Вы статистику по базе собирали?


B>буду признателен за идею, или предложение по обходу/изоляции/решению вот именно этой точки.
B>заранее спасибо.

B>p.s. ответ "это сделать нельзя" — я знаю. меня интересует другой ответ.

B>p.p.s. безопастники закрыли не только sql.ru, но даже поиск по данному сайту.
B>если уже перетирали, дайте линк прямой, плиз. хоть из дома гляну. во

Здравствуйте, bastrakov, Вы писали:

вот :

create index i_parent_id on tab(1,parent_id);

Здравствуйте, bastrakov, Вы писали:

B>привет всем.

B>как гласит идеология оракла, построить индекс для null значений нельзя.

с чего это вдруг нельзя?