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've been testing the nvl technique. The only limitation I found was that it the unrestricted selection omits null values. To take my previous example

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

Suppose you enter Select_value as null and there are 5 total rows in table_a. If 2 rows have nulls in col1 the select will only retrieve the 3 rows where col1 is not null. This may, or may not, but what you want.

Also, as you found out, there is no guarantee that this query will be efficient.

Those are significant limitations, but in the right situation it's still a valuable technique.

 

[mechadios]

If there is index on the col1 then col1 = col1 this condition should use the same index like when you use col1=some_value.
Regarding Karluk's limitations i didn't get the time to work on that so far i have been using this for the columns in which i never have null values. so this possiblity never came i will look into that.
Perfomance wise if the index is there it should work with col1 = col1 two also. Check the Execution plans.

 

[karluk]

Unfortunately execution plans aren't so simple to predict. Having "col1=col1" in your where clause won't get Oracle to use an index on col1. In fact Oracle should never use an index on a query like

select * from table_a
where col1 = col1;

Oracle has to retrieve all the rows of the table, and the most efficient way to do this is with a table scan.

The real question is whether the optimizer is smart enough to use index lookup in your nvl technique when there is real selection going on. Jad's testing is that it doesn't.

 

[evatype2]

and rownum=1

 

[jad]

a variable passed in to a procedure can't be optimised, i guess ... the dynamic sql seems to work ok though ... still it slows down with large amounts of data ... to get and display 43000 rows takes it about 50 seconds, most of that appears to be in the execute section.

does anyone know a quick way of extracting part of a query so as to 'page' through it ... say get a query to give you the first 200 rows, the second 200 etc without running the whole query?

just for askings sake, because at the end of the day no user could ever deal with 43000 rows; i'll tell them to constrain it.