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

On Thu, 14 Feb 2008 08:49:48 +0100, Alex Traud <alex.traud_at_gmx.de> wrote:

>we did not query for an executaion plan for every testcase
>we ran, however, for the queries we got an execution plan
>for we saw, that
>
>- the costs for the Joined query (involving LOCATION)
> were low (<100)
>- the costs for the MV_LOCATIONS based query were high
> (>1000)
>
>As the tests use varying xmin,xmax and ymin,ymax parameters
>the execution plans will also return different costs, so its
>difficult to make a more specific statement on this.
>
>I think the problem is really the PHYSICAL_READ value. But
>until know, it's not clear to me what causes the high number
>of reads.

Cost is an irrelevant criterion.
The physical_read value is also not the problem. The problem is your statement is accessing only one of the m:n columns of the junction tabel, and indexing structure of MV_LOCATIONS is different from indexing structure of the underlying tables.

Post those explain plan results, using dbms_xplan

-- 
Sybrand Bakker
Senior Oracle DBA