Create materialized views depending on the sysdate with enable query rewrite option [message #501334] |
Mon, 28 March 2011 10:46  |
 |
walnutsparrow2k
Messages: 9 Registered: March 2011
|
Junior Member |
|
|
Hi,
I want to create a materialized view for the last 10 days with the enable query rewrite option.
e.g. i want to create a view with the list of employees who joined the company in the last 10 days.
create materialized view M_Employee
refresh fast on commit
enable query rewrite as
select joining_date , name
from
employee
where joining_datde < TRUNC(sysdate) - 10
I seem to get the error SQL Error: ORA-30353: expression not supported for query rewrite
30353. 00000 - "expression not supported for query rewrite"
*Cause: The select clause referenced UID, USER, ROWNUM, SYSDATE.
This error is self explanatory , but is there any work around to
have a query liek this to list the employees based on sysdate.
I have been stuck on this for days. Please help!
Thanks!
|
|
|
|
|
Re: Create materialized views depending on the sysdate with enable query rewrite option [message #501440 is a reply to message #501334] |
Tue, 29 March 2011 03:48   |
Manoj.Gupta.91
Messages: 239 Registered: March 2008 Location: Delhi
|
Senior Member |
|
|
Hi,
You cann't use SYSDATE in Materialized View definition directly. There is a work arrount using Deterministic function. I've implemented this in past.
You are creating FAST refresh Materialized View it required Materialized View Log. Have you created view log?
CREATE OR REPLACE FUNCTION MY_TMP_FUNC
(
P_JOINING_DATDE IN EMPLOYEE.JOINING_DATDE%TYPE
)
RETURN NUMBER
DETERMINISTIC
AS
BEGIN
--Put your query conditions here that uses SYSDATE
IF P_JOINING_DATDE < TRUNC(SYSDATE) - 10 THEN
RETURN( 1 ) ;
END IF ;
RETURN( NULL ) ;
EXCEPTION
WHEN OTHERS THEN
NULL ;
END MY_TMP_FUNC ;
CREATE MATERIALIZED VIEW M_EMPLOYEE
REFRESH FAST ON COMMIT
ENABLE QUERY REWRITE
AS
SELECT E.JOINING_DATE , E.NAME
FROM EMPLOYEE E
WHERE MY_TMP_FUNC( E.JOINING_DATDE ) = 1 ;
Hope I answered your question correctly.
Regards
Manoj
|
|
|
|
Re: Create materialized views depending on the sysdate with enable query rewrite option [message #501452 is a reply to message #501447] |
Tue, 29 March 2011 04:54   |
Manoj.Gupta.91
Messages: 239 Registered: March 2008 Location: Delhi
|
Senior Member |
|
|
Hi,
@ Michel.
1.) I didn't write complete exception handling code because it will become a spoon feeding. Sorry if I'm wrong but that's what I feel. Although exception will never be raised there. Why you saying that It's a bug?
2.) I don't understand your concern MY_TMP_FUNC function I've created is a DETERMINISTIC function. Why you saying that It's a bug?
I don't see these as bugs. Plese help me to understand why you calling these bugs....! I'll learn something from this.
Thanks & Regards
Manoj
|
|
|
Re: Create materialized views depending on the sysdate with enable query rewrite option [message #501456 is a reply to message #501452] |
Tue, 29 March 2011 05:28   |
 |
Michel Cadot
Messages: 68765 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
1) Yes it is wrong, NEVER ever post something like that. Anywhere you said it is an incomplete code and even if you said so NEVER post this exception handler, better, FAR better, do not post any exception block.
It is a bug because as you accept ALL errors as no error then you accept as a valid answer that the function ALWAYS returns NULL, so your function is equivalent to:
BEGIN RETURN NULL; END;
2) It is a bug because the function is NOT deterministic.
Regards
Michel
[Updated on: Tue, 29 March 2011 05:29] Report message to a moderator
|
|
|
|
|
Re: Create materialized views depending on the sysdate with enable query rewrite option [message #501485 is a reply to message #501484] |
Tue, 29 March 2011 07:07   |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
If something is DETERMINISTIC, it means you can call it twice with the same inputs and it will return the same outputs. That is not true with your function. If you call it once with a date 9.99999 days ago and then again one second later, you would expect DIFFERENT results. But since you have told Oracle that it is Deterministic, Oracle will feel free to return the same result.
Defining a function as Deterministic when it really is not will produce unpredictable behavior.
Ross Leishman
|
|
|
|
|
Re: Create materialized views depending on the sysdate with enable query rewrite option [message #501497 is a reply to message #501339] |
Tue, 29 March 2011 08:01   |
 |
walnutsparrow2k
Messages: 9 Registered: March 2011
|
Junior Member |
|
|
Thank you very much for all your answers.
Michel,
" Do it yourself: create a table from this definition, create a job that periodically fills the mview as Oracle would do it."
Does that mean ,
create table employee (name varchar(40),joining_date date); -- base table
create materialized view M_Employee
enable query rewrite as
select joining_date , name
from
employee;
create view v_employee as
select joining_date , name
from
EMPLOYEE
where joining_date < TRUNC(sysdate) - 10;
Schedule this job to run periodically,
begin
insert into M_EMPLOYEE(JOINING_DATE,name) select joining_date , name from V_EMPLOYEE;
end;
Upon executing the last insert statement i get this error,
SQL Error: ORA-01732: data manipulation operation not legal on this view
01732. 00000 - "data manipulation operation not legal on this view"
*Cause:
*Action:
|
|
|
|
Re: Create materialized views depending on the sysdate with enable query rewrite option [message #501501 is a reply to message #501496] |
Tue, 29 March 2011 08:25   |
Manoj.Gupta.91
Messages: 239 Registered: March 2008 Location: Delhi
|
Senior Member |
|
|
Hi,
Ross said create the table with CURR_DATE as below.
CREATE TABLE EMPLOYEE
(
EMP_ID VARCHAR2(10) PRIMARY KEY,
EMP_NAME VARCHAR2(50) NOT NULL,
JOIN_DATE DATE NOT NULL,
CURR_DATE DATE
) ;
Now create a job that update CURR_DATE for employees at midnight.
UPDATE EMPLOYEE
SET CURR_DATE = TRUNC(SYSDATE) ;
COMMIT ;
Now base your MV on tables CURR_DATE
CREATE MATERIALIZED VIEW M_EMPLOYEE
REFRESH FAST ON COMMIT
ENABLE QUERY REWRITE
AS
SELECT E.JOINING_DATE , E.NAME
FROM EMPLOYEE E
WHERE E.JOIN_DATE < E.CURR_DATE - 10 ;
If I've 4 or 5 tables included in MV and each table have some DATE fields.
I want to compare DATE fields of these tables with SYSDATE.
1.) Then I need CURR_DATE column in each table.
2.) I need to create multiple or single job to update CURR_DATE in all tables.
My solution also works but that is a wrong practice. It is actually violating DETERMINISTIC meaning.
You provided an excellent solution for this but I've one question.
What happens when I've more than one table included in MV and I need to comparison with SYSDATE. Those are transactional tables and have lots of data in them. In midnight there is always EOD processes to complete and other important activities to finish before next day working starts. Update statement will put locks on the records and my EOD process may go into wait situation.
Due to this my regular working halts.
How can we handle such situation???
Thanks & Regards
Manoj
|
|
|
|
|
Re: Create materialized views depending on the sysdate with enable query rewrite option [message #501550 is a reply to message #501501] |
Tue, 29 March 2011 17:08   |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
manoj91 wrote on Wed, 30 March 2011 00:25Hi,
Ross said create the table with CURR_DATE as below.
CREATE TABLE EMPLOYEE
(
EMP_ID VARCHAR2(10) PRIMARY KEY,
EMP_NAME VARCHAR2(50) NOT NULL,
JOIN_DATE DATE NOT NULL,
CURR_DATE DATE
) ;
No I didn't. What I meant was exactly what @walnutsparrow2k implemented, except I was also recommending a scheduled job to update the DATE_TABLE table.
Ross Leishman
|
|
|
|
|
|
|
|
|