Hello Dmitry

Writing:
SELECT * FROM V_ABM_PERSONAS V WHERE V.PER_IDENTI > 0 ROWS 1 TO 100

The plan is:
PLAN JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (V P ORDER UQ_PERSONAS1 INDEX
(PK_PERSONAS), V D INDEX (UQ_DEPARTAMENTOS)), V L INDEX (UQ_LOCALIDADES)),
V B INDEX (UQ_BARRIOS)), V R INDEX (PK_PROFESIONES)), V S INDEX
(UQ_SUCURSALES1)), V O INDEX (PK_LOCALES))

It takes 3 seconds, 28 seconds, 41 seconds, 38 seconds, etc. No idea why
the difference between one execution and other.

Writing:
SELECT * FROM V_ABM_PERSONAS V ORDER BY V.PER_IDENTI ROWS 1 TO 100

The plan is:
PLAN SORT (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (V P ORDER UQ_PERSONAS1, V D
INDEX (UQ_DEPARTAMENTOS)), V L INDEX (UQ_LOCALIDADES)), V B INDEX
(UQ_BARRIOS)), V R INDEX (PK_PROFESIONES)), V S INDEX (UQ_SUCURSALES1)), V
O INDEX (PK_LOCALES)))

It takes more then 53 minutes!!! (and counting...)

Writing:
SELECT FIRST 100 * FROM V_ABM_PERSONAS V ORDER BY V.PER_IDENTI

The plan is:
PLAN SORT (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (V P ORDER UQ_PERSONAS1, V D
INDEX (UQ_DEPARTAMENTOS)), V L INDEX (UQ_LOCALIDADES)), V B INDEX
(UQ_BARRIOS)), V R INDEX (PK_PROFESIONES)), V S INDEX (UQ_SUCURSALES1)), V
O INDEX (PK_LOCALES)))

PK_ are the Primary Keys

UQ_ are the Unique Keys

Greetings.

Walter.



















On Fri, Mar 1, 2013 at 8:46 AM, Dmitry Yemanov <dimitr@...

> wrote:

> **
>
>
> 01.03.2013 8:37, W O wrote:
>
> > I have a table with 3.600.000 rows more or less.
> >
> > Writing:
> > SELECT * FROM V_ABM_PERSONAS V WHERE V.PER_IDENTI > 0 ROWS 1 TO 100
> >
> > takes 2.656 seconds (less than 3 seconds)
> >
> > Writing:
> > SELECT * FROM V_ABM_PERSONAS V ORDER BY V.PER_IDENTI ROWS 1 TO 100
> >
> > takes more than 11 minutes!!!! (bored, I did cancel the execution)
>
> Please show us the query plans.
>
> Dmitry
>
>
>

[Non-text portions of this message have been removed]