| create index on a table with VARRAY data type [message #207754] |
Wed, 06 December 2006 20:44  |
pcgame4u
Messages: 14
Registered: July 2006
|
Junior Member |
|
|
Dear all brother,
i have facing a problem in creating index on a table.
the table have the following structure:
CREATE OR REPLACE TYPE CAUSE_FOR_TERM_ARRAY AS VARRAY(8) OF CHAR (4);
CREATE TABLE abc (
dd CHAR (9),
ff CAUSE_FOR_TERM_ARRAY,
hh CHAR (1)
);
now i want to create unique index on all fields(dd,ff,hh).
CREATE UNIQUE INDEX abc_IND1 ON abc(dd,ff,hh);
It throws a ORA-02327 exception said that can't create index on NAMED ARRAY TYPE.
in my case, set the array type as unique index is a MUST.
Any brother know how to solve it?
Thanks very much for all help.
Sunny
|
|
|
|
|
|
| Re: create index on a table with VARRAY data type [message #207846 is a reply to message #207754] |
Thu, 07 December 2006 03:29  |
Frank Naude
Messages: 4581
Registered: April 1998
|
Senior Member |
|
|
You cannot. Consider using a nested table instead. Example:
SQL> CREATE OR REPLACE TYPE cause_for_term_typ AS OBJECT (cause CHAR(4));
2 /
Type created.
SQL>
SQL> CREATE OR REPLACE TYPE cause_for_term_table AS TABLE OF cause_for_term_typ;
2 /
Type created.
SQL>
SQL> CREATE TABLE abc(
2 dd CHAR(9),
3 ff cause_for_term_table,
4 hh CHAR(1))
5 NESTED TABLE ff STORE AS abc_ff_nt;
Table created.
SQL>
SQL> CREATE UNIQUE INDEX abc_ff_cuase_ind ON abc_ff_nt(cause);
Index created.
|
|
|
|
|
|
| Re: create index on a table with VARRAY data type [message #207856 is a reply to message #207848] |
Thu, 07 December 2006 03:51 |
pcgame4u
Messages: 14
Registered: July 2006
|
Junior Member |
|
|
| JRowbottom wrote on Thu, 07 December 2006 03:30 | What are you hoping that the index on the VARRAY will achieve?
Are you going to look for rows that contain exactly this Varray, or do you want to be able to find rows whose Varrays contain a given value?
|
i want to find rows whose Varrays contain a given value.(any value that exist inside the arrays)
for example,
select * abc
where dd = 'dd' and hh = 'hh' and [COLOR=red]ff = 'f1'[/COLOR]
if any records that have a value 'f1' inside the varray, then it will match and the record will query out.
Thanks all of you.
Sunny
|
|
|
|
| Re: create index on a table with VARRAY data type [message #207902 is a reply to message #207856] |
Thu, 07 December 2006 05:01 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
The best solution that leaps to mind is this:
Create a function that will accept a VARRAY and return the contents of that array delimited by some chr that you know won't exist in the data in the VARRAY.
Now you can use a query clause like AND INSTR(f_varray_function(varray_column,'£FF1£') > 0
I doubt there's much you can do in the way of indexing this, but it will at least make it searchable.
|
|
|
|
| Re: create index on a table with VARRAY data type [message #208032 is a reply to message #207902] |
Thu, 07 December 2006 21:36 |
pcgame4u
Messages: 14
Registered: July 2006
|
Junior Member |
|
|
| JRowbottom wrote on Thu, 07 December 2006 05:01 | The best solution that leaps to mind is this:
Create a function that will accept a VARRAY and return the contents of that array delimited by some chr that you know won't exist in the data in the VARRAY.
Now you can use a query clause like AND INSTR(f_varray_function(varray_column,'£FF1£') > 0
I doubt there's much you can do in the way of indexing this, but it will at least make it searchable.
|
Thanks for all brother help.
i have wrote the function above to completed this task.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
