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

Откуда: Scottsdale, AZ, USA
Сообщений: 3794
Потратил некоторое время, ломая голову над результатами :-)

Дано:
таблица t1, полученная в результате сложного SELECTa.
Таблица t2, полученная в процессе оптимизации этого самого SELECTa.
Сравениваю эти таблицы - в t1 9000 записей, в t2 8000.
select count(distinct document_id) from t1 возвращает все те же 9000,
такой же select from t2 - 6000.
Пытаюсь найти недостающие :
select document_id from t1 where document_id not in ( select document_id from t2) - ничего не показывает ! :-)

как переделать запрос, чтобы он таки возвращал записи из t1, отсутствующие в t2 ?

В таком вот аксепте
11 июл 08, 23:01    [5924884]     Ответить | Цитировать Сообщить модератору
 Re: Загадочный SQL :-)  [new]
olleg
Member

Откуда: Россия
Сообщений: 190
У меня так же получается, если
в t2 есть значение NULL в поле document_id.

Запрос переделал на:
select t1.document_id from t1 
where  not exists  
( select 1 from t2 where t2.document_id= t1.document_id) 
12 июл 08, 12:01    [5925409]     Ответить | Цитировать Сообщить модератору
 Re: Загадочный SQL :-)  [new]
olleg
Member

Откуда: Россия
Сообщений: 190
А вот еще:
select t1.document_id from t1 
where  0=( select count(*) from t2 where t2.document_id= t1.document_id) 
12 июл 08, 12:05    [5925416]     Ответить | Цитировать Сообщить модератору
 Re: Загадочный SQL :-)  [new]
Выбегалло
Member

Откуда: Scottsdale, AZ, USA
Сообщений: 3794
olleg
А вот еще:
select t1.document_id from t1 
where  0=( select count(*) from t2 where t2.document_id= t1.document_id) 



План смотрели ?
В такого рода запросах на каждое значение из t1 выполняется select из t2.

Подержу до понедельника, авось кто предложит более оптимальное решение ( с учетом того, что в t1 нет document_id = NULL)
13 июл 08, 01:00    [5926323]     Ответить | Цитировать Сообщить модератору
 Re: Загадочный SQL :-)  [new]
Журавлев Денис
Member

Откуда: St.John,NB,CA
Сообщений: 5532
post join predicates will help?

select t1.*
from t1 left outer join t2 on t1.documentid =t2.documentid
where t2.documentid is null
13 июл 08, 01:14    [5926349]     Ответить | Цитировать Сообщить модератору
 Re: Загадочный SQL :-)  [new]
Выбегалло
Member

Откуда: Scottsdale, AZ, USA
Сообщений: 3794
Журавлев Денис
post join predicates will help?

select t1.*
from t1 left outer join t2 on t1.documentid =t2.documentid
where t2.documentid is null


Ну можно и так. Только план почему-то ухудшается :-) Не говоря уж о том, что с left outer join надо постоянно помнить про порядок наложения предикатов.

Query plans после update statistics high :

QUERY:
------
select t1.dr_document_id from t1 left outer join t2 on (t1.dr_document_id = t2.dr_document_id)
where t2.dr_document_id is null

Estimated Cost: 18776
Estimated # of Rows Returned: 9136

1) informix.t1: SEQUENTIAL SCAN

2) informix.t2: AUTOINDEX PATH

(1) Index Keys: dr_document_id (Key-Only)
Lower Index Filter: informix.t1.dr_document_id = informix.t2.dr_document_id

ON-Filters:informix.t1.dr_document_id = informix.t2.dr_document_id
NESTED LOOP JOIN(LEFT OUTER JOIN)

PostJoin-Filters:informix.t2.dr_document_id IS NULL

Мой вариант :

QUERY:
------
select t1.dr_document_id from t1
where dr_document_id not in (select dr_document_id from t2 where dr_document_id is not null)



Estimated Cost: 611
Estimated # of Rows Returned: 4523

1) informix.t1: SEQUENTIAL SCAN

Filters: informix.t1.dr_document_id != ALL <subquery>

Subquery:
---------
Estimated Cost: 303
Estimated # of Rows Returned: 8925

1) informix.t2: SEQUENTIAL SCAN
Filters: informix.t2.dr_document_id IS NOT NULL
14 июл 08, 22:45    [5932965]     Ответить | Цитировать Сообщить модератору
 Re: Загадочный SQL :-)  [new]
olleg
Member

Откуда: Россия
Сообщений: 190
А планы моих вариантов?
В t2 были значения NULL?
Удалось найти записи из t1, отсутствующие в t2 ?
15 июл 08, 09:55    [5933945]     Ответить | Цитировать Сообщить модератору
 Re: Загадочный SQL :-)  [new]
onstat-
Member

Откуда:
Сообщений: 6941
Выбегалло


Ну можно и так. Только план почему-то ухудшается :-) Не говоря уж о том, что с left outer join надо постоянно помнить про порядок наложения предикатов.



По цифрам действительно ухудшается, но обращаем внимание выделенный текст

Выбегалло


Query plans после update statistics high :

QUERY:
------
select t1.dr_document_id from t1 left outer join t2 on (t1.dr_document_id = t2.dr_document_id)
where t2.dr_document_id is null

Estimated Cost: 18776
Estimated # of Rows Returned: 9136

1) informix.t1: SEQUENTIAL SCAN

2) informix.t2: AUTOINDEX PATH

(1) Index Keys: dr_document_id (Key-Only)
Lower Index Filter: informix.t1.dr_document_id = informix.t2.dr_document_id

ON-Filters:informix.t1.dr_document_id = informix.t2.dr_document_id
NESTED LOOP JOIN(LEFT OUTER JOIN)

PostJoin-Filters:informix.t2.dr_document_id IS NULL

Мой вариант :

QUERY:
------
select t1.dr_document_id from t1
where dr_document_id not in (select dr_document_id from t2 where dr_document_id is not null)



Estimated Cost: 611
Estimated # of Rows Returned: 4523

1) informix.t1: SEQUENTIAL SCAN

Filters: informix.t1.dr_document_id != ALL <subquery>

Subquery:
---------
Estimated Cost: 303
Estimated # of Rows Returned: 8925

1) informix.t2: SEQUENTIAL SCAN
Filters: informix.t2.dr_document_id IS NOT NULL




То есть в первом случае читаться будет только индекс, во втором случае
будет читаться таблица целяком, в зависимости от количества полей
будет разница в IO и следовательно разница в скорости.

Либо у Вас что то со статистикой, либо это какакая то фича оптимизатора которую я не понимаю.
15 июл 08, 10:34    [5934209]     Ответить | Цитировать Сообщить модератору
 Re: Загадочный SQL :-)  [new]
onstat-
Member

Откуда:
Сообщений: 6941
onstat-


Либо у Вас что то со статистикой, либо это какакая то фича оптимизатора которую я не понимаю.


Я догадываюсь в чем прикол, оптимизатор считает что прочитать
t2 через упреждающее чтение будет дешевле, чем читать индекс.

Что остается не ясным, индекс тоже можно читать упреждающим чтением
и поиск по индексу дешевле с точки зрения процессора.

Остается одно предположение, таблицы маленькие, и таблица t2 состоит
из 2 (+-) полей.

Если бы таблицы были большие первый вариант и по скорости и по косту был бы быстрее.
15 июл 08, 11:02    [5934491]     Ответить | Цитировать Сообщить модератору
 Re: Загадочный SQL :-)  [new]
Журавлев Денис
Member

Откуда: St.John,NB,CA
Сообщений: 5532
onstat-

Я догадываюсь в чем прикол, оптимизатор считает что прочитать
t2 через упреждающее чтение будет дешевле, чем читать индекс.
1. Индексов нет на обеих таблицах.
2. Для анси синтаксиса оптимизатор зачастую строит аригинальные планы.
3. Имхо самым лучшим в этом случае будет два seq scan и hj, но у автора возможно optcompind=0
select /*+use_hash(t1 /build, t2 /probe)*/ t1.*
from t1 left outer join t2 on t1.documentid =t2.documentid
where t2.documentid is null
4. Первый же ответ с where not exists , был самым труъ.

Сообщение было отредактировано: 15 июл 08, 13:45
15 июл 08, 11:41    [5934896]     Ответить | Цитировать Сообщить модератору
 Re: Загадочный SQL :-)  [new]
onstat-
Member

Откуда:
Сообщений: 6941
Журавлев Денис
onstat-

Я догадываюсь в чем прикол, оптимизатор считает что прочитать
t2 через упреждающее чтение будет дешевле, чем читать индекс.
1. Индексов нет на обеих таблицах.


Точно, недосмотрел

автор

2) informix.t2: AUTOINDEX PATH



Может стоит ему помочь построить зараннее индекс руцями, и собрать по нему статистику?
15 июл 08, 12:17    [5935280]     Ответить | Цитировать Сообщить модератору
 Re: Загадочный SQL :-)  [new]
Журавлев Денис
Member

Откуда: St.John,NB,CA
Сообщений: 5532
onstat-
Может стоит ему помочь построить зараннее индекс руцями, и собрать по нему статистику?
Запрос надо выполнить один раз, после этого таблички дропнут.
15 июл 08, 12:45    [5935532]     Ответить | Цитировать Сообщить модератору
 Re: Загадочный SQL :-)  [new]
Выбегалло
Member

Откуда: Scottsdale, AZ, USA
Сообщений: 3794
onstat-
onstat-


Либо у Вас что то со статистикой, либо это какакая то фича оптимизатора которую я не понимаю.


Я догадываюсь в чем прикол, оптимизатор считает что прочитать
t2 через упреждающее чтение будет дешевле, чем читать индекс.

Что остается не ясным, индекс тоже можно читать упреждающим чтением
и поиск по индексу дешевле с точки зрения процессора.

Остается одно предположение, таблицы маленькие, и таблица t2 состоит
из 2 (+-) полей.

Если бы таблицы были большие первый вариант и по скорости и по косту был бы быстрее.


Оптимизатор собирается строить индекс автоматически. Для построения индекса таблицу надо прочитать. А потом еще и пользоваться индексом.
При небольших таблицах ( у меня - 9к записей) проще всю таблицу втянуть в память.
15 июл 08, 20:46    [5938333]     Ответить | Цитировать Сообщить модератору
 Re: Загадочный SQL :-)  [new]
Выбегалло
Member

Откуда: Scottsdale, AZ, USA
Сообщений: 3794
Журавлев Денис

4. Первый же ответ с where not exists , был самым труъ.


План запроса для "самого труъ ответа" :-))))

select dr_document_id from t1
where not exists
( select 1 from t2 where t2.dr_document_id= t1.dr_document_id)



Estimated Cost: 7077470
Estimated # of Rows Returned: 4523

1) db_maint.t1: SEQUENTIAL SCAN

Filters: NOT EXISTS <subquery>

Subquery:
---------
Estimated Cost: 1564
Estimated # of Rows Returned: 905

1) db_maint.t2: SEQUENTIAL SCAN

Filters: db_maint.t2.dr_document_id = db_maint.t1.dr_document_id

ну и выполнялся он с минуту, на друх табличках по 9000 записей каждая.
15 июл 08, 20:55    [5938352]     Ответить | Цитировать Сообщить модератору
 Re: Загадочный SQL :-)  [new]
Журавлев Денис
Member

Откуда: St.John,NB,CA
Сообщений: 5532
Выбегалло
Журавлев Денис

4. Первый же ответ с where not exists , был самым труъ.
Нормальный план, он кажется страшным, но t2 сканируется не целиком. И план такой потому что optcompind 0, был бы 2, наверно было бы иначе (мне проверять лень).
16 июл 08, 15:45    [5942972]     Ответить | Цитировать Сообщить модератору
 Re: Загадочный SQL :-)  [new]
Выбегалло
Member

Откуда: Scottsdale, AZ, USA
Сообщений: 3794
Журавлев Денис
Выбегалло
Журавлев Денис

4. Первый же ответ с where not exists , был самым труъ.
Нормальный план, он кажется страшным, но t2 сканируется не целиком. И план такой потому что optcompind 0, был бы 2, наверно было бы иначе (мне проверять лень).


Остальные планы выполняются за секунду - а этот задумывается надолго.

OPTCOMPIND установлен в 1
17 июл 08, 03:13    [5945425]     Ответить | Цитировать Сообщить модератору
Все форумы / Informix Ответить