Tom,

Regarding the comment :

"However, performance will be BEST if you can avoid calling plsql from sql altogether -- something that is increasingly more often something that can be done with analytic functions or select (select). "

and some previously stated comments that complex queries can often usefully be hidden in a view, I'd like to here your opinion of this situation.

CREATE VIEW trans_view AS
select c.client_id,
c.client_type,
c1.desc client_type_desc,
c.client_status,
c2.desc client_status_desc,
a.account_id,
a.account_type,
a1.desc account_type_desc,
a.account_action,
a2.desc account_action_desc
t.transaction_id,
t.transaction_currency,
t1.desc trans_curr_desc,
t.transation_date,
from clients c,
accounts a,
transactions t,
ref_codes c1,
ref_codes c2,
ref_codes a1,
ref_codes a2
ref_codes t1
where a.client_id = c.client_id
and t.account_id = a.account_id
and c1.code_type = 'CLIENT TYPE'
and c2.code_type = 'CLIENT STATUS'
and a1.code_type = 'ACCOUNT TYPE'
and a2.code_type = 'ACCOUNT ACTION'
and t1.code_type = 'CURRENCY TYPE'
and c1.code = c.client_type
and c2.code = c.client_status
and a1.code = a.account_type
and a2.code = a.account_action
and t1.code = t.transaction_currency

against

CREATE FUNCTION get_desc (p_desc_type IN VARCHAR2, p_code IN VARCHAR2) return VARCHAR2 IS
v_return ref_codes.desc%TYPE;
BEGIN
SELECT desc
INTO v_return
FROM ref_codes
WHERE code_type = p_desc_type
AND code = p_code;
RETURN v_return;
END;

CREATE VIEW trans_view AS
select c.client_id,
c.client_type,
get_desc('CLIENT TYPE',c.client_type) client_type_desc,
c.client_status,
get_desc('CLIENT STATUS',c.client_status),
a.account_id,
a.account_type,
get_desc('ACCOUNT TYPE',a.account_type) account_type_desc,
a.account_action,
get_desc('ACCOUNT ACTION',a.account_action) account_action_desc
t.transaction_id,
t.transaction_currency,
get_desc('CURRENCY TYPE',t.tranaction_currency) trans_curr_desc,
t.transation_date
from clients c,
accounts a,
transactions t
where a.client_id = c.client_id
and t.account_id = a.account_id

From tests I've run (against Oracle 8.1.7), in the second case the function get_desc will only be run if the column is selected, whereas in the first case Oracle must perform all the joins even if the relevant column isn't selected (as it needs to know how many rows the join returns).

As such, I'd expect a performance trade off with the first view working faster if most/all of the descriptions are selected, and the second option working faster where fewer (zero or one maybe) of the descriptions are in the select list.

To work out the 'sweet spot' of which is better, I think it will be necessary to weigh up the time taken for get_desc to run and the number of times it's run, against the percentage of queries where descriptions are selected. Is that right ?

PS. I'm assuming code_type and code make up a unique key on ref_codes, and that all the expected matches are there.

Excellent information,

I'd read about this left correlation, but it seemed to be concerned with nested tables, and I didn't see that it could have performance benefits in thes sort of circumstances.

One niggle is that the explain plan in 8.1.7 didn't show the access of the ref_codes table whether the description was selected or not. However, I created a 'hamstrung' version of the view that wouldn't use the index, and it was obvious from the performance difference that, without the description selected, the unnecessary select/join wasn't performed.

Tom

Sorry to follow this up with a question (which you will possibly choose not to answer) about SELECT..SELECT as Metalink came up with nothing that I could find.

I am trying to bulk collect from an inline view. The inline view is performing a select key from nested table MINUS select (select ... ) from normal table, where the select (select... ) returns one row.

In 8.1.6.3 (unfortunately the version I must develop for), the SQL statement (i.e. bulk collect bit commented out) works fine, but to run it in PL/SQL gives me a runtime error saying it wasn't expecting the "(SELECT" after the SELECT keyword. But it runs OK in 9.2.0.1. Using the SELECT SELECT is already a workaround to a discrepancy in our model, so I'm wondering if you have any bug or workaround knowledge for using it with bulk collect in PL/SQL.

Examples in 8i and 9i:-

816> create table x as select object_id, object_name, object_type from user_objects;

Table created.

816> create table y as select rownum as surr_key from user_objects;

Table created.

816> create table z as select rownum as surr_key, object_id, object_name, object_type from user_objects;

Table created.

816> delete from y where rownum <= 10;

10 rows deleted.

816>
816> declare
2 type typ_nt_v2_4000 is table of varchar2(4000);
3 nt typ_nt_v2_4000;
4 begin
5 select object_id
6 bulk collect into nt
7 from (
8 select object_id
9 from x
10 minus
11 select (select object_id from z where surr_key = y.surr_key) as object_id
12 from y
13 );
14 dbms_output.put_line(nt.count||' fetched.');
15 end;
16 /
select (select object_id from z where surr_key = y.surr_key) as object_id
*
ERROR at line 11:
ORA-06550: line 11, column 18:
PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:
( - + mod not null others <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev sum variance
execute forall time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string>
ORA-06550: line 11, column 71:
PLS-00103: Encountered the symbol "AS" when expecting one of the following:
; return returning and or


816>
816> select count(*)
2 from (
3 select object_id
4 from x
5 minus
6 select (select object_id from z where surr_key = y.surr_key) as object_id
7 from y
8 );

COUNT(*)
----------
10

816>

****************************

920> create table x as select object_id, object_name, object_type from user_objects;

Table created.

920> create table y as select rownum as surr_key from user_objects;

Table created.

920> create table z as select rownum as surr_key, object_id, object_name, object_type from user_objects;

Table created.

920> delete from y where rownum <= 10;

10 rows deleted.

920>
920> declare
2 type typ_nt_v2_4000 is table of varchar2(4000);
3 nt typ_nt_v2_4000;
4 begin
5 select object_id
6 bulk collect into nt
7 from (
8 select object_id
9 from x
10 minus
11 select (select object_id from z where surr_key = y.surr_key) as object_id
12 from y
13 );
14 dbms_output.put_line(nt.count||' fetched.');
15 end;
16 /
10 fetched.

PL/SQL procedure successfully completed.

920>
920> select count(*)
2 from (
3 select object_id
4 from x
5 minus
6 select (select object_id from z where surr_key = y.surr_key) as object_id
7 from y
8 );

COUNT(*)
----------
10

920>



Any ideas other than upgrade ? ;)

Thanks

Adrian

Tom

Thanks for responding. You confirmed my worst fears... I've not heard the term "dynamically opened query" before. Don't suppose you can tell me if it works in 8.1.7.4 as it does in 9i?

8.1.7 is the only potential upgrade path I have at present (we are constrained by third-party tools - don't you just love it).

I'll change your view recommendation to a function to return the value I'm after. This SELECT (SELECT...) is a workaround to a problem I have whereby I'm referencing a whole long list of tables and their "key columns" from a parameter table to drive a dynamic SQL version of the demo I gave you. The parameter table's value for KEY_COLUMN feeds the subtractor in the MINUS SQL and are consistent and existent across all but one table. So for this one annoying table that doesn't have a corresponding key column, instead of storing the column name in the parameter table, I stored a SELECT (SELECT) instead. About which I was feeling quite pleased with myself, until I hit the 8.1.6.3 limitation. So a function name in my table instead of the SELECT (SELECT) will have to be my workaround...

Thanks again.

Adrian

Tom,

Pardon my ignorance. But is there a way that a view can be called with parameters and those parameters are used in the underlying query ?

EX: Create View (dno)
as
select * from emp where deptno = dno;

Appreciarte your help.

Tom, how to achieve this using sql if I want to get multiple columns using select (select ...) ? Say, I want

select col1, col2, (select col3, col4, col5 ...)

Is the above possible ? Also, where can I read more about using "select (select ...)" ?

Thanks in advance !

For the above question, I am using Oracle EE 8.1.7.

Thanks !

Can you please give an example to see which database statistics as to know that context switching is adding considerable overhead for me to move my pl/sql into the sql itself. I am calling a function in an sql statement and have coded this way to keep the code distinct and easy to understand.

here are the benchmark results(it was just an example, actually we dont need that function at all) What I really wanted to see was the effect of context switching, what parameter shows me the effect of context switching both in autotrace or tkprof.

10:39:56 scott@dybqr11>set autotrace traceonly;
10:39:56 scott@dybqr11>set arraysize 5000;
10:39:56 scott@dybqr11>alter session set sql_trace=true;

Session altered.

10:39:56 scott@dybqr11>select ename,sal,dept_max_sal(deptno) dep_max_sal from emps;

14999 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'EMPS'




Statistics
----------------------------------------------------------
15006 recursive calls
180000 db block gets
1455005 consistent gets
0 physical reads
0 redo size
731708 bytes sent via SQL*Net to client
457 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14999 rows processed

10:43:19 scott@dybqr11>select ename,sal,(select max(sal) from emps where deptno=e.deptno) dep_max_sal from emps e;

14999 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'EMPS'




Statistics
----------------------------------------------------------
0 recursive calls
132 db block gets
1070 consistent gets
0 physical reads
0 redo size
731743 bytes sent via SQL*Net to client
457 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14999 rows processed

10:43:32 scott@dybqr11>alter session set sql_trace=false;



TKPROF OUTPUT
-----------------

1) First SQL with Function call

select ename,sal,dept_max_sal(deptno) dep_max_sal
from
emps


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.19 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 4 2.00 3.73 0 100 12 14999
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 2.00 3.92 0 100 12 14999

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 209
********************************************************************************

SELECT MAX(SAL)
FROM
EMPS WHERE DEPTNO = :b1


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 14999 0.42 0.43 0 0 0 0
Fetch 14999 152.09 192.66 0 1454903 179988 14999
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 29999 152.51 193.09 0 1454903 179988 14999

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 209 (recursive depth: 1)



********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.19 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 4 2.00 3.73 0 100 12 14999
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 2.00 3.92 0 100 12 14999

Misses in library cache during parse: 1


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 15000 0.42 0.44 0 0 0 0
Fetch 15000 152.09 192.66 0 1454905 179988 15000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 30002 152.51 193.10 0 1454905 179988 15000

Misses in library cache during parse: 0

2 user SQL statements in session.
1 internal SQL statements in session.
3 SQL statements in session.

************************************************************
Second SQL without the function call
************************************************************
select ename,sal,(select max(sal)
from
emps where deptno=e.deptno) dep_max_sal from emps e


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 4 0.22 4.19 0 1070 132 14999
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.23 4.20 0 1070 132 14999

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 209

Rows Row Source Operation
------- ---------------------------------------------------
1 DELETE PLAN_TABLE
3 TABLE ACCESS FULL PLAN_TABLE




********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 4 0.22 4.19 0 1070 132 14999
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.23 4.20 0 1070 132 14999

Misses in library cache during parse: 1


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 0 0.00 0.00 0 0 0 0

Misses in library cache during parse: 0

1 user SQL statements in session.
0 internal SQL statements in session.
1 SQL statements in session.

I need to know how context switching is affecting me. I can see the difference in the elapsed time and the consistent gets and executions, but I want to know what exactly(which statistic) tells me here the problem with context swithing

what i am not clear about is
i) all the statistics i have gathered(cpu time, consistent gets etc) are to do with sql processing, arent they? i mean cpu time,consistent gets and elapsed time can go up by writing bad sql also, not necessarily by context switching.
ii) i want to see the time spent(elapsed and cpu) switching between sql engine and plsql engine -- somehow this is not clear to me
iii)yes, the "how many" thing in the recursive calls tells me the number of switches

i) of course.  but -- look at the cpu time with and without.  look at the elapsed time with and without.

That is the overhead in this case of doing it wrong (calling plsql from sql when you didn't need to) and doing it right.  That includes all of the overhead in this case of doing the context switch plus all of the extra work.


ii) write a dummy "return NULL" function if you just want to measure the context switch overhead.  not sure WHAT that will buy you exactly for it is a comparison of "doing it in plsql called from sql vs doing it in sql alone" that is relevant here.  It didn't really matter if the context switch was 10% of those values (the differences) or 90%.  The fact is -- doing in plsql would could (should) have been done in SQL took X more resources.

Here is a quick and dirty "do nothing" example:

ops$tkyte@ORA920> create or replace function f return number
  2  as
  3  begin
  4          return null;
  5  end;
  6  /
 
Function created.
 
ops$tkyte@ORA920>
ops$tkyte@ORA920>
ops$tkyte@ORA920> exec runStats_pkg.rs_start
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA920> prompt select null from all_objects;
select null from all_objects
ops$tkyte@ORA920> set termout off
ops$tkyte@ORA920> exec runStats_pkg.rs_middle
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA920> prompt select f() from all_objects;
select f() from all_objects
ops$tkyte@ORA920> set termout off
ops$tkyte@ORA920> exec runStats_pkg.rs_stop(50)<b>
Run1 ran in 267 hsecs
Run2 ran in 394 hsecs
run 1 ran in 67.77% of the time</b>
 
Name                                  Run1        Run2        Diff
LATCH.row cache enqueue latch       58,918      58,978          60
LATCH.row cache objects             88,377      88,437          60
LATCH.SQL memory manager worka          67         134          67
LATCH.cache buffers chains         298,631     298,701          70
LATCH.library cache pin alloca          24         102          78<b>
STAT...CPU used by this sessio         206         297          91
STAT...CPU used when call star         206         297          91</b>
STAT...Elapsed Time                    273         397         124
STAT...redo size                    64,980      65,132         152
LATCH.simulator hash latch           3,456       3,200        -256
LATCH.library cache pin                 54       4,381       4,327
LATCH.shared pool                       54       6,556       6,502
LATCH.library cache                    100       8,894       8,794
STAT...session pga memory                0      65,536      65,536
 
Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
454,727     474,555      19,828     95.82%
 
PL/SQL procedure successfully completed.
 

I have three tables.

stkh(stkh_nbr,stkh_acct_nbr,acct_id)
primary key(stkh_nbr,stkh_acct_nbr)


2.acq(stkh_nbr,stkh_acct_nbr,trf_nbr,trf_acct_nbr)
foreign keys
1. (stkh_nbr,stkh_acct_nbr) referencing stkh(stkh_nbr,stkh_acct_nbr)
2. (trf_nbr,trf_acct_nbr) referencing stkh(stkh_nbr,stkh_acct_nbr)

own_table(acct_id,trf_acct_id)

I want to insert in own_table where
stkh.stkh_nbr = acq.stkh_nbr and
stkh.stkh_Acct_nbr = acq.stkh_acct_nbr

So, if I have data like this

stkh(stkh_nbr,stkh_Acct_nbr are varchar2, acct_id is number)
stkh_nbr stkh_Acct_nbr acct_id
100 01 1
101 01 2
103 02 3
104 01 4
105 03 5


acq
stkh_nbr stkh_Acct_nbr trf_nbr trf_Acct_nbr
100 01 103 02
101 01 null null
104 01 100 01
105 03 104 01

Based on these data, I should get the result in own_table as

acct_id trf_id
1 3
2 null
4 1
5 4

To achieve this I have created a function like this

create or replace function get_trf_id(a number,b number)
return number
is
acct stkh.acct_id%type;
begin
select acct_id into acct from stkh,acq where
stkh.stkh_nbr = acq.stkh_nbr and
stkh.stkh_Acct_nbr = acq.stkh_acct_nbr;

return (acct);
exception
when no_data_found then
return(null);
end;
/

insert into own_table(acct_id,trf_acct_id)
select stkh.acct_id,get_trf_id(acq.trf_nbr,acq.trf_acct_nbr)
from stkh,acq
where stkh.stkh_nbr = acq.stkh_nbr and
stkh.stkh_Acct_nbr = acq.stkh_acct_nbr;



I'm getting the proper result, but I want to know whether I can write it without using that function.

Really appreciate your help.

Neelam,

Try this:

insert into own_table(acct_id,trf_acct_id)
select stkh.acct_id, (select stkh1.acct_id from stkh stkh1
where stkh1.stkh_nbr = acq.trf_nbr
and stkh1.stkh_acct_nbr = acq.trf_acct_nbr)
from stkh,acq
where stkh.stkh_nbr = acq.stkh_nbr and
stkh.stkh_Acct_nbr = acq.stkh_acct_nbr;

Basically replace your get_trf_id function with the scalar sub-qurey

(select stkh1.acct_id from stkh stkh1
where stkh1.stkh_nbr = acq.trf_nbr
and stkh1.stkh_acct_nbr = acq.trf_acct_nbr)

Thanks for the help.
Scalar query also worked fine for me.
joining stkh to acq always returns one row, never more than one.

Once again thanks

Tom

Regarding context switching, 

1)
if you were to have a stored procedure that does something like:


insert into mytable1
select ..
       .. 
       myFunction(col1),
       ..
from mytable2;

end myproc;
/

Then does this do a context switch to run the function?
I'm thinking no because it is sql run in plsql engine isn't it, so to call the function then it doesn't switch?

Whereas 

2)

SQL>insert into mytable1
select ..
       .. 
       myFunction(col1),
       ..
from mytable2;


Run in sqlplus would context switch??


OR

begin 

execute immediate 'insert into mytable1
select ..
       .. 
       myFunction(col1),
       ..
from mytable2';

end;
/

3) would also do a switch??


Am i right with these 3 examples?

(am on 8.1.7.4) 

so if you are saying there is a context switch involved in:

insert into mytable1
select ..
..
myFunction(col1),
..
from mytable2;

end myproc;
/



Then what about :

select myFunction(col1)
into myVariable
from mytable2;

end myproc;
/



SELECT INTO is plsql isn't it?
Would it do a context switch there?

Tom

As Cary Millsap would say, the best way to minimise context switching is to not do it at all ! However, I have a trade-off between wrapping some logic in a function ( as it needs to be used in several places ) and performance. I've decided on a function to simplify code maintenance, so to determine the best way to minimise switching I constructed the following simple test. But I'm confused by the results.

9i> CREATE OR REPLACE PACKAGE pkg AS
2 c PLS_INTEGER := 0;
3 FUNCTION fx RETURN DATE;
4 END pkg;
5 /

Package created.

9i>
9i> CREATE OR REPLACE PACKAGE BODY pkg AS
2 FUNCTION fx RETURN DATE IS
3 BEGIN
4 c := c + 1;
5 RETURN DATE '2004-06-16';
6 END fx;
7 END pkg;
8 /

Package body created.

9i>
9i> DECLARE
2
3 n PLS_INTEGER;
4
5 PROCEDURE op ( n_in IN PLS_INTEGER ) IS
6 BEGIN
7 DBMS_OUTPUT.PUT_LINE(
8 'Counted ' || TO_CHAR(n_in) ||
9 ' rows with ' || TO_CHAR(pkg.c) ||
10 ' function calls.' );
11 pkg.c := 0;
12 END op;
13
14 BEGIN
15
16 WITH dates AS ( SELECT pkg.fx AS d FROM dual )
17 SELECT COUNT(*) INTO n
18 FROM user_objects uo, dates
19 WHERE uo.created < dates.d;
20
21 op( n );
22
23 SELECT COUNT(*) INTO n
24 FROM user_objects uo, ( SELECT pkg.fx AS d FROM dual ) dates
25 WHERE uo.created < dates.d;
26
27 op( n );
28
29 SELECT COUNT(*) INTO n
30 FROM user_objects
31 WHERE created < pkg.fx;
32
33 op( n );
34
35 SELECT COUNT(*) INTO n
36 FROM user_objects
37 WHERE created < ( SELECT pkg.fx FROM dual );
38
39 op( n );
40
41 END;
42 /
Counted 674 rows with 750 function calls.
Counted 674 rows with 750 function calls.
Counted 674 rows with 750 function calls.
Counted 674 rows with 1 function calls.

PL/SQL procedure successfully completed.

My main confusion is with the subquery factoring and in-line view. I assumed that they would execute the function with 1 call, but in fact, they repeatedly get called, probably due to the cartesian product of user_objects and a single row view ( though I can't be sure ). The last two examples are also interesting and I'd like to know why they behave so differently ?

Perhaps I might just use a view...

Regards
Adrian

Tom

Interesting link and good examples - thanks. I was unaware that the scalar subquery wouldn't scale - it's a bit shocking to see.

Good advice re: application context. I'm already using a logon-trigger-loaded application context for several other "global" variables - and although the function I was using was a dumbed-down version for testing, there's no reason why I can't load the real value in as well. My lookup table that my appctx package reads contains both fixed attribute values as well as the names of functions to derive attribute values at runtime, so I can just put my function name and its params in there and voila !

Thanks
Adrian

Hi Tom,

Thanks in advance. I have 2 databases for example DB1 & DB2. There is a function F2 in database DB2.

There is a Database link dblink1 in database DB1 referring database DB2.

Now the question is, How do we access the function F2 from database DB1.

Thanks,

Thanks, Tom

Tom

Please can you take a look at this simple test case:

drop table t1;
create table t1 (c1 varchar2(10));

drop table t2;
create table t2 (c2 varchar2(10));


create or replace function f1 (p1 in varchar2)
return varchar2
as
begin

insert into t1 values (p1);

return p1;


end f1;
/


create or replace function f2 (p1 in varchar2, p2 in varchar2 default null)
return varchar2
as
begin



return 'success';


end f2;
/



-- first scenario is


truncate table t1;

insert into t2
select f2(a,a)
from ( select f1('hello') as a
from dual
);


select *
from t1;

-- i get

C1
----------
hello
hello


--here i've used the value of "a" twice in my call to f2

-- 2nd scenario is


truncate table t1;

insert into t2
select f2(a,null)
from ( select f1('hello') as a
from dual
);



select * from
t1;

-- now i get

C1
----------
hello






So my question is how can i have scenario 1 where i use the value of
"a" twice in the call to f2 ie f2(a, a) but it only performs the insert once????



I tried to do it by doing:


truncate table t1;

insert into t2
select f2(new_a,new_a)
from ( select a as new_a
from ( select f1('hello') as a
from dual
)
);



select * from
t1;

-- but you still get:

C1
----------
hello
hello


Why is this happening? is it because the queries are begin rewritten internally??
How can i achieve the behaviour i want?
Can you also explain exactly what is happening in the background with this sort of query, ie at what point it calls the function and why it doesn't just use the value of "a" it got from the previous select statement.

Many thanks

you have NO CONTROL over how many times the functions are called -- a simple change in a query plan over time can (and will) change the number of times a function is called.  

sql is not procedural, you cannot rely on side effects, especially when it comes to data integrity.

I don't know what you are trying to do, but it looks "fishy" -- like you are trying to be super tricky.  

can you describe your real requirement?  perhaps we can suggest a reliable way to achieve your goals.

(but the scalar subquery trick I've documented elsewhere on this site -- whereby scalar subqueries are "cached" for recent rows -- would "work around" this issue:

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t2
  2      select     f2(a,a)
  3      from     (    select  (select f1('hello') as a from dual) a
  4              from dual
  5          );
 
1 row created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select *
  2  from t1;
 
C1
----------
hello
 

but I would call it magic by side effect and not a reliable solution. 

Well the reason that i am doing this is because the function i am calling is doing some validation on the field and inserting any warnings or errors into a logging table, therefore i only want to insert this logging once (ie only call the function once) even though the value the function returns is used multiple time higher up the select statement eg:

insert into final_table
(
select b,
a,
..
from (select ..
a,
f2(a,a) as b,
..
from (select f1(c1) as a
from table)
)
);


So its because of the logging that i am needing to do it this way. The functions just validate and log and the "good" rows make it into the final_table, and i log the errors/warnings, its working fine except for this problem where some fields are being validated and logged multiple times.
This is a bulk load and validate process.

Does this info help to see what i am trying to do?

Hi Tom,
I'm working on Oracle 8.1.7 On Win-2K.
This is continuation with First question.

As per my knowledge i can not use alias name in SQL Order by Clause. (Same u answered for first guy) But If you go thro' following output here we really have to think about our knowledge. Can u Tell me Why this is happening or my knowledge abt SQL is Less?

Input truncated to 9 characters

USER                           DATABASE
------------------------------ -------------------------
SCOTT                          TESTDATABASE

SQL> select to_char(hiredate,'DD-Mon-YYYY') as dt,empno from emp
  2  order by dt asc;

DT               EMPNO
----------- ----------
01-May-1981       7698
01-May-1981       8698
02-Apr-1981       7566
02-Apr-1981       8566
03-Dec-1981       7900
.
.
.
.
.
23-Jan-1982       8934
23-May-1987       7876
23-May-1987       8876
28-Sep-1981       7654
28-Sep-1981       8654

28 rows selected.

==== But yea if you are using in where it's giving me problem

SQL> select to_char(hiredate,'DD-Mon-YYYY') as dt,empno from emp
  2  where dt < '01-Jun-1980'
  3  order by dt;
where dt < '01-Jun-1980'
      *
ERROR at line 2:
ORA-00904: invalid column name

Becoz of this my output gets wrong even tho' i used 
following query

SQL> select to_char(hiredate,'DD/MM/YYYY') as hiredate ,empno from emp
  2  order by Hiredate;

HIREDATE        EMPNO
---------- ----------
01/05/1981       7698
01/05/1981       8698
02/04/1981       7566
.
.
.
.
.
28/09/1981       7654
28/09/1981       8654


Thanx
Bipin G 

Hi ,

I am getting following error

+++++++++++++++++++++++++++++++++++++++

execute dbms_stats.gather_schema_stats('HR',DBMS_STATS.AUTO_SAMPLE_SIZE,cascade=>true);
BEGIN dbms_stats.gather_schema_stats('HR',DBMS_STATS.AUTO_SAMPLE_SIZE,cascade=>true); END;

*
ERROR at line 1:
ORA-00904: "FULL_NAME": invalid identifier
ORA-06512: at "SYS.DBMS_STATS", line 9643
ORA-06512: at "SYS.DBMS_STATS", line 10137
ORA-06512: at "SYS.DBMS_STATS", line 10324
ORA-06512: at "SYS.DBMS_STATS", line 10378
ORA-06512: at "SYS.DBMS_STATS", line 10355
ORA-06512: at line 1

++++++++++++++++++++++++++++++++++++++++++++++++

But HR conatins only one table which is not having "FULL_NAME" column

Plz help.

Thanks,
Atul

Following are the results after sql_trace

The following statement encountered a error during parse:

select /*+ cursor_sharing_exact dynamic_sampling(0) no_monitoring*/ count(*),count(UPPER("FULL_NAME")),count(distinct UPPER("FULL_NAME")),sum(vsize(UPPER("FUL
L_NAME"))),substrb(dump(min(substrb(UPPER("FULL_NAME"),1,32)),16,0,32),1,120),substrb(dump(max(substrb(UPPER("FULL_NAME"),1,32)),16,0,32),1,120),count("ATTRIBUT
E1"),count(distinct "ATTRIBUTE1"),sum(vsize("ATTRIBUTE1")),substrb(dump(min(substrb("ATTRIBUTE1",1,32)),16,0,32),1,120),substrb(dump(max(substrb("ATTRIBUTE1",1,
32)),16,0,32),1,120),count(distinct "PERSON_ID"),sum(vsize("PERSON_ID")),substrb(dump(min("PERSON_ID"),16,0,32),1,120),substrb(dump(max("PERSON_ID"),16,0,32),1,
120),count(distinct "EFFECTIVE_START_DATE"),substrb(dump(min("EFFECTIVE_START_DATE"),16,0,32),1,120),substrb(dump(max("EFFECTIVE_START_DATE"),16,0,32),1,120),co
unt(distinct "EFFECTIVE_END_DATE"),substrb(dump(min("EFFECTIVE_END_DATE"),16,0,32),1,120),substrb(dump(max("EFFECTIVE_END_DATE"),16,0,32),1,120),count(distinct
"PERSON_TYPE_ID"),sum(vsize("PERSON_TYPE_ID")),substrb(dump(min("PERSON_TYPE_ID"),16,0,32),1,120),substrb(dump(max("PERSON_TYPE_ID"),16,0,32),1,120),count(disti
nct "LAST_NAME"),sum(vsize("LAST_NAME")),substrb(dump(min(substrb("LAST_NAME",1,32)),16,0,32),1,120),substrb(dump(max(substrb("LAST_NAME",1,32)),16,0,32),1,120)
,count("EMAIL_ADDRESS"),count(distinct "EMAIL_ADDRESS"),sum(vsize("EMAIL_ADDRESS")),substrb(dump(min(substrb("EMAIL_ADDRESS",1,32)),16,0,32),1,120),substrb(dump
(max(substrb("EMAIL_ADDRESS",1,32)),16,0,32),1,120),count("FIRST_NAME"),count(distinct "FIRST_NAME"),sum(vsize("FIRST_NAME")),substrb(dump(min(substrb("FIRST_NA
ME",1,32)),16,0,32),1,120),substrb(dump(max(substrb("FIRST_NAME",1,32)),16,0,32),1,120),count("MIDDLE_NAMES"),count(distinct "MIDDLE_NAMES"),sum(vsize("MIDDLE_N
AMES")),substrb(dump(min(substrb("MIDDLE_NAMES",1,32)),16,0,32),1,120),substrb(dump(max(substrb("MIDDLE_NAMES",1,32)),16,0,32),1,120) from "HR"."PER_ALL_PEOPLE_
F" sample ( 24.7747747748) t

Error encountered: ORA-00904
********************************************************************************

BEGIN dbms_stats.gather_schema_stats('HR',DBMS_STATS.AUTO_SAMPLE_SIZE,cascade=
>true); END;


But if we do

VCOMP11T> desc hr.PER_ALL_PEOPLE_f
Name Null? Type
----------------------------------------- -------- ----------------------------
PERSON_ID NOT NULL NUMBER(10)
EFFECTIVE_START_DATE NOT NULL DATE
EFFECTIVE_END_DATE NOT NULL DATE
PERSON_TYPE_ID NOT NULL NUMBER(15)
LAST_NAME NOT NULL VARCHAR2(150)
EMAIL_ADDRESS VARCHAR2(240)
FIRST_NAME VARCHAR2(150)
MIDDLE_NAMES VARCHAR2(60)
ATTRIBUTE1 VARCHAR2(150)

There is no column names "FULL_NAME"

plz help

Thank,
Atul

Tom

I have been reading your discussion on context switching with interests.

Could you please clarify one thing for me. If I use an Oracle supplied function like INITCAP or NVL in my SQL, does oracle need to do a context switch?

Eg: select INITCAP(ename) from emp;

In other word, does context switching only apply to function and procedures we create by using CREATE FUNCTION f...?

Thanks

Hi Tom,

I was searching an appropriate thread to post my question. Guess this thread might be close.

Not sure whether this would be an appealing problem. Anyway ...

I have the following SQL statement:

select srlno, txm_dt,col2,....col30,MyFunction(srlno, txm_dt)
from tab1
where .....

=> this returns close to 4000 rows.


The function MyFunction accepts two arguments (tab1.srlno and tab1.txm_dt). Based on the argument, tab1.srlno, the function returns the sum of one of the 150 columns of table tab2(I inherited this horiible table design. but have to live with it for the moment untill I get rid of this). The select inside the function looks similar to the one below:

select sum (decode(srlno,1,c1
,2 c2
,3 c3
,....)
)
from tab2
where txm_dt = txm_dt;

Whenever the SQL is executed there is severe performance degradation. Quite obvious: context switches, recursive calls etc etc. "Execute Immediate" (or DBMS_SQL) within the function was even worse due to too many parse related locks on the library cache.

Points to be noted are that: 1. The values of srlno, txm_dt from SQL on tab1 do not have a definitive pattern. 2. We cannot take a procedural approach instead of the SQL on tab1. 3. Could have variable predicates on tab1 (predicates on selective columns will be present always). 4. Data of tab2 is not static, would change frequently.

The actual SQL (tab1 and tab2 are complex views actually) and functions are quite lengthy and complicated. I have tried to convey the problem in a simplistic terms. I personally feel that it is better if we can remove the function call and somehow join the SQL within the function to the original SQL. I am not focusing on having aggregate tables (aka MVs etc) till I have other possibilities / approaches from you.

I will need your experstise to address the problem. Thanks in advance.

Ray

Thanks Tom. As usual crisp and clear answer. Agree on the doing away with the function call. But have a couple of question on the rest.

1. Using the first approach,

select a, b, c, d, (select decode(tab1.srlno, 1, cr1, 2, cr2, .... )
from tab2
where tab2.txm_dt = tab1.txm_dt)
from tab1
if I create a view, chances are that the view SQL would be quite a lengthy one. In that case it is likely that it would take more time to parse. Ours being a very busy database the SQL might phase out after a while, again on next execution it would need to parse the entire thing. My question is just as we can pin an anonymous plsql block can we do the same for any sql?

2. I am not very clear on the internal working of the second approach using

select a,b,c,d,(select myfunction(a,b) from dual)
from tab1

It is true that the function arguments(a,b) will be repeated many times. In that case the statement "select myfunction(a,b) from dual" will require lesser parses. But a) even in cases where the function arguments are repeated will the actual function be executed or not? b) Or is it that for identical values of the arguments Oracle caches the return values as well and do not re execute the function at all? c) I thing b) is possible by using deterministic functions. Please correct me if I am wrong.

Please clarify.
Thanks in advance

Ray

Hi Tom,


1. we use customed function to generate the primary keys for a table.

The function is :

CREATE OR REPLACE FUNCTION gen_pk
(v_id IN NUMBER
)
RETURN NUMBER
IS
CURSOR c_tbl_oid IS
SELECT obj_num FROM table_pk WHERE id = v_id
FOR UPDATE;
v_new_pk NUMBER;
v_tbl_oid c_tbl_oid%ROWTYPE;
nOffset NUMBER;

BEGIN
OPEN c_tbl_oid;
FETCH c_tbl_oid INTO v_tbl_oid;

v_new_pk := v_tbl_oid.obj_num + 1;

UPDATE table_pk
SET obj_num = v_new_pk
WHERE CURRENT OF c_tbl_oid;
CLOSE c_tbl_oid;

SELECT site_id * 268435456 INTO nOffset
FROM TABLE_HEADER;

-- return the value + site_id offset
RETURN (nOffset + MOD( v_new_pk, 268435456 ));
END gen_pk;
/

2. Now we need to insert data into a new table from existing table.
Using
Insert into t1 as select gen_pk(200),t1.* from t1;
-- 200 is the id for that table
3. Problem is that :

t1 has 1 million rows so there would be 1 million recusrive calls to this function.
insert is taking ages.

ways out

a) get the initial pk using the function, then use

variable n number;

exec :n :=gen_pk (200);
insert into t1 as select n+rownum ,t1.* from t1;

and
upadate the refence table table_pk ( which keep track of the latest primary key assigned... so that next can be calculated)
with the value =( select max(primarykey) from t )


b) using sequence

create sequence s cache 1000
insert into t1 as select s.nextval ,t1.* from t1;
and
upadate the refence table table_pk ( which keep track of the latest primary key assigned... so that next can be calculated)
with the value =( select max(primarykey) from t )


c) any other method?


Kindly suggest.

contd above...
sorry I missed to share the Oracle version.

we have 9.2.0.8

Tom,
Thank you so much for your time.

1) I agree with you . by re-inventing the wheel we are making the system non scalable using the mentioned approach.

2) at the moment this function is being used heavily in application for primary key generation, but the task in hand ( ... insert into t .. select from ...) is one off activity and certainly, we can avoid serialisation during this insert.

3) I am thinking use of rownum instead of sequence during this one off insert acitivity. would it be better use of rownum instead of sequence? if yes, how it can be used in 02 bulk inserts each one done one after another?
we have to insert into target table t from the 02 tables t1 and t2 in 02 phases.


phase-1:
insert into t as select rownum,t1.* from t1;

phase-2:
insert into t as select rownum???,t2.* from t2;
^^^^^ what is the best way to use rownum here. i see it equal to -> n_start
where n_start is computed as follows.

variable n1 number;
exec :n1 :=(select max(primary key col ) from t);
variable n_start number;
exec :n_start := n +1 ;

many thanks.
regards

Tom,

4) the presence of mlog ( materliased view log table ) table on table t would be serialisation/non-scalable approach if we do bulk insert into t?
in other words...
would it be good if
i) we create mlog on t
do insert into t
or
ii) do insert into t
create mlog on t.

hi Tom
Thanks for your time.

yes true. serialisation is happenining in day to day operations. and there is an upper limit on primary keys range as well. once consumed all positive range we use negative range then once archived & deleted positive range ( we presume we wont need the old data) then same can be re-used.




.....I cannot even answer this question - as I don't see why you would be creating an MV log all of a sudden, you give NO context for that


sorry not giving the context earlier. the table t in question is a new table being introduced. this table will have data from the existing other tables t1 & t2. we have replicated environment.

table t would appear in master site that needs to be replicated to snapshot site after its creation and initial inserts from t1 & t2 .


further,

regarding use of rownum or sequence in one time activity.
oracle will do less work in case of rownum. as it need not to maintain sys.<table> for sequence 's'. but this would be a tiny work.
when i tkprof it ( sorry the trace is not avaiable with me right now) i seen using sequence

insert into t select sequnce.nextval , t1.* .....
took 103 sec to insert 1/2 million rows and recursive sql ( sys table update) for sequnce maintenance took just 0.45 sec. so i cannot see any issues using sequence over rownum.
is my understanding correct?

thanks.

Tom,

Yes caching more ( 100000) would result into almost no work.
regarding use of function - yes I am with you.
let me see when we shall we start utlising the standard/built-in things.

Thank you so much for your time.