Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2002

    Question 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 a.col1,
    (select b.col2 FROM table2 b WHERE b.col1 = a.col1) AS col2,
    (select ...) AS ...,
    FROM table1 a

  2. #2
    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. : )


  3. #3
    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
    SIPTI srl
    OnSite Services
    9i OCP DBA, Performance Engineer

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts