0

when I create a materialize view, I use in the query an order by ant it works fine. But when I refresh the MV with DBMS_MVIEW.REFRESH(('T_SEARCH', 'C') everything is completely messed up. So is there a way to avoid that problem ?

Thanks

Slim
  • 1,708
  • 5
  • 37
  • 60
jacen44
  • 168
  • 3
  • 18
  • Can we have an example of the code? – Slim Nov 15 '12 at 12:31
  • the order define by the order by during the creation – jacen44 Nov 15 '12 at 12:34
  • An `order by` during the view creation is pointless overhead. The data stored in the view (or any table) has no inherent order, even if it might look like it sometimes. When you select from the view, the only way to get consistent ordering is to use an `order by` clause as part of that select, as @Álvaro G. Vicario says. See [this old question](http://stackoverflow.com/q/899514/266304) too. – Alex Poole Nov 15 '12 at 12:48

1 Answers1

4

If you mean that SELECT * FROM T_SEARCH does not provide a coherent sort order, that's the expected behaviour in all SQL dialects I know. The fact that T_SEARCH is not a physical table does not trigger any exception: you need to provide an ORDER BY clause in the SELECT query.

Álvaro González
  • 142,137
  • 41
  • 261
  • 360