join taking too long

Question

I have a query that takes too long to run and I think is not normal. I am guessing some hints/optimizer would help, but I do not know much about it. So any help would be appreciated.

Basically the code is:

with datasetA as (),
     datasetB as (),
     datasetC as ()
select a.*
from datasetA a
     join datasetB b on b.key = a.key
     join datasetC c on c.key = a.key;

Each of the subquery (datasetA, B, C) takes only 1 second to run, but the join takes 30 minutes.... I am 100% sure it is a 1 to 1 join, and the datasets are small (like 1000 rows) and final output is small too.

first thing to be considered is explain plains to be shared for query performance questions. — Barbaros Özhan, Oct 28 '19 at 20:42
Plz check the datatypes you mentioned for the columns you are joining and make sure they are same. And check the syntax, also as said index.. — sandy v, Oct 28 '19 at 20:57
See [here](https://stackoverflow.com/a/34975420/4808122) how to post the *execution plan* and other things required to be able to assist you. — Marmite Bomber, Oct 28 '19 at 21:49
Please post a query which will actually run, along with full descriptions of all tables involved including all available indexes, row counts for all tables, source code for all functions which are called from your query, and complete query plan. Without information all that anyone can tell you is "add some indexes". Thanks. — Bob Jarvis - Слава Україні, Oct 28 '19 at 22:01
Well, not *all* @BobJarvis, "add some hints" is the next advice;) — Marmite Bomber, Oct 28 '19 at 22:29

score 0 · Answer 1 · answered Oct 28 '19 at 20:30

0

you need to add index on join foreign key column on all tables which will improve performance.

answered Oct 28 '19 at 20:30

Megh

135
2
10

score 0 · Answer 2 · answered Oct 28 '19 at 20:43

0

You are likely missing indexes

Follow the below link and it should help you. It should reduce the total execution time of the query by at least 100x

https://www.w3schools.com/sql/sql_create_index.asp

Also, if you could post results from SHOW INDEX FROM table1, SHOW INDEX from table2 we could identify missing indexes

answered Oct 28 '19 at 20:43

Standin.Wolf

1,224
1
10
32

2

Please note that the query has no `where` predicate and therefore a positive effect of adding an index is highly unlikely. – Marmite Bomber Oct 28 '19 at 21:54

score 0 · Answer 3 · answered Oct 29 '19 at 00:04

Oracle is probably applying a bad optimizer transformation, turning three queries that run fast independently into one query that runs slow. The simplest way to disable those transformations, and ensure that each query runs by itself the fast way, is to add a seemingly worthless ROWNUM condition, like this:

with datasetA as (... where rownum >= 1),
     datasetB as (... where rownum >= 1),
     datasetC as (... where rownum >= 1)
select a.*
from datasetA a
     join datasetB b on b.key = a.key
     join datasetC c on c.key = a.key;

The ROWNUM pseudo-column was intended for top-N reporting, which wouldn't work if Oracle rewrote each query. So the optimizer leaves those subqueries alone, even though the condition is logically redundant.

Above is the easy way to solve the immediate problem, but not necessarily the best way. You might want to investigate why Oracle is choosing bad transformations; bad optimizer statistics are often the culprit. Finding the root cause may help fix other problems, but can be a difficult process.

If you're curious why Oracle is doing this, optimizer transformations are often a good thing. Transformations like view merging and predicate pushing allow Oracle to apply knowledge from one part of the query to another, which often significantly improves the run time. For example, imagine a query like select * from (select * from huge_table) where primary_key = 1;. We'd certainly want that predicate to be pushed into the subquery, so Oracle doesn't have to read the whole table just to get one row.

*three queries that run fast* - most probably they return fast *the first page*... You must have a lot of mess to turn a join of three 1000 rows data sources in a 30 minutes execution. — Marmite Bomber, Oct 29 '19 at 04:48
@MarmiteBomber You might be right. Since OP mentioned the number of rows returned by the data sets I assumed he had measured the full time. Hopefully they'll update the question with details. — Jon Heller, Oct 29 '19 at 05:50

join taking too long

3 Answers3