Adding union alters explain plan drastically

[MikeJones]

Hi all,

I have an SQL stetement that I have to union with another statement, I'm having trouble keeping the explain plan as I want it, it seems the cost based optomiser won't leave it alone!

The query is below, with the plan statement. This is without the UNION, it's working quite nicely. I've stuck rule hints everywhere to try and stop the cost based optomiser switching in and using any statistics there may be about the data. Sorry about the line wrapping...

select /*+ RULE */ fol_folder_title, fol_folder_id, fol_folder_no, 0 item_no, 0 item_id, 0 item_yn, 0 item
from nim_cfg_folders
where exists (select /*+ RULE ORDERED INDEX(nim_cfg_folder_items fit_c_pk) INDEX(nim_cfg_items ite_c_pk)
USE_NL(nim_cfg_folder_items nim_cfg_items) USE_NL(nim_cfg_items nim_sec_user_profiles)
USE_NL(nim_sec_user_profiles nim_sec_profile_accesses) */
1
from nim_cfg_folder_items,
nim_cfg_items,
nim_sec_user_profiles,
nim_sec_profile_accesses
where fit_folder_id = fol_folder_id
and fit_item_id = ite_item_id
and ite_web_dll_name is not null
and ite_briefcase_specific_yn = 'N'
and nvl(ite_access_method, pac_access_id) = pac_access_id
and pac_profile_id = upr_profile_id
and upr_user_id = :user_id
)
and exists (select /*+ RULE */ 1
from nim_sec_user_profiles,
nim_sec_profile_accesses
where pac_profile_id = upr_profile_id
and upr_user_id = :user_id
and nvl(fol_access_method,pac_access_id) = pac_access_id)


Explain plan...

SELECT STATEMENT Optimizer=HINT: RULE (Cost=1 Card=1 Bytes=18)
FILTER
TABLE ACCESS (FULL) OF NIM_CFG_FOLDERS (Cost=1 Card=1 Bytes=18)
NESTED LOOPS (Cost=109 Card=2 Bytes=162)
NESTED LOOPS (Cost=13 Card=48 Bytes=3504)
NESTED LOOPS (Cost=9 Card=2 Bytes=130)
INDEX (RANGE SCAN) OF FIT_C_PK (UNIQUE) (Cost=1 Card=8 Bytes=48)
TABLE ACCESS (BY INDEX ROWID) OF NIM_CFG_ITEMS (Cost=1 Card=2 Bytes=118)
INDEX (UNIQUE SCAN) OF ITE_C_PK (UNIQUE)
INDEX (RANGE SCAN) OF UPR_C_PK (UNIQUE) (Cost=2 Card=24 Bytes=192)
INDEX (RANGE SCAN) OF PAC_C_PK (UNIQUE) (Cost=2 Card=1576 Bytes=12608)
NESTED LOOPS (Cost=6 Card=17 Bytes=272)
INDEX (FULL SCAN) OF PAC_C_PK (UNIQUE) (Cost=6 Card=16 Bytes=128)
INDEX (UNIQUE SCAN) OF UPR_C_PK (UNIQUE)

If I add a union though it decides to get rid of the filters and turn it all into nested loops, I wish it wouldn't as most of the work is in an exists I only need it to get one row matching before allowing the parent row through for the union. Again sorry for the line wrapping...

*+ RULE */ fol_folder_title, fol_folder_id, fol_folder_no, 0 item_no, 0 item_id, 0 item_yn, 0 item
from nim_cfg_folders
where exists (select /*+ RULE ORDERED INDEX(nim_cfg_folder_items fit_c_pk) INDEX(nim_cfg_items ite_c_pk)
USE_NL(nim_cfg_folder_items nim_cfg_items) USE_NL(nim_cfg_items nim_sec_user_profiles)
USE_NL(nim_sec_user_profiles nim_sec_profile_accesses) */
1
from nim_cfg_folder_items,
nim_cfg_items,
nim_sec_user_profiles,
nim_sec_profile_accesses
where fit_folder_id = fol_folder_id
and fit_item_id = ite_item_id
and ite_web_dll_name is not null
and ite_briefcase_specific_yn = 'N'
and nvl(ite_access_method, pac_access_id) = pac_access_id
and pac_profile_id = upr_profile_id
and upr_user_id = :user_id
)
and exists (select /*+ RULE */ 1
from nim_sec_user_profiles,
nim_sec_profile_accesses
where pac_profile_id = upr_profile_id
and upr_user_id = :user_id
and nvl(fol_access_method,pac_access_id) = pac_access_id)
union
select /*+ RULE */ 'A' ite_item_title, 0 fol_folder_id, 0 fol_folder_no, 0 fit_item_no, 0 fit_item_id, 1 item_yn, 0 ite_item_id
from dual

SELECT STATEMENT Optimizer=HINT: RULE (Cost=17 Card=2002 Bytes=230)
SORT (UNIQUE) (Cost=17 Card=2002 Bytes=230)
UNION-ALL
NESTED LOOPS (Cost=10 Card=2 Bytes=230)
NESTED LOOPS (Cost=10 Card=32 Bytes=3424)
NESTED LOOPS (Cost=8 Card=2 Bytes=198)
NESTED LOOPS (Cost=8 Card=32 Bytes=2912)
NESTED LOOPS (Cost=6 Card=2 Bytes=166)
NESTED LOOPS (Cost=4 Card=2 Bytes=130)
TABLE ACCESS (BY INDEX ROWID) OF NIM_CFG_ITEMS (Cost=2 Card=2 Bytes=118)
INDEX (FULL SCAN) OF ITE_C_PK (UNIQUE) (Cost=1 Card=2)
INDEX (FULL SCAN) OF FIT_C_PK (UNIQUE) (Cost=1 Card=51 Bytes=306)
TABLE ACCESS (BY INDEX ROWID) OF NIM_CFG_FOLDERS (Cost=1 Card=7 Bytes=126)
INDEX (UNIQUE SCAN) OF FOL_C_PK (UNIQUE)
INDEX (FAST FULL SCAN) OF PAC_C_PK (UNIQUE) (Cost=1 Card=1576 Bytes=12608)
INDEX (UNIQUE SCAN) OF UPR_C_PK (UNIQUE)
INDEX (FAST FULL SCAN) OF PAC_C_PK (UNIQUE) (Cost=1 Card=1576 Bytes=12608)
INDEX (UNIQUE SCAN) OF UPR_C_PK (UNIQUE)
TABLE ACCESS (FULL) OF DUAL (Cost=1 Card=2000)


Any help would be greatly appriated.

Mike.

 

[karluk]

Have you tried moving the "select from dual" part of the union to the beginning? I'm not sure it would make a difference, but if you're stuck it may be useful to try something new.

 

[MikeJones]

Thanks Karluk, but I've tried that, I've also tried wrapping the 2 sql statements in another SQL statement so they appear to by inline views that I'm selecting everything from (if you follow my drift...).

Alas to no avail!

I'm currently going back turning it into a more explicit sql statement but trying to get the filtering back, I guess I'll let everyone know if I solve it this way.

Thanks again,

Mike