SQL beginner here. I am a r/o user of a very large oracle 12 db with many tables that have to be combined to build the desired output. (using custom sql in tableau to build report) basic structure is;
Select
cus_fields
cus_loc_fields
ent_fields
cir_fields
from (
Select
cus_loc_fields
from (
Select
cus_fields
cus_loc_fields
from cus
inner join (
(select
cus_loc_fields
from cust_loc
union all
cus_loc_del_fields
from_cust_loc_del
) cus_loc
) on cus.cusnum = cus_loc.cusnum
) cus_loc
) cus_loc
left join (
(select
ent_fields
from
(select
ent_fields
from ent
union all
select
ent_del_fields
from ent_del
) ent
) ent
-
full outer join (
(select
cir_fields
from
(select
cir_fields
from cir
union all
select
cir_del_fields
from cir_del
) cir
) cir
) on ( ent.name = cir.name
and ent.shname = cir.shname )
-
) on cus_loc.name = ent.name
or cus_loc.name = cir.name
without the cir outer join, the tableau filter query to gather the list of customer names is in the seconds. Once I add the outer join in, the tableau filter query to gather the customer names takes hours. Everything works if i put a known customer name in the filter but still takes much longer.
furthermore I still need to left join 4 more tables to the cir table and 1 more left join to the ent table. In essence it becomes unusable
obviously i need some sort of an index, but how can i do this as a r/o user
