Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Double TABLE SCAN ACCESS

Posted on 2011-10-20
18
Medium Priority
?
262 Views
Last Modified: 2012-05-12


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
0
Comment
Question by:jairdur
  • 9
  • 9
18 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 37003279
please post dbms_xplan.display  output

it's much easier to read and more thorough
0
 

Author Comment

by:jairdur
ID: 37006304
Someone is looking into this case?

is very urgent
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37006322
please post requested information
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:jairdur
ID: 37006404

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

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37006426
can you extend your linesize ? your output is truncated
0
 

Author Comment

by:jairdur
ID: 37006474

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

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37006563
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

0
 

Author Comment

by:jairdur
ID: 37006748
GROUP BY rga_id
                              *
Error at line 6
ORA-00904: "HEA_B"."EVA_ID": invalid identifier
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37006765
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
0
 

Author Comment

by:jairdur
ID: 37006807
mmm sentences must generate the same result. Truth? What are you considering?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37006865
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


0
 

Assisted Solution

by:jairdur
jairdur earned 0 total points
ID: 37007267

Please see this


SELECT RGA_ID 
  FROM GESAMBIENTE.EVENTO_AMBIENTE EVA_A, 
       GESAMBIENTE.HORARIO_EVENTO_AMBIENTE HEA_A, 
(GESAMBIENTE.EVENTO_AMBIENTE EVA_B 
       INNER JOIN GESAMBIENTE.HORARIO_EVENTO_AMBIENTE HEA_B 
          ON EVA_B.EVA_ID = HEA_B.EVA_ID), 
       GESAMBIENTE.RGAXEVENTO_AMBIENTE RGA 
 WHERE 10968 = EVA_A.EVA_ID 
   AND EVA_B.AMB_ID = EVA_A.AMB_ID 
   AND '1' = HEA_A.HVA_ESTADO 
   AND '1' = HEA_B.HVA_ESTADO 
   AND '1' = EVA_A.EVA_ESTADO 
   AND '1' = EVA_B.EVA_ESTADO 
   AND EVA_B.EVA_ID = RGA.EVA_ID 
   AND HEA_A.EVA_ID = EVA_A.EVA_ID 
   AND HEA_A.EVA_ID = 10968 
   AND HEA_A.HVA_ESTADO = HEA_B.HVA_ESTADO 
   AND HEA_A.HVA_ESTADO = EVA_A.EVA_ESTADO 
   AND HEA_A.HVA_ESTADO = EVA_B.EVA_ESTADO 
   AND HEA_B.HVA_ESTADO = EVA_A.EVA_ESTADO 
   AND HEA_B.HVA_ESTADO = EVA_B.EVA_ESTADO 
   AND EVA_A.EVA_ESTADO = EVA_B.EVA_ESTADO 
   AND (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) 
 GROUP BY RGA_ID;
Plan hash value: 240025822
 
------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                         |     7 |   588 |   452   (1)| 00:00:03 |       |       |
|   1 |  HASH GROUP BY                           |                         |     7 |   588 |   452   (1)| 00:00:03 |       |       |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID      | RGAXEVENTO_AMBIENTE     |    34 |   340 |     8   (0)| 00:00:01 |       |       |
|   3 |    NESTED LOOPS                          |                         |     7 |   588 |   451   (1)| 00:00:03 |       |       |
|   4 |     NESTED LOOPS                         |                         |     1 |    74 |   443   (1)| 00:00:03 |       |       |
|*  5 |      HASH JOIN                           |                         |    34 |  1700 |    37   (3)| 00:00:01 |       |       |
|*  6 |       TABLE ACCESS BY GLOBAL INDEX ROWID | HORARIO_EVENTO_AMBIENTE |    14 |   336 |    19   (0)| 00:00:01 | ROWID | ROWID |
|*  7 |        INDEX RANGE SCAN                  | I_EVA_HVA_FK            |    20 |       |     3   (0)| 00:00:01 |       |       |
|   8 |       NESTED LOOPS                       |                         |  2012 | 52312 |    17   (0)| 00:00:01 |       |       |
|   9 |        PARTITION HASH SINGLE             |                         |     1 |    13 |     2   (0)| 00:00:01 |     1 |     1 |
|* 10 |         TABLE ACCESS BY LOCAL INDEX ROWID| EVENTO_AMBIENTE         |     1 |    13 |     2   (0)| 00:00:01 |     1 |     1 |
|* 11 |          INDEX UNIQUE SCAN               | I_EVA_PK                |     1 |       |     1   (0)| 00:00:01 |     1 |     1 |
|  12 |        PARTITION HASH ALL                |                         |  2012 | 26156 |    15   (0)| 00:00:01 |     1 |     4 |
|* 13 |         TABLE ACCESS BY LOCAL INDEX ROWID| EVENTO_AMBIENTE         |  2012 | 26156 |    15   (0)| 00:00:01 |     1 |     4 |
|* 14 |          INDEX RANGE SCAN                | I_AMB_EVA_FK            |    13 |       |     3   (0)| 00:00:01 |     1 |     4 |
|* 15 |      TABLE ACCESS BY GLOBAL INDEX ROWID  | HORARIO_EVENTO_AMBIENTE |     1 |    24 |    17   (0)| 00:00:01 | ROWID | ROWID |
|* 16 |       INDEX RANGE SCAN                   | I_EVA_HVA_FK            |    20 |       |     1   (0)| 00:00:01 |       |       |
|  17 |     PARTITION HASH ALL                   |                         |    34 |       |     3   (0)| 00:00:01 |     1 |     4 |
|* 18 |      INDEX RANGE SCAN                    | I_EVA_RGV_FK            |    34 |       |     3   (0)| 00:00:01 |     1 |     4 |
------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   5 - access("HEA_A"."EVA_ID"="EVA_A"."EVA_ID" AND "HEA_A"."HVA_ESTADO"="EVA_A"."EVA_ESTADO" AND 
              "HEA_A"."HVA_ESTADO"="EVA_B"."EVA_ESTADO")
   6 - filter("HEA_A"."HVA_ESTADO"='1')
   7 - access("HEA_A"."EVA_ID"=10968)
  10 - filter("EVA_A"."EVA_ESTADO"='1')
  11 - access("EVA_A"."EVA_ID"=10968)
  13 - filter("EVA_B"."EVA_ESTADO"='1' AND "EVA_A"."EVA_ESTADO"="EVA_B"."EVA_ESTADO")
  14 - access("EVA_B"."AMB_ID"="EVA_A"."AMB_ID")
  15 - filter("HEA_B"."HVA_ESTADO"='1' AND "HEA_A"."HVA_ESTADO"="HEA_B"."HVA_ESTADO" AND 
              "HEA_B"."HVA_ESTADO"="EVA_A"."EVA_ESTADO" AND "HEA_B"."HVA_ESTADO"="EVA_B"."EVA_ESTADO" AND 
              ("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")+.00
              001157407407407407407407407407407407407407 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")+.000
              01157407407407407407407407407407407407407 AND "HEA_B"."HVA_HORA_FIN"<="HEA_A"."HVA_HORA_FIN"))
  16 - access("HEA_B"."EVA_ID"="EVA_B"."EVA_ID")
  18 - access("EVA_B"."EVA_ID"="RGA"."EVA_ID")

Open in new window

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37007345
what am I supposed to see?

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

Author Comment

by:jairdur
ID: 37007673
For assessing No. We are working on the question
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37007716
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.  
0
 

Author Comment

by:jairdur
ID: 37008763

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

Accepted Solution

by:
sdstuber earned 800 total points
ID: 37009769
is it correct?

is 5 seconds fast enough?  if it is, then stop tuning.  if it's not, what is the target.

what is the impact of the changes I made,  also ,what is the impact of those same changes when made to your last query?
0
 

Author Closing Comment

by:jairdur
ID: 37098932
Thanks
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question