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 Go to next message
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8623
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 Go to previous message
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

Previous Topic: can procedures created in sql server be converted into oracle (merged)
Next Topic: minus query
Goto Forum:
  


Current Time: Sat Dec 03 14:08:08 CST 2016

Total time taken to generate the page: 0.08149 seconds