CREATE MATERIALIZED VIEW DWH.LA_CUST_MONTH
TABLESPACE DWH_DATA2
NOLOGGING
PARTITION BY RANGE (MON_SID)
(
PARTITION LCM_Y_2004 VALUES LESS THAN (200500)
NOLOGGING
NOCOMPRESS
TABLESPACE DWH_DATA2,
PARTITION LCM_M_200601 VALUES LESS THAN (200602)
NOLOGGING
NOCOMPRESS
TABLESPACE DWH_DATA2,
PARTITION LCM_M_200602 VALUES LESS THAN (200603)
NOLOGGING
NOCOMPRESS
TABLESPACE DWH_DATA2,
PARTITION LCM_M_200603 VALUES LESS THAN (200604)
NOLOGGING
NOCOMPRESS
TABLESPACE DWH_DATA2,
PARTITION LCM_M_200604 VALUES LESS THAN (200605)
NOLOGGING
NOCOMPRESS
TABLESPACE DWH_DATA2,
PARTITION LCM_M_200605 VALUES LESS THAN (200606)
NOLOGGING
NOCOMPRESS
TABLESPACE DWH_DATA2,
PARTITION LCM_M_200606 VALUES LESS THAN (200607)
NOLOGGING
NOCOMPRESS
TABLESPACE DWH_DATA2,
PARTITION LCM_M_200607 VALUES LESS THAN (200608)
NOLOGGING
NOCOMPRESS
TABLESPACE DWH_DATA2,
PARTITION LCM_M_200608 VALUES LESS THAN (200609)
NOLOGGING
NOCOMPRESS
TABLESPACE DWH_DATA2,
PARTITION LCM_M_200609 VALUES LESS THAN (200610)
NOLOGGING
NOCOMPRESS
TABLESPACE DWH_DATA2,
PARTITION LCM_M_2006010 VALUES LESS THAN (200611)
NOLOGGING
NOCOMPRESS
TABLESPACE DWH_DATA2,
PARTITION LCM_M_2006011 VALUES LESS THAN (200612)
NOLOGGING
NOCOMPRESS
TABLESPACE DWH_DATA2,
PARTITION LCM_M_2006012 VALUES LESS THAN (200701)
NOLOGGING
NOCOMPRESS
TABLESPACE DWH_DATA2,
PARTITION LCM_M_200701 VALUES LESS THAN (200702)
NOLOGGING
NOCOMPRESS
TABLESPACE DWH_DATA2,
PARTITION LCM_M_200702 VALUES LESS THAN (200703)
NOLOGGING
NOCOMPRESS
TABLESPACE DWH_DATA2,
PARTITION LCM_M_200703 VALUES LESS THAN (200704)
NOLOGGING
NOCOMPRESS
TABLESPACE DWH_DATA2,
PARTITION LCM_M_200704 VALUES LESS THAN (200705)
NOLOGGING
NOCOMPRESS
TABLESPACE DWH_DATA2,
PARTITION LCM_M_200705 VALUES LESS THAN (200706)
NOLOGGING
NOCOMPRESS
TABLESPACE DWH_DATA2,
PARTITION LCM_M_200706 VALUES LESS THAN (200707)
NOLOGGING
NOCOMPRESS
TABLESPACE DWH_DATA2,
PARTITION LCM_M_200707 VALUES LESS THAN (200708)
NOLOGGING
NOCOMPRESS
TABLESPACE DWH_DATA2,
PARTITION LCM_M_200708 VALUES LESS THAN (200709)
NOLOGGING
NOCOMPRESS
TABLESPACE DWH_DATA2,
PARTITION LCM_M_200709 VALUES LESS THAN (200710)
NOLOGGING
NOCOMPRESS
TABLESPACE DWH_DATA2,
PARTITION LCM_M_2007010 VALUES LESS THAN (200711)
NOLOGGING
NOCOMPRESS
TABLESPACE DWH_DATA2,
PARTITION LCM_M_2007011 VALUES LESS THAN (200712)
NOLOGGING
NOCOMPRESS
TABLESPACE DWH_DATA2,
PARTITION LCM_2007012 VALUES LESS THAN (200801)
NOLOGGING
NOCOMPRESS
TABLESPACE DWH_DATA2,
PARTITION LCM_M_OTHER VALUES LESS THAN (MAXVALUE)
NOLOGGING
NOCOMPRESS
TABLESPACE DWH_DATA2
)
NOCACHE
NOCOMPRESS
PARALLEL ( DEGREE DEFAULT INSTANCES DEFAULT )
BUILD IMMEDIATE
USING INDEX
TABLESPACE DWH_INDX2
REFRESH FAST ON DEMAND
WITH PRIMARY KEY
ENABLE QUERY REWRITE
AS
SELECT CUS_SID, MON_SID, MML_SID, AGR_SID, STO_SID, SUM(SALES_QTY) AS
SALES_QTY, SUM(SALES_AMOUNT) AS SALES_AMOUNT, SUM(SALES_NN_AMOUNT) AS
SALES_NN_AMOUNT,
SUM(SALES_OI_AMOUNT) AS SALES_OI_AMOUNT,
SUM(SALES_THEOR_AMOUNT) AS SALES_THEOR_AMOUNT,
SUM(SALES_DISC_AMOUNT) AS SALES_DISC_AMOUNT,
SUM(NBR_INVOICES) AS NBR_INVOICES,
COUNT(SALES_QTY) AS CNT1,
COUNT(SALES_AMOUNT) AS CNT2,
COUNT(SALES_NN_AMOUNT) AS CNT3,
COUNT(SALES_OI_AMOUNT) AS CNT4,
COUNT(SALES_THEOR_AMOUNT) AS CNT5,
COUNT(SALES_DISC_AMOUNT) AS CNT6,
COUNT(NBR_INVOICES) AS CNT7,
COUNT(*) AS CNT8
FROM LA_CUST_DAY
GROUP BY CUS_SID, MON_SID, MML_SID, AGR_SID, STO_SID;
CREATE BITMAP INDEX DWH.LA_CUST_MONTH_I1 ON DWH.LA_CUST_MONTH
(CUS_SID)
NOLOGGING
PARALLEL ( DEGREE DEFAULT INSTANCES DEFAULT );
CREATE BITMAP INDEX DWH.LA_CUST_MONTH_I2 ON DWH.LA_CUST_MONTH
(MON_SID)
NOLOGGING
PARALLEL ( DEGREE DEFAULT INSTANCES DEFAULT );
CREATE BITMAP INDEX DWH.LA_CUST_MONTH_I3 ON DWH.LA_CUST_MONTH
(MML_SID)
NOLOGGING
PARALLEL ( DEGREE DEFAULT INSTANCES DEFAULT );
CREATE BITMAP INDEX DWH.LA_CUST_MONTH_I4 ON DWH.LA_CUST_MONTH
(AGR_SID)
NOLOGGING
PARALLEL ( DEGREE DEFAULT INSTANCES DEFAULT );
CREATE BITMAP INDEX DWH.LA_CUST_MONTH_I5 ON DWH.LA_CUST_MONTH
(STO_SID)
NOLOGGING
PARALLEL ( DEGREE DEFAULT INSTANCES DEFAULT );
CREATE UNIQUE INDEX DWH.LA_CUST_MONTH_PK ON DWH.LA_CUST_MONTH
(CUS_SID, MON_SID, MML_SID, AGR_SID, STO_SID)
NOLOGGING
TABLESPACE DWH_INDX2
PARALLEL ( DEGREE DEFAULT INSTANCES DEFAULT );
________________________________
De: oracle_br@yahoogrupos.com.br [mailto:[EMAIL PROTECTED] Em nome de Andre
Santos
Enviada em: quarta-feira, 16 de abril de 2008 10:48
Para: oracle_br@yahoogrupos.com.br
Assunto: Re: [oracle_br] views materializadas
Orfeu
Não sei se é possível você criar um índice para uma "view materializada"
(precisaria pesquisar sobre isso...).
Mas, de cabeça, sei que é possível você criar uma "tabela" comum (inclusive
com índices, etc.) e, ao criar uma "view materializada" definir que deve ser
usada essa tabela como repositório da consulta (após isso, a tabela passa a
ser "gerenciada" pela materialized view).
Já fiz isso criando uma tabela organizada por índice (IOT) e depois
definindo-a como visão materializada.
[ ]
André
Em 16/04/08, orfeu lima <[EMAIL PROTECTED] <mailto:orfeuml%40hotmail.com> >
escreveu:
>
> Srs, alguém teria algum exemplo de uma view, na qual eu possa criar um
> indice em cima dela e ao mesmo tempo estar inserindo dados.
> obrigado
> __________________________________________________________
> Receba GRÁTIS as mensagens do Messenger no seu celular quando você estiver
> offline. Conheça o MSN Mobile!
> http://mobile.live.com/signup/signup2.aspx?lc=pt-br
> <http://mobile.live.com/signup/signup2.aspx?lc=pt-br>
>
> [As partes desta mensagem que não continham texto foram removidas]
>
>
>
[As partes desta mensagem que não continham texto foram removidas]
[As partes desta mensagem que não continham texto foram removidas]
