Re: Calling function from select statement is slower then using subselect in select

On Oct 25, 2006 at 5:54am Tomeo wrote:

(Method 1)

T> 15369502 consistent gets

(Method 2)

T> 125433 consistent gets

T> Why when I use function instead subquery, my query takes so long? T> What I'm doing wrong?

Clearly, from the above, there's something fundamentally different going on between the two. I've noticed Banner does this a lot - takes what should be a simple query, wraps a lot of PL/SQL functions around it, and massively increases the complexity and database workload to extract whatever information you needed. I can only assume that the logic is based around retrieval of one row, rather than many (And how long does it take them to start supporting the CBO...?!) - we get around that problem by materializing views for reporting requirements.

The facetious answer is that what you're doing wrong is using Banner... ;)

S.

-- 
Steven Patterson, MSci (Hons), Oracle Certified Professional