Re: UNION CASTING .

From: Maze Control - Terminal 23315 - Central 2 <trw-sinterface_at_pluto.gwy>
Date: Fri, 27 Apr 2001 17:59:07 +0100
Message-ID: <LxhG6.12172$_W2.12154@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 - 11:59:07 CDT