having a mismatch between querying data and MV refresh

Hans Langenberg

Hi, Having a question.

We have a question about a Materialized View request.

We have a MV, and the query defining it is complex.

But when i run the query it takes seconds to give me all the rows, when i use the query to fill a "normal" table it take about twice the amount of time. But when i do a refresh of the MV it takes almost 10 minutes. (being hours on production with full data set)

Our database version is 12.1

The insert of the refresh holds the following hint:

/*+ BYPASS_RECURSIVE_CHECK APPEND */

TIA for any idea that might help to solve this problem,

HansL

User_6XD9J

Can you paste the sQL query to use the materialzed view and the table definitions that the query references ? Have you profiled the MV query ?

Hans Langenberg

a) No i can not give the definitions (sorry company policy)

b) Have to check profile.

User_6XD9J

How many rows are returned by the MV (use count (*))

Hans Langenberg

3112 rows

User_6XD9J

are you able to profile the MV query ?