SQL & PL/SQL

Greetings,

Using Oracle Database 19c Enterprise Edition Release 19.0.0.0.0

The cost of my original query is very high. I create a sample data to show the data and query solution.

/*
drop table work_name;
drop table sampleData;
drop table sampleCount;
*/

create table work_name(
work_name_id number,
work_name varchar2(20)
);
insert into work_name(work_name_id,work_name)
values(100,'Chiclayo');
insert into work_name(work_name_id,work_name)
values(101,'Reque');
insert into work_name(work_name_id,work_name)
values(102,'Monsefu');
insert into work_name(work_name_id,work_name)
values(103,'Callanca');

create table sampleData(
sample_id number,
fieldA number,
fieldB varchar2(20),
fieldC number
);

insert into sampleData(sample_id,fieldA,fieldB,fieldC)
values(1,1,'Car 1',60);
insert into sampleData(sample_id,fieldA,fieldB,fieldC)
values(2,0,'Car 2',70);
insert into sampleData(sample_id,fieldA,fieldB,fieldC)
values(3,1,'Car 3',70);
insert into sampleData(sample_id,fieldA,fieldB,fieldC)
values(4,1,'Car 4',60);
insert into sampleData(sample_id,fieldA,fieldB,fieldC)
values(5,0,'Car 5',70);
insert into sampleData(sample_id,fieldA,fieldB,fieldC)
values(6,0,'Car 6',60);

create table sampleCount(
sample_id number,
fy number,
work_id number,
work_name_id number
);

insert into sampleCount (sample_id,fy,work_id,work_name_id)
values (1,2013,1,100);
insert into sampleCount (sample_id,fy,work_id,work_name_id)
values (1,2014,1,100);
insert into sampleCount (sample_id,fy,work_id,work_name_id)
values (1,2015,1,100);
insert into sampleCount (sample_id,fy,work_id,work_name_id)
values (1,2013,2,102);
insert into sampleCount (sample_id,fy,work_id,work_name_id)
values (1,2014,2,102);
insert into sampleCount (sample_id,fy,work_id,work_name_id)
values (1,2015,2,102);

insert into sampleCount (sample_id,fy,work_id,work_name_id)
values (2,2013,2,102);
insert into sampleCount (sample_id,fy,work_id,work_name_id)
values (2,2014,2,102);
insert into sampleCount (sample_id,fy,work_id,work_name_id)
values (2,2015,2,102);
insert into sampleCount (sample_id,fy,work_id,work_name_id)
values (2,2013,3,101);
insert into sampleCount (sample_id,fy,work_id,work_name_id)
values (2,2014,3,101);
insert into sampleCount (sample_id,fy,work_id,work_name_id)
values (2,2015,3,101);

insert into sampleCount (sample_id,fy,work_id,work_name_id)
values (4,2013,1,103);
insert into sampleCount (sample_id,fy,work_id,work_name_id)
values (4,2014,1,103);
insert into sampleCount (sample_id,fy,work_id,work_name_id)
values (4,2015,1,103);
insert into sampleCount (sample_id,fy,work_id,work_name_id)
values (4,2013,1,102);
insert into sampleCount (sample_id,fy,work_id,work_name_id)
values (4,2014,1,102);
insert into sampleCount (sample_id,fy,work_id,work_name_id)
values (4,2015,1,102);
insert into sampleCount (sample_id,fy,work_id,work_name_id)
values (4,2013,4,100);
insert into sampleCount (sample_id,fy,work_id,work_name_id)
values (4,2014,4,100);
insert into sampleCount (sample_id,fy,work_id,work_name_id)
values (4,2015,4,100);

insert into sampleCount (sample_id,fy,work_id,work_name_id)
values (5,2013,3,101);
insert into sampleCount (sample_id,fy,work_id,work_name_id)
values (5,2014,3,101);
insert into sampleCount (sample_id,fy,work_id,work_name_id)
values (5,2015,3,101);

insert into sampleCount (sample_id,fy,work_id,work_name_id)
values (5,2013,4,103);
insert into sampleCount (sample_id,fy,work_id,work_name_id)
values (5,2014,4,103);
insert into sampleCount (sample_id,fy,work_id,work_name_id)
values (5,2015,4,103);

The query:

with split_data as (
  select sample_id, work_id,
  case when count(distinct c.work_name_id) > 1 then 'SPLIT' else max(n.work_name) end as work_name
  from sampleCount c
  join work_name n on n.work_name_id = c.work_name_id
  group by sample_id, work_id
)
,got_all_data as (
  select a.sample_id, decode(fieldA,1,'TRUE','FALSE') as car_flag, fieldB, fieldC, work_id, c.work_name
  from sampleData a
  left join split_data c on c.sample_id = a.sample_id
)
select * from got_all_data
pivot (max (work_name)
  for work_id in (  1 as oil_map
                  , 2 as tired_map
                  , 3 as clean_map
                  , 4 as engine_map)
     )
order by fieldB;

The result:

SAMPLE_ID	CAR_FLAG	FIELDB	FIELDC	OIL_MAP	TIRED_MAP	CLEAN_MAP	ENGINE_MAP
1	TRUE	Car 1	60	Chiclayo	Monsefu		
2	FALSE	Car 2	70		Monsefu	Reque	
3	TRUE	Car 3	70				
4	TRUE	Car 4	60	SPLIT			Chiclayo
5	FALSE	Car 5	70			Reque	Callanca
6	FALSE	Car 6	60				

Is there a better way of doing this query?

Thanks! ~Johnny