retrive data from 3 tables

610 pts.
Tags:
Oracle
SQL
TOAD
HI i need to retrieve data of 3 tables using one more master table wat are the possible options Union,Joins anything else? with good performance please guide for considering the performance as the retrival count is huge in my case

Software/Hardware used:
TOAD,Oracle
ASKED: May 18, 2010  8:31 AM
UPDATED: May 21, 2010  4:33 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

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

Discuss This Question: 10  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Meandyou
    Without more information there can be no answer.
    5,220 pointsBadges:
    report
  • Kccrosser
    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.
    3,830 pointsBadges:
    report
  • Inprise
    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
    610 pointsBadges:
    report
  • Inprise
    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
    610 pointsBadges:
    report
  • Meandyou
    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
    				
    5,220 pointsBadges:
    report
  • Kccrosser
    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.
    3,830 pointsBadges:
    report
  • Inprise
    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
    610 pointsBadges:
    report
  • Kccrosser
    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.
    3,830 pointsBadges:
    report
  • Inprise
    Index is present in all tables on id column
    610 pointsBadges:
    report
  • Kccrosser
    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.
    3,830 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following