CODEWITHSUNDEEP
sqlperformanceoracle-databaseoptimization
Stefanos Kargas
Stefanos Kargas

Reputation: 11093

SQL statement strangely slow

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 searching for some data from 2 different tables. (Oracle database - wherefields indexed for both tables) The above query is taking 500ms to be executed. When I search the tables seperately for the same fields they finish in less than 20ms each.

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

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

EDIT: Adding oracle's explain 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

Upvotes: 3

Views: 324

Answers (5)

Erich Kitzmueller
Erich Kitzmueller

Reputation: 36987

You should probably analyze the tables... the full table access and the hash join make no sense in your case.

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

Upvotes: 1

MatBailie
MatBailie

Reputation: 86798

I can't see anything wrong with your query, and the (good) advice given in other answers will help you understand what is going on in detail.

In concept, however, you have to bear in mind the order of the data in each table, and whether the data is grouped up or spread out. It's all well saying "it only takes 20ms to find", but how long will it take to match the two data sets?

If both data sets are Known to be in the same order, the RDBMS can align them relatively quickly. But the RDBMS can only know this from an index.

If you have an index on Table1 that is Field8 then Field6, all the "values" will be lumped together, then ordered by Field6. If, however, you have an index of Field6 then Field8, the records you're interested in will be ordered but spread out through the index. Finally, if you don't have indexes on these fields, everything will be randomly ordered and spread out.

Depending on these types of factor, there are literally dozens of ways the RDBMS may complete your query. For best performance, one needs to understand what the RDBMS would need to do, then give it the index(es) to make it as easy as possible.

Upvotes: 1

tbone
tbone

Reputation: 15493

Slower than what? RDBMS was made for joining. If you think you'd get better response times from doing it procedurally (row by row using a cursor for loop or similar) you'd be wrong 99.9% of the time.

My guess is you are comparing the response times from a join that returns all rows (or even the first 500 or so rows if using FIRST_ROWS) vs a handful of records returned procedurally (or manually). Apples and oranges.

Upvotes: 2

René Nyffenegger
René Nyffenegger

Reputation: 40603

If there are 25 records in TABLE1 that satisfy field8='value' and if it takes 20ms to select ... from table2 where field6=??? then 500ms is in the realm of expected time.

So, it's quite meaningles to say each query takes 20ms, you'd also have to state how many records statisfy the field8 condition in TABLE1 and how many records on average satisfy a condition on TABLE2.FIELD6.

But to eliminate all guessing you should have Oracle explain the query and display (or post) the explained plan here for further analysis.

EDIT: Since there seems to be a 1:1 relationship between the criterias (and the query subsequently returns 1 record), 500ms is not expected. In this case I'd really stress the need for having the query explained. In case you're not familiar with it, you can do that like so:

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

select * from table(dbms_xplan.display);

And then post the result. This will allow us to help you better.

Upvotes: 2

Benoit
Benoit

Reputation: 79233

You should know what the query plan is, using either a reporting tool or hints like /*+ gather_plan_statistics */. Lookup this information on a search engine.

When the optimizer joins two tables it may use cartesian product, sort-merge join, hash join… Try SELECT /*+ USE_HASH(table1 table2) */ ….

Also, if the optimizer chooses a wrong plan, you might like to recompute statistics if they are non-representative using the DBMS_STATS.GATHER_SCHEMA_STATS procedure for example. This is the main source of bad optimizer choices.

Upvotes: 1

Related Questions