Hi all,
I'm exploring Oracle 12c new functionalities.
In SQL the rows limiting clause has been added:
{code}
O12c>Create table test_fetch as
2 Select level x from dual connect by level<=30;
Table created.
O12c>select * from test_fetch
2 order by x
3 fetch first 15 rows only;
X
----------
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
15 rows selected.
{/code}
the problem is that I'm not able to define a materialized view of the above query:
{code}
O12c>create materialized view mv_fetch as
2 select * from test_fetch
3 order by x
4 fetch first 15 rows only;
fetch first 15 rows only
*
ERROR at line 4:
ORA-00933: SQL command not properly ended
{/code}
While, if I remove the rows limiting clause the statement works:
{code}
O12c>create materialized view mv_fetch as
2 select * from test_fetch
3 order by x;
Materialized view created.
{/code}
On Oracle 12c docs I've just found that "Materialized views are not eligible for an incremental refresh if the defining query contains the row_limiting_clause", that doesn't mean the you cannot create a materialized view using the row limiting clause...
What am I missing?
Thanks,
Massimo