Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Jan 2004
    Location
    Austin
    Posts
    62

    Question Unanswered: Using Cursor in Subquery?

    From what I can see, it's not possible to reference a cursor in a SQL query.
    My hope was to do the following.

    DECLARE

    Cursor subsetofa IS
    Select distinct id from table b;

    BEGIN

    select * from a where a.id not in (subsetofa);

    END;
    /

    Essentially I want to exclude those ids found in subsetofa in multiple queries without having to run the query multiple times. Is there another method to achieve this goal?

    The help is much appreciated.

    Mike

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    select * from a where a.id not in (Select distinct id from table b);
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2004
    Location
    Austin
    Posts
    62

    Unhappy This is what I do now.

    Thanks, but this is how I'm doing it now. I want to improve performance by eliminating multiple calls of the same subquery.

    Mike

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    create a set?

    how about you post the whole proc for us to look at?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Apr 2004
    Posts
    246
    create a pipelined funtion to return the results of the cursor, and then cast that function as a table and you can use it in the query.

    Or, preferred method, write complex sql. sql will always perform better than pl/sql, so do what anacedent said, and make it as complex as you want:

    select * from a
    where id not in (
    select id from b
    union
    select id from c
    where not exists (....)
    ....
    )

    whatever your assorted queries for the cursors are, work them into the main sql.

    select * from a
    where not exists (select null from b where b.id = a.id)
    and not exists (select null from c where c.id = a.id)

    lots of options on how to write queries.
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  6. #6
    Join Date
    Jan 2004
    Location
    Austin
    Posts
    62

    Red face Not at liberty to post SQL

    It's government stuff, so I can't post the SQL. Essentially I doing this in an effort to add required records to parents in a subset type model.


    The top level must include a record for each more specific subset. I'm moving data from one data base to another, but I'm only interested in a specific subset, but this requires me to add corresponding records in 3 related tables by their unique identifier. This is why I need to reference the same set if ID's in more than one SQL statement. Essentially it would go like this.

    DECLARE

    Cursor subsetofa IS
    Select distinct id from table b;

    BEGIN

    select * from a where a.id not in (subsetofa);
    select * from c where c.id not in (subestofa);
    select * from d where d.id not in (subsetofa);

    END;
    /

    I'm actually doing a select into these tables, this is why I can't just run one big complex query. Not to mention I don't want to run out of temp space.

    Mike

  7. #7
    Join Date
    Apr 2004
    Posts
    246
    what does " doing a select into these tables" mean?

    and this, "requires me to add corresponding records in 3 related tables by their unique identifier", sounds like maybe you do this:

    select id from a
    union
    select id from c
    union
    select id from d
    minus
    select id from b

    maybe understanding where the results for the three queries goes would help.

    also, try using a "not exists" rather than a "not in" - this should help performance and reduce temp tablespace usage (assuming that b is indexed on id).
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  8. #8
    Join Date
    Jan 2004
    Location
    Austin
    Posts
    62

    Red face The problem isn't solved with a UNION

    The results don't go into one table. They go into three separate tables. Using the command.

    INSERT INTO Employee (ID, FName, LName)
    SELECT ID, FNAME, LNAME from PERSON where
    PERSON.ID not in (select ID from FIRED_PEOPLE );

    The idea being, I want to exclude the FIRED_PEOPLE set from more than one INSERT statement.

    Mike

  9. #9
    Join Date
    Apr 2004
    Posts
    246
    I don't think you worry about running the query multiple times - that shouldn't be an issue. Try rewriting your sql to use a not exists:
    INSERT INTO Employee (ID, FName, LName)
    SELECT ID, FNAME, LNAME from PERSON
    where not exists (select null from FIRED_PEOPLE where id = person.id);

    Oracle is fastest when performing Relational set operations, not when looping through linear data (it's the R in RDBMS). Using your query with a "not in" will not use an index, and therefore will be slower and use more temp space. Changing to "not exists" (assuming that fired_people.id is indexed) will run dramatically faster, and then you may not be concerned about running the subsetofa query multiple times.
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  10. #10
    Join Date
    Jan 2004
    Location
    Austin
    Posts
    62

    Thanks for not exists hint.

    I know Oracle is relational. However, I would suspect traversing what would essentially be an array of 50 records would be more efficient that doing a select on a 1M row table every time I want to id those 50 records.

    This is why I was looking for this type of solution. I figured a cursor is essentially a temporary data set, and I'm frustrated that I can't access it like a table.

    Mike

  11. #11
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    If you insist, you could use a global temporary table.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  12. #12
    Join Date
    Jul 2003
    Posts
    2,296
    create a bitmap index on FIRED_PEOPLE.id
    and it will FLY. Trust me.

    try it out.
    create the bitmap index then analyze the table then test your queries.
    should run terribly fast with that EXISTS clause.

    I cannot imagine FIRED_PEOPLE (if that is the case) table as being one
    that has many inserts or updates so the index should not be a problem.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  13. #13
    Join Date
    Apr 2004
    Posts
    246
    considering that the data is in a table, I don't see why you "can't access it like a table". You just need to write the sql differently.

    I'm assuming that "select ID from FIRED_PEOPLE" returns 50 rows, and PERSON contains the 1M (hate to think that you fired 1M people already). So, if you want to access all the people that haven't been fired, then you still need to access almost all rows of person anyway.

    If you mean that person has 1M rows, but only 50 have not been fired, you're "ideal" query will still not use an index, and therefore will use LOTS of temp space (both to buffer the almost 1M rows of the subquery, and then to compare to PERSON without an index) . How about:

    INSERT INTO Employee (ID, FName, LName)
    SELECT ID, FNAME, LNAME from PERSON
    where PERSON.ID in (
    select id from person minus select ID from FIRED_PEOPLE );

    Did you even try to run the "not exists" query I provided?
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  14. #14
    Join Date
    Jan 2004
    Location
    Austin
    Posts
    62

    Question Sorry for example

    I tried to simplify the example without providing all my SQL.

    The select statement in the subquery is actually a join of two larger tables. One being about 2M the other 1M records. It's not a simple filter on one table.

  15. #15
    Join Date
    Apr 2004
    Posts
    246
    Then, if the outside table is significantly less rows, use the not exists - correlated subqueries are great if the outer query has less rows than the inner.

    Otherwise, go with the global temp table - populate it with your subset query, and then just use it for the other subqueries. If your version doesn't support GTT, just use a regular table that you clear and repopulate.
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •