Home » SQL & PL/SQL » SQL & PL/SQL » create index on a table with VARRAY data type
create index on a table with VARRAY data type [message #207754] Wed, 06 December 2006 20:44 Go to next message
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 #207809 is a reply to message #207754] Thu, 07 December 2006 02:03 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Indexes on REF datatypes, nested table columns and varray columns are disallowed.
Re: create index on a table with VARRAY data type [message #207846 is a reply to message #207754] Thu, 07 December 2006 03:29 Go to previous messageGo to next message
Frank Naude
Messages: 4502
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 #207848 is a reply to message #207809] Thu, 07 December 2006 03:30 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
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?
Re: create index on a table with VARRAY data type [message #207856 is a reply to message #207848] Thu, 07 December 2006 03:51 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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
assuming that the function is called f_varray_function, the Varray column is called varray_column, and the delimiter you've chosen is '£'.

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 Go to previous message
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
assuming that the function is called f_varray_function, the Varray column is called varray_column, and the delimiter you've chosen is '£'.

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.
Previous Topic: Unable to create materialized view over db link
Next Topic: Dump table data in text files
Goto Forum:
  


Current Time: Sat Dec 03 03:34:41 CST 2016

Total time taken to generate the page: 0.10328 seconds