Re: Oracle query processing question
Date: Sun, 13 Apr 2008 23:03:04 +0100
"Victor Rosenberg" <Victor.Rosenberg_at_gmail.com> 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.
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
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.
-- Regards Jonathan Lewis http://jonathanlewis.wordpress.com Author: Cost Based Oracle: Fundamentals http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.htmlReceived on Sun Apr 13 2008 - 17:03:04 CDT