| need help [message #160226] |
Fri, 24 February 2006 00:04  |
venkatbollu
Messages: 53
Registered: April 2005
Location: Bangalore
|
Member |
|
|
hi guys,
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)
AS
(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
BUILD IMMEDIATE
REFRESH COMPLETE
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
(ADDRESS)
CREATE INDEX ZIPCODE#SEARCH#KEY2 ON ADDRESS_MVIEW
(ZIP_CODE_V)
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,
venkat
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
