Home » SQL & PL/SQL » SQL & PL/SQL » Joining with huge tables?
Joining with huge tables? [message #303242] Thu, 28 February 2008 09:04 Go to next message
bambi
Messages: 8
Registered: February 2008
Location: Köpenhamn
Junior Member
I have a table (actually the result of a view) containing some 25.000 records. This table is first (inner) joined with a huge table containing billions of records, and then with another huge table about the same size. None of these tables are indexed or analyzed, and the query is running out of temp table space after about 90 minutes. However, joining with only one of the huge tables gives the result in about half an hour.

How do I make absolutely sure the tables are joined in the right order, and not left for the optimizer to decide?

And if I use a view twice in a query (using an alias), will it only get computed only once?
Re: Joining with huge tables? [message #303247 is a reply to message #303242] Thu, 28 February 2008 09:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why not gathering statistics?
Why not posting the query?
Why not indexing the join columns?

Regards
Michel
Re: Joining with huge tables? [message #303261 is a reply to message #303247] Thu, 28 February 2008 09:49 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Quote:
How do I make absolutely sure the tables are joined in the right order, and not left for the optimizer to decide?

Well, if you have no statistics and no indexes, you leave the optimizer very little to decide anyway. The only option it has is to do full table scans and join in random order, since you refuse to give it any information on what it's doing or how to do it....
Given your post, I would suggest to create indexes, gather statistics and trust the optimizer to do a better job then you can.
Re: Joining with huge tables? [message #303292 is a reply to message #303261] Thu, 28 February 2008 12:50 Go to previous messageGo to next message
bambi
Messages: 8
Registered: February 2008
Location: Köpenhamn
Junior Member
skooman wrote on Thu, 28 February 2008 16:49
Quote:
How do I make absolutely sure the tables are joined in the right order, and not left for the optimizer to decide?

Well, if you have no statistics and no indexes, you leave the optimizer very little to decide anyway. The only option it has is to do full table scans and join in random order, since you refuse to give it any information on what it's doing or how to do it....
Given your post, I would suggest to create indexes, gather statistics and trust the optimizer to do a better job then you can.

I only have very basic rights on this database. I'm only able to read the tables - not to modify them in any way, including make indexes or analyze them.

They are archive tables containing data from the past 20 years and rarely have to be accessed, so the administrator has probably decided not to use resources on analyzing them.

However, I can create all the queries and views I want, and since the resulting table/view would be around 20-25,000 records I figured that it should be quite possible to run this query if I avoid joining the two huge tables.
Re: Joining with huge tables? [message #303293 is a reply to message #303292] Thu, 28 February 2008 12:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
They are archive tables containing data from the past 20 years and rarely have to be accessed, so the administrator has probably decided not to use resources on analyzing them.

This is the better case. It just have to gather statistics once and it's over.
Same thing for indexes. It just to create them once and then forget them.

Depending on your queries (you still don't show us) you may create materialized view(s).

Regards
Michel
Re: Joining with huge tables? [message #303315 is a reply to message #303293] Thu, 28 February 2008 20:59 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
If the tables are not indexed, you CANNOT use a nested loops join.

This means you MUST use either HASH join or SORT MERGE join.

A SORT-MERGE join will sort large tables using temporary segments. This is unavoidable. If your temp segments cannot handle BOTH of the large tables - it wont work.

A HASH join must create a hash table on one of the tables in a two table join - the smaller one. The 2nd table can then be joined without using Temp Segments (assuming the smaller table was small enough to be hashed in memory).

This gives a LARGE result set that must be joined to the 3rd (large) table. Joining 2 large row sets with a hash join WILL use Temp Segments. The overflow from the smaller one will spill to Temp, and the matching rows from the larger one will also spill to Temp. This is unavoidable.

You cannot join two large unindexed unpartitioned tables without using Temp segments. It's just not possible.

Your choices are:
- Index the tables. Then you can use Nested Loops. The SQL will be godawful slow, but it will finish. Eventually.
- Allocate more temp space. Hash join will work, and will be reasonably efficient.
- Partition the large tables on their join-key, ensuring that each partition is small enough to be hashed in memory. No Temp space will be required and the join will be super-fast.


Ross Leishman
Re: Joining with huge tables? [message #306028 is a reply to message #303315] Wed, 12 March 2008 10:44 Go to previous messageGo to next message
bambi
Messages: 8
Registered: February 2008
Location: Köpenhamn
Junior Member
I hoped I would be able to (inner) join the reasonably small result of my view with the first huge table. Again, the result would be of reasonable size which could be (inner) joined with the second huge table.

Joining the view with the first huge table is not a problem, so I hoped I could force Oracle to join the tables in a specific order using a directive.

The thing is: I don't have access to modify any existing tables, so I can't index the tables or allocate additional temp space.

Since neither the view nor the two tables are indexed, Oracle probably decides to join the two tables...

Could the use of temporary tables (rather than views) be a solution? Like first outputting basic data to TBL_1, then joining TBL_1 with HUGETBL_1 in TBL_2, and finally joining TBL_2 with HUGETBL_2 in TMPTBL_3.
Re: Joining with huge tables? [message #306136 is a reply to message #306028] Thu, 13 March 2008 01:00 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
bambi wrote on Thu, 13 March 2008 02:44
I hoped I would be able to (inner) join the reasonably small result of my view with the first huge table. Again, the result would be of reasonable size which could be (inner) joined with the second huge table.


Why would the join between a small table and a large table give a small result? It may give a huge result as big or bigger than the huge table.

Temporary tables wont help. They use temp space - same as joins. You would have to use real heap-organised tables that you create and then perhaps drop afterwards. This will still need space - I don't see why the DBA would give you this space but not TEMP space.

Ross Leishman
Re: Joining with huge tables? [message #306156 is a reply to message #306028] Thu, 13 March 2008 01:28 Go to previous message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
bambi
I don't have access to modify any existing tables, so I can't index the tables or allocate additional temp space.

But you *could* ask your DBA to do that for you (i.e. analyze tables, create indexes, perhaps allocate more temporary space). She or he doesn't have to respond positively (at first), but you may try, and later convince her/him to do that (otherwise, you can't finish your job and then it is her/his responsibility, not yours).
Previous Topic: How to store and retrieve audio files from the database
Next Topic: Update Statement Using Join
Goto Forum:
  


Current Time: Thu Dec 08 10:44:54 CST 2016

Total time taken to generate the page: 0.14173 seconds