Home » Server Options » Replication » need help
need help [message #160226] Fri, 24 February 2006 00:04 Go to next message
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
Re: need help [message #161638 is a reply to message #160226] Mon, 06 March 2006 07:09 Go to previous message
girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)
Senior Member
Yes Mviews can be created on tables with no primary keys. In these cases we use Rowids instead.

--Girish
Previous Topic: replication package-- BIP_Delta
Next Topic: Replication database
Goto Forum:
  


Current Time: Thu Mar 28 06:02:31 CDT 2024