Re: "Best Practices" for Application SQL coding

On Thu, Oct 30, 2008 at 10:53 PM, Gints Plivna <gints.plivna_at_gmail.com>wrote:

> If you read the entire thread (yes a loooong read) then you'd find
> many approaches and most of them bad :)
> We are actually using either contexts or somtehing similar to approach
> proposed by Darko (without dreaded % in the front of predicates of
> course! search in this thread for it).
> The main idea is to keep binds, do not allow sql injection
> possibilities and also keep different plans for different search
> combinations. At least I personally don't know other techniques than
> either using contexts or something similar as Darko proposed. Or of
> course listing all possible combinations in your code :D
>
> Gints Plivna
> http://www.gplivna.eu
>
> 2008/10/30 Charles Schultz <sacrophyte_at_gmail.com>:
> > Awesome, thanks!
> >
> > Given that Tom submitted that in 2001 under 8i, are people still using
> that
> > method? Is this a rather current approach to this particular problem?
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

Its all about gathering metrics...We toggled between both extremes until we realised that both can be correct, and both can be wrong, depending on the actual usage. So we changed our app to log every time someone used the facility where an arbitrary number of predicates could be specified.

Within a month, we had enough data to refactor the code so that the "5" most common query predicate combinations were made into binded statements, and the others left as literal to provide as much optimizer benefit as possible. But you need to the collect that data on usage - its invaluable.

hth
Connor

-- 
Connor McDonald
===========================
email: connor_mcdonald_at_yahoo.com
web:   http://www.oracledba.co.uk

"Semper in excremento, sole profundum qui variat"

--
http://www.freelists.org/webpage/oracle-l