Hi all,
Calling multiple views from a view, myView
create or replace view myView as
With ratesM as (
select columns from tableRates and join other tables
)
,firstData as (
select columns, sum(column) amt from view_A and join ratesM table and other tables
group by columns
)
select id, columns, sum (column) from view_B and join firstData and join another view_C
group by column
The view_B has /*+ no_merge */ hint on the select statement using also union all.
The view_B call also another view_C that has /*+ no_push_pred */ hint.
If I called myView with id = 34 (select * from myQuery where id = 34) and it takes 6 secs for 270 rows. That is the average of records per each id.
The myView without id should capture total 10 group ids with total of 3100 records aprox, but it takes over 6 mins.
Is there any hint that I can use to improve myView ?
Thanks, ~Johnny
Using Oracle Database 19c Enterprise Edition Release 19.0.0.0.0