View: Ignore a left join if it is not used?
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.