Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Partitioned view problem
Hi:
Make sure you have placed the necessary check constraints on each table. The optimizer does the partition elimination by looking at these check constraints. Also, verify that there are appropriate indexes on your date_col column in each table.
Good luck,
Dave Sisk
Nighr <nighr_at_aol.com> wrote in article
<19971228234700.SAA14362_at_ladder01.news.aol.com>...
> In article <66mr6r$s5u$1_at_news.worldonline.nl>, "Wilhelm Thieme"
> <w.thieme_at_consunet.nl> writes:
>
> >Subject: Partitioned view problem
From: "Wilhelm Thieme"
> ><w.thieme_at_consunet.nl>
> Date: Wed, 10 Dec 1997 20:39:31 +0100
>
> Hi
> >everyone
>
> I'm struggeling with a partitioned view which will NOT give
> me the
> >performance as expected.
>
> The view is created as follows:
> create or replace
> >view temp as
select * from table_19971118 where
> >date_col=to_date('19971118','yyyymmdd')
> union all
> select * from
> >table_19971119 where date_col=to_date('19971118','yyyymmdd')
> union all
> select
> >* from table_19971120 where date_col=to_date('19971118','yyyymmdd')
union
> >all
select * from table_19971121 where
> >date_col=to_date('19971118','yyyymmdd')
> /
>
>
> I would assign the test date to a variable. Functions in WHERE
statements
> usually cause full table scans. Also I'd check if date_col is indexed.
>
> Just my two cents worth,
> --Bob.
>
Received on Tue Dec 30 1997 - 00:00:00 CST