View: Ignore a left join if it is not used?

Question

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 > 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 > a.lastsyncdate then 1
end sync_needed

...

left join
    maximo_assets a

Scenario:

The view & 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

Balazs Papp · Accepted Answer · 2019-09-28T23:34:09.180

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> 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.

User1974 · Answer 2 · 2019-10-01T15:29:43.617

It looks like using a subquery instead of a join might do the trick:

create or replace view gis_sidewalks_vw_2 as (
select
    s.id,
    s.last_edited_date as gis_last_edited_date,
    case when
        s.last_edited_date > (select lastsyncdate from maximo_assets a where s.id = a.id) then 1
    end as sync_needed
from
    gis_sidewalks s
);


select
    id,
    gis_last_edited_date
from
    gis_sidewalks_2

Of course, a subquery would be much slower than a proper join.

But that would only effect purpose #1, not purpose #2.

score 0 · Answer 3 · answered Oct 02 '19 at 15:35

For what it's worth, the real table that this question applies to is the asset table in Maximo 7.6.1.1.

The asset table in Maximo does not have a unique index on the assetnum column (nor a primary key on assetnum).

However, it does have a unique index on the assetnum and the siteid columns.

While the index isn't exactly what I want, perhaps it will help somewhat.
If I add a WHERE clause to the underlying view (where siteid = 'SERVICES') then the query in question only references the index; it does not do a full table scan.

create or replace view gis_sidewalks_vw as (
select
    s.id,
    s.last_edited_date as gis_last_edited_date,
    a.changedate as maximo_lastsyncdate,
    case 
        when s.last_edited_date > a.changedate then 1
    end sync_needed
from
    gis_sidewalks s
left join
    asset a
    on s.id = a.assetnum
where
    siteid = 'SERVICES'
);

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

View: Ignore a left join if it is not used?

Scenario:

Problem:

Question:

3 Answers3