The view V_ABM_PERSONAS is:

CREATE VIEW V_ABM_PERSONAS(
PER_IDENTI,
PER_CODIGO,
PER_CEDULA,
PER_APELLD,
PER_NOMBRE,
PER_APODOX,
PER_FECNAC,
PER_SEXOXX,
PER_CODDEP,
PER_NOMDEP,
PER_CODLOC,
PER_NOMLOC,
PER_CODBAR,
PER_NOMBAR,
PER_DIRECC,
PER_TELPAR,
PER_TELLAB,
PER_CELUL1,
PER_CELUL2,
PER_CELUL3,
PER_EMAILX,
PER_IDEPRO,
PER_NOMPRO,
PER_FECINS,
PER_CODSUC,
PER_NOMSUC,
PER_HABILI,
PER_IDELVO,
PER_NOMLVO,
PER_POSEEV,
PER_CAPVEH,
PER_CABGRU,
PER_FOTOGR,
PER_APENOM)
AS
SELECT
P.PER_IDENTI,
P.PER_CODIGO,
P.PER_CEDULA,
P.PER_APELLD,
P.PER_NOMBRE,
P.PER_APODOX,
P.PER_FECNAC,
P.PER_SEXOXX,
P.PER_CODDEP,
D.DEP_NOMBRE AS PER_NOMDEP,
P.PER_CODLOC,
L.LOC_NOMBRE AS PER_NOMLOC,
P.PER_CODBAR,
B.BAR_NOMBRE AS PER_NOMBAR,
P.PER_DIRECC,
P.PER_TELPAR,
P.PER_TELLAB,
P.PER_CELUL1,
P.PER_CELUL2,
P.PER_CELUL3,
P.PER_EMAILX,
P.PER_IDEPRO,
COALESCE(R.PRO_NOMBRE, '') AS PER_NOMPRO,
P.PER_FECINS,
P.PER_CODSUC,
COALESCE(S.SUC_NOMBRE, '') AS PER_NOMSUC,
P.PER_HABILI,
P.PER_IDELVO,
COALESCE(O.LOC_NOMBRE, '') AS PER_NOMLVO,
P.PER_POSEEV,
P.PER_CAPVEH,
P.PER_CABGRU,
P.PER_FOTOGR,
P.PER_APENOM
FROM
PERSONAS P
LEFT JOIN
DEPARTAMENTOS D
ON P.PER_CODDEP = D.DEP_CODIGO
LEFT JOIN
LOCALIDADES L
ON P.PER_CODDEP = L.LOC_CODDEP AND
P.PER_CODLOC = L.LOC_CODIGO
LEFT JOIN
BARRIOS B
ON P.PER_CODDEP = B.BAR_CODDEP AND
P.PER_CODLOC = B.BAR_CODLOC AND
P.PER_CODBAR = B.BAR_CODIGO
LEFT JOIN
PROFESIONES R
ON P.PER_IDEPRO = R.PRO_IDENTI
LEFT JOIN
SUCURSALES S
ON P.PER_CODSUC = S.SUC_CODIGO
LEFT JOIN
LOCALES O
ON P.PER_IDELVO = O.LOC_IDENTI
ORDER BY
P.PER_CEDULA;

There is an index on the column PER_CEDULA and all the tables are using its
Primary Key or an Unique Key.

CREATE INDEX IDX_PERSONAS1 ON PERSONAS(PER_CEDULA);

Greetings.

Walter.

On Fri, Mar 1, 2013 at 9:23 AM, W O <sistemas2000profesional@...>wrote:

> 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]