Re: Oracle query processing question

From: Jonathan Lewis <>
Date: Sun, 13 Apr 2008 23:03:04 +0100
Message-ID: <>

"Victor Rosenberg" <> wrote in message
> Hey guys
> We have some third party UDT that can be stored in the table as one of
> the columns. These UDT objects are created using stored procedures and
> are compared using other stored procedure. There also some kind of
> index created for this UDT column.
> Here's example of a query:
> select objectid
> from st_test a
> where SP_Compare(a.shape, SP_PolyFromText('Some initialization
> string')) = 1;
> Shape field is the stored UDT object and SP_PolyFromText creates the
> object using a string parameter.
> Now the question is whether the SP_PolyFromText run for every row in
> the table, and whether the index is used, because the performance of
> this query for a table with more than 10K rows is catastrophic.
> Thanks in advance.
> Victor

I think the only people who can tell you this are the people who supply the software. There are things that COULD be done, and things that actually happen. If implemented properly I think
a) the sp_polyfrom function should be called only once and
b) the user-defined operator should then be called once with an input that is the (now constant) value that is the output from sp_polyfrom.

But there may be version-dependent issue with getting this to work, and the supplied code may have missed some critical implementation details (such as associating statistics with user-defined operators).

If you enable sql_trace and run a query over a small sample set you may get lucky and see some clues in the trace file about what the operator is doing and how many times the function gets called.


Jonathan Lewis

Author: Cost Based Oracle: Fundamentals

The Co-operative Oracle Users' FAQ
Received on Sun Apr 13 2008 - 17:03:04 CDT

Original text of this message