Re: Join or subselect

#1

Hy!

I think when you got a select in the in() Statement you are allowed to have
more than 1000 elements.

Ronny

"Laszlo Bartos" <laszlo.bartos (AT) empolis (DOT) hu> schrieb im Newsbeitrag
news:84cf6678.0403170301.55c219bb (AT) posting (DOT) google.com...

Quote:

Hi,

I have the following ugly SQL query:

select
T.sl_tr_id, T.sl_tr_container_flag, SL_Legal_Data_OBJ.sl_obj_wfst_id,
SL_Legal_Data_OBJ.sl_obj_check_out_flag,
SL_Legal_Data_OBJ.sl_obj_link_indicator,
SL_Legal_Data_OBJ.sl_obj_objtyp_id, '0', '1',
SL_Legal_Data_OBJ.sl_obj_check_out_by,
SL_Legal_Data_OBJ.sl_obj_content_flag,
SL_Legal_Data_OBJ.sl_obj_reuse_flag, T.sl_tr_splitnode_flag,
SL_Legal_Data_OBJ.sl_obj_note_flag, T.sl_obj_id, P.sl_tr_path,
T.sl_tr_display_title, SL_A.part_number, SL_B.type,
SL_A.authority, SL_B.date_, SL_B.number_, SL_A.region,
SL_C.version_num, SL_D.date2, SL_D.RV_number, SL_E.parties,
SL_A.cldb_stat, SL_B.in_cldb, SL_A.cldbid, SL_A.edbid,
SL_C.annex_num, SL_F.author, SL_G.sl_obj_check_out_date,
SL_G.sl_obj_last_changed_date, SL_G.sl_obj_wfst_id,
SL_G.sl_obj_last_changed_by, SL_G.sl_obj_check_out_by
from
SL_Legal_Data_OBJ, SL_Legal_Data_TREE T, SL_Legal_Data_TREE P,
SL_Legal_Data_METADATA SL_A, SL_Legal_Data_BASIC SL_B,
SL_Legal_Data_LEGISLATION SL_C, SL_Legal_Data_JURISPR SL_D,
SL_Legal_Data_COLL_JURISP SL_E, SL_Legal_Data_AUT_JUR_LEG SL_F,
SL_Legal_Data_OBJ SL_G
where
T.sl_tr_id IN (11,12,13,14,15,34,56,78) and
SL_Legal_Data_OBJ.sl_obj_id = T.sl_obj_id and
T.sl_tr_parent = P.sl_tr_id and
SL_Legal_Data_OBJ.sl_obj_deleted_flag = 0 and
T.sl_tr_deleted_flag = 0 and
P.sl_tr_deleted_flag = 0 and
SL_A.sl_obj_id (+)= SL_Legal_Data_OBJ.sl_obj_id and
SL_B.sl_obj_id (+)= SL_Legal_Data_OBJ.sl_obj_id and
SL_C.sl_obj_id (+)= SL_Legal_Data_OBJ.sl_obj_id and
SL_D.sl_obj_id (+)= SL_Legal_Data_OBJ.sl_obj_id and
SL_E.sl_obj_id (+)= SL_Legal_Data_OBJ.sl_obj_id and
SL_F.sl_obj_id (+)= SL_Legal_Data_OBJ.sl_obj_id and
SL_G.sl_obj_id (+)= SL_Legal_Data_OBJ.sl_obj_id

As you see there are several outer joins in the query, but it works
ok.

The problem is that the numbers in the IN condition are generated
using another select statement:

select sl_tr_id from sl_search_result where sl_search_session_id = 1

It may occur that the size of the resultset is very long (I mean
longer than 1000) and it is not allowed to use more than 1000 numbers
in the IN condition.

What do you suggest?

I have tried several solutions, but both of them had a poor
performance. I'm not familiar with the db tuning and I do not have
enough db experiance.

First I tried simple subquery, so instead of:

T.sl_tr_id IN (11,12,13,14,15,34,56,78)

I used:

T.sl_tr_id IN (select sl_tr_id from sl_search_result where
sl_search_session_id = 1)

But Oracle changed the execution plan: earlier the query was executed
about 40-50 ms, and using the subselect it took 13 seconds)

I have tried to join to the sl_search_result table, I have tried
EXISTS: same result.

I'm sure that Oracle is able to execute this query quicker, only I do
not find the right way.

Thanks,

Laszlo Bartos

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: Join or subselect

comp.database.oracle comp.database.oracle

Re: Join or subselect - 04-26-2004 , 08:03 AM