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

On Feb 13, 2:37 pm, Alex Traud <alex.tr..._at_gmx.de> wrote:
> 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

What did the respective execution plans reveal?

David Fitzjarrell Received on Wed Feb 13 2008 - 15:28:31 CST