can we write this cursor in more efficent way

[sonuji_in]

have a cursor that looks like this



CURSOR resavations(i_sol_id IN NUMBER, i_warehouses_id IN NUMBER, i_dnl_ids IN NUMBER_ARRAY) IS

SELECT sold.SALES_ORDER_LINES_ID,

wat.warehouse_assortment_trans_id,

sold.style_details_id,

wwt.actual_quantity AS quantity,

dnl.dispatch_note_lines_id,

dnld.dispatch_note_line_details_id,

wwt.warehouses_id

FROM warehouse_assortment_trans wat,

warehouse_trans wt,

warehouse_trans_types wtt,

warehouse_warehouse_trans wwt,

sales_order_line_details sold,

logistics.dispatch_note_lines dnl,

logistics.dispatch_note_line_details dnld

WHERE wat.sales_order_line_details_id = sold.sales_order_line_details_id

AND wat.warehouse_trans_id = wt.warehouse_trans_id

AND wt.warehouse_trans_id = wwt.warehouse_trans_id

AND wt.warehouse_trans_types_id = wtt.warehouse_trans_types_id

AND wtt.transaction_type_name = 'Ready for Shipment'

AND sold.sales_order_line_details_id = dnld.sales_order_line_details_id

AND dnld.dispatch_note_lines_id = dnl.dispatch_note_lines_id

AND wwt.actual_quantity != 0

AND wwt.warehouses_id = i_warehouses_id

AND wat.sales_order_lines_id = i_sol_id

AND dnl.dispatch_note_lines_id IN (SELECT * FROM TABLE ( cast ( i_dnl_ids as NUMBER_ARRAY) ));



Where NUMBER_ARRAY is defined like the following

CREATE OR REPLACE

TYPE NUMBER_ARRAY AS TABLE OF NUMBER;



After I have looked at my pl/sql function I have found out that (SELECT * FROM TABLE ( cast ( i_dnl_ids as NUMBER_ARRAY) )) is what seems to be taking the time.

Because I if removed this and only put in one value like (i_dnl_ids IN NUMBER) instead of (i_dnl_ids IN NUMBER_ARRAY) it seems to be running better.

But this will of course not work cause of multiple values that I have to pass to the cursor.



Now my question is

Is there a better way to do this?

Or is this the only way?

Sonu

[sonuji_in]

hi
some more info for you
*****
You can get the details about this cursor in a package dispatch_note_pck.CONVERT_SOL_TO_DN_CLIENT_ARRAY
**************
CREATE OR REPLACE PACKAGE DISPATCH_NOTE_PCK AS
TYPE ref_cursor IS REF CURSOR;
FUNCTION CONVERT_SOL_TO_DN_CLIENT_ARRAY(p_users_id IN NUMBER, p_sol_id IN NUMBER_ARRAY, p_warehouses_id IN NUMBER_ARRAY, p_transport_decription_id IN NUMBER_ARRAY) RETURN ref_cursor;
-- Description
-- Used to convert sales order lines to dispatch notes and transfer them to BestPack version 2 for printing
-- Parematers
-- p_users_id : is the is for the user
-- p_sol_id : is a array of sales order lines that have to be converted to dispatch notes
-- p_warehouses_id : is a array of warehouses the sold have to be reserved on
-- p_transport_desription : is the transport description the dispatch notes have to have.
-- Return
-- ref_cusor with the id for the dispatch_note_line that have been made
END DISPATCH_NOTE_PCK;
***********
can we write this cursor in some other way.
sonu

[Scorby]

If the number of values in TABLE ( cast ( i_dnl_i )) is quite small, you could just format it into a string e.g,

dnl.dispatch_note_lines_id IN (1,2,3,5,...);

Alternatively, you could create a global temporary table and put the values of i_dnl_i into that. You could then do your "in" against the temporary table. You could even put an index on the temporary table, although the cost based optimizer doesn't always work well when you're mixing temporary and permanant tables in an query.

[slimdave]

After I have looked at my pl/sql function I have found out that (SELECT * FROM TABLE ( cast ( i_dnl_ids as NUMBER_ARRAY) )) is what seems to be taking the time.

Because I if removed this and only put in one value like (i_dnl_ids IN NUMBER) instead of (i_dnl_ids IN NUMBER_ARRAY) it seems to be running better.

I don't think that this is a correct deduction. The difference probably lies in either a different execution plan for the two queries (with NUMBER and with NUMBER_ARRAY) or just because in the ARRAY version you are passing in a lot of values.

So how many values are you generally passing in, and what is the explain plan for the query?

[sonuji_in]

hi

my developers can pass 300-350 values.

abhi

[sonuji_in]

hi we are trying this
CURSOR resavations(i_sol_id IN NUMBER, i_warehouses_id IN NUMBER, i_dnl_ids IN NUMBER_ARRAY) IS

SELECT sold.SALES_ORDER_LINES_ID,

wat.warehouse_assortment_trans_id,

sold.style_details_id,

wwt.actual_quantity AS quantity,

dnl.dispatch_note_lines_id,

dnld.dispatch_note_line_details_id,

wwt.warehouses_id

FROM warehouse_assortment_trans wat,

warehouse_trans wt,

warehouse_trans_types wtt,

warehouse_warehouse_trans wwt,

sales_order_line_details sold,

logistics.dispatch_note_lines dnl,

logistics.dispatch_note_line_details dnld

WHERE wat.sales_order_line_details_id = sold.sales_order_line_details_id

AND wat.warehouse_trans_id = wt.warehouse_trans_id

AND wt.warehouse_trans_id = wwt.warehouse_trans_id

AND wt.warehouse_trans_types_id = wtt.warehouse_trans_types_id

AND wtt.transaction_type_name = 'Ready for Shipment'

AND sold.sales_order_line_details_id = dnld.sales_order_line_details_id

AND dnld.dispatch_note_lines_id = dnl.dispatch_note_lines_id

AND wwt.actual_quantity != 0

AND wwt.warehouses_id = i_warehouses_id

AND wat.sales_order_lines_id = i_sol_id

AND Exists (select * from temporary table where ""coloumnname say it is"" idnld =dnl.dispatch_note_lines_id).


can any body suggest me the better way of writing this

sonu

[sonuji_in]

any more ideas my friends

Abhi