asked on

Double TABLE SCAN ACCESS

To improve the query that is terrible

SELECT RGA_ID
  FROM GESAMBIENTE.EVENTO_AMBIENTE EVA_A
 INNER JOIN GESAMBIENTE.HORARIO_EVENTO_AMBIENTE HEA_A ON (EVA_A.EVA_ID = HEA_A.EVA_ID)
 INNER JOIN(GESAMBIENTE.EVENTO_AMBIENTE EVA_B
            INNER JOIN GESAMBIENTE.HORARIO_EVENTO_AMBIENTE HEA_B ON (EVA_B.EVA_ID = HEA_B.EVA_ID)) 
    ON ((HEA_A.HVA_HORA_INICIO BETWEEN HEA_B.HVA_HORA_INICIO                AND ((HEA_B.HVA_HORA_FIN) - 1 / 86400)) OR 
        (HEA_A.HVA_HORA_FIN    BETWEEN((HEA_B.HVA_HORA_INICIO) + 1 / 86400) AND HEA_B.HVA_HORA_FIN) OR 
        (HEA_B.HVA_HORA_INICIO BETWEEN HEA_A.HVA_HORA_INICIO                AND ((HEA_A.HVA_HORA_FIN) - 1 / 86400)) OR 
        (HEA_B.HVA_HORA_FIN    BETWEEN((HEA_A.HVA_HORA_INICIO) + 1 / 86400) AND HEA_A.HVA_HORA_FIN))
 INNER JOIN GESAMBIENTE.RGAXEVENTO_AMBIENTE RGA ON (RGA.EVA_ID = EVA_B.EVA_ID)
 WHERE EVA_A.EVA_ID = 10968--:8
   AND EVA_A.AMB_ID = EVA_B.AMB_ID
   AND HEA_A.HVA_ESTADO = '1'
   AND HEA_B.HVA_ESTADO = '1'
   AND EVA_A.EVA_ESTADO = '1'
   AND EVA_B.EVA_ESTADO = '1'
 GROUP BY RGA_ID

Open in new window

create table GESAMBIENTE.HORARIO_EVENTO_AMBIENTE
(
  HVA_ID           NUMBER(10),
  EVA_ID           NUMBER(10),
  HVA_HORA_INICIO  DATE,
  HVA_HORA_FIN     DATE,
  HVA_ESTADO       CHAR(1),
  NOM_ID           NUMBER(10),
  HVA_DIA_SEMANA   VARCHAR2(20),
  HVA_FCH_REGISTRO DATE default SYSDATE not null,
  NIS_FUN_REGISTRO NUMBER(12)
)partition by hash (HVA_ID) (
  partition HVA_ID_P1 tablespace GESAMBIENTE_DAT_01,
  partition HVA_ID_P2 tablespace GESAMBIENTE_DAT_02,
  partition HVA_ID_P3 tablespace GESAMBIENTE_DAT_03,
  partition HVA_ID_P4 tablespace GESAMBIENTE_DAT_04
)
alter table GESAMBIENTE.HORARIO_EVENTO_AMBIENTE add constraint HVA_PK primary key (HVA_ID);
alter table GESAMBIENTE.HORARIO_EVENTO_AMBIENTE add constraint EVA_HVA_FK foreign key (EVA_ID)
  references GESAMBIENTE.EVENTO_AMBIENTE (EVA_ID);
alter table GESAMBIENTE.HORARIO_EVENTO_AMBIENTE add constraint NOM_HVA_FK foreign key (NOM_ID)
  references GESAMBIENTE.NOVEDAD_AMBIENTE (NOM_ID);

create index GESAMBIENTE.I_EVA_HVA_FK on GESAMBIENTE.HORARIO_EVENTO_AMBIENTE (EVA_ID, HVA_ID)
create index GESAMBIENTE.I_HORA_INI_FIN on GESAMBIENTE.HORARIO_EVENTO_AMBIENTE (HVA_HORA_INICIO, HVA_HORA_FIN, HVA_ID)
create unique index GESAMBIENTE.I_HVA_PK on GESAMBIENTE.HORARIO_EVENTO_AMBIENTE (HVA_ID);
create index GESAMBIENTE.I_NOM_HVA_FK on GESAMBIENTE.HORARIO_EVENTO_AMBIENTE (NOM_ID);

Open in new window

q004ExplainPlan.jpg

Sean Stuber

please post dbms_xplan.display output

it's much easier to read and more thorough

jairdur

ASKER

Someone is looking into this case?

is very urgent

Sean Stuber

please post requested information

jairdur

ASKER

SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2401590011
--------------------------------------------------------------------------------
| Id  | Operation                                | Name                    | Row
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                         | 925
|   1 |  HASH GROUP BY                           |                         | 925
|*  2 |   HASH JOIN                              |                         | 925
|*  3 |    HASH JOIN                             |                         |  37
|*  4 |     TABLE ACCESS BY LOCAL INDEX ROWID    | EVENTO_AMBIENTE         |  25
|   5 |      NESTED LOOPS                        |                         | 350
|   6 |       NESTED LOOPS                       |                         |
|   7 |        PARTITION HASH SINGLE             |                         |
|*  8 |         TABLE ACCESS BY LOCAL INDEX ROWID| EVENTO_AMBIENTE         |
|*  9 |          INDEX UNIQUE SCAN               | I_EVA_PK                |
|* 10 |        TABLE ACCESS BY GLOBAL INDEX ROWID| HORARIO_EVENTO_AMBIENTE |
|* 11 |         INDEX RANGE SCAN                 | I_EVA_HVA_FK            |
|  12 |       PARTITION HASH ALL                 |                         |
|* 13 |        INDEX RANGE SCAN                  | I_AMB_EVA_FK            |
|  14 |     PARTITION HASH ALL                   |                         |  83
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 15 |      TABLE ACCESS FULL                   | HORARIO_EVENTO_AMBIENTE |  83
|  16 |    PARTITION HASH ALL                    |                         |
|  17 |     TABLE ACCESS FULL                    | RGAXEVENTO_AMBIENTE     |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("RGA"."EVA_ID"="EVA_B"."EVA_ID")
   3 - access("HEA_B"."EVA_ID"="EVA_B"."EVA_ID")
       filter("HEA_A"."HVA_HORA_INICIO">="HEA_B"."HVA_HORA_INICIO" AND
              "HEA_A"."HVA_HORA_INICIO"<=INTERNAL_FUNCTION("HEA_B"."HVA_HORA_FIN
              "HEA_A"."HVA_HORA_FIN">=INTERNAL_FUNCTION("HEA_B"."HVA_HORA_INICIO
              "HEA_A"."HVA_HORA_FIN"<="HEA_B"."HVA_HORA_FIN" OR "HEA_B"."HVA_HOR
              "HEA_B"."HVA_HORA_INICIO"<=INTERNAL_FUNCTION("HEA_A"."HVA_HORA_FIN
              "HEA_B"."HVA_HORA_FIN">=INTERNAL_FUNCTION("HEA_A"."HVA_HORA_INICIO
              "HEA_B"."HVA_HORA_FIN"<="HEA_A"."HVA_HORA_FIN")
   4 - filter("EVA_B"."EVA_ESTADO"='1')
   8 - filter("EVA_A"."EVA_ESTADO"='1')
   9 - access("EVA_A"."EVA_ID"=10968)
  10 - filter("HEA_A"."HVA_ESTADO"='1')
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
  11 - access("HEA_A"."EVA_ID"=10968)
  13 - access("EVA_A"."AMB_ID"="EVA_B"."AMB_ID")
  15 - filter("HEA_B"."HVA_ESTADO"='1')
 
44 rows selected
 
SQL>

Open in new window

Sean Stuber

can you extend your linesize ? your output is truncated

jairdur

ASKER

Plan hash value: 2401590011
 
--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                         | 92542 |  7591K|       | 33789   (8)| 00:02:51 |       |       |
|   1 |  HASH GROUP BY                           |                         | 92542 |  7591K|    16M| 33789   (8)| 00:02:51 |       |       |
|*  2 |   HASH JOIN                              |                         | 92542 |  7591K|       | 32245   (8)| 00:02:44 |       |       |
|*  3 |    HASH JOIN                             |                         |  3734 |   269K|  2128K| 21909   (7)| 00:01:51 |       |       |
|*  4 |     TABLE ACCESS BY LOCAL INDEX ROWID    | EVENTO_AMBIENTE         |  2500 | 32500 |       |    15   (0)| 00:00:01 |       |       |
|   5 |      NESTED LOOPS                        |                         | 35089 |  1713K|       |   231   (0)| 00:00:02 |       |       |
|   6 |       NESTED LOOPS                       |                         |    14 |   518 |       |    21   (0)| 00:00:01 |       |       |
|   7 |        PARTITION HASH SINGLE             |                         |     1 |    13 |       |     2   (0)| 00:00:01 |     1 |     1 |
|*  8 |         TABLE ACCESS BY LOCAL INDEX ROWID| EVENTO_AMBIENTE         |     1 |    13 |       |     2   (0)| 00:00:01 |     1 |     1 |
|*  9 |          INDEX UNIQUE SCAN               | I_EVA_PK                |     1 |       |       |     1   (0)| 00:00:01 |     1 |     1 |
|* 10 |        TABLE ACCESS BY GLOBAL INDEX ROWID| HORARIO_EVENTO_AMBIENTE |    14 |   336 |       |    19   (0)| 00:00:01 | ROWID | ROWID |
|* 11 |         INDEX RANGE SCAN                 | I_EVA_HVA_FK            |    20 |       |       |     3   (0)| 00:00:01 |       |       |
|  12 |       PARTITION HASH ALL                 |                         |    13 |       |       |     3   (0)| 00:00:01 |     1 |     4 |
|* 13 |        INDEX RANGE SCAN                  | I_AMB_EVA_FK            |    13 |       |       |     3   (0)| 00:00:01 |     1 |     4 |
|  14 |     PARTITION HASH ALL                   |                         |  8364K|   191M|       | 16699   (8)| 00:01:25 |     1 |     4 |
|* 15 |      TABLE ACCESS FULL                   | HORARIO_EVENTO_AMBIENTE |  8364K|   191M|       | 16699   (8)| 00:01:25 |     1 |     4 |
|  16 |    PARTITION HASH ALL                    |                         |    19M|   185M|       |  9981   (7)| 00:00:51 |     1 |     4 |
|  17 |     TABLE ACCESS FULL                    | RGAXEVENTO_AMBIENTE     |    19M|   185M|       |  9981   (7)| 00:00:51 |     1 |     4 |
--------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("RGA"."EVA_ID"="EVA_B"."EVA_ID")
   3 - access("HEA_B"."EVA_ID"="EVA_B"."EVA_ID")
       filter("HEA_A"."HVA_HORA_INICIO">="HEA_B"."HVA_HORA_INICIO" AND 
              "HEA_A"."HVA_HORA_INICIO"<=INTERNAL_FUNCTION("HEA_B"."HVA_HORA_FIN")-.00001157407407407407407407407407407407407407 OR 
              "HEA_A"."HVA_HORA_FIN">=INTERNAL_FUNCTION("HEA_B"."HVA_HORA_INICIO")+.00001157407407407407407407407407407407407407 AND 
              "HEA_A"."HVA_HORA_FIN"<="HEA_B"."HVA_HORA_FIN" OR "HEA_B"."HVA_HORA_INICIO">="HEA_A"."HVA_HORA_INICIO" AND 
              "HEA_B"."HVA_HORA_INICIO"<=INTERNAL_FUNCTION("HEA_A"."HVA_HORA_FIN")-.00001157407407407407407407407407407407407407 OR 
              "HEA_B"."HVA_HORA_FIN">=INTERNAL_FUNCTION("HEA_A"."HVA_HORA_INICIO")+.00001157407407407407407407407407407407407407 AND 
              "HEA_B"."HVA_HORA_FIN"<="HEA_A"."HVA_HORA_FIN")
   4 - filter("EVA_B"."EVA_ESTADO"='1')
   8 - filter("EVA_A"."EVA_ESTADO"='1')
   9 - access("EVA_A"."EVA_ID"=10968)
  10 - filter("HEA_A"."HVA_ESTADO"='1')
  11 - access("HEA_A"."EVA_ID"=10968)
  13 - access("EVA_A"."AMB_ID"="EVA_B"."AMB_ID")
  15 - filter("HEA_B"."HVA_ESTADO"='1')

Open in new window

Sean Stuber

are the row counts reasonable?

for example do you exepct EVENT0_AMBIENTE to have approximately 2500 rows for EVA_ESTADO = '1' ?

if not, your statistics are out of date or need to be recalculated with higher precision

also, your date comparisons can probably be simplified.
try this...

SELECT   rga_id
    FROM gesambiente.evento_ambiente eva_a
        INNER JOIN gesambiente.horario_evento_ambiente hea_a
            ON eva_a.eva_id = hea_a.eva_id
        INNER JOIN gesambiente.evento_ambiente eva_b
            ON eva_b.eva_id = hea_b.eva_id
        INNER JOIN gesambiente.horario_evento_ambiente hea_b
            ON     hea_a.hva_hora_inicio < hea_b.hva_hora_fin
               AND hea_a.hva_hora_fin > hea_b.hva_hora_inicio
        INNER JOIN gesambiente.rgaxevento_ambiente rga
            ON rga.eva_id = eva_b.eva_id
   WHERE     eva_a.eva_id = 10968  --:8
         AND eva_a.amb_id = eva_b.amb_id
         AND hea_a.hva_estado = '1'
         AND hea_b.hva_estado = '1'
         AND eva_a.eva_estado = '1'
         AND eva_b.eva_estado = '1'
GROUP BY rga_id

Open in new window

jairdur

ASKER

GROUP BY rga_id
*
Error at line 6
ORA-00904: "HEA_B"."EVA_ID": invalid identifier

Sean Stuber

change the order of the joins

SELECT rga_id
FROM gesambiente.evento_ambiente eva_a
INNER JOIN gesambiente.horario_evento_ambiente hea_a
ON eva_a.eva_id = hea_a.eva_id
INNER JOIN gesambiente.horario_evento_ambiente hea_b
ON hea_a.hva_hora_inicio < hea_b.hva_hora_fin
AND hea_a.hva_hora_fin > hea_b.hva_hora_inicio
INNER JOIN gesambiente.evento_ambiente eva_b
ON eva_b.eva_id = hea_b.eva_id
INNER JOIN gesambiente.rgaxevento_ambiente rga
ON rga.eva_id = eva_b.eva_id
WHERE eva_a.eva_id = 10968 --:8
AND eva_a.amb_id = eva_b.amb_id
AND hea_a.hva_estado = '1'
AND hea_b.hva_estado = '1'
AND eva_a.eva_estado = '1'
AND eva_b.eva_estado = '1'
GROUP BY rga_id

jairdur

ASKER

mmm sentences must generate the same result. Truth? What are you considering?

Sean Stuber

the only thing I changed was rearranging the table order and removing parentheses.
and I changed your date comparisons.

your "INICIO between INICIO and FIN" conditions are checking if A and B overlap on date ranges.

is that correct?

if so, then

hea_a.hva_hora_inicio < hea_b.hva_hora_fin
AND hea_a.hva_hora_fin > hea_b.hva_hora_inicio

does the same thing, but more efficiently.

but, only you can verify if the changes are correct or not. Test it and check the results

SOLUTION

jairdur

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sean Stuber

what am I supposed to see?

did you try what I posted? What were the results?

jairdur

ASKER

For assessing No. We are working on the question

Sean Stuber

I have no idea what you mean : "for assessing No." what is No? and how am I supposed to assess it?

"We are working on the question" - right, that mean you need to try the suggestions and let me know how they work so I know what, if anything, I need to do differently in my next post.

Without knowing how my query is working in relation to the original query I have nothing to do with the second query.
Especially since I don't know what you intended by posting it.

jairdur

ASKER

sdstuber:. In fact, this last statement is an improvement from 70 to 5 seconds without TABLE ACCESS FULL. Do you have any suggestions?

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jairdur

ASKER

Thanks