Чудеса материализации

Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 
Connected as self
-- баг от версии не зависит
SQL> 
drop table t1
/

Table dropped
drop table t2
/

Table dropped
create table t1 as
select rownum rn,t.owner o,t.table_name tn,t.num_rows
from dba_tables t
where t.owner = 'SCOTT' and t.table_name in ('EMP','DEPT','BONUS')
/

Table created
create table t2 as
select rn,o owner ,tn table_name,num_rows
from t1
union all
select rownum+10 rn,t.owner,t.table_name,t.num_rows
from dba_tables t
where t.owner = 'SYS'
  and rownum < 4
/
-- создали две таблички и функцию проверки наличия строк в таблице
-- кроме того функция печатает в output имя таблицы из вызова
Table created
create or replace function IsEmptyTable(p_tableowner varchar2, p_tablename  varchar2)  return number is
  l_IsEmpty number;
  table_not_exist exception;
  pragma exception_init(table_not_exist, -942);
begin
  begin
    execute immediate ' select 0 from '||p_tableowner||'.'||p_tablename||' where rownum = 1' into l_IsEmpty;
  exception
    when no_data_found then
      l_IsEmpty := 1;
    when table_not_exist then
      l_IsEmpty := -1;
  end;
  dbms_output.put_line(p_tableowner||'.'||p_tablename||' '||l_IsEmpty);
  return l_IsEmpty;
end;
/

Function created
set serveroutput on
--запрашиваем у внутреннего объединения
select t2.* from t1,t2
where t1.rn = t2.rn
  and IsEmptyTable(owner,table_name) = 0
/

        RN OWNER                          TABLE_NAME                       NUM_ROWS
---------- ------------------------------ ------------------------------ ----------
         2 SCOTT                          DEPT                                    4
         3 SCOTT                          EMP                                    14

-- данные правильные, но output показывает что функция вызывалась чаще чем нужно
SCOTT.BONUS 1
SCOTT.DEPT 0
SCOTT.EMP 0
SYS.ICOL$ -1
SYS.CON$ -1
SYS.UNDO$ -1
-- переписываем так и даже пытаемся материализовать...
select * from (
select /*+ MATERIALIZE */ t2.* from t2,t1
where t1.rn = t2.rn
)
where IsEmptyTable(owner,table_name) = 0
/

        RN OWNER                          TABLE_NAME                       NUM_ROWS
---------- ------------------------------ ------------------------------ ----------
         2 SCOTT                          DEPT                                    4
         3 SCOTT                          EMP                                    14

SCOTT.BONUS 1
SCOTT.DEPT 0
SCOTT.EMP 0
SYS.ICOL$ -1
SYS.CON$ -1
SYS.UNDO$ -1
-- и так
select /*+ NO_QUERY_TRANSFORMATION */ * from (
select t2.* from t2,t1
where t1.rn = t2.rn
)
where IsEmptyTable(owner,table_name) = 0
/

        RN OWNER                          TABLE_NAME                       NUM_ROWS
---------- ------------------------------ ------------------------------ ----------
         2 SCOTT                          DEPT                                    4
         3 SCOTT                          EMP                                    14

SCOTT.BONUS 1
SCOTT.DEPT 0
SCOTT.EMP 0
SYS.ICOL$ -1
SYS.CON$ -1
SYS.UNDO$ -1
select * from (
select /*+ NO_MERGE */ distinct t2.owner,t2.table_name from t2,t1
where t1.rn = t2.rn
order by t2.table_name
)
where IsEmptyTable(owner,table_name) = 0
/

OWNER                          TABLE_NAME
------------------------------ ------------------------------
SCOTT                          DEPT
SCOTT                          EMP

SCOTT.BONUS 1
SCOTT.DEPT 0
SCOTT.EMP 0
SYS.ICOL$ -1
SYS.CON$ -1
SYS.UNDO$ -1
set echo off

вот такая картинка с материализацией
Что не так?
Собственно хотелось ограничить количество вызовов функции

materialize/inline - работают только для селектов под with
План смотрите - он покажет

Посмотрел в плане- хинт MATERIALIZE не срабатывал :) Спасибо
Но на план влияют хинты NO_QUERY_TRANSFORMATION и NO_MERGE, правда без особой пользы

SQL> set autotrace traceonly explain
SQL> select * from (
  2  select t2.* from t2,t1
  3  where t1.rn = t2.rn
  4  )
  5  where IsEmptyTable(owner,table_name) = 0
  6  /

План выполнения
----------------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
Plan hash value: 2959412835                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
---------------------------------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
---------------------------------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
|   0 | SELECT STATEMENT   |      |     1 |    73 |     7  (15)| 00:00:01 |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
|*  1 |  HASH JOIN         |      |     1 |    73 |     7  (15)| 00:00:01 |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
|*  2 |   TABLE ACCESS FULL| T2   |     1 |    60 |     3   (0)| 00:00:01 |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
|   3 |   TABLE ACCESS FULL| T1   |     3 |    39 |     3   (0)| 00:00:01 |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
---------------------------------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
Predicate Information (identified by operation id):                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
---------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
   1 - access("T1"."RN"="T2"."RN")                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
   2 - filter("ISEMPTYTABLE"("T2"."OWNER","T2"."TABLE_NAME")=0)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
Note                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
-----                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
   - dynamic sampling used for this statement                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   

SQL> select /*+ NO_QUERY_TRANSFORMATION */ * from (
  2  select t2.* from t2,t1
  3  where t1.rn = t2.rn
  4  )
  5  where IsEmptyTable(owner,table_name) = 0
  6  /

План выполнения
----------------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
Plan hash value: 864717605                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
----------------------------------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
----------------------------------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
|   0 | SELECT STATEMENT    |      |     1 |    60 |     7  (15)| 00:00:01 |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
|   1 |  VIEW               |      |     1 |    60 |     7  (15)| 00:00:01 |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
|*  2 |   HASH JOIN         |      |     1 |    73 |     7  (15)| 00:00:01 |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
|*  3 |    TABLE ACCESS FULL| T2   |     1 |    60 |     3   (0)| 00:00:01 |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
|   4 |    TABLE ACCESS FULL| T1   |     3 |    39 |     3   (0)| 00:00:01 |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
----------------------------------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
Predicate Information (identified by operation id):                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
---------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
   2 - access("T1"."RN"="T2"."RN")                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
   3 - filter("ISEMPTYTABLE"("T2"."OWNER","T2"."TABLE_NAME")=0)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 

и согласно плану функция отрабатывает для каждой строки T2
Как добиться чтобы ф-ция работала после JOIN`а?

_a_n_d_

..и согласно плану функция отрабатывает для каждой строки T2 Как добиться чтобы ф-ция работала после JOIN`а?

Например :

with t as (select --+ materialize
джоин )
select * from t
where функция=..

Либо

SQL> select * from (
  2  select t2.* from t2,t1
  3  where t1.rn = t2.rn and rownum < 100000000000    
  4  )
  5  where IsEmptyTable(owner,table_name) = 0
  6  /

на 10-ке

SQL> with v as (
  2  select /*+ MATERIALIZE */ t2.* from t2,t1
  3  where t1.rn = t2.rn
  4  )
  5  select * from v
  6  where IsEmptyTable(owner,table_name) = 0
  7  /

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

---------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                            |     3 |   180 |     9  (12)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION |                            |       |       |            |          |
|   2 |   LOAD AS SELECT           |                            |       |       |            |          |
|*  3 |    HASH JOIN               |                            |     3 |   219 |     7  (15)| 00:00:01 |
|   4 |     TABLE ACCESS FULL      | T1                         |     3 |    39 |     3   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL      | T2                         |     6 |   360 |     3   (0)| 00:00:01 |
|*  6 |   VIEW                     |                            |     3 |   180 |     2   (0)| 00:00:01 |
|   7 |    TABLE ACCESS FULL       | SYS_TEMP_0FD9D660F_16DD722 |     3 |   180 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

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

   3 - access("T1"."RN"="T2"."RN")
   6 - filter("ISEMPTYTABLE"("OWNER","TABLE_NAME")=0)

но 9-ке план не такой красивый но результат правильный

SQL> with v as (
  2  select /*+ MATERIALIZE */ t2.* from t2,t1
  3  where t1.rn = t2.rn
  4  )
  5  select * from v
  6  where IsEmptyTable(owner,table_name) = 0
  7  /

План выполнения
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    2     RECURSIVE EXECUTION OF 'SYS_LE_2_0'
   2    0   TEMP TABLE TRANSFORMATION
   3    2     VIEW
   4    3       TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6604_D4134F97'

Спасибо - выход с with работает, но... эффект "затягивания" функции внутрь не всегда удобен
а ход выполнения неочевиден
Например

set serveroutput on
select t.owner,t.table_name,t.num_rows
from all_tables t
where t.owner = 'SCOTT'
  and IsEmptyTable(t.owner,t.table_name) = 0
/

OWNER                          TABLE_NAME                       NUM_ROWS
------------------------------ ------------------------------ ----------
SCOTT                          DEPT                                    4
SCOTT                          EMP                                    12
SCOTT                          SALGRADE                                5

SCOTT.BONUS 1
SCOTT.DEPT 0
SCOTT.EMP 0
SCOTT.PK_DEPT -1
SCOTT.PK_EMP -1
SCOTT.SALGRADE 0

Т.е. на вход функции подавались не только ТАБЛИЦЫ (из all_tables) но и ИНДЕКСЫ схемы SCOTT...
(хорошо еще что не объекты всех схем ;)))

Можно ли обойтись без with (ведь при динамическом формировании запроса не всегда заранее можно знать что окажется во фразе where)?

_a_n_d_

.. Можно ли обойтись без with (ведь при динамическом формировании запроса не всегда заранее можно знать что окажется во фразе where)?

(имхо) На самом деле - именно при динамическом формировании запроса такая
(грубая и явная) материализация наиболее удобна+наглядна+надежна.
Возможные критерии надо предварительно классифицировать по уровням, на которых они применяются (и не факт, что только по двум уровням). Затем из этого - вполне формально - генерится запрос.

with t1 as (select --+ ma..
      ,t2 as (select --+ ma..
..
select * from tn

orawish

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

В реальном запросе уже используется with

with
w1 as (
select id as ID1
  from (
select a.id,a.code
 from t1 a
 inner join t2 b using(id_b)
 inner join t3 c using(id_c)...
 where b.code = 'BBB'
   and (c.code like 'CCC%')...
       ) where (check_function(id)=1)
       ),
w2 as (
select id as ID2...
       )...
select * from big_table,w1,w2...

При попытке следовать совету разбить по уровням

with
w1 as ( 
  with w as (
  select a.id,a.code
   from t1 a
   inner join t2 b using(id_b)
   inner join t3 c using(id_c)...
   where b.code = 'BBB'
     and (c.code like 'CCC%')...
         )
  select id as ID1
    from w where (check_function(id)=1)
       ),
w2 as (
select id as ID2...
       )...
select * from big_table,w1,w2...

Так ли я понял? Вероятно нет, т.к. получаем

ORA-32034 unsupported use of WITH clause

Cause: Improper use of WITH clause due to one of the following:

Nesting of WITH clause within WITH clause not supported.
For a set query, WITH clause can't be specified for a branch.
WITH clause cantbespecifiedwithinparentheses.'

Action: correct the query and retry.

Результативнее другой занятный вариант с

and rownum > 0

как аналог предложенного

and rownum < 100000000000

Но... может есть еще варианты ?

_a_n_d_

При попытке следовать совету разбить по уровням ORA-32034 unsupported use of WITH clause

дык а так - чтото мешает?

with
w as (
  select a.id,a.code
   from t1 a
   inner join t2 b using(id_b)
   inner join t3 c using(id_c)...
   where b.code = 'BBB'
     and (c.code like 'CCC%')...
         )
,w1 as ( 
  select id as ID1
    from w where (check_function(id)=1)
       ),
w2 as (
select id as ID2...
       )...
select * from big_table,w1,w2...

Zloxa

дык а так - чтото мешает? with w as ( select a.id,a.code from t1 a inner join t2 b using(id_b) inner join t3 c using(id_c)... where b.code = 'BBB' and (c.code like 'CCC%')... ) ,w1 as ( select id as ID1 from w where (check_function(id)=1) ), w2 as ( select id as ID2... )... select * from big_table,w1,w2...

Спасибо - возьму на вооружение :)