it requires me to and use a table of N number of rowsWhat exactly is the problem with this? A real table has advantages, such as you can gather stats on it.
Unless this is causing other problems you've not mentioned, I'm not seeing why you're objecting to using a solution that works.
You could still use CONNECT BY DUAL in other, simpler queries if you insist.
So I thought I would ask here in case you are already aware of any known issue with such behavior. I've chatted about this with Nigel Bayliss, optimizer PM. We think it's a side-effect of so many things going on this in query, not a bug with JPPD.
When using a real table and preventing view merging with query block hints:
create or replace view as_dim_view as select * from as_dimension asd, table(cast(multiset(select /*+ qb_name(MYQB) */ num_val from temp_table where num_val <= length(asd.username)) as sys.odcinumberlist)) asd_t ; (edited)
explain plan for
select /*+ NO_MERGE(f) NO_MERGE(d) NO_MERGE(@MYQB) */ *
from as_fact_sqlview f
left outer join as_dim_view d on d.username = f.owner
where trunc(f.created) = to_date('17-FEB-2020','DD-MON-YYYY') ;
You should see the same plan as CONNECT BY DUAL. This appears to prevent simple view merging. Meaning JPPD is not an option later on.