0

I have a SELECT which is quite slow and I think it'll be fast when using two SELECT instead of a subselect or join.

Basically I need to select some numbers from a table and use them in the where-statement of another SELECT.

At the moment I have working:

DECLARE
  v_skus VARCHAR2(255);
BEGIN
  SELECT LISTAGG(num,',') WITHIN GROUP (ORDER BY num) INTO v_skus FROM VIEWNAME WHERE parent IN ('1000');
  DBMS_OUTPUT.PUT_LINE('v_skus=' || v_skus);
END;

That would give me a '1001,1002' in the variable.

I want to use this now in another SELECT like

... WHERE number IN (v_skus)

If I put the second SELECT in the BEGIN block I get the error "INTO clause is expected in this SELECT statement".

So do I have to use another variable for the actual result, and how do I manage to get the actual tablelike result like a plain SELECT would do?

Is my approach wrong?

New contributor
Angel_de is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • I am pretty sure that two statements won't make this faster – a_horse_with_no_name Nov 8 at 13:29
  • @a_horse_with_no_name i'm afraid that you're right ... :-( – Angel_de Nov 8 at 14:14
  • Please post the SQL you say is slow, perhaps we can work with that. BTW: 2 selects is a virtual guarantee to make the overall process slower. DBMS have been in the business of optimizing joins for 50 years. You use 2 queries then you are taking over the join, you don't use the JOIN operation, but that's still what it is. – Belayer Nov 9 at 17:33
0

Why you are using 2 different selects when you can use single nested select -

DECLARE
  v_skus VARCHAR2(255);
BEGIN
  SELECT YOUR_FIRST_QUERY
  INTO v_skus
  FROM YOUR_OTHER_TABLE
  WHERE WHERE number IN (SELECT num FROM VIEWNAME WHERE parent IN ('1000'));
  DBMS_OUTPUT.PUT_LINE('v_skus=' || v_skus);
END;
  • i used a subselect before and it had the same performance issues + i've read, that oracle does internally a left join when you use a subselect. So using the subselect wont change anything. – Angel_de Nov 8 at 12:35

Your Answer

Angel_de is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.