Designing a multiple Join query

[s khosa]

Hi,
Just wanted to have some opinion on the fact that what will be the best way to design a query that is joining around five tables with around 80,000 rows and returns a resultset of around 170 thousand rows. I have ruled out outer joins as they are too slow.
The problem is that when i run the query on SQL prompt on oracle server, its returns results pretty fast. But running it through JDBC on middle tier makes it slow. The query is:
SELECT REL.RL_ID, REL.CONTENT_ID, REL.WEIGHT, REL.PASS, DC.DC_ID, DC.DC_DISPLAYNAME, MAP.MP_ID, MAP.MP_DISPLAYNAME, MAP.MP_NODECOUNT, MAP.FLASHMAP_RELPATH, MAP.MP_RELEVANCE, NODE.ND_ID, NODE.ND_DISPLAYNAME, NODE.ND_RELEVANCE, ARTICLE.ART_ID, ARTICLE.ART_URL, ARTICLE.ART_TITLE, CHANNEL.CH_ID, CHANNEL.CH_NAME, CHANNEL.IMAGE_PATH, CHANNEL.DESCRIPTION, CHANNEL.CATEGORY, CHANNEL.MORELINK_TEXT, MISC_CHANNEL.HOSPITAL_ID, MISC_CHANNEL.URL_KEYWORDS, MISC_CHANNEL.DESCRIPTION AS MISC_DESCRIPTION, MISC_CHANNEL.RESULTS, MISC_CHANNEL.SEARCH_RESULTS, MISC_CHANNEL.RELATION, MISC_CHANNEL.XML_FILE_PATH FROM REL, DC, MAP, ARTICLE, CHANNEL, NODE, MISC_CHANNEL WHERE CHANNEL.CH_ID <> 4 AND ARTICLE.ART_ID = REL.ART_ID AND ARTICLE.CH_ID = CHANNEL.CH_ID AND REL.ND_ID = NODE.ND_ID AND MAP.MP_ID = NODE.MP_ID AND MAP.DC_ID = DC.DC_ID AND NODE.ACTIVATION_STATUS > 0 AND NODE.CODING_STATUS > 0 AND ARTICLE.ART_ID = MISC_CHANNEL.ART_ID(+)
Any criticism in the query design is welcome. I dont want to use a view of all these tables joined offline and run my query off this unnormalised view.
Thanks,
Sumeer.

[Jeanne Boyarsky]

You could probably make the actual database processing time faster by rearranging you where clause. It is generally better to put the joins last (unless your status codes are almost always positive numbers.) Also, you can look into adding appropriate indexes to make the query faster. The majority of your problem is not coming from the database execution time if it performs at an acceptable speed from the command line. But every little bit helps!
If the query runs at an acceptable speed at the command line, the problem is due to the amount of data you are sending across the network. Why do you need so much data in your java program? 170,000 rows * 30 columns is more than a million data fields! I suspect you don't really need the id of the table as it is for the database. To sum it up, make sure you are using every single field you are returning.

[s khosa]

I am trying to write a search engine. For the user to be able search across entire database, i need create search document indexes of all the five tables. I agree that putting any kind of filters before hand in 'where' clause does help and so does having indexes on columns being fetched.