Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2008
    Posts
    8

    Unanswered: Double join solutions?

    Hello!

    I have 2 tables, "locations" and "travels"
    ----------
    locations
    location_id, subdivision, city, region, country, continent
    ----------
    travels
    date, src_location_id, dst_location_id, duration
    ----------

    I want to display statistics based on location, something like:
    "travels to mount everest by country" or "most popular summer vacation cities by country"

    my program assembles an SQL statement that looks like this:

    SELECT loc1.city, loc2.region, COUNT(*), SUM(t.duration)
    FROM travels t
    LEFT OUTER JOIN locations loc1 ON t.src_location_id = loc1.location_ID
    LEFT OUTER JOIN locations loc2 ON t.dst_location_id = loc2.location_ID
    /*WHERE date = xxx, etc etc*/
    GROUP BY loc1.city, loc2.region

    The location table contains thousands of locations, and compounded with tens of thousands of travel data, it's just too slow.

    I tried to create a materialized view that appended src_city, dst_city, src_region, dst_region, etc... to the travels table,
    but it seems that I can't perform fast refreshes on joined tables.

    Any suggestions?
    Thanks for your time!

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    "Thousands" and "tens of thousands" are small tables.

    Did you create indexes on relevant columns? What does EXPLAIN PLAN say?

Posting Permissions

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