Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!newsfeed.icl.net!newsfeed.fjserv.net!oleane.net!oleane!nerim.net!proxad.net!64.233.160.134.MISMATCH!postnews.google.com!g10g2000cwb.googlegroups.com!not-for-mail
From: "Kin" <kin_ng5@yahoo.com>
Newsgroups: comp.databases.oracle.server
Subject: Re: Why functions in Select Clause affect performance?
Date: 4 Apr 2006 16:25:28 -0700
Organization: http://groups.google.com
Lines: 24
Message-ID: <1144193128.805587.52760@g10g2000cwb.googlegroups.com>
References: <1144174643.540169.119060@t31g2000cwb.googlegroups.com>
   <1144180001.413360@yasure.drizzle.com>
NNTP-Posting-Host: 155.57.254.1
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
X-Trace: posting.google.com 1144193134 32259 127.0.0.1 (4 Apr 2006 23:25:34 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Tue, 4 Apr 2006 23:25:34 +0000 (UTC)
User-Agent: G2/0.2
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; LSCOE40; LSACOE40; .NET CLR 1.1.4322),gzip(gfe),gzip(gfe)
Complaints-To: groups-abuse@google.com
Injection-Info: g10g2000cwb.googlegroups.com; posting-host=155.57.254.1;
   posting-account=IYYQQwwAAADOhJAGkXyPdoeLq-6mVy4N
Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:264860

The Explain Plan did not show any of the tables used in the function,
as expected.   The SQL frankly is too complex to show here.   What I
tried to find out was if there was any possibility that Oracle will
invoke functions in the Select Clause midway instead of at the last
moment.

After further tests, even the 20 seconds results may be misleading as
SQL Plus may be applying the /*+ first_row /* to it.   So I changed the
sql to do something like this

create table testtbl as
select f(a_a,b_b,c_c)
from
(
select a.a as a_a, b.b as b_b, c.c as c_c
from a, b, c
where ...
)


This also took a long time.   I didn't wait for it to end as it clearly
showed the function is where the problem is.   So may be Oracle is
doing what I expected: invoke the functions at the last moment.