Well, in fact I need to perform "dummy" union with joins; but I cant use
union, because this
select is incapsulated into view, and so, first of all it will union
tables, ant only then will do outer filtering (and, I suppose, without
indices)
Maybe I am wrong ?

sample :
TB1 (ID1,DATE1),TB2(ID2,DATE2). ID,DATE1/2 - indexed.
this would be what I need :

select tb1.id,tb1.date1 from tb1
where date1 > '2000.01.01'
union
select tb2.id2,tb2.date2 from tb2
where date2 > '2000.01.01'.

date1/2 is indexed field, so results are instant.

But, because of some reason, we have to incapsulate this selection into
view :
create view V_A1
(
ID,
DATE0
)
AS
select tb1.id,tb1.date1 from tb1
union
select tb2.id2,tb2.date2 from tb2.

And now,
select * V_A1 where date0 > '2000.01.01'
is very, very slow....I looks, like FB decides first of all make union
and then filter it. I definitely know this does not happen with joins :).
That is why I need make "union" :) via joins :).
Adomas





Martijn Tonies wrote:

>
>
>>Yes, and reads from tables : count(tb1) * count(tb2) . no good.
>>
>>
>
>Well, given that you want the FULL results from BOTH tables,
>what do you expect?
>
>What is it that you're trying to do?
>
>>From the sample result, I'd say you want all rows in T1 and
>all rows in T2 and have NULLs for places where there's no
>existing match.
>
>Martijn Tonies
>Database Workbench - development tool for Firebird and more!
>Upscene Productions
>http://www.upscene.com
>My thoughts:
>http://blog.upscene.com/martijn/
>Database development questions? Check the forum!
>http://www.databasedevelopmentforum.com
>
>
>
>>Nick wrote:
>>
>>
>>
>>>select * from table1 full join table2 on id1 = id2
>>>
>>>Hi,
>>>
>>>I need simply to join 2 tables with join, where result is :
>>> TB1 (ID1) - ID - index
>>> 1
>>> 2
>>> 3
>>> TB2(ID2) - ID - index
>>> 4
>>> 5
>>> 6
>>>select .... :
>>> TB1.ID1 TB2.ID2
>>> 1 NULL
>>> 2 NULL
>>> 3 NULL
>>> NULL 4
>>> NULL 5
>>> NULL 6
>>>Any way to do it ?
>>>Conditions :
>>>NO SP, NO Union. (in real conditions and union and Sp is to slow
>>>because of size of tables (>2.5mill records.))
>>>
>>>
>
>
>
>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>Visit http://firebird.sourceforge.net and click the Resources item
>on the main (top) menu. Try Knowledgebase and FAQ links !
>
>Also search the knowledgebases at http://www.ibphoenix.com
>
>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>Yahoo! Groups Links
>
>
>
>
>
>
>
>
>
>

--

Pagarbiai / Best Regards,
Adomas Urbanavicius
+37068543336