есть две таблицы:

create table items (
itemid integer not null,
name varchar(128) not null)

itemid name
---------------
1 item1
2 item2
3 item3
4 item4
5 item5

и

create table itemvalues (
itemid integer not null,
ondate date not null,
ivalue double precision)

itemid ondate ivalue
----------------------
1 1.1.2000 1.000
1 1.2.2000 2.000
2 1.1.2000 1.500
4 1.3.2000 4.000

Следующий прок должен выбрать значения итемов на дату:

CREATE PROCEDURE SP_GET
RETURNS (
ITEMID INTEGER,
NAME VARCHAR(128),
IVALUE DOUBLE PRECISION,
IONDATE DATE)
AS
begin
for select itemid, name from items into :ITEMID, :NAME
do begin
IVALUE = null;
IONDATE = null;
select iv.ivalue, iv.ondate from itemvalues iv
where iv.itemid=:ITEMID and iv.ondate=
(select max(ondate) from itemvalues
where itemid=:ITEMID and ondate<='1.1.2005')
into :IVALUE, :IONDATE;
suspend;
end
end

результат должен быть такой:

itemid name ivalue iondate
------------------------------
1 item1 2.000 1.2.2000
2 item2 1.500 1.1.2000
3 item3 null null
4 item4 4.000 1.3.2000
5 item5 null null

а возвращается:

itemid name ivalue iondate
------------------------------
1 item1 2.000 1.2.2000
2 item2 null null
3 item3 null null
4 item4 null null
5 item5 null null

причём, что интересно, если в подзапросе пишу:
...
(select max(ondate) from itemvalues
where itemid=iv.itemid and ondate<='1.1.2005')
...
то всё работает, как надо. Где здесь могут быть грабли?

запускаю ibexpert-ом

та же фигня при запуске через ibx

и ещё - проверялось на fb1.5 и fb1.03. результаты одинаковые

2Gold:

CREATE TABLE ITEMS (
ITEMID INTEGER NOT NULL,
NAME VARCHAR(128) NOT NULL
);


CREATE TABLE ITEMVALUES (
ITEMID INTEGER NOT NULL,
ONDATE DATE NOT NULL,
IVALUE DOUBLE PRECISION
);

INSERT INTO ITEMS (ITEMID, NAME) VALUES (1, 'Item1');
INSERT INTO ITEMS (ITEMID, NAME) VALUES (2, 'Item2');
INSERT INTO ITEMS (ITEMID, NAME) VALUES (3, 'Item3');
INSERT INTO ITEMS (ITEMID, NAME) VALUES (4, 'Item4');
INSERT INTO ITEMS (ITEMID, NAME) VALUES (5, 'Item5');

INSERT INTO ITEMVALUES (ITEMID, ONDATE, IVALUE) VALUES (1, '2000-01-01 00:00:00', 1);
INSERT INTO ITEMVALUES (ITEMID, ONDATE, IVALUE) VALUES (1, '2000-02-01 00:00:00', 2);
INSERT INTO ITEMVALUES (ITEMID, ONDATE, IVALUE) VALUES (2, '2000-01-01 00:00:00', 1.5);
INSERT INTO ITEMVALUES (ITEMID, ONDATE, IVALUE) VALUES (4, '2000-03-01 00:00:00', 4);

ALTER TABLE ITEMS ADD CONSTRAINT PK_ITEMS PRIMARY KEY (ITEMID);
ALTER TABLE ITEMVALUES ADD CONSTRAINT FK_ITEMVALUES FOREIGN KEY (ITEMID) REFERENCES ITEMS (ITEMID);

SET TERM ^ ;

CREATE PROCEDURE SP_GET
RETURNS (
ITEMID INTEGER,
NAME VARCHAR(128),
IVALUE DOUBLE PRECISION,
IONDATE DATE)
AS
begin
for select itemid, name from items into :ITEMID, :NAME
do begin
IVALUE = null;
IONDATE = null;
select iv.ivalue, iv.ondate from itemvalues iv
where iv.itemid=:ITEMID and iv.ondate=
(select max(ondate) from itemvalues
where itemid=:ITEMID and ondate<='1.1.2005')
into :IVALUE, :IONDATE;
suspend;
end
end
^

SET TERM ; ^

2Johnmen:
авторЭто не грабли.
То есть, ты хочешь, сказать, что так быть и должно?
авторТебе просто надо понять
1. что такое iv и зачем оно нужно
2. таблица во внешнем запросе и во вложенном с т.з. самого запроса - разные таблицы
А что конкретно надо понять? Чё-то я тебя недопонял.

2Мимопроходящий: спасибо, хотя вот Johnmen говорит, что это не грабли. и кому в этом случае верить?
обойти-то я обошёл, правда прок стал работать медленнее

1.5.0.4306

ok - скачаю.
теперь новый прикол:

на таблице itemvalues индекс IV_IDX(ITEMID, ONDATE), в самой таблице ~3500000 записей.

для коррелированного и некоррелированного подзапросов выходит разное время выполнения:

select iv.ivalue, iv.ondate from itemvalues iv
where iv.itemid=1 and iv.ondate=
(select max(ondate) from itemvalues
where itemid=iv.itemid and ondate<='1.1.2005')


Plan
PLAN (ITEMVALUES INDEX (IV_IDX))
PLAN (IV INDEX (IV_IDX))

Adapted Plan
PLAN (ITEMVALUES INDEX (IV_IDX)) PLAN (IV INDEX (IV_IDX))

------ Performance info ------
Prepare time = 0ms
Execute time = 735ms
Avg fetch time = 735,00 ms
Current memory = 1 726 128
Max memory = 2 504 592
Memory buffers = 2 048
Reads from disk to cache = 0
Writes from cache to disk = 6
Fetches from cache = 254 657


select iv.ivalue, iv.ondate from itemvalues iv
where iv.itemid=1 and iv.ondate=
(select max(ondate) from itemvalues
where itemid=1 and ondate<='1.1.2005')

Plan
PLAN (ITEMVALUES INDEX (IV_IDX))
PLAN (IV INDEX (IV_IDX))

Adapted Plan
PLAN (ITEMVALUES INDEX (IV_IDX)) PLAN (IV INDEX (IV_IDX))

------ Performance info ------
Prepare time = 0ms
Execute time = 16ms
Avg fetch time = 16,00 ms
Current memory = 1 652 648
Max memory = 2 504 592
Memory buffers = 2 048
Reads from disk to cache = 0
Writes from cache to disk = 6
Fetches from cache = 838

В первом случае он чуть ли не весь кэш перелопатил - можно подумать, что индекс не использовался, хотя пишет, что таки использовался.
Как такое можно объяснить?

авторОчень просто. В первом случае поздапрос выполняется для каждой записи внешнего. Во втором - один раз.
:)
дык и в первом, и во втором случае одна запись возвращается.

я так думаю, что сервер идёт по индексу и для каждой записи c itemid=1 (а их порядка 300) выполняет один и тот же подзапрос. Получается, что оптимизатор не выполнил свою основную задачу? или как?

2mv:
ты что, как можно? зайцы - это для меня святое :)

2Johnmen:
умеешь ты, однако, загадками говорить... :)

ага - значит таки оптимизатор криво работает. тогда на этом вопрос закрыт

ну и где же тут оптимальность его работы? два запроса, возвращающие одну и ту же запись, выполняются один быстрее, другой медленнее.
обоснуй, плиз, своё утверждение.

ты вот с этим
авторя так думаю, что сервер идёт по индексу и для каждой записи c itemid=1 (а их порядка 300) выполняет один и тот же подзапрос
согласен? если нет, объясни, где я неправ

авторНу нельзя же так тупить, ей богу !
туплю, кажися, не я :)

авторвложенный запрос выполняется СТОЛЬКО РАЗ, СКОЛЬКО ЗАПИСЕЙ в таблице itemvalues. - это ты погорячился.
сколько себя помню, всегда считал, что коррелированный подзапрос выполняется столько раз, сколько записей возвращает основной запрос

так что, борис - ты неправ

ну не может он выполняться для всех записей, не может. в таблице их больше 3 млн. Простой select count по этой таблице выполняется 3 сек, а этот меньше секунды. к тому же Perfomance Analysis показал, что было выполнено 100 с лишним тысяч чтений индекса, но никак не 3 млн и не NATURAL, если бы эта ботва выполнялась для ВСЕХ записей из таблицы.

авторТы говоришь про общую голую теорию безотносительно какого "типа" вложенный запрос.
а коррелированные подзапросы бывают разных типов?

авторНо если ты хочешь "подогнать" под неё, считай "столько раз, сколько записей возвращает основной запрос", в данном случае = сколько записей в таблице.
уточняю ещё раз: оба запроса (с коррелированным и некоррелированным подзапросами) возращают ОДНУ запись, ОДНУ! а не все 3 млн.

такое ощущение, что мы о разных вещах говорим.
ладно, Johnmen, давай по-другому.
вот тебе запрос:

select iv.ivalue, iv.ondate from itemvalues iv
where iv.itemid=1 and iv.ondate=
(select max(ondate) from itemvalues
where itemid=iv.itemid and ondate<='1.1.2005')

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

2mv: спасибо, я уже просёк, что от тебя ничего конструктивного не дождёшься

ну наконец-то

автордля внешнего запроса сканируется индекс IV_IDX для условия iv.itemid=1. это даёт не 1-у запись, а много больше
на самом деле это даёт одну запись, хотя перебирает он их все в процессе сканирования индекса(их у меня 355 для itemid=1)

ну а дальше
автордля каждой полученной записи выполняется кореллированный подзапрос
и фильтрует их по условию iv.ondate = (select max(ondate) ...
вот о чём я и говорю: он 355 раз выполняет один и тот же подзапрос.
355*355=126025. аналайзер показал 126380 чтений индекса - как раз где-то так и выходит. порядка 300 чтений серверу надо чтобы по дереву добраться до itemid=1.

авторПроблемы оптимизатора в том, что он не понимает того, что подзапрос на самом деле не кореллированный, т.е. не комбинирует условие корелляции itemid=iv.itemid с условием внешнего запроса iv.itemid=1. Не уверен, что он
должен это делать ;)
получается, что проблемы таки есть. то бишь оптимизатор не оправдывает возложенных на него надежд. собственно, проверять коррелированный подзапрос на некоррелированность от него и не требуется.
нужно всего лишь проверить, изменились ли параметры для вложенного подзапроса при переборе записей основного, и выполнять подзапрос только в том случае, если изменение имело место. а так получается мартышкин труд.
так это у меня и данных-то немного, а положим что их количество вырастет в несколько раз - так тут такие тормоза начнутся, что мама не горюй

JohnmenУчти в своих рассуждениях ещё один существенный в данном случае момент.
Булево выражение вычисляется не слева-направо, а справа-налево.
а это здесь каким боком?

ничего не поменялось - всё по-прежнему. а какие тут могут быть проблемы?