Performance question: returning joined tables as one resultset vs. returning multiple resultsets

Hello all:

If I have two tables that I need to return to a client application (in my case, it's a java sevlet)

create table notes (
primarykey number,
comments varchar2(4000)
)

create table person (
primarykey number,
name varchar2(50)
)

Notes is the parent table and person is the child table. Each note will have 1 or more people related to it.

Option#1: return 1 resultset

select p.name, n.comments from notes n, person p where n.primarykey = 10001 and n.primarykey = 10001  

Option#2: return 2 resultsets

select n.comment from notes where primarykey = 10001 select p.name from person where primarykey = 10001

The question is, which one would give me better performance option#1 or option#2? I would think that option#2 would be be better, because if a note has many people (e.g., 20), then option#1 will return 80,000 bytes (20 comments of 4,000 bytes each). But option#2 would only return 4,000 bytes.

This is really a general question about how Oracle works. If the answer is option#2, is that always the best solution? Or is it better sometimes option#1? And what about if I'm joining 3 or 5 tables. Does that change anything? What if the query is very complex (e.g., a lot of where conditions)

Thank you in advance for answering my question. Received on Thu Aug 23 2001 - 03:18:44 CEST