View: Ignore a left join if it is not used? I have a web service that references a view called gis_sidewalks_vw.

create table gis_sidewalks (
    id number(10,0),
    last_edited_date date
    );
insert into gis_sidewalks (id, last_edited_date) values (1, TO_DATE('2019/01/01 00:00:00', 'yyyy/mm/dd hh24:mi:ss'));
insert into gis_sidewalks (id, last_edited_date) values (2, TO_DATE('2019/02/01 00:00:00', 'yyyy/mm/dd hh24:mi:ss'));
insert into gis_sidewalks (id, last_edited_date) values (3, TO_DATE('2019/03/01 00:00:00', 'yyyy/mm/dd hh24:mi:ss'));
insert into gis_sidewalks (id, last_edited_date) values (4, TO_DATE('2019/04/01 00:00:00', 'yyyy/mm/dd hh24:mi:ss'));
commit;

create table maximo_assets (
    id number(10,0),
    lastsyncdate date
    );
insert into maximo_assets (id, lastsyncdate) values (1, TO_DATE('2019/04/01 00:00:00', 'yyyy/mm/dd hh24:mi:ss'));
insert into maximo_assets (id, lastsyncdate) values (2, TO_DATE('2019/03/01 00:00:00', 'yyyy/mm/dd hh24:mi:ss'));
insert into maximo_assets (id, lastsyncdate) values (3, TO_DATE('2019/02/01 00:00:00', 'yyyy/mm/dd hh24:mi:ss'));
insert into maximo_assets (id, lastsyncdate) values (4, TO_DATE('2019/01/01 00:00:00', 'yyyy/mm/dd hh24:mi:ss'));
commit;

create or replace view gis_sidewalks_vw as (
select
    s.id,
    s.last_edited_date as gis_last_edited_date,
    a.lastsyncdate as maximo_lastsyncdate,
    case 
        when s.last_edited_date &gt; a.lastsyncdate then 1
    end sync_needed
from
    gis_sidewalks s
left join
    maximo_assets a
    on s.id = a.id
);

select * from gis_sidewalks_vw;

         ID GIS_LAST_EDITED_DATE MAXIMO_LASTSYNCDATE SYNC_NEEDED
 ---------- -------------------- ------------------- -----------
          1 01-JAN-19            01-APR-19                      
          2 01-FEB-19            01-MAR-19                      
          3 01-MAR-19            01-FEB-19                     1
          4 01-APR-19            01-JAN-19                     1


The view has a left join and a calculated column:

case 
    when s.last_edited_date &gt; a.lastsyncdate then 1
end sync_needed

...

left join
    maximo_assets a




Scenario:

The view &amp; the web service are multi-purpose.

Purpose #1:

Serve up only the rows where sync_needed = 1 to a cron task (synced weekly to a separate database).

Purpose #2:

Serve up all the rows in the view to a web map (map is in constant use).



Problem:

In purpose #1, it makes sense to join to the maximo_assets table and generate the calculated column.

However, in purpose #2, it does not make sense to join to the maximo_assets table and generate the calculated column.

Unsurprisingly, with purpose #2, I am experiencing performance issues in the web map due to the unnecessary join.



Question:

Is there a way to design the view so that it ignores the join to the maximo_assets table if the join is not being used?

For example:

select
    id,
    gis_last_edited_date
    --maximo_lastsyncdate
    --sync_needed
from
    gis_sidewalks_vw
Think about it. Such a join without further information can not be skipped. Even if you do not select columns from maximo_assets, the join to it may increase the number of rows returned, because for 1 ID in gis_sidewalks, you can have multiple rows in maximo_assets with the same ID.

If the IDs are unique (or PK), and you make this known to the database by creating the proper constraints, then the above is not true anymore, and it will be able to skip the maximo_assets table:

alter table gis_sidewalks add primary key (id);
alter table maximo_assets add primary key (id);

select
    id,
    gis_last_edited_date--,
    --maximo_lastsyncdate,
    --sync_needed
from
    gis_sidewalks_vw;

        ID GIS_LAST_
---------- ---------
         1 01-JAN-19
         2 01-FEB-19
         3 01-MAR-19
         4 01-APR-19

SQL&gt; select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  f4p3b2huhfk5a, child number 0
-------------------------------------
select     id,     gis_last_edited_date--,     --maximo_lastsyncdate,
  --sync_needed from     gis_sidewalks_vw

Plan hash value: 1031306697

-----------------------------------------------------------------------------------
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS FULL| GIS_SIDEWALKS |     4 |    88 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


The performance with the subquery you posted will not scale well.

Also, multi-purpose SQL is the root of a lot of performance problems, so handle with care.