Re: UNION CASTING .

 or rather I should say

'to select sub-optimal access paths' since they are not theoretically incorrect, just perform approx 100 times slower than if the optimizer knew there were no nulls.

"Maze Control - Terminal 23315 - Central 2" <trw-sinterface_at_pluto.gwy> wrote in message news:LxhG6.12172$_W2.12154_at_news.indigo.ie...
> Hi, thanks for your response
>
> You are correct - a view is a stored query.
> For access to the data in the tables,
> we must go through the UNION VIEW. (partitioning and other reasons).
> The tables may not be accessed directly.
> This may not be changed.
> ==========
> Therefore, anything that does a select on the view will believe
 incorrectly
> that underlying data may contain NULLs
> This later leads the optimizer to select incorrect access paths since it
> believes it must cater for NULLs .
>
> One solution is to create tables as select * from my UNION views
> and then enable the NOT NULL constraint.
> - however I do not have 244GB of spare storage.
>
> Thanks, however, it's interesting to see different people's response to
 the
> query.
>
> Chris.
>
>
> "Frank" <franjoe_at_frisurf.no> wrote in message
> news:6bgG6.1108$Ty6.15460_at_news1.oke.nextra.no...
> > Hi!
> >
> > I don't quite see the problem; your tables have NOT NULL and no one will
 be
> > able to add inconsistent data in it, a view is basically a stored query,
 why
> > bother with the constraints on a query?
> > Is this for documentation purpose?
> >
> > If you had the situation below, how should the view be then?
> > create table table1 (col1 integer NOT NULL)
> > create table table2 (col1 integer NULL)
> > create view view1 as select * from table1
> > union all select * from table2
> >
> > Frank!
> >
> > Maze Control - Terminal 23315 - Central 2 <trw-sinterface_at_pluto.gwy>
 wrote
> > in message news:0PbG6.12036$_W2.12062_at_news.indigo.ie...
> > > Can anyone help me with this ?
> > >
> > > Try the following SQL.
> > > --create table table1 (col1 integer not null)
> > > --create table table2 (col1 integer not null)
> > > --create view view1 as select * from table1
> > > ---union all select * from table2
> > >
> > >
> > >
> > > if you then do select column_name,nullable from user_tab_columns where
> > > table_name='VIEW1'
> > > the result is that col1 can contain nulls, which is incorrect.
> > >
> > > Try it on your own system - happens at least on Dynix and Win2000.
 V817
> > >
> > > Does anyone know how to hack the data dictionary to force this to be
> > > not-null ? (i.e. correct )
> > >
> > > Creating another view on top of this (AS SELECT COL1 from VIEW1 WHERE
 COL1
> > > is NOT NULL )
> > > does not work .
> > >
> > > This is a small case of a much larger production issue. The view
 cannot
 be
> > > replaced with a table.
> > > TIA !
> > >
> > >
> > >
> >
> >
>
>
Received on Fri Apr 27 2001 - 12:17:27 CDT