| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Strange behaviour of CBO in 9ir2
Thanks for the reply, hopefully my usenet connection will post this
reply apoligies if it appears many times.
> If you run a full explain plan against the query,
> you will see that the addition of the 'OR' clause
> has changed the filtering in a way that suggests
> that Oracle has done a smart optimisation in
> unnesting the inner queries and pushing predicates
> in the code without the OR, but cannot do the same
> in the code with the OR.
If I understand you right then, my problem isn't that the Query is executed incorrectly when I add the OR but that it can not perform the same optomisations with the OR? I'm surprised, see new example below.
> You haven't given us a value for bind variable i_volume,
> and the test case went ballistic when I tried '1000' with
> the OR clause.
Sorry, poor posting on my part. but 1000 was the value I would have given. I'm not entirely sure what a ballistic query looks like, but if it looks like one where the IO's (LIO + PIO) go up 427 times, then I see that too.
> You don't have a 'start with' on your 'connect by' query,
> is this deliberate ?
Yes, I didn't think it would "add" to the query but I will try this.
> If you want to avoid the problem can you do something
> like:
>
> select count(*) from
> (
> select -- that works well
> union
> select -- to take only the part from the OR branch
> )
>
I'm not sure I can... you see the OR will be another IN test, testing if another column in the main table is in a different set. basically you can have access one of two ways, via a security node structure or directly to that single record. I would end up trying to code
select count(*)
from main
where (security_colum_1 or security_column_2) "as a single column, 2
row set"
in
( select node_id
from that_main_sub_query_i_have
union
select sonmething_else
from another_query_that_would_be_the_or_set
)
and I don't know how I can do that.
I must confess I don't understand why the OR means that Oracle can't perform the same optomisation. I've switched off the cursor_sharing so you can see the values I used, :I_VOLUME was set to 1000 for both queries, I stuck a hint in so you can tell them apart. why has "or 1=0" invalidated the optomisation performed otherwise? can I get it back in any way?
Again a huge thanks for any help anyone can give.
Mike.
-=-=-=-=-=-=-=-=-=--=-==-=-=
<below from tkprof file>
select /* no or */ count(*)
from has_permission_unit_test main
where main.primary_key <= :i_volume
and
(
main.security_node_id in
( select np.node_id
from plt_security_nodes sn, plt_node_permissions np
where sn.node_id = np.node_id
and np.user_id in
( select user_id
from plt_user_delegates
where delegate_user_id = 'MJONES'
and sysdate between effective_from and effective_to
and is_deleted = 0
union all
select 'MJONES' user_id
from dual
call count cpu elapsed disk query current
rows
Parse 1 0.04 0.04 38 380 0
0
Execute 1 0.00 0.00 0 0 0
0
Fetch 2 0.34 0.62 2385 2417 0
1
total 4 0.38 0.66 2423 2797 0
1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 120
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=2417 r=2385 w=0 time=620691 us)
1000 HASH JOIN SEMI (cr=2417 r=2385 w=0 time=620232 us)
1000 TABLE ACCESS FULL HAS_PERMISSION_UNIT_TEST (cr=2370 r=2368
w=0 time=529161 us)
500 VIEW (cr=47 r=17 w=0 time=83096 us)
500 FILTER (cr=47 r=17 w=0 time=82652 us)
517 CONNECT BY WITH FILTERING (cr=36 r=16 w=0 time=71104 us)
511 COUNT (cr=18 r=16 w=0 time=54453 us)
511 HASH JOIN (cr=18 r=16 w=0 time=54038 us)
511 INDEX FAST FULL SCAN NODE_AND_USER (cr=6 r=5 w=0
time=12605 us)(object id 151390)
1520 INDEX FAST FULL SCAN NODE_AND_PARENT_NODE (cr=12 r=11
w=0 time=34185 us)(object id 151391)
6 HASH JOIN (cr=18 r=0 w=0 time=11525 us)
511 CONNECT BY PUMP (cr=0 r=0 w=0 time=486 us)
511 HASH JOIN (cr=18 r=0 w=0 time=8373 us)
511 INDEX FAST FULL SCAN NODE_AND_USER (cr=6 r=0 w=0
time=463 us)(object id 151390)
1520 INDEX FAST FULL SCAN NODE_AND_PARENT_NODE (cr=12 r=0
w=0 time=1241 us)(object id 151391)
1 UNION-ALL (cr=11 r=1 w=0 time=10808 us)
0 TABLE ACCESS BY INDEX ROWID PLT_USER_DELEGATES (cr=8 r=1
w=0 time=10704 us)
0 INDEX RANGE SCAN PUD_PK (cr=8 r=1 w=0 time=10691
us)(object id 129898)
1 FILTER (cr=3 r=0 w=0 time=53 us)
1 TABLE ACCESS FULL DUAL (cr=3 r=0 w=0 time=48 us)
********************************************************************************
select /* with or */ count(*)
from has_permission_unit_test main
where main.primary_key <= :i_volume
and
(
main.security_node_id in
( select np.node_id
from plt_security_nodes sn, plt_node_permissions np
where sn.node_id = np.node_id
and np.user_id in
( select user_id
from plt_user_delegates
where delegate_user_id = 'MJONES'
and sysdate between effective_from and effective_to
and is_deleted = 0
union all
select 'MJONES' user_id
from dual
call count cpu elapsed disk query current
rows
Parse 1 0.00 0.00 0 0 0
0
Execute 1 0.00 0.00 0 0 0
0
Fetch 2 8.69 8.55 2389 881488 0
1
total 4 8.69 8.55 2389 881488 0
1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 120
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=881488 r=2389 w=0 time=8556529 us)
1000 FILTER (cr=881488 r=2389 w=0 time=8556045 us)
1000 TABLE ACCESS FULL OBJ#(150327) (cr=2370 r=2367 w=0
time=195119 us)
428 FILTER (cr=879118 r=22 w=0 time=8348191 us) 83925 CONNECT BY WITH FILTERING (cr=879114 r=22 w=0 time=8285066 us)
218708 COUNT (cr=439557 r=22 w=0 time=3008455 us)
218708 NESTED LOOPS (cr=439557 r=22 w=0 time=2826941 us)
218708 INDEX FULL SCAN OBJ#(151390) (cr=1712 r=0 w=0
time=233959 us)(object id 151390)
218708 TABLE ACCESS BY INDEX ROWID OBJ#(129797) (cr=437845
r=22 w=0 time=1905374 us)
218708 INDEX UNIQUE SCAN OBJ#(129895) (cr=219137 r=10 w=0
time=940217 us)(object id 129895)
2568 HASH JOIN (cr=439557 r=0 w=0 time=3957559 us)
218708 CONNECT BY PUMP (cr=0 r=0 w=0 time=190729 us)
218708 NESTED LOOPS (cr=439557 r=0 w=0 time=2797125 us)
218708 INDEX FULL SCAN OBJ#(151390) (cr=1712 r=0 w=0
time=238910 us)(object id 151390)
218708 TABLE ACCESS BY INDEX ROWID OBJ#(129797) (cr=437845 r=0
w=0 time=1879401 us)
218708 INDEX UNIQUE SCAN OBJ#(129895) (cr=219137 r=0 w=0
time=927899 us)(object id 129895)
1 UNION-ALL (cr=4 r=0 w=0 time=77 us)
0 TABLE ACCESS BY INDEX ROWID OBJ#(129800) (cr=1 r=0 w=0
time=16 us)
0 INDEX RANGE SCAN OBJ#(129898) (cr=1 r=0 w=0 time=14
us)(object id 129898)
1 FILTER (cr=3 r=0 w=0 time=49 us)
1 TABLE ACCESS FULL OBJ#(222) (cr=3 r=0 w=0 time=47 us)
Received on Fri Nov 12 2004 - 08:46:58 CST
 |
 |