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: REPOST: partition views

Re: REPOST: partition views

From: bonminh Lam <no_spam_at_bmlam.de>
Date: Thu, 19 Jul 2001 00:17:39 +0200
Message-ID: <3B560B03.10374579@bmlam.de>

>
> On Mon, 16 Jul 2001 12:59:58 -0700 (PDT), you wrote:
>
> >In article <ep46lt00v09qpaneceb0q5497ghlf5ifpl_at_4ax.com>, Chuck says...
> >>
> >>I'm working on a project that requires partitioning but must run on an
> >>8i standard edition database (i.e. no partition option). I want to use
> >>the old 7.3 style partition view approach but the optimizer is not
> >>eliminating partitions (tables) from the view. I've set
> >>partition_view_enabled to true, created the tables and view, and put
> >>constraints on what would be the paritition key in each table to limit
> >>what values can exist in that table.
 

> >can you post the exact DDL (shortened version, just enough to reproduce). lets
> >see date formats et. al.
>
> Essentially there are 10 tables that look like this. The only
> difference is the date range in the check constraint. Ther are no
> indexes.
>
> CREATE TABLE LCL_RPT_E_07252001 (
> QVC_START_TIME DATE NOT NULL,
> QVC_CURRENT_TIME DATE NOT NULL,
> QVC_CHAN VARCHAR2 (3) NOT NULL,
> QVC_VRU VARCHAR2 (3) NOT NULL,
> QVC_EVENT_CODE VARCHAR2 (51),
> CONSTRAINT CK_LCL_RPT_E_07252001
> CHECK (qvc_start_time >= to_date('07252001','mmddyyyy') and
> qvc_start_time < to_date('07-26-2001','mm-dd-yyyy')))
> TABLESPACE VRU_D
> PCTFREE 10
> PCTUSED 40
> INITRANS 1
> MAXTRANS 255
> STORAGE (
> INITIAL 262144
> NEXT 262144
> MINEXTENTS 1
> MAXEXTENTS 2147483645
> FREELISTS 1 FREELIST GROUPS 1 )
> NOCACHE;
>
> The view's DDL is ...
>
> CREATE OR REPLACE VIEW lcl_rpt_e_all
> (
> qvc_start_time,
> qvc_current_time,
> qvc_chan,
> qvc_vru,
> qvc_event_code
> )
> AS
> select
> "QVC_START_TIME","QVC_CURRENT_TIME","QVC_CHAN","QVC_VRU","QVC_EVENT_CODE"
> from LCL_RPT_E_07162001
> union all
> select
> "QVC_START_TIME","QVC_CURRENT_TIME","QVC_CHAN","QVC_VRU","QVC_EVENT_CODE"
> from LCL_RPT_E_07172001
> union all
> select
> "QVC_START_TIME","QVC_CURRENT_TIME","QVC_CHAN","QVC_VRU","QVC_EVENT_CODE"
> from LCL_RPT_E_07182001
> union all
> select
> "QVC_START_TIME","QVC_CURRENT_TIME","QVC_CHAN","QVC_VRU","QVC_EVENT_CODE"
> from LCL_RPT_E_07192001
> union all
> select
> "QVC_START_TIME","QVC_CURRENT_TIME","QVC_CHAN","QVC_VRU","QVC_EVENT_CODE"
> from LCL_RPT_E_07202001
> union all
> select
> "QVC_START_TIME","QVC_CURRENT_TIME","QVC_CHAN","QVC_VRU","QVC_EVENT_CODE"
> from LCL_RPT_E_07212001
> union all
> select
> "QVC_START_TIME","QVC_CURRENT_TIME","QVC_CHAN","QVC_VRU","QVC_EVENT_CODE"
> from LCL_RPT_E_07222001
> union all
> select
> "QVC_START_TIME","QVC_CURRENT_TIME","QVC_CHAN","QVC_VRU","QVC_EVENT_CODE"
> from LCL_RPT_E_07232001
> union all
> select
> "QVC_START_TIME","QVC_CURRENT_TIME","QVC_CHAN","QVC_VRU","QVC_EVENT_CODE"
> from LCL_RPT_E_07242001
> union all
> select
> "QVC_START_TIME","QVC_CURRENT_TIME","QVC_CHAN","QVC_VRU","QVC_EVENT_CODE"
> from LCL_RPT_E_07252001
>

Your equivalent of "range partitioning" logic is in the check constraint of the underlying tables. I am not sure if the optimizer is aware of this bit. I would try with adding the range partitioning information as where predicate into each SELECT branche of the partitioned view. My experience is that then the optimizer can select the right table if there is an appropiate WHERE filter in the query.  

In the query "SELECT * from <partitioned view>" I dont see any filter anyway.

Hth

-- 
To reply to me directly, please mail to: bmlam at online dot de
Received on Wed Jul 18 2001 - 17:17:39 CDT

Original text of this message

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