How to make my query more efficient?

Question

I am running a query in an Oracle SQL environment using Toad where I build a table by joining 16 different tables and I am running out of temp tablespace and was hoping I could get some advice on how to make my query more efficient. I don't have a background in this so I am not sure if the best way is to use intermediary tables or change the order of my joins.

There are two main tables, Header and Detail, the header has 26 million rows, the detail has 175 million rows. I use an inner join with these tables which will result in 175 million rows. The other 14 tables are smaller description tables that I join using left joins. Three of those tables are 350k rows and less, the other 11 are under 1,000 rows. My current pseudo code is as follows:

    create table END_TABLE as
    select *
    from Detail
    inner join Header
    left join description_table_1
    left join description_table_2
    left join description_table_3
    left join description_table_4
    left join description_table_5
    left join description_table_6
    left join description_table_7
    left join description_table_8
    left join description_table_9
    left join description_table_10
    left join description_table_11
    left join description_table_12
    left join description_table_13
    left join description_table_14;

Since I am starting with my detail table and then joining the header is that less efficient than if I did it the other way around? I assumed since it was an inner join it wouldn't matter but like I said before I am not very knowledgeable on making queries more efficient.

My idea is to create a separate table joining the header and detail together and then create a final table where I join the smaller detail tables. Would this help? Would changing my join order help?

http://stackoverflow.com/questions/9614922/does-the-join-order-matters-in-sql — Siyual, May 29 '14 at 21:04
You're going to need an ON or USING clause on each of those joins. Do you have a WHERE clause on this that might restrict the number of rows? I doubt very much that putting header before detail or detail before header will matter. Best of luck. — Bob Jarvis - Слава Україні, May 29 '14 at 21:17
I am aware I will need an ON for the joins, I didn't see how adding the keys would help the pseudo code though, they cannot be changed. there is no WHERE criteria. — user1723699, May 29 '14 at 21:32

score 1 · Answer 1 · answered May 30 '14 at 00:33

1

You are joining a table with 175 million rows against another table with 26 million rows using with no indices and no where clause.

You need indices. Without them, you're just shuffling around the deck chairs on the titanic.

answered May 30 '14 at 00:33

Aheho

12,622
13
54
83

Not always, it depends on the context. There are many systems that join billions of rows and work best without indexes. – Jon Heller May 30 '14 at 05:13
@JonEarles - Could you provide an example of a scenario where performance is better without indices? There are times when a where clause is best handled via a table scan, but I've never seen a real world instance where joins were better handled without indices. – Aheho May 30 '14 at 13:34
Here's a simple example: 1. `create table t as select * from dba_objects;` 2. `create index i on t(object_id);` 3. `select /*+ index(t1) index(t2) */ distinct t1.owner,t2.owner from t t1 join t t2 using (object_id);` 4. `alter system flush buffer_cache;` 5. `select /*+ no_index(t1) no_index(t2) */ distinct t1.owner,t2.owner from t t1 join t t2 using (object_id);` Indexes work best when they contain all the queried data or when they are used to retrieve only a small percentage of the data. That covers almost all OLTP queries but does not apply to many data warehouse queries. – Jon Heller Jun 03 '14 at 03:38

score 1 · Answer 2 · answered May 30 '14 at 01:38

Indices most likely will not help:

When you joining two tables entirely, Oracle most likely will do FULL TABLE SCAN of both tables, use HASH JOIN, and will not consider using indices.

You can try to partition Header and Detail tables and then join one partition at a time in the loop and insert results into END_TABLE table.

score 0 · Answer 3 · answered May 30 '14 at 00:25

In my experience on Oracle 11g, when I was dealing with many joins of large tables, I found it often sped things up dramatically to use intermediate tables with fewer joins in each step (in one case what took 7 hours in one step took ~20 minutes in multiple steps). Probably I would join Detail and Header into one table, then left join this intermediate table against 1-3 description tables at a time, then start combining those further intermediate tables. You say you don't have indexes: I would expect indexes to speed the joins even if you aren't excluding any data in where constraints. Maybe you can't have indexes on the primary tables but you may be able to for the intermediate tables in case you go this path. Keep in mind that you need to think about whether you're maintaining one transaction throughout the series of queries in case the data are changing.

Intermediate tables *may* help but creating intermediate result sets is something the optimizer should do for you transparently. It would be better to figure out why Oracle is not building those intermediate results sets correctly. — Jon Heller, May 30 '14 at 05:08

score 0 · Answer 4 · answered May 30 '14 at 01:56

if your maximum amount of data is about 100% of data in the table (and based on your description it is) then you don't need indexes -FTS would work just fine.
I am not a big fan of ANSI SQL and prefer to use "right tool for the right task" - and using Oracle SQL syntax and NOT ANSI, but it seems that you are not specifying columns for joins. From my recollection you don't need columns only when you use NATURAL JOIN - WIKI. All other cases of JOIN need definition of which columns are participants.
Columns specifications also relevant for LEFT JOIN (LEFT OUTER JOIN) - again

How to make my query more efficient?

4 Answers4