| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Some weird behavior with a collection in a subquery.
I have no experience at all with collections in in-lists,
but in general, in-lists can be resolved in three ways by Oracle:
- using the INLIST ITERATOR (most of the time the most efficient path) - in-list expansion, so it becomes sort of a repeated UNION ALL - apply the in-list afterwards as a filter
the first two approaches can be prevented, and the third one can be forced. hope this helps,
kind regards,
Lex.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On
Behalf Of Vlad Sadilovskiy
Sent: Friday, June 10, 2005 18:40
To: oracle-l
Subject: Fwd: Some weird behavior with a collection in a subquery.
Hello.
asktom is busy I guess,
Let me try my luck here.
Can you please explain
Artificial setup environment can be achieved by running following:
create table t1 as select * from all_objects;
create unique index t1_i on t1(object_id);
create table t2 as select * from all_objects;
create index t2_i on t2(object_id);
update t1 set status =3D 0;
update t2 set status =3D 0;
create or replace type table_of_number as table of number; /
create or replace function getnumericlist(card_n number) return table_of_nu= mber as
l_numeric_list table_of_number;
begin
select object_id
bulk collect into l_numeric_list
from (select distinct object_id from t1)
where rownum <=3D card_n;
return l_numeric_list;
end getnumericlist;
/
begin
dbms_stats.delete_table_stats(null, 't1'); dbms_stats.gather_table_stats(null, 't1',
estimate_percent =3D> 100,
method_opt =3D> 'for all columns size 254',
cascade =3D> true);
dbms_stats.delete_table_stats(null, 't2'); dbms_stats.gather_table_stats(null, 't2',
estimate_percent =3D> 100,
method_opt =3D> 'for all columns size 254',
cascade =3D> true);
Test query:
select count(*) from (
select t1.object_name,t1.object_type, t2.object_name from t1, t2 where
t1.object_id in (select /*+ cardinality(nlist 1000) */ *
from table(cast(getnumericlist(1000) as
table_of_number)) nlist)
and t2.object_id =3D t1.object_id and t1.status =3D 0 and t2.status =3D 0);
(for proper test use same value for getnumericlist function as for cardinal= ity parameter)
Plan for low carinality of the collection: Execution Plan
3 2 NESTED LOOPS (Cost=3D71 Card=3D10 Bytes=3D160) 4 3 NESTED LOOPS (Cost=3D51 Card=3D10 Bytes=3D90) 5 4 SORT (UNIQUE) 6 5 COLLECTION ITERATOR (PICKLER FETCH) OF 'GETNUMERICLIST= ' 7 4 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=3D1 Card=3D1 Bytes=3D7) 8 7 INDEX (UNIQUE SCAN) OF 'T1_I' (UNIQUE) 9 3 INDEX (RANGE SCAN) OF 'T2_I' (NON-UNIQUE) (Cost=3D1 Card=
Offending plan for collection cardinality > 13 (my case.. magic number!? :)=
) :
Execution Plan
3 2 NESTED LOOPS (Cost=3D93 Card=3D1 Bytes=3D16) 4 3 HASH JOIN (SEMI) (Cost=3D91 Card=3D1 Bytes=3D9) 5 4 TABLE ACCESS (FULL) OF 'T1' (Cost=3D51 Card=3D30295 Bytes=3D212065) 6 4 COLLECTION ITERATOR (PICKLER FETCH) OF 'GETNUMERICLIST' 7 3 INDEX (RANGE SCAN) OF 'T2_I' (NON-UNIQUE) (Cost=3D1 Card=
As you can see the cardinality of the outcome from collection and T1 is 1. It is always evaluated as 1 regardless of the collection cardinality. In fact it = is evaluated as "card(t1) * selectivity(t1.object_id)" from 10053 trace.
Result of the offending query with collection cardinality =3D 100000
Statistics
6 recursive calls
0 db block gets
55746 consistent gets
0 physical reads
0 redo size
211 bytes sent via SQL*Net to client
344 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
1 rows processed
Probably one can predict that hash join on T2 reduces LIOs substantially:
Execution Plan
2 1 HASH JOIN (Cost=3D143 Card=3D1 Bytes=3D16) 3 2 HASH JOIN (SEMI) (Cost=3D91 Card=3D1 Bytes=3D9) 4 3 TABLE ACCESS (FULL) OF 'T1' (Cost=3D51 Card=3D30295 Bytes=212072)
=3D212065)
5 3 COLLECTION ITERATOR (PICKLER FETCH) OF 'GETNUMERICLIST' 6 2 TABLE ACCESS (FULL) OF 'T2' (Cost=3D51 Card=3D30296 Bytes=3D=
Statistics
6 recursive calls
0 db block gets
456 consistent gets
0 physical reads
0 redo size
211 bytes sent via SQL*Net to client
344 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
1 rows processed
2. This doesn't happen with real tables. Is it possible to suppress semi-jo= in for this particular type of queries?
Side note: always_semi_join=3Doff isn't working for me in some versions (9.2.0.5+SA #68, 9.2.0.6); 10.1.0.2 works by I'd rather find the conceptual workaround that will allow legitimate semi-joins take place.
Thanks.
P.S. after posting this found that always_semi_join obsolete and substituted with _always_semi_join, so please disregard note part.
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Sat Jun 11 2005 - 14:21:30 CDT
 |
 |