Hello, community.
We faced some intresting issue with Oracle 11.2.0.3 EE.
We have created a type
create or replace type test_t as object ( test1 number, test2 number)
And two tables
create table test1 (
id number,
test2_id number,
test_value varchar2(25)
)
create table test2 (
id number,
test_value2 test_t
)
And then we wanted to join it by with clause and materilize hint
This one will create exactly that plan which we desired
with sql1 as
(select /*+ materialize */
t1.test_value, t2.id
from test1 t1 , test2 t2
where t1.test2_id = t2.id )
select * from sql1
But when we just add our column with type test_t (that we've created by own), plan doesn't meet our expectation
with sql1 as
(select /*+ materialize */
t1.test_value, t2.id, t2,test_value
from test1 t1 , test2 t2
where t1.test2_id = t2.id )
select * from sql1
What we are doing wrong? Or oracle doesn't support materialize hint (or maybe other also) for user-defined type at all?
Would be appreciate for any clue.