{"id":83,"date":"2011-04-25T20:42:28","date_gmt":"2011-04-25T20:42:28","guid":{"rendered":"http:\/\/62.131.51.129\/wordpress\/?p=83"},"modified":"2011-04-25T20:42:28","modified_gmt":"2011-04-25T20:42:28","slug":"temporary-tables-in-oracle","status":"publish","type":"post","link":"http:\/\/archief.van-maanen.com\/?p=83","title":{"rendered":"Temporary tables in Oracle"},"content":{"rendered":"<p>Oracle has a feature it is possible to create tamporary tables. The idea is that a table can be created, can be populated with data, but these data can only be seen during the session. Other sessions do see the table but they see no rows. Even if someone uses the same userid as the user that created the temporary table, no rows are returned.<br \/>\nFirst the syntax of this temporary table:<\/p>\n<pre>\n\ncreate global temporary table onzin\n(i number(5,0),\nnaam varchar2(30))\non commit preserve rows;\n\n<\/pre>\n<p>The working can be checked by adding a few rows. It can be verified that the rows exist: even after a commit, the rows are returned. <br \/>\nHowever if another session is opened, the rows are NOT returned. <br \/>\nWhat is the use of this feature?<br \/>\nBurleson (http:\/\/www.dba-oracle.com\/t_temporary_tables_sql.htm) claims that the performance can be improved by such temporary tables. I understand that some in-between results are kept in thic temporary table. They are later re-used by a second query. <br \/>\nThis is disputed by Tom Kyle. He writes: &#8220;I&#8217;m of the mindset that they are almost never needed&#8221;. He then proceeds by asking the readers to give him a situation where such temporary tables might be needed. He got some replies but they did not look very convincing. <br \/>\nWhatever: Oracle does know the concept of a temporary table, but their usage seems somewhat limited.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Oracle has a feature it is possible to create tamporary tables. The idea is that a table can be created, can be populated with data, but these data can only be seen during the session. Other sessions do see the table but they see no rows. Even if someone uses the same userid as the [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[],"class_list":["post-83","post","type-post","status-publish","format-standard","hentry","category-data-warehousing"],"_links":{"self":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/83","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=83"}],"version-history":[{"count":0,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/83\/revisions"}],"wp:attachment":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=83"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=83"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=83"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}