New to PL/SQL - Returning multiple result sets question

Ok - I've got quite an extensive background with SQL Server, but I've only piddled around in Oracle before. Well, push has come to shove, and I need to create a relatively complex query. Essentially, it boils down to 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 for this sequence is because the initial call to Table1 is extremely heavy (~5s runtime) because it is a highly complex call against our data warehouse. I would like to return the results from Table2 in a separate call because there are likely to be 5-10 records in Table2 for every one in Table1 and it makes my front-end pivoting easier.

I'm aware that I can do

SELECT Table1.Col1, Table2.OtherCol
FROM Table1
LEFT JOIN Table2 
  ON Table1.Col1 = Table2.fkCol1

and then re-normalize the data in the front end (processing only the first record for Col1, then all of the OtherCol records until I discover a new Col1)

I'm not a DB ex开发者_JS百科pert, though, so I'm not sure which is the better approach. From a dev stance, the first solution is easier for me to consume. It also (gut feeling) looks more performant, as one won't have to return the "thick" results of Table1 coupled to Table2. Table1 will be returning ~1200 rows and is ~2kb wide. Table2 is significantly leaner (~20 bytes wide) but has more rows (6000-12000).

So, ultimately my question is, which solution is the best one for a PL/SQL environment, and if it's the first one, what's the best way of going about it? Global Temporary Table / Cursor / sub select / what?

I'd use the join. It's easier to code and read and should be faster than three separate selects. And if you just select Col1 it doesn't matter how "thick" a complete row is.

If you are worried about performance, you should just test it and compare the results. I'd personally go with the join solution. Oracle will do its thing behind the scenes and probably improve your performance.

You should also check out the Execution Plans for the queries. I'm sure that your development environment allows you to see the execution plan quickly. The cost of your queries will give you a good idea of what's happening, and will help you decide.

In reponse to your comment about not knowing how to implement the 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;

Assuming data in Table1.Col1 is trusted, since there is an injection vulnerability in both cursors.