Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Помогите переделать запрос.  [new]
Ryuu
Member

Откуда:
Сообщений: 549
Собственно сам запрос вот.
WITH t1 AS
  (SELECT '1' AS f1, 'text' AS f2, '' AS f3 FROM dual
  UNION ALL
  SELECT '1' AS f1, 'text2' AS f2, '' AS f3 FROM dual
  UNION ALL
  SELECT '2' AS f1, 'text' AS f2, 'Z' AS f3 FROM dual
  UNION ALL
  SELECT '2' AS f1, 'text2' AS f2, 'Z' AS f3 FROM dual
  UNION ALL
  SELECT '3' AS f1, 'text' AS f2, 'Z' AS f3 FROM dual
  UNION ALL
  SELECT '3' AS f1, 'text2' AS f2, 'Z' AS f3 FROM dual
  )
SELECT f1,
  f2,
  f3,
  (
  CASE
    WHEN (SELECT COUNT(*) FROM t1 WHERE f3 IS NULL) = 1
    THEN 1
    ELSE 0
  END) AS f4
FROM t1 ;

Выполняет он то, что и должен. Однако, настоящий запрос несколько больше, поэтому конструкции -
(SELECT COUNT(*) FROM t1 WHERE f3 IS NULL) = 1
хотелось бы избежать, без потери производительности.

То есть, у меня есть результат запроса, мне нужно исходя из этого результата проставить флаг, есть ли повторяющиеся строчки с определенным признаком, в данном и в моем случае это null. При этом выводить их все равно нужно. Обычно я бы с этим не заморачивался, но запрос может выполняться несколько тысяч раз, поэтому увеличение его времени работы даже на одну секунду меня огорчит.
28 авг 14, 15:01    [16507385]     Ответить | Цитировать Сообщить модератору
 Re: Помогите переделать запрос.  [new]
user1241
Guest
заменить

(SELECT COUNT(*) FROM t1 WHERE f3 IS NULL)


на

sum(decode(f3,null,1,0)) over ()
28 авг 14, 15:20    [16507559]     Ответить | Цитировать Сообщить модератору
 Re: Помогите переделать запрос.  [new]
Ryuu
Member

Откуда:
Сообщений: 549
Спасибо user1241.
28 авг 14, 15:21    [16507576]     Ответить | Цитировать Сообщить модератору
 Re: Помогите переделать запрос.  [new]
Ryuu
Member

Откуда:
Сообщений: 549
Поставил не удачно условия задачи, сплю уже видимо, но спасибо назад не забираю. Может пригодится. В любом случае интересное решение. Моя же задача решается так...
WITH t1 AS
  (SELECT '111' AS f0, '1' AS f1, 'text' AS f2, '' AS f3 FROM dual
  UNION ALL
  SELECT '112' AS f0, '1' AS f1, 'text2' AS f2, '' AS f3 FROM dual
  UNION ALL
  SELECT '208' AS f0, '2' AS f1, 'text' AS f2, 'Z' AS f3 FROM dual
  UNION ALL
  SELECT '209' AS f0, '2' AS f1, 'text2' AS f2, 'Z' AS f3 FROM dual
  UNION ALL
  SELECT '210' AS f0, '3' AS f1, 'text' AS f2, 'Z' AS f3 FROM dual
  UNION ALL
  SELECT '211' AS f0, '3' AS f1, 'text2' AS f2, 'Z' AS f3 FROM dual
  )
SELECT f0,
  f1,
  f2,
  f3,
  (
  CASE
    WHEN MAX(f0) over () = MIN(f0) over ()
    THEN 1
    ELSE 0
  END) AS f4
FROM t1 
where f3 is null;

А что? Мало ли кому интересно. хД
28 авг 14, 16:00    [16507974]     Ответить | Цитировать Сообщить модератору
 Re: Помогите переделать запрос.  [new]
Elic
Member

Откуда:
Сообщений: 29990
Ryuu
MAX(f0) over () = MIN(f0) over ()
count(unique f0) over () = 1
28 авг 14, 16:27    [16508193]     Ответить | Цитировать Сообщить модератору
 Re: Помогите переделать запрос.  [new]
Ryuu
Member

Откуда:
Сообщений: 549
Elic, спасибо.
28 авг 14, 16:29    [16508206]     Ответить | Цитировать Сообщить модератору
 Re: Помогите переделать запрос.  [new]
user1241
Guest
Ryuu,

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


Я в таких случаях предпочитаю max = min, т.к. быстрее,

с unique ему приходится результат сортировать, даже когда уже есть индекс по полям и сортировка берется из индекса, будет максимум INDEX FULL SCAN
(когда берется сортировка из индекса INDEX FAST FULL SCAN устраняется, в той же ситуации с MAX = MIN из индекса почти всегда будет INDEX FAST FULL SCAN)
(это касается 11g release 2)

даже если добиться одинаковых планов выполнения, очевидно, count unique все равно будет медленнее выполняться:
+
Присоединен к:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options

SQL> create table test10
  2  as
  3  select cast( trunc(level) as varchar2(100)) f1
  4       , level f2
  5       , level f3
  6       , cast(level as char(10)) f4
  7  from dual
  8  connect by level < power(10,6)
  9  ;

Таблица создана.

SQL>
SQL> commit;

Фиксация обновлений завершена.

SQL>
SQL> begin
  2    -- на моей машине не хватает памяти выполнить за раз
  3    for rec in 1..5 loop
  4
  5      execute immediate '
  6      insert into test10
  7      select cast( trunc(level) as varchar2(100)) f1
  8           , level f2
  9           , level f3
 10           , cast(level as char(10)) f4
 11      from dual
 12  connect by level < power(10,6)'
 13  ;
 14      commit;
 15    end loop;
 16  end;
 17  /

Процедура PL/SQL успешно завершена.

SQL>
SQL> alter table test10 modify f1 not null;

Таблица изменена.

SQL>
SQL> create index ffff on test10(f1)
  2  ;

Индекс создан.

SQL>
SQL> set autotrace on
SQL> set timing on
SQL>
SQL> select sum(f1)
  2  from (
  3    select case when min(f1) over () = max(f1) over () then 1 else 0 end f1
  4    from (select f1 from test10 t order by f1)
  5  )
  6  ;

   SUM(F1)
----------
         0

Затрач.время: 00:00:22.18

План выполнения
----------------------------------------------------------
Plan hash value: 2001697707

-------------------------------------------------------------------------------

----------

| Id  | Operation                 | Name | Rows  | Bytes |TempSpc| Cost (%CPU)|
Time     |

-------------------------------------------------------------------------------

----------

|   0 | SELECT STATEMENT          |      |     1 |     3 |       | 72911   (1)|
00:14:35 |

|   1 |  SORT AGGREGATE           |      |     1 |     3 |       |            |
         |

|   2 |   VIEW                    |      |  5313K|    15M|       | 72911   (1)|
00:14:35 |

|   3 |    WINDOW BUFFER          |      |  5313K|   263M|       | 72911   (1)|
00:14:35 |

|   4 |     VIEW                  |      |  5313K|   263M|       | 72911   (1)|
00:14:35 |

|   5 |      SORT ORDER BY        |      |  5313K|   263M|   305M| 72911   (1)|
00:14:35 |

|   6 |       INDEX FAST FULL SCAN| FFFF |  5313K|   263M|       |  4174   (2)|
00:00:51 |

-------------------------------------------------------------------------------

----------


Note
-----
   - dynamic sampling used for this statement (level=2)


Статистика
----------------------------------------------------------
        252  recursive calls
          4  db block gets
      15093  consistent gets
      44735  physical reads
          0  redo size
        344  bytes sent via SQL*Net to client
        359  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          2  sorts (disk)
          1  rows processed

SQL>
SQL> select sum(f1)
  2  from (
  3    select case when count(distinct f1) over () = 1 then 1 else 0 end f1
  4    from (select f1 from test10 t order by f1)
  5  )
  6  ;

   SUM(F1)
----------
         0

Затрач.время: 00:00:47.14

План выполнения
----------------------------------------------------------
Plan hash value: 2001697707

-------------------------------------------------------------------------------

----------

| Id  | Operation                 | Name | Rows  | Bytes |TempSpc| Cost (%CPU)|
Time     |

-------------------------------------------------------------------------------

----------

|   0 | SELECT STATEMENT          |      |     1 |     3 |       | 72911   (1)|
00:14:35 |

|   1 |  SORT AGGREGATE           |      |     1 |     3 |       |            |
         |

|   2 |   VIEW                    |      |  5313K|    15M|       | 72911   (1)|
00:14:35 |

|   3 |    WINDOW BUFFER          |      |  5313K|   263M|       | 72911   (1)|
00:14:35 |

|   4 |     VIEW                  |      |  5313K|   263M|       | 72911   (1)|
00:14:35 |

|   5 |      SORT ORDER BY        |      |  5313K|   263M|   305M| 72911   (1)|
00:14:35 |

|   6 |       INDEX FAST FULL SCAN| FFFF |  5313K|   263M|       |  4174   (2)|
00:00:51 |

-------------------------------------------------------------------------------

----------


Note
-----
   - dynamic sampling used for this statement (level=2)


Статистика
----------------------------------------------------------
        329  recursive calls
          4  db block gets
      15084  consistent gets
      77089  physical reads
          0  redo size
        344  bytes sent via SQL*Net to client
        359  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          2  sorts (disk)
          1  rows processed

SQL>
SQL> drop table test10
  2  ;

Таблица удалена.
28 авг 14, 19:08    [16509155]     Ответить | Цитировать Сообщить модератору
 Re: Помогите переделать запрос.  [new]
ora601
Guest
user1241, Кстати из твоего примера нечень очевидно что max=min быстрее, количество физ чтений то разное.
29 авг 14, 01:27    [16510211]     Ответить | Цитировать Сообщить модератору
 Re: Помогите переделать запрос.  [new]
Ryuu
Member

Откуда:
Сообщений: 549
Провел тест в полевых условиях, так скажем. Мне самому больше понравилось unique, из-за читабельности. Но что можно увидеть, при выполнении моего запроса в pentaho 856 раз:
MAX MIN - 12 минут 28 секунд,
Unique - 12 минут 37 секунд.
Вероятно это связано с тем, что запрос выдает от 1 до 20 строк, причем наиболее частые случаи от 1 до 5.
Но, пожалуй я оставлю Unique, будет легче вспомнить, чего я хотел добиться, на тот случай, если придется править запрос. В любом случае довольно интересная информация. Спасибо.
29 авг 14, 09:02    [16510580]     Ответить | Цитировать Сообщить модератору
 Re: Помогите переделать запрос.  [new]
xtender
Member

Откуда: Мск
Сообщений: 5704
user1241
Я в таких случаях предпочитаю max = min, т.к. быстрее,
именно в таком случае лучше уж воспользоваться кешированием скалярных подзапросов:
+
SQL> -- изначально:
SQL> select sum(f1)
  2  from (
  3    select case when min(f1) over () = max(f1) over () then 1 else 0 end f1
  4    from (select f1 from test10 t order by f1)
  5  );

   SUM(F1)
----------
         0
Elapsed: 00:00:42.45
SQL>
SQL> select sum(f1)
  2  from (
  3    select case when count(distinct f1) over () = 1 then 1 else 0 end f1
  4    from (select f1 from test10 t order by f1)
  5  );

   SUM(F1)
----------
         0
Elapsed: 00:00:57.95
SQL> -- без лишней сортировки:
SQL> select sum(f1)
  2  from (
  3    select case when min(f1) over () = max(f1) over () then 1 else 0 end f1
  4    from test10 t
  5  );

   SUM(F1)
----------
         0
Elapsed: 00:00:24.21
SQL>
SQL> select sum(f1)
  2  from (
  3    select case when count(distinct f1) over () = 1 then 1 else 0 end f1
  4    from test10 t
  5  );

   SUM(F1)
----------
         0
Elapsed: 00:00:42.59
SQL> -- cо scalar subquery caching:
SQL> with v_cached as (select count(*) cnt from (select distinct t2.f1 from test10 t2 where f1 is not null) where rownum<=2 )
  2  select sum(f1)
  3  from (
  4    select decode((select cnt from v_cached),1,1,0) f1
  5    from test10
  6   )
  7  /

   SUM(F1)
----------
         0
Elapsed: 00:00:06.62
29 авг 14, 10:56    [16511192]     Ответить | Цитировать Сообщить модератору
 Re: Помогите переделать запрос.  [new]
xtender
Member

Откуда: Мск
Сообщений: 5704
Ryuu
Провел тест в полевых условиях,
лучше привести реальный запрос с планом со статистиками выполнения, т.к. то что верно для "упрощенного" варианта, к реальному запросу может не так удачно подойти
29 авг 14, 10:58    [16511204]     Ответить | Цитировать Сообщить модератору
 Re: Помогите переделать запрос.  [new]
Ryuu
Member

Откуда:
Сообщений: 549
xtender, я не знаю, как выложить план не скриншотом. К тому же, в данном запросе он ведет себя неадекватно, что на MIN MAX, что на Unique, особенно на Unique.
Сам запрос вот, я его все еще допиливаю, так что адекватные замечания буду рад выслушать.
SELECT pot.art_id,
  z_art.art_id             AS zagid,
  REPLACE(pot.kod_odi,'.') AS kod_odi,
  pot.kod_odi_zag,
  pzag.f_value                                                   AS kod_odi_m,
  REPLACE(trim(TO_CHAR(pot.norma, 'B9999990.000000')), '.', ',') AS norma,
  REPLACE(pot.ed_izm, '^', '')                                   AS ed_izm,
  pot.avtor,
  'T13'                                AS t13,
  TO_CHAR(pot.data_aktual, 'yyyyMMdd') AS data_aktual,
  (
  CASE
    WHEN z_art.pr_id = 6
    THEN 'V'
    ELSE ''
  END)           AS pr_id,
  '4'            AS section_id,
  pot.section_id AS real_section_id,
  (
  CASE
    WHEN z_art.purchased = '+'
    THEN 1
    ELSE 2
  END ) AS purchased,
  (
  CASE
    WHEN (trim(TO_CHAR(pot.massa, 'B9999990.000000')) IS NULL)
    OR (trim(TO_CHAR(pot.massa, 'B9999990.000000'))    = '')
    THEN '0,000000'
    ELSE (
      CASE
        WHEN mu.mu_id = '1'
        THEN REPLACE(trim(TO_CHAR(pot.massa*mu.k, 'B9999990.000000')), '.', ',')
        WHEN mu.mu_id = '3'
        THEN REPLACE(trim(TO_CHAR(pot.massa*mu.k, 'B9999990.000000')), '.', ',')
        WHEN mu.mu_id = '4'
        THEN REPLACE(trim(TO_CHAR(pot.massa*mu.k, 'B9999990.000000')), '.', ',')
        ELSE REPLACE(trim(TO_CHAR(pot.massa, 'B9999990.000000')), '.', ',')
      END)
  END) AS massa,
  (
  CASE
    WHEN (trim(TO_CHAR(pot.massa, 'B9999990.000000')) IS NULL)
    OR (trim(TO_CHAR(pot.massa, 'B9999990.000000'))    = '')
    THEN '0,000000'
    ELSE REPLACE(trim(TO_CHAR(pot.massa, 'B9999990.000000')), '.', ',')
  END) AS real_massa,
  pot.mu_id,
  mu.mu_short_name,
  (
  CASE
    WHEN sos.kod_odi_p = pot.kod_odi_zag
    THEN 1
    ELSE 0
  END) AS sovpalo,
  (
  CASE
    WHEN COUNT(UNIQUE pot.kod_odi_zag) over () = 1
    THEN 1
    ELSE 0
  END) AS fhsm
FROM
  ( SELECT DISTINCT tpz.f_key AS f_key,
    zas.art_id                AS art_id,
    zas.kod_odi               AS kod_odi,
    tpzdd.f_value             AS kod_odi_zag,
    tpzf.f6                   AS norma,
    tpzs.f4                   AS ed_izm,
    tpzd.f_value              AS avtor,
    tpz.f_data_aktual         AS data_aktual,
    art.section_id            AS section_id,
    art.massa                 AS massa,
    art.mu_id                 AS mu_id
  FROM zebra_all_sect zas,
    sysdba.articles art ,
    sysdba.tc_obj2link@imbase_dblink obj2link,
    sysdba.tp_zag@imbase_dblink tpz,
    sysdba.tp_zag_d@imbase_dblink tpzd,
    sysdba.tp_zag_d@imbase_dblink tpzdd,
    sysdba.tp_zag_f@imbase_dblink tpzf,
    sysdba.tp_zag_s@imbase_dblink tpzs
  WHERE zas.art_id        = art.art_id
  AND zas.art_id          = obj2link.f_art_id
  AND obj2link.f_obj_key  = tpz.f_key
  AND tpz.f_status        =0
  AND obj2link.f_obj_type = 3
  AND tpz.f_key           = tpzd.f_parentkey
  AND tpzd.f_entity       = '%ZUO'
  AND tpzdd.f_parentkey   = tpz.f_key
  AND tpzdd.f_entity      = 'Мкдо'
  AND tpzf.f_parentkey    =tpz.f_key
  AND tpzf.F_ROW          =1
  AND tpzs.f_parentkey    = tpz.f_key
  AND tpzs.f_row          =8
  AND zas.KOD_ODI        IS NOT NULL
  AND zas.art_id          = :art_id
  )pot
LEFT JOIN zebra_all_sect z_zas
ON z_zas.kod_odi_p = pot.kod_odi_zag
LEFT JOIN
  (SELECT art_id,
    MAX(art_ver_id),
    purchased,
    pr_id
  FROM sysdba.v_articles
  GROUP BY art_id,
    purchased,
    pr_id
  )z_art
ON z_art.art_id = z_zas.art_id
LEFT JOIN
  (SELECT art_id,
    kod_odi_p
  FROM sysdba.pc a,
    zebra_all_sect b
  WHERE a.part_aid              = b.art_id
  AND level                     = 1
  AND KOD_ODI_P                IS NOT NULL
    START WITH a.proj_aid       =:art_id
    CONNECT BY PRIOR a.part_aid = a.proj_aid
  ) sos
ON sos.art_id = z_art.art_id
LEFT JOIN sysdba.tp_zag_d@imbase_dblink tpzddd
ON tpzddd.F_PARENTKEY =pot.F_KEY
AND tpzddd.F_ROW      =0
AND tpzddd.F_ENTITY   ='ЗАМз'
LEFT JOIN
  (SELECT potnuii.f_art_id,
    tp_zz.f_value
  FROM
    ( SELECT DISTINCT tupozag.f_key,
      tobi.f_art_id
    FROM sysdba.tc_obj2link@imbase_dblink tobi,
      sysdba.tp_zag@imbase_dblink tupozag
    WHERE tobi.f_obj_key = tupozag.f_key
    AND tupozag.f_status =0
    AND tobi.f_obj_type  = 3
    AND tobi.f_art_id    = :art_id
    ) potnuii
  LEFT JOIN sysdba.tp_zag_d@imbase_dblink tp_z
  ON tp_z.F_PARENTKEY =potnuii.F_KEY
  AND tp_z.F_ROW      =0
  AND tp_z.F_ENTITY   ='ЗАМз'
  LEFT JOIN sysdba.tp_zag_d@imbase_dblink tp_zz
  ON tp_zz.F_PARENTKEY    =potnuii.F_KEY
  AND tp_zz.f_entity      = 'Мкдо'
  WHERE tp_z.F_value     IS NOT NULL
  ) pzag ON pzag.f_art_id = pot.art_id
JOIN sysdba.mu mu
ON mu.mu_id           = pot.mu_id
WHERE tpzddd.f_value IS NULL ;
29 авг 14, 13:04    [16512243]     Ответить | Цитировать Сообщить модератору
 Re: Помогите переделать запрос.  [new]
user1241
Guest
ora601
user1241, Кстати из твоего примера нечень очевидно что max=min быстрее, количество физ чтений то разное.


Ну почему же, тест повторяемый, distinct вторым идет и все равно медленнее,
к слову c count без distinct там же получили бы тот же план и теже 44к physical reads

http://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_eight.htm#i1037371
physical reads - Total number of data blocks read from disk. This number equals the value of "physical reads direct" plus all reads into buffer cache.


соответственно count distinct чаще использует буфферный кеш, отсюда разница в производительности.

xtender
именно в таком случае лучше уж воспользоваться кешированием скалярных подзапросов:

агрегатная функция быстрее чем аналитическая, оно понятно, фишка в том, что агрегатная функция с min = max в тех же случае будет еще быстрее чем с count distinct :)

+
SQL> select sum(f1)
  2  from (
  3    select decode(min(f1),max(f1),1,0) f1
  4    from (select f1 from test10 t)
  5  )
  6  ;

   SUM(F1)
----------
         0

Затрач.время: 00:00:04.06

SQL>
SQL> with v_cached as (select count(*) cnt from (select distinct t2.f1 from test
10 t2 where f1 is not null) where rownum<=2 )
  2  select sum(f1)
  3  from (
  4    select decode((select cnt from v_cached),1,1,0) f1
  5    from test10
  6   )
  7  ;

   SUM(F1)
----------
         0

Затрач.время: 00:00:06.78

SQL>
SQL> select sum(f1)
  2  from (
  3    select decode(count(distinct f1),1,1,0) f1
  4    from (select f1 from test10 t)
  5  )
  6  ;

   SUM(F1)
----------
         0

Затрач.время: 00:00:04.89

SQL> drop table test10;

Таблица удалена.



а так да, вариант с кешированием самый быстрый:
+
-- INDEX FULL SCAN (MIN/MAX) 
SQL> with t1
  2  as (select min(f1) q from test10)
  3  , t2 as (select max(f1) q from test10)
  4  select sum(f1)
  5  from (
  6    select decode(t1.q,t2.q,1,0) f1
  7    from t1, t2
  8  )
  9  ;

   SUM(F1)
----------
         0

 Затрач.время: 00:00:00.09

29 авг 14, 13:31    [16512439]     Ответить | Цитировать Сообщить модератору
 Re: Помогите переделать запрос.  [new]
xtender
Member

Откуда: Мск
Сообщений: 5704
user1241
SQL> select sum(f1)
  2  from (
  3    select decode(min(f1),max(f1),1,0) f1
  4    from (select f1 from test10 t)
  5  )
  6  ;

   SUM(F1)
----------
         0

user1241
-- INDEX FULL SCAN (MIN/MAX) 
SQL> with t1
  2  as (select min(f1) q from test10)
  3  , t2 as (select max(f1) q from test10)
  4  select sum(f1)
  5  from (
  6    select decode(t1.q,t2.q,1,0) f1
  7    from t1, t2
  8  )
  9  ;

   SUM(F1)
----------
         0

 Затрач.время: 00:00:00.09

чот ты кажется не понимаешь своих запросов... они совершенно неэквивалентны остальному с чем сравниваешь...

user1241
а так да, вариант с кешированием самый быстрый:
то, что ты показал, - это не кеширование
29 авг 14, 15:18    [16513346]     Ответить | Цитировать Сообщить модератору
 Re: Помогите переделать запрос.  [new]
user1241
Guest
xtender,

твой запрос с v_cached как-то я криво прочитал и некорректно вписал в тест, смотреть надо остальные 3 запроса
по ним смотрится какой вариант кешировать выгоднее,

можно сделать полный test case с кешированием, прогнать execute immediate с запросами 10к раз, сравнить с твоим вариантом, получить видимую разницу между min=max и остальными, но , имхо, и так все видно
29 авг 14, 15:42    [16513490]     Ответить | Цитировать Сообщить модератору
 Re: Помогите переделать запрос.  [new]
Ryuu
Member

Откуда:
Сообщений: 549
Я так понял, я мог и не выкладывать свой запрос... хД
29 авг 14, 16:08    [16513722]     Ответить | Цитировать Сообщить модератору
 Re: Помогите переделать запрос.  [new]
Ryuu
Member

Откуда:
Сообщений: 549
Текущая версия запроса.
+
SELECT pot.art_id,
  z_art.art_id             AS zagid,
  REPLACE(pot.kod_odi,'.') AS kod_odi,
  pot.kod_odi_zag,
  REPLACE(trim(TO_CHAR(pot.norma, 'B9999990.000000')), '.', ',') AS norma,
  REPLACE(pot.ed_izm, '^', '')                                   AS ed_izm,
  ''                                                             AS priznak,
  pot.avtor,
  'T13'                                AS t13,
  TO_CHAR(pot.data_aktual, 'yyyyMMdd') AS data_aktual,
  (
  CASE
    WHEN z_art.pr_id = 6
    THEN 'V'
    ELSE ''
  END)               AS pr_id,
  '0'                AS NL,
  'Основной маршрут' AS OM,
  '3'                AS section_id,
  pot.section_id     AS real_section_id,
  (
  CASE
    WHEN z_art.purchased = '+'
    THEN 1
    ELSE 2
  END )      AS purchased,
  '0,000000' AS massa,
  (
  CASE
    WHEN sos.kod_odi_p = pot.kod_odi_zag
    THEN 1
    ELSE 0
  END) AS sovpalo, -- совпадение кода материала в составе, с кодом заготовки на сборке
  (
  CASE
    WHEN pot.section_id = 3
    OR pot.section_id   = 5
    OR pot.section_id   = 6
    OR pot.section_id   = 8
    OR pot.section_id   = 99
    OR pot.section_id   = 467
    THEN 1
    ELSE 0
  END) AS fnsvns -- флаг наличия состава в не сборке
FROM
  ( SELECT DISTINCT tpz.f_key AS f_key,
    zas.art_id                AS art_id,
    zas.kod_odi               AS kod_odi,
    tpzdd.f_value             AS kod_odi_zag,
    tpzf.f6                   AS norma,
    tpzs.f4                   AS ed_izm,
    tpzd.f_value              AS avtor,
    tpz.f_data_aktual         AS data_aktual,
    art.section_id            AS section_id,
    art.massa                 AS massa,
    art.mu_id                 AS mu_id,
    zas.OTR_NORM              AS OTR_NORM,
    zas.OTR_KONSTR            AS OTR_KONSTR
  FROM zebra_all_sect zas,
    sysdba.articles art ,
    sysdba.tc_obj2link@imbase_dblink obj2link,
    sysdba.tp_zag@imbase_dblink tpz,
    sysdba.tp_zag_d@imbase_dblink tpzd,
    sysdba.tp_zag_d@imbase_dblink tpzdd,
    sysdba.tp_zag_f@imbase_dblink tpzf,
    sysdba.tp_zag_s@imbase_dblink tpzs
  WHERE zas.art_id        = art.art_id
  AND zas.art_id          = obj2link.f_art_id
  AND obj2link.f_obj_key  = tpz.f_key
  AND tpz.f_status        =0
  AND obj2link.f_obj_type = 3
  AND tpz.f_key           = tpzd.f_parentkey
  AND tpzd.f_entity       = '%ZUO'
  AND tpzdd.f_parentkey   = tpz.f_key
  AND tpzdd.f_entity      = 'Мкдо'
  AND tpzf.f_parentkey    =tpz.f_key
  AND tpzf.F_ROW          =1
  AND tpzs.f_parentkey    = tpz.f_key
  AND tpzs.f_row          =8
  AND zas.KOD_ODI        IS NOT NULL
  AND zas.art_id          = :art_id
  )pot
LEFT JOIN zebra_all_sect z_zas
ON z_zas.kod_odi_p = pot.kod_odi_zag
LEFT JOIN
  (SELECT art_id,
    MAX(art_ver_id),
    purchased,
    pr_id
  FROM sysdba.v_articles
  GROUP BY art_id,
    purchased,
    pr_id
  )z_art
ON z_art.art_id = z_zas.art_id
LEFT JOIN
  (SELECT art_id,
    kod_odi_p
  FROM sysdba.pc a,
    zebra_all_sect b
  WHERE a.part_aid              = b.art_id
  AND level                     = 1
  AND KOD_ODI_P                IS NOT NULL
    START WITH a.proj_aid       =:art_id
    CONNECT BY PRIOR a.part_aid = a.proj_aid
  ) sos
ON sos.art_id = z_art.art_id
LEFT JOIN sysdba.tp_zag_d@imbase_dblink tpzddd
ON tpzddd.F_PARENTKEY                               =pot.F_KEY
AND tpzddd.F_ROW                                    =0
AND tpzddd.F_ENTITY                                 ='ЗАМз'
WHERE tpzddd.f_value                               IS NULL
AND instr(pot.OTR_NORM, 'Отработано')               = 1
AND instr(pot.OTR_KONSTR, 'Выверено конструктором') = 1 ;

План запроса в навигаторе.
+
  SQL Statement from editor:
   
   
  ------------------------------------------------------------
    
  Statement Id=D7ADC9A4   Type=SELECT STATEMENT
    Cost=9853  TimeStamp=29-&#192;&#194;&#195;-14::04:21:19
  
       (1)  SELECT STATEMENT  ALL_ROWS 
     Est. Rows: 1  Cost: 9 853
       (153)  HASH JOIN OUTER 
     Est. Rows: 1  Cost: 9 853
           (95)  HASH JOIN OUTER 
                Est. Rows: 1  Cost: 8 308
               (91)  HASH JOIN OUTER 
                    Est. Rows: 1  Cost: 2 636
                   (67)  FILTER
                       (66)  NESTED LOOPS OUTER 
                            Est. Rows: 1  Cost: 1 098
                           (64)  VIEW (Embedded SQL) 
                                Est. Rows: 1  Cost: 1 095
                               (63)  HASH UNIQUE 
                                    Est. Rows: 1  Cost: 1 095
                                   (62)  HASH JOIN 
                                        Est. Rows: 1  Cost: 1 085
                                       (60)  NESTED LOOPS 
                                            Est. Rows: 21  Cost: 28
                                           (3)  TABLE TABLE ACCESS BY INDEX ROWID SYSDBA.ARTICLES  [Analyzed] 
                                           (3)   Blocks: 6 398 Est. Rows: 1 of 456 155  Cost: 3 
                                                Tablespace: USERS
                                               (2)  INDEX (UNIQUE) INDEX UNIQUE SCAN SYSDBA.ARTICLES_PRIM_NDX  [Analyzed] 
                                                    Est. Rows: 1  Cost: 2
                                           (59)  VIEW VIEW SRCHVIEW.ZEBRA_ALL_SECT 
                                                Est. Rows: 21  Cost: 25
                                               (58)  UNION-ALL
                                                   (6)  FILTER
                                                       (5)  TABLE TABLE ACCESS BY INDEX ROWID SYSDBA.SECT_0  [Analyzed] 
                                                       (5)   Est. Rows: 1 
                                                            Tablespace: SYSTEM
                                                           (4)  INDEX INDEX FULL SCAN SYSDBA.SECT_0_KOD_ODI_NDX  [Analyzed] 
                                                                Est. Rows: 1
                                                   (8)  TABLE TABLE ACCESS BY INDEX ROWID SYSDBA.SECT_1  [Analyzed] 
                                                   (8)   Blocks: 364 Est. Rows: 1 of 95 463  Cost: 2 
                                                        Tablespace: SYSTEM
                                                       (7)  INDEX (UNIQUE) INDEX UNIQUE SCAN SYSDBA.SECT_1_PRIMARY  [Analyzed] 
                                                            Est. Rows: 1  Cost: 1
                                                   (11)  FILTER
                                                       (10)  TABLE TABLE ACCESS BY INDEX ROWID SYSDBA.SECT_107  [Analyzed] 
                                                       (10)   Blocks: 226 Est. Rows: 1 of 17 679 
                                                            Tablespace: SYSTEM
                                                           (9)  INDEX INDEX FULL SCAN SYSDBA.SECT_107_KOD_ODI_NDX  [Analyzed] 
                                                                Est. Rows: 1
                                                   (14)  FILTER
                                                       (13)  TABLE TABLE ACCESS BY INDEX ROWID SYSDBA.SECT_131  [Analyzed] 
                                                       (13)   Blocks: 256 Est. Rows: 1 of 21 355  Cost: 2 
                                                            Tablespace: USERS
                                                           (12)  INDEX INDEX FULL SCAN SYSDBA.SECT_131_KOD_ODI_NDX  [Analyzed] 
                                                                Est. Rows: 1  Cost: 1
                                                   (17)  FILTER
                                                       (16)  TABLE TABLE ACCESS BY INDEX ROWID SYSDBA.SECT_148  [Analyzed] 
                                                       (16)   Blocks: 39 Est. Rows: 1 of 3 515  Cost: 2 
                                                            Tablespace: USERS
                                                           (15)  INDEX (UNIQUE) INDEX UNIQUE SCAN SYSDBA.SECT_148_PRIMARY  [Analyzed] 
                                                                Est. Rows: 1  Cost: 1
                                                   (20)  FILTER
                                                       (19)  TABLE TABLE ACCESS BY INDEX ROWID SYSDBA.SECT_191  [Analyzed] 
                                                       (19)   Blocks: 4 Est. Rows: 1 of 142  Cost: 1 
                                                            Tablespace: USERS
                                                           (18)  INDEX (UNIQUE) INDEX UNIQUE SCAN SYSDBA.SECT_191_PRIMARY  [Analyzed] 
                                                                Est. Rows: 1
                                                   (23)  FILTER
                                                       (22)  TABLE TABLE ACCESS BY INDEX ROWID SYSDBA.SECT_2  [Analyzed] 
                                                       (22)   Blocks: 2 Est. Rows: 1 of 1 
                                                            Tablespace: SYSTEM
                                                           (21)  INDEX INDEX FULL SCAN SYSDBA.SECT_2_KOD_ODI_NDX  [Analyzed] 
                                                                Est. Rows: 1
                                                   (25)  TABLE TABLE ACCESS BY INDEX ROWID SYSDBA.SECT_3  [Analyzed] 
                                                   (25)   Blocks: 1 068 Est. Rows: 1 of 26 928  Cost: 2 
                                                        Tablespace: SYSTEM
                                                       (24)  INDEX (UNIQUE) INDEX UNIQUE SCAN SYSDBA.SECT_3_PRIMARY  [Analyzed] 
                                                            Est. Rows: 1  Cost: 1
                                                   (27)  TABLE TABLE ACCESS BY INDEX ROWID SYSDBA.SECT_4  [Analyzed] 
                                                   (27)   Blocks: 2 905 Est. Rows: 1 of 62 703  Cost: 2 
                                                        Tablespace: SYSTEM
                                                       (26)  INDEX (UNIQUE) INDEX UNIQUE SCAN SYSDBA.SECT_4_PRIMARY  [Analyzed] 
                                                            Est. Rows: 1  Cost: 1
                                                   (29)  TABLE TABLE ACCESS BY INDEX ROWID SYSDBA.SECT_5  [Analyzed] 
                                                   (29)   Blocks: 656 Est. Rows: 1 of 18 195  Cost: 2 
                                                        Tablespace: SYSTEM
                                                       (28)  INDEX (UNIQUE) INDEX UNIQUE SCAN SYSDBA.SECT_5_PRIMARY  [Analyzed] 
                                                            Est. Rows: 1  Cost: 1
                                                   (31)  TABLE TABLE ACCESS BY INDEX ROWID SYSDBA.SECT_53  [Analyzed] 
                                                   (31)   Blocks: 8 Est. Rows: 1 of 73  Cost: 1 
                                                        Tablespace: USERS
                                                       (30)  INDEX (UNIQUE) INDEX UNIQUE SCAN SYSDBA.SECT_53_PRIMARY  [Analyzed] 
                                                            Est. Rows: 1
                                                   (34)  FILTER
                                                       (33)  TABLE TABLE ACCESS BY INDEX ROWID SYSDBA.SECT_55  [Analyzed] 
                                                       (33)   Blocks: 16 Est. Rows: 1 of 801 
                                                            Tablespace: USERS
                                                           (32)  INDEX INDEX FULL SCAN SYSDBA.SECT_55_KOD_ODI_NDX  [Analyzed] 
                                                                Est. Rows: 1
                                                   (37)  FILTER
                                                       (36)  TABLE TABLE ACCESS BY INDEX ROWID SYSDBA.SECT_56  [Analyzed] 
                                                       (36)   Blocks: 23 Est. Rows: 1 of 1 174 
                                                            Tablespace: USERS
                                                           (35)  INDEX INDEX FULL SCAN SYSDBA.SECT_56_KOD_ODI_NDX  [Analyzed] 
                                                                Est. Rows: 1
                                                   (40)  FILTER
                                                       (39)  TABLE TABLE ACCESS BY INDEX ROWID SYSDBA.SECT_57  [Analyzed] 
                                                       (39)   Blocks: 24 Est. Rows: 1 of 861 
                                                            Tablespace: USERS
                                                           (38)  INDEX INDEX FULL SCAN SYSDBA.SECT_57_KOD_ODI_NDX  [Analyzed] 
                                                                Est. Rows: 1
                                                   (42)  TABLE TABLE ACCESS BY INDEX ROWID SYSDBA.SECT_6  [Analyzed] 
                                                   (42)   Blocks: 480 Est. Rows: 1 of 38 492  Cost: 2 
                                                        Tablespace: SYSTEM
                                                       (41)  INDEX (UNIQUE) INDEX UNIQUE SCAN SYSDBA.SECT_6_PRIMARY  [Analyzed] 
                                                            Est. Rows: 1  Cost: 1
                                                   (45)  FILTER
                                                       (44)  TABLE TABLE ACCESS BY INDEX ROWID SYSDBA.SECT_63  [Analyzed] 
                                                       (44)   Blocks: 8 Est. Rows: 1 of 172  Cost: 1 
                                                            Tablespace: USERS
                                                           (43)  INDEX (UNIQUE) INDEX UNIQUE SCAN SYSDBA.SECT_63_PRIMARY  [Analyzed] 
                                                                Est. Rows: 1
                                                   (48)  FILTER
                                                       (47)  TABLE TABLE ACCESS BY INDEX ROWID SYSDBA.SECT_7  [Analyzed] 
                                                       (47)   Blocks: 611 Est. Rows: 1 of 45 631  Cost: 2 
                                                            Tablespace: SYSTEM
                                                           (46)  INDEX (UNIQUE) INDEX UNIQUE SCAN SYSDBA.SECT_7_PRIMARY  [Analyzed] 
                                                                Est. Rows: 1  Cost: 1
                                                   (50)  TABLE TABLE ACCESS BY INDEX ROWID SYSDBA.SECT_701  [Analyzed] 
                                                   (50)   Blocks: 96 Est. Rows: 1 of 1 329  Cost: 2 
                                                        Tablespace: USERS
                                                       (49)  INDEX (UNIQUE) INDEX UNIQUE SCAN SYSDBA.SECT_701_PRIMARY  [Analyzed] 
                                                            Est. Rows: 1  Cost: 1
                                                   (52)  TABLE TABLE ACCESS BY INDEX ROWID SYSDBA.SECT_8  [Analyzed] 
                                                   (52)   Blocks: 36 Est. Rows: 1 of 798  Cost: 2 
                                                        Tablespace: SYSTEM
                                                       (51)  INDEX (UNIQUE) INDEX UNIQUE SCAN SYSDBA.SECT_8_PRIMARY  [Analyzed] 
                                                            Est. Rows: 1  Cost: 1
                                                   (55)  FILTER
                                                       (54)  TABLE TABLE ACCESS BY INDEX ROWID SYSDBA.SECT_80  [Analyzed] 
                                                       (54)   Blocks: 2 Est. Rows: 1 of 65  Cost: 1 
                                                            Tablespace: SYSTEM
                                                           (53)  INDEX (UNIQUE) INDEX UNIQUE SCAN SYSDBA.SECT_80_PRIMARY  [Analyzed] 
                                                                Est. Rows: 1
                                                   (57)  TABLE TABLE ACCESS BY INDEX ROWID SYSDBA.SECT_99  [Analyzed] 
                                                   (57)   Blocks: 24 Est. Rows: 1 of 365  Cost: 1 
                                                        Tablespace: USERS
                                                       (56)  INDEX (UNIQUE) INDEX UNIQUE SCAN SYSDBA.SECT_99_PRIMARY  [Analyzed] 
                                                            Est. Rows: 1
                                       (61)  REMOTE REMOTE 
                                            Est. Rows: 2  Cost: 1 056
                           (65)  REMOTE REMOTE.TP_ZAG_D 
                                Est. Rows: 1  Cost: 3
                   (90)  VIEW VIEW SRCHVIEW.ZEBRA_ALL_SECT 
                        Est. Rows: 335 743  Cost: 1 536
                       (89)  UNION-ALL
                           (68)  TABLE TABLE ACCESS FULL SYSDBA.SECT_0  [Analyzed] 
                           (68)   Est. Rows: 1  Cost: 2 
                                Tablespace: SYSTEM
                           (69)  TABLE TABLE ACCESS FULL SYSDBA.SECT_1  [Analyzed] 
                           (69)   Blocks: 364 Est. Rows: 95 463 of 95 463  Cost: 84 
                                Tablespace: SYSTEM
                           (70)  TABLE TABLE ACCESS FULL SYSDBA.SECT_107  [Analyzed] 
                           (70)   Blocks: 226 Est. Rows: 17 679 of 17 679  Cost: 51 
                                Tablespace: SYSTEM
                           (71)  TABLE TABLE ACCESS FULL SYSDBA.SECT_131  [Analyzed] 
                           (71)   Blocks: 256 Est. Rows: 21 355 of 21 355  Cost: 57 
                                Tablespace: USERS
                           (72)  TABLE TABLE ACCESS FULL SYSDBA.SECT_148  [Analyzed] 
                           (72)   Blocks: 39 Est. Rows: 3 515 of 3 515  Cost: 10 
                                Tablespace: USERS
                           (73)  INDEX (UNIQUE) INDEX FULL SCAN SYSDBA.SECT_191_PRIMARY  [Analyzed] 
                                Est. Rows: 142  Cost: 1
                           (74)  TABLE TABLE ACCESS FULL SYSDBA.SECT_2  [Analyzed] 
                           (74)   Blocks: 2 Est. Rows: 1 of 1  Cost: 2 
                                Tablespace: SYSTEM
                           (75)  TABLE TABLE ACCESS FULL SYSDBA.SECT_3  [Analyzed] 
                           (75)   Blocks: 1 068 Est. Rows: 26 928 of 26 928  Cost: 236 
                                Tablespace: SYSTEM
                           (76)  TABLE TABLE ACCESS FULL SYSDBA.SECT_4  [Analyzed] 
                           (76)   Blocks: 2 905 Est. Rows: 62 703 of 62 703  Cost: 640 
                                Tablespace: SYSTEM
                           (77)  TABLE TABLE ACCESS FULL SYSDBA.SECT_5  [Analyzed] 
                           (77)   Blocks: 656 Est. Rows: 18 195 of 18 195  Cost: 146 
                                Tablespace: SYSTEM
                           (78)  TABLE TABLE ACCESS FULL SYSDBA.SECT_53  [Analyzed] 
                           (78)   Blocks: 8 Est. Rows: 73 of 73  Cost: 3 
                                Tablespace: USERS
                           (79)  TABLE TABLE ACCESS FULL SYSDBA.SECT_55  [Analyzed] 
                           (79)   Blocks: 16 Est. Rows: 801 of 801  Cost: 5 
                                Tablespace: USERS
                           (80)  TABLE TABLE ACCESS FULL SYSDBA.SECT_56  [Analyzed] 
                           (80)   Blocks: 23 Est. Rows: 1 174 of 1 174  Cost: 7 
                                Tablespace: USERS
                           (81)  TABLE TABLE ACCESS FULL SYSDBA.SECT_57  [Analyzed] 
                           (81)   Blocks: 24 Est. Rows: 861 of 861  Cost: 7 
                                Tablespace: USERS
                           (82)  TABLE TABLE ACCESS FULL SYSDBA.SECT_6  [Analyzed] 
                           (82)   Blocks: 480 Est. Rows: 38 492 of 38 492  Cost: 107 
                                Tablespace: SYSTEM
                           (83)  INDEX (UNIQUE) INDEX FULL SCAN SYSDBA.SECT_63_PRIMARY  [Analyzed] 
                                Est. Rows: 172  Cost: 1
                           (84)  TABLE TABLE ACCESS FULL SYSDBA.SECT_7  [Analyzed] 
                           (84)   Blocks: 611 Est. Rows: 45 631 of 45 631  Cost: 136 
                                Tablespace: SYSTEM
                           (85)  TABLE TABLE ACCESS FULL SYSDBA.SECT_701  [Analyzed] 
                           (85)   Blocks: 96 Est. Rows: 1 329 of 1 329  Cost: 22 
                                Tablespace: USERS
                           (86)  TABLE TABLE ACCESS FULL SYSDBA.SECT_8  [Analyzed] 
                           (86)   Blocks: 36 Est. Rows: 798 of 798  Cost: 10 
                                Tablespace: SYSTEM
                           (87)  INDEX (UNIQUE) INDEX FULL SCAN SYSDBA.SECT_80_PRIMARY  [Analyzed] 
                                Est. Rows: 65  Cost: 1
                           (88)  TABLE TABLE ACCESS FULL SYSDBA.SECT_99  [Analyzed] 
                           (88)   Blocks: 24 Est. Rows: 365 of 365  Cost: 7 
                                Tablespace: USERS
               (94)  VIEW (Embedded SQL) 
                    Est. Rows: 651 593  Cost: 5 669
                   (93)  HASH GROUP BY 
                        Est. Rows: 651 593  Cost: 5 669
                       (92)  TABLE TABLE ACCESS FULL SYSDBA.V_ARTICLES  [Analyzed] 
                       (92)   Blocks: 11 024 Est. Rows: 651 593 of 651 593  Cost: 2 437 
                            Tablespace: SYSTEM
           (152)  VIEW (Embedded SQL) 
                Est. Rows: 51  Cost: 1 544
               (151)  FILTER
                   (150)  CONNECT BY WITH FILTERING
                       (121)  HASH JOIN 
                            Est. Rows: 51  Cost: 1 544
                           (97)  TABLE TABLE ACCESS BY INDEX ROWID SYSDBA.PC  [Analyzed] 
                           (97)   Blocks: 2 302 Est. Rows: 17 of 298 393  Cost: 6 
                                Tablespace: USERS
                               (96)  INDEX INDEX RANGE SCAN SYSDBA.PC_PROJAID_INDEX  [Analyzed] 
                                    Est. Rows: 17  Cost: 3
                           (120)  VIEW VIEW SRCHVIEW.ZEBRA_ALL_SECT 
                                Est. Rows: 335 743  Cost: 1 536
                               (119)  UNION-ALL
                                   (98)  TABLE TABLE ACCESS FULL SYSDBA.SECT_0  [Analyzed] 
                                   (98)   Est. Rows: 1  Cost: 2 
                                        Tablespace: SYSTEM
                                   (99)  TABLE TABLE ACCESS FULL SYSDBA.SECT_1  [Analyzed] 
                                   (99)   Blocks: 364 Est. Rows: 95 463 of 95 463  Cost: 84 
                                        Tablespace: SYSTEM
                                   (100)  TABLE TABLE ACCESS FULL SYSDBA.SECT_107  [Analyzed] 
                                   (100)   Blocks: 226 Est. Rows: 17 679 of 17 679  Cost: 51 
                                        Tablespace: SYSTEM
                                   (101)  TABLE TABLE ACCESS FULL SYSDBA.SECT_131  [Analyzed] 
                                   (101)   Blocks: 256 Est. Rows: 21 355 of 21 355  Cost: 57 
                                        Tablespace: USERS
                                   (102)  TABLE TABLE ACCESS FULL SYSDBA.SECT_148  [Analyzed] 
                                   (102)   Blocks: 39 Est. Rows: 3 515 of 3 515  Cost: 10 
                                        Tablespace: USERS
                                   (103)  INDEX (UNIQUE) INDEX FULL SCAN SYSDBA.SECT_191_PRIMARY  [Analyzed] 
                                        Est. Rows: 142  Cost: 1
                                   (104)  TABLE TABLE ACCESS FULL SYSDBA.SECT_2  [Analyzed] 
                                   (104)   Blocks: 2 Est. Rows: 1 of 1  Cost: 2 
                                        Tablespace: SYSTEM
                                   (105)  TABLE TABLE ACCESS FULL SYSDBA.SECT_3  [Analyzed] 
                                   (105)   Blocks: 1 068 Est. Rows: 26 928 of 26 928  Cost: 236 
                                        Tablespace: SYSTEM
                                   (106)  TABLE TABLE ACCESS FULL SYSDBA.SECT_4  [Analyzed] 
                                   (106)   Blocks: 2 905 Est. Rows: 62 703 of 62 703  Cost: 640 
                                        Tablespace: SYSTEM
                                   (107)  TABLE TABLE ACCESS FULL SYSDBA.SECT_5  [Analyzed] 
                                   (107)   Blocks: 656 Est. Rows: 18 195 of 18 195  Cost: 146 
                                        Tablespace: SYSTEM
                                   (108)  TABLE TABLE ACCESS FULL SYSDBA.SECT_53  [Analyzed] 
                                   (108)   Blocks: 8 Est. Rows: 73 of 73  Cost: 3 
                                        Tablespace: USERS
                                   (109)  TABLE TABLE ACCESS FULL SYSDBA.SECT_55  [Analyzed] 
                                   (109)   Blocks: 16 Est. Rows: 801 of 801  Cost: 5 
                                        Tablespace: USERS
                                   (110)  TABLE TABLE ACCESS FULL SYSDBA.SECT_56  [Analyzed] 
                                   (110)   Blocks: 23 Est. Rows: 1 174 of 1 174  Cost: 7 
                                        Tablespace: USERS
                                   (111)  TABLE TABLE ACCESS FULL SYSDBA.SECT_57  [Analyzed] 
                                   (111)   Blocks: 24 Est. Rows: 861 of 861  Cost: 7 
                                        Tablespace: USERS
                                   (112)  TABLE TABLE ACCESS FULL SYSDBA.SECT_6  [Analyzed] 
                                   (112)   Blocks: 480 Est. Rows: 38 492 of 38 492  Cost: 107 
                                        Tablespace: SYSTEM
                                   (113)  INDEX (UNIQUE) INDEX FULL SCAN SYSDBA.SECT_63_PRIMARY  [Analyzed] 
                                        Est. Rows: 172  Cost: 1
                                   (114)  TABLE TABLE ACCESS FULL SYSDBA.SECT_7  [Analyzed] 
                                   (114)   Blocks: 611 Est. Rows: 45 631 of 45 631  Cost: 136 
                                        Tablespace: SYSTEM
                                   (115)  TABLE TABLE ACCESS FULL SYSDBA.SECT_701  [Analyzed] 
                                   (115)   Blocks: 96 Est. Rows: 1 329 of 1 329  Cost: 22 
                                        Tablespace: USERS
                                   (116)  TABLE TABLE ACCESS FULL SYSDBA.SECT_8  [Analyzed] 
                                   (116)   Blocks: 36 Est. Rows: 798 of 798  Cost: 10 
                                        Tablespace: SYSTEM
                                   (117)  INDEX (UNIQUE) INDEX FULL SCAN SYSDBA.SECT_80_PRIMARY  [Analyzed] 
                                        Est. Rows: 65  Cost: 1
                                   (118)  TABLE TABLE ACCESS FULL SYSDBA.SECT_99  [Analyzed] 
                                   (118)   Blocks: 24 Est. Rows: 365 of 365  Cost: 7 
                                        Tablespace: USERS
                       (149)  HASH JOIN 
                            Est. Rows: 51  Cost: 1 544
                           (125)  NESTED LOOPS
                               (122)  CONNECT BY PUMP
                               (124)  TABLE TABLE ACCESS BY INDEX ROWID SYSDBA.PC  [Analyzed] 
                               (124)   Blocks: 2 302 Est. Rows: 17 of 298 393  Cost: 6 
                                    Tablespace: USERS
                                   (123)  INDEX INDEX RANGE SCAN SYSDBA.PC_PROJAID_INDEX  [Analyzed] 
                                        Est. Rows: 17  Cost: 3
                           (148)  VIEW VIEW SRCHVIEW.ZEBRA_ALL_SECT 
                                Est. Rows: 335 743  Cost: 1 536
                               (147)  UNION-ALL
                                   (126)  TABLE TABLE ACCESS FULL SYSDBA.SECT_0  [Analyzed] 
                                   (126)   Est. Rows: 1  Cost: 2 
                                        Tablespace: SYSTEM
                                   (127)  TABLE TABLE ACCESS FULL SYSDBA.SECT_1  [Analyzed] 
                                   (127)   Blocks: 364 Est. Rows: 95 463 of 95 463  Cost: 84 
                                        Tablespace: SYSTEM
                                   (128)  TABLE TABLE ACCESS FULL SYSDBA.SECT_107  [Analyzed] 
                                   (128)   Blocks: 226 Est. Rows: 17 679 of 17 679  Cost: 51 
                                        Tablespace: SYSTEM
                                   (129)  TABLE TABLE ACCESS FULL SYSDBA.SECT_131  [Analyzed] 
                                   (129)   Blocks: 256 Est. Rows: 21 355 of 21 355  Cost: 57 
                                        Tablespace: USERS
                                   (130)  TABLE TABLE ACCESS FULL SYSDBA.SECT_148  [Analyzed] 
                                   (130)   Blocks: 39 Est. Rows: 3 515 of 3 515  Cost: 10 
                                        Tablespace: USERS
                                   (131)  INDEX (UNIQUE) INDEX FULL SCAN SYSDBA.SECT_191_PRIMARY  [Analyzed] 
                                        Est. Rows: 142  Cost: 1
                                   (132)  TABLE TABLE ACCESS FULL SYSDBA.SECT_2  [Analyzed] 
                                   (132)   Blocks: 2 Est. Rows: 1 of 1  Cost: 2 
                                        Tablespace: SYSTEM
                                   (133)  TABLE TABLE ACCESS FULL SYSDBA.SECT_3  [Analyzed] 
                                   (133)   Blocks: 1 068 Est. Rows: 26 928 of 26 928  Cost: 236 
                                        Tablespace: SYSTEM
                                   (134)  TABLE TABLE ACCESS FULL SYSDBA.SECT_4  [Analyzed] 
                                   (134)   Blocks: 2 905 Est. Rows: 62 703 of 62 703  Cost: 640 
                                        Tablespace: SYSTEM
                                   (135)  TABLE TABLE ACCESS FULL SYSDBA.SECT_5  [Analyzed] 
                                   (135)   Blocks: 656 Est. Rows: 18 195 of 18 195  Cost: 146 
                                        Tablespace: SYSTEM
                                   (136)  TABLE TABLE ACCESS FULL SYSDBA.SECT_53  [Analyzed] 
                                   (136)   Blocks: 8 Est. Rows: 73 of 73  Cost: 3 
                                        Tablespace: USERS
                                   (137)  TABLE TABLE ACCESS FULL SYSDBA.SECT_55  [Analyzed] 
                                   (137)   Blocks: 16 Est. Rows: 801 of 801  Cost: 5 
                                        Tablespace: USERS
                                   (138)  TABLE TABLE ACCESS FULL SYSDBA.SECT_56  [Analyzed] 
                                   (138)   Blocks: 23 Est. Rows: 1 174 of 1 174  Cost: 7 
                                        Tablespace: USERS
                                   (139)  TABLE TABLE ACCESS FULL SYSDBA.SECT_57  [Analyzed] 
                                   (139)   Blocks: 24 Est. Rows: 861 of 861  Cost: 7 
                                        Tablespace: USERS
                                   (140)  TABLE TABLE ACCESS FULL SYSDBA.SECT_6  [Analyzed] 
                                   (140)   Blocks: 480 Est. Rows: 38 492 of 38 492  Cost: 107 
                                        Tablespace: SYSTEM
                                   (141)  INDEX (UNIQUE) INDEX FULL SCAN SYSDBA.SECT_63_PRIMARY  [Analyzed] 
                                        Est. Rows: 172  Cost: 1
                                   (142)  TABLE TABLE ACCESS FULL SYSDBA.SECT_7  [Analyzed] 
                                   (142)   Blocks: 611 Est. Rows: 45 631 of 45 631  Cost: 136 
                                        Tablespace: SYSTEM
                                   (143)  TABLE TABLE ACCESS FULL SYSDBA.SECT_701  [Analyzed] 
                                   (143)   Blocks: 96 Est. Rows: 1 329 of 1 329  Cost: 22 
                                        Tablespace: USERS
                                   (144)  TABLE TABLE ACCESS FULL SYSDBA.SECT_8  [Analyzed] 
                                   (144)   Blocks: 36 Est. Rows: 798 of 798  Cost: 10 
                                        Tablespace: SYSTEM
                                   (145)  INDEX (UNIQUE) INDEX FULL SCAN SYSDBA.SECT_80_PRIMARY  [Analyzed] 
                                        Est. Rows: 65  Cost: 1
                                   (146)  TABLE TABLE ACCESS FULL SYSDBA.SECT_99  [Analyzed] 
                                   (146)   Blocks: 24 Est. Rows: 365 of 365  Cost: 7 
                                        Tablespace: USERS
29 авг 14, 16:25    [16513839]     Ответить | Цитировать Сообщить модератору
 Re: Помогите переделать запрос.  [new]
Ryuu
Member

Откуда:
Сообщений: 549
А, блин, не тот запрос выложил... То по изделиям, имеющим состав, сейчас выложу тот, что без... Х_Х
29 авг 14, 16:25    [16513844]     Ответить | Цитировать Сообщить модератору
 Re: Помогите переделать запрос.  [new]
Ryuu
Member

Откуда:
Сообщений: 549
Запрос...
+
SELECT pot.art_id,
  z_art.art_id             AS zagid,
  REPLACE(pot.kod_odi,'.') AS kod_odi,
  pot.kod_odi_zag,
  pzag.f_value                                                   AS kod_odi_m,
  REPLACE(trim(TO_CHAR(pot.norma, 'B9999990.000000')), '.', ',') AS norma,
  REPLACE(pot.ed_izm, '^', '')                                   AS ed_izm,
  pot.avtor,
  'T13'                                AS t13,
  TO_CHAR(pot.data_aktual, 'yyyyMMdd') AS data_aktual,
  (
  CASE
    WHEN z_art.pr_id = 6
    THEN 'V'
    ELSE ''
  END)           AS pr_id,
  '4'            AS section_id,
  pot.section_id AS real_section_id,
  (
  CASE
    WHEN z_art.purchased = '+'
    THEN 1
    ELSE 2
  END ) AS purchased,
  (
  CASE
    WHEN (trim(TO_CHAR(pot.massa, 'B9999990.000000')) IS NULL)
    OR (trim(TO_CHAR(pot.massa, 'B9999990.000000'))    = '')
    THEN '0,000000'
    ELSE (
      CASE
        WHEN mu.mu_id = '1'
        THEN REPLACE(trim(TO_CHAR(pot.massa*mu.k, 'B9999990.000000')), '.', ',')
        WHEN mu.mu_id = '3'
        THEN REPLACE(trim(TO_CHAR(pot.massa*mu.k, 'B9999990.000000')), '.', ',')
        WHEN mu.mu_id = '4'
        THEN REPLACE(trim(TO_CHAR(pot.massa*mu.k, 'B9999990.000000')), '.', ',')
        ELSE REPLACE(trim(TO_CHAR(pot.massa, 'B9999990.000000')), '.', ',')
      END)
  END) AS massa,
  (
  CASE
    WHEN (trim(TO_CHAR(pot.massa, 'B9999990.000000')) IS NULL)
    OR (trim(TO_CHAR(pot.massa, 'B9999990.000000'))    = '')
    THEN '0,000000'
    ELSE REPLACE(trim(TO_CHAR(pot.massa, 'B9999990.000000')), '.', ',')
  END) AS real_massa,
  pot.mu_id,
  mu.mu_short_name,
  (
  CASE
    WHEN sos.kod_odi_p = pot.kod_odi_zag
    THEN 1
    ELSE 0
  END) AS sovpalo, -- совпадение кода материала в составе, с кодом заготовки на сборке
  (
  CASE
    WHEN COUNT(UNIQUE pot.kod_odi_zag) over () = 1
    THEN 1
    ELSE 0
  END) AS fhsm --Флаг на уникальность. Основных заготов на деталь не может быть больше одной.
FROM
  ( SELECT DISTINCT tpz.f_key AS f_key,
    zas.art_id                AS art_id,
    zas.kod_odi               AS kod_odi,
    tpzdd.f_value             AS kod_odi_zag,
    tpzf.f6                   AS norma,
    tpzs.f4                   AS ed_izm,
    tpzd.f_value              AS avtor,
    tpz.f_data_aktual         AS data_aktual,
    art.section_id            AS section_id,
    art.massa                 AS massa,
    art.mu_id                 AS mu_id,
    zas.OTR_NORM              AS OTR_NORM,
    zas.OTR_KONSTR            AS OTR_KONSTR
  FROM zebra_all_sect zas,
    sysdba.articles art ,
    sysdba.tc_obj2link@imbase_dblink obj2link,
    sysdba.tp_zag@imbase_dblink tpz,
    sysdba.tp_zag_d@imbase_dblink tpzd,
    sysdba.tp_zag_d@imbase_dblink tpzdd,
    sysdba.tp_zag_f@imbase_dblink tpzf,
    sysdba.tp_zag_s@imbase_dblink tpzs
  WHERE zas.art_id        = art.art_id
  AND zas.art_id          = obj2link.f_art_id
  AND obj2link.f_obj_key  = tpz.f_key
  AND tpz.f_status        =0
  AND obj2link.f_obj_type = 3
  AND tpz.f_key           = tpzd.f_parentkey
  AND tpzd.f_entity       = '%ZUO'
  AND tpzdd.f_parentkey   = tpz.f_key
  AND tpzdd.f_entity      = 'Мкдо'
  AND tpzf.f_parentkey    =tpz.f_key
  AND tpzf.F_ROW          =1
  AND tpzs.f_parentkey    = tpz.f_key
  AND tpzs.f_row          =8
  AND zas.KOD_ODI        IS NOT NULL
  AND zas.art_id          = :art_id
  )pot
LEFT JOIN zebra_all_sect z_zas
ON z_zas.kod_odi_p = pot.kod_odi_zag
LEFT JOIN
  (SELECT art_id,
    MAX(art_ver_id),
    purchased,
    pr_id
  FROM sysdba.v_articles
  GROUP BY art_id,
    purchased,
    pr_id
  )z_art
ON z_art.art_id = z_zas.art_id
LEFT JOIN
  (SELECT art_id,
    kod_odi_p
  FROM sysdba.pc a,
    zebra_all_sect b
  WHERE a.part_aid              = b.art_id
  AND level                     = 1
  AND KOD_ODI_P                IS NOT NULL
    START WITH a.proj_aid       =:art_id
    CONNECT BY PRIOR a.part_aid = a.proj_aid
  ) sos
ON sos.art_id = z_art.art_id
LEFT JOIN sysdba.tp_zag_d@imbase_dblink tpzddd
ON tpzddd.F_PARENTKEY =pot.F_KEY
AND tpzddd.F_ROW      =0
AND tpzddd.F_ENTITY   ='ЗАМз'
LEFT JOIN
  (SELECT potnuii.f_art_id,
    tp_zz.f_value
  FROM
    ( SELECT DISTINCT tupozag.f_key,
      tobi.f_art_id
    FROM sysdba.tc_obj2link@imbase_dblink tobi,
      sysdba.tp_zag@imbase_dblink tupozag
    WHERE tobi.f_obj_key = tupozag.f_key
    AND tupozag.f_status =0
    AND tobi.f_obj_type  = 3
    AND tobi.f_art_id    = :art_id
    ) potnuii
  LEFT JOIN sysdba.tp_zag_d@imbase_dblink tp_z
  ON tp_z.F_PARENTKEY =potnuii.F_KEY
  AND tp_z.F_ROW      =0
  AND tp_z.F_ENTITY   ='ЗАМз'
  LEFT JOIN sysdba.tp_zag_d@imbase_dblink tp_zz
  ON tp_zz.F_PARENTKEY    =potnuii.F_KEY
  AND tp_zz.f_entity      = 'Мкдо'
  WHERE tp_z.F_value     IS NOT NULL
  ) pzag ON pzag.f_art_id = pot.art_id
JOIN sysdba.mu mu
ON mu.mu_id           = pot.mu_id
WHERE tpzddd.f_value IS NULL
AND instr(pot.OTR_NORM, 'Отработано')               = 1
AND instr(pot.OTR_KONSTR, 'Выверено конструктором') = 1 ;

План.
+
  SQL Statement from editor:
   
   
  SELECT pot.art_id,
    z_art.art_id             AS zagid,
    REPLACE(pot.kod_odi,'.') AS kod_odi,
    pot.kod_odi_zag,
    REPLACE(trim(TO_CHAR(pot.norma, 'B9999990.000000')), '.', ',') AS norma,
    REPLACE(pot.ed_izm, '^', '')                                   AS ed_izm,
    ''                                                             AS priznak,
    pot.avtor,
    'T13'                                AS t13,
    TO_CHAR(pot.data_aktual, 'yyyyMMdd') AS data_aktual,
    (
    CASE
      WHEN z_art.pr_id = 6
      THEN 'V'
      ELSE ''
    END)               AS pr_id,
    '0'                AS NL,
    '&#206;&#241;&#237;&#238;&#226;&#237;&#238;&#233; &#236;&#224;&#240;&#248;&#240;&#243;&#242;' AS OM,
    '3'                AS section_id,
    pot.section_id     AS real_section_id,
    (
    CASE
      WHEN z_art.purchased = '+'
      THEN 1
      ELSE 2
    END )      AS purchased,
    '0,000000' AS massa,
    (
    CASE
      WHEN sos.kod_odi_p = pot.kod_odi_zag
      THEN 1
      ELSE 0
    END) AS sovpalo, -- &#241;&#238;&#226;&#239;&#224;&#228;&#229;&#237;&#232;&#229; &#234;&#238;&#228;&#224; &#236;&#224;&#242;&#229;&#240;&#232;&#224;&#235;&#224; &#226; &#241;&#238;&#241;&#242;&#224;&#226;&#229;, &#241; &#234;&#238;&#228;&#238;&#236; &#231;&#224;&#227;&#238;&#242;&#238;&#226;&#234;&#232; &#237;&#224; &#241;&#225;&#238;&#240;&#234;&#229;
    (
    CASE
      WHEN pot.section_id = 3
      OR pot.section_id   = 5
      OR pot.section_id   = 6
      OR pot.section_id   = 8
      OR pot.section_id   = 99
      OR pot.section_id   = 467
      THEN 1
      ELSE 0
    END) AS fnsvns -- &#244;&#235;&#224;&#227; &#237;&#224;&#235;&#232;&#247;&#232;&#255; &#241;&#238;&#241;&#242;&#224;&#226;&#224; &#226; &#237;&#229; &#241;&#225;&#238;&#240;&#234;&#229;
  FROM
    ( SELECT DISTINCT tpz.f_key AS f_key,
      zas.art_id                AS art_id,
      zas.kod_odi               AS kod_odi,
      tpzdd.f_value             AS kod_odi_zag,
      tpzf.f6                   AS norma,
      tpzs.f4                   AS ed_izm,
      tpzd.f_value              AS avtor,
      tpz.f_data_aktual         AS data_aktual,
      art.section_id            AS section_id,
      art.massa                 AS massa,
      art.mu_id                 AS mu_id,
      zas.OTR_NORM              AS OTR_NORM,
      zas.OTR_KONSTR            AS OTR_KONSTR
    FROM zebra_all_sect zas,
      sysdba.articles art ,
      sysdba.tc_obj2link@imbase_dblink obj2link,
      sysdba.tp_zag@imbase_dblink tpz,
      sysdba.tp_zag_d@imbase_dblink tpzd,
      sysdba.tp_zag_d@imbase_dblink tpzdd,
      sysdba.tp_zag_f@imbase_dblink tpzf,
      sysdba.tp_zag_s@imbase_dblink tpzs
    WHERE zas.art_id        = art.art_id
    AND zas.art_id          = obj2link.f_art_id
    AND obj2link.f_obj_key  = tpz.f_key
    AND tpz.f_status        =0
    AND obj2link.f_obj_type = 3
    AND tpz.f_key           = tpzd.f_parentkey
    AND tpzd.f_entity       = '%ZUO'
    AND tpzdd.f_parentkey   = tpz.f_key
    AND tpzdd.f_entity      = '&#204;&#234;&#228;&#238;'
    AND tpzf.f_parentkey    =tpz.f_key
    AND tpzf.F_ROW          =1
    AND tpzs.f_parentkey    = tpz.f_key
    AND tpzs.f_row          =8
    AND zas.KOD_ODI        IS NOT NULL
    AND zas.art_id          = 436559
    )pot
  LEFT JOIN zebra_all_sect z_zas
  ON z_zas.kod_odi_p = pot.kod_odi_zag
  LEFT JOIN
    (SELECT art_id,
      MAX(art_ver_id),
      purchased,
      pr_id
    FROM sysdba.v_articles
    GROUP BY art_id,
      purchased,
      pr_id
    )z_art
  ON z_art.art_id = z_zas.art_id
  LEFT JOIN
    (SELECT art_id,
      kod_odi_p
    FROM sysdba.pc a,
      zebra_all_sect b
    WHERE a.part_aid              = b.art_id
    AND level                     = 1
    AND KOD_ODI_P                IS NOT NULL
      START WITH a.proj_aid       =436559
      CONNECT BY PRIOR a.part_aid = a.proj_aid
    ) sos
  ON sos.art_id = z_art.art_id
  LEFT JOIN sysdba.tp_zag_d@imbase_dblink tpzddd
  ON tpzddd.F_PARENTKEY                               =pot.F_KEY
  AND tpzddd.F_ROW                                    =0
  AND tpzddd.F_ENTITY                                 ='&#199;&#192;&#204;&#231;'
  WHERE tpzddd.f_value                               IS NULL
  AND instr(pot.OTR_NORM, '&#206;&#242;&#240;&#224;&#225;&#238;&#242;&#224;&#237;&#238;')               = 1
  AND instr(pot.OTR_KONSTR, '&#194;&#251;&#226;&#229;&#240;&#229;&#237;&#238; &#234;&#238;&#237;&#241;&#242;&#240;&#243;&#234;&#242;&#238;&#240;&#238;&#236;') = 1 ;
  ------------------------------------------------------------
    
  Statement Id=113   Type=TABLE ACCESS
  Cost=146  TimeStamp=29-08-14::16::28:19
  
       (1)  SELECT STATEMENT  ALL_ROWS 
     Est. Rows: 1  Cost: 9 853
       (153)  HASH JOIN OUTER 
     Est. Rows: 1  Cost: 9 853
           (95)  HASH JOIN OUTER 
                Est. Rows: 1  Cost: 8 308
               (91)  HASH JOIN OUTER 
                    Est. Rows: 1  Cost: 2 636
                   (67)  FILTER
                       (66)  NESTED LOOPS OUTER 
                            Est. Rows: 1  Cost: 1 098
                           (64)  VIEW (Embedded SQL) 
                                Est. Rows: 1  Cost: 1 095
                               (63)  HASH UNIQUE 
                                    Est. Rows: 1  Cost: 1 095
                                   (62)  HASH JOIN 
                                        Est. Rows: 1  Cost: 1 085
                                       (60)  NESTED LOOPS 
                                            Est. Rows: 21  Cost: 28
                                           (3)  TABLE TABLE ACCESS BY INDEX ROWID SYSDBA.ARTICLES  [Analyzed] 
                                           (3)   Blocks: 6 398 Est. Rows: 1 of 456 155  Cost: 3 
                                                Tablespace: USERS
                                               (2)  INDEX (UNIQUE) INDEX UNIQUE SCAN SYSDBA.ARTICLES_PRIM_NDX  [Analyzed] 
                                                    Est. Rows: 1  Cost: 2
                                           (59)  VIEW VIEW SRCHVIEW.ZEBRA_ALL_SECT 
                                                Est. Rows: 21  Cost: 25
                                               (58)  UNION-ALL
                                                   (6)  FILTER
                                                       (5)  TABLE TABLE ACCESS BY INDEX ROWID SYSDBA.SECT_0  [Analyzed] 
                                                       (5)   Est. Rows: 1 
                                                            Tablespace: SYSTEM
                                                           (4)  INDEX INDEX FULL SCAN SYSDBA.SECT_0_KOD_ODI_NDX  [Analyzed] 
                                                                Est. Rows: 1
                                                   (8)  TABLE TABLE ACCESS BY INDEX ROWID SYSDBA.SECT_1  [Analyzed] 
                                                   (8)   Blocks: 364 Est. Rows: 1 of 95 463  Cost: 2 
                                                        Tablespace: SYSTEM
                                                       (7)  INDEX (UNIQUE) INDEX UNIQUE SCAN SYSDBA.SECT_1_PRIMARY  [Analyzed] 
                                                            Est. Rows: 1  Cost: 1
                                                   (11)  FILTER
                                                       (10)  TABLE TABLE ACCESS BY INDEX ROWID SYSDBA.SECT_107  [Analyzed] 
                                                       (10)   Blocks: 226 Est. Rows: 1 of 17 679 
                                                            Tablespace: SYSTEM
                                                           (9)  INDEX INDEX FULL SCAN SYSDBA.SECT_107_KOD_ODI_NDX  [Analyzed] 
                                                                Est. Rows: 1
                                                   (14)  FILTER
                                                       (13)  TABLE TABLE ACCESS BY INDEX ROWID SYSDBA.SECT_131  [Analyzed] 
                                                       (13)   Blocks: 256 Est. Rows: 1 of 21 355  Cost: 2 
                                                            Tablespace: USERS
                                                           (12)  INDEX INDEX FULL SCAN SYSDBA.SECT_131_KOD_ODI_NDX  [Analyzed] 
                                                                Est. Rows: 1  Cost: 1
                                                   (17)  FILTER
                                                       (16)  TABLE TABLE ACCESS BY INDEX ROWID SYSDBA.SECT_148  [Analyzed] 
                                                       (16)   Blocks: 39 Est. Rows: 1 of 3 515  Cost: 2 
                                                            Tablespace: USERS
                                                           (15)  INDEX (UNIQUE) INDEX UNIQUE SCAN SYSDBA.SECT_148_PRIMARY  [Analyzed] 
                                                                Est. Rows: 1  Cost: 1
                                                   (20)  FILTER
                                                       (19)  TABLE TABLE ACCESS BY INDEX ROWID SYSDBA.SECT_191  [Analyzed] 
                                                       (19)   Blocks: 4 Est. Rows: 1 of 142  Cost: 1 
                                                            Tablespace: USERS
                                                           (18)  INDEX (UNIQUE) INDEX UNIQUE SCAN SYSDBA.SECT_191_PRIMARY  [Analyzed] 
                                                                Est. Rows: 1
                                                   (23)  FILTER
                                                       (22)  TABLE TABLE ACCESS BY INDEX ROWID SYSDBA.SECT_2  [Analyzed] 
                                                       (22)   Blocks: 2 Est. Rows: 1 of 1 
                                                            Tablespace: SYSTEM
                                                           (21)  INDEX INDEX FULL SCAN SYSDBA.SECT_2_KOD_ODI_NDX  [Analyzed] 
                                                                Est. Rows: 1
                                                   (25)  TABLE TABLE ACCESS BY INDEX ROWID SYSDBA.SECT_3  [Analyzed] 
                                                   (25)   Blocks: 1 068 Est. Rows: 1 of 26 928  Cost: 2 
                                                        Tablespace: SYSTEM
                                                       (24)  INDEX (UNIQUE) INDEX UNIQUE SCAN SYSDBA.SECT_3_PRIMARY  [Analyzed] 
                                                            Est. Rows: 1  Cost: 1
                                                   (27)  TABLE TABLE ACCESS BY INDEX ROWID SYSDBA.SECT_4  [Analyzed] 
                                                   (27)   Blocks: 2 905 Est. Rows: 1 of 62 703  Cost: 2 
                                                        Tablespace: SYSTEM
                                                       (26)  INDEX (UNIQUE) INDEX UNIQUE SCAN SYSDBA.SECT_4_PRIMARY  [Analyzed] 
                                                            Est. Rows: 1  Cost: 1
                                                   (29)  TABLE TABLE ACCESS BY INDEX ROWID SYSDBA.SECT_5  [Analyzed] 
                                                   (29)   Blocks: 656 Est. Rows: 1 of 18 195  Cost: 2 
                                                        Tablespace: SYSTEM
                                                       (28)  INDEX (UNIQUE) INDEX UNIQUE SCAN SYSDBA.SECT_5_PRIMARY  [Analyzed] 
                                                            Est. Rows: 1  Cost: 1
                                                   (31)  TABLE TABLE ACCESS BY INDEX ROWID SYSDBA.SECT_53  [Analyzed] 
                                                   (31)   Blocks: 8 Est. Rows: 1 of 73  Cost: 1 
                                                        Tablespace: USERS
                                                       (30)  INDEX (UNIQUE) INDEX UNIQUE SCAN SYSDBA.SECT_53_PRIMARY  [Analyzed] 
                                                            Est. Rows: 1
                                                   (34)  FILTER
                                                       (33)  TABLE TABLE ACCESS BY INDEX ROWID SYSDBA.SECT_55  [Analyzed] 
                                                       (33)   Blocks: 16 Est. Rows: 1 of 801 
                                                            Tablespace: USERS
                                                           (32)  INDEX INDEX FULL SCAN SYSDBA.SECT_55_KOD_ODI_NDX  [Analyzed] 
                                                                Est. Rows: 1
                                                   (37)  FILTER
                                                       (36)  TABLE TABLE ACCESS BY INDEX ROWID SYSDBA.SECT_56  [Analyzed] 
                                                       (36)   Blocks: 23 Est. Rows: 1 of 1 174 
                                                            Tablespace: USERS
                                                           (35)  INDEX INDEX FULL SCAN SYSDBA.SECT_56_KOD_ODI_NDX  [Analyzed] 
                                                                Est. Rows: 1
                                                   (40)  FILTER
                                                       (39)  TABLE TABLE ACCESS BY INDEX ROWID SYSDBA.SECT_57  [Analyzed] 
                                                       (39)   Blocks: 24 Est. Rows: 1 of 861 
                                                            Tablespace: USERS
                                                           (38)  INDEX INDEX FULL SCAN SYSDBA.SECT_57_KOD_ODI_NDX  [Analyzed] 
                                                                Est. Rows: 1
                                                   (42)  TABLE TABLE ACCESS BY INDEX ROWID SYSDBA.SECT_6  [Analyzed] 
                                                   (42)   Blocks: 480 Est. Rows: 1 of 38 492  Cost: 2 
                                                        Tablespace: SYSTEM
                                                       (41)  INDEX (UNIQUE) INDEX UNIQUE SCAN SYSDBA.SECT_6_PRIMARY  [Analyzed] 
                                                            Est. Rows: 1  Cost: 1
                                                   (45)  FILTER
                                                       (44)  TABLE TABLE ACCESS BY INDEX ROWID SYSDBA.SECT_63  [Analyzed] 
                                                       (44)   Blocks: 8 Est. Rows: 1 of 172  Cost: 1 
                                                            Tablespace: USERS
                                                           (43)  INDEX (UNIQUE) INDEX UNIQUE SCAN SYSDBA.SECT_63_PRIMARY  [Analyzed] 
                                                                Est. Rows: 1
                                                   (48)  FILTER
                                                       (47)  TABLE TABLE ACCESS BY INDEX ROWID SYSDBA.SECT_7  [Analyzed] 
                                                       (47)   Blocks: 611 Est. Rows: 1 of 45 631  Cost: 2 
                                                            Tablespace: SYSTEM
                                                           (46)  INDEX (UNIQUE) INDEX UNIQUE SCAN SYSDBA.SECT_7_PRIMARY  [Analyzed] 
                                                                Est. Rows: 1  Cost: 1
                                                   (50)  TABLE TABLE ACCESS BY INDEX ROWID SYSDBA.SECT_701  [Analyzed] 
                                                   (50)   Blocks: 96 Est. Rows: 1 of 1 329  Cost: 2 
                                                        Tablespace: USERS
                                                       (49)  INDEX (UNIQUE) INDEX UNIQUE SCAN SYSDBA.SECT_701_PRIMARY  [Analyzed] 
                                                            Est. Rows: 1  Cost: 1
                                                   (52)  TABLE TABLE ACCESS BY INDEX ROWID SYSDBA.SECT_8  [Analyzed] 
                                                   (52)   Blocks: 36 Est. Rows: 1 of 798  Cost: 2 
                                                        Tablespace: SYSTEM
                                                       (51)  INDEX (UNIQUE) INDEX UNIQUE SCAN SYSDBA.SECT_8_PRIMARY  [Analyzed] 
                                                            Est. Rows: 1  Cost: 1
                                                   (55)  FILTER
                                                       (54)  TABLE TABLE ACCESS BY INDEX ROWID SYSDBA.SECT_80  [Analyzed] 
                                                       (54)   Blocks: 2 Est. Rows: 1 of 65  Cost: 1 
                                                            Tablespace: SYSTEM
                                                           (53)  INDEX (UNIQUE) INDEX UNIQUE SCAN SYSDBA.SECT_80_PRIMARY  [Analyzed] 
                                                                Est. Rows: 1
                                                   (57)  TABLE TABLE ACCESS BY INDEX ROWID SYSDBA.SECT_99  [Analyzed] 
                                                   (57)   Blocks: 24 Est. Rows: 1 of 365  Cost: 1 
                                                        Tablespace: USERS
                                                       (56)  INDEX (UNIQUE) INDEX UNIQUE SCAN SYSDBA.SECT_99_PRIMARY  [Analyzed] 
                                                            Est. Rows: 1
                                       (61)  REMOTE REMOTE 
                                            Est. Rows: 2  Cost: 1 056
                           (65)  REMOTE REMOTE.TP_ZAG_D 
                                Est. Rows: 1  Cost: 3
                   (90)  VIEW VIEW SRCHVIEW.ZEBRA_ALL_SECT 
                        Est. Rows: 335 743  Cost: 1 536
                       (89)  UNION-ALL
                           (68)  TABLE TABLE ACCESS FULL SYSDBA.SECT_0  [Analyzed] 
                           (68)   Est. Rows: 1  Cost: 2 
                                Tablespace: SYSTEM
                           (69)  TABLE TABLE ACCESS FULL SYSDBA.SECT_1  [Analyzed] 
                           (69)   Blocks: 364 Est. Rows: 95 463 of 95 463  Cost: 84 
                                Tablespace: SYSTEM
                           (70)  TABLE TABLE ACCESS FULL SYSDBA.SECT_107  [Analyzed] 
                           (70)   Blocks: 226 Est. Rows: 17 679 of 17 679  Cost: 51 
                                Tablespace: SYSTEM
                           (71)  TABLE TABLE ACCESS FULL SYSDBA.SECT_131  [Analyzed] 
                           (71)   Blocks: 256 Est. Rows: 21 355 of 21 355  Cost: 57 
                                Tablespace: USERS
                           (72)  TABLE TABLE ACCESS FULL SYSDBA.SECT_148  [Analyzed] 
                           (72)   Blocks: 39 Est. Rows: 3 515 of 3 515  Cost: 10 
                                Tablespace: USERS
                           (73)  INDEX (UNIQUE) INDEX FULL SCAN SYSDBA.SECT_191_PRIMARY  [Analyzed] 
                                Est. Rows: 142  Cost: 1
                           (74)  TABLE TABLE ACCESS FULL SYSDBA.SECT_2  [Analyzed] 
                           (74)   Blocks: 2 Est. Rows: 1 of 1  Cost: 2 
                                Tablespace: SYSTEM
                           (75)  TABLE TABLE ACCESS FULL SYSDBA.SECT_3  [Analyzed] 
                           (75)   Blocks: 1 068 Est. Rows: 26 928 of 26 928  Cost: 236 
                                Tablespace: SYSTEM
                           (76)  TABLE TABLE ACCESS FULL SYSDBA.SECT_4  [Analyzed] 
                           (76)   Blocks: 2 905 Est. Rows: 62 703 of 62 703  Cost: 640 
                                Tablespace: SYSTEM
                           (77)  TABLE TABLE ACCESS FULL SYSDBA.SECT_5  [Analyzed] 
                           (77)   Blocks: 656 Est. Rows: 18 195 of 18 195  Cost: 146 
                                Tablespace: SYSTEM
                           (78)  TABLE TABLE ACCESS FULL SYSDBA.SECT_53  [Analyzed] 
                           (78)   Blocks: 8 Est. Rows: 73 of 73  Cost: 3 
                                Tablespace: USERS
                           (79)  TABLE TABLE ACCESS FULL SYSDBA.SECT_55  [Analyzed] 
                           (79)   Blocks: 16 Est. Rows: 801 of 801  Cost: 5 
                                Tablespace: USERS
                           (80)  TABLE TABLE ACCESS FULL SYSDBA.SECT_56  [Analyzed] 
                           (80)   Blocks: 23 Est. Rows: 1 174 of 1 174  Cost: 7 
                                Tablespace: USERS
                           (81)  TABLE TABLE ACCESS FULL SYSDBA.SECT_57  [Analyzed] 
                           (81)   Blocks: 24 Est. Rows: 861 of 861  Cost: 7 
                                Tablespace: USERS
                           (82)  TABLE TABLE ACCESS FULL SYSDBA.SECT_6  [Analyzed] 
                           (82)   Blocks: 480 Est. Rows: 38 492 of 38 492  Cost: 107 
                                Tablespace: SYSTEM
                           (83)  INDEX (UNIQUE) INDEX FULL SCAN SYSDBA.SECT_63_PRIMARY  [Analyzed] 
                                Est. Rows: 172  Cost: 1
                           (84)  TABLE TABLE ACCESS FULL SYSDBA.SECT_7  [Analyzed] 
                           (84)   Blocks: 611 Est. Rows: 45 631 of 45 631  Cost: 136 
                                Tablespace: SYSTEM
                           (85)  TABLE TABLE ACCESS FULL SYSDBA.SECT_701  [Analyzed] 
                           (85)   Blocks: 96 Est. Rows: 1 329 of 1 329  Cost: 22 
                                Tablespace: USERS
                           (86)  TABLE TABLE ACCESS FULL SYSDBA.SECT_8  [Analyzed] 
                           (86)   Blocks: 36 Est. Rows: 798 of 798  Cost: 10 
                                Tablespace: SYSTEM
                           (87)  INDEX (UNIQUE) INDEX FULL SCAN SYSDBA.SECT_80_PRIMARY  [Analyzed] 
                                Est. Rows: 65  Cost: 1
                           (88)  TABLE TABLE ACCESS FULL SYSDBA.SECT_99  [Analyzed] 
                           (88)   Blocks: 24 Est. Rows: 365 of 365  Cost: 7 
                                Tablespace: USERS
               (94)  VIEW (Embedded SQL) 
                    Est. Rows: 651 593  Cost: 5 669
                   (93)  HASH GROUP BY 
                        Est. Rows: 651 593  Cost: 5 669
                       (92)  TABLE TABLE ACCESS FULL SYSDBA.V_ARTICLES  [Analyzed] 
                       (92)   Blocks: 11 024 Est. Rows: 651 593 of 651 593  Cost: 2 437 
                            Tablespace: SYSTEM
           (152)  VIEW (Embedded SQL) 
                Est. Rows: 51  Cost: 1 544
               (151)  FILTER
                   (150)  CONNECT BY WITH FILTERING
                       (121)  HASH JOIN 
                            Est. Rows: 51  Cost: 1 544
                           (97)  TABLE TABLE ACCESS BY INDEX ROWID SYSDBA.PC  [Analyzed] 
                           (97)   Blocks: 2 302 Est. Rows: 17 of 298 393  Cost: 6 
                                Tablespace: USERS
                               (96)  INDEX INDEX RANGE SCAN SYSDBA.PC_PROJAID_INDEX  [Analyzed] 
                                    Est. Rows: 17  Cost: 3
                           (120)  VIEW VIEW SRCHVIEW.ZEBRA_ALL_SECT 
                                Est. Rows: 335 743  Cost: 1 536
                               (119)  UNION-ALL
                                   (98)  TABLE TABLE ACCESS FULL SYSDBA.SECT_0  [Analyzed] 
                                   (98)   Est. Rows: 1  Cost: 2 
                                        Tablespace: SYSTEM
                                   (99)  TABLE TABLE ACCESS FULL SYSDBA.SECT_1  [Analyzed] 
                                   (99)   Blocks: 364 Est. Rows: 95 463 of 95 463  Cost: 84 
                                        Tablespace: SYSTEM
                                   (100)  TABLE TABLE ACCESS FULL SYSDBA.SECT_107  [Analyzed] 
                                   (100)   Blocks: 226 Est. Rows: 17 679 of 17 679  Cost: 51 
                                        Tablespace: SYSTEM
                                   (101)  TABLE TABLE ACCESS FULL SYSDBA.SECT_131  [Analyzed] 
                                   (101)   Blocks: 256 Est. Rows: 21 355 of 21 355  Cost: 57 
                                        Tablespace: USERS
                                   (102)  TABLE TABLE ACCESS FULL SYSDBA.SECT_148  [Analyzed] 
                                   (102)   Blocks: 39 Est. Rows: 3 515 of 3 515  Cost: 10 
                                        Tablespace: USERS
                                   (103)  INDEX (UNIQUE) INDEX FULL SCAN SYSDBA.SECT_191_PRIMARY  [Analyzed] 
                                        Est. Rows: 142  Cost: 1
                                   (104)  TABLE TABLE ACCESS FULL SYSDBA.SECT_2  [Analyzed] 
                                   (104)   Blocks: 2 Est. Rows: 1 of 1  Cost: 2 
                                        Tablespace: SYSTEM
                                   (105)  TABLE TABLE ACCESS FULL SYSDBA.SECT_3  [Analyzed] 
                                   (105)   Blocks: 1 068 Est. Rows: 26 928 of 26 928  Cost: 236 
                                        Tablespace: SYSTEM
                                   (106)  TABLE TABLE ACCESS FULL SYSDBA.SECT_4  [Analyzed] 
                                   (106)   Blocks: 2 905 Est. Rows: 62 703 of 62 703  Cost: 640 
                                        Tablespace: SYSTEM
                                   (107)  TABLE TABLE ACCESS FULL SYSDBA.SECT_5  [Analyzed] 
                                   (107)   Blocks: 656 Est. Rows: 18 195 of 18 195  Cost: 146 
                                        Tablespace: SYSTEM
                                   (108)  TABLE TABLE ACCESS FULL SYSDBA.SECT_53  [Analyzed] 
                                   (108)   Blocks: 8 Est. Rows: 73 of 73  Cost: 3 
                                        Tablespace: USERS
                                   (109)  TABLE TABLE ACCESS FULL SYSDBA.SECT_55  [Analyzed] 
                                   (109)   Blocks: 16 Est. Rows: 801 of 801  Cost: 5 
                                        Tablespace: USERS
                                   (110)  TABLE TABLE ACCESS FULL SYSDBA.SECT_56  [Analyzed] 
                                   (110)   Blocks: 23 Est. Rows: 1 174 of 1 174  Cost: 7 
                                        Tablespace: USERS
                                   (111)  TABLE TABLE ACCESS FULL SYSDBA.SECT_57  [Analyzed] 
                                   (111)   Blocks: 24 Est. Rows: 861 of 861  Cost: 7 
                                        Tablespace: USERS
                                   (112)  TABLE TABLE ACCESS FULL SYSDBA.SECT_6  [Analyzed] 
                                   (112)   Blocks: 480 Est. Rows: 38 492 of 38 492  Cost: 107 
                                        Tablespace: SYSTEM
                                   (113)  INDEX (UNIQUE) INDEX FULL SCAN SYSDBA.SECT_63_PRIMARY  [Analyzed] 
                                        Est. Rows: 172  Cost: 1
                                   (114)  TABLE TABLE ACCESS FULL SYSDBA.SECT_7  [Analyzed] 
                                   (114)   Blocks: 611 Est. Rows: 45 631 of 45 631  Cost: 136 
                                        Tablespace: SYSTEM
                                   (115)  TABLE TABLE ACCESS FULL SYSDBA.SECT_701  [Analyzed] 
                                   (115)   Blocks: 96 Est. Rows: 1 329 of 1 329  Cost: 22 
                                        Tablespace: USERS
                                   (116)  TABLE TABLE ACCESS FULL SYSDBA.SECT_8  [Analyzed] 
                                   (116)   Blocks: 36 Est. Rows: 798 of 798  Cost: 10 
                                        Tablespace: SYSTEM
                                   (117)  INDEX (UNIQUE) INDEX FULL SCAN SYSDBA.SECT_80_PRIMARY  [Analyzed] 
                                        Est. Rows: 65  Cost: 1
                                   (118)  TABLE TABLE ACCESS FULL SYSDBA.SECT_99  [Analyzed] 
                                   (118)   Blocks: 24 Est. Rows: 365 of 365  Cost: 7 
                                        Tablespace: USERS
                       (149)  HASH JOIN 
                            Est. Rows: 51  Cost: 1 544
                           (125)  NESTED LOOPS
                               (122)  CONNECT BY PUMP
                               (124)  TABLE TABLE ACCESS BY INDEX ROWID SYSDBA.PC  [Analyzed] 
                               (124)   Blocks: 2 302 Est. Rows: 17 of 298 393  Cost: 6 
                                    Tablespace: USERS
                                   (123)  INDEX INDEX RANGE SCAN SYSDBA.PC_PROJAID_INDEX  [Analyzed] 
                                        Est. Rows: 17  Cost: 3
                           (148)  VIEW VIEW SRCHVIEW.ZEBRA_ALL_SECT 
                                Est. Rows: 335 743  Cost: 1 536
                               (147)  UNION-ALL
                                   (126)  TABLE TABLE ACCESS FULL SYSDBA.SECT_0  [Analyzed] 
                                   (126)   Est. Rows: 1  Cost: 2 
                                        Tablespace: SYSTEM
                                   (127)  TABLE TABLE ACCESS FULL SYSDBA.SECT_1  [Analyzed] 
                                   (127)   Blocks: 364 Est. Rows: 95 463 of 95 463  Cost: 84 
                                        Tablespace: SYSTEM
                                   (128)  TABLE TABLE ACCESS FULL SYSDBA.SECT_107  [Analyzed] 
                                   (128)   Blocks: 226 Est. Rows: 17 679 of 17 679  Cost: 51 
                                        Tablespace: SYSTEM
                                   (129)  TABLE TABLE ACCESS FULL SYSDBA.SECT_131  [Analyzed] 
                                   (129)   Blocks: 256 Est. Rows: 21 355 of 21 355  Cost: 57 
                                        Tablespace: USERS
                                   (130)  TABLE TABLE ACCESS FULL SYSDBA.SECT_148  [Analyzed] 
                                   (130)   Blocks: 39 Est. Rows: 3 515 of 3 515  Cost: 10 
                                        Tablespace: USERS
                                   (131)  INDEX (UNIQUE) INDEX FULL SCAN SYSDBA.SECT_191_PRIMARY  [Analyzed] 
                                        Est. Rows: 142  Cost: 1
                                   (132)  TABLE TABLE ACCESS FULL SYSDBA.SECT_2  [Analyzed] 
                                   (132)   Blocks: 2 Est. Rows: 1 of 1  Cost: 2 
                                        Tablespace: SYSTEM
                                   (133)  TABLE TABLE ACCESS FULL SYSDBA.SECT_3  [Analyzed] 
                                   (133)   Blocks: 1 068 Est. Rows: 26 928 of 26 928  Cost: 236 
                                        Tablespace: SYSTEM
                                   (134)  TABLE TABLE ACCESS FULL SYSDBA.SECT_4  [Analyzed] 
                                   (134)   Blocks: 2 905 Est. Rows: 62 703 of 62 703  Cost: 640 
                                        Tablespace: SYSTEM
                                   (135)  TABLE TABLE ACCESS FULL SYSDBA.SECT_5  [Analyzed] 
                                   (135)   Blocks: 656 Est. Rows: 18 195 of 18 195  Cost: 146 
                                        Tablespace: SYSTEM
                                   (136)  TABLE TABLE ACCESS FULL SYSDBA.SECT_53  [Analyzed] 
                                   (136)   Blocks: 8 Est. Rows: 73 of 73  Cost: 3 
                                        Tablespace: USERS
                                   (137)  TABLE TABLE ACCESS FULL SYSDBA.SECT_55  [Analyzed] 
                                   (137)   Blocks: 16 Est. Rows: 801 of 801  Cost: 5 
                                        Tablespace: USERS
                                   (138)  TABLE TABLE ACCESS FULL SYSDBA.SECT_56  [Analyzed] 
                                   (138)   Blocks: 23 Est. Rows: 1 174 of 1 174  Cost: 7 
                                        Tablespace: USERS
                                   (139)  TABLE TABLE ACCESS FULL SYSDBA.SECT_57  [Analyzed] 
                                   (139)   Blocks: 24 Est. Rows: 861 of 861  Cost: 7 
                                        Tablespace: USERS
                                   (140)  TABLE TABLE ACCESS FULL SYSDBA.SECT_6  [Analyzed] 
                                   (140)   Blocks: 480 Est. Rows: 38 492 of 38 492  Cost: 107 
                                        Tablespace: SYSTEM
                                   (141)  INDEX (UNIQUE) INDEX FULL SCAN SYSDBA.SECT_63_PRIMARY  [Analyzed] 
                                        Est. Rows: 172  Cost: 1
                                   (142)  TABLE TABLE ACCESS FULL SYSDBA.SECT_7  [Analyzed] 
                                   (142)   Blocks: 611 Est. Rows: 45 631 of 45 631  Cost: 136 
                                        Tablespace: SYSTEM
                                   (143)  TABLE TABLE ACCESS FULL SYSDBA.SECT_701  [Analyzed] 
                                   (143)   Blocks: 96 Est. Rows: 1 329 of 1 329  Cost: 22 
                                        Tablespace: USERS
                                   (144)  TABLE TABLE ACCESS FULL SYSDBA.SECT_8  [Analyzed] 
                                   (144)   Blocks: 36 Est. Rows: 798 of 798  Cost: 10 
                                        Tablespace: SYSTEM
                                   (145)  INDEX (UNIQUE) INDEX FULL SCAN SYSDBA.SECT_80_PRIMARY  [Analyzed] 
                                        Est. Rows: 65  Cost: 1
                                   (146)  TABLE TABLE ACCESS FULL SYSDBA.SECT_99  [Analyzed] 
                                   (146)   Blocks: 24 Est. Rows: 365 of 365  Cost: 7 
                                        Tablespace: USERS
29 авг 14, 16:29    [16513877]     Ответить | Цитировать Сообщить модератору
 Re: Помогите переделать запрос.  [new]
Ryuu
Member

Откуда:
Сообщений: 549
Развлекайтесь...
29 авг 14, 16:31    [16513894]     Ответить | Цитировать Сообщить модератору
 Re: Помогите переделать запрос.  [new]
user1241
Guest
user1241
xtender,

можно сделать полный test case с кешированием, прогнать execute immediate с запросами 10к раз, сравнить с твоим вариантом, получить видимую разницу между min=max и остальными, но , имхо, и так все видно

Пятница блин, ничего не соображаю, у тебя же не count distinct, а частный случай count distinct rownum, тут уже не очевидно, да и практического смысла считать кто быстрее нет, разница на комарах, но ради интереса, вот test case

+
Присоединен к:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options

SQL> create table test10
  2  as
  3  select cast( trunc(level) as varchar2(100)) f1
  4     , level f2
  5     , level f3
  6     , cast(level as char(10)) f4
  7  from dual
  8  connect by level < power(10,5)
  9  ;

Таблица создана.

Затрач.время: 00:00:00.45
SQL>
SQL> commit;

Фиксация обновлений завершена.

Затрач.время: 00:00:00.00
SQL>
SQL> alter table test10 modify f1 not null;

Таблица изменена.

Затрач.время: 00:00:00.14
SQL>
SQL> create index ffff on test10(f1);

Индекс создан.

Затрач.время: 00:00:00.14
SQL>
SQL> set autotrace on
SQL> set timing on
SQL>
SQL> declare
  2    q number;
  3  begin
  4    for i in 1..10000 loop
  5    execute immediate '
  6  with
  7  v as (select /*+ inline*/
  8               (select min(t2.f1) q from test10 t2) m1
  9             , (select max(t2.f1) q from test10 t2) m2
 10        from dual where rownum <=1)
 11  select sum(f1)
 12  from (
 13    select decode((select m1 from v),(select m2 from v),1,0) f1
 14    from test10
 15  )' into q;
 16    end loop;
 17  end;
 18  /

Процедура PL/SQL успешно завершена.

Затрач.время: 00:00:45.50
SQL>
SQL> declare
  2    q number;
  3  begin
  4    for i in 1..10000 loop
  5    execute immediate '
  6  with
  7  v as (select /*+ materialize*/
  8               (select min(t2.f1) q from test10 t2) m1
  9             , (select max(t2.f1) q from test10 t2) m2
 10        from dual)
 11  select sum(f1)
 12  from (
 13    select decode((select m1 from v),(select m2 from v),1,0) f1
 14    from test10
 15  )' into q;
 16    end loop;
 17  end;
 18  /

Процедура PL/SQL успешно завершена.

Затрач.время: 00:02:30.67
SQL>
SQL> declare
  2    q number;
  3  begin
  4    for i in 1..10000 loop
  5    execute immediate '
  6  with
  7  v as (select count(*) cnt from  (select distinct t2.f1 from test10 t2
  8                                   where f1 is not null)
  9        where rownum<=2
 10  )
 11  select sum(f1)
 12  from (
 13    select decode((select cnt from v),1,1,0) f1
 14    from test10
 15  )' into q;
 16    end loop;
 17  end;
 18  /

Процедура PL/SQL успешно завершена.

Затрач.время: 00:04:15.60
SQL>
SQL> drop table test10;

Таблица удалена.

время почему-то каждый раз разным получается, но кто быстрее, кто медленнее - это не меняется

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

а вообще distinct-ов у тебя подозрительно много
WHERE level = 1 непонятно зачем нужен
29 авг 14, 17:41    [16514288]     Ответить | Цитировать Сообщить модератору
 Re: Помогите переделать запрос.  [new]
Ryuu
Member

Откуда:
Сообщений: 549
автор
отлаживать его нужно, однако
планы нормальные смотреть

Поподробнее?

автор
а вообще distinct-ов у тебя подозрительно много

Два - много? Такие таблицы... Лишних нет. И из-за небольшого числа данных, вред от них незначительный.

автор
WHERE level = 1 непонятно зачем нужен

Флаг, есть состав, или нету. Если есть, то нужно будет отгружать, как пакость. Для проверки наличия мне хватает и одной строчки. Возможно, это можно сделать как-то иначе.
1 сен 14, 09:18    [16518705]     Ответить | Цитировать Сообщить модератору
 Re: Помогите переделать запрос.  [new]
Ryuu
Member

Откуда:
Сообщений: 549
Меня в принципе данные запросы устраивают, время их работы, поэтому я конечно замечания буду рад учесть, но можете не заморачиваться. Изначальный вопрос был решен.
1 сен 14, 09:21    [16518714]     Ответить | Цитировать Сообщить модератору
 Re: Помогите переделать запрос.  [new]
Ryuu
Member

Откуда:
Сообщений: 549
автор
Флаг, есть состав, или нету. Если есть, то нужно будет отгружать, как пакость. Для проверки наличия мне хватает и одной строчки. Возможно, это можно сделать как-то иначе.

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

Данное ограничение по уровню нужно затем, чтобы проверить код заготовки, совпадет с кодом материала на первом уровне, или нет, именно на первом. В общем не парьтесь, он не лишний.
1 сен 14, 16:59    [16520534]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить