|need help [message #160226]
||Fri, 24 February 2006 00:04
Registered: April 2005
I need a little help on Materialized views.
here is my query:
I have a table "Address", I don't have any primary key on this table. and I have to create a view on this table with 2 or 3indexes. so I think I have to go for Mviews only. for this I did like this:
View on the address table
CREATE OR REPLACE FORCE VIEW K.ADDRESS_VIEW
(ACCOUNT_LINK_CODE_N, ADDRESS, ZIP_CODE_V)
(SELECT a.ACCOUNT_LINK_CODE_N, SUBSTR((trim(ADDRESS_1_V) || ', ' || trim(ADDRESS_2_V) || ', ' || trim(ADDRESS_3_V) || ', ' || trim(ADDRESS_4_V)),1,250) Address, trim(ZIP_CODE_V)
FROM cb_address a, (SELECT ACCOUNT_LINK_CODE_N FROM ACCOUNT_MASTER) b
WHERE a.ACCOUNT_LINK_CODE_N = b.ACCOUNT_LINK_CODE_N
AND ACCOUNT_TYPE_V = 'A'
AND ADDRESS_TYPE_N =3);
matView for the address search
CREATE MATERIALIZED VIEW k.ADDRESS_MVIEW
ENABLE QUERY REWRITE
AS SELECT ACCOUNT_LINK_CODE_N, ADDRESS, ZIP_CODE_V FROM CB_SEARCH_ADDRESS_VIEW
COMMENT ON TABLE ADDRESS_MVIEW IS 'snapshot table for snapshot K.CB_SEARCH_ADDRESS_MVIEW';
CREATE INDEX ADRESS#SEARCH#KEY1 ON ADDRESS_MVIEW
CREATE INDEX ZIPCODE#SEARCH#KEY2 ON ADDRESS_MVIEW
its already created and working properly but very slow I hope. n
Now is there any other way to make it faster. and when the Mview will be refreshed.
Can we create Mviews on the tables that are not having primary keys??
Thks in advance,