Re: "Best Practices" for Application SQL coding
- From: "Connor McDonald" <mcdonald.connor@xxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 3 Nov 2008 20:32:27 +0800
On Thu, Oct 30, 2008 at 10:53 PM, Gints Plivna <gints.plivna@xxxxxxxxx>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@xxxxxxxxx>:
> > 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?
> --
> //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@xxxxxxxxx
web: http://www.oracledba.co.uk
"Semper in excremento, sole profundum qui variat"
Other related posts:
