Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: Global Temporary Tables

    We're a Sybase shop migrating to Oracle, and so we're used to using temp tables in our procedures. I've seen other posts like this before, where seasoned Oracle developers/DBAs mention their affinity for using cursors as an alternative.

    Is there a performance gain to either approach? Although I haven't gotten to the point of performance testing yet, it seems to me that running a query which uses 2 temp tables would be faster than a proc which opens a cursor on one data set, and then performs a fetch for each record in that dataset, against another dataset. If the main recordset has 5000 recs, then a query has to be run 5000x for the inner cursor's data retrieval.

    Is there any merit to the way I am conceptualizing this process? Because you have to keep track of the Global Temporary Tables, recreating them across separate DB instances, I do like the cursor method better. I'm just used to temp table programming.

    Thanks,
    -Chuck

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    HUH?
    I could read the words but am having a problem visualizing what you mean.
    If you could post an example where you think a temporary table would reduce elapsed time, it might help you to get a meaningful response.
    Of course, the proof is in the pudding.
    Real world testing is vastly superior to idle speculation with no supporting facts.
    ==============================================
    Here is my off the cuff response & knee jerk reaction.
    Cursors are done/run in memory.
    Tables (even "temporary" one) can go to disk.
    In my mind processing in RAM will typically be faster than going to disk.

    HTH & YMMV!
    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
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    I would second anacedent's point that nothing beats real world testing.

    A third possibility though is just because in Sybase it might be more efficient to use cursor doesnt mean you cant go for a different approach in oracle. Try joining your two queries into one query and see how it performs, if you can tune it correctly it might be much quicker than any other approach. But again test it on your TEST or PROD system.

    i.e.

    select b.*
    from
    ( <first query> ) a,
    ( <second query> ) b
    where a.<join column> = b.<join column> ...


    Alan

  4. #4
    Join Date
    Apr 2004
    Posts
    246
    Too many people rely on pl/sql loops because they never bothered to learn the SQL language (select is SQL, while is pl/sql). There are always merits to temp tables (and I don't necessarily mean the new "temp" tables where the data goes away automatically after the session), as well as pl/sql loops, and just using straight SQL. You are very correct in stating that nested loops add overhead (we call them context switches) because oracle has to leave pl/sql in order to execute the sql. So:

    for c1 in (select...) loop
    for c2 in (select...) loop

    is usually slower than

    for c in (select... from c1, c2 where ...) loop

    But if you just want to move the data to another table, then

    insert into t select ... from c1, c2 where ...

    is the fastest you can get. Of course, you'll need to make certain that you index the tables properly. Oracles performance guide is a must read:
    http://download-west.oracle.com/docs...a96532/toc.htm

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    PL/SQL will "never" be faster than native SQL.
    PL/SQL is just a wrapper around SQL.
    A context switch is required to transition from the PL/SQL engine to the SQL engine.
    A 2nd context switch is required to return from the SQL engine to PL/SQL.
    When done many times within a LOOP this overhead will be measurable.
    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.

Posting Permissions

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