August 2009 Archives

Recently one of my colleague Jeff Norris had a weird error. He was trying to build a materialized view over some tables in his local database and some tables in his remote database using database links the sql to create the view ran fine and provided the results as expected, but when put inside a materialized view statement complained with ORA-00942 errors.

Lets say the two databases in question are local and remote, so the sql to create the materialized view to load immediately and refresh everyday is

CREATE MATERIALIZED VIEW MV_CUSTOMERBALANCE 
BUILD IMMEDIATE
REFRESH FORCE START WITH ROUND(SYSDATE) + 23/24
NEXT SYSDATE + 1
AS
SELECT customer.name , account.balance, accounttype.name 
FROM customer , account@remotedb account, accounttype@remotedb accounttype
WHERE
customer.id = account.customerid
AND account.accounttyppeid = accounttype.id
/

Oracle started to complain when creating the above materialized view issuing an error ORA-00942: table or view does not exist, but the SQL without the create materialized view command ran fine giving the expected results.

SELECT customer.name , account.balance, accounttype.name 
FROM customer , account@remotedb account, accounttype@remotedb accounttype
WHERE
customer.id = account.customerid
AND account.accounttyppeid = accounttype.id
/

After some searching around and experimenting I found, in the create materialized view statement the database link name can be used only once, which meant we can only use the "remotedb" name once, we got around this restriction by creating two database links to the remote database as REMOTEACCOUNT and REMOTEACCOUNTTYPE and using them in the creation of the materialized view as shown below.

CREATE MATERIALIZED VIEW MV_CUSTOMERBALANCE 
BUILD IMMEDIATE
REFRESH FORCE START WITH ROUND(SYSDATE) + 23/24
NEXT SYSDATE + 1
AS
SELECT customer.name , account.balance, accounttype.name 
FROM customer , account@remoteaccount account, accounttype@remoteaccounttype accounttype
WHERE
customer.id = account.customerid
AND account.accounttyppeid = accounttype.id
/

Okay this is kind of a rant, maybe I'm too picky or just that I hate to see perfectly good data not being used. This is how it goes..

I go regularly to this store to get Horizon organic milk for my family, about 60% of the time I see milk I need NOT in stock, okay I can live with that, may be lots of folks are buying organic milk, but not when it happens frequently, especially when the store knows how much milk was ordered (or supplied from the warehouse) and how much milk was sold, the store should be able to figure out that organic milk gets sold out pretty fast, putting my Business Intelligence (BI) hat on, I think the store should be able to predict when they are going to run out of organic milk ( for that matter any product), its especially frustrating when they have all the data they need to get it done.

One more non usage of data that really makes me red is, when the organic milk in the store is already expired (past the sell by date). I mean how hard is it for someone to generate a list of all the products that expire today and ask the store associates to remove them from the shelves by the end of the day, especially when its edible items.