08-02-02, 12:44 #1Registered User
- Join Date
- Aug 2002
Unanswered: Making large join queries run fast
I'm trying to join about 4-7 large tables, each containing between 200,000 and 6,000,000 rows. I'm doing inner joins and it's taking more than 3 minutes to return the reusults. I've also tried doing sub-queries instead, which runs faster but still takes over 1 minute. I've put indexes on the join fields but that doesn't help much either.
Can someone please give me some tips or ideas as to how to get these joins to run faster?
Here's what I've tried (inner join):
select a.col1, b.col2, c.col3, ...
FROM table1 a, table2 b, table3 c, ...
WHERE a.col1=b.col1 AND b.col2 = c.col2 AND ...
(select b.col2 FROM table2 b WHERE b.col1 = a.col1) AS col2,
(select ...) AS ...,
FROM table1 a
08-02-02, 13:57 #2Registered User
- Join Date
- Mar 2002
- Reading, UK
Assuming all the columns have already been correctly indexed and all tables and indexes have been analyzed recently.
1. Look at using hints to ensure it is joining the tables in the right order (see the ORDERED hint) . i.e. it should ensure the table which has the most restrictive criteria should be the first table used in the execution plan.
2. If the resultset is very large make sure you are using a large rollback segment with large extents. Also make sure there are enough rollback segments so you dont have to wait to acquire one.
3. if the query is ordered, then sorting will be quicker if the sort area is in memory and doesnt go out to disk.
4. There are parameters to tune the optimiser (see alter session) to go more for indexes or for full table scans. NOTE using an index is NOT always the fastest way to go especially if you have to join all the records in a table.
5. Is it possible to use summarising of the data to speed things up.
6. Find a quiet time to run the query or get a bigger box : )
There are lots of other things you can also look at like clustered tables, index organised tables etc. : )
09-17-02, 10:44 #3Registered User
- Join Date
- Sep 2002
Re: Making large join queries run fast
Have you tested the hash join method?
Allocate a large hash area to that session,
put a hash_multiblock quite high (16, 32),
pay attention to next extent size of temporary tablespace.
good luck.Franco Ceotto
9i OCP DBA, Performance Engineer