Home » SQL & PL/SQL » SQL & PL/SQL » Problem with VARRAYs (Oracle 11gR1 RAC on Oracle EL5)
Problem with VARRAYs [message #312324] |
Tue, 08 April 2008 10:12  |
Mopper
Messages: 11 Registered: April 2008 Location: Belgium
|
Junior Member |
|
|
Hi everyone.
We have quite a problem on our hands here, on of our applications uses an Oracle 11gR1 RAC as a db, and there is some logic that needs to be performed in the DB itself.
To be more precise: there is a table that contains records which have 2 VARRAYs. They are VARRAYs because the order of the elements matters. Now, the problem is that the elements of these VARRAYs need to be multiplied one by one, but Oracle multiplies every element from one array with every element of the other array.
To visualize it:
say VARRAY1 = (a, b, c)
and VARRAY2 = (d, e, f)
then i want results as following: a*d, b*e, c*f.
But what oracle gives us is: a*d,a*e,a*f,b*d,b*e,b*f,c*d,c*e,c*f.
This is the query that i used:
select vals.COLUMN_VALUE * cols.COLUMN_VALUE from beta_new B, TABLE(B.BETA_VAL) VALS, TABLE(B.BETA_NAMES) COLS;
BETA_VAL and BETA_NAMES are the 2 VARRAYs.
Is there any way to make oracle understand that it shouldn't multiply each element with each element from the other VARRAY?
I tried using vals(ROWNUM).COLUMN_VALUE, but apparently it's not possible to use the index in an sql query.
Thanks in advance.
|
|
|
Re: Problem with VARRAYs [message #312380 is a reply to message #312324] |
Tue, 08 April 2008 13:17   |
 |
Barbara Boehmer
Messages: 9104 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
You can only use subscripts in pl/sql, but you can use them in a pl/sql function and call that function from sql. I have provided a pure sql example below and another example that calls a pl/sql function. You may not need all of the complexities that I provided. For example, if you know that you will always have matching numbers of elements, then you can eliminate the full outer join and if you know the maximum number of elements then you can just use that number instead of calculating it.
-- test environment:
SCOTT@orcl_11g> CREATE OR REPLACE TYPE num_varray AS VARRAY (3) OF NUMBER;
2 /
Type created.
SCOTT@orcl_11g> CREATE TABLE beta_new
2 (beta_val num_varray,
3 beta_names num_varray)
4 /
Table created.
SCOTT@orcl_11g> INSERT INTO beta_new VALUES (num_varray (1, 2, 3), num_varray (4, 5))
2 /
1 row created.
SCOTT@orcl_11g> INSERT INTO beta_new VALUES (num_varray (6, 7), num_varray (8, 9, 10))
2 /
1 row created.
-- pure sql:
SCOTT@orcl_11g> WITH vs AS
2 (select ROW_NUMBER () OVER (PARTITION BY ROWID ORDER BY ROWNUM) rn,
3 ROWID id, vals.COLUMN_VALUE v
4 from beta_new B, TABLE(B.BETA_VAL) VALS),
5 cs AS
6 (select ROW_NUMBER () OVER (PARTITION BY ROWID ORDER BY ROWNUM) rn,
7 ROWID id, cols.COLUMN_VALUE c
8 from beta_new B, TABLE(B.BETA_NAMES) COLS)
9 select vs.v, cs.c, vs.v*cs.c v_times_c
10 FROM vs FULL OUTER JOIN cs ON (vs.id = cs.id AND vs.rn = cs.rn)
11 ORDER BY vs.id, cs.id, vs.rn, cs.rn
12 /
V C V_TIMES_C
---------- ---------- ----------
1 4 4
2 5 10
3
6 8 48
7 9 63
10
6 rows selected.
-- sql calling a pl/sql function:
SCOTT@orcl_11g> CREATE OR REPLACE FUNCTION get_element
2 (p_varray IN num_varray,
3 p_subscript IN NUMBER)
4 RETURN NUMBER
5 AS
6 BEGIN
7 RETURN p_varray (p_subscript);
8 EXCEPTION
9 WHEN SUBSCRIPT_BEYOND_COUNT THEN RETURN NULL;
10 WHEN SUBSCRIPT_OUTSIDE_LIMIT THEN RETURN NULL;
11 END get_element;
12 /
Function created.
SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> SELECT get_element (beta_val, rn) v,
2 get_element (beta_names, rn) c,
3 get_element (beta_val, rn) * get_element (beta_names, rn) v_times_c
4 FROM beta_new,
5 (SELECT ROWNUM rn
6 FROM DUAL
7 CONNECT BY LEVEL <=
8 (SELECT MAX (elements)
9 FROM (SELECT COUNT (*) elements
10 FROM beta_new, TABLE (beta_val)
11 GROUP BY ROWID
12 UNION ALL
13 SELECT COUNT (*) elements
14 FROM beta_new, TABLE (beta_names)
15 GROUP BY ROWID)))
16 ORDER BY beta_new.ROWID, rn
17 /
V C V_TIMES_C
---------- ---------- ----------
1 4 4
2 5 10
3
6 8 48
7 9 63
10
6 rows selected.
SCOTT@orcl_11g>
|
|
|
Re: Problem with VARRAYs [message #312525 is a reply to message #312380] |
Wed, 09 April 2008 02:36  |
Mopper
Messages: 11 Registered: April 2008 Location: Belgium
|
Junior Member |
|
|
Thanks a lot, that's exactly what I needed.
I am aware that the same can be achieved using functions, and we already had functions which did this. The thing is, however, that the multiplication is only a part of a bigger calculation, and when we did the whole calculation with functions, the various calls to functions seemed to slow the whole calculation down a lot.
The slowing down with the functions did make me come up with a question though. I had a piece of code that needed to be executed about 10000 times (for each row in a table with test data). At first, I put the piece of code in a separate function, which I then called 10000 times from a FOR loop. This gave me an execution time of about 48 seconds. However, when I put the code from that function directly in the FOR loop, thus without any calls to functions in the FOR loop, the execution time went down to 2.3 seconds. Is it just the calls to the function that made execution so slow, or does oracle have some way of optimizing/caching/whatever the code that is inside a FOR loop?
The functions being slow is why we wanted a single query, also because that way, we thought it would be easier to experiment with various degrees of parallelism and with the optimizer.
But like I said, you gave us exactly what we needed. Thanks.
[Updated on: Wed, 09 April 2008 03:47] Report message to a moderator
|
|
|
Goto Forum:
Current Time: Sun Feb 09 08:24:01 CST 2025
|