Path: news.netfront.net!news.glorb.com!gegeweb.org!aioe.org!not-for-mail
From: Alex Traud <alex.traud@gmx.de>
Newsgroups: comp.databases.oracle.server
Subject: Slow select queries with joins, fast queries with materialized view:
 High PHYSICAL_READS value in V$SESS_IO
Date: Wed, 13 Feb 2008 21:37:04 +0100
Organization: Aioe.org NNTP Server
Lines: 60
Message-ID: <fovkde$dgh$1@aioe.org>
NNTP-Posting-Host: h6+aABiFJ8UbEafZFQnNVQ.user.aioe.org
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 7bit
X-Complaints-To: abuse@aioe.org
User-Agent: Thunderbird 2.0.0.9 (Windows/20071031)
Xref: news.netfront.net comp.databases.oracle.server:182420

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