<?xml version='1.0' encoding='UTF-8'?><?xml-stylesheet href="http://www.blogger.com/styles/atom.css" type="text/css"?><feed xmlns='http://www.w3.org/2005/Atom' xmlns:openSearch='http://a9.com/-/spec/opensearchrss/1.0/' xmlns:blogger='http://schemas.google.com/blogger/2008' xmlns:georss='http://www.georss.org/georss' xmlns:gd="http://schemas.google.com/g/2005" xmlns:thr='http://purl.org/syndication/thread/1.0'><id>tag:blogger.com,1999:blog-6790758757919456346</id><updated>2024-10-31T23:54:10.827-07:00</updated><category term="D2K INTERVIEW"/><category term="SQL TUNING"/><category term="OLD POST"/><category term="QUICK VIEW"/><category term="SQL SERVER"/><category term="FORMS AND REPORTS"/><category term="ORACLE FUNCTION"/><category term="ORACLE QUERY"/><category term="ORACLE WAREHOUSE BUILDER 10g"/><category term="TIPS AND TRICKS"/><category term="DATA WAREHOUSE"/><category term="INSTALLATION"/><category term="ORACLE HOT JOBS"/><category term="ORACLE OCA"/><category term="SYNTAX"/><title type='text'>TO ACHIEVE WHAT WE WANT</title><subtitle type='html'></subtitle><link rel='http://schemas.google.com/g/2005#feed' type='application/atom+xml' href='http://allmyneed.blogspot.com/feeds/posts/default'/><link rel='self' type='application/atom+xml' href='http://www.blogger.com/feeds/6790758757919456346/posts/default?redirect=false'/><link rel='alternate' type='text/html' href='http://allmyneed.blogspot.com/'/><link rel='hub' href='http://pubsubhubbub.appspot.com/'/><link rel='next' type='application/atom+xml' href='http://www.blogger.com/feeds/6790758757919456346/posts/default?start-index=26&amp;max-results=25&amp;redirect=false'/><author><name>Unknown</name><email>noreply@blogger.com</email><gd:image rel='http://schemas.google.com/g/2005#thumbnail' width='16' height='16' src='https://img1.blogblog.com/img/b16-rounded.gif'/></author><generator version='7.00' uri='http://www.blogger.com'>Blogger</generator><openSearch:totalResults>49</openSearch:totalResults><openSearch:startIndex>1</openSearch:startIndex><openSearch:itemsPerPage>25</openSearch:itemsPerPage><entry><id>tag:blogger.com,1999:blog-6790758757919456346.post-6079290562362362676</id><published>2010-05-27T03:30:00.000-07:00</published><updated>2010-05-27T03:36:19.439-07:00</updated><category scheme="http://www.blogger.com/atom/ns#" term="ORACLE WAREHOUSE BUILDER 10g"/><title type='text'>FLAT FILE DATA TRANSACTION USING OWB 10g Part- 2</title><content type='html'>&lt;div class=&quot;summary&quot; style=&quot;border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none;&quot;&gt;&lt;div style=&quot;border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none;&quot;&gt;&lt;a href=&quot;http://allmyneed.blogspot.com/2010/05/flat-file-data-transaction-using-owb.html&quot;&gt;&lt;strong&gt;FLAT FILE DATA TRANSACTION USING OWB 10g Part- 1&lt;/strong&gt;&lt;/a&gt;&lt;/div&gt;&lt;div style=&quot;border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none;&quot;&gt;&lt;br /&gt;
&lt;/div&gt;&lt;div style=&quot;border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none;&quot;&gt;The Edit File System Location: SOURCE_LOCATION1 window:&lt;/div&gt;&lt;div style=&quot;border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none;&quot;&gt;&lt;br /&gt;
&lt;/div&gt;&lt;div style=&quot;border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none;&quot;&gt;&lt;br /&gt;
&lt;/div&gt;&lt;div class=&quot;separator&quot; style=&quot;clear: both; text-align: center;&quot;&gt;&lt;a href=&quot;https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjE-CDaN7EKelyqbEXnm5CSb62HpU-KndFaLVLQx6hjUc62XWcrLJXmKulHjo0fyKMqIQB8A4IR4msvpAR-rtvm4n9N8hIfzx9O1s295GMTab5QUrzve6_Oc0K3AA9raZjpaf4k1CNifGI/s1600/5.+Oracle+Warehouse+Builder+10g+The+Edit+File+System+Location+.bmp&quot; imageanchor=&quot;1&quot; style=&quot;clear: left; cssfloat: left; float: left; margin-bottom: 1em; margin-right: 1em;&quot;&gt;&lt;img border=&quot;0&quot; gu=&quot;true&quot; src=&quot;https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjE-CDaN7EKelyqbEXnm5CSb62HpU-KndFaLVLQx6hjUc62XWcrLJXmKulHjo0fyKMqIQB8A4IR4msvpAR-rtvm4n9N8hIfzx9O1s295GMTab5QUrzve6_Oc0K3AA9raZjpaf4k1CNifGI/s320/5.+Oracle+Warehouse+Builder+10g+The+Edit+File+System+Location+.bmp&quot; /&gt;&lt;/a&gt;&lt;/div&gt;&lt;div style=&quot;border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none;&quot;&gt;&amp;nbsp;In the Edit File System Location: SOURCE_LOCATION1 window, rename the default name SOURCE_LOCATION1 to SOURCE_LOCATION. &lt;/div&gt;&lt;br /&gt;
&lt;div style=&quot;border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none;&quot;&gt;Click Browse. &lt;/div&gt;&lt;div style=&quot;border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none;&quot;&gt;&lt;/div&gt;&lt;br /&gt;
&lt;br /&gt;
&lt;br /&gt;
&lt;div style=&quot;border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none;&quot;&gt;&amp;nbsp; &lt;/div&gt;&lt;div style=&quot;border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none;&quot;&gt;&amp;nbsp; &lt;/div&gt;&lt;div style=&quot;border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none;&quot;&gt;&amp;nbsp; &lt;/div&gt;&amp;nbsp; &lt;br /&gt;
&lt;div style=&quot;border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none;&quot;&gt;&amp;nbsp; &lt;/div&gt;&lt;div style=&quot;border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none;&quot;&gt;&amp;nbsp; &lt;/div&gt;&lt;div style=&quot;border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none;&quot;&gt;The Edit File System Location, Browse Directory window: &lt;/div&gt;&lt;div style=&quot;border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none;&quot;&gt;&amp;nbsp; &lt;/div&gt;&lt;/div&gt;&lt;div class=&quot;separator&quot; style=&quot;border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none; clear: both; text-align: left;&quot;&gt;&lt;a href=&quot;https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj_tH2gdrUsSwMcCZtiH24eBEhyvni9XNZBHuNIzP5VqZJxgDAQpPUDZc_KXeD0_khVD_xil06JxZU5ihZVCVY1pQB5q5VfGq_pD6OneaoTYlx6_gwLBh4U6mwEUJrDDgSKTRExb0tSa7c/s1600/6.bmp&quot; imageanchor=&quot;1&quot; style=&quot;clear: left; cssfloat: left; float: left; margin-bottom: 1em; margin-right: 1em;&quot;&gt;&lt;img border=&quot;0&quot; gu=&quot;true&quot; src=&quot;https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj_tH2gdrUsSwMcCZtiH24eBEhyvni9XNZBHuNIzP5VqZJxgDAQpPUDZc_KXeD0_khVD_xil06JxZU5ihZVCVY1pQB5q5VfGq_pD6OneaoTYlx6_gwLBh4U6mwEUJrDDgSKTRExb0tSa7c/s320/6.bmp&quot; /&gt;&lt;/a&gt;In the Browse Directory window, select&amp;nbsp; or the drive and directory (which contains the source folders) where you saved the Data File. &lt;/div&gt;&lt;div class=&quot;&quot; style=&quot;border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none; clear: both; text-align: left;&quot;&gt;Connection Information :&lt;/div&gt;&lt;div class=&quot;&quot; style=&quot;border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none; clear: both; text-align: left;&quot;&gt;&lt;br /&gt;
&lt;/div&gt;&lt;div class=&quot;separator&quot; style=&quot;border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none; clear: both; text-align: left;&quot;&gt;&lt;a href=&quot;https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgLQkznT0l2EgS7PO_UzTxX2GGMC5Aph5wd_8aojNoqIG-tP_oMI8LGqp6x-47CaIlOCEW7QO50wBF-sYlBEqBHnMCKzUizb-PRmGv-GDGgHNx0wUhZqh1phKVPoS-fbv2xSOwkbIiwYWI/s1600/7.+Oracle+Warehouse+Builder+10g+the+Connection+Information+window+.bmp&quot; imageanchor=&quot;1&quot; style=&quot;clear: left; cssfloat: left; float: left; margin-bottom: 1em; margin-right: 1em;&quot;&gt;&lt;img border=&quot;0&quot; gu=&quot;true&quot; src=&quot;https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgLQkznT0l2EgS7PO_UzTxX2GGMC5Aph5wd_8aojNoqIG-tP_oMI8LGqp6x-47CaIlOCEW7QO50wBF-sYlBEqBHnMCKzUizb-PRmGv-GDGgHNx0wUhZqh1phKVPoS-fbv2xSOwkbIiwYWI/s320/7.+Oracle+Warehouse+Builder+10g+the+Connection+Information+window+.bmp&quot; /&gt;&lt;/a&gt;Ensure that Import after finish is selected. Selecting this option launches the Import Metadata Wizard. Click Next.&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&lt;/div&gt;&lt;div class=&quot;separator&quot; style=&quot;border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none; clear: both; text-align: left;&quot;&gt;&lt;strong&gt;Tip:&lt;/strong&gt; If the check box is not selected, the Import Metadata Wizard does not launch automatically. To launch the Import Metadata Wizard manually, in Project Explorer, expand Files, right-click the SOURCE module, and select Import. &lt;/div&gt;&lt;div class=&quot;separator&quot; style=&quot;border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none; clear: both; text-align: left;&quot;&gt;&lt;br /&gt;
&lt;/div&gt;&lt;div class=&quot;separator&quot; style=&quot;border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none; clear: both; text-align: left;&quot;&gt;&lt;br /&gt;
&lt;/div&gt;&lt;div class=&quot;separator&quot; style=&quot;border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none; clear: both; text-align: left;&quot;&gt;Summary page:&lt;/div&gt;&lt;div class=&quot;separator&quot; style=&quot;border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none; clear: both; text-align: left;&quot;&gt;&lt;br /&gt;
&lt;/div&gt;&lt;div class=&quot;separator&quot; style=&quot;border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none; clear: both; text-align: left;&quot;&gt;&lt;a href=&quot;https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg2RDcSzMoCq4bCdCPn5-34HtFIYWintkguRKeYp21PUVn1aQGvkjWON3YZvguf5B0I6P5dHjmKJ_Ujodv1QiLv3JugH75vYoWiQQji_b7INkKwT1JI2wq3PP0619wEQaUK0PhXBK8M0yc/s1600/8.Oracle+Warehouse+Builder+10g+Summary+.bmp&quot; imageanchor=&quot;1&quot; style=&quot;clear: left; cssfloat: left; float: left; margin-bottom: 1em; margin-right: 1em;&quot;&gt;&lt;img border=&quot;0&quot; gu=&quot;true&quot; src=&quot;https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg2RDcSzMoCq4bCdCPn5-34HtFIYWintkguRKeYp21PUVn1aQGvkjWON3YZvguf5B0I6P5dHjmKJ_Ujodv1QiLv3JugH75vYoWiQQji_b7INkKwT1JI2wq3PP0619wEQaUK0PhXBK8M0yc/s320/8.Oracle+Warehouse+Builder+10g+Summary+.bmp&quot; /&gt;&lt;/a&gt;On the Summary page, review the module settings. Click Finish.&lt;/div&gt;&lt;br /&gt;
&lt;br /&gt;
&lt;br /&gt;
&lt;br /&gt;
&amp;nbsp; &lt;br /&gt;
&amp;nbsp; &lt;br /&gt;
&amp;nbsp; &lt;br /&gt;
&amp;nbsp; &lt;br /&gt;
&amp;nbsp; &lt;br /&gt;
&amp;nbsp; &lt;br /&gt;
&amp;nbsp; &lt;br /&gt;
&amp;nbsp; &lt;br /&gt;
&amp;nbsp; &lt;br /&gt;
&amp;nbsp; &lt;br /&gt;
&amp;nbsp; &lt;br /&gt;
&lt;strong&gt;FLAT FILE DATA TRANSACTION USING OWB 10g Part- 3&lt;/strong&gt;&lt;div class=&quot;blogger-post-footer&quot;&gt;IF YOU HAVE ANY PROBLEM OR WANT MORE DATA PLS EMAIL ME.
ss.sabyasachipaul@gmail.com&lt;/div&gt;</content><link rel='edit' type='application/atom+xml' href='http://www.blogger.com/feeds/6790758757919456346/posts/default/6079290562362362676'/><link rel='self' type='application/atom+xml' href='http://www.blogger.com/feeds/6790758757919456346/posts/default/6079290562362362676'/><link rel='alternate' type='text/html' href='http://allmyneed.blogspot.com/2010/05/flat-file-data-transaction-using-owb_27.html' title='FLAT FILE DATA TRANSACTION USING OWB 10g Part- 2'/><author><name>Unknown</name><email>noreply@blogger.com</email><gd:image rel='http://schemas.google.com/g/2005#thumbnail' width='16' height='16' src='https://img1.blogblog.com/img/b16-rounded.gif'/></author><media:thumbnail xmlns:media="http://search.yahoo.com/mrss/" url="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjE-CDaN7EKelyqbEXnm5CSb62HpU-KndFaLVLQx6hjUc62XWcrLJXmKulHjo0fyKMqIQB8A4IR4msvpAR-rtvm4n9N8hIfzx9O1s295GMTab5QUrzve6_Oc0K3AA9raZjpaf4k1CNifGI/s72-c/5.+Oracle+Warehouse+Builder+10g+The+Edit+File+System+Location+.bmp" height="72" width="72"/></entry><entry><id>tag:blogger.com,1999:blog-6790758757919456346.post-7055828707233881904</id><published>2010-05-27T02:46:00.000-07:00</published><updated>2010-05-27T03:36:55.810-07:00</updated><category scheme="http://www.blogger.com/atom/ns#" term="ORACLE WAREHOUSE BUILDER 10g"/><title type='text'>FLAT FILE DATA TRANSACTION USING OWB 10g Part - 1</title><content type='html'>&lt;div class=&quot;summary&quot; style=&quot;border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none;&quot;&gt;Flat files are a typical data source for warehouse projects. Here, you learn how to create a flat file source module. Modules are storage objects within projects that help you logically group source and target object definitions. Source modules hold metadata describing source systems from which you extract data. Target modules hold metadata describing your target data warehouse or data marts. &lt;/div&gt;&lt;div class=&quot;summary&quot; style=&quot;border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none;&quot;&gt;&lt;/div&gt;&lt;div class=&quot;summary&quot;&gt;&lt;br /&gt;
&lt;/div&gt;&lt;div class=&quot;summary&quot;&gt;A flat file source module defines a connection to a folder that contains a number of flat file sample files. You learn how to use the wizard to import flat file metadata into the flat file source module.&amp;nbsp;&amp;nbsp; &lt;/div&gt;&lt;div class=&quot;summary&quot;&gt;&amp;nbsp; &lt;/div&gt;&lt;div class=&quot;separator&quot; style=&quot;border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none; clear: both; text-align: center;&quot;&gt;&lt;a href=&quot;https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjZCeJ2WuV5znMiIr2NHe2RxnRUZb-Tgl9sxc7EDw8IkuBgYT1PleDoCHn7PqGhZI4UfRHk-IWKqH1OEUAWZHagMj3PfpB56JWDWobHD8ZybrNf7rEVBm3yGGSaVbWXsxv3GRHn0TqdF40/s1600/1.bmp&quot; imageanchor=&quot;1&quot; style=&quot;clear: left; cssfloat: left; float: left; margin-bottom: 1em; margin-right: 1em;&quot;&gt;&lt;img border=&quot;0&quot; gu=&quot;true&quot; src=&quot;https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjZCeJ2WuV5znMiIr2NHe2RxnRUZb-Tgl9sxc7EDw8IkuBgYT1PleDoCHn7PqGhZI4UfRHk-IWKqH1OEUAWZHagMj3PfpB56JWDWobHD8ZybrNf7rEVBm3yGGSaVbWXsxv3GRHn0TqdF40/s320/1.bmp&quot; /&gt;&lt;/a&gt;&lt;/div&gt;&lt;div class=&quot;summary&quot;&gt;&amp;nbsp; &lt;/div&gt;&lt;div class=&quot;summary&quot; style=&quot;border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none;&quot;&gt;Right-click the Files node and select New. &lt;/div&gt;&lt;div class=&quot;summary&quot; style=&quot;border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none;&quot;&gt;&amp;nbsp; &lt;/div&gt;&lt;div class=&quot;summary&quot; style=&quot;border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none;&quot;&gt;&amp;nbsp; &lt;/div&gt;&lt;div class=&quot;summary&quot; style=&quot;border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none;&quot;&gt;&amp;nbsp; &lt;/div&gt;&lt;div class=&quot;summary&quot;&gt;&amp;nbsp; &lt;/div&gt;&lt;div class=&quot;summary&quot; style=&quot;border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none;&quot;&gt;&amp;nbsp; &lt;/div&gt;&lt;div class=&quot;summary&quot;&gt;&amp;nbsp; &lt;/div&gt;&lt;div class=&quot;summary&quot; style=&quot;border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none;&quot;&gt;&amp;nbsp; &lt;/div&gt;&lt;div class=&quot;summary&quot;&gt;&amp;nbsp; &lt;/div&gt;&lt;div class=&quot;summary&quot;&gt;&amp;nbsp; &lt;/div&gt;&lt;div class=&quot;summary&quot; style=&quot;border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none;&quot;&gt;&amp;nbsp; &lt;/div&gt;&lt;div class=&quot;summary&quot;&gt;&amp;nbsp; &lt;/div&gt;&lt;div class=&quot;summary&quot; style=&quot;border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none;&quot;&gt;&amp;nbsp; &lt;/div&gt;&lt;div class=&quot;summary&quot;&gt;&amp;nbsp;&amp;nbsp;&amp;nbsp; &lt;/div&gt;&lt;div align=&quot;left&quot; class=&quot;summary&quot;&gt;&lt;br /&gt;
&lt;/div&gt;&lt;div class=&quot;summary&quot; style=&quot;text-align: left;&quot;&gt;&lt;br /&gt;
&lt;/div&gt;&lt;div class=&quot;summary&quot; style=&quot;text-align: left;&quot;&gt;&lt;br /&gt;
&lt;/div&gt;&lt;div class=&quot;summary&quot; style=&quot;text-align: left;&quot;&gt;&lt;br /&gt;
&lt;/div&gt;&lt;div class=&quot;summary&quot; style=&quot;border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none; text-align: left;&quot;&gt;The Create Module Wizard: Welcome page:&lt;/div&gt;&lt;div class=&quot;separator&quot; style=&quot;border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none; clear: both; text-align: left;&quot;&gt;&lt;br /&gt;
&lt;/div&gt;&lt;div class=&quot;separator&quot; style=&quot;border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none; clear: both; text-align: left;&quot;&gt;&lt;a href=&quot;https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjwcqkeYTQWpJ-1bu0nFxx_8olfVjWq4DESdl9ANPSuw3NrAgEkWzE48JwinAkCSTGB2CJa6r4fD7YmmRHq3gop4IWIR6VhpEiEnR8vdS9GrxbIuHglHdqEzthAStSvCMgre843jKL6jZg/s1600/2.bmp&quot; imageanchor=&quot;1&quot; style=&quot;clear: left; cssfloat: left; float: left; margin-bottom: 1em; margin-right: 1em;&quot;&gt;&lt;img border=&quot;0&quot; gu=&quot;true&quot; src=&quot;https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjwcqkeYTQWpJ-1bu0nFxx_8olfVjWq4DESdl9ANPSuw3NrAgEkWzE48JwinAkCSTGB2CJa6r4fD7YmmRHq3gop4IWIR6VhpEiEnR8vdS9GrxbIuHglHdqEzthAStSvCMgre843jKL6jZg/s320/2.bmp&quot; /&gt;&lt;/a&gt;Click Next&lt;/div&gt;&lt;div class=&quot;separator&quot; style=&quot;border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none; clear: both; text-align: left;&quot;&gt;&lt;br /&gt;
&lt;/div&gt;&lt;div class=&quot;separator&quot; style=&quot;border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none; clear: both; text-align: left;&quot;&gt;The Create Module Wizard: Name and Description page:&lt;/div&gt;&lt;div class=&quot;separator&quot; style=&quot;border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none; clear: both; text-align: left;&quot;&gt;&lt;br /&gt;
&lt;/div&gt;&lt;div class=&quot;separator&quot; style=&quot;border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none; clear: both; text-align: left;&quot;&gt;&lt;a href=&quot;https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhw99QX1G_ip2Ir-4At_7kzwgYUwpLgrxtoOrzoht_mzZX3mzolIA2hO0oWpmhiyyxIVLsFShl3008lm_1PKht7ROO4OkNBQ09yl-NCJpVmfqMd4FZwpAHLWQ6PUuiEXGAcoSCDy7_oqrM/s1600/3.bmp&quot; imageanchor=&quot;1&quot; style=&quot;clear: left; cssfloat: left; float: left; margin-bottom: 1em; margin-right: 1em;&quot;&gt;&lt;img border=&quot;0&quot; gu=&quot;true&quot; src=&quot;https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhw99QX1G_ip2Ir-4At_7kzwgYUwpLgrxtoOrzoht_mzZX3mzolIA2hO0oWpmhiyyxIVLsFShl3008lm_1PKht7ROO4OkNBQ09yl-NCJpVmfqMd4FZwpAHLWQ6PUuiEXGAcoSCDy7_oqrM/s320/3.bmp&quot; /&gt;&lt;/a&gt;Enter SOURCE as the name for the module. And click Next.&lt;/div&gt;&amp;nbsp; &lt;br /&gt;
&amp;nbsp; &lt;br /&gt;
&lt;div class=&quot;separator&quot; style=&quot;border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none; clear: both; text-align: left;&quot;&gt;The Create Module Wizard: Connection Information page:&lt;/div&gt;&lt;br /&gt;
&lt;a href=&quot;https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOC2PQnKO9jViLxQD9rsY36vtdOEC_mwxHIlDw5YzKq0tBI90gmWZ9lxzLBeU5Xu32k8aUw8vsJWpRrwIgZWSEHWRp3nFBHNxL-msHh7TrB0R1oxZFzArRbwND9PxIqAAKOXl8Zrsxscg/s1600/4.bmp&quot; imageanchor=&quot;1&quot; style=&quot;clear: left; cssfloat: left; float: left; margin-bottom: 1em; margin-right: 1em;&quot;&gt;&lt;img border=&quot;0&quot; gu=&quot;true&quot; src=&quot;https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOC2PQnKO9jViLxQD9rsY36vtdOEC_mwxHIlDw5YzKq0tBI90gmWZ9lxzLBeU5Xu32k8aUw8vsJWpRrwIgZWSEHWRp3nFBHNxL-msHh7TrB0R1oxZFzArRbwND9PxIqAAKOXl8Zrsxscg/s320/4.bmp&quot; /&gt;&lt;/a&gt;OWB creates a default location, &amp;nbsp;SOURCE_LOCATION1. Click Edit.&lt;br /&gt;
&lt;br /&gt;
&lt;br /&gt;
&amp;nbsp; &lt;br /&gt;
&amp;nbsp; &lt;br /&gt;
&amp;nbsp; &lt;br /&gt;
&amp;nbsp; &lt;br /&gt;
&amp;nbsp; &lt;br /&gt;
&amp;nbsp; &lt;br /&gt;
&amp;nbsp; &lt;br /&gt;
&amp;nbsp; &lt;br /&gt;
&amp;nbsp; &lt;br /&gt;
&amp;nbsp; &lt;br /&gt;
&amp;nbsp; &lt;br /&gt;
&amp;nbsp; &lt;br /&gt;
&lt;a href=&quot;http://allmyneed.blogspot.com/2010/05/flat-file-data-transaction-using-owb_27.html&quot;&gt;&lt;strong&gt;FLAT FILE DATA TRANSACTION USING OWB 10g Part - 2&lt;/strong&gt;&lt;/a&gt;&lt;div class=&quot;blogger-post-footer&quot;&gt;IF YOU HAVE ANY PROBLEM OR WANT MORE DATA PLS EMAIL ME.
ss.sabyasachipaul@gmail.com&lt;/div&gt;</content><link rel='edit' type='application/atom+xml' href='http://www.blogger.com/feeds/6790758757919456346/posts/default/7055828707233881904'/><link rel='self' type='application/atom+xml' href='http://www.blogger.com/feeds/6790758757919456346/posts/default/7055828707233881904'/><link rel='alternate' type='text/html' href='http://allmyneed.blogspot.com/2010/05/flat-file-data-transaction-using-owb.html' title='FLAT FILE DATA TRANSACTION USING OWB 10g Part - 1'/><author><name>Unknown</name><email>noreply@blogger.com</email><gd:image rel='http://schemas.google.com/g/2005#thumbnail' width='16' height='16' src='https://img1.blogblog.com/img/b16-rounded.gif'/></author><media:thumbnail xmlns:media="http://search.yahoo.com/mrss/" url="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjZCeJ2WuV5znMiIr2NHe2RxnRUZb-Tgl9sxc7EDw8IkuBgYT1PleDoCHn7PqGhZI4UfRHk-IWKqH1OEUAWZHagMj3PfpB56JWDWobHD8ZybrNf7rEVBm3yGGSaVbWXsxv3GRHn0TqdF40/s72-c/1.bmp" height="72" width="72"/></entry><entry><id>tag:blogger.com,1999:blog-6790758757919456346.post-6296597059994143670</id><published>2010-04-28T06:07:00.000-07:00</published><updated>2010-04-28T06:19:33.481-07:00</updated><category scheme="http://www.blogger.com/atom/ns#" term="INSTALLATION"/><title type='text'>Oracle Application Server 10g Release2 Spet By Spet Installation</title><content type='html'>&lt;div class=&quot;separator&quot; style=&quot;clear: both; text-align: center;&quot;&gt;&lt;a href=&quot;https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEims5gMAIuZGNZ7j6u_GlRSXLHfzfwRnhIE49H67Dq4VgpZ4xo_lOCZ_2kffIbeJOIlU6hPSsdl2KzETd8lJmlzuzkh3XwZg2_F39ItGi9nDQSDbn3MM5bu3ZHb3qA3MMDgePpQQdlUokU/s1600/1.Welcome.JPG&quot; imageanchor=&quot;1&quot; style=&quot;clear: left; cssfloat: left; float: left; margin-bottom: 1em; margin-right: 1em;&quot;&gt;&lt;img border=&quot;0&quot; src=&quot;https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEims5gMAIuZGNZ7j6u_GlRSXLHfzfwRnhIE49H67Dq4VgpZ4xo_lOCZ_2kffIbeJOIlU6hPSsdl2KzETd8lJmlzuzkh3XwZg2_F39ItGi9nDQSDbn3MM5bu3ZHb3qA3MMDgePpQQdlUokU/s1600/1.Welcome.JPG&quot; tt=&quot;true&quot; /&gt;&lt;/a&gt;&lt;/div&gt;&lt;br /&gt;
&lt;strong&gt;1) Oracle 10g R2&amp;nbsp; &quot;Welcome Screen&quot;&lt;/strong&gt;&lt;br /&gt;
&lt;br /&gt;
&lt;br /&gt;
Click &quot;Deinstall Product&quot; to Remove the existing Oracle From The Server.&lt;br /&gt;
&lt;br /&gt;
Click next to Install Oracle 10g R2.&lt;br /&gt;
&lt;br /&gt;
&lt;br /&gt;
&lt;br /&gt;
&lt;div class=&quot;separator&quot; style=&quot;clear: both; text-align: center;&quot;&gt;&lt;a href=&quot;https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhh15TFDs8qZZbN-Yl3RshQRGGs1D3lC-lZ_Ln744eMSs9qPkvqrHSpin1uScC3cibAt7EcolH7YSVhpYKxfnFzFh2URuLIMm5M1Dj9oail4zaxR2mZB6MHFvf1MBMvkRdhuZZDIaxWZtE/s1600/2.Specify%20File%20Location.JPG&quot; imageanchor=&quot;1&quot; style=&quot;clear: left; cssfloat: left; float: left; margin-bottom: 1em; margin-right: 1em;&quot;&gt;&lt;img border=&quot;0&quot; src=&quot;https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhh15TFDs8qZZbN-Yl3RshQRGGs1D3lC-lZ_Ln744eMSs9qPkvqrHSpin1uScC3cibAt7EcolH7YSVhpYKxfnFzFh2URuLIMm5M1Dj9oail4zaxR2mZB6MHFvf1MBMvkRdhuZZDIaxWZtE/s1600/2.Specify%20File%20Location.JPG&quot; tt=&quot;true&quot; /&gt;&lt;/a&gt;&lt;/div&gt;&lt;br /&gt;
&lt;strong&gt;2) &quot;Specify File Locations&quot;&lt;/strong&gt;&lt;br /&gt;
&lt;br /&gt;
&lt;u&gt;Source Path :&lt;/u&gt;&amp;nbsp;&amp;nbsp; Where the installer is located.&lt;br /&gt;
&lt;u&gt;Destination Name :&lt;/u&gt;&amp;nbsp; Oracle instance name.&lt;br /&gt;
&lt;u&gt;Destination Path :&lt;/u&gt;&amp;nbsp; Installation path.&lt;br /&gt;
&lt;br /&gt;
Click Next.&lt;div class=&quot;blogger-post-footer&quot;&gt;IF YOU HAVE ANY PROBLEM OR WANT MORE DATA PLS EMAIL ME.
ss.sabyasachipaul@gmail.com&lt;/div&gt;</content><link rel='edit' type='application/atom+xml' href='http://www.blogger.com/feeds/6790758757919456346/posts/default/6296597059994143670'/><link rel='self' type='application/atom+xml' href='http://www.blogger.com/feeds/6790758757919456346/posts/default/6296597059994143670'/><link rel='alternate' type='text/html' href='http://allmyneed.blogspot.com/2010/04/oracle-application-server-10g-release2.html' title='Oracle Application Server 10g Release2 Spet By Spet Installation'/><author><name>Unknown</name><email>noreply@blogger.com</email><gd:image rel='http://schemas.google.com/g/2005#thumbnail' width='16' height='16' src='https://img1.blogblog.com/img/b16-rounded.gif'/></author><media:thumbnail xmlns:media="http://search.yahoo.com/mrss/" url="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEims5gMAIuZGNZ7j6u_GlRSXLHfzfwRnhIE49H67Dq4VgpZ4xo_lOCZ_2kffIbeJOIlU6hPSsdl2KzETd8lJmlzuzkh3XwZg2_F39ItGi9nDQSDbn3MM5bu3ZHb3qA3MMDgePpQQdlUokU/s72-c/1.Welcome.JPG" height="72" width="72"/></entry><entry><id>tag:blogger.com,1999:blog-6790758757919456346.post-5262346013735508276</id><published>2010-04-08T03:07:00.000-07:00</published><updated>2010-04-08T03:20:57.290-07:00</updated><category scheme="http://www.blogger.com/atom/ns#" term="TIPS AND TRICKS"/><title type='text'>Dual Dropped and Recreation..</title><content type='html'>&lt;pre class=&quot;jive-pre&quot;&gt;&lt;code class=&quot;jive-code jive-java&quot;&gt;&lt;span style=&quot;font-family: Arial, Helvetica, sans-serif;&quot;&gt;SQL&amp;gt; DROP TABLE SYS.DUAL ;

Table dropped.
&lt;/span&gt;
&lt;span style=&quot;font-family: Arial, Helvetica, sans-serif;&quot;&gt;SQL&amp;gt; CREATE TABLE SYS.DUAL
2  (
3    DUMMY  VARCHAR2(1 BYTE)
4  )
5  TABLESPACE SYSTEM;

Table created.

SQL&amp;gt; CREATE PUBLIC SYNONYM DUAL FOR SYS.DUAL;

Synonym created.

SQL&amp;gt; GRANT SELECT ON SYS.DUAL TO PUBLIC WITH GRANT OPTION;

Grant succeeded.

SQL&amp;gt; INSERT INTO dual VALUES (&lt;span style=&quot;color: navy;&quot;&gt;&#39;X&#39;&lt;/span&gt;);

1 row created.
&lt;/span&gt;

&lt;span style=&quot;font-family: Arial, Helvetica, sans-serif;&quot;&gt;SQL&amp;gt; SELECT * FROM dual;

D                                                                               
-                                                                               
X  &lt;/span&gt;&lt;/code&gt;&lt;/pre&gt;&lt;div class=&quot;blogger-post-footer&quot;&gt;IF YOU HAVE ANY PROBLEM OR WANT MORE DATA PLS EMAIL ME.
ss.sabyasachipaul@gmail.com&lt;/div&gt;</content><link rel='edit' type='application/atom+xml' href='http://www.blogger.com/feeds/6790758757919456346/posts/default/5262346013735508276'/><link rel='self' type='application/atom+xml' href='http://www.blogger.com/feeds/6790758757919456346/posts/default/5262346013735508276'/><link rel='alternate' type='text/html' href='http://allmyneed.blogspot.com/2010/04/dual-dropped-and-recreation.html' title='Dual Dropped and Recreation..'/><author><name>Unknown</name><email>noreply@blogger.com</email><gd:image rel='http://schemas.google.com/g/2005#thumbnail' width='16' height='16' src='https://img1.blogblog.com/img/b16-rounded.gif'/></author></entry><entry><id>tag:blogger.com,1999:blog-6790758757919456346.post-7718673887001960112</id><published>2010-02-01T03:48:00.000-08:00</published><updated>2010-02-01T04:07:14.736-08:00</updated><category scheme="http://www.blogger.com/atom/ns#" term="SQL SERVER"/><title type='text'>SQL SERVER Questions And Answers Page 5</title><content type='html'>&lt;div align=&quot;justify&quot;&gt;&lt;span style=&quot;font-size:130%;&quot;&gt;1. What is deadlock?&lt;/span&gt;&lt;/div&gt;&lt;span style=&quot;font-size:130%;&quot;&gt;&lt;div align=&quot;justify&quot;&gt;&lt;br /&gt;&lt;/span&gt;A dead lock is a situation in which two users (or transactions) have locks on separate objects, and each user is waiting for a lock on the other’s object. It usually occurs in a multi-user environment.&lt;br /&gt;&lt;br /&gt;&lt;span style=&quot;font-size:130%;&quot;&gt;2. What are triggers?&lt;/span&gt;&lt;/div&gt;&lt;span style=&quot;font-size:130%;&quot;&gt;&lt;/span&gt;&lt;div align=&quot;justify&quot;&gt;&lt;br /&gt;A trigger is a block of code that constitutes with a set of T-SQL statements that are activated in response to certain actions. A trigger can also be interpreted as a special kind of stored procedure that is executed whenever an action, such as data modification, takes place.&lt;br /&gt;A trigger is always defined on a table, and is said to have fired whenever the data in the underlying table is affected by any of the Data Manipulation Language (DML) statements-INSERT, UPDATE, or DELETE. A trigger fires in response to an event like insertion, updation, and deletion of data.&lt;br /&gt;Triggers help in maintaining consistent, reliable, and correct data in tables. They enable the performance of complex actions and cascade these actions to other dependant tables.&lt;br /&gt;Characteristics of a trigger:&lt;br /&gt;&lt;br /&gt;·      It can be associated with tables.&lt;br /&gt;·      It cannot be defined on temporary tables or views. However, it can reference temporary tables and views.&lt;br /&gt;·      Whenever any data modification statement is issued then SQL Server fires it automatically.&lt;br /&gt;·       It cannot be explicitly invoked or executed, as in the case of stored procedures.&lt;br /&gt;·       Triggers can be nested up to 16 levels. The nesting of triggers occurs when a trigger performs an action that initiates another trigger.&lt;br /&gt;·      It prevents incorrect, unauthorized, and inconsistent changes in data.&lt;br /&gt;·      It cannot return data to the user.&lt;br /&gt;&lt;br /&gt;There are three types of triggers&lt;br /&gt;1) INSERT&lt;br /&gt;2) DELETE&lt;br /&gt;3) UPDATE&lt;/div&gt;&lt;div align=&quot;justify&quot;&gt;&lt;br /&gt;&lt;span style=&quot;font-size:130%;&quot;&gt;3. What is Normalization? Explain three normal forms?&lt;/span&gt;&lt;/div&gt;&lt;div align=&quot;justify&quot;&gt;&lt;br /&gt;Normalization is a scientific method of breaking down complex table structures in to simple table structures by using certain rules. Hence reduce redundancy in a table and eliminate inconsistency problems and disk space usage.&lt;/div&gt;&lt;div align=&quot;justify&quot;&gt;&lt;br /&gt;&lt;strong&gt;First Normal Form&lt;/strong&gt;&lt;br /&gt;A table is said to be in 1NF when each cell of the table contains precisely one value.&lt;br /&gt;Functional dependency&lt;br /&gt;If you have two attribute A and B, A is said to be functionally dependant on B, if for each value of B, there is exactly one value of A.&lt;/div&gt;&lt;div align=&quot;justify&quot;&gt; &lt;/div&gt;&lt;div align=&quot;justify&quot;&gt;&lt;strong&gt;&lt;/strong&gt; &lt;/div&gt;&lt;div align=&quot;justify&quot;&gt;&lt;br /&gt;&lt;strong&gt;Second Normal Form&lt;/strong&gt;&lt;br /&gt;Identify the functionally dependent keys and place them in a different table.&lt;/div&gt;&lt;div align=&quot;justify&quot;&gt;&lt;br /&gt;&lt;strong&gt;Third Normal Form&lt;/strong&gt;&lt;br /&gt;A relation is said to be 3NF when every non-key attribute is functionally dependant only on the primary key.&lt;/div&gt;&lt;div class=&quot;blogger-post-footer&quot;&gt;IF YOU HAVE ANY PROBLEM OR WANT MORE DATA PLS EMAIL ME.
ss.sabyasachipaul@gmail.com&lt;/div&gt;</content><link rel='edit' type='application/atom+xml' href='http://www.blogger.com/feeds/6790758757919456346/posts/default/7718673887001960112'/><link rel='self' type='application/atom+xml' href='http://www.blogger.com/feeds/6790758757919456346/posts/default/7718673887001960112'/><link rel='alternate' type='text/html' href='http://allmyneed.blogspot.com/2010/02/sql-server-questions-and-answers-page-5.html' title='SQL SERVER Questions And Answers Page 5'/><author><name>Unknown</name><email>noreply@blogger.com</email><gd:image rel='http://schemas.google.com/g/2005#thumbnail' width='16' height='16' src='https://img1.blogblog.com/img/b16-rounded.gif'/></author></entry><entry><id>tag:blogger.com,1999:blog-6790758757919456346.post-2647529537405743117</id><published>2010-02-01T03:44:00.000-08:00</published><updated>2010-02-01T03:48:55.512-08:00</updated><category scheme="http://www.blogger.com/atom/ns#" term="SQL SERVER"/><title type='text'>SQL SERVER Questions And Answers Page 4</title><content type='html'>&lt;div class=&quot;summary&quot; align=&quot;justify&quot;&gt;&lt;span style=&quot;font-size:130%;&quot;&gt;1. Explain about BCP and DTS.&lt;/span&gt;&lt;/div&gt;&lt;span style=&quot;font-size:130%;&quot;&gt;&lt;div class=&quot;summary&quot; align=&quot;justify&quot;&gt;&lt;br /&gt;&lt;/span&gt;BCP&lt;br /&gt;The transfer data from an external source to SQL Server is performed using Bulk Copy Program utility. The external source is a flat file. Data transfer from external source to SQL Server in BCP IN. The transfer of data from SQL Server to external source is BCP OUT.&lt;br /&gt;DTS&lt;br /&gt;Data transformation services can be used to import and export data between heterogeneous data sources and SQL Server. The external data sources include Visual FoxPro, MS Excel, Paradox, MS Access, Dbase, and text files.&lt;br /&gt;&lt;br /&gt;&lt;span style=&quot;font-size:130%;&quot;&gt;2.What are transactions and their properties?&lt;/span&gt;&lt;/div&gt;&lt;span style=&quot;font-size:130%;&quot;&gt;&lt;/span&gt;&lt;div class=&quot;summary&quot; align=&quot;justify&quot;&gt;&lt;br /&gt;A transaction is a sequence of operations performed together as a single unit of logical work. It has four properties.&lt;br /&gt;Atomicity – it states that either all the data modifications are performed or none are performed.&lt;br /&gt;Consistency - it states that all the data is in a consistent state after a successful completion of transaction.&lt;br /&gt;Isolation – it states any data modification made my concurrent transactions must be isolated from the modifications made by other concurrent transaction.&lt;br /&gt; Durability – it states that ay change made by a completed transaction remains permanently in the system.&lt;/div&gt;&lt;div class=&quot;summary&quot; align=&quot;justify&quot;&gt;&lt;br /&gt;&lt;span style=&quot;font-size:130%;&quot;&gt;3.Locking mechanism.&lt;/span&gt;&lt;br /&gt;&lt;br /&gt;SQL Server uses the concept of locking to ensure transactional integrity and database consistency. Locking, by functionally prevents users from accessing information being changed by other users. In a multi-user environment, Locking prevents users from changing the same data at the same time. In SQL Server locking is implemented automatically.&lt;br /&gt;&lt;br /&gt;SQL Server implements multi-granular locking, which allows transactions to lock different types of resources at different levels. SQL Server can lock the following resources.&lt;br /&gt;&lt;br /&gt;RID – is a row identifier that individually locks a row in a table.&lt;br /&gt;Key – is a rowlock with in an index.&lt;br /&gt;Page – is an 8k page or an index page.&lt;br /&gt;Extent – is a contiguous group of 8k data pages or index pages.&lt;br /&gt;Table – is the entire table, including all data and indexes.&lt;br /&gt;Database – is the complete database.&lt;br /&gt;                      &lt;br /&gt;&lt;strong&gt;Shared Locks&lt;/strong&gt;&lt;br /&gt; It is used for operations that do not change or update the data. This allows concurrent transactions to read a resource and no other transaction can modify the data on that resource.&lt;br /&gt;&lt;strong&gt;Update Locks&lt;/strong&gt;&lt;br /&gt; This lock is implemented when a transaction modify a row. Only one update lock is allowed on a resource at a time.&lt;br /&gt;&lt;strong&gt; Exclusive locks&lt;br /&gt; Intent locks&lt;br /&gt; Schema Locks&lt;/strong&gt;&lt;/div&gt;&lt;div class=&quot;blogger-post-footer&quot;&gt;IF YOU HAVE ANY PROBLEM OR WANT MORE DATA PLS EMAIL ME.
ss.sabyasachipaul@gmail.com&lt;/div&gt;</content><link rel='edit' type='application/atom+xml' href='http://www.blogger.com/feeds/6790758757919456346/posts/default/2647529537405743117'/><link rel='self' type='application/atom+xml' href='http://www.blogger.com/feeds/6790758757919456346/posts/default/2647529537405743117'/><link rel='alternate' type='text/html' href='http://allmyneed.blogspot.com/2010/02/sql-server-questions-and-answers-page-4.html' title='SQL SERVER Questions And Answers Page 4'/><author><name>Unknown</name><email>noreply@blogger.com</email><gd:image rel='http://schemas.google.com/g/2005#thumbnail' width='16' height='16' src='https://img1.blogblog.com/img/b16-rounded.gif'/></author></entry><entry><id>tag:blogger.com,1999:blog-6790758757919456346.post-866013232783165871</id><published>2010-02-01T03:37:00.000-08:00</published><updated>2010-02-01T03:43:07.294-08:00</updated><category scheme="http://www.blogger.com/atom/ns#" term="SQL SERVER"/><title type='text'>SQL SERVER Questions And Answers Page 3</title><content type='html'>&lt;div align=&quot;justify&quot;&gt;&lt;span style=&quot;font-size:130%;&quot;&gt;1. What are indexes?&lt;/span&gt;&lt;/div&gt;&lt;span style=&quot;font-size:130%;&quot;&gt;&lt;/span&gt;&lt;div align=&quot;justify&quot;&gt;&lt;br /&gt;To speed up data retrieval indexes are used. Indexes also enforce the uniqueness of rows.&lt;br /&gt;&lt;strong&gt;Advantages&lt;/strong&gt;&lt;br /&gt;1.      Improves the speed of execution.&lt;br /&gt;2.      Enforces uniqueness of data.&lt;br /&gt;3.      Speeds up joins between tables.&lt;br /&gt;&lt;strong&gt;Disadvantages&lt;br /&gt;&lt;/strong&gt;1.Takes disk space to store.&lt;br /&gt;2.Data modification takes longer.&lt;br /&gt;3.Takes time to create index.&lt;/div&gt;&lt;div align=&quot;justify&quot;&gt;&lt;br /&gt;&lt;strong&gt;&lt;u&gt;Types of indexes&lt;br /&gt;&lt;/u&gt;Clustered index&lt;/strong&gt;&lt;br /&gt;1.The data is physically sorted&lt;br /&gt;2.One clustered index can be created per table, so you should build it on attributes that have a high percentage of unique values and that are not modified often.&lt;br /&gt;&lt;strong&gt;Nonclustered index&lt;br /&gt;&lt;/strong&gt;1.The physical order of the rows is not the same as the index order.&lt;br /&gt;2.There can be as many as 249 nonclustered indexes per column. &lt;/div&gt;&lt;div align=&quot;justify&quot;&gt;&lt;br /&gt;&lt;span style=&quot;font-size:130%;&quot;&gt;2.What are views?&lt;/span&gt;&lt;/div&gt;&lt;span style=&quot;font-size:130%;&quot;&gt;&lt;/span&gt;&lt;div align=&quot;justify&quot;&gt;&lt;br /&gt;A view is a virtual table, which gives access to a subset of columns from one or more tables. It is a query stored as an object in the database. Hence a view is an object that derives it data from one or more tables.&lt;br /&gt;&lt;strong&gt;Advantages&lt;/strong&gt;&lt;br /&gt;1.A view serves as a security mechanism.&lt;br /&gt;2.A view simplifies the usage of complex queries.&lt;/div&gt;&lt;div align=&quot;justify&quot;&gt;&lt;br /&gt;&lt;span style=&quot;font-size:130%;&quot;&gt;3. What are store procedures and its advantages?&lt;/span&gt;&lt;/div&gt;&lt;span style=&quot;font-size:130%;&quot;&gt;&lt;div align=&quot;justify&quot;&gt;&lt;br /&gt;&lt;/span&gt;A stored procedure is collection or batch of Transact-SQL statements and control flow language that is stored under one name, and executed as single unit. It helps in improving the performance of a query. It is a precompiled object. As it is ready to execute no time is needed for parsing and compiling the procedure.&lt;br /&gt;&lt;strong&gt;Advantages&lt;br /&gt;&lt;/strong&gt;Improved performance – Applications do not have to compile the procedure over and over again.&lt;br /&gt;Reduction in network congestion – applications need not submit multiple SQL statements to server for the purpose of processing.&lt;br /&gt;Enhanced accuracy – SQL statements included in a procedure are designed by experienced programmers and are therefore more efficient, error free, and tested.&lt;br /&gt;Better security mechanism – users can be granted permission to execute a stored procedure even if they do not own it.&lt;br /&gt;&lt;strong&gt;&lt;u&gt;Types of stored procedures&lt;/u&gt;&lt;br /&gt;1.User-defined&lt;br /&gt;2.System defined&lt;/strong&gt;&lt;br /&gt;These are prefixed with sp_. These are for administrative purpose and are stored in the database and are accessible to all users.&lt;br /&gt;&lt;strong&gt;3.Temporary&lt;br /&gt;&lt;/strong&gt;These are prefixed with #, stored in tempdb and are automatically dropped when connection terminates.&lt;br /&gt;&lt;strong&gt;4.Remote&lt;/strong&gt;&lt;br /&gt;These are created and stored in remote servers and can accessed by users with appropriate permissions.&lt;br /&gt;&lt;strong&gt;5.Extended&lt;br /&gt;&lt;/strong&gt;These are dlls that are executed outside SQL Server. They are prefixed by xp_.&lt;/div&gt;&lt;div class=&quot;blogger-post-footer&quot;&gt;IF YOU HAVE ANY PROBLEM OR WANT MORE DATA PLS EMAIL ME.
ss.sabyasachipaul@gmail.com&lt;/div&gt;</content><link rel='edit' type='application/atom+xml' href='http://www.blogger.com/feeds/6790758757919456346/posts/default/866013232783165871'/><link rel='self' type='application/atom+xml' href='http://www.blogger.com/feeds/6790758757919456346/posts/default/866013232783165871'/><link rel='alternate' type='text/html' href='http://allmyneed.blogspot.com/2010/02/sql-server-questions-and-answers-page-3.html' title='SQL SERVER Questions And Answers Page 3'/><author><name>Unknown</name><email>noreply@blogger.com</email><gd:image rel='http://schemas.google.com/g/2005#thumbnail' width='16' height='16' src='https://img1.blogblog.com/img/b16-rounded.gif'/></author></entry><entry><id>tag:blogger.com,1999:blog-6790758757919456346.post-8123284198108954805</id><published>2010-02-01T03:32:00.000-08:00</published><updated>2010-02-01T03:36:37.130-08:00</updated><category scheme="http://www.blogger.com/atom/ns#" term="SQL SERVER"/><title type='text'>SQL SERVER Questions And Answers Page 2</title><content type='html'>&lt;div align=&quot;justify&quot;&gt;&lt;span style=&quot;font-size:130%;&quot;&gt;1. What is a sub query?&lt;/span&gt;&lt;/div&gt;&lt;div align=&quot;justify&quot;&gt;&lt;br /&gt;Sometimes the results of one query are dependant on the results of another query. For this purpose one query is nested inside another query, this is called as sub query.&lt;/div&gt;&lt;div align=&quot;justify&quot;&gt;&lt;br /&gt;&lt;span style=&quot;font-size:130%;&quot;&gt;2. What are the types of constraints?&lt;/span&gt;&lt;/div&gt;&lt;div align=&quot;justify&quot;&gt;&lt;br /&gt;You can enforce data integrity by using constraints. Constraints are divided in to five categories.&lt;br /&gt;&lt;strong&gt;Primary key constraint&lt;/strong&gt;&lt;br /&gt;A primary key constraint is defined on a column are a set of columns whose values uniquely identify the rows in a table. It cannot contain null values.&lt;br /&gt;&lt;strong&gt;Unique constraint&lt;br /&gt;&lt;/strong&gt;Unique constraints are used to enforce uniqueness on non-primary key columns. It allows null values but only one row can have a null value.Multiple unique constraints can be created on a table.&lt;br /&gt;&lt;strong&gt;Foreign Key constraint&lt;br /&gt;&lt;/strong&gt;You can use the foreign key constraint to remove the inconsistency in two tables when the data in the one table is dependant on the other table.&lt;br /&gt;&lt;strong&gt;Check constraint&lt;/strong&gt;&lt;br /&gt;It enforces domain integrity by restricting the values to be inserted in a column. It is possible to define multiple check constraints on a single column. These are evaluated in the order in which they are defined.&lt;br /&gt;&lt;strong&gt;Default Constraint&lt;br /&gt;&lt;/strong&gt;A default constraint can be use to assign a constant value to a column and the user need not insert values in to that column.&lt;br /&gt;&lt;br /&gt;&lt;span style=&quot;font-size:130%;&quot;&gt;3.What is a rule?&lt;/span&gt;&lt;br /&gt;&lt;br /&gt;The required integrity can be enforced by specifying a check constraint or by defining a rule. But check constraint modifies the table structure. The constraint can there fore be implemented using rules with out changing the table structure. This rule is applied before an insert or update statement .A rule must be bound to a column or a user-define d data type. This is done using stored procedure sp_bindrule. Rules do not apply to data that has already been inserted in the table. The existing values in tables do not have to meet the criteria specified by the rule.&lt;/div&gt;&lt;div class=&quot;blogger-post-footer&quot;&gt;IF YOU HAVE ANY PROBLEM OR WANT MORE DATA PLS EMAIL ME.
ss.sabyasachipaul@gmail.com&lt;/div&gt;</content><link rel='edit' type='application/atom+xml' href='http://www.blogger.com/feeds/6790758757919456346/posts/default/8123284198108954805'/><link rel='self' type='application/atom+xml' href='http://www.blogger.com/feeds/6790758757919456346/posts/default/8123284198108954805'/><link rel='alternate' type='text/html' href='http://allmyneed.blogspot.com/2010/02/sql-server-questions-and-answers-page-2.html' title='SQL SERVER Questions And Answers Page 2'/><author><name>Unknown</name><email>noreply@blogger.com</email><gd:image rel='http://schemas.google.com/g/2005#thumbnail' width='16' height='16' src='https://img1.blogblog.com/img/b16-rounded.gif'/></author></entry><entry><id>tag:blogger.com,1999:blog-6790758757919456346.post-6571142813468530452</id><published>2010-02-01T03:24:00.000-08:00</published><updated>2010-02-01T03:29:17.629-08:00</updated><category scheme="http://www.blogger.com/atom/ns#" term="SQL SERVER"/><title type='text'>SQL SERVER Questions And Answers Page 1</title><content type='html'>&lt;div align=&quot;justify&quot;&gt;&lt;span style=&quot;font-size:130%;&quot;&gt;1. What is an Entity?&lt;/span&gt; &lt;/div&gt;&lt;div align=&quot;justify&quot;&gt;&lt;br /&gt;The basic data item stored in database is called entity. An entity can be any object, item, place, person, concept, or activity about which data is stored.&lt;/div&gt;&lt;div align=&quot;justify&quot;&gt;&lt;br /&gt;&lt;span style=&quot;font-size:130%;&quot;&gt;2. What is an attribute?&lt;/span&gt;&lt;/div&gt;&lt;div align=&quot;justify&quot;&gt;&lt;br /&gt;An attribute is a property of an entity. It describes a part of an entity. Entity could have one or more attributes.&lt;/div&gt;&lt;div align=&quot;justify&quot;&gt;&lt;br /&gt;&lt;span style=&quot;font-size:130%;&quot;&gt;3. What is ER diagram?&lt;/span&gt;&lt;/div&gt;&lt;div align=&quot;justify&quot;&gt;&lt;br /&gt;An Entity Relationship Diagram is diagrammatic representation of the logical structure of a database system.&lt;/div&gt;&lt;div align=&quot;justify&quot;&gt;&lt;br /&gt;&lt;span style=&quot;font-size:130%;&quot;&gt;4.Describe the concept of keys.&lt;/span&gt;&lt;/div&gt;&lt;div align=&quot;justify&quot;&gt;&lt;br /&gt;&lt;strong&gt;Candidate key&lt;br /&gt;&lt;/strong&gt;An attribute that uniquely identifies a row is called candidate key. It is also called das surrogate key.&lt;br /&gt;&lt;strong&gt;Primary key&lt;/strong&gt;&lt;br /&gt;A candidate key that you choose to identify rows uniquely is called a primary key.&lt;br /&gt;Alternate key&lt;br /&gt;If there are multiple candidate keys in a table, the candidate keys that are chosen as primary key are called the alternate keys.&lt;br /&gt;&lt;strong&gt;Composite key&lt;br /&gt;&lt;/strong&gt;When the key that uniquely identifies the rows of a table is made up of more than one attribute, it is called as a composite key.&lt;br /&gt;&lt;strong&gt;Foreign key&lt;br /&gt;&lt;/strong&gt;Two tables can be related using a common attribute. When a primary key of one table is also available as an attribute in another related table it is called a foreign key.&lt;/div&gt;&lt;div align=&quot;justify&quot;&gt;&lt;br /&gt;&lt;span style=&quot;font-size:130%;&quot;&gt;5.What are joins?&lt;/span&gt;&lt;/div&gt;&lt;div align=&quot;justify&quot;&gt;&lt;br /&gt;Sometimes, data from multiple tables is to be displayed using select statement. For this purpose, the tables in use must have a column that is equated. This is termed as simple join or multiple join.Sometimes, you might want to display all records from one table and some from another. This type of join is called an outer join. An outer join is only possible between two tables.&lt;br /&gt;There are two types of outer join, namely left and right. In a left outer join, all the rows of the first table named in the FROM clause are displayed. In a right outer join all the rows from the second table mentioned in the FROM clause are displayed. In either case, all the matching rows from other table are displayed.&lt;/div&gt;&lt;div class=&quot;blogger-post-footer&quot;&gt;IF YOU HAVE ANY PROBLEM OR WANT MORE DATA PLS EMAIL ME.
ss.sabyasachipaul@gmail.com&lt;/div&gt;</content><link rel='edit' type='application/atom+xml' href='http://www.blogger.com/feeds/6790758757919456346/posts/default/6571142813468530452'/><link rel='self' type='application/atom+xml' href='http://www.blogger.com/feeds/6790758757919456346/posts/default/6571142813468530452'/><link rel='alternate' type='text/html' href='http://allmyneed.blogspot.com/2010/02/sql-server-questions-and-answers-page-1.html' title='SQL SERVER Questions And Answers Page 1'/><author><name>Unknown</name><email>noreply@blogger.com</email><gd:image rel='http://schemas.google.com/g/2005#thumbnail' width='16' height='16' src='https://img1.blogblog.com/img/b16-rounded.gif'/></author></entry><entry><id>tag:blogger.com,1999:blog-6790758757919456346.post-4855104166120243718</id><published>2010-01-27T01:06:00.000-08:00</published><updated>2010-01-27T01:10:21.647-08:00</updated><category scheme="http://www.blogger.com/atom/ns#" term="ORACLE HOT JOBS"/><title type='text'>ORACLE JOBS</title><content type='html'>TO GET ORACLE PL/SQL , D2K , WAREHOUSE , ETL  JOBS .....&lt;br /&gt;EXP  - 0 to 10 Yr ....&lt;br /&gt;CTC  - 1.8 L/A to 12 L/A .....&lt;div class=&quot;blogger-post-footer&quot;&gt;IF YOU HAVE ANY PROBLEM OR WANT MORE DATA PLS EMAIL ME.
ss.sabyasachipaul@gmail.com&lt;/div&gt;</content><link rel='edit' type='application/atom+xml' href='http://www.blogger.com/feeds/6790758757919456346/posts/default/4855104166120243718'/><link rel='self' type='application/atom+xml' href='http://www.blogger.com/feeds/6790758757919456346/posts/default/4855104166120243718'/><link rel='alternate' type='text/html' href='http://allmyneed.blogspot.com/2010/01/oracle-jobs.html' title='ORACLE JOBS'/><author><name>Unknown</name><email>noreply@blogger.com</email><gd:image rel='http://schemas.google.com/g/2005#thumbnail' width='16' height='16' src='https://img1.blogblog.com/img/b16-rounded.gif'/></author></entry><entry><id>tag:blogger.com,1999:blog-6790758757919456346.post-5559015082252066271</id><published>2010-01-21T21:48:00.000-08:00</published><updated>2010-01-21T22:20:46.672-08:00</updated><category scheme="http://www.blogger.com/atom/ns#" term="DATA WAREHOUSE"/><title type='text'>Why Data Warehouse</title><content type='html'>&lt;div align=&quot;justify&quot;&gt;&quot;&lt;strong&gt;We have mountains of data in this company, but we can’t access it.&lt;/strong&gt;&quot;&lt;br /&gt;&quot;&lt;strong&gt;We need to slice and dice the data every which way.&lt;/strong&gt;&quot;&lt;br /&gt;&quot;&lt;strong&gt;You’ve got to make it easy for business people to get at the data directly.&lt;/strong&gt;&quot;&lt;br /&gt;&quot;&lt;strong&gt;Just show me what is important.&lt;/strong&gt;&quot;&lt;br /&gt;&quot;&lt;strong&gt;It drives me crazy to have two people present the same business metrics at a meeting, but with different numbers.&lt;/strong&gt;&quot;&lt;br /&gt;&quot;&lt;strong&gt;We want people to use information to support more fact-based decision making.&lt;/strong&gt;&quot;&lt;/div&gt;&lt;div align=&quot;justify&quot;&gt;&lt;/div&gt;&lt;br /&gt;&lt;div align=&quot;justify&quot;&gt;&lt;/div&gt;&lt;div align=&quot;justify&quot;&gt;Let’s turn these business management quotations into data warehouse requirements.&lt;/div&gt;&lt;div align=&quot;justify&quot;&gt;&lt;strong&gt;&lt;/strong&gt;&lt;/div&gt;&lt;br /&gt;&lt;div align=&quot;justify&quot;&gt;&lt;strong&gt;The data warehouse must make an organization’s information easily accessible.&lt;/strong&gt;&lt;br /&gt;The contents of the data warehouse must be understandable. The data must be intuitive and obvious to the business user, not merely the developer. nderstandability implies legibility; the contents of the data warehouse need to be labeled meaningfully. Business users want to separate and combine the data in the warehouse in endless combinations, a process commonly referred to as slicing and dicing.&lt;br /&gt;The tools that access the data warehouse must be simple and easy to use. They also must return query results to the user with minimal wait times.&lt;br /&gt;&lt;div align=&quot;justify&quot;&gt;&lt;/div&gt;&lt;/div&gt;&lt;p align=&quot;justify&quot;&gt;&lt;strong&gt;The data warehouse must present the organization’s information consistently.&lt;/strong&gt;&lt;br /&gt;The data in the warehouse must be credible. Data must be carefully assembled from a variety of sources around the organization, cleansed, quality assured, and released only when it is fit for user consumption. Information from one business process should match with information from another. If two performance measures have the same name, then they must mean the same thing. Conversely, if two measures don’t mean the same thing, then they should be labeled differently. Consistent information means high-quality information. It means that all the data is accounted for and complete. Consistency also implies that common definitions for the contents of the data warehouse are available for users.&lt;/p&gt;&lt;p align=&quot;justify&quot;&gt;&lt;br /&gt;&lt;strong&gt;The data warehouse must be adaptive and resilient to change.&lt;br /&gt;&lt;/strong&gt;We simply can’t avoid change. User needs, business conditions, data, and technology are all subject to the shifting sands of time. The data warehouse must be designed to handle this inevitable change. Changes to the data warehouse should be graceful, meaning that they don’t invalidate existing data or applications. The existing data and applications should not be changed or disrupted when the business community asks new questions or new data is added to the warehouse. If descriptive data in the warehouse is modified, we must account for the changes appropriately.&lt;/p&gt;&lt;p align=&quot;justify&quot;&gt;&lt;strong&gt;The data warehouse must be a secure bastion that protects our information assets.&lt;br /&gt;&lt;/strong&gt;An organization’s informational crown jewels are stored in the data warehouse. At a minimum, the warehouse likely contains information about what we’re selling to whom at what price potentially harmful details in the hands of the wrong people. The data warehouse must effectively control access to the organization’s confidential information.&lt;/p&gt;&lt;p align=&quot;justify&quot;&gt;&lt;strong&gt;The data warehouse must serve as the foundation for improved decision making.&lt;br /&gt;&lt;/strong&gt;The data warehouse must have the right data in it to support decision making. There is only one true output from a data warehouse: the decisions that are made after the data warehouse has presented its evidence. These decisions deliver the business impact and value attributable to the warehouse. The original label that predates the data warehouse is still the best description of what we are designing: a decision support system.&lt;/p&gt;&lt;p align=&quot;justify&quot;&gt;&lt;/p&gt;&lt;p align=&quot;justify&quot;&gt;&lt;strong&gt;The business community must accept the data warehouse if it is to be deemed successful.&lt;br /&gt;&lt;/strong&gt;It doesn’t matter that we’ve built an elegant solution using best-of-breed products and platforms. If the business community has not embraced the data warehouse and continued to use it actively six months after training, then we have failed the acceptance test. Unlike an operational system rewrite, where business users have no choice but to use the new system, data warehouse usage is sometimes optional. Business user acceptance has more to do with simplicity than anything else. As this list illustrates, successful data warehousing demands much more than being a stellar DBA or technician. With a data warehousing initiative, we have one foot in our information technology (IT) comfort zone, while our other foot is on the unfamiliar turf of business users. We must straddle the two, modifying some of our tried-and-true skills to adapt to the unique demands of data warehousing. Clearly, we need to bring a bevy of skills to the party to behave like we’re a hybrid DBA/MBA.&lt;/p&gt;&lt;div class=&quot;blogger-post-footer&quot;&gt;IF YOU HAVE ANY PROBLEM OR WANT MORE DATA PLS EMAIL ME.
ss.sabyasachipaul@gmail.com&lt;/div&gt;</content><link rel='edit' type='application/atom+xml' href='http://www.blogger.com/feeds/6790758757919456346/posts/default/5559015082252066271'/><link rel='self' type='application/atom+xml' href='http://www.blogger.com/feeds/6790758757919456346/posts/default/5559015082252066271'/><link rel='alternate' type='text/html' href='http://allmyneed.blogspot.com/2010/01/why-data-warehouse.html' title='Why Data Warehouse'/><author><name>Unknown</name><email>noreply@blogger.com</email><gd:image rel='http://schemas.google.com/g/2005#thumbnail' width='16' height='16' src='https://img1.blogblog.com/img/b16-rounded.gif'/></author></entry><entry><id>tag:blogger.com,1999:blog-6790758757919456346.post-2952406491654319798</id><published>2009-11-26T23:42:00.000-08:00</published><updated>2009-11-27T00:05:54.473-08:00</updated><category scheme="http://www.blogger.com/atom/ns#" term="QUICK VIEW"/><title type='text'>Built-in Datatype Summary for 10g R 2(10.2)</title><content type='html'>&lt;div style=&quot;text-align: justify;&quot;&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;VARCHAR2(size [BYTE | CHAR])&lt;/span&gt;&lt;br /&gt;&lt;br /&gt;Variable-length character string having maximum length size bytes or characters. Maximum size is 4000 bytes or characters,and minimum is 1 byte or 1 character. You must specify size for VARCHAR2.&lt;br /&gt;BYTE indicates that the column will have byte length semantics;&lt;br /&gt;CHAR indicates that the column will have character semantics.&lt;br /&gt;&lt;br /&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;NVARCHAR2(size)&lt;/span&gt;&lt;br /&gt;&lt;br /&gt;Variable-length Unicode character string having maximum length size characters. The number of bytes can be up to two times size for AL16UTF16 encoding and three times size for UTF8 encoding. Maximum size is determined by the national character set definition, with an upper limit of 4000 bytes. You must specify size for NVARCHAR2.&lt;br /&gt;&lt;br /&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;NUMBER[(precision [, scale]])&lt;/span&gt;&lt;br /&gt;&lt;br /&gt;Number having precision p and scale s. The precision p can range from 1 to 38. The scale s can range from -84 to 127.&lt;br /&gt;&lt;br /&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;LONG&lt;/span&gt;&lt;br /&gt;&lt;br /&gt;Character data of variable length up to 2 gigabytes, or 2 bytes. Provided for backward compatibility.&lt;br /&gt;&lt;br /&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;DATE&lt;/span&gt;&lt;br /&gt;&lt;br /&gt;Valid date range from January 1, 4712 BC to December 31, 9999 AD. The default format is determined explicitly by the NLS_DATE_FORMAT parameter or implicitly by the NLS_TERRITORY parameter. The size is fixed at 7 bytes. This datatype contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND.It does not have fractional seconds or a time zone.&lt;br /&gt;&lt;br /&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;BINARY_FLOAT&lt;/span&gt;&lt;br /&gt;&lt;br /&gt;32-bit floating point number. This datatype requires 5 bytes,including the length byte.&lt;br /&gt;&lt;br /&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;BINARY_DOUBLE&lt;/span&gt;&lt;br /&gt;&lt;br /&gt;64-bit floating point number. This datatype requires 9 bytes,including the length byte.&lt;br /&gt;&lt;br /&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;TIMESTAMP [(fractional_seconds)]&lt;/span&gt;&lt;br /&gt;&lt;br /&gt;Year, month, and day values of date, as well as hour, minute,and second values of time, where fractional_seconds_precision is the number of digits in the fractional part of the SECOND datetime field. Accepted values of fractional_seconds_precision are 0 to 9. The default is 6. The default format is determined explicitly by the NLS_DATE_FORMAT parameter or implicitly by the NLS_TERRITORY parameter. The sizes varies from 7 to 11 bytes, depending on the precision. This datatype contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. It contains fractional seconds but does not have a time zone.&lt;br /&gt;&lt;br /&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;TIMESTAMP [(fractional_seconds)] WITH TIME ZONE&lt;/span&gt;&lt;br /&gt;&lt;br /&gt;All values of TIMESTAMP as well as time zone displacement value, where fractional_seconds_precision is the number of digits in the fractional part of the SECOND datetime field. Accepted values are 0 to 9. The default is 6. The default format is determined explicitly by the NLS_DATE_FORMAT parameter or implicitly by the NLS_TERRITORY parameter. The size is fixed at 13 bytes. This datatype contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR, and TIMEZONE_MINUTE. It has fractional seconds and an explicit time zone.&lt;br /&gt;&lt;br /&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;TIMESTAMP [(fractional_seconds)] WITH LOCAL TIME ZONE&lt;/span&gt;&lt;br /&gt;&lt;br /&gt;All values of TIMESTAMP WITH TIME ZONE, with the following&lt;br /&gt;exceptions:&lt;br /&gt;** Data is normalized to the database time zone when it is stored in the database.&lt;br /&gt;** When the data is retrieved, users see the data in the session time zone.&lt;br /&gt;The default format is determined explicitly by the NLS_DATE_FORMAT parameter or implicitly by the NLS_TERRITORY parameter. The sizes varies from 7 to 11 bytes, depending on the precision.&lt;br /&gt;&lt;br /&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;INTERVAL YEAR [(year_precision)] TO MONTH&lt;/span&gt;&lt;br /&gt;&lt;br /&gt;Stores a period of time in years and months, where year_precision is the number of digits in the YEAR datetime field.Accepted values are 0 to 9. The default is 2. The size is fixed at 5 bytes.&lt;br /&gt;&lt;br /&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds)]&lt;/span&gt;&lt;br /&gt;&lt;br /&gt;Stores a period of time in days, hours, minutes, and seconds,where day_precision is the maximum number of digits in the DAY datetime field. Accepted values are 0 to 9. The default is 2.And fractional_seconds_precision is the number of digits in the fractional part of the SECOND field. Accepted values are 0 to 9. The default is 6.The size is fixed at 11 bytes.&lt;br /&gt;&lt;br /&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;RAW(size)&lt;/span&gt;&lt;br /&gt;&lt;br /&gt;Raw binary data of length size bytes. Maximum size is 2000 bytes. You must specify size for a RAW value.&lt;br /&gt;&lt;br /&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;LONG RAW&lt;/span&gt;&lt;br /&gt;&lt;br /&gt;Raw binary data of variable length up to 2 gigabytes.&lt;br /&gt;&lt;br /&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;ROWID&lt;/span&gt;&lt;br /&gt;&lt;br /&gt;Base 64 string representing the unique address of a row in its table. This datatype is primarily for values returned by the ROWID pseudocolumn.&lt;br /&gt;&lt;br /&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;UROWID [(size)]&lt;/span&gt;&lt;br /&gt;&lt;br /&gt;Base 64 string representing the logical address of a row of an index-organized table. The optional size is the size of a column of type UROWID. The maximum size and default is 4000 bytes.&lt;br /&gt;&lt;br /&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;CHAR [(size [BYTE | CHAR])]&lt;/span&gt;&lt;br /&gt;&lt;br /&gt;Fixed-length character data of length size bytes. Maximum size is 2000 bytes or characters. Default and minimum size is 1 byte.BYTE and CHAR have the same semantics as for VARCHAR2.&lt;br /&gt;&lt;br /&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;NCHAR[(size)]&lt;/span&gt;&lt;br /&gt;&lt;br /&gt;Fixed-length character data of length size characters. The number of bytes can be up to two times size for AL16UTF16 encoding and three times size for UTF8 encoding. Maximum size is determined by the national character set definition, with an upper limit of 2000 bytes. Default and minimum size is 1 character.&lt;br /&gt;&lt;br /&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;CLOB&lt;/span&gt;&lt;br /&gt;&lt;br /&gt;A character large object containing single-byte or multibyte characters. Both fixed-width and variable-width character sets are supported, both using the database character set. Maximum size is (4 gigabytes - 1) * (database block size).&lt;br /&gt;&lt;br /&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;NCLOB&lt;/span&gt;&lt;br /&gt;&lt;br /&gt;A character large object containing Unicode characters. Both fixed-width and variable-width character sets are supported,both using the database national character set. Maximum size is (4 gigabytes - 1) * (database block size). Stores national character set data.&lt;br /&gt;&lt;br /&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;BLOB&lt;/span&gt;&lt;br /&gt;&lt;br /&gt;A binary large object. Maximum size is (4 gigabytes - 1) * (database block size).&lt;br /&gt;&lt;br /&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;BFILE&lt;/span&gt;&lt;br /&gt;&lt;br /&gt;Contains a locator to a large binary file stored outside the database. Enables byte stream I/O access to external LOBs residing on the database server. Maximum size is 4 gigabytes.&lt;br /&gt;&lt;br /&gt;&lt;br /&gt;&lt;/div&gt;&lt;div class=&quot;blogger-post-footer&quot;&gt;IF YOU HAVE ANY PROBLEM OR WANT MORE DATA PLS EMAIL ME.
ss.sabyasachipaul@gmail.com&lt;/div&gt;</content><link rel='edit' type='application/atom+xml' href='http://www.blogger.com/feeds/6790758757919456346/posts/default/2952406491654319798'/><link rel='self' type='application/atom+xml' href='http://www.blogger.com/feeds/6790758757919456346/posts/default/2952406491654319798'/><link rel='alternate' type='text/html' href='http://allmyneed.blogspot.com/2009/11/built-in-datatype-summary-for-10g-r.html' title='Built-in Datatype Summary for 10g R 2(10.2)'/><author><name>Unknown</name><email>noreply@blogger.com</email><gd:image rel='http://schemas.google.com/g/2005#thumbnail' width='16' height='16' src='https://img1.blogblog.com/img/b16-rounded.gif'/></author></entry><entry><id>tag:blogger.com,1999:blog-6790758757919456346.post-1033993966432020729</id><published>2009-11-26T22:59:00.000-08:00</published><updated>2009-11-26T23:39:09.022-08:00</updated><category scheme="http://www.blogger.com/atom/ns#" term="SYNTAX"/><title type='text'>Syntax for SQL functions</title><content type='html'>&lt;span style=&quot;font-weight: bold;&quot;&gt;Page 1 :-&lt;br /&gt;&lt;br /&gt;ABS&lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-style: italic;&quot;&gt;ABS(n)&lt;br /&gt;&lt;br /&gt;&lt;/span&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;ACOS&lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-style: italic;&quot;&gt;ACOS(n)&lt;br /&gt;&lt;br /&gt;&lt;/span&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;ADD_MONTHS&lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-style: italic;&quot;&gt;ADD_MONTHS(date, integer)&lt;br /&gt;&lt;br /&gt;&lt;/span&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;analytic_function&lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-style: italic;&quot;&gt;analytic_function([ arguments ]) &lt;/span&gt;&lt;span style=&quot;font-style: italic;&quot;&gt;OVER (analytic_clause)&lt;br /&gt;&lt;br /&gt;&lt;/span&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;APPENDCHILDXML&lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-style: italic;&quot;&gt;APPENDCHILDXML &lt;/span&gt;&lt;span style=&quot;font-style: italic;&quot;&gt;( XMLType_instance, XPath_string, value_expr [, namespace_string ]&lt;/span&gt;&lt;span style=&quot;font-style: italic;&quot;&gt;)&lt;br /&gt;&lt;br /&gt;&lt;/span&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;ASCII&lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-style: italic;&quot;&gt;ASCII(char)&lt;br /&gt;&lt;br /&gt;&lt;/span&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;ASCIISTR&lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-style: italic;&quot;&gt;ASCIISTR(char)&lt;br /&gt;&lt;br /&gt;&lt;/span&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;ASIN&lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-style: italic;&quot;&gt;ASIN(n)&lt;br /&gt;&lt;br /&gt;&lt;/span&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;ATAN&lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-style: italic;&quot;&gt;ATAN(n)&lt;br /&gt;&lt;br /&gt;&lt;/span&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;ATAN2&lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-style: italic;&quot;&gt;ATAN2(n1 { , | / } n2)&lt;/span&gt;&lt;br /&gt;&lt;br /&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;AVG&lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-style: italic;&quot;&gt;AVG([ DISTINCT | ALL ] expr) &lt;/span&gt;[ OVER(analytic_clause) ]&lt;br /&gt;&lt;br /&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;BFILENAME&lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-style: italic;&quot;&gt;BFILENAME(&#39;directory&#39;, &#39;filename&#39;)&lt;br /&gt;&lt;br /&gt;&lt;/span&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;BIN_TO_NUM&lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-style: italic;&quot;&gt;BIN_TO_NUM(expr [, expr ]... )&lt;br /&gt;&lt;br /&gt;&lt;/span&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;BITAND&lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-style: italic;&quot;&gt;BITAND(expr1, expr2)&lt;br /&gt;&lt;br /&gt;&lt;/span&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;CARDINALITY&lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-style: italic;&quot;&gt;CARDINALITY(nested_table)&lt;br /&gt;&lt;br /&gt;&lt;/span&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;CAST&lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-style: italic;&quot;&gt;CAST({ expr | MULTISET (subquery) } AS type_name)&lt;br /&gt;&lt;br /&gt;&lt;/span&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;CEIL&lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-style: italic;&quot;&gt;CEIL(n)&lt;br /&gt;&lt;br /&gt;&lt;/span&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;CHARTOROWID&lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-style: italic;&quot;&gt;CHARTOROWID(char)&lt;br /&gt;&lt;br /&gt;&lt;/span&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;CHR&lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-style: italic;&quot;&gt;CHR(n [ USING NCHAR_CS ])&lt;br /&gt;&lt;br /&gt;&lt;/span&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;CLUSTER_ID&lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-style: italic;&quot;&gt;CLUSTER_ID ( [ schema . ] model mining_attribute_clause )&lt;br /&gt;&lt;br /&gt;&lt;/span&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;CLUSTER_PROBABILITY&lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-style: italic;&quot;&gt;CLUSTER_PROBABILITY ( [ schema . ] model [ , cluster_id ] mining_attribute_clause )&lt;/span&gt;&lt;br /&gt;&lt;br /&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;CLUSTER_SET&lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-style: italic;&quot;&gt;CLUSTER_SET ( [ schema . ] model [ , topN [ , cutoff ] ] mining_attribute_clause ) COALESCE&lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-style: italic;&quot;&gt;COALESCE(expr [, expr ]...)&lt;/span&gt;&lt;br /&gt;&lt;br /&gt;&lt;br /&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;COLLECT&lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-style: italic;&quot;&gt;COLLECT (column)&lt;br /&gt;&lt;br /&gt;&lt;/span&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;COMPOSE&lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-style: italic;&quot;&gt;COMPOSE(char)&lt;br /&gt;&lt;br /&gt;&lt;/span&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;CONCAT&lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-style: italic;&quot;&gt;CONCAT(char1, char2)&lt;br /&gt;&lt;br /&gt;&lt;/span&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;CONVERT&lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-style: italic;&quot;&gt;CONVERT(char, dest_char_set[, source_char_set ])&lt;br /&gt;&lt;br /&gt;&lt;/span&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;CORR&lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-style: italic;&quot;&gt;CORR(expr1, expr2) [ OVER (analytic_clause) ]&lt;br /&gt;&lt;br /&gt;&lt;/span&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;CORR_K, CORR_S&lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-style: italic;&quot;&gt;{ CORR_K | CORR_S } (expr1, expr2 [, { COEFFICIENT | ONE_SIDED_SIG | ONE_SIDED_SIG_POS | ONE_SIDED_SIG_NEG | TWO_SIDED_SIG } ] )&lt;/span&gt;&lt;div class=&quot;blogger-post-footer&quot;&gt;IF YOU HAVE ANY PROBLEM OR WANT MORE DATA PLS EMAIL ME.
ss.sabyasachipaul@gmail.com&lt;/div&gt;</content><link rel='edit' type='application/atom+xml' href='http://www.blogger.com/feeds/6790758757919456346/posts/default/1033993966432020729'/><link rel='self' type='application/atom+xml' href='http://www.blogger.com/feeds/6790758757919456346/posts/default/1033993966432020729'/><link rel='alternate' type='text/html' href='http://allmyneed.blogspot.com/2009/11/syntax-for-sql-functions.html' title='Syntax for SQL functions'/><author><name>Unknown</name><email>noreply@blogger.com</email><gd:image rel='http://schemas.google.com/g/2005#thumbnail' width='16' height='16' src='https://img1.blogblog.com/img/b16-rounded.gif'/></author></entry><entry><id>tag:blogger.com,1999:blog-6790758757919456346.post-7979297976516860389</id><published>2009-09-09T02:31:00.000-07:00</published><updated>2009-09-09T02:58:46.376-07:00</updated><category scheme="http://www.blogger.com/atom/ns#" term="D2K INTERVIEW"/><title type='text'>Before Facing PL/SQL Interview</title><content type='html'># &lt;span style=&quot;font-weight: bold;&quot;&gt;SQL&lt;/span&gt; : The language used to access a relational database is called SQL&lt;br /&gt;&lt;br /&gt;# &lt;span style=&quot;font-weight: bold;&quot;&gt;DRL&lt;/span&gt;- (I)Select&lt;br /&gt;&lt;br /&gt;# &lt;span style=&quot;font-weight: bold;&quot;&gt;DML&lt;/span&gt;-&lt;br /&gt;&lt;br /&gt;(I) Insert&lt;br /&gt;&lt;br /&gt;(II) Update&lt;br /&gt;&lt;br /&gt;(III) Delete&lt;br /&gt;&lt;br /&gt;# &lt;span style=&quot;font-weight: bold;&quot;&gt;DDL&lt;/span&gt;-&lt;br /&gt;&lt;br /&gt;(I) Create&lt;br /&gt;&lt;br /&gt;(II) Alter&lt;br /&gt;&lt;br /&gt;(III) Drop&lt;br /&gt;&lt;br /&gt;(IV) Rename&lt;br /&gt;&lt;br /&gt;(V) Truncate&lt;br /&gt;&lt;br /&gt;# &lt;span style=&quot;font-weight: bold;&quot;&gt;TCL&lt;/span&gt;-&lt;br /&gt;&lt;br /&gt;(I) Commit&lt;br /&gt;&lt;br /&gt;(II) Savepoint&lt;br /&gt;&lt;br /&gt;(III) Rollback&lt;br /&gt;&lt;br /&gt;&lt;br /&gt;# &lt;span style=&quot;font-weight: bold;&quot;&gt;DCL&lt;/span&gt;-&lt;br /&gt;&lt;br /&gt;(I)Grant&lt;br /&gt;&lt;br /&gt;(II) Revoke&lt;br /&gt;&lt;br /&gt;&lt;br /&gt;# &lt;span style=&quot;font-weight: bold;&quot;&gt;NULL&lt;/span&gt;: In database terms, nothing is called null.&lt;br /&gt;&lt;br /&gt;# &lt;span style=&quot;font-weight: bold;&quot;&gt;DISTINCT&lt;/span&gt;: To obtain unique value or a column contains duplicate value.&lt;br /&gt;&lt;br /&gt;# &lt;span style=&quot;font-weight: bold;&quot;&gt;PRIMARY KEY&lt;/span&gt;: Is an column used for identifying the uniqueness of each row in table.&lt;br /&gt;&lt;br /&gt;# &lt;span style=&quot;font-weight: bold;&quot;&gt;FORGIN KEY&lt;/span&gt;: Then common column appering in the chilled table is referenced as forgin key .&lt;br /&gt;&lt;br /&gt;# &lt;span style=&quot;font-weight: bold;&quot;&gt;JOIN&lt;/span&gt; : Is a query that combaine row from two are more table,view,M.view,&lt;br /&gt;&lt;br /&gt;# &lt;span style=&quot;font-weight: bold;&quot;&gt;CARTATION PRODUCT&lt;/span&gt;: Is a join query without join condition.&lt;br /&gt;&lt;br /&gt;# &lt;span style=&quot;font-weight: bold;&quot;&gt;EQUI/SIMPAL/INNER JOIN&lt;/span&gt; : Is a join query with a join condition .Containg an equality (=) oprator.&lt;br /&gt;&lt;br /&gt;# &lt;span style=&quot;font-weight: bold;&quot;&gt;TABLE ALIAS&lt;/span&gt; : Can be used table of names.&lt;br /&gt;&lt;br /&gt;# &lt;span style=&quot;font-weight: bold;&quot;&gt;SELF JOIN&lt;/span&gt; : It’s a join of table to itself.&lt;br /&gt;&lt;br /&gt;# &lt;span style=&quot;font-weight: bold;&quot;&gt;NON EQUI JOIN&lt;/span&gt;: When join condition that are executed when such as no column in one table cooesponding directly to a column in other tables.&lt;br /&gt;&lt;br /&gt;# &lt;span style=&quot;font-weight: bold;&quot;&gt;OUTER JOIN&lt;/span&gt;(which table want null use + sign) : An outer join extends the result of&lt;br /&gt;Simpal join.&lt;br /&gt;&lt;br /&gt;# &lt;span style=&quot;font-weight: bold;&quot;&gt;ANSI/ISO JOIN SYNTAX&lt;/span&gt; : join table name on join condition  From Emp join dept&lt;br /&gt;on Emp.deptno=Dept.deptno;&lt;br /&gt;&lt;br /&gt;# How many comparision do you need : - If you join N table then N-1 join condition here.&lt;br /&gt;&lt;br /&gt;# &lt;span style=&quot;font-weight: bold;&quot;&gt;NATURAL JOIN&lt;/span&gt;: Is a join between two table where oracles join the table according to the columns in the two table sharing in the same name.&lt;br /&gt;&lt;br /&gt;# &lt;span style=&quot;font-weight: bold;&quot;&gt;CROSS JOIN&lt;/span&gt;: ISO syantax producing creationproduct through use of cross join.&lt;br /&gt;&lt;br /&gt;# &lt;span style=&quot;font-weight: bold;&quot;&gt;GROUP FUNCTON&lt;/span&gt; : The data from main query can be displayed by using a group function is a sub query.&lt;br /&gt;&lt;br /&gt;# &lt;span style=&quot;font-weight: bold;&quot;&gt;SUB QUERY&lt;/span&gt; : Query within a query OR select statement nested within select statement.&lt;br /&gt;&lt;br /&gt;# &lt;span style=&quot;font-weight: bold;&quot;&gt;TYPES OF SUB QUERY&lt;/span&gt;:&lt;br /&gt;&lt;br /&gt;  1.Single Row Sub Query :Main query expect return only one value.&lt;br /&gt;  2.Multipel Row Sub Query : Return more the one row.&lt;br /&gt;  3.Inline(Inline view) Row Sub Query :Is a sub query use in from clause.&lt;br /&gt;  4.Multipel Column Sub Query : Return more then one column of return data in condition.&lt;br /&gt;&lt;br /&gt;# &lt;span style=&quot;font-weight: bold;&quot;&gt;Constrints&lt;/span&gt; : Are used to define an intigrity constraints, as rule that restrict the value in Database.&lt;br /&gt;&lt;br /&gt;# &lt;span style=&quot;font-weight: bold;&quot;&gt;Data Intigrity&lt;/span&gt;: It’s a state in which all the data value stored in database collect.&lt;br /&gt;&lt;br /&gt;  1.Entity Intigrity : It define a row as unique Enitity for particular table.&lt;br /&gt;  2.Domain Intigrity :Validates the entries for a given column.&lt;br /&gt;  3.Refrential Intigrity :Define relationship between tables where record are entered are deleted.&lt;br /&gt;  4.User define Intigrity : allow to difine specfic business rule that to not full into any one of the other Intigrity catgories.&lt;br /&gt;&lt;br /&gt;# &lt;span style=&quot;font-weight: bold;&quot;&gt;UNIQUE KEY&lt;/span&gt; : Enforce uniquness on value in constraints columns.&lt;br /&gt;&lt;br /&gt;# &lt;span style=&quot;font-weight: bold;&quot;&gt;CHECK&lt;/span&gt; : Enforce to value add to constraint column must be present static list.&lt;br /&gt;&lt;br /&gt;# &lt;span style=&quot;font-weight: bold;&quot;&gt;NOT NULL&lt;/span&gt; : Column such that the column may not be null for any row.&lt;br /&gt;&lt;br /&gt;# &lt;span style=&quot;font-weight: bold;&quot;&gt;SEQUENCE&lt;/span&gt; : Database object that genrate integer according to rules specified at time of&lt;br /&gt;sequence is creation.&lt;br /&gt;&lt;br /&gt;# &lt;span style=&quot;font-weight: bold;&quot;&gt;INDEXES&lt;/span&gt; : Are schema objects that provide a mapping of all the values in the table column.&lt;br /&gt;&lt;br /&gt;  1.B-TREE INDEX: Nornaml Index&lt;br /&gt;  2.BETMAP INDEX : Store rowed associated with key value Bitmap.&lt;br /&gt;  3.PARTIONED INDEX : Table Data can pe partion is posible&lt;br /&gt;  4.FUNCTION BASED INDEX :They are based on expression&lt;br /&gt;  5.DOMAIN INDEX :Instance of application specific index of type index type.&lt;br /&gt;  6.REVECE KEY INDEX :&lt;br /&gt;&lt;br /&gt;# &lt;span style=&quot;font-weight: bold;&quot;&gt;Synonmys&lt;/span&gt;: Schema objects which act as an alternate name for an existing objects.&lt;br /&gt;&lt;br /&gt;  1.Private Synonmys&lt;br /&gt;  2.Public Synonmys&lt;br /&gt;&lt;br /&gt;#&lt;span style=&quot;font-weight: bold;&quot;&gt; EXISTS OPRATER&lt;/span&gt; : Test to existence of row in sequence.&lt;br /&gt;&lt;br /&gt;# &lt;span style=&quot;font-weight: bold;&quot;&gt;ROWNUM&lt;/span&gt;: Is a result set row postion show after transaction.&lt;br /&gt;&lt;br /&gt;# &lt;span style=&quot;font-weight: bold;&quot;&gt;SCHEMA&lt;/span&gt;: Is a logical grouping of database objects based on userwho own the objects.&lt;br /&gt;&lt;br /&gt;# &lt;span style=&quot;font-weight: bold;&quot;&gt;PL/SQL &lt;/span&gt;: Stands for procedurial language Extention to SQL.&lt;br /&gt;&lt;br /&gt;  1.Anonymoys Block :&lt;br /&gt;  2.Named Block:&lt;br /&gt;  3.Sub Programed Block:&lt;br /&gt;&lt;br /&gt;# &lt;span style=&quot;font-weight: bold;&quot;&gt;CURSSOR&lt;/span&gt;: Used to process multipal row retrival from database(with select statement).&lt;br /&gt;OR&lt;br /&gt;Is a Handler or Pointer to the context area.&lt;br /&gt;1.Implicity Cursor :&lt;br /&gt;  2.Ecplicit Cursor&lt;br /&gt;  3.Refrence Cursor&lt;br /&gt;&lt;br /&gt;# &lt;span style=&quot;font-weight: bold;&quot;&gt;CURSOR ATTRIBUTE&lt;/span&gt; :&lt;br /&gt;&lt;br /&gt;  1.NOT FOUND&lt;br /&gt;  2.FOUND&lt;br /&gt;  3.ISOPEN&lt;br /&gt;  4.ROWCOUNT&lt;br /&gt;&lt;br /&gt;# &lt;span style=&quot;font-weight: bold;&quot;&gt;EXCEPTION&lt;/span&gt;: To handle unpredictable situation ,plsql provide mechnisum is known as Exception.&lt;br /&gt;&lt;br /&gt;  1.EXCEPTION HANDLING :&lt;br /&gt;  2.EXCEPTION TRAPING :&lt;br /&gt;  3.EXCEPTION PROPOGATION :&lt;br /&gt;&lt;br /&gt;Genral Categories:&lt;br /&gt;1) System define Exception&lt;br /&gt;2)User Define Exception&lt;br /&gt;&lt;br /&gt;# &lt;span style=&quot;font-weight: bold;&quot;&gt;PROCEDURE&lt;/span&gt; : Is a named program unit that can be called on directly buy its name.&lt;br /&gt;( Not pass littreal)&lt;br /&gt;&lt;br /&gt;# &lt;span style=&quot;font-weight: bold;&quot;&gt;FUNCTION&lt;/span&gt; : Is a named program unit that return atlest singal value.&lt;br /&gt;&lt;br /&gt;# &lt;span style=&quot;font-weight: bold;&quot;&gt;PACKAGE&lt;/span&gt; : Is simpaly collection of related type procedure,functions.&lt;br /&gt;&lt;br /&gt;  1.SPECFFICATION&lt;br /&gt;  2.BODY&lt;br /&gt;&lt;br /&gt;# &lt;span style=&quot;font-weight: bold;&quot;&gt;TRIGEER&lt;/span&gt;: Is a named pl/sql block stored in database and executed implicit when trigeer event is occurs.&lt;br /&gt;&lt;br /&gt;# &lt;span style=&quot;font-weight: bold;&quot;&gt;COLLECTION&lt;/span&gt; : Manupulate many variable at once as one unit DATA TYPE LIKE.&lt;br /&gt;&lt;br /&gt;  1.INDEX BY TABLE : Non scaler Type&lt;br /&gt;  2.NEXTED TABLE : created sequential key&amp;amp; like key can not be negative.&lt;br /&gt;  3.VARRY: Is data type ,simlar ina rray.&lt;br /&gt;&lt;br /&gt;# &lt;span style=&quot;font-weight: bold;&quot;&gt;Query&lt;/span&gt; : Its an opration that retrive data from one are more table or view.&lt;div class=&quot;blogger-post-footer&quot;&gt;IF YOU HAVE ANY PROBLEM OR WANT MORE DATA PLS EMAIL ME.
ss.sabyasachipaul@gmail.com&lt;/div&gt;</content><link rel='edit' type='application/atom+xml' href='http://www.blogger.com/feeds/6790758757919456346/posts/default/7979297976516860389'/><link rel='self' type='application/atom+xml' href='http://www.blogger.com/feeds/6790758757919456346/posts/default/7979297976516860389'/><link rel='alternate' type='text/html' href='http://allmyneed.blogspot.com/2009/09/before-facing-plsql-interview.html' title='Before Facing PL/SQL Interview'/><author><name>Unknown</name><email>noreply@blogger.com</email><gd:image rel='http://schemas.google.com/g/2005#thumbnail' width='16' height='16' src='https://img1.blogblog.com/img/b16-rounded.gif'/></author></entry><entry><id>tag:blogger.com,1999:blog-6790758757919456346.post-2442347205743706637</id><published>2009-08-06T01:13:00.000-07:00</published><updated>2009-08-06T01:27:53.403-07:00</updated><category scheme="http://www.blogger.com/atom/ns#" term="SQL TUNING"/><title type='text'>USE EXISTS IN PLACE OF DISTINCT</title><content type='html'>&lt;div style=&quot;text-align: justify;&quot;&gt;The DISTINCT combination of deptno and dname columns have to be displayed. Only those rows need be displayed,  whose deptno exists in the EMP table.&lt;br /&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;Query 1&lt;/span&gt;&lt;br /&gt; &lt;span style=&quot;font-style: italic;&quot;&gt;SELECT DISTINCT E.deptno,D.dname &lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-style: italic;&quot;&gt;   FROM emp E,dept D &lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-style: italic;&quot;&gt;   WHERE D.deptno = E.deptno;&lt;/span&gt;&lt;br /&gt; &lt;/div&gt;&lt;table style=&quot;text-align: left; margin-left: 0px; margin-right: 0px;&quot; border=&quot;1&quot;&gt;&lt;tbody&gt;&lt;tr&gt;&lt;br /&gt;&lt;th&gt;OPERATION&lt;/th&gt;&lt;br /&gt;&lt;th&gt;OPTIONS&lt;/th&gt;&lt;br /&gt;&lt;th&gt;OBJECT_NAME&lt;/th&gt;&lt;br /&gt;&lt;th&gt;COST&lt;/th&gt;&lt;br /&gt;&lt;/tr&gt;&lt;br /&gt;&lt;tr&gt;&lt;br /&gt;&lt;td&gt;SELECT STATEMENT&lt;/td&gt;&lt;br /&gt;&lt;td&gt;  &lt;br /&gt;&lt;/td&gt;&lt;br /&gt;&lt;td&gt;  &lt;br /&gt;&lt;/td&gt;&lt;br /&gt;&lt;td&gt;14659&lt;/td&gt;&lt;br /&gt;&lt;/tr&gt;&lt;br /&gt;&lt;tr&gt;&lt;br /&gt;&lt;td&gt;SORT&lt;/td&gt;&lt;br /&gt;&lt;td&gt;UNIQUE&lt;/td&gt;&lt;br /&gt;&lt;td&gt;   &lt;br /&gt;&lt;/td&gt;&lt;br /&gt;&lt;td&gt;14659&lt;/td&gt;&lt;br /&gt;&lt;/tr&gt;&lt;br /&gt;&lt;tr&gt;&lt;br /&gt;&lt;td&gt;HASH JOIN&lt;/td&gt;&lt;br /&gt;&lt;td&gt;  &lt;br /&gt;&lt;/td&gt;&lt;br /&gt;&lt;td&gt;  &lt;br /&gt;&lt;/td&gt;&lt;br /&gt;&lt;td&gt;1000&lt;/td&gt;&lt;br /&gt;&lt;/tr&gt;&lt;br /&gt;&lt;tr&gt;&lt;br /&gt;&lt;td&gt;TABLE ACCESS&lt;/td&gt;&lt;br /&gt;&lt;td&gt;FULL&lt;/td&gt;&lt;br /&gt;&lt;td&gt;DEPT&lt;/td&gt;&lt;br /&gt;&lt;td&gt;1&lt;/td&gt;&lt;br /&gt;&lt;/tr&gt;&lt;br /&gt;&lt;tr&gt;&lt;br /&gt;&lt;td&gt;TABLE ACCESS&lt;/td&gt;&lt;br /&gt;&lt;td&gt;FULL&lt;/td&gt;&lt;br /&gt;&lt;td&gt;EMP&lt;/td&gt;&lt;br /&gt;&lt;td&gt;822&lt;/td&gt;&lt;br /&gt;&lt;/tr&gt;&lt;br /&gt;&lt;/tbody&gt;&lt;/table&gt;&lt;div style=&quot;text-align: justify;&quot;&gt;&lt;br /&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;Query 2&lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-style: italic;&quot;&gt;SELECT D.deptno,D.dname &lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-style: italic;&quot;&gt;   FROM dept D &lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-style: italic;&quot;&gt;   WHERE EXISTS ( SELECT &#39;X&#39; &lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-style: italic;&quot;&gt;        FROM emp E &lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-style: italic;&quot;&gt;        WHERE E.deptno = D.deptno );&lt;/span&gt;&lt;br /&gt;&lt;/div&gt;&lt;table style=&quot;text-align: left; margin-left: 0px; margin-right: 0px;&quot; border=&quot;1&quot;&gt;&lt;br /&gt;&lt;tbody&gt;&lt;tr&gt;&lt;br /&gt;&lt;th&gt;OPERATION&lt;/th&gt;&lt;br /&gt;&lt;th&gt;OPTIONS&lt;/th&gt;&lt;br /&gt;&lt;th&gt;OBJECT_NAME&lt;/th&gt;&lt;br /&gt;&lt;th&gt;COST&lt;/th&gt;&lt;br /&gt;&lt;/tr&gt;&lt;br /&gt;&lt;tr&gt;&lt;br /&gt;&lt;td&gt;SELECT STATEMENT&lt;/td&gt;&lt;br /&gt;&lt;td&gt;  &lt;br /&gt;&lt;/td&gt;&lt;br /&gt;&lt;td&gt;  &lt;br /&gt;&lt;/td&gt;&lt;br /&gt;&lt;td&gt;1&lt;/td&gt;&lt;br /&gt;&lt;/tr&gt;&lt;br /&gt;&lt;tr&gt;&lt;br /&gt;&lt;td&gt;FILTER&lt;/td&gt;&lt;br /&gt;&lt;td&gt;     &lt;br /&gt;&lt;/td&gt;&lt;br /&gt;&lt;td&gt;     &lt;br /&gt;&lt;/td&gt;&lt;br /&gt;&lt;td&gt;&lt;br /&gt;&lt;/td&gt;&lt;br /&gt;&lt;/tr&gt;&lt;br /&gt;&lt;tr&gt;&lt;br /&gt;&lt;td&gt;TABLE ACCESS&lt;/td&gt;&lt;br /&gt;&lt;td&gt;FULL&lt;/td&gt;&lt;br /&gt;&lt;td&gt;DEPT&lt;/td&gt;&lt;br /&gt;&lt;td&gt;1&lt;/td&gt;&lt;br /&gt;&lt;/tr&gt;&lt;br /&gt;&lt;tr&gt;&lt;br /&gt;&lt;td&gt;TABLE ACCESS&lt;/td&gt;&lt;br /&gt;&lt;td&gt;FULL&lt;/td&gt;&lt;br /&gt;&lt;td&gt;EMP1&lt;/td&gt;&lt;br /&gt;&lt;td&gt;822&lt;/td&gt;&lt;br /&gt;&lt;/tr&gt;&lt;br /&gt;&lt;/tbody&gt;&lt;/table&gt;&lt;div style=&quot;text-align: justify;&quot;&gt;&lt;br /&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;Inference&lt;/span&gt;&lt;br /&gt;There is a reduction of more than 97% in estimated cost.In the query using the DISTINCT clause, the explain plan shows that there is a HASH join between the two tables. In this, the smaller of the two tables (in this case the DEPT table) is converted into a hash table and stored in memory. For every row of the larger table (EMP table) retrieved, the hash table is scanned to check for the join condition.&lt;br /&gt;The query using EXISTS uses a FILTER operation. The FILTER operation accepts a set of rows, eliminates some of them, and returns the rest. In the above case, the rows from the DEPT and EMP table are filtered out based on the join condition. EXISTS is a faster and a cheaper alternative because the optimizer realizes that when the sub-query has been satisfied once, there is no need to proceed any further, and the next driving row can be fetched. In the example shown only one row needs to be returned from dept even though many rows in emp might match the subquery. &lt;/div&gt;&lt;div class=&quot;blogger-post-footer&quot;&gt;IF YOU HAVE ANY PROBLEM OR WANT MORE DATA PLS EMAIL ME.
ss.sabyasachipaul@gmail.com&lt;/div&gt;</content><link rel='edit' type='application/atom+xml' href='http://www.blogger.com/feeds/6790758757919456346/posts/default/2442347205743706637'/><link rel='self' type='application/atom+xml' href='http://www.blogger.com/feeds/6790758757919456346/posts/default/2442347205743706637'/><link rel='alternate' type='text/html' href='http://allmyneed.blogspot.com/2009/08/use-exists-in-place-of-distinct.html' title='USE EXISTS IN PLACE OF DISTINCT'/><author><name>Unknown</name><email>noreply@blogger.com</email><gd:image rel='http://schemas.google.com/g/2005#thumbnail' width='16' height='16' src='https://img1.blogblog.com/img/b16-rounded.gif'/></author></entry><entry><id>tag:blogger.com,1999:blog-6790758757919456346.post-8819470811413147048</id><published>2009-08-06T01:05:00.001-07:00</published><updated>2009-08-06T01:11:05.767-07:00</updated><category scheme="http://www.blogger.com/atom/ns#" term="SQL TUNING"/><title type='text'>AVOID USING SUBSTR FUNCTION ON INDEXED COLUMNS</title><content type='html'>A non unique index exists on the ename column of the emp table(ie ENAME_INDX). All employees with their names having the first two letters as &#39;KI&#39; have to be selected.&lt;br /&gt;&lt;br /&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;Query 1&lt;/span&gt;&lt;br /&gt;&lt;br /&gt;&lt;span style=&quot;font-style: italic;&quot;&gt;SELECT ename FROM emp &lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-style: italic;&quot;&gt;   WHERE SUBSTR (ename,1,2) = &#39;KI&#39;;&lt;/span&gt;&lt;br /&gt;&lt;br /&gt;&lt;table border=&quot;1&quot;&gt;&lt;br /&gt;&lt;tbody&gt;&lt;tr&gt;&lt;br /&gt;&lt;th&gt;OPERATION&lt;/th&gt;&lt;br /&gt;&lt;th&gt;OPTIONS&lt;/th&gt;&lt;br /&gt;&lt;th&gt;OBJECT_NAME&lt;/th&gt;&lt;br /&gt;&lt;th&gt;COST&lt;/th&gt;&lt;br /&gt;&lt;/tr&gt;&lt;br /&gt;&lt;tr&gt;&lt;br /&gt;&lt;td&gt;SELECT STATEMENT&lt;/td&gt;&lt;br /&gt;&lt;td&gt;  &lt;br /&gt;&lt;/td&gt;&lt;br /&gt;&lt;td&gt;  &lt;br /&gt;&lt;/td&gt;&lt;br /&gt;&lt;td&gt;822&lt;/td&gt;&lt;br /&gt;&lt;/tr&gt;&lt;br /&gt;&lt;tr&gt;&lt;br /&gt;&lt;td&gt;TABLE ACCESS&lt;/td&gt;&lt;br /&gt;&lt;td&gt;FULL&lt;/td&gt;&lt;br /&gt;&lt;td&gt;EMP&lt;/td&gt;&lt;br /&gt;&lt;td&gt;822&lt;/td&gt;&lt;br /&gt;&lt;/tr&gt;&lt;br /&gt;&lt;/tbody&gt;&lt;/table&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;&lt;br /&gt;Query 2&lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-style: italic;&quot;&gt;SELECT ename FROM emp &lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-style: italic;&quot;&gt;   WHERE ename LIKE &#39;KI%&#39;;&lt;/span&gt;&lt;br /&gt;&lt;br /&gt;&lt;table border=&quot;1&quot;&gt;&lt;br /&gt;&lt;tbody&gt;&lt;tr&gt;&lt;br /&gt;&lt;th&gt;OPERATION&lt;/th&gt;&lt;br /&gt;&lt;th&gt;OPTIONS&lt;/th&gt;&lt;br /&gt;&lt;th&gt;OBJECT_NAME&lt;/th&gt;&lt;br /&gt;&lt;th&gt;COST&lt;/th&gt;&lt;br /&gt;&lt;/tr&gt;&lt;br /&gt;&lt;tr&gt;&lt;br /&gt;&lt;td&gt;SELECT STATEMENT&lt;/td&gt;&lt;br /&gt;&lt;td&gt;  &lt;br /&gt;&lt;/td&gt;&lt;br /&gt;&lt;td&gt;  &lt;br /&gt;&lt;/td&gt;&lt;br /&gt;&lt;td&gt;148&lt;/td&gt;&lt;br /&gt;&lt;/tr&gt;&lt;br /&gt;&lt;tr&gt;&lt;br /&gt;&lt;td&gt;INDEX&lt;/td&gt;&lt;br /&gt;&lt;td&gt;RANGE SCAN&lt;/td&gt;&lt;br /&gt;&lt;td&gt;ENAME_INDX&lt;/td&gt;&lt;br /&gt;&lt;td&gt;148&lt;/td&gt;&lt;br /&gt;&lt;/tr&gt;&lt;br /&gt;&lt;/tbody&gt;&lt;/table&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;&lt;br /&gt;Inference&lt;/span&gt;&lt;br /&gt;Here again, the cost is reduced by more than 82% by avoiding the use of SUBSTR function in the WHERE clause. As explained above, the Optimizer will not make use of the index even though it is available, since there is a function on the indexed column in the WHERE condition.&lt;div class=&quot;blogger-post-footer&quot;&gt;IF YOU HAVE ANY PROBLEM OR WANT MORE DATA PLS EMAIL ME.
ss.sabyasachipaul@gmail.com&lt;/div&gt;</content><link rel='edit' type='application/atom+xml' href='http://www.blogger.com/feeds/6790758757919456346/posts/default/8819470811413147048'/><link rel='self' type='application/atom+xml' href='http://www.blogger.com/feeds/6790758757919456346/posts/default/8819470811413147048'/><link rel='alternate' type='text/html' href='http://allmyneed.blogspot.com/2009/08/avoid-using-substr-function-on-indexed.html' title='AVOID USING SUBSTR FUNCTION ON INDEXED COLUMNS'/><author><name>Unknown</name><email>noreply@blogger.com</email><gd:image rel='http://schemas.google.com/g/2005#thumbnail' width='16' height='16' src='https://img1.blogblog.com/img/b16-rounded.gif'/></author></entry><entry><id>tag:blogger.com,1999:blog-6790758757919456346.post-3953856771657941620</id><published>2009-08-06T00:42:00.000-07:00</published><updated>2009-08-06T00:59:43.261-07:00</updated><category scheme="http://www.blogger.com/atom/ns#" term="SQL TUNING"/><title type='text'>AVOID USING UPPER OR LOWER FUNCTIONS ON COLUMNS WHICH ARE INDEXED</title><content type='html'>&lt;div style=&quot;text-align: justify;&quot;&gt;A non unique index exists on the ename column of the emp table (ie ENAME_INDX). All employees having the name &#39;KING&#39; have to be selected.&lt;br /&gt;&lt;br /&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;Query 1&lt;/span&gt;&lt;br /&gt;SELECT ename FROM emp&lt;br /&gt;  WHERE UPPER (ename) = &#39;KING&#39;;&lt;br /&gt;          &lt;br /&gt;or&lt;br /&gt;&lt;br /&gt; SELECT ename FROM emp&lt;br /&gt;WHERE LOWER(ename) = &#39;king&#39;;&lt;br /&gt;&lt;br /&gt;&lt;/div&gt;&lt;table style=&quot;text-align: left; margin-left: 0px; margin-right: 0px;&quot; border=&quot;1&quot;&gt;&lt;br /&gt;&lt;tbody&gt;&lt;tr&gt;&lt;br /&gt;&lt;th&gt;OPERATION&lt;/th&gt;&lt;br /&gt;&lt;th&gt;OPTIONS&lt;/th&gt;&lt;br /&gt;&lt;th&gt;OBJECT_NAME&lt;/th&gt;&lt;br /&gt;&lt;th&gt;COST&lt;/th&gt;&lt;br /&gt;&lt;/tr&gt;&lt;br /&gt;&lt;tr&gt;&lt;br /&gt;&lt;td&gt;SELECT STATEMENT&lt;/td&gt;&lt;br /&gt;&lt;td&gt;&lt;br /&gt;&lt;/td&gt;&lt;br /&gt;&lt;td&gt;&lt;br /&gt;&lt;/td&gt;&lt;br /&gt;&lt;td&gt;822&lt;/td&gt;&lt;br /&gt;&lt;/tr&gt;&lt;br /&gt;&lt;tr&gt;&lt;br /&gt;&lt;td&gt;TABLE ACCESS&lt;/td&gt;&lt;br /&gt;&lt;td&gt;FULL&lt;/td&gt;&lt;br /&gt;&lt;td&gt;EMP&lt;/td&gt;&lt;br /&gt;&lt;td&gt;822&lt;/td&gt;&lt;br /&gt;&lt;/tr&gt;&lt;br /&gt;&lt;/tbody&gt;&lt;/table&gt;&lt;div style=&quot;text-align: justify;&quot;&gt;&lt;br /&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;Query 2&lt;/span&gt;&lt;br /&gt;SELECT ename FROM emp&lt;br /&gt; WHERE ename=&#39;KING&#39;;&lt;br /&gt;&lt;/div&gt;&lt;table style=&quot;text-align: left; margin-left: 0px; margin-right: 0px;&quot; border=&quot;1&quot;&gt;&lt;br /&gt;&lt;tbody&gt;&lt;tr&gt;&lt;br /&gt;&lt;th&gt;OPERATION&lt;/th&gt;&lt;br /&gt;&lt;th&gt;OPTIONS&lt;/th&gt;&lt;br /&gt;&lt;th&gt;OBJECT_NAME&lt;/th&gt;&lt;br /&gt;&lt;th&gt;COST&lt;/th&gt;&lt;br /&gt;&lt;/tr&gt;&lt;br /&gt;&lt;tr&gt;&lt;br /&gt;&lt;td&gt;SELECT STATEMENT&lt;/td&gt;&lt;br /&gt;&lt;td&gt;&lt;br /&gt;&lt;/td&gt;&lt;br /&gt;&lt;td&gt;&lt;br /&gt;&lt;/td&gt;&lt;br /&gt;&lt;td&gt;2&lt;/td&gt;&lt;br /&gt;&lt;/tr&gt;&lt;br /&gt;&lt;tr&gt;&lt;br /&gt;&lt;td&gt;INDEX&lt;/td&gt;&lt;br /&gt;&lt;td&gt;RANGE SCAN&lt;/td&gt;&lt;br /&gt;&lt;td&gt;ENAME_INDX&lt;/td&gt;&lt;br /&gt;&lt;td&gt;2&lt;/td&gt;&lt;br /&gt;&lt;/tr&gt;&lt;br /&gt;&lt;/tbody&gt;&lt;/table&gt;&lt;div style=&quot;text-align: justify;&quot;&gt;&lt;br /&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;Inference&lt;/span&gt;&lt;br /&gt;The cost is reduced by an incredible 99% when the use of functions in the WHERE clause is eliminated.&lt;br /&gt;Thus, even though the leading column of the index forms a part of the WHERE clause, the available index will not be used. This is because, an SQL function is applied to that column. In such cases, the Oracle Optimizer will not make use of the available index and will opt for a FULL scan on the table. The optimizer has to apply the function to the value in each row which satisfy the condition specified which increases the cost incurred.&lt;/div&gt;&lt;div class=&quot;blogger-post-footer&quot;&gt;IF YOU HAVE ANY PROBLEM OR WANT MORE DATA PLS EMAIL ME.
ss.sabyasachipaul@gmail.com&lt;/div&gt;</content><link rel='edit' type='application/atom+xml' href='http://www.blogger.com/feeds/6790758757919456346/posts/default/3953856771657941620'/><link rel='self' type='application/atom+xml' href='http://www.blogger.com/feeds/6790758757919456346/posts/default/3953856771657941620'/><link rel='alternate' type='text/html' href='http://allmyneed.blogspot.com/2009/08/avoid-using-upper-or-lower-functions-on.html' title='AVOID USING UPPER OR LOWER FUNCTIONS ON COLUMNS WHICH ARE INDEXED'/><author><name>Unknown</name><email>noreply@blogger.com</email><gd:image rel='http://schemas.google.com/g/2005#thumbnail' width='16' height='16' src='https://img1.blogblog.com/img/b16-rounded.gif'/></author></entry><entry><id>tag:blogger.com,1999:blog-6790758757919456346.post-7162705397185314225</id><published>2009-08-05T23:54:00.000-07:00</published><updated>2009-08-06T00:36:03.965-07:00</updated><category scheme="http://www.blogger.com/atom/ns#" term="SQL TUNING"/><title type='text'>MAKE THE MOST OFTEN USED COLUMN IN A WHERE CLAUSE AS THE LEADING COLUMN IN A CONCATENATED INDEX</title><content type='html'>&lt;div style=&quot;text-align: justify;&quot;&gt;A composite index exists on the empno and deptno columns of the emp table (i.e. EMPNO_DEPTNO_INDX in which empno is the leading column). All employees belonging to department 10 have to be selected.&lt;br /&gt;&lt;br /&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;Query 1&lt;/span&gt;&lt;br /&gt;&lt;br /&gt;&lt;span style=&quot;font-style: italic;&quot;&gt;SELECT * FROM emp &lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-style: italic;&quot;&gt;            WHERE deptno = 10;&lt;/span&gt;&lt;table border=&quot;1&quot;&gt;&lt;br /&gt;&lt;tbody&gt;&lt;tr&gt;&lt;br /&gt;&lt;th&gt;OPERATION&lt;/th&gt;&lt;br /&gt;&lt;th&gt;OPTIONS&lt;/th&gt;&lt;br /&gt;&lt;th&gt;OBJECT_NAME&lt;/th&gt;&lt;br /&gt;&lt;th&gt;COST&lt;/th&gt;&lt;br /&gt;&lt;/tr&gt;&lt;br /&gt;&lt;tr&gt;&lt;br /&gt;&lt;td&gt;SELECT STATEMENT&lt;/td&gt;&lt;br /&gt;&lt;td&gt;&lt;br /&gt;&lt;/td&gt;&lt;br /&gt;&lt;td&gt;&lt;br /&gt;&lt;/td&gt;&lt;br /&gt;&lt;td&gt;822&lt;/td&gt;&lt;br /&gt;&lt;/tr&gt;&lt;br /&gt;&lt;tr&gt;&lt;br /&gt;&lt;td&gt;TABLE ACCESS&lt;/td&gt;&lt;br /&gt;&lt;td&gt;FULL&lt;/td&gt;&lt;br /&gt;&lt;td&gt;EMP&lt;/td&gt;&lt;br /&gt;&lt;td&gt;822&lt;/td&gt;&lt;br /&gt;&lt;/tr&gt;&lt;br /&gt;&lt;/tbody&gt;&lt;/table&gt;&lt;br /&gt;&lt;br /&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;Query 2&lt;/span&gt;&lt;br /&gt;&lt;br /&gt;&lt;span style=&quot;font-style: italic;&quot;&gt;SELECT * FROM emp &lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-style: italic;&quot;&gt;            WHERE empno &gt; 0 AND deptno = 10;&lt;/span&gt;&lt;table border=&quot;1&quot;&gt;&lt;br /&gt;&lt;tbody&gt;&lt;tr&gt;&lt;br /&gt;&lt;th&gt;OPERATION&lt;/th&gt;&lt;br /&gt;&lt;th&gt;OPTIONS&lt;/th&gt;&lt;br /&gt;&lt;th&gt;OBJECT_NAME&lt;/th&gt;&lt;br /&gt;&lt;th&gt;COST&lt;/th&gt;&lt;br /&gt;&lt;/tr&gt;&lt;br /&gt;&lt;tr&gt;&lt;br /&gt;&lt;td&gt;SELECT STATEMENT&lt;/td&gt;&lt;br /&gt;&lt;td&gt;&lt;br /&gt;&lt;/td&gt;&lt;br /&gt;&lt;td&gt;&lt;br /&gt;&lt;/td&gt;&lt;br /&gt;&lt;td&gt;293&lt;/td&gt;&lt;br /&gt;&lt;/tr&gt;&lt;br /&gt;&lt;tr&gt;&lt;br /&gt;&lt;td&gt;TABLE ACCESS&lt;/td&gt;&lt;br /&gt;&lt;td&gt;BY INDEX ROWID&lt;/td&gt;&lt;br /&gt;&lt;td&gt;EMP&lt;/td&gt;&lt;br /&gt;&lt;td&gt;293&lt;/td&gt;&lt;br /&gt;&lt;/tr&gt;&lt;br /&gt;&lt;tr&gt;&lt;br /&gt;&lt;td&gt;INDEX&lt;/td&gt;&lt;br /&gt;&lt;td&gt;RANGE SCAN&lt;/td&gt;&lt;br /&gt;&lt;td&gt;EMPNO_DEPTNO_INDX&lt;/td&gt;&lt;br /&gt;&lt;td&gt;26&lt;/td&gt;&lt;br /&gt;&lt;/tr&gt;&lt;br /&gt;&lt;/tbody&gt;&lt;/table&gt;&lt;br /&gt;&lt;br /&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;Inference&lt;/span&gt;&lt;br /&gt;There is a huge cost difference between the above two queries, almost a 62% reduction in the cost when the second approach is used.&lt;br /&gt;Thus, the leading column should be the most selective column, and it should also be the column most often used by limiting conditions in queries. So it is advisable to include a dummy WHERE clause for such queries as shown in the second query.&lt;br /&gt;&lt;br /&gt;&lt;/div&gt;&lt;div class=&quot;blogger-post-footer&quot;&gt;IF YOU HAVE ANY PROBLEM OR WANT MORE DATA PLS EMAIL ME.
ss.sabyasachipaul@gmail.com&lt;/div&gt;</content><link rel='edit' type='application/atom+xml' href='http://www.blogger.com/feeds/6790758757919456346/posts/default/7162705397185314225'/><link rel='self' type='application/atom+xml' href='http://www.blogger.com/feeds/6790758757919456346/posts/default/7162705397185314225'/><link rel='alternate' type='text/html' href='http://allmyneed.blogspot.com/2009/08/make-most-often-used-column-in-where.html' title='MAKE THE MOST OFTEN USED COLUMN IN A WHERE CLAUSE AS THE LEADING COLUMN IN A CONCATENATED INDEX'/><author><name>Unknown</name><email>noreply@blogger.com</email><gd:image rel='http://schemas.google.com/g/2005#thumbnail' width='16' height='16' src='https://img1.blogblog.com/img/b16-rounded.gif'/></author></entry><entry><id>tag:blogger.com,1999:blog-6790758757919456346.post-5367020514243758846</id><published>2009-08-05T23:49:00.000-07:00</published><updated>2009-08-05T23:53:14.664-07:00</updated><category scheme="http://www.blogger.com/atom/ns#" term="SQL TUNING"/><title type='text'>SQL Standards</title><content type='html'>&lt;div style=&quot;text-align: justify;&quot;&gt;It is essential to standardize the way the SQL statements are written. This not only ensures that identical statements performing the same tasks are shared but also provides the ease of reading the statements.&lt;br /&gt;&lt;br /&gt;If the list of employees whose salary is greater than the average salary of their respective department is to be found: -&lt;br /&gt;&lt;br /&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;The inappropriate way of writing the query&lt;/span&gt;&lt;br /&gt;&lt;br /&gt;&lt;span style=&quot;font-style: italic;&quot;&gt;select ename,job,sal,comm,hiredate,deptno from emp e1 where sal &gt; (select avg(sal) from emp e2 where  e2.deptno=e1.deptno);&lt;/span&gt;&lt;br /&gt;&lt;br /&gt;The appropriate way of writing the query&lt;br /&gt;&lt;br /&gt;&lt;br /&gt;&lt;span style=&quot;font-style: italic;&quot;&gt;        SELECT ename,job,sal,comm,hiredate,deptno &lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-style: italic;&quot;&gt;            FROM emp E1&lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-style: italic;&quot;&gt;            WHERE sal &gt; &lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-style: italic;&quot;&gt;                (SELECT AVG(sal) &lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-style: italic;&quot;&gt;                    FROM emp E2&lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-style: italic;&quot;&gt;                    WHERE E2.deptno=E1.deptno);&lt;/span&gt;&lt;br /&gt;&lt;br /&gt;    There is also a technical reason to use a set style throughout the organization. If the statement it is trying to parse, does not match a statement in the shared pool precisely, character by character, and case for case, Oracle will not re-use the parsed statement and instead will opt to create a new cursor. This means your statement will have to undergo the Oracle parse, bind, execute, and fetch phases again. Also the extra resources in the library cache will be added to maintain this copy of a statement. You may actually age out a statement frequently used if the shared pool is too small.&lt;br /&gt;&lt;br /&gt;    In the following section we would be looking into various situations. Under each scenario the appropriate way to write the query and an inappropriate way to write the query is discussed. It should be noted that both the compared queries return the same results, the only difference being the efficiency. The whole section deals with two tables, namely EMP and DEPT. Their structures are given below: -&lt;br /&gt;&lt;br /&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;EMP table&lt;/span&gt;&lt;br /&gt;&lt;br /&gt;            EMPNO        NOT NULL  NUMBER(4)&lt;br /&gt;            ENAME                              VARCHAR2(10)&lt;br /&gt;            JOB                                     VARCHAR2(9)&lt;br /&gt;            MGR                                   NUMBER(4)&lt;br /&gt;            HIREDATE                        DATE&lt;br /&gt;            SAL                                    NUMBER(7,2)&lt;br /&gt;            COMM                               NUMBER(7,2)&lt;br /&gt;            DEPTNO       NOT NULL NUMBER(2)&lt;br /&gt;&lt;br /&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;DEPT Table&lt;/span&gt;&lt;br /&gt;&lt;br /&gt;            DEPTNO      NOT NULL   NUMBER(2)&lt;br /&gt;            DNAME                              VARCHAR2(14)&lt;br /&gt;            LOC                                    VARCHAR2(13)&lt;br /&gt;&lt;br /&gt;DEPTNO from the EMP table references the DEPT table. EMPNO column is the primary key in the EMP table and DEPTNO is the primary key in the DEPT table. EMP table has 9.5 lac rows. DEPT table has 4 rows. It is assumed that none of the columns have indexes unless specified. The cost depicted in the results may vary according to the size of the table and the number of distinct values in the columns. Also note that the tables used here are created in a test environment and more real time examples will be added later as and when they come up considering the fact that this is the first iteration of the document. It is also worth mentioning that Indexes should be made use of on column, only after carefully considering the frequency and type of activities on the column in question.&lt;br /&gt;&lt;br /&gt;&lt;/div&gt;&lt;div class=&quot;blogger-post-footer&quot;&gt;IF YOU HAVE ANY PROBLEM OR WANT MORE DATA PLS EMAIL ME.
ss.sabyasachipaul@gmail.com&lt;/div&gt;</content><link rel='edit' type='application/atom+xml' href='http://www.blogger.com/feeds/6790758757919456346/posts/default/5367020514243758846'/><link rel='self' type='application/atom+xml' href='http://www.blogger.com/feeds/6790758757919456346/posts/default/5367020514243758846'/><link rel='alternate' type='text/html' href='http://allmyneed.blogspot.com/2009/08/sql-standards.html' title='SQL Standards'/><author><name>Unknown</name><email>noreply@blogger.com</email><gd:image rel='http://schemas.google.com/g/2005#thumbnail' width='16' height='16' src='https://img1.blogblog.com/img/b16-rounded.gif'/></author></entry><entry><id>tag:blogger.com,1999:blog-6790758757919456346.post-5942476161561493671</id><published>2009-08-05T23:44:00.001-07:00</published><updated>2009-08-05T23:49:38.861-07:00</updated><category scheme="http://www.blogger.com/atom/ns#" term="SQL TUNING"/><title type='text'>SQL Processing</title><content type='html'>Here&#39;s an overview about the SQL processing which will help appreciate the need for the better-written SQL statements.&lt;br /&gt;&lt;br /&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;SQL Processing Phases&lt;br /&gt;&lt;br /&gt;&lt;/span&gt;1. Open&lt;br /&gt;2. Parse&lt;br /&gt;3. Bind&lt;br /&gt;4. Execute&lt;br /&gt;5. Fetch (Select statements)&lt;br /&gt;6. Close&lt;br /&gt;&lt;br /&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;($$) The PARSE phase&lt;/span&gt;&lt;br /&gt;Checks to see if the statement already exists in the shared pool.&lt;br /&gt;Checks to see if the statement is valid SQL language.&lt;br /&gt;Oracle will then look to see if the referenced objects exist.&lt;br /&gt;Oracle will see if the parsing user has permission on all underlying objects.&lt;br /&gt;Explodes any queries on views and attempts to simplify the statement by using a join.&lt;br /&gt;Picks an execution plan and stores it with the cursor&lt;br /&gt;&lt;br /&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;($$) The BIND phase&lt;/span&gt;&lt;br /&gt;Searches statement for bind variables&lt;br /&gt;Assigns a value to the parsed statement.&lt;br /&gt;&lt;br /&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;($$) The execute phase&lt;/span&gt;&lt;br /&gt;Oracle will then run the execution plan against the block buffers.&lt;br /&gt;Oracle performs all the required input and output calls to gather the data for the DMLstatement. This phase may require Oracle to perform sorting operations.&lt;br /&gt;&lt;br /&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;($$) The FETCH phase&lt;/span&gt;&lt;br /&gt;Retrieve all the rows for the select statement.&lt;br /&gt;Possibly sort the retrieved records.&lt;br /&gt;All returned rows are retrieved using an array fetch.&lt;div class=&quot;blogger-post-footer&quot;&gt;IF YOU HAVE ANY PROBLEM OR WANT MORE DATA PLS EMAIL ME.
ss.sabyasachipaul@gmail.com&lt;/div&gt;</content><link rel='edit' type='application/atom+xml' href='http://www.blogger.com/feeds/6790758757919456346/posts/default/5942476161561493671'/><link rel='self' type='application/atom+xml' href='http://www.blogger.com/feeds/6790758757919456346/posts/default/5942476161561493671'/><link rel='alternate' type='text/html' href='http://allmyneed.blogspot.com/2009/08/sql-processing.html' title='SQL Processing'/><author><name>Unknown</name><email>noreply@blogger.com</email><gd:image rel='http://schemas.google.com/g/2005#thumbnail' width='16' height='16' src='https://img1.blogblog.com/img/b16-rounded.gif'/></author></entry><entry><id>tag:blogger.com,1999:blog-6790758757919456346.post-1607902051218666395</id><published>2009-08-05T23:32:00.000-07:00</published><updated>2009-08-05T23:35:38.940-07:00</updated><category scheme="http://www.blogger.com/atom/ns#" term="SQL TUNING"/><title type='text'>General Techniques For Improving SQL Performance</title><content type='html'>&lt;span style=&quot;font-weight: bold;&quot;&gt;($$) Views&lt;br /&gt;&lt;br /&gt;&lt;/span&gt;&lt;div style=&quot;text-align: justify;&quot;&gt;Optimizing the performance of views involves optimizing the SQL statement upon which the view is based and also the SQL that is likely to result when selection criteria are pushed up into the view.&lt;br /&gt;&lt;br /&gt;Creating views containing hints can be a useful technique for optimizing queries and SQL generated by query tools.&lt;br /&gt;&lt;br /&gt;Partition views can be created which reduces the overhead of scanning a substantive range of large tables.&lt;br /&gt;&lt;br /&gt;Snapshots can be used to store the results of complex queries and allow the rapid retrieval of results, which may somewhat be out of range.&lt;br /&gt;&lt;br /&gt;Performance of a simple snapshot refresh is improved by the creation of a snapshot log, providing that only a small proportion of the source table has been changed.&lt;br /&gt;&lt;br /&gt;Snapshots can reduce the overhead of SQL dramatically for static tables.&lt;br /&gt;&lt;br /&gt;Oracle sequences are an efficient mechanism of generating primary key values and should be used in preference to sequence tables or other mechanisms.&lt;br /&gt;&lt;br /&gt;The DECODE operator can be used to perform complex aggregations, which might otherwise need to be performed via multiple queries.&lt;br /&gt;&lt;br /&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;($$) Indexes&lt;/span&gt;&lt;br /&gt;&lt;br /&gt;Indexes on small tables (less than 100 rows) are totally useless, unless of course they are here to implement unique constraints (primary or unique keys).&lt;br /&gt;&lt;br /&gt;Indexes for which there are less than 8 different key values should come under very close scrutiny (this of course refers to &#39;traditional&#39; tree-type indexes, not bitmap ones). Here, the distribution of values is of course extremely important: -&lt;br /&gt;If the values are roughly equally distributed, the index can be scrapped. If, however, searches are for keys, which belong to a small minority, then the index can be kept. It can make sense to have an index on a personnel table on the &#39;SEX&#39; column if you are frequently searching for females in a predominantly male environment or vice versa.&lt;br /&gt;   &lt;br /&gt;Concatenated indexes should be concatenated from the most selective to the least selective column.&lt;br /&gt;&lt;br /&gt;Indexes on single columns, which also appear as the first column in a concatenated index, are redundant. Database engines can use the concatenated index only when the first columns in the index appear in the search condition.&lt;br /&gt;&lt;br /&gt;&lt;/div&gt;&lt;div class=&quot;blogger-post-footer&quot;&gt;IF YOU HAVE ANY PROBLEM OR WANT MORE DATA PLS EMAIL ME.
ss.sabyasachipaul@gmail.com&lt;/div&gt;</content><link rel='edit' type='application/atom+xml' href='http://www.blogger.com/feeds/6790758757919456346/posts/default/1607902051218666395'/><link rel='self' type='application/atom+xml' href='http://www.blogger.com/feeds/6790758757919456346/posts/default/1607902051218666395'/><link rel='alternate' type='text/html' href='http://allmyneed.blogspot.com/2009/08/general-techniques-for-improving-sql.html' title='General Techniques For Improving SQL Performance'/><author><name>Unknown</name><email>noreply@blogger.com</email><gd:image rel='http://schemas.google.com/g/2005#thumbnail' width='16' height='16' src='https://img1.blogblog.com/img/b16-rounded.gif'/></author></entry><entry><id>tag:blogger.com,1999:blog-6790758757919456346.post-6524817307992288788</id><published>2009-08-05T23:24:00.000-07:00</published><updated>2009-08-05T23:32:11.171-07:00</updated><category scheme="http://www.blogger.com/atom/ns#" term="SQL TUNING"/><title type='text'>SQL Tuning Overview</title><content type='html'>&lt;span style=&quot;font-weight: bold;&quot;&gt;($$) Why is SQL tuning important?&lt;/span&gt;&lt;br /&gt;&lt;br /&gt;First in the series of steps undertaken for tuning the database.&lt;br /&gt;One of the least expensive steps.&lt;br /&gt;Generally highest possible performance gain.&lt;br /&gt;&lt;br /&gt;&lt;span style=&quot;font-weight: bold;&quot;&gt;($$) Some things to know about SQL tuning&lt;/span&gt;&lt;br /&gt;&lt;br /&gt;&lt;div style=&quot;text-align: justify;&quot;&gt;The DBA &amp;amp; Developers should work together when tuning statements. During design and development, the application Developers can determine which combination of system resources and Oracle features best meet the performance goals and requirements as established by the business rules. The best possible execution time occurs with the usage of least amount of resources, which include I/O (logical and physical) to and from the database, CPU usage.&lt;br /&gt;&lt;br /&gt;There are many tools that can assist in the tuning endeavor such as TKPROF, the SQL trace facility, SQL Analyze, Oracle Trace, and the Enterprise Manager Tuning Pack. But tuning SQL statements remains a trial and error process.&lt;br /&gt;&lt;br /&gt;SQL tuning should be done prior to looking at database tuning effort. Until it is certain that all the applicable SQL statements are tuned, the system parameters should not be modified to adjust for poorly written SQL.&lt;br /&gt;&lt;br /&gt;SQL tuning involves the least expense compared to hardware changes to increase the memory capacity of the systems.&lt;br /&gt;&lt;br /&gt;For SQL tuning to be accomplished, it is necessary to know what the current execution time is and what resources are needed to successfully execute the statement there by improving its performance.&lt;br /&gt;&lt;br /&gt;It is very essential to write optimal queries so that the CPU resources can be equally shared among the various users connected to the systems in a fair manner.&lt;br /&gt;&lt;br /&gt;Oracle execution path may not be the source of the performance problem; it is very likely the wrong approach to the problem has been followed. This may not even be the best path to follow. It is helpful if alternative ways of accomplishing the same tasks are looked into. High performance improvements may be achieved by making a simple statement work from a different angle.&lt;br /&gt;&lt;br /&gt;The initial focus should be on the most offending SQL, as they will yield the best immediate returns.&lt;br /&gt;&lt;br /&gt;&lt;br /&gt;&lt;/div&gt;&lt;div class=&quot;blogger-post-footer&quot;&gt;IF YOU HAVE ANY PROBLEM OR WANT MORE DATA PLS EMAIL ME.
ss.sabyasachipaul@gmail.com&lt;/div&gt;</content><link rel='edit' type='application/atom+xml' href='http://www.blogger.com/feeds/6790758757919456346/posts/default/6524817307992288788'/><link rel='self' type='application/atom+xml' href='http://www.blogger.com/feeds/6790758757919456346/posts/default/6524817307992288788'/><link rel='alternate' type='text/html' href='http://allmyneed.blogspot.com/2009/08/sql-tuning-overview.html' title='SQL Tuning Overview'/><author><name>Unknown</name><email>noreply@blogger.com</email><gd:image rel='http://schemas.google.com/g/2005#thumbnail' width='16' height='16' src='https://img1.blogblog.com/img/b16-rounded.gif'/></author></entry><entry><id>tag:blogger.com,1999:blog-6790758757919456346.post-5539407706182062553</id><published>2009-08-01T05:02:00.001-07:00</published><updated>2009-08-01T12:14:41.593-07:00</updated><category scheme="http://www.blogger.com/atom/ns#" term="FORMS AND REPORTS"/><title type='text'>Trigger Typical Usage On Different Levels</title><content type='html'>   	&lt;meta http-equiv=&quot;CONTENT-TYPE&quot; content=&quot;text/html; charset=utf-8&quot;&gt;&lt;title&gt;&lt;/title&gt;&lt;meta name=&quot;GENERATOR&quot; content=&quot;OpenOffice.org 1.1.5  (Linux)&quot;&gt;&lt;meta name=&quot;CREATED&quot; content=&quot;20090801;17090300&quot;&gt;&lt;meta name=&quot;CHANGED&quot; content=&quot;16010101;0&quot;&gt; 	 	 	 	 	&lt;style&gt; 	&lt;!-- 		@page { size: 8.27in 11.69in; margin: 0.79in } 		P { margin-bottom: 0.08in } 	--&gt; 	&lt;/style&gt;  &lt;p style=&quot;margin-bottom: 0in;&quot; align=&quot;left&quot; lang=&quot;en-US&quot;&gt;&lt;font size=&quot;2&quot;&gt;&lt;b&gt;&lt;u&gt;Trigger	Typical Usage On Different Levels&lt;/u&gt;&lt;/b&gt;&lt;/font&gt;&lt;/p&gt;&lt;p style=&quot;margin-bottom: 0in;&quot; align=&quot;left&quot; lang=&quot;en-US&quot;&gt;&lt;font size=&quot;2&quot;&gt;&lt;b&gt;A.Block Processing Triggers:&lt;u&gt;&lt;br&gt;&lt;/u&gt;&lt;/b&gt;&lt;/font&gt;&lt;/p&gt;&lt;p style=&quot;margin-bottom: 0in;&quot; align=&quot;left&quot; lang=&quot;en-US&quot;&gt;    	&lt;meta http-equiv=&quot;CONTENT-TYPE&quot; content=&quot;text/html; charset=utf-8&quot;&gt;&lt;title&gt;&lt;/title&gt;&lt;meta name=&quot;GENERATOR&quot; content=&quot;OpenOffice.org 1.1.5  (Linux)&quot;&gt;&lt;meta name=&quot;CREATED&quot; content=&quot;20090801;17090300&quot;&gt;&lt;meta name=&quot;CHANGED&quot; content=&quot;16010101;0&quot;&gt; 	 	 	 	 	&lt;style&gt; 	&lt;!-- 		@page { size: 8.27in 11.69in; margin: 0.79in } 		P { margin-bottom: 0.08in } 	--&gt; 	&lt;/style&gt;  &lt;/p&gt;&lt;p style=&quot;margin-bottom: 0in;&quot; align=&quot;left&quot; lang=&quot;en-US&quot;&gt;&lt;font size=&quot;2&quot;&gt;&lt;u&gt;&lt;b&gt;When-Create-Record&lt;/b&gt;&lt;/u&gt;: 	Perform an action whenever Form Builder attempts to create a new record in a block.  For example, to set complex, calculated, or data-driven default values that must be specified at runtime, rather than design time.&lt;/font&gt;&lt;/p&gt;&lt;p style=&quot;margin-bottom: 0in;&quot; align=&quot;left&quot; lang=&quot;en-US&quot;&gt;   	&lt;meta http-equiv=&quot;CONTENT-TYPE&quot; content=&quot;text/html; charset=utf-8&quot;&gt;&lt;title&gt;&lt;/title&gt;&lt;meta name=&quot;GENERATOR&quot; content=&quot;OpenOffice.org 1.1.5  (Linux)&quot;&gt;&lt;meta name=&quot;CREATED&quot; content=&quot;20090801;17090300&quot;&gt;&lt;meta name=&quot;CHANGED&quot; content=&quot;16010101;0&quot;&gt; 	 	 	 	 	&lt;style&gt; 	&lt;!-- 		@page { size: 8.27in 11.69in; margin: 0.79in } 		P { margin-bottom: 0.08in } 	--&gt; 	&lt;/style&gt;  &lt;/p&gt;&lt;p style=&quot;margin-bottom: 0in;&quot; align=&quot;left&quot; lang=&quot;en-US&quot;&gt;&lt;font size=&quot;2&quot;&gt;&lt;u&gt;&lt;b&gt;When-Clear-Block&lt;/b&gt;&lt;/u&gt;: 	Perform an action whenever Form Builder flushes the current block; that is, removes all records from the block.&lt;/font&gt;&lt;/p&gt;&lt;p style=&quot;margin-bottom: 0in;&quot; align=&quot;left&quot; lang=&quot;en-US&quot;&gt;   	&lt;meta http-equiv=&quot;CONTENT-TYPE&quot; content=&quot;text/html; charset=utf-8&quot;&gt;&lt;title&gt;&lt;/title&gt;&lt;meta name=&quot;GENERATOR&quot; content=&quot;OpenOffice.org 1.1.5  (Linux)&quot;&gt;&lt;meta name=&quot;CREATED&quot; content=&quot;20090801;17090300&quot;&gt;&lt;meta name=&quot;CHANGED&quot; content=&quot;16010101;0&quot;&gt; 	 	 	 	 	&lt;style&gt; 	&lt;!-- 		@page { size: 8.27in 11.69in; margin: 0.79in } 		P { margin-bottom: 0.08in } 	--&gt; 	&lt;/style&gt;  &lt;/p&gt;&lt;p style=&quot;margin-bottom: 0in;&quot; align=&quot;left&quot; lang=&quot;en-US&quot;&gt;&lt;font size=&quot;2&quot;&gt;&lt;u&gt;&lt;b&gt;When-Database-Record&lt;/b&gt;&lt;/u&gt;: 	Perform an action whenever Form Builder changes a record&amp;#39;s status to Insert or Update, thus indicating that the record should be processed by the next COMMIT_FORM operation&lt;/font&gt;&lt;/p&gt; &lt;p style=&quot;margin-bottom: 0in;&quot; align=&quot;left&quot; lang=&quot;en-US&quot;&gt;&lt;font size=&quot;2&quot;&gt;&lt;u&gt;&lt;b&gt;When-Remove-Record&lt;/b&gt;&lt;/u&gt; :	Perform an action whenever a record is cleared or deleted.  For example, to adjust a running total that is being calculated for all of the records displayed in a block.&lt;/font&gt;&lt;/p&gt;&lt;p style=&quot;margin-bottom: 0in;&quot; align=&quot;left&quot; lang=&quot;en-US&quot;&gt;   	&lt;meta http-equiv=&quot;CONTENT-TYPE&quot; content=&quot;text/html; charset=utf-8&quot;&gt;&lt;title&gt;&lt;/title&gt;&lt;meta name=&quot;GENERATOR&quot; content=&quot;OpenOffice.org 1.1.5  (Linux)&quot;&gt;&lt;meta name=&quot;CREATED&quot; content=&quot;20090801;17090300&quot;&gt;&lt;meta name=&quot;CHANGED&quot; content=&quot;16010101;0&quot;&gt; 	 	 	 	 	&lt;style&gt; 	&lt;!-- 		@page { size: 8.27in 11.69in; margin: 0.79in } 		P { margin-bottom: 0.08in } 	--&gt; 	&lt;/style&gt;  &lt;/p&gt;&lt;p style=&quot;margin-bottom: 0in;&quot; align=&quot;left&quot; lang=&quot;en-US&quot;&gt;&lt;font size=&quot;2&quot;&gt;&lt;u&gt;&lt;b&gt;When-New-Block-Instance:&lt;/b&gt;&lt;/u&gt; 	Perform an action immediately after the input focus moves to an item in a block other than the block that previously had input focus.&lt;/font&gt;&lt;/p&gt;&lt;p style=&quot;margin-bottom: 0in;&quot; align=&quot;left&quot; lang=&quot;en-US&quot;&gt;   	&lt;meta http-equiv=&quot;CONTENT-TYPE&quot; content=&quot;text/html; charset=utf-8&quot;&gt;&lt;title&gt;&lt;/title&gt;&lt;meta name=&quot;GENERATOR&quot; content=&quot;OpenOffice.org 1.1.5  (Linux)&quot;&gt;&lt;meta name=&quot;CREATED&quot; content=&quot;20090801;17090300&quot;&gt;&lt;meta name=&quot;CHANGED&quot; content=&quot;16010101;0&quot;&gt; 	 	 	 	 	&lt;style&gt; 	&lt;!-- 		@page { size: 8.27in 11.69in; margin: 0.79in } 		P { margin-bottom: 0.08in } 	--&gt; 	&lt;/style&gt;  &lt;/p&gt;&lt;p style=&quot;margin-bottom: 0in;&quot; align=&quot;left&quot; lang=&quot;en-US&quot;&gt;&lt;font size=&quot;2&quot;&gt;&lt;u&gt;&lt;b&gt;When-New-Record-Instance 	:&lt;/b&gt;&lt;/u&gt;Perform an action immediately after the input focus moves to an item in a different record.  If the new record is in a different block, fires after the When-New-Block-Instance trigger, but before the When-New-Item-Instance trigger.&lt;/font&gt;&lt;/p&gt; &lt;p style=&quot;margin-bottom: 0in;&quot; align=&quot;left&quot; lang=&quot;en-US&quot;&gt;&lt;font size=&quot;2&quot;&gt;&lt;u&gt;&lt;b&gt;When-New-Item-Instance:&lt;/b&gt;&lt;/u&gt; 	Perform an action immediately after the input focus moves to a different item.  If the new item is in a different block, fires after the When-New-Block-Instance trigger.&lt;/font&gt;&lt;/p&gt;&lt;p style=&quot;margin-bottom: 0in;&quot; align=&quot;left&quot; lang=&quot;en-US&quot;&gt;   	&lt;meta http-equiv=&quot;CONTENT-TYPE&quot; content=&quot;text/html; charset=utf-8&quot;&gt;&lt;title&gt;&lt;/title&gt;&lt;meta name=&quot;GENERATOR&quot; content=&quot;OpenOffice.org 1.1.5  (Linux)&quot;&gt;&lt;meta name=&quot;CREATED&quot; content=&quot;20090801;17090300&quot;&gt;&lt;meta name=&quot;CHANGED&quot; content=&quot;16010101;0&quot;&gt; 	 	 	 	 	&lt;style&gt; 	&lt;!-- 		@page { size: 8.27in 11.69in; margin: 0.79in } 		P { margin-bottom: 0.08in } 	--&gt; 	&lt;/style&gt;  &lt;/p&gt;&lt;p style=&quot;margin-bottom: 0in;&quot; align=&quot;left&quot; lang=&quot;en-US&quot;&gt;&lt;font size=&quot;2&quot;&gt;&lt;u&gt;&lt;b&gt;On-Delete:&lt;/b&gt;&lt;/u&gt; 	Replace the default Form Builder processing for handling deleted records during transaction posting. &lt;/font&gt; &lt;/p&gt; &lt;p style=&quot;margin-bottom: 0in;&quot; align=&quot;left&quot; lang=&quot;en-US&quot;&gt;&lt;font size=&quot;2&quot;&gt;&lt;u&gt;&lt;b&gt;On-Insert :&lt;/b&gt;&lt;/u&gt;	Replace the default Form Builder processing for handling inserted records during transaction posting. &lt;/font&gt; &lt;/p&gt; &lt;p style=&quot;margin-bottom: 0in;&quot; align=&quot;left&quot; lang=&quot;en-US&quot;&gt;&lt;font size=&quot;2&quot;&gt;&lt;u&gt;&lt;b&gt;On-Lock:&lt;/b&gt;&lt;/u&gt; 	Replace the default Form Builder processing for locking rows in the database.&lt;/font&gt;&lt;/p&gt; &lt;p style=&quot;margin-bottom: 0in;&quot; align=&quot;left&quot; lang=&quot;en-US&quot;&gt;&lt;font size=&quot;2&quot;&gt;&lt;u&gt;&lt;b&gt;On-Logon:&lt;/b&gt;&lt;/u&gt; 	Replace the default Form Builder processing for connecting to ORACLE, especially for a form that does not require a database connection or for connecting to a non-ORACLE data source. &lt;/font&gt; &lt;/p&gt; &lt;p style=&quot;margin-bottom: 0in;&quot; align=&quot;left&quot; lang=&quot;en-US&quot;&gt;&lt;font size=&quot;2&quot;&gt;&lt;u&gt;&lt;b&gt;On-Logout:&lt;/b&gt;&lt;/u&gt; 	Replace the default Form Builder processing for logout from ORACLE. &lt;/font&gt; &lt;/p&gt; &lt;p style=&quot;margin-bottom: 0in;&quot; align=&quot;left&quot; lang=&quot;en-US&quot;&gt;&lt;font size=&quot;2&quot;&gt;&lt;u&gt;&lt;b&gt;On-Update:&lt;/b&gt;&lt;/u&gt; 	Replace the default Form Builder processing for handling updated records during transaction posting. &lt;/font&gt; &lt;/p&gt; &lt;p style=&quot;margin-bottom: 0in;&quot; align=&quot;left&quot; lang=&quot;en-US&quot;&gt;&lt;font size=&quot;2&quot;&gt;&lt;u&gt;&lt;b&gt;Post-Database-Commit:&lt;/b&gt;&lt;/u&gt; 	Augment default Form Builder processing following a database commit. &lt;/font&gt; &lt;/p&gt; &lt;p style=&quot;margin-bottom: 0in;&quot; align=&quot;left&quot; lang=&quot;en-US&quot;&gt;&lt;font size=&quot;2&quot;&gt;&lt;u&gt;&lt;b&gt;Post-Delete:&lt;/b&gt;&lt;/u&gt; 	Audit transactions following the deletion of a row from the database. &lt;/font&gt; &lt;/p&gt; &lt;p style=&quot;margin-bottom: 0in;&quot; align=&quot;left&quot; lang=&quot;en-US&quot;&gt;&lt;font size=&quot;2&quot;&gt;&lt;u&gt;&lt;b&gt;Post-Forms-Commit:&lt;/b&gt;&lt;/u&gt; 	Augment the default Form Builder commit statement prior to committing a transaction. &lt;/font&gt; &lt;/p&gt; &lt;p style=&quot;margin-bottom: 0in;&quot; align=&quot;left&quot; lang=&quot;en-US&quot;&gt;&lt;font size=&quot;2&quot;&gt;&lt;u&gt;&lt;b&gt;Post-Insert&lt;/b&gt;&lt;/u&gt; 	Audit transactions following the insertion of a row in the database. &lt;/font&gt; &lt;/p&gt; &lt;p style=&quot;margin-bottom: 0in;&quot; align=&quot;left&quot; lang=&quot;en-US&quot;&gt;&lt;font size=&quot;2&quot;&gt;&lt;u&gt;&lt;b&gt;Post-Update&lt;/b&gt;&lt;/u&gt; 	Audit: transactions following the updating of a row in the database.&lt;/font&gt;&lt;/p&gt; &lt;p style=&quot;margin-bottom: 0in;&quot; align=&quot;left&quot; lang=&quot;en-US&quot;&gt;&lt;font size=&quot;2&quot;&gt;&lt;u&gt;&lt;b&gt;Pre-Commit:&lt;/b&gt;&lt;/u&gt; 	Perform an action immediately before the Post and Commit Transactions process, when Form Builder determines that there are changes in the form to post or to commit. &lt;/font&gt; &lt;/p&gt; &lt;p style=&quot;margin-bottom: 0in;&quot; align=&quot;left&quot; lang=&quot;en-US&quot;&gt;&lt;font size=&quot;2&quot;&gt;&lt;u&gt;&lt;b&gt;Pre-Delete:&lt;/b&gt;&lt;/u&gt; 	Manipulate a record prior to its being deleted from the database during the default Post and Commit Transactions process; for example, to prevent deletion of the record if certain conditions exist. &lt;/font&gt; &lt;/p&gt;  &lt;p&gt;&lt;/p&gt; &lt;p&gt;&lt;/p&gt;&lt;p style=&quot;margin-bottom: 0in;&quot; align=&quot;left&quot; lang=&quot;en-US&quot;&gt;   	&lt;meta http-equiv=&quot;CONTENT-TYPE&quot; content=&quot;text/html; charset=utf-8&quot;&gt;&lt;title&gt;&lt;/title&gt;&lt;meta name=&quot;GENERATOR&quot; content=&quot;OpenOffice.org 1.1.5  (Linux)&quot;&gt;&lt;meta name=&quot;CREATED&quot; content=&quot;20090801;17090300&quot;&gt;&lt;meta name=&quot;CHANGED&quot; content=&quot;16010101;0&quot;&gt; 	 	 	 	 	&lt;style&gt; 	&lt;!-- 		@page { size: 8.27in 11.69in; margin: 0.79in } 		P { margin-bottom: 0.08in } 	--&gt; 	&lt;/style&gt;  &lt;/p&gt;&lt;p style=&quot;margin-bottom: 0in;&quot; align=&quot;left&quot; lang=&quot;en-US&quot;&gt;&lt;font size=&quot;2&quot;&gt;&lt;u&gt;&lt;b&gt;Pre-Insert:&lt;/b&gt;&lt;/u&gt; 	Manipulate a record prior to its being inserted in the database during the default Post and Commit Transactions process.&lt;/font&gt;&lt;/p&gt;&lt;p style=&quot;margin-bottom: 0in;&quot; align=&quot;left&quot; lang=&quot;en-US&quot;&gt;   	&lt;meta http-equiv=&quot;CONTENT-TYPE&quot; content=&quot;text/html; charset=utf-8&quot;&gt;&lt;title&gt;&lt;/title&gt;&lt;meta name=&quot;GENERATOR&quot; content=&quot;OpenOffice.org 1.1.5  (Linux)&quot;&gt;&lt;meta name=&quot;CREATED&quot; content=&quot;20090801;17090300&quot;&gt;&lt;meta name=&quot;CHANGED&quot; content=&quot;16010101;0&quot;&gt; 	 	 	 	 	&lt;style&gt; 	&lt;!-- 		@page { size: 8.27in 11.69in; margin: 0.79in } 		P { margin-bottom: 0.08in } 	--&gt; 	&lt;/style&gt;  &lt;/p&gt;&lt;p style=&quot;margin-bottom: 0in;&quot; align=&quot;left&quot; lang=&quot;en-US&quot;&gt;&lt;font size=&quot;2&quot;&gt;&lt;u&gt;&lt;b&gt;Pre-Update:&lt;/b&gt;&lt;/u&gt;Validate or modify a record prior to its being updated in the database during the default Post and Commit Transactions process. &lt;/font&gt; &lt;/p&gt;  &lt;p&gt;&lt;/p&gt; &lt;p&gt;&lt;/p&gt; &lt;p&gt;&lt;/p&gt;&lt;p style=&quot;margin-bottom: 0in;&quot; align=&quot;left&quot; lang=&quot;en-US&quot;&gt;   	&lt;meta http-equiv=&quot;CONTENT-TYPE&quot; content=&quot;text/html; charset=utf-8&quot;&gt;&lt;title&gt;&lt;/title&gt;&lt;meta name=&quot;GENERATOR&quot; content=&quot;OpenOffice.org 1.1.5  (Linux)&quot;&gt;&lt;meta name=&quot;CREATED&quot; content=&quot;20090801;17090300&quot;&gt;&lt;meta name=&quot;CHANGED&quot; content=&quot;16010101;0&quot;&gt; 	 	 	 	 	&lt;style&gt; 	&lt;!-- 		@page { size: 8.27in 11.69in; margin: 0.79in } 		P { margin-bottom: 0.08in } 	--&gt; 	&lt;/style&gt;  &lt;/p&gt;&lt;p style=&quot;margin-bottom: 0in;&quot; align=&quot;left&quot; lang=&quot;en-US&quot;&gt;   	&lt;meta http-equiv=&quot;CONTENT-TYPE&quot; content=&quot;text/html; charset=utf-8&quot;&gt;&lt;title&gt;&lt;/title&gt;&lt;meta name=&quot;GENERATOR&quot; content=&quot;OpenOffice.org 1.1.5  (Linux)&quot;&gt;&lt;meta name=&quot;CREATED&quot; content=&quot;20090801;17090300&quot;&gt;&lt;meta name=&quot;CHANGED&quot; content=&quot;16010101;0&quot;&gt; 	 	 	 	 	&lt;style&gt; 	&lt;!-- 		@page { size: 8.27in 11.69in; margin: 0.79in } 		P { margin-bottom: 0.08in } 	--&gt; 	&lt;/style&gt;  &lt;/p&gt;&lt;p style=&quot;margin-bottom: 0in;&quot; align=&quot;left&quot; lang=&quot;en-US&quot;&gt;&lt;font size=&quot;2&quot;&gt;&lt;u&gt;&lt;b&gt;Pre-Form:&lt;/b&gt;&lt;/u&gt; 	Perform an action just before Form Builder navigates to the form from &amp;quot;outside&amp;quot; the form, such as at form startup. &lt;/font&gt; &lt;/p&gt; &lt;p style=&quot;margin-bottom: 0in;&quot; align=&quot;left&quot; lang=&quot;en-US&quot;&gt;&lt;font size=&quot;2&quot;&gt;&lt;u&gt;&lt;b&gt;Pre-Block&lt;/b&gt;&lt;/u&gt; 	:Perform an action before Form Builder navigates to the block level from the form level. &lt;/font&gt; &lt;/p&gt; &lt;p style=&quot;margin-bottom: 0in;&quot; align=&quot;left&quot; lang=&quot;en-US&quot;&gt;&lt;font size=&quot;2&quot;&gt;&lt;u&gt;&lt;b&gt;Pre-Record&lt;/b&gt;&lt;/u&gt; 	:Perform an action before Form Builder navigates to the record level from the block level. &lt;/font&gt; &lt;/p&gt; &lt;p style=&quot;margin-bottom: 0in;&quot; align=&quot;left&quot; lang=&quot;en-US&quot;&gt;&lt;font size=&quot;2&quot;&gt;&lt;u&gt;&lt;b&gt;Pre-Text-Item 	:&lt;/b&gt;&lt;/u&gt;Perform an action before Form Builder navigates to a text item from the record level. &lt;/font&gt; &lt;/p&gt; &lt;p style=&quot;margin-bottom: 0in;&quot; align=&quot;left&quot; lang=&quot;en-US&quot;&gt;&lt;font size=&quot;2&quot;&gt;&lt;u&gt;&lt;b&gt;Post-Text-Item&lt;/b&gt;&lt;/u&gt;: 	Manipulate an item when Form Builder leaves a text item and navigates to the record level.&lt;/font&gt;&lt;/p&gt;&lt;p style=&quot;margin-bottom: 0in;&quot; align=&quot;left&quot; lang=&quot;en-US&quot;&gt;   	&lt;meta http-equiv=&quot;CONTENT-TYPE&quot; content=&quot;text/html; charset=utf-8&quot;&gt;&lt;title&gt;&lt;/title&gt;&lt;meta name=&quot;GENERATOR&quot; content=&quot;OpenOffice.org 1.1.5  (Linux)&quot;&gt;&lt;meta name=&quot;CREATED&quot; content=&quot;20090801;17090300&quot;&gt;&lt;meta name=&quot;CHANGED&quot; content=&quot;16010101;0&quot;&gt; 	 	 	 	 	&lt;style&gt; 	&lt;!-- 		@page { size: 8.27in 11.69in; margin: 0.79in } 		P { margin-bottom: 0.08in } 	--&gt; 	&lt;/style&gt;  &lt;/p&gt;&lt;p style=&quot;margin-bottom: 0in;&quot; align=&quot;left&quot; lang=&quot;en-US&quot;&gt;&lt;font size=&quot;2&quot;&gt;&lt;u&gt;&lt;b&gt;Post-Record&lt;/b&gt;&lt;/u&gt;: 	Manipulate a record when Form Builder leaves a record and navigates to the block level.&lt;/font&gt;&lt;/p&gt; &lt;p style=&quot;margin-bottom: 0in;&quot; align=&quot;left&quot; lang=&quot;en-US&quot;&gt;&lt;font size=&quot;2&quot;&gt;&lt;u&gt;&lt;b&gt;Post-Block&lt;/b&gt;&lt;/u&gt;: 	Manipulate the current record when Form Builder leaves a block and navigates to the form level. &lt;/font&gt; &lt;/p&gt; &lt;p style=&quot;margin-bottom: 0in;&quot; align=&quot;left&quot; lang=&quot;en-US&quot;&gt;&lt;font size=&quot;2&quot;&gt;&lt;u&gt;&lt;b&gt;Post-Form&lt;/b&gt;&lt;/u&gt;: 	Perform an action before Form Builder navigates to &amp;quot;outside&amp;quot; the form, such as when exiting the form.&lt;/font&gt;&lt;/p&gt;&lt;p style=&quot;margin-bottom: 0in;&quot; align=&quot;left&quot; lang=&quot;en-US&quot;&gt;   	&lt;meta http-equiv=&quot;CONTENT-TYPE&quot; content=&quot;text/html; charset=utf-8&quot;&gt;&lt;title&gt;&lt;/title&gt;&lt;meta name=&quot;GENERATOR&quot; content=&quot;OpenOffice.org 1.1.5  (Linux)&quot;&gt;&lt;meta name=&quot;CREATED&quot; content=&quot;20090801;17090300&quot;&gt;&lt;meta name=&quot;CHANGED&quot; content=&quot;16010101;0&quot;&gt; 	 	 	 	 	&lt;style&gt; 	&lt;!-- 		@page { size: 8.27in 11.69in; margin: 0.79in } 		P { margin-bottom: 0.08in } 	--&gt; 	&lt;/style&gt;  &lt;/p&gt;&lt;p style=&quot;margin-bottom: 0in;&quot; align=&quot;left&quot; lang=&quot;en-US&quot;&gt;&lt;font size=&quot;2&quot;&gt;&lt;u&gt;&lt;b&gt;When-New-Form-Instance&lt;/b&gt;&lt;/u&gt;: 	Perform an action at form start-up.  (Occurs after the Pre-Form trigger fires).&lt;/font&gt;&lt;/p&gt; &lt;p style=&quot;margin-bottom: 0in;&quot; align=&quot;left&quot; lang=&quot;en-US&quot;&gt;&lt;font size=&quot;2&quot;&gt;&lt;u&gt;&lt;b&gt;When-New-Block-Instance:&lt;/b&gt;&lt;/u&gt; 	Perform an action immediately after the input focus moves to an item in a block other than the block that previously had input focus.&lt;/font&gt;&lt;/p&gt; &lt;p style=&quot;margin-bottom: 0in;&quot; align=&quot;left&quot; lang=&quot;en-US&quot;&gt;&lt;font size=&quot;2&quot;&gt;&lt;u&gt;&lt;b&gt;When-New-Record-Instance 	:&lt;/b&gt;&lt;/u&gt;Perform an action immediately after the input focus moves to an item in a different record.  If the new record is in a different block, fires after the When-New-Block-Instance trigger, but before the When-New-Item-Instance trigger.&lt;/font&gt;&lt;/p&gt; &lt;p style=&quot;margin-bottom: 0in;&quot; align=&quot;left&quot; lang=&quot;en-US&quot;&gt;&lt;font size=&quot;2&quot;&gt;&lt;u&gt;&lt;b&gt;When-New-Item-Instance:&lt;/b&gt;&lt;/u&gt; 	Perform an action immediately after the input focus moves to a different item.  If the new item is in a different block, fires after the When-New-Block-Instance trigger. &lt;/font&gt; &lt;/p&gt; &lt;p&gt;&lt;/p&gt; &lt;p&gt;&lt;/p&gt; &lt;p&gt;&lt;/p&gt; &lt;p&gt;&lt;/p&gt;&lt;p style=&quot;margin-bottom: 0in;&quot; align=&quot;left&quot; lang=&quot;en-US&quot;&gt;   	&lt;meta http-equiv=&quot;CONTENT-TYPE&quot; content=&quot;text/html; charset=utf-8&quot;&gt;&lt;title&gt;&lt;/title&gt;&lt;meta name=&quot;GENERATOR&quot; content=&quot;OpenOffice.org 1.1.5  (Linux)&quot;&gt;&lt;meta name=&quot;CREATED&quot; content=&quot;20090801;17090300&quot;&gt;&lt;meta name=&quot;CHANGED&quot; content=&quot;16010101;0&quot;&gt; 	 	 	 	 	&lt;style&gt; 	&lt;!-- 		@page { size: 8.27in 11.69in; margin: 0.79in } 		P { margin-bottom: 0.08in } 	--&gt; 	&lt;/style&gt;  &lt;/p&gt;&lt;p style=&quot;margin-bottom: 0in;&quot; align=&quot;left&quot; lang=&quot;en-US&quot;&gt;&lt;font size=&quot;2&quot;&gt;&lt;u&gt;&lt;b&gt;When-Validate-Item&lt;/b&gt;&lt;/u&gt;: 	Augment default validation of an item.&lt;/font&gt;&lt;/p&gt; &lt;p style=&quot;margin-bottom: 0in;&quot; align=&quot;left&quot; lang=&quot;en-US&quot;&gt;&lt;font size=&quot;2&quot;&gt;&lt;u&gt;&lt;b&gt;When-Validate-Record&lt;/b&gt;&lt;/u&gt; 	:Augment default validation of a record. &lt;/font&gt; &lt;/p&gt; &lt;p style=&quot;margin-bottom: 0in;&quot; align=&quot;left&quot; lang=&quot;en-US&quot;&gt;&lt;br&gt; &lt;/p&gt;  &lt;p&gt;&lt;/p&gt;  &lt;p&gt;&lt;/p&gt;  &lt;p&gt;&lt;/p&gt; &lt;p&gt;&lt;/p&gt; &lt;div class=&quot;blogger-post-footer&quot;&gt;IF YOU HAVE ANY PROBLEM OR WANT MORE DATA PLS EMAIL ME.
ss.sabyasachipaul@gmail.com&lt;/div&gt;</content><link rel='edit' type='application/atom+xml' href='http://www.blogger.com/feeds/6790758757919456346/posts/default/5539407706182062553'/><link rel='self' type='application/atom+xml' href='http://www.blogger.com/feeds/6790758757919456346/posts/default/5539407706182062553'/><link rel='alternate' type='text/html' href='http://allmyneed.blogspot.com/2009/08/trigger-typical-usage-on-different.html' title='Trigger Typical Usage On Different Levels'/><author><name>Unknown</name><email>noreply@blogger.com</email><gd:image rel='http://schemas.google.com/g/2005#thumbnail' width='16' height='16' src='https://img1.blogblog.com/img/b16-rounded.gif'/></author></entry><entry><id>tag:blogger.com,1999:blog-6790758757919456346.post-7773201067274194757</id><published>2009-08-01T03:07:00.001-07:00</published><updated>2009-08-01T12:15:16.841-07:00</updated><category scheme="http://www.blogger.com/atom/ns#" term="FORMS AND REPORTS"/><title type='text'>What is the difference between the following triggers</title><content type='html'>&lt;strong&gt;&lt;font face=&quot;times new roman,serif&quot;&gt;&lt;font size=&quot;4&quot;&gt;What is the difference between the following triggers :&lt;/font&gt;&lt;/font&gt;&lt;/strong&gt;&lt;br&gt;- Post-Fetch vs. Post_Query&lt;br&gt;- Post-Forms-Commit vs. Post-Database-Commit&lt;br&gt;- Pre-Query vs. Pre-Select&lt;br&gt; &lt;br&gt;Answer&lt;br&gt;&lt;br&gt;A comparison of these triggers follows :&lt;br&gt;Post-Fetch vs. Post-Query&lt;br&gt;POST-FETCH fires just after each attempt to fetch records from the database.  Depending on the value of &amp;quot;Array Size&amp;quot; for the block in question, and how many records are actually delivered from the database in response to our request, this trigger may fire fewer times.&lt;br&gt; In contrast, POST-QUERY fires once for *each* record that is brought from the waiting area (where records first go after they are fetched in a bunch from the database) and added to the end of the current block&amp;#39;s list of records.&lt;br&gt; &lt;br&gt;Post-Forms-Commit vs. Post-Database-Commit&lt;br&gt;POST-FORMS-COMMIT fires as the last trigger which is still part of the current transaction during commit-time processing.  It is the last chance for the application to perform any processing or to rollback the transaction before the actual &amp;#39;COMMIT&amp;#39; is issued.&lt;br&gt; The POST-DATABASE-COMMIT triggers fires just after the &amp;#39;COMMIT&amp;#39; statement is issued by forms to close the current transaction.  At this point it is too late to rollback the transaction.&lt;br&gt;&lt;br&gt;Pre-Query vs. Pre-Select&lt;br&gt; PRE-QUERY fires just before Forms begins to prepare the SQL statement that will be required to query the appropriate records from the database in response to the query criteria that have been entered into the example record.&lt;br&gt; It is the designer&#39;s last chance to &amp;quot;deposit&amp;quot; query criteria into the items in the block as if they had been entered into the example record by the operator.&lt;br&gt;PRE-SELECT fires after the PRE-QUERY trigger, and just before the actual, physical construction of the SELECT statement begins.  It would typically be used in conjunction with an ON-SELECT trigger in which the designer would be supplanting the normal construction of the SELECT statement with a user exit of his own to construct and prepare the SELECT statement for execution (perhaps against a foreign datasource).&lt;br&gt; &lt;br&gt; &lt;div class=&quot;blogger-post-footer&quot;&gt;IF YOU HAVE ANY PROBLEM OR WANT MORE DATA PLS EMAIL ME.
ss.sabyasachipaul@gmail.com&lt;/div&gt;</content><link rel='edit' type='application/atom+xml' href='http://www.blogger.com/feeds/6790758757919456346/posts/default/7773201067274194757'/><link rel='self' type='application/atom+xml' href='http://www.blogger.com/feeds/6790758757919456346/posts/default/7773201067274194757'/><link rel='alternate' type='text/html' href='http://allmyneed.blogspot.com/2009/08/what-is-difference-between-following.html' title='What is the difference between the following triggers'/><author><name>Unknown</name><email>noreply@blogger.com</email><gd:image rel='http://schemas.google.com/g/2005#thumbnail' width='16' height='16' src='https://img1.blogblog.com/img/b16-rounded.gif'/></author></entry><entry><id>tag:blogger.com,1999:blog-6790758757919456346.post-2345067368465600517</id><published>2009-07-31T04:51:00.000-07:00</published><updated>2009-07-31T04:54:00.168-07:00</updated><category scheme="http://www.blogger.com/atom/ns#" term="QUICK VIEW"/><title type='text'>Disable Related Foreign Keys</title><content type='html'>&lt;div style=&quot;font-weight: bold;&quot; class=&quot;summary&quot;&gt;How To Disable Related Foreign Keys In a Table?&lt;/div&gt;&lt;br /&gt;&lt;div class=&quot;restofpost&quot;&gt;&lt;br /&gt;begin&lt;br /&gt;     for cur in (select fk.owner, fk.constraint_name , fk.table_name&lt;br /&gt;      from all_constraints fk, all_constraints pk&lt;br /&gt;      where fk.CONSTRAINT_TYPE = &#39;R&#39; and&lt;br /&gt;      pk.owner = &#39;PAYROLL&#39; and&lt;br /&gt;      fk.R_CONSTRAINT_NAME = pk.CONSTRAINT_NAME and&lt;br /&gt;      pk.TABLE_NAME = &amp;amp;TABLE_NAME) loop&lt;br /&gt;       execute immediate &#39;ALTER TABLE &#39;||cur.owner||&#39;.&#39;||cur.table_name||&#39; MODIFY CONSTRAINT &#39;||cur.constraint_name||&#39; DISABLE&#39;;&lt;br /&gt;       end loop;&lt;br /&gt;    end;&lt;br /&gt;&lt;/div&gt;&lt;div class=&quot;blogger-post-footer&quot;&gt;IF YOU HAVE ANY PROBLEM OR WANT MORE DATA PLS EMAIL ME.
ss.sabyasachipaul@gmail.com&lt;/div&gt;</content><link rel='edit' type='application/atom+xml' href='http://www.blogger.com/feeds/6790758757919456346/posts/default/2345067368465600517'/><link rel='self' type='application/atom+xml' href='http://www.blogger.com/feeds/6790758757919456346/posts/default/2345067368465600517'/><link rel='alternate' type='text/html' href='http://allmyneed.blogspot.com/2009/07/disable-related-foreign-keys.html' title='Disable Related Foreign Keys'/><author><name>Unknown</name><email>noreply@blogger.com</email><gd:image rel='http://schemas.google.com/g/2005#thumbnail' width='16' height='16' src='https://img1.blogblog.com/img/b16-rounded.gif'/></author></entry></feed>