Specifying conditions on VARRAY's
Miroslav, October 29, 2001 - 12:45 pm UTC
Thanks a lot. You did answer my question.
You can still write a condition on the element of the VARRAY:
select name, prev.city, prev.state, prev.zip_code
from people p, table( p.previous_addresses ) prev
where prev.state = 'PA';
(excuse the copyrights ;-)
So, we could say that the optimization of a query involving a nested table (or a simple child table) for PREVIOUS_ADDRESSES would benefit from statistics on the columns, whereas VARRAY will not give us such capability, right?
Thanks a lot for this great service!
- Miroslav
October 29, 2001 - 12:49 pm UTC
But it's not really a table and its not indexed... Its more of a "trick".
If you plan on flattening this object all of the time -- you might consider good old fashioned relational tables with true parent/child relationships. You'll have more opportunities to tune such a setup. In the above example, you have no choice but to full scan the people table, synthesize the rows in the varray, and apply the where clause.
In a true parent/child, an index on prev.state could be used if applicable.
Specifying conditions on VARRAY's
Miroslav, October 29, 2001 - 1:00 pm UTC
Thanks a lot. You did answer my question.
You can still write a condition on the element of the VARRAY:
select name, prev.city, prev.state, prev.zip_code
from people p, table( p.previous_addresses ) prev
where prev.state = 'PA';
(excuse the copyrights ;-)
So, we could say that the optimization of a query involving a nested table (or a simple child table) for PREVIOUS_ADDRESSES would benefit from statistics on the columns, whereas VARRAY will not give us such capability, right?
Thanks a lot for this great service!
- Miroslav
Thank you.
Miroslav, October 29, 2001 - 1:08 pm UTC
Thanks again for such prompt responses.
Sorry for the duplicating the follow up.
BTW, for those who want more information about using VARRAYS (and Oracle pragmatics in general), please see:
Expert One on One Oracle, by Thomas Kyte
</code>
http://www.amazon.com/exec/obidos/ASIN/1861004826 <code>
The "trick" above is copied from Tom's book.
-Miroslav
October 29, 2001 - 1:17 pm UTC
ahh, now i understand the "copyright" comment ;)
Yes, it is from the book and I went back and found right after that example I have the text:
...
One big difference here is that in the nested table implementation, we could have created an index on the nested table's STATE column, and the optimizer would have been able to use that. Here, the STATE column cannot be indexed.
......
(so at least I'm consistent!)
Create Varray of CLOB
A reader, July 22, 2003 - 7:49 pm UTC
Hi Tom,
In 9i Documentation:
http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96594/adobjbas.htm#467559
A varray cannot contain LOBs. This means that a varray also cannot contain elements of a user-defined type that has a LOB attribute
But in 9.2.0.3:
SQL> create type varray_of_clob as varray(10) of clob;
2 /
Type created.
SQL> create type type_of_clob as object (attr clob);
2 /
Type created.
SQL> create type varrary_of_type_of_clob as varray(10) of type_of_clob;
2 /
Type created.
Both succeeded. Is it just a document error or my mistake?
July 22, 2003 - 8:13 pm UTC
must be a doc bug
On the subject of varrays
Rob, May 27, 2005 - 6:00 pm UTC
Tom:
I want to do an insert select to populate a table that contains a user defined column with a varray by doing a select * from an identical table in a different schema. Unfortunately I get an ORA-00932 error. I know that I can get around this using import with toid_novalidate. Any way to work around this in sql.
CREATE OR REPLACE TYPE USER1.VARRAY_7_8_4 AS VARRAY(7) OF NUMBER (8,4);
;
CREATE OR REPLACE TYPE USER2.VARRAY_7_8_4 AS VARRAY(7) OF NUMBER (8,4);
;
CREATE TABLE USER1.AUDIT_BASE_TURN
(
AUDIT_ID NUMBER(7) NOT NULL,
ITEM_ID NUMBER(8) NOT NULL,
LOCATION_ID NUMBER(6) NOT NULL,
NEW_BASE_TURN_ARRAY USER1.VARRAY_7_8_4 NULL,
AUDIT_STATE_ID NUMBER(1) NOT NULL,
MODIFIED_USER_ID NUMBER(7) NULL,
MODIFIED_DATE DATE NULL
)
;
CREATE TABLE USER2.AUDIT_BASE_TURN
(
AUDIT_ID NUMBER(7) NOT NULL,
ITEM_ID NUMBER(8) NOT NULL,
LOCATION_ID NUMBER(6) NOT NULL,
NEW_BASE_TURN_ARRAY USER2.VARRAY_7_8_4 NULL,
AUDIT_STATE_ID NUMBER(1) NOT NULL,
MODIFIED_USER_ID NUMBER(7) NULL,
MODIFIED_DATE DATE NULL
)
;
INSERT INTO USER1.AUDIT_BASE_TURN
SELECT * FROM USER2.AUDIT_BASE_TURN;
ORA-00932: inconsistent datatypes: expected USER1.VARRAY_7_8_4 got USER2.VARRAY_7_8_4
Thanks as always,
Rob
May 27, 2005 - 6:20 pm UTC
well, they are not "identical", they use different types, completely.
Can you use consistent types? else we have to either
a) flatten and unflatten the data (in which case, we need to know the primary key)
b) write a function that takes a user1.var.... and returns a user2.var.... type
How to flatten and unflatten?
Rob, June 01, 2005 - 3:08 pm UTC
After the data is moved from user1 to user2, user1 will be dropped so I don't think I can use a consistent type. The option of "flattening and unflattening" sounds like something I would like to try.
The pk of both tables is
AUDIT_ID,ITEM_ID,LOCATION_ID. Can you give me a quick example of how to flatten and unflatten the data.
June 01, 2005 - 5:28 pm UTC
strongly recommend you consider having a schema of TYPES and just use a single type for all in the future.
ops$tkyte@ORA10GR1> create table t1 (x int, y va1 );
Table created.
ops$tkyte@ORA10GR1> create table t2 (x int, y va2 );
Table created.
ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> insert into t1 values ( 1, va1(1,2,3) );
1 row created.
ops$tkyte@ORA10GR1> insert into t1 values ( 2, va1(4,5,6) );
1 row created.
<b>this is how to flatten technically, we'll use the same concept but with a scalar subquery in a moment</b>
ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> select x, column_value
2 from t1, table(t1.y)
3 /
X COLUMN_VALUE
---------- ------------
1 1
1 2
1 3
2 4
2 5
2 6
6 rows selected.
ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> insert into t2
2 select x, cast( multiset(select column_value from TABLE(t1.y)) as va2 )
3 from t1
4 /
2 rows created.
ops$tkyte@ORA10GR1> select * from t2;
X Y
---------- ---------------
1 VA2(1, 2, 3)
2 VA2(4, 5, 6)
nvl on varray
Rob, August 30, 2005 - 9:31 pm UTC
Tom:
This was very helpful but now I have a new requirement to convert any null value within the varray to 0 while doing the insert select. Is there a way to do this within the insert ... select.
Thanks as always.
Rob
August 31, 2005 - 1:07 pm UTC
seems like you are trying to patch an application bug??
not without un-nesting and re-nesting it, gets ugly.
I figured it out
Rob, August 31, 2005 - 11:50 am UTC
nvl(column_value,0) did it.
How to determine average row length for tables with VARRAYS
Vladimir, August 30, 2016 - 8:59 pm UTC
Hi Tom,
I noticed that Oracle stats do not include VARRAY column length into average row length. I need to estimate how much space is going to be used. How do I find out space used by VARRAYs?
I created a table below with "short" VARRAY less than 4000 bytes that is supposed to be stored inline in RAW column, and "long" array greater than 4000 bytes, which is going to be stored outside of table segment in a LOB segment.
Stats claim that an average row length is 0..
</>
CREATE OR REPLACE TYPE test_avg_length_long_va IS VARRAY(10) OF CHAR(41);
/
CREATE OR REPLACE TYPE test_avg_length_short_va IS VARRAY(10) OF CHAR(10);
/
--drop table t;
create table t (shortva test_avg_length_short_va, longva test_avg_length_long_va);
insert into t(shortva, longva) values(test_avg_length_short_va(), test_avg_length_long_va());
begin
DBMS_STATS.GATHER_TABLE_STATS (
ownname => user,
tabname => '"T"',
estimate_percent => 100
);
end;
/
select sample_size, num_rows, blocks, avg_row_len from user_Tab_statistics where table_name='T';
insert into t(shortva, longva) SELECT test_avg_length_short_va('1', '1','1', '1', '1','1', '1', '1','1', '1'), test_avg_length_long_va('1', '1', '1','1', '1', '1','1', '1', '1','1') from all_objects where rownum < 100;
begin
DBMS_STATS.GATHER_TABLE_STATS (
ownname => user,
tabname => '"T"',
estimate_percent => 100
);
end;
/
select sample_size, num_rows, blocks, avg_row_len from user_Tab_statistics where table_name='T';
</>
August 31, 2016 - 1:47 am UTC
I'll propose something different, because I think using AVG_ROW_LEN is not a good way to estimate the size of a table (even in the simplest cases), because there are so many variables in play nowadays:
- basic/advanced compression
- encryption
- pctfree/pctused
- assm block allocations
etc etc
For me, the way to estimate the space is the load some representative data and extrapolate from there.
SQL> CREATE OR REPLACE TYPE test_avg_length_long_va IS VARRAY(10) OF CHAR(60);
2 /
Type created.
SQL>
SQL> CREATE OR REPLACE TYPE test_avg_length_short_va IS VARRAY(10) OF CHAR(10);
2 /
Type created.
SQL>
SQL>
SQL> create table t (shortva test_avg_length_short_va, longva test_avg_length_long_va) tablespace demo;
Table created.
SQL>
SQL> insert into t(shortva, longva)
2 SELECT
3 test_avg_length_short_va('1', '1','1', '1', '1','1', '1', '1','1', '1'),
4 test_avg_length_long_va('1', '1', '1','1', '1', '1','1', '1', '1','1')
5 from all_objects where rownum < 10000;
9999 rows created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> select segment_name, bytes
2 from user_segments
3 where tablespace_name = 'DEMO';
SEGMENT_NAME BYTES
------------------------------ ----------
T 9437184
1 row selected.
So in this example, I would estimate eveyr 10,000 rows will consume 10M