These is a prototype of the actul long query. Table p1 foreign key is fid, not all rows in Table f1 have matching rows in p1. So what we want to display is all rows
whather or not they have a matching row in the f1 table. Well the simplest answer is an outer join. But this will not work, the reason being, if you look at the view
we pass in fstart and otime, the reason for this, both p1 and f1 are partitioned and hence make use of partition pruning we pass both values. So even if we use an outer join,
since otime will be null if there was no matching rcords in the f1 table, they will be eliminated based on the view predicate definition. Partition pruning happens properly
on the main query on the view above the "union all", but the subquery for the "not exists" ideally I want to partition prune like all the other querries from the precidates
passed on from the view. Is there any way to make sure that the otime passed in the view predicate is being used by the sub-query.
create or replace view view_test as
select f.fid, aid, f.fstart, sal, pid, otime, website from f1 f, p1 p where
f.f1 = p.f1
union all
select f2.fd, aid, fstart, sal, null, null, null from f1 f2
WHERE NOT EXISTS (SELECT NULL
FROM p1 p2
WHERE p2.fid = f2.fid)
select * from view_test where fstart >= to_date('02/27/2008 23:25:26', 'mm/dd/yyyy hh24:mi:ss') and fstart <= to_date('02/27/2008 23:27:26', 'mm/dd/yyyy hh24:mi:ss')
and otime >= to_date('02/27/2008 23:25:26', 'mm/dd/yyyy hh24:mi:ss') and otime <= to_date('02/27/2008 23:27:26', 'mm/dd/yyyy hh24:mi:ss');
-- This is what happens if we used literals instead of the view
select f.fid, aid, f.fstart, sal, pid, otime, website from f1 f, p1 p where
f.f1 = p.f1
fstart >= to_date('02/27/2008 23:25:26', 'mm/dd/yyyy hh24:mi:ss') and fstart <= to_date('02/27/2008 23:27:26', 'mm/dd/yyyy hh24:mi:ss')
and otime >= to_date('02/27/2008 23:25:26', 'mm/dd/yyyy hh24:mi:ss') and otime <= to_date('02/27/2008 23:27:26', 'mm/dd/yyyy hh24:mi:ss')
union all
select f2.fd, aid, fstart, sal, null, null, null from f1 f2
WHERE NOT EXISTS (SELECT NULL
FROM p1 p2
WHERE p2.fid = f2.fid)
and fstart >= to_date('02/27/2008 23:25:26', 'mm/dd/yyyy hh24:mi:ss') and fstart <= to_date('02/27/2008 23:27:26', 'mm/dd/yyyy hh24:mi:ss');
-- But what we want to happen is something like this
select f.fid, aid, f.fstart, sal, pid, otime, website from f1 f, p1 p where
f.f1 = p.f1
fstart >= to_date('02/27/2008 23:25:26', 'mm/dd/yyyy hh24:mi:ss') and fstart <= to_date('02/27/2008 23:27:26', 'mm/dd/yyyy hh24:mi:ss')
and otime >= to_date('02/27/2008 23:25:26', 'mm/dd/yyyy hh24:mi:ss') and otime <= to_date('02/27/2008 23:27:26', 'mm/dd/yyyy hh24:mi:ss')
union all
select f2.fd, aid, fstart, sal, null, null, null from f1 f2
WHERE NOT EXISTS (SELECT NULL
FROM p1 p2
WHERE p2.fid = f2.fid and otime >= to_date('02/27/2008 23:25:26', 'mm/dd/yyyy hh24:mi:ss') and otime <= to_date('02/27/2008 23:27:26', 'mm/dd/yyyy hh24:mi:ss')
and fstart >= to_date('02/27/2008 23:25:26', 'mm/dd/yyyy hh24:mi:ss') and fstart <= to_date('02/27/2008 23:27:26', 'mm/dd/yyyy hh24:mi:ss');