Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2012
    Posts
    2

    Unanswered: table variable or collection

    Dear all

    --I have union query in each union there are few common tables , so i want to keep the result of this common tables in temp table and then want to use them in different union.
    --i have one sp(stored proc) which brings two result sets both query have few comman tables, so i want to keep the result of this common tables in temp table and then want to use them in two different
    query in same sp(stored proc).

    please note: i do not want to use global temp tables.
    yours sincerely.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    use a view & avoid duplicating data
    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
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253

    Lightbulb

    I see developers trying to "cache" data all the time. My advice is, don't try to cache the data yourself. Write your SQL to solve the query each time. Oracle has a really out of this world data caching mechanism that works much better than anything that you will come up with.

    I would recommend that you read Oracle Database Concepts: Buffer Cache which will give you some idea of how Oracle "caches" your data for you.
    I would seriously recommend you get your hands on a book by Tom Kyte called Exper Oracle Database Architecture, in particular chapter 4 on memory structures. He explains it all very well.

Posting Permissions

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