I have two tables in my database. Each table is having 2000 records. As I have a large number of records I would like to write the most optimal code for retrieving records. USER_DETAILS table is:
+---------+-----------+-----------+
| user_id | user_name | join_date |
+---------+-----------+-----------+
The second table which is refering USER_DETAILS table is:
+---------+-----------+-----------+
| user_id | fav_color | fav_dish |
+---------+-----------+-----------+
I have two approaches first one:
SELECT UD.*,FAV.FAV_COLOR, FAV.FAV_DISH FROM USER_DETAILS UD, FAV_DETAILS FAV
WHERE UD.USER_ID = FAV.USER_ID;
Second approach is writing a PL/SQL procedure which is:
DECLARE
CURSOR C1(X NUMBER) IS SELECT * FROM USER_DETAILS WHERE USER_ID = X;
CURSOR C2 IS SELECT * FROM USER_FAV;
Y NUMBER := &USER_ID;
BEGIN
FOR C IN C1(Y)
LOOP
DBMS_OUTPUT.PUT_LINE('USER DETAILS');
DBMS_OUTPUT.PUT_LINE('----------------');
FOR D IN C2
LOOP
IF C.DEPTNO = D.DEPTNO THEN
DBMS_OUTPUT.PUT_LINE(RPAD(C.USER_ID,10)||RPAD(C.USER_NAME,10)||RPAD(D.FAV_COLOR,10));
END IF;
END LOOP;
END LOOP;
END;
Which code will give better performance and why? I want to get the complete details of a user.
If I am using cursor will I get all the records form the server to the SGA? I will use this database in a JSP page which will be accessed by mobile devices only.
As internet in mobile device is very slow of my target users (around 10KB) hence I am concerned about bandwidth. In my point of view I find that performing a join will do a Cartesian product and check for matching result which will take one condition out of 1000*1000 conditions where as the checking conditions in PL/SQL block is only 1000 + 1000. It reduces the number of conditions. But as per my knowledge cursor will create a shadow page in client memory and it will create the table. This means it will fetch all the data form the server and store in client. Am I correct at this point?
You can read here Tom Kyte's mantra:
You should do it in a single SQL statement if at all possible.
If you cannot do it in a single SQL Statement, then do it in PL/SQL.
If you cannot do it in PL/SQL, try a Java Stored Procedure.
If you cannot do it in Java, do it in a C external procedure.
If you cannot do it in a C external routine, you might want to seriously think about why it is you need to do it…
Basically by using a plsql stored procedure you move from sql engine to plsql engine back and forward. More then that, if you have the right indexes and you build your query right, the sql optimizer will probably make things faster than you.