| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.tools -> Re: Determining a PK for a view
Spencer wrote:
> > > If I have a view or table, I can usually index these
> > > by rowid. However, I can't index those which are constructed
> > > using 'distinct' or 'group by'. But each of these constructs
> > > must have a primary key (of sorts - i.e. a pk by date/codd's
> > > definition, if not defined as such in the dd), being those
> > > fields after the group by or distinct.
> is it possible you are referring to ordering a result set
> using an ORDER BY clause? your usage of the term
> "index" does not make much sense in this context.
I meant a non-oracle index. See previous post.
> yes, a lot of queries return result sets that consist of
> unique rows. but this is NOT a requirement of sets
> returned by Oracle.
>
> yes, it is possible to create an Oracle TABLE with an
> enabled PRIMARY KEY constraint. but this is NOT
> required. it is very (almost too?) easy to create a table
> with no enabled primary key constraint or unique index.
true, but most tables and views can still be uniquely identified using a rowid. ones with group by and distinct can't. e.g:
SQL> select rowid from masset;
ROWID
But, with a view created using a group by:
SQL> select rowid from mm_coursesize;
select rowid from mm_coursesize
*
ERROR at line 1:
ORA-01446: cannot select ROWID from view with DISTINCT, GROUP BY, etc.
What I really need is a good alternative. these would normally be used in a query of the form 'select rowid,xxx.* from xxx'
> > > e.g. with: create view x as (select y,count(f) from z group by y),
> > > y must be a primary key.
> no. column x.y most definitely not a PRIMARY KEY constraint.
Sorry, I confused my terminology. It is a candidate key. Or is there a situation whereby it might not be? That doesn't really change my question. How do I find out what y is? i.e. what columns does it contain?
> it is possible that z is a table, and if it is, that column z.y may
> be referenced in the PRIMARY KEY constraint for table z.
But it may not :( I really need a generic solution. I can parse the view definition if I have to, but would prefer a more elegant (and simpler) solution.
Richard Received on Tue Jun 05 2001 - 04:35:01 CDT
 |
 |