Jonathan Lewis wrote:
> "Chris McMahon" <NOSPAM_cochrane68_at_hotmail.com> wrote in message
> news:o7goe.33772$Fv.29970_at_lakeread01...
>
>
>>I'm new to VPDs, and I'm not sure I totally understand what you said. Are
>>you saying that my simple SQL statement:
>>
>>SELECT * FROM test;
>>
>>is first parsed (1st row in log table added) and then executed (2nd row in
>>log table added)?
>
>
>
> Correct.
> Try running with sql_trace set to true, and check
> the trace file:
>
> Do two tests
> Test 1:
> select colX from test where rownum =1;
> select colX from test where rownum =1;
> select colX from test where rownum =1;
> select colX from test where rownum =1;
> select colX from test where rownum =1;
> select colX from test where rownum =1;
> select colX from test where rownum =1;
> select colX from test where rownum =1;
> select colX from test where rownum =1;
> select colX from test where rownum =1;
>
> Test 2
> declare
> m_v varchar2(32); -- or whatever your colX is
> begin
> for i in 1..10 loop
> select colX
> into m_v
> from test
> where rownum = 1
> ;
> end loop;
> end;
> .
> /
>
>
> In the first test, you should get 20 calls to the VPD function,
> in the second you should get 11 (one on the parse, and 10
> on the executes) because pl/sql will hold the cursor open.
>
Thanks for the answer! I assume there aren't any significant negative
performance implications to this "double" call to the predicate function
that I need to worry about?
Received on Sat Jun 04 2005 - 06:54:23 CDT
