Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Union query between two different schemas

Re: Union query between two different schemas

From: Spencer <spencerp_at_swbell.net>
Date: Tue, 30 Jan 2001 22:07:27 -0600
Message-ID: <VbMd6.342$nD1.8511@nnrp1.sbc.net>

<sybrandb_at_my-deja.com> wrote in message news:956aud$r87$1_at_nnrp1.deja.com...
> In article <YHxd6.163013$P82.19658608_at_news1.rdc1.ct.home.com>,
> "Sally Madeira" <sallym1_at_home.com> wrote:
> > I have two users set up: UserFRF ans UserEOD. They both have the
 same
> > tables but
> > hold different information based on region. I need to do the
 following:
> >
> > SELECT Office, Region Officer FROM UserFRF.tbOfficertable
> > UNION
> > SELECT Office, Region Officer FROM UserEOD.tbOfficertable
> >
> > I get an error saying UserEOD.tbOfficertable not found (not sure what
 the
> > real message was)
> >
> > I am doing this statement in User "UserFRF" and I am user version 8.0
 on my
> > test server
> > and it also does not work on 8i on the production server.
> >
> > Question: Can this be done? I need this to be a view for our website.
> >
> > Sally
> >
> >
> sure it can be done, provided userFRF has select privilege on
> userEOD.tbofficertable.
> Whether it is desirable is a different story, as this will inevitable
> results in two full table scans, and IMO full table scans should be
> avoided as much as possible, let alone full table scans on a web site.
>
> Hth,
> --
> Sybrand Bakker, Oracle DBA
>

the UNION set operator will require a sort in order to remove duplicates from the result set.

if you do not require the statement to remove duplicates, then consider using the UNION ALL set operator instead. Received on Tue Jan 30 2001 - 22:07:27 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US