Re: Help with SQL query (and outer joins...)

yes, do the typical optimizing ... no idea how it will perform because we don't know anything about the underlying views and the underlying tables of the views.

if worse comes to worst, maybe a sledgehammer approach of the materialized view will help you. depends on exactly how up to date your new bus. requirements need it.

stevenmgarcia_at_hotmail.com (Steve) wrote in message news:<4c049a8c.0203300914.14d0c9e6_at_posting.google.com>...
> This is exactly what I came up with later in the evening! I have four
> tables that I must do this across...so I did a union across the four
> tables.
>
> The next thing I need to know is whether the performance of this query
> is just totally crappy. I guess I could run it through tkprof or set
> autotrace on.
>
> Does this seem like a totally inefficient query?
>
> crappygolucky_at_hotmail.com (crappy) wrote in message news:<ce31c410.0203300030.663438ce_at_posting.google.com>...
> > select logday, sum(totalcount), sum(revenue), sum(something_else)
> > from (
> > select logday, totalcount, 0 revenue, 0 something_else from view1
> > union
> > select logday, 0, revenue, 0 from view2
> > union
> > select 0, 0, something_else from view3
> > )
> > group by logday
> >
> >
> > "Steven Garcia" <sgarcia_at_inktomi.com> wrote in message news:<3ca4e358$1_at_digink>...
> > > I'm having trouble with a view I'm developing. For the simplified
> > > version...I have two views that look like:
> > >
> > > View 1:
> > > Logday (date, 'ddd') totalcount
> > > --------------------- -----------
> > > 20-FEB-2002 10
> > > 21-FEB-2002 20
> > >
> > > View 2:
> > > Logday (date, 'ddd') revenue
> > > --------------------- ----------
> > > 21-FEB-2002 5.00
> > > 22-FEB-2002 8.00
> > >
> > > I want to create an uber view from both of these views that it will look
> > > like
> > > Logday (date, 'ddd') totalcount revenue
> > > --------------------- ---------- --------
> > > 20-FEB-2002 10 0
> > > 21-FEB-2002 20 5.00
> > > 22-FEB-2002 0 8.00
> > >
> > > I know this is a perfect candidate for an outer join. The real example I'm
> > > trying to do is more complicated because there are 4 views (each with a
> > > logday column) and I want to create the uber view that has a column from
> > > each of the 4 views, along with some other data that will exist across all
> > > four views (so there won't be a need to do a outer-join on those columns.)
> > >
> > > How do I construct such a query? I'm trying to solve my problem in such a
> > > way that my uber view will look like
> > > the following...
> > >
> > > Logday col1 col2 col3
> > > col4
> > > ------------------- ---------- ---------- ---------- ------------
> > > 20-FEB-2002 0 1 15 0
> > > 21-FEB-2002 20 10 0 23
> > > 22-FEB-2002 14 0 14 0
> > > 23-FEB-2002 0 4 0
> > > 34
> > >
> > > where I have 0 in the columns where the data doesn't exist from my "feeder"
> > > views.
> > >
> > > We are currently solving this problem by having a job on a daily basis do
> > > this population in a table, but our business requirements have changed such
> > > that the current implementation is not suitable. I'm a little worried about
> > > performance of this view but these reporting tables are not access often in
> > > our application (so a performance hit is something that I will accept.)
> > >
> > > Thanks for your help,
> > > Steve Garcia
Received on Mon Apr 01 2002 - 10:55:12 CST