Home » SQL & PL/SQL » SQL & PL/SQL » Create materialized views depending on the sysdate with enable query rewrite option
Create materialized views depending on the sysdate with enable query rewrite option [message #501334] Mon, 28 March 2011 10:46 Go to next message
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 #501339 is a reply to message #501334] Mon, 28 March 2011 11:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Do it yourself: create a table from this definition, create a job that periodically fills the mview as Oracle would do it.

Regards
Michel
Re: Create materialized views depending on the sysdate with enable query rewrite option [message #501432 is a reply to message #501339] Tue, 29 March 2011 03:23 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You could create a table that contains the current date and a DBMS_SCHEDULER job that updates it at midnight, then use this table in the MV (and the queries that you want re-written).

Ross Leishman
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 Go to previous messageGo to next message
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 #501447 is a reply to message #501440] Tue, 29 March 2011 04:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
EXCEPTION
WHEN OTHERS THEN
NULL ;

This is a bug.

Quote:
DETERMINISTIC

This is another bug, the function is NOT deterministic.

Regards
Michel
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #501481 is a reply to message #501456] Tue, 29 March 2011 06:49 Go to previous messageGo to next message
Manoj.Gupta.91
Messages: 239
Registered: March 2008
Location: Delhi
Senior Member
Hi,

I understand point 1. I'll never put exception handling code.

I'm failed to understand point 2. I created this function as deterministic but you are saying it's not deterministic.

Please let me know why it is not deterministic??

Can you correct my example function to become deterministic.

Thanks & Regards
Manoj
Re: Create materialized views depending on the sysdate with enable query rewrite option [message #501484 is a reply to message #501481] Tue, 29 March 2011 06:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
it is not deterministic because it uses sysdate to build its result and sysdate is not deterministic.

Regards
Michel
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 Go to previous messageGo to next message
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 #501494 is a reply to message #501485] Tue, 29 March 2011 07:52 Go to previous messageGo to next message
Manoj.Gupta.91
Messages: 239
Registered: March 2008
Location: Delhi
Senior Member
Hi,

I agree and now deterministic function is more clear to me.

But this solution actually worked for this problem and I've done this in past.

Is it a wrong practice?
Is there any other better way to handle this?

Thanks & Regards
Manoj
Re: Create materialized views depending on the sysdate with enable query rewrite option [message #501496 is a reply to message #501494] Tue, 29 March 2011 07:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
Is it a wrong practice?

Telling something wrong to Oracle is not a good practice.
Do you really understand Ross' example?

Quote:
Is there any other better way to handle this?

Is this not what we said before your post?

Regards
Michel
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 Go to previous messageGo to next message
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 #501500 is a reply to message #501497] Tue, 29 March 2011 08:21 Go to previous messageGo to next message
walnutsparrow2k
Messages: 9
Registered: March 2011
Junior Member
Quote:
You could create a table that contains the current date and a DBMS_SCHEDULER job that updates it at midnight, then use this table in the MV (and the queries that you want re-written).
Ross Leishman


That worked fine. Thank you very much!
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 Go to previous messageGo to next message
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 #501506 is a reply to message #501501] Tue, 29 March 2011 08:38 Go to previous messageGo to next message
walnutsparrow2k
Messages: 9
Registered: March 2011
Junior Member
Hi Manoj,

I created a separate table just to store the date value, in that case updating should not take long.
create table DATE_TABLE (current_date date);

create materialized view M_Employee
enable query rewrite as
select joining_date , name
from
EMPLOYEE ,DATE_TABLE
where joining_date > date_table.current_date;

Thanks.
Re: Create materialized views depending on the sysdate with enable query rewrite option [message #501514 is a reply to message #501506] Tue, 29 March 2011 09:59 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
Just another point, the requirements are "i want to create a view with the list of employees who joined the company in the last 10 days." and the code is

joining_datde < TRUNC(sysdate) - 10

It should be

joining_datde >= TRUNC(sysdate) - 10
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 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
manoj91 wrote on Wed, 30 March 2011 00:25
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
      ) ;


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
Re: Create materialized views depending on the sysdate with enable query rewrite option [message #501624 is a reply to message #501550] Wed, 30 March 2011 03:38 Go to previous messageGo to next message
walnutsparrow2k
Messages: 9
Registered: March 2011
Junior Member
There seems to be rolling window concept for materialized views. Since our base tables are already partitioned, it might be more efficient to partition the MV and then create and drop partitions pretty much the same way as it is been done for the base tables.

Re: Create materialized views depending on the sysdate with enable query rewrite option [message #501938 is a reply to message #501624] Fri, 01 April 2011 09:50 Go to previous messageGo to next message
walnutsparrow2k
Messages: 9
Registered: March 2011
Junior Member
I created the following view which worked fine,

Quote:
create materialized view M_Employee
refresh fast on commit
enable query rewrite as
select joining_date , name
from
employee
where joining_date < TRUNC(sysdate) - 10


But , when i tried to do the following query (2011-04-01 is still within the TRUNC(sysdate) - 10 range)

select joining_date , name
from
employee
where joining_date > TO_DATE('2011-04-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss');

The autotrace shows that the data is been fetched from the base table instead of the materialized view.
Ideally it should be pointing to the materialized view, isn't it?
I will have to get this result from the materialized view instead of the base tables.
Any thoughts?
Thanks!
Re: Create materialized views depending on the sysdate with enable query rewrite option [message #501940 is a reply to message #501938] Fri, 01 April 2011 10:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
Ideally it should be pointing to the materialized view, isn't it?

No, your query has nothing to do with the mview definition. It is the expected behaviour.

Regards
Michel
Re: Create materialized views depending on the sysdate with enable query rewrite option [message #501942 is a reply to message #501940] Fri, 01 April 2011 10:22 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I want to create a materialized view for the last 10 days with the enable query rewrite option.
why a materialized view?
why not just a plain VIEW?
Re: Create materialized views depending on the sysdate with enable query rewrite option [message #501944 is a reply to message #501940] Fri, 01 April 2011 10:39 Go to previous messageGo to next message
walnutsparrow2k
Messages: 9
Registered: March 2011
Junior Member
Hi Michel,

Does that mean if i want to use the mview then i have to use the exact query that is been used to define the view?

In my application i have several joins and aggregations and thats why i ended up creating a materialized view for the last 10 days of data which is the most frequently used data.

At most instances users might want to just get 4th day or 5th day data.I was under the impression that filtering the column which the date should get the data from the materialized view , which would save enormous time in our case. But with this query data seems to be selected from the base table.

Is there any other work around for this?

Thanks!
Re: Create materialized views depending on the sysdate with enable query rewrite option [message #501948 is a reply to message #501944] Fri, 01 April 2011 11:23 Go to previous message
Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
Does that mean if i want to use the mview then i have to use the exact query that is been used to define the view?

Almost same one, Oracle must be able to substitute part of your query with the mview, so your query must contain about this definition.

Regards
Michel
Previous Topic: SQL
Next Topic: Help to make relational query... (2 Merged)
Goto Forum:
  


Current Time: Wed Aug 20 07:18:51 CDT 2025