NOT IN( select from X), где "X" содержит NULL в первой записи: всегда ли он это учитывает? / Firebird, InterBase

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

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

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / Firebird, InterBase [игнор отключен] [закрыт для гостей] / NOT IN( select from X), где "X" содержит NULL в первой записи: всегда ли он это учитывает?

21 сообщений из 21, страница 1 из 1

NOT IN( select from X), где "X" содержит NULL в первой записи: всегда ли он это учитывает?

#38574137

Таблоид

Участник

Сообщения: 9 702

Рейтинг: 0 / 0

hi all

Дано: LI-T3.0.0.30889

Код: sql

1.
2.
3.

recreate table t(i int); commit;
insert into t select row_number()over() i from rdb$types a,rdb$types b rows 1000;
commit;

Вот два варианта одного и того же запроса, в котором NOT IN() лезет в выборку, содержащую в одной из записей NULL (след-но, итоговая выборка должна быть пустой):
var-1

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.

with
b as(
  select row_number()over() i from t a join t b on a.i=b.i rows 500
)
,c as(
  select i from (
      select cast(null as int) i from rdb$database union all -- помещаем эту запись в начало выборки
      select i from (select row_number()over() i from t a join t b on a.i=b.i rows 499)
  )
)
select x.i
from b x
where x.i not in (select i from c);

Его статистика однозначно говорит, что ФБ прекратил сканровать 'c' сразу после того, как наткнулся на NULL-строку:

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.

PLAN (RDB$DATABASE NATURAL, HASH (T NATURAL, T NATURAL))
PLAN (RDB$DATABASE NATURAL, HASH (T NATURAL, T NATURAL))
PLAN HASH (X B NATURAL, X A NATURAL)
0 records fetched
       3 ms, 5556 fetch(es) 

Table                             Natural     Index    U
********************************************************
RDB$DATABASE                          500
T                                    2000

var-2.

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.

with
b as(
  select row_number()over() i from t a join t b on a.i=b.i rows 500
)
,c as(
  select i from (
      --select cast(null as int) i from rdb$database union all
      select i from (select row_number()over() i from t a join t b on a.i=b.i rows 499)
      union all select cast(null as int) i from rdb$database -- теперь NULL-запись указываем в самом заду
  )
)
select x.i
from b x
where x.i not in (select i from c);

Статистика показывает, что ФБ парился вплоть до последней записи:

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.

PLAN (HASH (T NATURAL, T NATURAL), RDB$DATABASE NATURAL)
PLAN (HASH (T NATURAL, T NATURAL), RDB$DATABASE NATURAL)
PLAN HASH (X B NATURAL, X A NATURAL)
0 records fetched
    1455 ms, 2032059 fetch(es) 

Table                             Natural     Index    Up
*********************************************************
RDB$DATABASE                            1
T                                 1002000

Пытаемся подправить дело, принудительно заставив ФБ упорядочить выборку 'c' так, чтобы NULL-строка оказалась первой:
var 2b.

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.

with
b as(
  select row_number()over() i from t a join t b on a.i=b.i rows 500
)
,c as(
  select i from (
      --select cast(null as int) i from rdb$database union all
      select i from (select row_number()over() i from t a join t b on a.i=b.i rows 499)
      union all select cast(null as int) i from rdb$database
  )
  group by 1
)
select x.i
from b x
where x.i not in (select i from c);

или так:
var 2c.

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.

with
b as(
  select row_number()over() i from t a join t b on a.i=b.i rows 500
)
,c as(
  select i from (
      --select cast(null as int) i from rdb$database union all
      select i from (select row_number()over() i from t a join t b on a.i=b.i rows 499)
      union all select cast(null as int) i from rdb$database
  )
  order by i nulls first
)
select x.i
from b x
where x.i not in (select i from c);

Для обоих вариантов (2b & 2c) - увы, ничего не взлетает:

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.

PLAN SORT (HASH (T NATURAL, T NATURAL), RDB$DATABASE NATURAL)
PLAN SORT (HASH (T NATURAL, T NATURAL), RDB$DATABASE NATURAL)
PLAN HASH (X B NATURAL, X A NATURAL)
0 records fetched
    1533 ms, 2035056 fetch(es) 

Table                             Natural     Index    Update  
***************************************************************
RDB$DATABASE                          500
T                                 1002000

И еще.
Если заменить row_number() на само поле 't.i':

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.

with
b as(
  select a.i from t a join t b on a.i=b.i rows 500
)
,c as(
  select i from (
      --select cast(null as int) i from rdb$database union all
      select i from (select a.i from t a join t b on a.i=b.i rows 499)
      union all select cast(null as int) i from rdb$database
  )
  --group by 1
  order by i nulls first
)
select x.i
from b x
where x.i not in (select i from c);

- то статистика станет лучше, фетчей меньше на пол-ляма (хотя всё равно идёт туго):

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.

PLAN SORT (HASH (T NATURAL, T NATURAL), RDB$DATABASE NATURAL)
PLAN SORT (HASH (T NATURAL, T NATURAL), RDB$DATABASE NATURAL)
PLAN HASH (X B NATURAL, X A NATURAL)
0 records fetched
     938 ms, 1525541 fetch(es) 

Table                             Natural     Index    Update 
**************************************************************
RDB$DATABASE                          500
T                                  751000

И на довесок: order by i nulls first оказывает вообще какое-то губительное воздействие, даже если в выборке 'c' строку с null'ом засадить первой, как в var-1 :

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.

with
b as(
  select a.i from t a join t b on a.i=b.i rows 500
)
,c as(
  select i from (
      select cast(null as int) i from rdb$database union all -- снова на первом месте
      select i from (select a.i from t a join t b on a.i=b.i rows 499)
      --union all select cast(null as int) i from rdb$database
  )
  order by i nulls first -- бестолковая (в данном случае) сортировка, но она приводит к той же проблеме, что в var-2
)
select x.i
from b x
where x.i not in (select i from c);

Trace:

Код: plaintext

1.
2.
3.
4.
5.

    935 ms, 1525541 fetch(es)

Table                             Natural     Index    Update 
**************************************************************
RDB$DATABASE                          500
T                                  751000

В общем, я не понял что-то: order by i nulls first - он же должен выполниться мгновенно (там 500 строк всего). СТЕшка 'c', следовательно, должна поступить на вход в финальный запрос уже отсортированная, причём в ней первой записью будет NULL.

Что тогда мешает ФБ выполнять все разновидности var-2 так же быстро, как var-1 ?

...

Рейтинг:

0 / 0

27.02.2014, 13:59:15

| Ответить | Цитировать | Написать

NOT IN( select from X), где "X" содержит NULL в первой записи: всегда ли он это учитывает?

#38574182

Таблоид

Участник

Сообщения: 9 702

Рейтинг: 0 / 0

Кстати, если затолкать в таблицу 't' побольше строк:

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.

create sequence g;
commit;
alter sequence g restart with 0;
delete from t;
commit;
select count(*) from t;
commit;
insert into t select gen_id(g,1) i from rdb$types a,rdb$types b rows 5000;
commit;

- то при сравнении вот такого варианта на 3.0 и 2.5:

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.

with
b as(
  select a.i from t a join t b on a.i=b.i rows 2000
)
,c as(
  select i from (
      select i from (select a.i from t a join t b on a.i=b.i rows 1999)
      union all select cast(null as int) i from rdb$database
  )
  order by i nulls first
)
select x.i
from b x
where x.i not in (select i from c);

- видно, что hash join идёт вровень по времени с merge join'ом:

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.

PLAN SORT (HASH (T NATURAL, T NATURAL), RDB$DATABASE NATURAL)
PLAN SORT (HASH (T NATURAL, T NATURAL), RDB$DATABASE NATURAL)
PLAN HASH (X B NATURAL, X A NATURAL)
0 records fetched
  18871 ms,  36476227  fetch(es)

Table                             Natural     Index    Update 
**************************************************************
RDB$DATABASE                         2000
T                                14005000

== vs 2.5 ==

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.

PLAN MERGE (SORT (T NATURAL), SORT (T NATURAL))
PLAN (RDB$DATABASE NATURAL)
PLAN MERGE (SORT (T NATURAL), SORT (T NATURAL))
PLAN (RDB$DATABASE NATURAL)
PLAN MERGE (SORT (X B NATURAL), SORT (X A NATURAL))
0 records fetched
  18938 ms,  48638302  fetch(es)

Table                             Natural     Index 
****************************************************
RDB$DATABASE                         2000
T                                20010000

В статистике не хватает сведений о других расходах при HJ.
2 dimitr / hvlad : это можно будет устранить в альфа/бете/гамма/дельте-3.x ?

...

Рейтинг:

0 / 0

27.02.2014, 14:18:15

| Ответить | Цитировать | Написать

NOT IN( select from X), где "X" содержит NULL в первой записи: всегда ли он это учитывает?

#38574207

Симонов Денис

Участник

Откуда: Рязань

Сообщения: 11 624

Рейтинг: 0 / 0

ТаблоидВот два варианта одного и того же запроса, в котором NOT IN() лезет в выборку, содержащую в одной из записей NULL (след-но, итоговая выборка должна быть пустой):

думал над этой фразой и решил проверить запрос

авторwith
c(n) as(
select null from rdb$database union all
select 2 from rdb$database union all
select 3 from rdb$database
)
select count(*) as q
from rdb$database
where 1 not in (select n from c);

даёт план

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.

Select Expression
    -> Union
        -> Table "RDB$DATABASE" Full Scan
        -> Table "RDB$DATABASE" Full Scan
        -> Table "RDB$DATABASE" Full Scan
Select Expression
    -> Filter
        -> Union
            -> Filter
                -> Table "RDB$DATABASE" Full Scan
            -> Filter
                -> Table "RDB$DATABASE" Full Scan
            -> Filter
                -> Table "RDB$DATABASE" Full Scan
Select Expression
    -> Aggregate
        -> Filter
            -> Table "RDB$DATABASE" Full Scan

           Q
============
           0

по мне так довольно странный

...

Рейтинг:

0 / 0

27.02.2014, 14:33:31

| Ответить | Цитировать | Написать

NOT IN( select from X), где "X" содержит NULL в первой записи: всегда ли он это учитывает?

#38574213

Таблоид

Участник

Сообщения: 9 702

Рейтинг: 0 / 0

Пока все молчат, буду говорить дальше :-)
Добавляю индекс:

Код: sql

create index t_i on t(i); commit;

Запускаю:

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.

with
b as(
  select a.i from t a join t b on a.i=b.i rows 2000
)
,c as(
  select i from (
      select i from (select a.i from t a join t b on a.i=b.i rows 1999)
      union all select cast(null as int) i from rdb$database
  )
  order by i nulls first
)
select x.i
from b x
where x.i not in (select i from c);

В обоих версиях план = NL.
Но ФБ-3 проигрывает: он делает ДВЕ сортировки (почему, кстати ?) и на 8 млн больше фетчей

trace 3.0

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.

Statement 32766:
-----------------------------------------------------------------------
with
b as(
  select a.i from t a join t b on a.i=b.i rows 2000
)
,c as(
  select i from (
      select i from (select a.i from t a join t b on a.i=b.i rows 1999)
      union all select cast(null as int) i from rdb$database
  )
  order by i nulls first
)
select x.i
from b x
where x.i not in (select i from c)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN SORT (JOIN (T NATURAL, T INDEX (T_I)), RDB$DATABASE NATURAL)
PLAN SORT (JOIN (T NATURAL, T INDEX (T_I)), RDB$DATABASE NATURAL)
PLAN JOIN (X A NATURAL, X B INDEX (T_I))
0 records fetched
  18936 ms, 36168078 fetch(es)

Table                             Natural     Index    Update    Insert
***********************************************************************
RDB$DATABASE                         2000
T                                 4000000   4000000

== vs ==

trace 2.5

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.

Statement 75:
-----------------------------------------------------------------------
with
b as(
  select a.i from t a join t b on a.i=b.i rows 2000
)
,c as(
  select i from (
      select i from (select a.i from t a join t b on a.i=b.i rows 1999)
      union all select cast(null as int) i from rdb$database
  )
  order by i nulls first
)
select x.i
from b x
where x.i not in (select i from c)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN JOIN (T NATURAL, T INDEX (T_I))
PLAN (RDB$DATABASE NATURAL)
PLAN JOIN (T NATURAL, T INDEX (T_I))
PLAN (RDB$DATABASE NATURAL)
PLAN JOIN (X A NATURAL, X B INDEX (T_I))
0 records fetched
  17586 ms, 28112053 fetch(es)

Table                             Natural     Index    Update    Insert
***********************************************************************
RDB$DATABASE                         2000
T                                 4000000   4000000

...

Рейтинг:

0 / 0

27.02.2014, 14:37:13

| Ответить | Цитировать | Написать

NOT IN( select from X), где "X" содержит NULL в первой записи: всегда ли он это учитывает?

#38574219

Таблоид

Участник

Сообщения: 9 702

Рейтинг: 0 / 0

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

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.

SQL> set explain on;
SQL> with
CON> b as(
CON>  select a.i from t a join t b on a.i=b.i rows 2000
CON> )
CON> ,c as(
CON>   select i from (
CON>       select i from (select a.i from t a join t b on a.i=b.i rows 1999)
CON>       union all select cast(null as int) i from rdb$database
CON>   )
CON>   order by i nulls first
CON> )
CON> select x.i
CON> from b x
CON> where x.i not in (select i from c);

Select Expression
    -> Sort (record length: 68, key length: 8)
        -> Union
            -> First N Records
                ->  Nested Loop Join (inner)
                    -> Table "T" Full Scan
                    -> Filter
                        -> Table "T" Access By ID
                            -> Bitmap
                                -> Index "T_I" Range Scan (full match)
            -> Table "RDB$DATABASE" Full Scan
Select Expression
    -> Filter
        -> Sort (record length: 68, key length: 8)
            -> Union
                -> First N Records
                    ->  Nested Loop Join (inner)
                        -> Table "T" Full Scan
                        -> Filter
                            -> Table "T" Access By ID
                                -> Bitmap
                                    -> Index "T_I" Range Scan (full match)
                -> Table "RDB$DATABASE" Full Scan
Select Expression
    -> Filter
        -> First N Records
            ->  Nested Loop Join (inner)
                -> Table "X A" Full Scan
                -> Filter
                    -> Table "X B" Access By ID
                        -> Bitmap
                            -> Index "T_I" Range Scan (full match)

...

Рейтинг:

0 / 0

27.02.2014, 14:41:25

| Ответить | Цитировать | Написать

NOT IN( select from X), где "X" содержит NULL в первой записи: всегда ли он это учитывает?

#38574228

Симонов Денис

Участник

Откуда: Рязань

Сообщения: 11 624

Рейтинг: 0 / 0

ТаблоидНо ФБ-3 проигрывает: он делает ДВЕ сортировки (почему, кстати ?) и на 8 млн больше фетчей

что-то странно. По приведённому тобой плану в 2.5 сортировки нет вовсе

...

Рейтинг:

0 / 0

27.02.2014, 14:44:49

| Ответить | Цитировать | Написать

NOT IN( select from X), где "X" содержит NULL в первой записи: всегда ли он это учитывает?

#38574310

Таблоид

Участник

Сообщения: 9 702

Рейтинг: 0 / 0

Симонов ДенисПо приведённому тобой плану в 2.5 сортировки нет вовсеЧто-то поменялось с not in().

var-1.

Код: sql

1.
2.
3.
4.

select 1 from rdb$database
where
    1
    not in(select 1 from rdb$database union distinct select 1 from rdb$database)

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

FB-3.0:
PLAN SORT (RDB$DATABASE NATURAL, RDB$DATABASE NATURAL) -- если она не лишняя, то в чём её цель ?
PLAN SORT (RDB$DATABASE NATURAL, RDB$DATABASE NATURAL)
PLAN (RDB$DATABASE NATURAL)

FB-2.5:
PLAN (RDB$DATABASE NATURAL)
PLAN (RDB$DATABASE NATURAL)
PLAN SORT ((RDB$DATABASE NATURAL)
PLAN (RDB$DATABASE NATURAL))
PLAN (RDB$DATABASE NATURAL)

var-2.

Код: sql

1.
2.
3.
4.

select 1 from rdb$database
where
    1
    not in(select min(1) from rdb$database group by null)

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.

FB-3.0:
PLAN SORT (RDB$DATABASE NATURAL) -- лишняя (?)
PLAN SORT (RDB$DATABASE NATURAL)
PLAN (RDB$DATABASE NATURAL)

Fb-2.5:
PLAN SORT ((RDB$DATABASE NATURAL)) -- и тут, в 2.5, тоже она есть!
PLAN SORT ((RDB$DATABASE NATURAL))
PLAN (RDB$DATABASE NATURAL)

...

Рейтинг:

0 / 0

27.02.2014, 15:26:25

| Ответить | Цитировать | Написать

NOT IN( select from X), где "X" содержит NULL в первой записи: всегда ли он это учитывает?

#38574324

Симонов Денис

Участник

Откуда: Рязань

Сообщения: 11 624

Рейтинг: 0 / 0

Таблоид,

судя по этому плану

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.

Select Expression
    -> Union
        -> Table "RDB$DATABASE" Full Scan
        -> Table "RDB$DATABASE" Full Scan
        -> Table "RDB$DATABASE" Full Scan
Select Expression
    -> Filter
        -> Union
            -> Filter
                -> Table "RDB$DATABASE" Full Scan
            -> Filter
                -> Table "RDB$DATABASE" Full Scan
            -> Filter
                -> Table "RDB$DATABASE" Full Scan
Select Expression
    -> Aggregate
        -> Filter
            -> Table "RDB$DATABASE" Full Scan

Второй сортировки там таки нет. Просто она неверно прорисовывается. Здесь тоже RDB$DATABASE вырисовывается 6 раз. Не понимаю почему не 3

...

Рейтинг:

0 / 0

27.02.2014, 15:33:26

| Ответить | Цитировать | Написать

NOT IN( select from X), где "X" содержит NULL в первой записи: всегда ли он это учитывает?

#38574327

dimitr

Участник

Откуда: PNZ

Сообщения: 7 059

Рейтинг: 0 / 0

занеси себе в голову, что:

- сортировка ничего не возвращает наружу, пока не отсортирует весь входной поток
- NOT IN выполняется для каждой записи

тогда ты поймешь, чтобы твой запрос выполняет как минимум 500 сортировок по 500 записей

ЗЫ. будете продолжать валить кучу несвязанных вопросов в один топик - не буду даже пытаться разбираться

...

Рейтинг:

0 / 0

27.02.2014, 15:34:58

| Ответить | Цитировать | Написать

NOT IN( select from X), где "X" содержит NULL в первой записи: всегда ли он это учитывает?

#38574332

Симонов Денис

Участник

Откуда: Рязань

Сообщения: 11 624

Рейтинг: 0 / 0

для запроса

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.

with
c(n) as(
select null from rdb$database union all
select 2 from rdb$database union all
select 3 from rdb$database
)
select count(*) as q
from rdb$database
where 1 in (select n from c);

план уже такой

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.

Select Expression
    -> Filter
        -> Union
            -> Table "C RDB$DATABASE" Full Scan
            -> Table "C RDB$DATABASE" Full Scan
            -> Table "C RDB$DATABASE" Full Scan
Select Expression
    -> Aggregate
        -> Filter
            -> Table "RDB$DATABASE" Full Scan

...

Рейтинг:

0 / 0

27.02.2014, 15:36:57

| Ответить | Цитировать | Написать

NOT IN( select from X), где "X" содержит NULL в первой записи: всегда ли он это учитывает?

#38574337

dimitr

Участник

Откуда: PNZ

Сообщения: 7 059

Рейтинг: 0 / 0

сначала пишем UNION DISTINCT , а потом удивляемся, а откуда там сортировка...

...

Рейтинг:

0 / 0

27.02.2014, 15:39:34

| Ответить | Цитировать | Написать

NOT IN( select from X), где "X" содержит NULL в первой записи: всегда ли он это учитывает?

#38574342

Симонов Денис

Участник

Откуда: Рязань

Сообщения: 11 624

Рейтинг: 0 / 0

dimitrзанеси себе в голову, что:

- сортировка ничего не возвращает наружу, пока не отсортирует весь входной поток
- NOT IN выполняется для каждой записи

тогда ты поймешь, чтобы твой запрос выполняет как минимум 500 сортировок по 500 записей

ЗЫ. будете продолжать валить кучу несвязанных вопросов в один топик - не буду даже пытаться разбираться

хорошо. Тогда почему для

with
c(n) as(
select null from rdb$database union all
select 2 from rdb$database union all
select 3 from rdb$database
)
select count(*) as q
from rdb$database
where 1 not in (select n from c);

план не такой

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.

Select Expression
    -> Filter
        -> Union
            -> Filter
                -> Table "RDB$DATABASE" Full Scan
            -> Filter
                -> Table "RDB$DATABASE" Full Scan
            -> Filter
                -> Table "RDB$DATABASE" Full Scan
Select Expression
    -> Aggregate
        -> Filter
            -> Table "RDB$DATABASE" Full Scan

...

Рейтинг:

0 / 0

27.02.2014, 15:41:25

| Ответить | Цитировать | Написать

NOT IN( select from X), где "X" содержит NULL в первой записи: всегда ли он это учитывает?

#38574353

dimitr

Участник

Откуда: PNZ

Сообщения: 7 059

Рейтинг: 0 / 0

Симонов Денис,

потому что NOT IN уже давно трансформируется в два (или три, не помню) подзапроса, чтобы он мог использовать индексы

...

Рейтинг:

0 / 0

27.02.2014, 15:47:27

| Ответить | Цитировать | Написать

NOT IN( select from X), где "X" содержит NULL в первой записи: всегда ли он это учитывает?

#38574382

Симонов Денис

Участник

Откуда: Рязань

Сообщения: 11 624

Рейтинг: 0 / 0

dimitr,

можно поподробнее. А то в статье по методам доступа этого не увидел

вот такой запрос

Код: sql

1.
2.

select count(*) 
from color where code_color not in (select code_breed from breed);

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.

Select Expression
    -> Table "BREED" Full Scan
Select Expression
    -> Filter
        -> Table "BREED" Access By ID
            -> Bitmap Or
                -> Bitmap
                    -> Index "PK_BREED" Unique Scan
                -> Bitmap
                    -> Index "PK_BREED" Unique Scan
Select Expression
    -> Aggregate
        -> Filter
            -> Table "COLOR" Full Scan

       COUNT
============
         111

зачем здесь

Код: plaintext

Select Expression
    -> Table "BREED" Full Scan

...

Рейтинг:

0 / 0

27.02.2014, 16:02:00

| Ответить | Цитировать | Написать

NOT IN( select from X), где "X" содержит NULL в первой записи: всегда ли он это учитывает?

#38574526

Таблоид

Участник

Сообщения: 9 702

Рейтинг: 0 / 0

dimitrзанеси себе в голову, что:

- сортировка ничего не возвращает наружу, пока не отсортирует весь входной поток
- NOT IN выполняется для каждой записи

тогда ты поймешь, чтобы твой запрос выполняет как минимум 500 сортировок по 500 записейБудет ли материализация результатов подзапросов, которая вроде как грядёт в скором времени в ФБ-3, распространена также на то, что внутри [not] in( ... ) ?

...

Рейтинг:

0 / 0

27.02.2014, 17:37:18

| Ответить | Цитировать | Написать

NOT IN( select from X), где "X" содержит NULL в первой записи: всегда ли он это учитывает?

#38574617

Симонов Денис

Участник

Откуда: Рязань

Сообщения: 11 624

Рейтинг: 0 / 0

Симонов Денисзачем здесь

Код: plaintext

Select Expression
    -> Table "BREED" Full Scan

кажется понял. Это наверное для того чтобы обнаружить NULLы

...

Рейтинг:

0 / 0

27.02.2014, 18:55:46

| Ответить | Цитировать | Написать

NOT IN( select from X), где "X" содержит NULL в первой записи: всегда ли он это учитывает?

#38574644

Симонов Денис

Участник

Откуда: Рязань

Сообщения: 11 624

Рейтинг: 0 / 0

хотя не снимается

Код: sql

1.
2.
3.

SELECT COUNT(*)
FROM COLOR
WHERE CODE_COLOR NOT IN (SELECT CODE_BREED FROM BREED);

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.

Select Expression
    -> Table "BREED" Full Scan
Select Expression
    -> Filter
        -> Table "BREED" Access By ID
            -> Bitmap Or
                -> Bitmap
                    -> Index "PK_BREED" Unique Scan
                -> Bitmap
                    -> Index "PK_BREED" Unique Scan
Select Expression
    -> Aggregate
        -> Filter
            -> Table "COLOR" Full Scan

       COUNT
============
         111

Current memory = 140476008
Delta memory = 63960
Max memory = 141805576
Elapsed time= 0.068 sec
Buffers = 8192
Reads = 0
Writes = 0
Fetches = 1202

ведь эквивалентен

Код: sql

1.
2.
3.
4.
5.

SELECT COUNT(*)
FROM COLOR
WHERE NOT EXISTS(SELECT *
                 FROM BREED
                 WHERE CODE_BREED = CODE_COLOR OR CODE_BREED IS NULL);

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.

Select Expression
    -> Filter
        -> Table "BREED" Access By ID
            -> Bitmap Or
                -> Bitmap
                    -> Index "PK_BREED" Unique Scan
                -> Bitmap
                    -> Index "PK_BREED" Unique Scan
Select Expression
    -> Aggregate
        -> Filter
            -> Table "COLOR" Full Scan

       COUNT
============
         111

Current memory = 140469928
Delta memory = -6080
Max memory = 141805576
Elapsed time= 0.071 sec
Buffers = 8192
Reads = 0
Writes = 0
Fetches = 1201

однако второй запрос никаких натурал сканов в плане не имеет

...

Рейтинг:

0 / 0

27.02.2014, 19:24:34

| Ответить | Цитировать | Написать

NOT IN( select from X), где "X" содержит NULL в первой записи: всегда ли он это учитывает?

#38574673

Симонов Денис

Участник

Откуда: Рязань

Сообщения: 11 624

Рейтинг: 0 / 0

один момент я всё же опустил

ведь эквивалентен

Код: sql

1.
2.
3.
4.
5.
6.

SELECT COUNT(*)
FROM COLOR
WHERE NOT EXISTS(SELECT *
                 FROM BREED
                 WHERE CODE_BREED = CODE_COLOR OR CODE_BREED IS NULL)
  [color=red]AND CODE_COLOR IS NOT NULL[/color];

...

Рейтинг:

0 / 0

27.02.2014, 19:59:14

| Ответить | Цитировать | Написать

NOT IN( select from X), где "X" содержит NULL в первой записи: всегда ли он это учитывает?

#38574767

dimitr

Участник

Откуда: PNZ

Сообщения: 7 059

Рейтинг: 0 / 0

Симонов Денис,

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

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

// Try to convert nodes of expression:
//   select ... from <t1>
//     where <x> not in (select <y> from <t2>)
//   (and its variants that uses the same BLR: {NOT (a = ANY b)} and {a <> ALL b})
// to:
//   select ... from <t1>
//     where not ((x is null and exists (select 1 from <t2>)) or
//                exists (select <y> from <t2> where <y> = <x> or <y> is null))
//
// Because the second form can use indexes.

...

Рейтинг:

0 / 0

27.02.2014, 21:52:00

| Ответить | Цитировать | Написать

NOT IN( select from X), где "X" содержит NULL в первой записи: всегда ли он это учитывает?

#38574770

dimitr

Участник

Откуда: PNZ

Сообщения: 7 059

Рейтинг: 0 / 0

ТаблоидБудет ли материализация результатов подзапросов, которая вроде как грядёт в скором времени в ФБ-3, распространена также на то, что внутри [not] in( ... ) ?
все может быть

...

Рейтинг:

0 / 0

27.02.2014, 21:53:25

| Ответить | Цитировать | Написать

NOT IN( select from X), где "X" содержит NULL в первой записи: всегда ли он это учитывает?

#38574940

Симонов Денис

Участник

Откуда: Рязань

Сообщения: 11 624

Рейтинг: 0 / 0

dimitr

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

// Try to convert nodes of expression:
//   select ... from <t1>
//     where <x> not in (select <y> from <t2>)
//   (and its variants that uses the same BLR: {NOT (a = ANY b)} and {a <> ALL b})
// to:
//   select ... from <t1>
//     where not ((x is null and exists (select 1 from <t2>)) or
//                exists (select <y> from <t2> where <y> = <x> or <y> is null))
//
// Because the second form can use indexes.

Спасибо. Теперь ясно откуда такой план. Да и на вопрос про двойную сортировку это даёт ответ.

...

Рейтинг:

0 / 0

28.02.2014, 09:08:30

| Ответить | Цитировать | Написать

21 сообщений из 21, страница 1 из 1

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/search_topic.php?author=phdoc&author_mode=last_topics&do_search=1]:	0ms
get settings:	8ms
get forum list:	15ms
get settings:	7ms
get forum list:	16ms
check forum access:	3ms
check topic access:	3ms
track hit:	316ms
get topic data:	9ms
get forum data:	2ms
get page messages:	53ms
get tp. blocked users:	1ms
others:	386ms

total:	819ms

	Необходимые cookie
	Cookie для сбора статистики
	Cookie для маркетинга и рекламы