Joining Temp Tables with dblinks
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Joining Temp Tables with dblinks

  1. #1
    Join Date
    Sep 2009
    Posts
    3

    Joining Temp Tables with dblinks

    Joining the temp table with dblink will decrease the performance of the query?

  2. #2
    Join Date
    Sep 2009
    Posts
    3

    Will joining the temp table with dblinks will decrease the performance?

    Joining the temp table with dblink will decrease the performance of the query?
    My scenario is presently i am creating tables dynamically and storing the records in the tables.I am using dblinks to get the data from other tables.
    So now i want to change the procedures to use temp tables instead of dynamically creating and storing.

    So my only concern is the performance.Because i saw somewhere, joining the temp tables with the dblink tables will decrease the performance.
    If this is yes can someone let me know what exactly happens when i use temp tables instead of dynamically creating them.

    It would be helpful if someone respond to this immediately,because this is very urgent for me.

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    DBLinks are a poor performing feature - avoid if possible.

    You create a "temp" table - Oracle creates a temp table for you; pretty much the same thing.

    Want to be sure? test it.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  4. #4
    Join Date
    Sep 2009
    Posts
    3
    Hi , thanks for the reply.

    But my question is "previously i used dynamically generated tables(not temporary tables).Now i want to use temporary tables instead of using dynamically created tables.

    In some situations i may need to join the temporary table with dblink tables.
    So if i join temporary table with dblink will that cause the performance problem.
    If yes, then how?

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    mmhh... I see. Let me clarify it.

    "DBLinks are a poor performing feature - avoid if possible.

    You create a "temp" table - Oracle creates a temp table for you; pretty much the same thing.

    Want to be sure? test it."
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  6. #6
    Join Date
    Oct 2006
    Posts
    175
    You access dblink tables through network, its ought to be slower, whether you join it with temporary or permanent table. Why don't you first get the dblink tables locally and then perform whatever you feel like?

  7. #7
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by gtcol View Post
    You access dblink tables through network
    Not always the case, dblinked instance could be hosted in the same box.
    Either way dblink would perform poorly no matter what.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

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


Click Here to Expand Forum to Full Width