PL/sql table
RS, October 18, 2001 - 10:00 am UTC
Hi Tom ,
I want to do something like this.Doesn't work for me .Please suggest
decalre
type mytableType as table of number index by binary_integer;
x mytabtype;
begin
x(1):=1;
x(2):=2;
declare
cursor mycur is
select a.column_value val
from the ( select cast( x as mytableType )
from dual ) a
begin
------
end;
end;
October 18, 2001 - 12:45 pm UTC
as it says in the example below:
<b>
REM instead of putting a type in a spec, do this:
</b>
ops$tkyte@DEV816> create or replace type myTableType
as table of number;
2 /
Type created.
It MUST be a sql type -- NOT a plsql type.
Need more
READER, October 18, 2001 - 2:41 pm UTC
Hi, TOm,
Could you kindly give me an example of how to set up and manipulate the "SQL" type plsql table which has more then 1 columns?
For example:
SQL>create or replace type myTableType as table of emp%recordtype???? and how to manipulate by sql?
Thanks
October 18, 2001 - 3:34 pm UTC
search for:
myscalartype mytabletype
on my site here. lots of examples (those are the names I use for my examples)
Speed
READER, October 18, 2001 - 4:08 pm UTC
Compare this SQL type Table(in sqlplus) with plsql type table(in olsql), which one is more faster(from thw performance point of view)?
How can we use the index in SQL type table if we want to assign certain values into that structure?
Thanks
October 18, 2001 - 6:20 pm UTC
The are roughly equivalent:
ops$tkyte@ORA717DEV.US.ORACLE.COM> create table run_stats ( runid varchar2(15), name varchar2(80), value int );
Table created.
ops$tkyte@ORA717DEV.US.ORACLE.COM>
ops$tkyte@ORA717DEV.US.ORACLE.COM> create or replace view stats
2 as select 'STAT...' || a.name name, b.value
3 from v$statname a, v$mystat b
4 where a.statistic# = b.statistic#
5 union all
6 select 'LATCH.' || name, gets
7 from v$latch;
View created.
ops$tkyte@ORA717DEV.US.ORACLE.COM> /*
DOC>create or replace view stats
DOC>as select 'STAT...' || a.name name, a.value
DOC> from v$sysstat a
DOC> union all
DOC> select 'LATCH.' || name, gets
DOC> from v$latch;
DOC>*/
ops$tkyte@ORA717DEV.US.ORACLE.COM> column name format a40
ops$tkyte@ORA717DEV.US.ORACLE.COM>
ops$tkyte@ORA717DEV.US.ORACLE.COM>
ops$tkyte@ORA717DEV.US.ORACLE.COM> create or replace type myTableType as table of varchar2(2000)
2 /
Type created.
ops$tkyte@ORA717DEV.US.ORACLE.COM>
ops$tkyte@ORA717DEV.US.ORACLE.COM>
ops$tkyte@ORA717DEV.US.ORACLE.COM> declare
2 type myArrayType is table of varchar2(2000) index by binary_integer;
3
4 l_sql_type myTableType;
5
6 l_plsql_type myArrayType;
7 l_empty myArrayType;
8
9 l_start number;
10 l_string long;
11 begin
12 insert into run_stats select 'before', stats.* from stats;
13
14 l_start := dbms_utility.get_time;
15 for i in 1 .. 1000
16 loop
17 l_sql_type := myTableType();
18
19 for i in 1 .. 500
20 loop
21 l_sql_type.extend;
22 l_sql_type(l_sql_type.count) := rpad( '*', 4*i, '*' );
23 end loop;
24
25 for i in 1 .. 500
26 loop
27 l_string := l_sql_type( dbms_random.value(1,500) );
28 end loop;
29 end loop;
30 dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs' );
31
32 insert into run_stats select 'after 1', stats.* from stats;
33
34 l_start := dbms_utility.get_time;
35 for i in 1 .. 1000
36 loop
37 l_plsql_type := l_empty;
38
39 for i in 1 .. 500
40 loop
41 l_plsql_type(l_plsql_type.count+1) := rpad( '*', 4*i, '*' );
42 end loop;
43
44 for i in 1 .. 500
45 loop
46 l_string := l_plsql_type( dbms_random.value(1,500) );
47 end loop;
48 end loop;
49 commit;
50 dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs' );
51
52 insert into run_stats select 'after 2', stats.* from stats;
53 end;
54 /
4982 hsecs
5127 hsecs
PL/SQL procedure successfully completed.
ops$tkyte@ORA717DEV.US.ORACLE.COM>
ops$tkyte@ORA717DEV.US.ORACLE.COM> select a.name, b.value-a.value run1, c.value-b.value run2,
2 ( (c.value-b.value)-(b.value-a.value)) diff
3 from run_stats a, run_stats b, run_stats c
4 where a.name = b.name
5 and b.name = c.name
6 and a.runid = 'before'
7 and b.runid = 'after 1'
8 and c.runid = 'after 2'
9 and (c.value-a.value) > 0
10 and (c.value-b.value) <> (b.value-a.value)
11 and a.name like '%redo%'
12 order by abs( (c.value-b.value)-(b.value-a.value))
13 /
NAME RUN1 RUN2 DIFF
---------------------------------------- ---------- ---------- ----------
STAT...redo entries 16 14 -2
LATCH.redo writing 64 72 8
LATCH.redo allocation 29 44 15
STAT...redo size 21452 21488 36
I don't understand the second part about "the index in a sql type table" - the sql types are roughly equivalent to the plsql table type -- you might have to EXTEND them first (allocate)....
Randy Richardson, April 16, 2002 - 3:57 pm UTC
I am using the type collection for an IN statement for three columns as the user application can select one or more ID to query. I find performance is dropping using more than one collection type at a time. Here is what I have, ASSOCIATION table has concatenated index "AK_ASSOC_L1" on all five columns location_id,area_id,process_id ...
SQL:
SELECT
E.DESCRIPTION,
A.EQUIP_ID,
AR.DESCRIPTION AR_DESCRIPTION,
PR.DESCRIPTION PR_DESCRIPTION,
ET.DESCRIPTION ET_DESCRIPTION,
A.REQUIRESAPPROVAL
FROM CMR.ASSOCIATION A,
CMR.EQUIPMENT_TYPE ET,
CMR.AREA AR,
CMR.PROCESS PR,
CMR.EQUIPMENT E
WHERE A.LOCATION_ID = 3
AND A.AREA_ID IN (3)
AND A.PROCESS_ID IN (12,13,10)
AND A.EQUIP_TYPE_ID IN (41)
AND A.EQUIP_ID = E.EQUIP_ID
AND A.PROCESS_ID = PR.PROCESS_ID
AND A.AREA_ID = AR.AREA_ID
AND A.EQUIP_TYPE_ID = ET.EQUIP_TYPE_ID
AND A.ASSOC_TYPE_ID = 4
AND A.DELETED = 0
26 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=1 Bytes=107)
1 0 NESTED LOOPS (Cost=7 Card=1 Bytes=107)
2 1 NESTED LOOPS (Cost=6 Card=1 Bytes=76)
3 2 NESTED LOOPS (Cost=5 Card=1 Bytes=64)
4 3 NESTED LOOPS (Cost=2 Card=1 Bytes=42)
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'AREA' (Cost=1 Card=1 Bytes=15)
6 5 INDEX (UNIQUE SCAN) OF 'PK_AREA' (UNIQUE)
7 4 TABLE ACCESS (BY INDEX ROWID) OF 'EQUIPMENT_TYPE' (Cost=1 Card=1 Bytes=27)
8 7 INDEX (UNIQUE SCAN) OF 'PK_EQUIP_TYPE' (UNIQUE)
9 3 INLIST ITERATOR
10 9 INDEX (RANGE SCAN) OF 'AK_ASSOC_L1' (NON-UNIQUE) (Cost=3 Card=1 Bytes=22)
11 2 INLIST ITERATOR
12 11 TABLE ACCESS (BY INDEX ROWID) OF 'PROCESS' (Cost=1 Card=3 Bytes=36)
13 12 INDEX (UNIQUE SCAN) OF 'PK_PROCESS' (UNIQUE)
14 1 TABLE ACCESS (BY INDEX ROWID) OF 'EQUIPMENT' (Cost=1 Card=65077 Bytes=2017387)
15 14 INDEX (UNIQUE SCAN) OF 'PK_EQUIPMENT' (UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
253 consistent gets
0 physical reads
0 redo size
2413 bytes sent via SQL*Net to client
536 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
26 rows processed
SQL With types:
SELECT
E.DESCRIPTION,
A.EQUIP_ID,
AR.DESCRIPTION AR_DESCRIPTION,
PR.DESCRIPTION PR_DESCRIPTION,
ET.DESCRIPTION ET_DESCRIPTION,
A.REQUIRESAPPROVAL
FROM CMR.ASSOCIATION A,
CMR.EQUIPMENT_TYPE ET,
CMR.AREA AR,
CMR.PROCESS PR,
CMR.EQUIPMENT E
WHERE A.LOCATION_ID = 3
AND A.AREA_ID IN (SELECT *
FROM THE(SELECT CAST(STR2TBL('3')
AS MYTABLETYPE) FROM DUAL ) )
AND A.PROCESS_ID IN (SELECT *
FROM THE(SELECT CAST(STR2TBL('12,13,10')
AS MYTABLETYPE) FROM DUAL ) )
AND A.EQUIP_TYPE_ID IN (SELECT *
FROM THE(SELECT CAST(STR2TBL('41')
AS MYTABLETYPE) FROM DUAL ) )
AND A.EQUIP_ID = E.EQUIP_ID
AND A.PROCESS_ID = PR.PROCESS_ID
AND A.AREA_ID = AR.AREA_ID
AND A.EQUIP_TYPE_ID = ET.EQUIP_TYPE_ID
AND A.ASSOC_TYPE_ID = 4
AND A.DELETED = 0
/
26 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=671 Card=656396 Byte
s=95833816)
1 0 HASH JOIN (Cost=671 Card=656396 Bytes=95833816)
2 1 VIEW OF 'VW_NSO_1' (Cost=32 Card=8168 Bytes=106184)
3 2 SORT (UNIQUE) (Cost=32 Card=8168 Bytes=16336)
4 3 COLLECTION ITERATOR (PICKLER FETCH)
5 4 TABLE ACCESS (FULL) OF 'DUAL' (Cost=1 Card=82)
6 1 HASH JOIN (Cost=235 Card=12215 Bytes=1624595)
7 6 HASH JOIN (Cost=155 Card=824 Bytes=98880)
8 7 NESTED LOOPS (Cost=116 Card=98 Bytes=10486)
9 8 HASH JOIN (Cost=18 Card=98 Bytes=7448)
10 9 HASH JOIN (Cost=10 Card=98 Bytes=4802)
11 10 HASH JOIN (Cost=6 Card=98 Bytes=3626)
12 11 INDEX (RANGE SCAN) OF 'AK_ASSOC_L1' (NON-UNIQUE) (Cost=4 Card=98 Bytes=2156)
13 11 TABLE ACCESS (FULL) OF 'AREA' (Cost=1 Card=222 Bytes=3330)
14 10 TABLE ACCESS (FULL) OF 'PROCESS' (Cost=2 Card=2204 Bytes=26448)
15 9 TABLE ACCESS (FULL) OF 'EQUIPMENT_TYPE' (Cost=5 Card=4095 Bytes=110565)
16 8 TABLE ACCESS (BY INDEX ROWID) OF 'EQUIPMENT' (Cost=1 Card=65077 Bytes=2017387)
17 16 INDEX (UNIQUE SCAN) OF 'PK_EQUIPMENT' (UNIQUE)
18 7 VIEW OF 'VW_NSO_2' (Cost=32 Card=8168 Bytes=106184)
19 18 SORT (UNIQUE) (Cost=32 Card=8168 Bytes=16336)
20 19 COLLECTION ITERATOR (PICKLER FETCH)
21 20 TABLE ACCESS (FULL) OF 'DUAL' (Cost=1 Card=82)
22 6 VIEW OF 'VW_NSO_3' (Cost=32 Card=8168 Bytes=106184)
23 22 SORT (UNIQUE) (Cost=32 Card=8168 Bytes=16336)
24 23 COLLECTION ITERATOR (PICKLER FETCH)
25 24 TABLE ACCESS (FULL) OF 'DUAL' (Cost=1 Card=82)
Statistics
----------------------------------------------------------
15 recursive calls
36 db block gets
4410 consistent gets
78 physical reads
0 redo size
2413 bytes sent via SQL*Net to client
536 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
20 sorts (memory)
0 sorts (disk)
26 rows processed
For some reason CBO wants to do full table scans on area,process and equip_type. I have Oracle 8.1.7.3 and all values in the above query are parameters in a package returning a ref curser.
The application currently uses dynamic sql from the application server but this query is called alot and the sql area have about 60 different versions of it so I wanted to use a procedure and bind variables.
Thanks..
April 16, 2002 - 9:48 pm UTC
in ( a, b, c )
can be processed very differently then
in ( sub-query )
sub-query can return an indeterminate number of rows. in (a,b,c) has 3 values forever. INLIST Iterator can be used.
Try joining to the collection instead of "in'ing" on it. Play with the query -- try other constructs, even consider doing a cartesian product of the 3 string to tables and joining to that.
All else fails -- use the values in the IN LIST for issue:
alter session set cursor_sharing=force;
open the query
alter session set cursor_sharing=exact;
to "auto-bind" it.
Randy Richardson, April 17, 2002 - 11:13 am UTC
Thanks for the feedback.
Can you show how to join to the collection. I'm tring:
--------------------------------
SQL> SELECT * FROM THE
2 (SELECT CAST (STR2TBL ('1,2,3') AS MYTABLETYPE)
3 FROM DUAL);
COLUMN_VALUE
------------
1
2
3
create table junk (area_id number);
Table created.
SQL> create table junk (area_id number);
Table created.
SQL> insert into junk values(1);
1 row created.
SQL> insert into junk values(2);
1 row created.
SQL> insert into junk values(3);
1 row created.
SQL> insert into junk values(4);
1 row created.
SQL> select a.area_id
2 from junk a , (SELECT * FROM THE
3 (SELECT CAST (STR2TBL ('1,2,3') AS MYTABLETYPE)
4 FROM DUAL)) b
5 where a.area_id = b.*;
where a.area_id = b.*
*
ERROR at line 5:
ORA-01747: invalid user.table.column, table.column, or column specification
----------------------------------
I can't seem to be able to lable the COLUMN_VALUE to do the join.
Thanks.
April 17, 2002 - 12:52 pm UTC
the column name -- as shown in sqlplus -- is column_value. = b.column_value
Randy Richardson, April 17, 2002 - 11:45 am UTC
I got it. Kind of ugly though.
select a.area_id
from junk a , (SELECT c.column_value FROM THE
(SELECT CAST (STR2TBL ('1,2,3') AS MYTABLETYPE)
FROM DUAL)c) b
where a.area_id = b.column_value;
April 17, 2002 - 12:55 pm UTC
if you have a more current release of Oracle, it is "less ugly"
ops$tkyte@ORA817DEV.US.ORACLE.COM> create type myTableType as table of number;
2 /
Type created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace function f( p_n in number ) return myTableType
2 as
3 l_data myTableType := myTableType();
4 begin
5 for i in 1 .. p_n loop
6 l_data.extend;
7 l_data(l_data.count) := i;
8 end loop;
9 return l_data;
10 end;
11 /
Function created.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from TABLE(cast(f(5) as myTableType));
COLUMN_VALUE
------------
1
2
3
4
5
ops$tkyte@ORA817DEV.US.ORACLE.COM>
A reader, October 31, 2002 - 8:43 pm UTC
Tom,
I trying to create a function using collections, if i pass empno it should return me ename, sal, deptno of that employee..
here is my code..
SQL> create or replace type myTableType as table of varchar2(50);
2 /
Type created.
SQL> ed
Wrote file afiedt.buf
1 create or replace function f( p_n in number )
2 return myTableType
3 as
4 l_data myTableType := myTableType();
5 begin
6 for i in ( select ename||' '||sal||' '||deptno from emp where empno=p_n ) loop
7 l_data.extend;
8 l_data(l_data.count) := i;
9 end loop;
10 return l_data;
11* end;
SQL> /
Warning: Function created with compilation errors.
SQL> sho err
Errors for FUNCTION F:
LINE/COL ERROR
-------- ---------------------------------------------------8/21 PL/SQL: Statement ignored
8/45 PLS-00382: expression is of wrong type
October 31, 2002 - 9:38 pm UTC
6 for i in ( select ename||' '||sal||' '||deptno data from emp where
empno=p_n ) loop
7 l_data.extend;
8 l_data(l_data.count) := i.data;
9 end loop;
How about returning multiple columns..
pasko, November 01, 2002 - 4:02 am UTC
Hi Tom,
thanks for a Great Discussion!
I have a question though :
I see that you are returning only one value :
column_value
Do we call this a pseudo column ?
What do i do i want to return multiple values .
Can my sql table type be of Type of
Database_Table%RowType ?
Thanks.
Performance Doubts
sundeep, December 18, 2002 - 7:45 pm UTC
Tom,
We have 8174 db and I am finding it very hard to tune queries with collection parameters e.g in predicates like:
WHERE eqp.equipment_id IN (SELECT *
FROM TABLE(CAST(equipment_id_arr AS num_arr)))
I also played around with using in-line views e.g:
FROM equipment eqp,
(SELECT column_value equipment_id
FROM TABLE(CAST(equipment_id_arr AS num_arr))) eqp_list
WHERE eqp.equipment_id = eqp_list.equipment_id
Both resulting in terrible execution plans (do full table scan of a large equipments table etc. The befuddling part is I can't seem to figure out the execution plan. So far it seems to work better to convert the collection of ids or strings to a comma seperated list and using dynamic sql using IN operator.
The idea of using arrays as stored procs parameters is appealing in many instances but unlike regular queries, I find it really hard to figure out why optimizer is choosing a certain plan when collections are used as mentioned above. Almost any query with object type parameters poses a conundrum and results in an unexpected and difficult to tune plan. Please shed some more light....
How in form
A reader, January 03, 2003 - 1:23 pm UTC
Hi, TOm,
I tried this method in local procedure in FROM(6i),
what I did is declare a local variable:
v sql_tab_type; --- "sql_tab_type" created as follows:
--SQL> create or replace type
--sql_tab_type as TABLE of VARCHAR(80)
--/
but it give me error:
RPC can not use variable in schema level user-define
types in this realeas.
My FORM is 6i and DB is 8.1.7.00
Would mind tell me the work around?
THanks
January 03, 2003 - 2:27 pm UTC
the variable must live in the server -- especially for SQL to be able to select from it. You'll need to put this in a packaged procedure in the SERVER, not on the client.
Great solution but slow....
Rizwan Qazi, January 28, 2003 - 12:44 pm UTC
Tom,
Is there any way I can make the following select fast:
select a.column_value val
from THE ( select cast( l_x as mytableType )
from dual ) a
I mean not the exact sql, but anything involving object table type...when I tried it is very very slow compared to other methods which does not involve object table types..
Thanks in advance,
Rizwan
January 28, 2003 - 3:04 pm UTC
its fast for me. give me a concrete example to work with.
Explain Plan
Vipin, June 09, 2003 - 4:26 pm UTC
Hi Tom,
Could you please explain why we are not able to see the complete explain plan of following statement:-
select Deptno, Dname,
Cursor(Select empno,
ename,
sal
from Emp
where Deptno = Dept.Deptno
) the_details
From Dept
Where Dname like 'ACC%'
We are able to see only the plan for selection from DEPT. But not able to see from EMP.
June 09, 2003 - 6:59 pm UTC
because scalar subqueries do not CURRENTLY appear in explain plans. (currently....)
PL/SQL Table with Join Operation
Mohan K, July 19, 2003 - 2:43 am UTC
I am having a PL/SQL table index by integer TYPE defined in a package spec. I want to use this type to declare a vbariable in another package body. How to do this.
Also I have stored a set of values using the above type in a package. I want to use the same values in another package.
I do not want to use SQL objects since they cannot be indexed. I do not want to use global temporary tables since this will generate error if I use the function from a SQL query. Any solution.
Mohan
July 19, 2003 - 11:40 am UTC
declare
variable package_name.type_name;
begin
.....
i don't understand half of the second paragraph -- gtts have an error?
but anyway, just put the variables in the spec and access them via package_name.variable_name.
collection query mystery
A reader, August 15, 2003 - 5:06 pm UTC
Hi Tom
I have a strange situation - I have two queries that look like:
query1
select a.timestamp1
bulk collect into l_timestampList
where <where clause>;
Then I use the above l_timestampList variable in query 2
query2
SELECT count(*)
into l_count
FROM table1 a,
TABLE ( CAST ( l_timestampList AS timestampTable)) b
WHERE a.timestamp1 = value(b)
This gives me bunch of records. However, when I try
to avoid the collection variable above and directly
put the select in query1 in query2 - I dont get any
records selected - this query would look like:
SELECT count(*)
into l_count
FROM table1 a,
(
/* this is query1 */
select a.timestamp1
where <where clause>
) b
WHERE a.timestamp1 = b.timestamp1;
I am not sure why this happens.
Has it anything to do with timestamp format potentially?
(Don't think so)
I would post the original queries if those are required..but thought may be you can think of
something of the top of your head ( the crystal ball may
not be in the shop, perhaps!;) - just kidding!)
Thanx for a great site!!
August 15, 2003 - 8:19 pm UTC
is "a.timestamp1" a date?
is l_timestamplist a collection of varchar2?
could be as simple as time truncation on a date...
so many questions -- how about you post a complete, terse, test case?
thanx Tom!
A reader, August 15, 2003 - 8:41 pm UTC
"is "a.timestamp1" a date?"
Yes
" is l_timestamplist a collection of varchar2?"
No it is a collection of dates...
I will try and post a test case asap - it will take
some time for me to make it terse enough.
Thanx a bunch!!
PL/SQL Tables and Join Conditions
Anand, May 04, 2004 - 10:57 am UTC
Hi Tom...
I have a problem in using the PL/SQL tables with join in a normal query.
Let me try to explain my requirement...
(1) I need to construct a dynamic where condition where the number of join conditions might vary depending on the settings in site where the software is installed and along with that the values that i have to pass as bind variables. I hope it is not possible to construct the USING clause dynamically. I can either use a REF cursor or a EXECUTE IMMEDIATE for doing this. I cannot use a SQL Object type as it is restricted by the development standards.
(2) the Dynamic WHERE clause thus constructed needs to be added to a query to fetch the result set either into a PL/SQL table or any normal table and the results has to be either inserted into a temporary table or any persistent database table.
(3) I need to open a cursor on the table in which the resultset from the previous set can be inserted where the result set is stored, and use the data in that table for further processing. I am not able to use a REF CURSOR as it gets reset everytime i open this procedure...
My code has Procedure A from package X calling Procedure B in Package Y inside a WHILE LOOP. Procedure B has all the above steps from (1) to (3). I need to open the Cursor once and fetch record by record in the next iteration... I tried using a REF cursor, but it gets reset every time and always gives me the first Record.
I hope I was atleast half successful explaining my situation.
Please let me know of any way by which I can achive this.
Anand
May 04, 2004 - 1:32 pm UTC
ahh, just like the old salem witch trials
o we are afraid of something
o so we'll outlaw it
o and go on a crusade against it.
brilliant.
anyway....
</code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1288401763279 <code>
shows how to do #1. Just be prepared to be tuning for the rest of your life once you get customers with different situations.............
watch out, generic code = slow code = you'll be supporting stuff forever.
I don't understand your point #3 at all.
Performance of PL/SQL table (ie Index by Table) in 8.1.7.4.0
Greg, June 11, 2004 - 3:21 pm UTC
Hi Tom,
Just wondering if you could give me some ideas to work on here .. (I'm not expecting a solution on this - don't panic ... ) ;)
The code surrounding this issue is pretty large and extensive, and I can't post it (besides - it'd just scare you .. hehe ) so I'm just looking for ideas and issues to watch for ... I should be able to dig into more specific cases on my own based on that.
The issue at hand (in a nutshell):
We have a program running in our batch which normally runs for about 10-15 minutes. During this time it processes about 10,000 records. (it's doing a lot of checks, etc ... )
Occasionally, a certain piece of code runs, and the job takes "forever" .. it doesn't hang ... it just runs very very slowly.
I've monitored the performance of this job, and it starts off well: (ie ~1000 records per minute or better), but quickly this rate declines. Over the period of about 5 minutes, I've watched it processing at a rate of 700, then 500, then 200, then 100, then 50 .. etc... it just - quite literally - bogs down, running slower and slower the longer it runs.
I managed to dig into the code and isolate the lines of code slowing it down - as you may have guessed by my choice of where to post this question - it deals with Index By Tables.
It has a small loop (~5 items), using .COUNT, .FIRST, .NEXT as well as GREATEST and ABS functions .. nothing magical, yet, this loop slows it down slightly ..
The bigger concern I have are the lines that follow this loop. They are simple assignments .. such as:
lt_tab(ln_inv).some_field := some_value;
There's about 4 of these lines, and trust me, they are this simple.
I've tried duplicating the problem using simpler code and cannot ... which tells me that there must be something else interacting with this PL/SQL table (possibly other code, or something else in Oracle??) ... and that's what I'm looking for ideas for ...
I've monitored machine performance while the job is running, used OEM to study what's going on .. no ideas ..
Again ... I'm not asking for solutions - I've hardly given you anything to work with .. ;) ... but I am hoping you can drop some ideas on me .. things to look for, things that clash with pl/SQL tables, etc ...
Anything .. really ...
This is what I do know:
- there are about 10 lines of code, a small loop, and a few lines of assignments, which - when commented out - improves performance of the job 10 fold, and prevents any degradation of the job as it runs. No other code, when commented out, has this same effect.
- The loop is a very simple loop, no more than 5 iterations, which search for a "maximum" value within a pl/SQL table (using GREATEST and ABS functions)
- The assignments are straight forward, no complex math formulas, or "hidden" functions ... straight "A=B" type assignments.
- The pl/SQL tables are of a pre-defined RECORD type, all fields (about 7 or 8) are of type NUMBER. (2 are NUMBER, but are defined as table.row%TYPE, as opposed to NUMBER (ie that is, the table/columns it's declared off of, are NUMBER ).
- The pl/SQL table that seems to be causing the most problem is indexed by a large integer (eg 1342568 or 69876004 ) - not that there are that many items in the pl/sql table ... but it is using those numbers as indexes .. I suspect part of the problem is this is creating a large pl/sql table which is consuming a lot of memory ... the longer the program runs, the more it stores, the more it consumes, the slower it runs .. (again - keep in mind - this thing only runs through about 10,000 accounts ... ) :\
I'm not asking for ideas to re-write .. we have already removed this code from our primary process (it's actually not needed) ... what I'm trying to figure out is "why" it slows down, not "how" to fix it .. and I need to do alot more digging to find that out .. :)
Thanks ahead of time for any ideas or directions you can point me to ..
June 11, 2004 - 4:44 pm UTC
have you measured the pga size while this is running?
are you perhaps paging like mad while this is happening (at the os level)?
Performance of PL/SQL table (ie Index by Table) in 8.1.7.4.0
Greg, June 11, 2004 - 5:17 pm UTC
We monitored a bunch of stats in Oracle .. but I guess we overlooked the pga .. :o ... we'll check that out on Monday .. thanks.
As for the o/s .. we're on Unix, and we did monitor the system quite closely on that one ... we didn't see any large swapping or anything else out of the ordinary outside of Oracle ...
We'll try looking at the pga on Monday ...
Anything else specific to pl/sql tables? or is this a unique behaviour you've never seen with them? (again - implying it's actually something else ... ) :\
Thanks again for your time on this ... :)
June 11, 2004 - 5:29 pm UTC
i've never attempted to use sparse PLSQL tables -- with random indices. I've only used them as conventional arrays. I know for "associative arrays" (where the index is a string), they made changes to the algorithm.
I haven't benchmarked the affect of sparse table types like that though.
anyone else?
Everyone likes a happy ending ...
Gabe, June 14, 2004 - 1:57 pm UTC
I was hoping to see some follow-ups to Greg's problem Â… and hear from him on what the final answer/resolution was.
Just to (hopefully) keep it alive Â… I would venture the opinion that I doubt Oracle would not have benchmarked sparse arrays upon their introduction.
To eliminate the sparse-factor as the _villain_ I suggest the following Â… leave the 10 lines in the code in but replace any occurrences like Â…
lt_tab(ln_inv).some_field := some_value;
with
lt_tab(1).some_field := some_value;
for all pl/sql tables involved in those 10 lines.
That is, just write over everything into the first element. If this eliminates the slowdown, one could even try to write into some _high_ element:
lt_tab(999999999).some_field := some_value;
just to see if it makes any difference (very much doubt it).
One question for Greg Â… assuming the code is in a package, where are those pl/sql arrays being defined? Â… local to some packaged procedure/function? Â… in the package body? Â… in the package spec?
The sure option is of course to post the code (or a slimmed-down variant exhibiting the slow-down) Â… IÂ’m sure the resolution will come out pretty quickly after that.
Still searching ... ;)
Greg, June 15, 2004 - 10:41 am UTC
Thanks Tom for the suggestions, I'm still trying to narrow down the root cause of this sucker ..
Gabe - thanks for the suggestion .. I did try changing the pl/sql table access to just hardcoded (1) instead of the (inv_num) ... then re-ran ... still slowed down.
And just to double-check .. I commented out only those lines and re-ran ... and it's not acting the same today :o
I'll be back .. I gotta re-gather the troops ... ;)
But I do have a few things to dig into .. thanks everyone .. once I get more (consistent) results ... I'll be back .. :)
(I'm suspecting more and more that these lines I've isolated are more of a catalyst, then the cause ... if you know what I mean ....)
Later ... for now ...
Gabe - it is quicker ...
Greg, June 15, 2004 - 11:06 am UTC
Gabe - changing the index to a fixed: (1) or even (99999999) also fixes the problem .. it flies ...
Put it back to the original (inv_num) (which is typically an 8 to 10 digit number) and it bogs right down ...
I still haven't checked the pga yet, Tom .. sorry ... been busy - you know how it is!! ;)
And yeah, I'm still trying to duplicate the problem in a simple enough script to post here ... as it stands now, I'd have to post several thousand lines of code so you guys could see what I was running ... ;) Not only is that bad for you - but I doubt my company would appreciate me posting their code all over the internet .. hehe
Oh, and to answer another question asked: yes, it is in packages, the pl/sql tables are defined in the package spec.
Each table is declared based on a record type declared a few lines above - all values in those records types are of type: NUMBER ...
The pl/sql table which we're looking at is declared like this:
TYPE rec_acct_list IS RECORD
(inv_num investment.inv_num%TYPE, <- NUMBER(10)
max_dev NUMBER,
threshold NUMBER );
TYPE tab_acc_list IS TABLE OF rec_acct_list INDEX BY BINARY_INTEGER;
I'm still reverse-engineering this code myself .. so I can't really tell much more than this ... again - based on my original post to Tom - at this point - I'm not asking you guys for solutions .. just ideas to go on .. :) As I dig more - I'll be able to come up with more specific - more relevant questions .. hehe
Thanks!!
If I had known I would found this - I wouldn't have posted the last 2 so quickly .. ;)
Greg, June 15, 2004 - 12:17 pm UTC
Ok, sorry for the multiple posts in such a quick time - but I think I've got something ... looks like it *might* have something to do with the pl/sql tables being passed into a procedure via IN/OUT parameter ... (I'm going to be working on changing how this routine works - so again - I don't need suggestions on how to fix this routine as much as I'm just trying to understand what's happening ... I know how to fix - I just don't understand why it's happening!) :)
Here's a test script I've managed to put together:
=================================================
set serverout on
DECLARE
-- misc vars
lv_junk VARCHAR2(4000);
-- array def'ns ...
TYPE rec_acct_list IS RECORD
(
inv_num NUMBER,
deviation NUMBER,
threshold NUMBER
);
TYPE tab_acct_list IS TABLE OF rec_acct_list
INDEX BY BINARY_INTEGER;
lt_inv tab_acct_list;
-- procedure to loop multiple times ...
PROCEDURE p_do ( in_pass IN NUMBER,
in_inv IN OUT tab_acct_list )
IS
ln_dev NUMBER := .345;
ln_max NUMBER := 10;
ln_inv NUMBER;
BEGIN
FOR c_loop IN
( SELECT inv_num
FROM inv_list
WHERE group_num = in_pass )
LOOP
ln_inv := c_loop.inv_num;
in_inv(ln_inv).inv_num := ln_inv;
in_inv(ln_inv).deviation := ln_max;
in_inv(ln_inv).threshold := ln_dev;
END LOOP;
END p_do;
BEGIN
-- 1st pass
lv_junk := TO_CHAR ( sysdate , 'dd-mon-yyyy hh24:mi:ss' );
p_do ( 1, lt_inv );
lv_junk := lv_junk || ' <> ' || TO_CHAR ( sysdate , 'dd-mon-yyyy hh24:mi:ss' );
DBMS_OUTPUT.PUT_LINE ( lv_junk );
-- 2nd pass
lv_junk := TO_CHAR ( sysdate , 'dd-mon-yyyy hh24:mi:ss' );
p_do ( 2, lt_inv );
lv_junk := lv_junk || ' <> ' || TO_CHAR ( sysdate , 'dd-mon-yyyy hh24:mi:ss' );
DBMS_OUTPUT.PUT_LINE ( lv_junk );
-- 3rd pass
lv_junk := TO_CHAR ( sysdate , 'dd-mon-yyyy hh24:mi:ss' );
p_do ( 3, lt_inv );
lv_junk := lv_junk || ' <> ' || TO_CHAR ( sysdate , 'dd-mon-yyyy hh24:mi:ss' );
DBMS_OUTPUT.PUT_LINE ( lv_junk );
-- 4th pass
lv_junk := TO_CHAR ( sysdate , 'dd-mon-yyyy hh24:mi:ss' );
p_do ( 4, lt_inv );
lv_junk := lv_junk || ' <> ' || TO_CHAR ( sysdate , 'dd-mon-yyyy hh24:mi:ss' );
DBMS_OUTPUT.PUT_LINE ( lv_junk );
-- 5th pass
lv_junk := TO_CHAR ( sysdate , 'dd-mon-yyyy hh24:mi:ss' );
p_do ( 5, lt_inv );
lv_junk := lv_junk || ' <> ' || TO_CHAR ( sysdate , 'dd-mon-yyyy hh24:mi:ss' );
DBMS_OUTPUT.PUT_LINE ( lv_junk );
END;
/
=================================================
And you'll need a table that looks kinda like this:
create fo_list (group_num number(5), inv_num number(10) )
/
I've populated it with 5 groups each of 10,000 records.
(duplicates don't matter too much ... but unique is better)
For example:
GROUP_NUM INV_NUM
---------- ----------
1 105031
1 105032
1 105033
1 161984
<snip>
2 8355018
2 8355019
2 8369027
<snip>
... etc ...
just toss in large numbers for (inv_num), doesn't matter what ... and the setup 5 groups ... Just make sure to have the same # of records in each group. No indexes on the table - just let it do a full table scan ... because of the logic in the cursor, Oracle will have to do a full table scan for each process - so that should be the same for each and not contribute to the slow down ..
The script calls the procedure: p_do 5 times, because it's the easiest way I knew of to see the slow down on repeated calls - I capture the time to start/end each call ...
Either:
a) create large groups
or
b) Call the procedure p_do more times ..
And when it's finished, you should see a slow down from the first job to the last ... it keeps running slower ...
Here's the results from 1 of my runs .. it's unfortunately small numbers - but it does show the slow down ...
=========================
@test
15-jun-2004 11:57:05 <> 15-jun-2004 11:57:07
15-jun-2004 11:57:07 <> 15-jun-2004 11:57:09
15-jun-2004 11:57:09 <> 15-jun-2004 11:57:12
15-jun-2004 11:57:12 <> 15-jun-2004 11:57:16
15-jun-2004 11:57:16 <> 15-jun-2004 11:57:21
PL/SQL procedure successfully completed.
==========================
Considering each call to the procedure processes the same # of records, this shouldn't be the case .. obviously in the original code, this slow down is much more pronounced ... (I just wish I could duplicate that degree of slow down in a test case like this .. *sigh* )
---------
I guess the question now is: Does anyone know why a PL/SQL table passed as an IN/OUT parameter slow Oracle down? (I also tried it with two seperate IN and OUT parameters .. still slowed down ..)
---------
Oh yeah, and btw .. I did test this without the IN/OUT - that is, only pass the 1st argument to the p_do proc, and change: in_inv lines to: lt_inv (ie access the global variable directly), and it doesn't slow down ... ;)
-----------------
@test
15-jun-2004 12:00:44 <> 15-jun-2004 12:00:46
15-jun-2004 12:00:46 <> 15-jun-2004 12:00:48
15-jun-2004 12:00:48 <> 15-jun-2004 12:00:49
15-jun-2004 12:00:49 <> 15-jun-2004 12:00:51
15-jun-2004 12:00:51 <> 15-jun-2004 12:00:52
PL/SQL procedure successfully completed.
----------------------
June 15, 2004 - 5:21 pm UTC
well, each time you call that you are doing a COPY on the way in and a copy on the way out!
and each time the array is 10,000 elements bigger than the last time.
IN OUT variables are copied to a temporary on the way in, they are copied to the permanent variable on the successful "exit"
You can try "NOCOPY" which changes the behaviour (a pointer to the array is passed, not a copy of it -- but it means if the procedure raises and exception, the modifications it made to the array are "made" -- whereas with copy they would not be)
What do they do with them arrays?!?!
Gabe, June 15, 2004 - 11:47 pm UTC
Hey Greg,
<quote>I just wish I could duplicate that degree of slow down in a test case like this</quote>
Your test script only loads those arrays ... in the code they are surely used for something ... and if there is some sort of scanning/processing of the arrays in between those incremental loads then the scanning will get slower and slower.
<quote>I'm still reverse-engineering this code myself</quote>
If I were in your place I would investigate why is there a need to load that much data in pl/sql tables ... what exactly are they doing with those arrays that cannot be done in straight SQL. And even if they are somehow needed ... why don't deallocate the arrays in between the loads? ... is there indeed a need to load 10K records then another 10K in top of that and so on? Maybe ... but get your own answers on it.
Anyway, it looks like the slowdown has been explained (at least a big part) ... so good luck with "reverse-engineering" the rest of the code ... few thousand lines of code? ... that should be just another couple of hours !! :)
Thanks, Tom!
Greg, June 16, 2004 - 8:53 am UTC
Tom, yes, thank-you .. I was just starting to look into those options myself ... once I narrowed it down to those pesky IN/OUT parameters .. you're points are well received, thank-you .. (saves me hours trying to look it up .. hehe)
Sorry to have taken so much of your time on this one ...
Gabe:
Yes, the test script only loads it, as I pointed out in my original post, it's those lines that are causing the slow down .. not the reading it later ...
And no, they don't need those lines - as I have indicated, I am already in the midst of re-working this code ... (part of which is to bypass these lines - because they are not needed in the particular case where we notice the slow down).
Again .. the reason I wrote was to get figure out "why" it was slowing down ... not to ask what to do to fix it (the reason of course was because we didn't need those lines - so yes I have already removed them - my managers, of course, wanted a more extensive explanation on what happened, and more importantly: "Why" it happened - so we can avoid it in the future). Of which Tom (and you) have helped me to pin-point ... thank-you both for your time and your help!!
are they same?
Sajid Anwar, June 16, 2004 - 9:39 am UTC
Hi Tom,
In your reply to the original question of this thread. You said :
REM here is an example of selecting from a 'static' dynamic
REM table. this example returns 2 rows...
ops$tkyte@DEV816> select a.column_value val
2 from the (select cast( myTableType(1,2) as mytableType )
3 from dual ) a
4 /
VAL
----------
1
2
My question is if I do the following, would it be better in any way?
sql@dev920> select a.column_value val
2 from table(cast( myTableType(1,2) as mytableType ));
VAL
----------
1
2
As we get the same output from both queries, I would like to know how oracle would interpret these queries internally.
Many thanks in advance.
Regards,
Sajid Anwar
London
June 16, 2004 - 12:56 pm UTC
the "the" syntax was the original 8.0 syntax, deprecated.
use table(cast(...
which is better in terms of performance?
Sajid Anwar, June 16, 2004 - 11:49 am UTC
Hi Tom,
In reference to your original reponse where you said :
10 -- How to fill a nested table from a query:
11
12 select cast( multiset( select user_id
13 from all_users
14 where rownum < p_x )
15 AS myTableType )
16 into g_x
17 from dual;
If I do something like this :
select user_id bulk collect
into g_x
from all_users
where rownum < p_x;
Would it be better ?
Thanks in advance for your input.
Regards,
Sajid Anwar
London
June 16, 2004 - 1:24 pm UTC
today, yes.
when first answered (pre-bulk collect) no :)
Collections drive CBO mad
sergei, September 03, 2004 - 12:31 pm UTC
It is really amazing how using of plsql collection affects CBO and thus performance. Example is below. Is there any way to avoid full scan when using collections? I was trying different joins, hints etc. No result:(
shevelev@AEK> create table t_big(id primary key) as select rownum id from all_objects;
Table created.
shevelev@AEK> analyze table t_big compute statistics;
Table analyzed.
shevelev@AEK> select count(*) from t_big;
COUNT(*)
----------
120103
shevelev@AEK> set timing on
shevelev@AEK> set autotrace on explain
shevelev@AEK>
shevelev@AEK> -- try to use IN-LIST -- works fine, index scan is being used
shevelev@AEK>
shevelev@AEK> select * from t_big where rowid in (select rowid from t_big where id in (1, 2, 3));
ID
----------
1
2
3
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=3 Bytes=48)
1 0 NESTED LOOPS (Cost=5 Card=3 Bytes=48)
2 1 INLIST ITERATOR
3 2 INDEX (RANGE SCAN) OF 'SYS_C004036' (UNIQUE) (Cost=2 Card=3 Bytes=24)
4 1 TABLE ACCESS (BY USER ROWID) OF 'T_BIG' (Cost=1 Card=1 Bytes=8)
shevelev@AEK>
shevelev@AEK> -- try to use select from table() -- full scan is performed
shevelev@AEK>
shevelev@AEK> select * from t_big where rowid in (select rowid from t_big where id in (select * from
table(t_integer_table(1, 2, 3))));
ID
----------
1
3
2
Elapsed: 00:00:01.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2041977 Card=1 Bytes=16)
1 0 NESTED LOOPS (SEMI) (Cost=2041977 Card=1 Bytes=16)
2 1 HASH JOIN (Cost=226 Card=120103 Bytes=1921648)
3 2 TABLE ACCESS (FULL) OF 'T_BIG' (Cost=30 Card=120103 Bytes=960824)
4 2 TABLE ACCESS (FULL) OF 'T_BIG' (Cost=30 Card=120103 Bytes=960824)
5 1 COLLECTION ITERATOR (CONSTRUCTOR FETCH)
shevelev@AEK>
shevelev@AEK> -- create regular table instead of collection -- works fine
shevelev@AEK>
shevelev@AEK> create table ttt(id number);
Table created.
shevelev@AEK> insert into ttt SELECT * FROM table(t_integer_table(1, 2, 3));
3 rows created.
shevelev@AEK> select * from t_big where rowid in (select rowid from t_big where id in (select * from
ttt));
ID
----------
1
2
3
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=88 Card=82 Bytes=2378)
1 0 NESTED LOOPS (Cost=88 Card=82 Bytes=2378)
2 1 NESTED LOOPS (Cost=6 Card=82 Bytes=1722)
3 2 SORT (UNIQUE)
4 3 TABLE ACCESS (FULL) OF 'TTT' (Cost=2 Card=82 Bytes=1066)
5 2 INDEX (UNIQUE SCAN) OF 'SYS_C004036' (UNIQUE)
6 1 TABLE ACCESS (BY USER ROWID) OF 'T_BIG' (Cost=1 Card=120103 Bytes=960824)
September 03, 2004 - 1:40 pm UTC
Why this hint is not documented?
sergei, September 06, 2004 - 2:45 am UTC
Thanks, it really works!
But... If the *cardinality* hint is useful in some situations and sometimes even the only way to go, why it is not documented?
Using stragg
A reader, September 13, 2004 - 5:34 pm UTC
I have the following
create or replace type varchar2_tab as table of varchar2(4000);
declare
type x_t is table of varchar2(100) index by varchar2(100);
x x_t;
y varchar2(400);
begin
x('one'):='1';
x('two'):='2';
x('three'):='3';
-- How can I get the stragg of the contents of 'x' into "y" above?
end;
i.e. I want to get "1,2,3" (or the subscripts "one,two,three") into "y" above
I tried using cast and multiset and stuff but couldnt quite get the syntax right.
Help? Thanks
September 13, 2004 - 8:55 pm UTC
with an associative array, you are not going to "select from it"
ops$tkyte@ORA9IR2> declare
2 type x_t is table of varchar2(100) index by varchar2(100);
3 x x_t;
4 y varchar2(400);
5
6 l_str long;
7 l_idx varchar2(100);
8 begin
9 x('one'):='1';
10 x('two'):='2';
11 x('three'):='3';
12
13 l_idx := x.first;
14 while (l_idx is not null)
15 loop
16 l_str := l_str || ',' || x(l_idx);
17 l_idx := x.next(l_idx);
18 end loop;
19 dbms_output.put_line( ltrim( l_str , ',' ) );
20
21 l_str := null;
22 l_idx := x.first;
23 while (l_idx is not null)
24 loop
25 l_str := l_str || ',' || l_idx;
26 l_idx := x.next(l_idx);
27 end loop;
28 dbms_output.put_line( ltrim( l_str, ',' ) );
29 end;
30 /
1,3,2
one,three,two
PL/SQL procedure successfully completed.
A reader, September 13, 2004 - 10:22 pm UTC
Hm, why cant I 'select' from an associative array's contents? i.e. why cant I cast(multiset()) the contents of the array as a object type table of varchar2(4000)?
Forget about the string subscripts, if I just want to access the data at those subscripts, how is that any different than the examples of mytabletype you presented earlier on this page?
Would this be possible if this were a regular pl/sql (index by binary_integer) table?
September 14, 2004 - 7:41 am UTC
because you can only do that to SQL types.
what are you going to "cast" that plsql type as?
it differs in that it didn't do this:
ops$tkyte@DEV816> create or replace type myTableType
as table of number;
2 /
Type created.
to A Reader
Jan, September 14, 2004 - 4:35 am UTC
because
variables declared inside of a PL/SQL are not visible by SQL
A reader, September 14, 2004 - 9:40 am UTC
"it differs in that it didn't do this:
ops$tkyte@DEV816> create or replace type myTableType as table of number;"
No, I had the same SQL type in my example
create or replace type varchar2_tab as table of varchar2(4000);
Why cant I "cast" the assoc array to this object type?
September 14, 2004 - 11:09 am UTC
you didn't use that varchar2_tab anywhere.
You could certainly
a) declare a variable of type varchar2_tab
b) select from it
but you CANNOT declare a variable of
type x_t is table of varchar2(100) index by varchar2(100);
and select from it. In your example, varchar2_tab was quite simply "not relevant", don't even understand why it was there. An associative array is a 'plsql programming construct', it is not a varchar2_tab, it is not castable as a varchar2_tab
Returning a number
smay, September 14, 2004 - 12:23 pm UTC
Rather than using a table type as you've shown here, I have been attempting to just return a number from a function, and include the call to that function in a from clause. When I run the select alone, it works fine, but when I put the select in a proc (or even just a begin .. end), I get "expression is of wrong type".
FUNCTION sel_CompoundID (CorpID IN VARCHAR2) RETURN NUMBER
AS
CompoundID NUMBER;
BEGIN
select number_column
into CompoundID
from ...;
return CompoundID;
END sel_CompoundID;
This works fine --------------------------------
SELECT lotTemp.LotID, cmpdTemp.Xint_CompoundID
FROM (
SELECT Max(hcl.lot_id) + 1 AS LotID
FROM hts_sample hs,
hts_compound_lot hcl
WHERE hs.sample_id = hcl.sample_id
AND hs.alternate_id = Upper('INCB000046')) lotTemp,
(SELECT CAST(oRS3_Compound.oRS3_sel_CompoundID(Upper('INCB000046')) AS NUMBER) AS Xint_CompoundID FROM dual) cmpdTemp
But this gives the error above at "select cast..." ------
DECLARE
x NUMBER;
y NUMBER;
BEGIN
SELECT lotTemp.LotID, cmpdTemp.CompoundID
INTO x, y
FROM
(SELECT Max(hcl.lot_id) + 1 AS LotID
FROM hts_sample hs,
hts_compound_lot hcl
WHERE hs.sample_id = hcl.sample_id
AND hs.alternate_id = Upper('INCB000046')) lotTemp,
(SELECT CAST(oRS3_Compound.oRS3_sel_CompoundID(Upper('INCB000046')) AS NUMBER) AS CompoundID FROM dual) cmpdTemp;
Dbms_Output.put_line('x=' || x || ', y=' || y);
END;
September 14, 2004 - 1:10 pm UTC
remove the cast, it isn't needed at all in this case.
suggest you look at using a scalar subquery if you can with these functions
select max(....) lotid, (select f(x) from dual) compoundid
from hts_sample hs
where....
San, November 12, 2004 - 10:24 am UTC
Tom I have few doubts about nested table
1. Can I create a index on nested table column
2. Can I create a primary key on nested table column
3. Is it possible to create foreign key on nested table column
Please illustrate with examples
Thanks in advance
November 12, 2004 - 1:28 pm UTC
1) yes.
2) yes.
3) no
ops$tkyte@ORA9IR2> create or replace type myArrayType as table of number
2 /
Type created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create table t ( x int primary key, y myArrayType )
2 nested table y store as y_tab;
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create index y_tab_idx on y_tab(column_value);
Index created.
ops$tkyte@ORA9IR2> alter table y_tab add constraint
2 y_tab_pk primary key(nested_table_id,column_value);
Table altered.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter table y_tab add constraint
2 y_tab_fk foreign key(column_value) references t;
alter table y_tab add constraint
*
ERROR at line 1:
ORA-30730: referential constraint not allowed on nested table column
Should we delete PL/SQL table elements?
Tony, November 30, 2004 - 12:14 am UTC
I'm extensively using PL/SQL tables in my procedures for bulk operations. The PL/SQL tables might have millions of records. Do I have to delete the elements of PL/SQL table at the end of the program (<array>.DELETE)? What will be the impact if I don’t delete the elements?
Thanks in advance.
November 30, 2004 - 7:31 am UTC
*millions*
*millions*???
you might consider doing hundreds/thousands at a time and use LIMIT on your bulk collects. Unless you have infinite ram.
When the plsql table goes out of scope, its memory will be returned to the SESSION heap. you need to .delete it only if you are using global variables in a package -- but they I'd be saying
*globals*
*globals*???
you might consider using no globals....
Deleting an element from a collection
Neil, October 14, 2005 - 8:13 am UTC
Tom -
How do I delete an element in a collection? As you can see, I can pick out a row from the collection, but I can't delete it. I'm probably doing something silly, but I can't seem to work this out!
Oracle8.1.7> get ex
1 drop type my_table_type
2 /
3 drop type my_row_type
4 /
5 create or replace type
6 my_row_type as object (obj_name varchar(30)
7 ,obj_type varchar2(18))
8 /
9 create or replace type
10 my_table_type as table of my_row_type
11 /
12 declare
13 cursor stuff
14 is
15 select my_row_type(o.object_name,o.object_type)
16 from all_objects o;
17 local_table my_table_type := my_table_type();
18 begin
19 open stuff;
20 fetch stuff
21 bulk collect
22 into local_table;
23 for x in (select c_view.obj_name oname
24 ,c_view.obj_type otype
25 from all_objects o,
26 (select cltn.obj_name
27 ,cltn.obj_type
28 from table(cast(local_table as my_table_type)) cltn) c_view
29 where c_view.obj_name = o.object_name
30 and c_view.obj_type = o.object_type
31 and o.object_name = 'MY_ROW_TYPE'
32 and o.object_type = 'TYPE')
33 loop
34 dbms_output.put_line('element to delete '||x.oname||' '||x.otype);
35 -- local_table.delete(x);
36 end loop;
37* end;
38 .
Oracle8.1.7> @ex
Type dropped.
Type dropped.
Type created.
Type created.
element to delete MY_ROW_TYPE TYPE
PL/SQL procedure successfully completed.
Oracle8.1.7> ed ex
Oracle8.1.7> get ex
1 drop type my_table_type
2 /
3 drop type my_row_type
4 /
5 create or replace type
6 my_row_type as object (obj_name varchar(30)
7 ,obj_type varchar2(18))
8 /
9 create or replace type
10 my_table_type as table of my_row_type
11 /
12 declare
13 cursor stuff
14 is
15 select my_row_type(o.object_name,o.object_type)
16 from all_objects o;
17 local_table my_table_type := my_table_type();
18 begin
19 open stuff;
20 fetch stuff
21 bulk collect
22 into local_table;
23 for x in (select c_view.obj_name oname
24 ,c_view.obj_type otype
25 from all_objects o,
26 (select cltn.obj_name
27 ,cltn.obj_type
28 from table(cast(local_table as my_table_type)) cltn) c_view
29 where c_view.obj_name = o.object_name
30 and c_view.obj_type = o.object_type
31 and o.object_name = 'MY_ROW_TYPE'
32 and o.object_type = 'TYPE')
33 loop
34 dbms_output.put_line('element to delete '||x.oname||' '||x.otype);
35 local_table.delete(x);
36 end loop;
37* end;
38 .
Oracle8.1.7> @ex
Type dropped.
Type dropped.
Type created.
Type created.
local_table.delete(x);
*
ERROR at line 28:
ORA-06550: line 28, column 7:
PLS-00306: wrong number or types of arguments in call to 'DELETE'
ORA-06550: line 28, column 7:
PL/SQL: Statement ignored
Oracle8.1.7> spo off
And another thing! How do I pass a collection to a remote procedure??? I keep getting this error:
ERROR at line 201:
ORA-06550: line 201, column 5:
PLS-00306: wrong number or types of arguments in call to 'P_INSERT'
ORA-06550: line 201, column 5:
PL/SQL: Statement ignored
I have created the necessary types on both local and remote databases.
I have declared a variable of that type in both the local and the remote procedures
I have created the remote procedure with a formal parameter of the type created on both databases.
I have created a synonym to hide the "remoteness" of the procedure
Why is there a type mismatch?
Am I suffering from eightonesevenitis?
October 14, 2005 - 9:03 am UTC
you delete with a subscript (number, index)
X here is a "record", not a number.
Of course it is!
Neil, October 14, 2005 - 9:49 am UTC
I don't know whether to laugh or cry! Sometimes, the wood and the trees are hard to discern! Any thoughts on the remote procedure call?
October 14, 2005 - 10:22 am UTC
object types are not very "database link friendly"
you can use a plsql record and table of records over the DBLINK. you'd move them into the collection type on the other side.
the local system would
create synonym local_pkg for pkg@remote;
and then in the code it can:
is
l_data local_pkg.thetype;
begin
....
local_pkg.p( l_data );
and the pkg@remote would have:
create package pkg
as
type x is record .....;
type thetype is table of record index by binary_integer;
.....
procedure your_proceduer( p_inputs thetype );
What about performance
Jean Boule, April 10, 2006 - 5:18 pm UTC
Hi,
I've loaded 352 rows in my variable l_tab_ref_parent who is of type asge_extr_tab_ref
In the course of my program I do this select often but I don't understand the cpu time consuming
Is there an index missing on the column in the type
I'v included my type definition and the TKPROF result
select parent.l_constraint,
parent.l_proprietaire,
parent.l_constraint_ref,
parent.l_proprietaire_ref,
parent.l_nom_table_ref
from THE ( select cast( l_tab_ref_parent as asge_extr_tab_ref ) from dual ) parent
where parent.l_proprietaire = upper(l_prop)
and parent.l_nom_table = upper(l_nom);
this is my setup
create or replace type asge_extr_rec_ref as object
( l_proprietaire varchar2(30),
l_nom_table varchar2(30),
l_constraint varchar2(30),
l_type_constraint varchar2(1),
l_proprietaire_ref varchar2(30),
l_nom_table_ref varchar2(30),
l_constraint_ref varchar2(30),
constructor function asge_extr_rec_ref return self as result
)
/
create or replace type body asge_extr_rec_ref
as
constructor function asge_extr_rec_ref return self as result
is
begin
return;
end;
end;
/
create or replace type asge_extr_tab_ref as table of asge_extr_rec_ref
/
Result in TkProf
SELECT PARENT.L_CONSTRAINT, PARENT.L_PROPRIETAIRE, PARENT.L_CONSTRAINT_REF,
PARENT.L_PROPRIETAIRE_REF, PARENT.L_NOM_TABLE_REF
FROM
THE ( SELECT CAST( :B1 AS ASGE_EXTR_TAB_REF ) FROM DUAL ) PARENT WHERE
PARENT.L_PROPRIETAIRE = UPPER(:B3 ) AND PARENT.L_NOM_TABLE = UPPER(:B2 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 323 2.05 2.06 17 2235 0 0
Fetch 1071 0.48 0.44 0 0 0 748
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1395 2.53 2.50 17 2235 0 748
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer goal: RULE
Parsing user id: 235 (JBOULE) (recursive depth: 1)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: RULE
0 COLLECTION ITERATOR (PICKLER FETCH)
0 TABLE ACCESS (FULL) OF 'DUAL'
April 11, 2006 - 12:14 pm UTC
Not bad eh - 0.007 cpu seconds per execution. I would have thought it would have been higher too ;)
Or, is that not what you meant?
example of delete
babloo, August 29, 2006 - 5:51 pm UTC
Hi tom ,
I wanmt to delete elements from pl/sql table using sql keyword delete so that I do not have to go through loop .eg
suppose I have a pl/sql table with employess who are acive as well as inactive . I WANT TO DELETE ALL THE EMPLOYEES who are inactive . I could not find any example anywhere. I know I can select from the collection and exculde records and put this into another pl/sql table . Is this the only way ?
August 30, 2006 - 7:53 am UTC
if you use a collection, instead of a plsql table type, you can "select out" the things to keep.
ops$tkyte%ORA10GR2> create or replace type myType as object
2 ( ename varchar2(10),
3 empno number,
4 job varchar2(9)
5 )
6 /
Type created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace type myArray as table of myType
2 /
Type created.
ops$tkyte%ORA10GR2> variable x refcursor
ops$tkyte%ORA10GR2> declare
2 l_data myArray;
3 l_newdata myArray;
4 begin
5 select myType(ename,empno,job) bulk collect into l_data
6 from scott.emp;
7
8 select myType(ename,empno,job) bulk collect into l_newdata
9 from TABLE( cast(l_data as myArray) ) x
10 where x.job <> 'CLERK';
11
12 dbms_output.put_line( l_data.count || ', ' || l_newdata.count );
13 end;
14 /
14, 10
PL/SQL procedure successfully completed.
but that is about it.
why cast
babloo, August 30, 2006 - 11:39 am UTC
Thanks,
On a related note , I always wondered
l_datais already defined as myArray
why we have to do cast(l_data as myArray)
August 30, 2006 - 5:41 pm UTC
because the l-data thing is a 'bind variable' - it'll be turned into :bind, the datatype will temporarily be hidden from the parser (not in all releases, they've corrected it in current ones, I just keep doing it anyway)
combining two single recordsets into one recordsrt
Shahram Samardar, April 23, 2007 - 7:00 pm UTC
Please assume
create or replace type myNumberTableType as table of number;
create or replace type myVarcharTableType as table of Varchar2;
declare
t1 myNumberTableType :=
myNumberTableType( 1, 2 );
t2 myVarcharTableType :=
myNumberTableType( 'a' , 'b' );
If I have above two variables, What SQL select returns ( ( 1,'a' ) , ( 2, 'b' ) ) ?
April 24, 2007 - 10:23 am UTC
this sort of relies on the (not documented) observation that collection variables return their data by accident in the order of the collection. parallel query, other features may some day render this "not true". It would be "safer" to have a pipelined function to do the right thing and return "R" procedurally
ops$tkyte%ORA10GR2> create or replace type myNumberTableType as table of number;
2 /
Type created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace type myVarcharTableType as table of Varchar2(10);
2 /
Type created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> variable x refcursor
ops$tkyte%ORA10GR2> declare
2 t1 myNumberTableType :=
3 myNumberTableType( 1, 2 );
4
5 t2 myVarcharTableType :=
6 myVarcharTableType( 'a' , 'b' );
7 begin
8 open :x for
9 select a, b
10 from (select column_value a, rownum r from TABLE(t1)) t1,
11 (select column_value b, rownum r from TABLE(t2)) t2
12 where t1.r = t2.r;
13 end;
14 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> print x
A B
---------- ----------
1 a
2 b
Shahram Samardar, April 24, 2007 - 9:10 pm UTC
Thank you for your response.
I thought about something like this and that ordering was an issue that I thought it could be a way to return always in the order. Your answer confirms there is not a way except pipelining.
Also , Is Column_value a reserved word to return a single column of record type?
Also a quick question, what is difference between using THE and TABLE ? somehow THE must be related to nested/unnesting or not sure.
April 25, 2007 - 9:52 am UTC
column_value is the default name of a table of builtin-type, yes.
THE is deprecated, from version 8.0 of Oracle. Use TABLE()
Joe Bloggs, May 28, 2008 - 7:37 am UTC
What on earth was in the mind of the person at Oracle who decided to call a table function 'THE' ???
Do you know the history behind this, as it seems a pretty name?
May 28, 2008 - 9:46 am UTC
that is archaic syntax that was deprecated. The standards bodies make this stuff up usually, not Oracle.
Joe Bloggs, May 28, 2008 - 7:50 am UTC
...pretty bizarre name
Add FORALL
Paul, August 29, 2008 - 12:25 pm UTC
I'd like to add a small twist to the nested table join. Is it possible to add a FORALL around the last example here? What if you had a table that contains both columns and you want to use a FORALL to insert all of the data from tables t1 and t2?
create table both (cola number, colb varchar2(10));
Can we get the data from t1 and t2 into table both without having to use a cursor?
August 30, 2008 - 9:49 am UTC
big page here, please post a "self contained" question. I'm not sure what you mean.
... Can we get the data from t1 and t2 into table both without having to use a
cursor?
...
into table both?
Add FORALL clarification
Paul, September 04, 2008 - 11:21 am UTC
My question was related to the title "combining two single recordsets into one recordset". You're right. It is a very big page.
Your example:
create or replace type myNumberTableType as table of number;
create or replace type myVarcharTableType as table of Varchar2(10);
variable x refcursor
declare
t1 myNumberTableType := myNumberTableType( 1, 2 );
t2 myVarcharTableType := myVarcharTableType( 'a' , 'b' );
begin
open :x for
select a, b
from (select column_value a, rownum r from TABLE(t1)) t1,
(select column_value b, rownum r from TABLE(t2)) t2
where t1.r = t2.r;
end;
/
I'd like to add a table:
create table both (cola number, colb varchar2(10));
I'd like to know if there is anyway to add a FORALL around your "select a,b..." that will insert the results into my table called both. I was trying to avoid a cursor with an explicit:
insert into table both (cola, colb) values (a,b);
Something like:
declare
t1 myNumberTableType := myNumberTableType( 1, 2 );
t2 myVarcharTableType := myVarcharTableType( 'a' , 'b' );
begin
FORALL i IN 1..t1.COUNT
INSERT INTO both VALUES
select a, b
from (select column_value a, rownum r from TABLE(t1)) t1,
(select column_value b, rownum r from TABLE(t2)) t2
where t1.r = t2.r;
end;
/
I know this doesn't work and it appears that you can't specify the column names in the values clause.
September 04, 2008 - 1:41 pm UTC
I don't understand why you think you need a forall there at all?
ops$tkyte%ORA10GR2> create table both ( cola number, colb varchar2(10) );
Table created.
ops$tkyte%ORA10GR2> declare
2 t1 myNumberTableType := myNumberTableType( 1, 2 );
3 t2 myVarcharTableType := myVarcharTableType( 'a' , 'b' );
4 begin
5 insert into both
6 select a, b
7 from (select column_value a, rownum r from TABLE(t1)) t1,
8 (select column_value b, rownum r from TABLE(t2)) t2
9 where t1.r = t2.r;
10 end;
11 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> select * from both;
COLA COLB
---------- ----------
1 a
2 b
New question
Koteswararao, April 01, 2010 - 5:53 pm UTC
Hi sir, this site is very useful to me to know the new things regarding Oracle. i have a small question, i need the SQL code for the finding the CPU usage cycles. (i.e. how much time it will take to execute the query)
April 05, 2010 - 1:09 pm UTC
explain plan give you our best guess at how long a query MIGHT take.
v$sql reports a cumulative time regarding how long it ACTUALLY took.
good for any level developpers
A reader, June 11, 2010 - 7:34 am UTC
Good
It was helpful when one stuck up some where in the mid .