Slow select queries with joins, fast queries with materialized view: High PHYSICAL_READS value in V$SESS_IO

Hello Everybody,

I am facing a hard problem with poor performing queries that involves a join of two tables for quite a while now. Here's the situatation:

we have the tables

• LOCATION: a table contained geo information for locations with x/y coordinates, Primary key: LOCATION_ID, columns X,Y and others
• LOCATION_LAYER_ASSIGN: an assignement table that assigns locations to layer (columns LOCATION_ID, LAYER_ID, m:n)
• a materialzed view MV_LOCATIONS that is created using these two tables. Columns LOCATION_ID, X, Y, LAYER_ID.

fast query against MV_LOCATIONS:
---

SELECT *
  FROM MV_LOCATIONS WHERE
  (X BETWEEN ? AND ?) AND (Y BETWEEN ? AND ?)   AND LAYER_ID IN (<layer_ids>)
---

slow querey against LOCATION/LOCATION_LAYER_ASSIGN:
---

SELECT *
FROM
  LOCATION INNER JOIN LOCATION_LAYER_ASSIGN   ON (LOCATION.LOCATION_ID = LOCATION_LAYER_ASSIGN.LOCATION_ID) WHERE
  (X BETWEEN ? AND ?) AND (Y BETWEEN ? AND ? )   AND LAYER_ID IN (<layer_ids>)
---

We ran some thousand test queries and anaylized the table V$SESS_IO for a LOCATION and MV_LOCATIONS based database session. The result was that the value PHYSICAL_READS associated with the LOCATION based session was 10 to 1000 times higher compared to the value associated with the MV_LOCATION based session.

Obviously, in the former case, we are doing a lot more physical IO than in the later one, however, I cannot figure out why.

We also tried replacing the JOIN with a sub select and other changes to the SQL statement like replacing the BETWEEN statements with relational operator. However, nothing improved.

can anyone give me a hint what might be going wrong here and what I can do?

many thanks in advance.

Alex Received on Wed Feb 13 2008 - 14:37:04 CST