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

Откуда: Moscow
Сообщений: 83
Есть 2 таблицы и функция

drop table t1;
drop table t2;

create table t1 as select trunc(dbms_random.value(1,100)) as n from dual connect by level<=100000;
create table t2 as select trunc(dbms_random.value(1,100)) as n from dual connect by level<=10000;

create or replace function f(n number)
return number
is 
begin
  return 1;
end;
/


Пишем запрос:

select * from t2 where exists (select null from t1 where t2.n = f(t1.n))


План получается не ахти

Plan hash value: 2132554994
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   101 |   303 |   206   (1)| 00:00:03 |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| T2   | 10000 | 30000 |     7   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T1   |     1 |     3 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter( EXISTS (SELECT 0 FROM "T1" "T1" WHERE "F"("T1"."N")=:B1))
   3 - filter("F"("T1"."N")=:B1)


При этом, если переписать на IN, то всё OK

select * from t2 where t2.n in (select f(t1.n) from t1)


Plan hash value: 3077929639
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   101 |   606 |    54   (2)| 00:00:01 |
|*  1 |  HASH JOIN SEMI    |      |   101 |   606 |    54   (2)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T2   | 10000 | 30000 |     7   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T1   |   100K|   292K|    46   (0)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("T2"."N"="F"("T1"."N"))


Хинты USE_HASH, HASH_SJ не помогли.

Глюк оптимизатора?

P.S. 11.2.0.4 64-bit windows и linux
24 ноя 15, 20:22    [18467271]     Ответить | Цитировать Сообщить модератору
 Re: IN vs. EXISTS + user function  [new]
AlexFF__|
Member

Откуда:
Сообщений: 2852
Clipsya
Глюк оптимизатора?

Нет
24 ноя 15, 21:55    [18467711]     Ответить | Цитировать Сообщить модератору
 Re: IN vs. EXISTS + user function  [new]
Clipsya
Member

Откуда: Moscow
Сообщений: 83
В общем, проблема в функции.

Если заменить в 1м запросе user-defined функцию F на что-нибудь типа LENGTH, MOD, SIN и т.д., то запрос прекрасно UNNEST-ится.

трассировка 10053 говорит о том, что

*****************************
Cost-Based Subquery Unnesting
*****************************
SU: Unnesting query blocks in query block SEL$1 (#1) that are valid to unnest.
Subquery Unnesting on query block SEL$1 (#1)SU: Performing unnesting that does not require costing.
SU: Considering subquery unnest on query block SEL$1 (#1).
SU:   Checking validity of unnesting subquery SEL$2 (#2)
SU:     SU bypassed: Invalid correlated predicates.
SU:   Validity checks failed.


Кроме того, там же многократно встречается следующее сообщение:

PL/SQL function (F) is not secure.


Беглый поиск по гуглу ничё полезного не дал.
Буду копать дальше.
25 ноя 15, 01:00    [18468424]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить