Without more information there can be no answer.

You need to give us a little more to go on:

How big are the tables? (# rows, # columns)

What percent of the contents of the tables do you expect to retrieve for a typical query? (<1 %, 5%, 50%?)

Is this for a report, or is this for interactive presentation to a user? (If the latter, the result should normally be presented within 6 seconds to avoid users getting irritated waiting for results. If the former, and you are looking at retrieving a relatively high percentage of the table contents, you may want to consider some of the Oracle “bulk” transactions methods.)

Are these disjoint types of data, or are the secondary tables just additional attributes for the primary table data? (e.g., is this something like “select … from person join name join address join phone …” (the secondary tables contain additional information about the records in the primary table), or is this something like “select … from person union all select .. from company …” (the secondary tables contain different types of data that you want to combine for statistical or other purposes)?)

Generally, if you are simply collecting additional information from “child” tables, simple JOINs will suffice and probably be as efficient as anything else (assuming proper keys and indexes). If you are trying to combine data from different types of data structures, then UNION is usually better (but not always).

Have you tried coding a query and doing an “explain plan” on it? If you could post the results of the plan, that makes it easier to give suggestions as to how to tune your queries.

Thanks for the reply
yes this is for report
I can explain my requirement
we have a master table with id as pkey and this is available in all 3 other tables which are referenced
Current query is just select statement with join btn master and 1st reference table
select statement with join btn master and 2ndreference table
select statement with join btn master and 3rd reference table

this we would like to club together and run however Union we are not able to use as it s not supported with Hibernate we use
So we need some other alter solution with performance

And the data is more fall under 50% plus
simple join of all 4 tables with this id field retrieve arnd 1.8 lac record in 4 mins time
so we need better logic to improve the performance
yes its disjoint set of data

you have

select statement with join btn master and 2ndreference table
select statement with join btn master and 3rd reference table

how about 
 

select statement with join between MASTER and 2nd reference table
and between MASTER and 3rd reference table

We still need a bit more information here.

Is the query relatively static? Could you define Views for the three queries and then do a select across the three views?

Create View1 as …
Create View2 as …
Create View3 as …

select … from View1
join View2 on View2.id = View1.id
join View3 on View3.id = View1.id

If you could describe the contents of the tables (column names, perhaps?) and the general syntax of the queries, that would be very helpful.

SELECT e.smsg,r.rid as drid,r.vrid as cid ,r.pid tid,e.qid FROM tv e , vry r WHERE (e.id = r.id) AND (e.qid = r.qid)

SELECT e.smsg,s.rid as drid,s.vrid as cid ,s.pid as tid,e.qid FROM tv e , sry s WHERE (e.id = s.id) AND (e.qid = s.qid)

SELECT e.smsg,d.rid as drid,d.vrid as cid ,d.pid as tid,e.qid fROM tv e ,dry d WHERE (e.id = d.id) AND (e.qid = d.qid)

these are my actually queries each table has many id where id is the primary key of table e(master) and present in all otehr tables and one more column available everywhere is qid

usually this 3 commands continues one after other,we just want single query to do the same work with better performance

Are you using covering indexes? If not, that would probably dramatically improve performance.

For example, for your query:
SELECT e.smsg,r.rid as drid,r.vrid as cid ,r.pid tid,e.qid FROM tv e , vry r WHERE (e.id = r.id) AND (e.qid = r.qid)

You should have an index defined as:
create (unique, if possible) index myNewIndex on tv(id, qid);

If you are using separate indexes on tv.id and tv.qid, using a single combined index may be WAY faster. If you can make that index unique (by extending it with the TV primary key, for example), that can often help as well, like:

create unique index myNewUniqueIndex on tv(id, qid, pkcol1, pkcol2 …)

Note – if the values in qid are more discriminant than the values in id, then the index should have qid first, as in:

create unique index .. on tv(qid, id, …)

Covering indexes are often overlooked, but can make big differences in performance.

Before you try to combine the three queries, I would make sure the individual ones are well-tuned.

Index is present in all tables on id column

I am *assuming* that since you are joining on two columns (id and qid), that the values in the ID column are not unique.
Therefore, if you do not have an index on the QID column as well, and you are retrieving many rows, the query engine may wind up doing table scans.

Creating a covering index on both columns may produce a huge performance increase.

Obviously, you want to create these covering indexes on ALL the tables, and you will probably see these go way faster.

Assuming you have a primary key column on each of the tables (call it “pkid”), try the following:

create unique index uix_tv_id_qid on tv(id, qid, pkid);
create unique index uix_vry_id_qid on vry(id, qid, pkid);
create unique index uix_sry_id_qid on sry(id, qid, pkid);
create unique index uix_dry_id_qid on dry(id, qid, pkid);

With these indexes in place, you might see your queries go faster by orders of magnitude…

Note – since I don’t know what version of Oracle you are running, or how you have it tuned, you may want/need to analyze the table/indexes to regenerate the statistics:

analyze table tv compute statistics;
analyze table vry compute statistics;
analyze table sry compute statistics;
analyze table dry compute statistics;

If you are on a reasonably recent version of Oracle, this shouldn’t be necessary, but up through Oracle 9i, analyzing the tables after changing indexes was necessary for the query optimizer to properly decide which indexes to use.