Ok - I have got quite a comprehensive background with SQL Server, but I have only piddled around in Oracle before. Well, push originates to shove, and I have to produce a relatively complex query. Basically, it boils lower for this in T-SQL:
SELECT Col1 INTO #tmpTable FROM Table1 WHERE Col3 = @paramValue SELECT Col1 FROM #tmpTable SELECT OtherCol FROM Table2 INNER JOIN #tmpTable ON Table1.Col1 = Table2.fkCol1
The reasoning with this sequence happens because the first call to Table1 is very heavy (~5s runtime) since it is a very complex call against our data warehouse. I must return the outcomes from Table2 inside a separate call because you will find apt to be 5-10 records in Table2 for each one out of Table1 also it makes my front-finish pivoting simpler.
I am aware will be able to do
SELECT Table1.Col1, Table2.OtherCol FROM Table1 LEFT JOIN Table2 ON Table1.Col1 = Table2.fkCol1
after which re-normalize the information within the front-end (processing just the first record for Col1, then all the OtherCol records until I uncover a brand new Col1)
I am not really a DB expert, though, so I am unsure the better approach. From the dev stance, the very first option would be simpler that i can consume. Additionally, it (stomach feeling) looks more performant, as you will not need to return the "thick" outcomes of Table1 combined to Table2. Table1 is going to be coming back ~1200 rows and it is ~2kb wide. Table2 is considerably slimmer (~20 bytes wide) but has more rows (6000-12000).
So, ultimately my real question is, which solution is the greatest one for any PL/SQL atmosphere, and when it's the first, what's the easiest way of carrying out it? Global Temporary Table / Cursor / sub choose / what?
I'd make use of the join. It's simpler to code and browse and really should be faster than three separate chooses. And when you simply choose
Col1 it does not matter how "thick" an entire row is.
If you're concerned about performance, you need to just test drive it and compare the outcomes. I would opt for the join solution. Oracle is going to do its factor behind the curtain and most likely enhance your performance.
It's also wise to browse the Execution Plans for that queries. I am certain your development atmosphere enables you to view the execution plan rapidly. The price of your queries provides you with advisable of what is happening, and can help you decide.
In reponse for your comment about being unsure of how you can implement the very first solution:
procedure get_data(p_paramValue in varchar2, c_data1 out sys_refcursor, c_data2 out sys_refcursor) is v_tmptable in varchar2(30); begin SELECT Col1 INTO v_tmpTable FROM Table1 WHERE Col3 = p_paramValue; open c_data1 for 'SELECT Col1 FROM '||v_temptable; open c_data2 for 'SELECT OtherCol FROM Table2 INNER JOIN '||v_tmpTable|| ' ON Table1.Col1 = Table2.fkCol1'; end get_data;
Presuming data in Table1.Col1 is reliable, since there's a shot vulnerability both in cursors.