optional parameters in procedure 2

[jad]

we have a procedure here with a cursor to access a table with about 115000 rows in it ... the table is a list of links, and works quickly if written without ambiguity. However the way we need to run it means that up to 2 parameters can be entered, and the more parameters entered the greater the discrimination ... and if no parameters are entered then it shows all possible matches (such that they are)

the code is roughly as follows:

    cursor c_curs is
        select testnames.short_test_name, Matrix.matrix_name,
               TECHNIQUEs.short_TECHNIQUE_name
            from expanded_tests, techniques, matrix, testnames
            where ( Selected_Technique is Null
                 or techniques.Techniqueid = Selected_Technique)
              and ( Selected_Matrix is Null
                 or matrix.matrix_id = Selected_Matrix)
              and techniques.techniqueid = expanded_tests.TECHNIQUE_TECHNIQUEID
              and matrix.matrix_id = expanded_tests.MATRIX_MATRIX_ID
              and testnames.testname_id = expanded_tests.TESTNAME_TESTNAME_ID
              and expanded_tests.show = 1
            order by upper(TECHNIQUEs.short_TECHNIQUE_name),
                     upper(Matrix.matrix_name),
                     upper(testnames.short_test_name);

where Selected_Technique and Selected_Matrix are passed in variables to the procedure.

Matrix, Technique and Testname are tables with text strings in them, and expanded_tests is a lookup table that joins them together.

the weak point is :

            where ( Selected_Technique is Null
                 or techniques.Techniqueid = Selected_Technique)

or the Selected_Matrix equivalent, where there is an 'or' statement, so for each row of the table it seems to recheck the Selected_Technique against null ...

if i remove the 'or' and specify the variables then it takes a fraction of a second. if i leave them it takes about 10 seconds.

is there any way of doing this within an sql cursor such that it either only checks once, or a replacement that does the same job?

Thanks

Jon

 

[karluk]

I think your problem is that Oracle doesn't store null values in indexes. That means that when you need Oracle to check for nulls there is no alternative but doing a table scan.

If you want this to run more efficiently I think you will need to rewrite your cursor to eliminate the nulls in the selection logic. Not knowing your application, I'm not sure if that's feasible.

 

[jad]

umm ... the nulls are passed in ... there are no nulls stored in the table.

if the parameter is not passed in it defaults to null ... and i only want to do a comparison if a parameter has been passed in.

Jon

 

[lewisp]

I would rewrite the code as two cursors, one with techniques.Techniqueid = Selected_Technique and one without. Then open one cursor if Selected_Technique has been entered or the other one if not.

 

[jad]

means 4 different cursors though ...

one with neither, one with both, and one with just technique, and one with just matrix.

if thats the way i have to code it, then thats ok, but i was wondering if there was a 'nice' way around it ... makes the code ugly (the query is actually bigger that the code i pasted ... i just cut most of the rubbish out)

jon

 

[karluk]

This looks like a candidate for using dynamic sql.

Another possibility is, if your columns are alphanumeric, to experiment with using "like" instead of = in your selection logic.

(techniques.Techniqueid like Selected_Technique|| '%')
and (matrix.matrix_id like Selected_Matrix || '%')

Please be aware, however, that it's likely that your query is inherently untunable if your users don't enter any selection parameters - Oracle simply has to retrieve so many rows that it gives up and does a table scan. That's something you may not be able to change. The key is to avoid making the other cases, where you do have good selection criteria, equally inefficient.

 

[karluk]

By the way, another possible problem with using "like" instead of = is that you may not get the same result set. Obviously you are relaxing the requirement that your records exactly match the keys supplied by your users.

 

[jad]

i had thought of 'to_char(techniqueid,'0000009') like to_char(Selected_Technique,'0000000') || '%' 'ing it ... which would work, i guess ...

looking at the dynamic sql version now ... not done it before, so got to get the order right.

Jon

 

[jad]

that 'like' thing didn't speed it up by the way ... i think it's better as dynamic ...

yuck, it looks awful ...

 

[karluk]

That's too bad, but it's not particularly surprising. Trying to write a single universal query carries a big risk that you will get performance similar to the worst case scenario.

 

[sem]

Try to use dynamically created cursor (ref cursor).
The result statement itself will depend on parameters passed :

declare
type t is ref cursor;
c t;
main_query varchar2(2000):= 'select ... where ...';
begin

if Selected_Technique is not null then
main_query||' and techniques.Techniqueid = ' ||selected_Technique;
end if

if Selected_Matrix is not Null then
main_query||' and matrix.matrix_id = '||Selected_Matrix;
end if;

open c for main_query;

....
end;

This will spent some extra resources for parsing but will be executed optimally.

 

[mechadios]

Try like this for those columns which you think can be null
and you want to select all for them

matrix.matrix_id = nvl(Selected_Matrix,matrix.matrix_id)

this will take into this only when Selected_Matrix is not null othrwise the condition will be true always.

 

[jad]

but if it is not set i don't want it to match ... so NVL is the opposite of what i want ...

i did the dynamic sql ... seemed to work great, got a good speed on 'small' queries ... but i get a get a

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

error when returning large numbers of rows ... about 43,504 rows to be exact

looks like there is a maximum size of buffer in DBMS_SQL ...

looks like i'll try the last option ... the cursor ref ... the real query is about 27 lines ... i'll just have to repeat it 4 times ... makes the code bigger and uglier, but it should work.

 

[jad]

correction it's not the DBMS_SQL that has problems with the extending temp ... it's my output program, complaining about the amount of data dbms_sql has passed it ... time to rewrite my loops to limit at say ... 10000 rows

 

[karluk]

It sounds as if you are finding a satisfactory solution with dynamic sql. However, I would try Mechadios's suggestion if you are looking for alternatives. It looks as if it will work with a good chance of not degrading performance when you have selection parameters.

Basically Mechadios's suggestion gives you one of two additions to your where clause

matrix.matrix_id = matrix.matrix_id (when Selected_Matrix is null)

matrix.matrix_id = Selected_Matrix (otherwise)

Very clever - you get a vacuously true condition when nulls are entered. That's what you want, no restrictions on the matrix_id.

 

[mechadios]

My solution is not going to match if you have not set matrix.matrix_id in that case this Where Clause is just going to be true for all the rows in the table. May be if i have misunderstood your requirement but this is the best solution when you want to write a query where in if you are passing a matrix_id it will select all the rows for that matrix_id otherwise pass matrix_id as NULL and it will select the rows for all matrix_id. Elaborate on what exactly you are looking for!!!

matrix.matrix_id = nvl(Selected_Matrix,matrix.matrix_id)

 

[jad]

matrix.matrix_id is set but if selected_matrix is null i want it to match _everything_ which cannot be matched with an NVL ...

dynamic sql is happening ... although it is slowed down by the sheer volume of stuff in the table, it takes time to EXECUTE it.

 

[karluk]

It may not be worth pursuing, but you are not understanding the beauty of Mechadios's suggestion. It definitely should work. To take a simple example

select * from table_a
where col1 = nvl(Select_value, col1);

When Select_value is null this is equivalent to

select * from table_a
where col1 = col1;

See how nicely this works? With null in the selection_value you want all the rows in the table, and an always true condition like col1=col1 gives you just that.

The other case is when Select_value is not null. In that case the select is equivalent to

select * from table_a
where col1 = Select_value;

which is also what you want. This is really a nice technique to remember, even if you don't use it for the solution to this particular problem.

 

[jad]

i get you ... tuesdays must be slow ...

will look at that one for speed, and report back ...

 

[jad]

was the same speed ... i.e. slow