Re: REPOST: partition views

From: Chuck Hamilton <chuck_hamilton_at_yahoo.com>
Date: Tue, 17 Jul 2001 08:29:35 -0400
Message-ID: <lsb8lts85tpi62dtl7psgdtk387ve0dbv0@4ax.com>

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

--
Chuck Hamilton
chuck_hamilton_at_yahoo.com

"Do not be deceived, God is not mocked; 
for whatever a man sows, this he will also
reap." (Gal 6:7 NASB)

Received on Tue Jul 17 2001 - 07:29:35 CDT