Re: Determining a PK for a view

comments inline.

"Daniel A. Morgan" <dmorgan_at_exesolutions.com> wrote in message news:3B1C6798.E723782F_at_exesolutions.com...
> Richard Wheeldon wrote:
>
> > Hi,
> >
> > I have a problem which I'm trying to find a solution for.
> >
> > 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.

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.

> > 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. 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.

> > So the question is, how can I find out which these columns
> > are from the data dictionary, without having to parse
> > the view definition?
> >

all columns in all tables and all views are in the data dictionary.

> > Richard
>
> What just a minute. You can't index anything by rowid. Rowid is not
> indexable. So when you say "usually" I am thinking you've never done
> anything in Oracle before.
>
> And the rest of your query makes little sense as it stands.
>
> Why don't you state the end result you are trying to achieve and let
> those that know Oracle help you by suggesting a solution. I, for one,
> haven't a clue what you are attempting from the above.
>
> Daniel A. Morgan
>
Received on Tue Jun 05 2001 - 01:21:27 CDT