which way of querying is better

[amolik]

Hi All

I have a 2 tables which related to each other
Nemaster(primary key is Oid) and
Elements (Neid reference Oid)

Now I have to run following query

A) SELECT IPADDRESS FROM ELEMENTS WHERE NEID = (SELECT OID FROM NEMASTER WHERE EID = &ID);

I can run the above query as below

B) SELECT IPADDRESS FROM NEMASTER A, ELEMENTS B
WHERE A.OID = B.NEID AND A.EID = @ID;

If my Nemaster table is going to be bulkier and if I am using above queries in FUNCTIONS then how I should call the query. By method A or B.

Amol

[anandkl]

I guess it would be best to use the first query since the subquery will be exeucted very fast as the primary key is indexec and u r passing a hard code value and only one table is scanned.And the main query will also be executed very fast since it has the required matching values in the where condition.

hope this helps


regards
anandkl

[SANJAY_G]

Compare the execution plan and cost for both the statements.

One more thing, in your first query the subquery should only return one row otherwise the statment will fail.

Sanjay

[gpsingh]

It should not matter , as this kind of subquery shall be transformed into a join by Oracle, so should concentrate on tuning second query by creating an index on EID and OID.