SQL statement is weirdly slow

Question

select TABLE1.FIELD1, 
       TABLE1.FIELD2, 
       TABLE1.FIELD3, 
       TABLE1.FIELD4, 
       TABLE1.FIELD5, 
       TABLE2.FIELD6, 
       TABLE2.FIELD7
  from TABLE1,
       TABLE2
 where TABLE1.FIELD8 = 'value' 
   and TABLE2.FIELD6 = TABLE1.FIELD6;

I am looking for some data from two different tables. (Oracle database - fields indexed for both tables) The above query takes 500 ms to complete. When I look at the tables separately for the same fields, they end in less than 20 meters each.

I could look up TABLE1 for the data I need (+ FIELD6) and then find TABLE2 for the rest using FIELD6.

My question is. Why is it so slow when I join tables. Am I doing something wrong?

EDIT: adding oracle explanation plan

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
| Id  | Operation                    |  Name                   | Rows  | Bytes | Cost  |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                         |  6318 |   586K|   620 |
|   1 |  HASH JOIN                   |                         |  6318 |   586K|   620 |
|   2 |   TABLE ACCESS BY INDEX ROWID| TABLE1                  |  6318 |   450K|     2 |
|   3 |    INDEX RANGE SCAN          | INDEX_TABLE1_FIELD8     |  2527 |       |     1 |
|   4 |   TABLE ACCESS FULL          | TABLE2                  |   430K|  9242K|   508 |
----------------------------------------------------------------------------------------

Note: cpu costing is off, 'PLAN_TABLE' is old version

Answer 1

I do not see anything wrong with your request, and the (good) advice provided in the other answers will help you understand what is going on in detail.

, , . , " 20 , ", ?

, . .

1, 8, 6, "" , 6. , , Field6, Field8, , , , . , , .

, RDBMS . , , (), .

Answer 2

1 25 , field8='value', 20 select ... from table2 where field6=???, 500 .

, , 20 , , field8 TABLE1 TABLE2.FIELD6.

, , Oracle ( ) .

EDIT: 1:1 ( 1 ), 500 . . , :

explain plan for
   select .... <your entire select statement goes here>
;

select * from table(dbms_xplan.display);

. .

Answer 3

? . , ( ), 99,9% .

: , ( 500 FIRST_ROWS) , ( ). .

Answer 4

, , /*+ gather_plan_statistics */. .

, , -, -... Try SELECT /*+ USE_HASH(table1 table2) */ ….

, , , , , DBMS_STATS.GATHER_SCHEMA_STATS. .

Answer 5

, ... - .

begin
  dbms_stats.gather_table_stats('YOURUSERNAME', 'TABLE1');
  dbms_stats.gather_table_stats('YOURUSERNAME', 'TABLE2');
end;