Денормализация деревьев

[Surt]

А планы запросов можно увидеть?

[Webtoucher]

EXPLAIN PLAN FOR
SELECT *
  FROM object
    WHERE type = 'city'
    START WITH id = 1 CONNECT BY PRIOR id = parent_id;

---------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |        |     5 |   825 |     4  (25)| 00:00:01 |
|*  1 |  FILTER                                  |        |       |       |            |          |
|*  2 |   CONNECT BY NO FILTERING WITH START-WITH|        |       |       |            |          |
|   3 |    TABLE ACCESS FULL                     | OBJECT |     5 |   825 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

EXPLAIN PLAN FOR
SELECT *
  FROM object
    WHERE type = 'country'
    START WITH id = 5 CONNECT BY PRIOR parent_id = id;

--------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |           |     2 |   330 |     7  (29)| 00:00:01 |
|*  1 |  FILTER                        |           |       |       |            |          |
|*  2 |   CONNECT BY WITH FILTERING    |           |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID | OBJECT    |     1 |   165 |     2   (0)| 00:00:01 |
|*  4 |     INDEX UNIQUE SCAN          | PK_OBJECT |     1 |       |     1   (0)| 00:00:01 |
|   5 |    NESTED LOOPS                |           |     1 |   178 |     3   (0)| 00:00:01 |
|   6 |     CONNECT BY PUMP            |           |       |       |            |          |
|   7 |     TABLE ACCESS BY INDEX ROWID| OBJECT    |     1 |   165 |     1   (0)| 00:00:01 |
|*  8 |      INDEX UNIQUE SCAN         | PK_OBJECT |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

EXPLAIN PLAN FOR
SELECT *
  FROM object_fast
    WHERE parent_id = 1 AND object_type = 'city';

-------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                      |     2 |   660 |     2   (0)| 00:00:01 |
|*  1 |  MAT_VIEW ACCESS BY INDEX ROWID| OBJECT_FAST          |     2 |   660 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN             | OBJECT_FAST_OBJ_TYPE |     6 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
 
EXPLAIN PLAN FOR
SELECT *
  FROM object_fast
    WHERE object_id = 5 AND parent_type = 'country';

-----------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                    |     1 |   330 |     2   (0)| 00:00:01 |
|*  1 |  MAT_VIEW ACCESS BY INDEX ROWID| OBJECT_FAST        |     1 |   330 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN             | OBJECT_FAST_OBJ_ID |     3 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

[Webtoucher]

Но это для этого примера. Тут простое дерево. Обычно деревья более мудрёные… Таблицы нормализованные, данные разбиты на несколько таблиц… Поэтому и эффект такой оптимизации чувствуется ещё сильней.

[klvov]

Насколько я помню, то, что у вас называется object_fast_table, обычно называется таблицей транзитивных замыканий. В принципе, достаточно удобный способ работы с древовидными структурами в SQL. Если CONNECT BY PRIOR или аналоги в диалекте SQL не поддерживается, то без такой или подобной структуры трудно обойтись.

[xtender]

пакет-то зачем создавать?
Мвьюху можно было создать на основе простого запроса:

select 
   id
  ,type
  ,name
  ,parent_id
  ,prior type parent_type
  ,prior name parent_name
  ,level nesting_level
from object o
start with parent_id is null
connect by PRIOR id = parent_id;

[xtender]

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

[xtender]

Забавный минус :)
Удаляй пакет и делаю мвью просто по запросу:

         select 
            connect_by_root id   as object_id
           ,connect_by_root name as object_name
           ,connect_by_root type as object_type
           ,id                   as parent_id
           ,name                 as parent_name
           ,type                 as parent_type
           ,level-1              as nesting_level
         from object o
         connect by PRIOR parent_id = id
         order by id, nesting_level

[xtender]

Удаляй пакет и делаю делай мвью просто по запросу:

[Webtoucher]

Большое спасибо! Статья была переработана.