SQL & PL/SQL

Hey everyone,

We have a table that consists of a couple key columns and a column containing a boolean expression. We want to select a number of rows based on the key columns and evaluate the boolean expression in the expression column for each, only returning the first row wherein the boolean expression evaluates to true.

We are using a context to inject the test variables into the boolean expressions and currently we are selecting all the key rows in a PL/SQL procedure and going through the cursor evaluating each of the boolean expressions with an execute immediate.

The expressions are currently formated in the columns as anonymous PL/SQL blocks that, when called, will either return true or false to the PL/SQL procedure. Upon returning true, the procedure stops looping and returns true to the caller.

Psuedo-code would be:

- set-up sys_context with condition variables
- create cursor for: select * from expression_tbl et where <key matches>
- begin loop through cursor
- execute immediate cursor_row.expression using out :out_is_true
- if out_is_true = 'Y' then return true
- loop
- return false

The expressions (anonymous PL/SQL blocks) from the table look something like this:

begin if ((''||sys_context('ctx', '1')||'' = 'FL') AND (''||sys_context('ctx', '3')||'' BETWEEN 1619 AND 4598) ) then :1 := 'Y'; end if; end;

This works, but it seems like I should be able to do this another way and potentially extract more performance.

So...a couple questions:

1) I have read on ask Tom that a dynamic select statement (rather than an anonymous PL/SQL block) can evaulate the boolean expressions. I.E. rather than use an execute immediate clause on an anonymous PL/SQL block contained in the expression column, I would make the expression column compatible with a where clause and create this dynamic SQL query: 'select count(*) from dual where ' || expression. I would then execute that query and if it returns a row then the expression is true, otherwise it is false. My question is, does anyone think the performance of parsing the SQL and executing it would be better than that of executing an anonymous PL/SQL block for every row? Tom said that the SQL could incur a lot of hard-parses and kill performance, but how deathly is the constant compilation of anonymous PL/SQL blocks as shown above in comparison?

2) Would there be any benefit to pulling the execute routines out of the PL/SQL block and issuing a query such as the following:

select * from expression_tbl et where <key matches> AND pkg.eval_routine(et.expression) = 'Y' AND rownum <= 1.

I realize that the evaulating routine would then need to either perform an execute immediate on et.expression (if we keep the current method in place) or formulate the dynamic SQL statement and execute it. But, could this be faster than doing the same loop through rows explicitely in PL/SQL?

Doing this would trim my PL/SQL down to:

- set up sys_context
- execute the above select statmenet
- if a row is returned then return true otherwise return false

Seems more elegant, but the peformance is all that matters.

3) Is there any built-in routine that I may be able to replace pkg.eval_routine from 2 with that would evaulate boolean expressions for me? Or any other way to inline the idea from 1 with 2? I can't think of one given the dynamic nature of the beast, but...maybe there's something I missed.

Thanks everyone! Hopefully I've expressed myself clearly.