| Joining with huge tables? [message #303242] |
Thu, 28 February 2008 09:04  |
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 #303261 is a reply to message #303247] |
Thu, 28 February 2008 09:49  |
skooman
Messages: 913
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 |
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 #303315 is a reply to message #303293] |
Thu, 28 February 2008 20:59 |
rleishman
Messages: 3728
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 |
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 |
rleishman
Messages: 3728
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 |
 |
Littlefoot
Messages: 21823
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).
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
