tag:blogger.com,1999:blog-78524302870146153752024-03-13T22:12:44.139-07:00dbms-notes: writing blocks to diskDatabase and system administrationMinima-Imagemhttp://www.blogger.com/profile/03798032367384388122noreply@blogger.comBlogger111125tag:blogger.com,1999:blog-7852430287014615375.post-13156801499498499292014-01-15T22:45:00.000-08:002014-02-01T13:10:58.593-08:00The Binomial Distributon<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgYX-EfsFsUTZRBzf4jxmJ9P-gqbTDYiLhX5mL1zzgiBv0YyBuTMCke1_8Pv7mrW8FNn7XqCiaxpiJmYXdEETXAMu_Y38TnoC5XxOhYBy-xxvYfPEmv3lYckw7ZfsbsJhldnHF8iylnV29K/s1600/binomial-distribution-1.png" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgYX-EfsFsUTZRBzf4jxmJ9P-gqbTDYiLhX5mL1zzgiBv0YyBuTMCke1_8Pv7mrW8FNn7XqCiaxpiJmYXdEETXAMu_Y38TnoC5XxOhYBy-xxvYfPEmv3lYckw7ZfsbsJhldnHF8iylnV29K/s400/binomial-distribution-1.png" /></a></div><span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: large;"><br />
The <b>Binomial Distribution</b> </span><br />
<blockquote><ul><li>It is a <b><a href="http://en.wikipedia.org/wiki/Probability_distribution#Discrete_probability_distribution">discrete</a></b> probability distribution.</li>
<li>The distribution of a random variable <b>X</b> is <b>discrete</b>, if it can assume only a <b>finite</b> or <b>countably infinite</b> number of values.</li>
<li>Considering <i>u</i> the set of all possible values of X: $$\sum_u Pr \left(X = u\right) = 1 $$ </li>
<li> The <a href="http://en.wikipedia.org/wiki/Binomial_distribution" target="_blank"><b>binomial distribution</b></a> is the <b>discrete</b> probability distribution of the number of successes in a sequence of <i>n</i> <b>independent</b> yes/no experiments, each of which yields success with probability <i><b>p</b></i>.</li>
<li>Each success/failure experiment is called a Bernoulli trial.</li>
<li>The binomial distribution is the basis of the binomial test of statistical significance</li>
<li>It is frequently used to model the number of successes in a sample of size <i>n</i> drawn with <b>replacement</b> from a population of size <i>N</i>. The replacement after each draws makes the draws independents.</li>
</ul><br />
If the probability of a successful trial is <i><b>p</b></i>, then the probability of having exactly <i><b>k</b></i> successes in <i><b>n</b></i> identical independent trials is given by the <b>probability mass function</b> below: <br />
\[\begin{aligned}<br />
f\left(k; n,p \right) = Pr \left(X = k\right) = \binom{n}{k} p^k {\left( 1 - p \right)}^{n-k} \\<br />
\text{for k = 0, 1, 2, ..., n, where} \\<br />
\binom{n}{k} = \frac{n!}{k!(n-k)!} <br />
\end{aligned} \]<br />
<br />
The formula can be understood as follows: we want <i><b>k</b></i> successes (with probability $p^k$) and <i><b>n-1</b></i> failures (probability ${\left( 1 - n \right)}^{n-k}$). However, the <i><b>k</b></i> successes can occur anywhere among the <i><b>n</b></i> trials, and there are $ \binom{n}{k}$ different ways of distributing <i><b>k</b></i> success in a sequence of <i><b>n</b></i> trials.<br />
<br />
<table id="tblreg"><tbody>
<tr> <td id="tdreg"><div class="separator" style="clear: both; text-align: center;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEihLDWTYWDSIS-c8AoOLIhptIAvKe23EX5qHJMLAfKBtnvrBO74fs1oOuP5is2REds75C5xfVTddSRcGI_0FCCB3P5-VV1QQvAzNybHF3K23u2wHbR3PgUDkBiUCOsN5Riuju-Pwss6N14z/s320/smqmark.png" height="32" width="32" /></div></td> <td id="tdreg"><b>Consider the following problem:</b><br />
One six-sided dice is rolled 15 times. <i><b>What is the probability of rolling 5 or less 2's</b></i>?</td></tr>
</tbody></table>In each roll, the probability of rolling a particular number, say 2, is 1/6.<br />
The probability of rolling 5 or less 2's is the sum of probabilities of rolling 0,1,2,3,4 and 5 2's.<br />
\[\begin{aligned}<br />
Pr \left(X \leq 5\right) = \sum_{k=0}^5 Pr \left(X = k\right)<br />
\end{aligned} \]<br />
Using <b><a href="http://www.r-tutor.com/elementary-statistics/probability-distributions/binomial-distribution" target="_blank"><b>R</b></a></b> density or <b>probability function </b><b><a href="http://stat.ethz.ch/R-manual/R-patched/library/stats/html/Binomial.html" target="_blank">dbinom</a>()</b> to obtain the probability:<br />
<ul><li><b>dbinom()</b> returns the probability of an outcome of a binomial distribution</li>
<li>The probability of rolling exactly 5 2's is</li>
</ul><pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 12px; line-height: 14px; padding: 5px;">> dbinom(5, size=15, prob=0.167)
[1] 0.06274624
</pre><ul><li>The probability of rolling 0,1,2,3,4 or 5 2's:</li>
</ul><pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 12px; line-height: 14px; padding: 5px;">> dbinom(0, size=15, prob=0.167) +
+ dbinom(1, size=15, prob=0.167) +
+ dbinom(2, size=15, prob=0.167) +
+ dbinom(3, size=15, prob=0.167) +
+ dbinom(4, size=15, prob=0.167) +
+ dbinom(5, size=15, prob=0.167)
[1] 0.9723556
</pre><ul><li>Alternatively, we can use the <b>cumulative probability function</b> for binomial distribution <b><a href="http://stat.ethz.ch/R-manual/R-patched/library/stats/html/Binomial.html" target="_blank">pbinom()</a></b>.</li>
<li>$Pr\left(X \leq 5 \right)$</li>
</ul><pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 12px; line-height: 14px; padding: 5px;">> pbinom(5,size=15, prob=0.167)
[1] 0.9723556
</pre><br />
<br />
<ul><li>As seen above, the <b>pbinom()</b> function is useful to summing consecutive binomial probabilities.</li>
<li>Other questions that can be answered include: </li>
<ul><li>What is the probability of rolling <b>5 or more</b> 2's? $Pr\left(X \geq 5 \right) $</li>
<ul><li>$Pr\left(X \geq 5 \right) = 1 - Pr\left(X \leq 4 \right) = 1 - \text{pbinom(4, size=15, prob=0.167) = 0.09039}$<br />
</li>
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 12px; line-height: 14px; padding: 5px;">> 1 - pbinom(4, 15, 0.167)
[1] 0.09039063
</pre></ul><li>What is the probability of rolling <b>more than 4</b> and <b>less than 8</b> 2's? $Pr\left(4 \leq X \leq 8 \right)$ </li>
<ul><li>$Pr\left(4 \leq X \leq 8 \right) = Pr\left(X \leq 8 \right) - Pr\left(X \leq 5 \right) = \text{pbinom(8, size=15, prob=0.167) - pbinom(5, 15, 0.167) = 0.02720835}$<br />
</li>
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 12px; line-height: 14px; padding: 5px;">> pbinom(8, 15, 0.16667) - pbinom(5,15, 0.16667)
[1] 0.02720835
</pre></ul></ul></ul><ul><li>Plotting the probability distribution:</li>
<pre style="font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; color: #000000;
font-size: 12px;border: 1px dashed #999999;line-height: 14px;
background-color: #eee; padding: 5px;">df <- data.frame(x=1:15, prob=dbinom(1:15, 15, prob=0.167))
plot(df, type="b", xlab="Number (x) of rolls of 2's", ylab= "Pr(x)")</pre>
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOIMdSL8NhS-q-FW5XbdhpLKjMOHZg7BrSTTOBemQRM7SMZ3UZpULfp3bpS5Mk186FjIy7WkbbyA58wbZm4grHpHSaot4vZ19uTLarPTzBDQgN4DXpk-b-RMhaBHdWl3hPhMNvEdNvGCrl/s1600/binom-dice-rolls.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOIMdSL8NhS-q-FW5XbdhpLKjMOHZg7BrSTTOBemQRM7SMZ3UZpULfp3bpS5Mk186FjIy7WkbbyA58wbZm4grHpHSaot4vZ19uTLarPTzBDQgN4DXpk-b-RMhaBHdWl3hPhMNvEdNvGCrl/s320/binom-dice-rolls.png" /></a></div><li>Consider n=100 (number of observations), size=15 (number of trials), prob=0.167 (probability of success in each trial). </li>
<pre style="font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; color: #000000;
font-size: 12px;border: 1px dashed #999999;line-height: 14px;
background-color: #eee; padding: 5px;">bindat <- rbinom(100, 15, 0.167)
hist(bindat, breaks=seq(0,10,1), xlab="N successes")</pre>
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhaN_UGUH3WNg97gi14EzhQnrblV9yy10OCgf1iUGvCLhqW8AiTd_XL8JJ8cf7aRPiamlFppSWA9UN8vlgsLecG24_NhwEZ0_pTAJikK5CwKxky6Jeio3NvpyIsYuPGseVt4qUL4ndtXtE1/s1600/binom-dice-rolls-hist.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhaN_UGUH3WNg97gi14EzhQnrblV9yy10OCgf1iUGvCLhqW8AiTd_XL8JJ8cf7aRPiamlFppSWA9UN8vlgsLecG24_NhwEZ0_pTAJikK5CwKxky6Jeio3NvpyIsYuPGseVt4qUL4ndtXtE1/s320/binom-dice-rolls-hist.png" /></a></div><li>Plotting the area showing the cumulative probability: What is the probability of rolling "at least" 5 2's (5 or more)?<br />
<pre style="font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; color: #000000;
font-size: 12px;border: 1px dashed #999999;line-height: 14px;
background-color: #eee; padding: 5px;">df <- data.frame(x=1:15, prob=dbinom(1:15, 15, prob=0.167))
require(ggplot2)
ggplot(data=df, aes(x=x,y=prob)) + geom_line() +
geom_ribbon(data=subset(df,x>=5 & x<=15),aes(ymax=prob),ymin=0,
fill="red", colour = NA, alpha = 0.5)
</pre>
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjoph-n-XKZFwMJm5YnRUf05sBLSqNWtTTK7HWBKu4VldQEm_LFPJiFDyoWslZNCUMnOEkju0tmBV46HZ0XE9h34qGL_bzvjtU64vf8_6tMXJkN_pgo4RlVqxdA0uqnyqKAVf5SnrjHDjay/s1600/binom-dice-rolls-cumulative.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjoph-n-XKZFwMJm5YnRUf05sBLSqNWtTTK7HWBKu4VldQEm_LFPJiFDyoWslZNCUMnOEkju0tmBV46HZ0XE9h34qGL_bzvjtU64vf8_6tMXJkN_pgo4RlVqxdA0uqnyqKAVf5SnrjHDjay/s320/binom-dice-rolls-cumulative.png" /></a></div></li>
</ul></blockquote>Minima-Imagemhttp://www.blogger.com/profile/03798032367384388122noreply@blogger.com0tag:blogger.com,1999:blog-7852430287014615375.post-47177359737547782872013-10-08T23:57:00.000-07:002014-02-01T16:56:27.444-08:00Probability Distributions (I)<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhyROyiGF6qGDdEmoIgZxWZMItFWS-mS92L6LSQd1PyWFLgt2f40wjD3TICCO006OhSNNzvbmFMaF32LLFMpUma30UfirDZrAvLiw8ZP7-C5x1R6pTHNs5LyzoQxwW6jgeECM_c5556q0DD/s1600/NormalDist.png" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhyROyiGF6qGDdEmoIgZxWZMItFWS-mS92L6LSQd1PyWFLgt2f40wjD3TICCO006OhSNNzvbmFMaF32LLFMpUma30UfirDZrAvLiw8ZP7-C5x1R6pTHNs5LyzoQxwW6jgeECM_c5556q0DD/s320/NormalDist.png" height="78" /></a></div><br />
<br />
<br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: large;"><br />
<b>Probability distributions</b></span><br />
<blockquote><ul><li>A <b>probability distribution</b> describes how the values of a <b>random variable</b> are distributed.</li>
<li> It <b>assigns a probability</b> to each possible outcome of a process or experiment that is assumed random. The random variable can be <i><b>continuous</b></i> or <i><b>discrete.</b></i></li>
<li>Probability distributions can be very useful because, since the characteristics of each distribution are well understood, they can be used to, using a sample of observations, make <b>statistical inferences</b> on the entire population.</li>
<li>A probability distribution can be specified in a number of ways:</li>
<ul><li>Through a <i><b>probability density function</b></i> (<i><b>probability mass function</b></i>)</li>
<li>Through a <i><b>cumulative distribution function</b></i> (<i><b>survival function</b></i>)</li>
<li>Through a <i><b>hazard function</b></i></li>
<li>Through a <i><b>characteristic function</b></i></li>
</ul><li>Some common distributions include:</li>
<ul><li><b><i><a href="http://www.dbms-notes.com/2014/01/the-binomial-distributon.html" target=_blank>Binomial distribution</a></i></b>:<span style="font-family: Courier New, Courier, monospace;"><b> dbinom()</b></span></li>
<ul><li>The collection of possible outcomes of a coin toss [<i>H</i>|<i>T</i>] follow a</li>
</ul><li><b><i>Cauchy distribution</i></b>:<span style="font-family: Courier New, Courier, monospace;"><b> dcauchy()</b></span></li>
<li><b><i>Chi-squared distribution</i></b>:<span style="font-family: Courier New, Courier, monospace;"><b> dchisq()</b></span></li>
<li><b><i>Exponential distribution</i></b>:<span style="font-family: Courier New, Courier, monospace;"><b> dexp()</b></span> </li>
<li><b><i>F distribution</i></b>:<span style="font-family: Courier New, Courier, monospace;"><b> df()</b></span> </li>
<li><b><i>Gamma distribution</i></b>:<span style="font-family: Courier New, Courier, monospace;"><b> dgamma()</b></span> </li>
<li><b><i>Hypergeometric distribution</i></b>:<span style="font-family: Courier New, Courier, monospace;"><b> dhyper()</b></span> </li>
<li><b><i>Log-normal distribution</i></b>:<span style="font-family: Courier New, Courier, monospace;"><b> dlnorm()</b></span> </li>
<li><b><i>Geometric distribution</i></b>:<span style="font-family: Courier New, Courier, monospace;"><b> dgeom()</b></span> </li>
<li><b><i>Multinomial distribution</i></b>:<span style="font-family: Courier New, Courier, monospace;"><b> dmultinom()</b></span> </li>
<li><b><i>Negative binomial distribution</i></b>:<span style="font-family: Courier New, Courier, monospace;"><b> dnbinom()</b></span> </li>
<li><b><i>Normal distribution</i></b>:<span style="font-family: Courier New, Courier, monospace;"><b> dnorm()</b></span> </li>
<li><b><i>Poisson distribution</i></b>:<span style="font-family: Courier New, Courier, monospace;"><b> dpois()</b></span> </li>
<li><b><i>Student's t distribution</i></b>:<span style="font-family: Courier New, Courier, monospace;"><b> dhyper()</b></span> </li>
<li><b><i>Uniform distribution</i></b>:<span style="font-family: Courier New, Courier, monospace;"><b> dunif()</b></span> </li>
<li><b><i>Weibull distribution</i></b>:<span style="font-family: Courier New, Courier, monospace;"><b> dweibull()</b></span> </li>
</ul></ul></blockquote><br />
Minima-Imagemhttp://www.blogger.com/profile/03798032367384388122noreply@blogger.com0tag:blogger.com,1999:blog-7852430287014615375.post-52239208366095583222013-08-06T10:08:00.000-07:002014-01-22T23:16:02.026-08:00If you get: "xterm-256color: Unknown terminal type"<br />
When connecting to a Solaris host, using <b>ssh</b>, from MacOSX or a Linux machine, you may get the message when you try to use <b>vi</b>:<br />
<br />
For example, you may try: <br />
<pre style="font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; color: #000000;
font-size: 12px;border: 1px dashed #999999;line-height: 14px;
background-color: #eee; padding: 5px; overflow: auto; width: 100%">$ vi instructions.txt
xterm-256color: Unknown terminal type
I don't know what kind of terminal you are on - all I have is 'xterm-256color'.
[Using open mode]
"instructions.txt" 25 lines, 2465 characters
Instructions to run the Gateway Management utility
</pre><table id="tblreg"><tr> <td id="tdreg"><div class="separator" style="clear: both; text-align: center;"><img border="0" height="32" width="32" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEihLDWTYWDSIS-c8AoOLIhptIAvKe23EX5qHJMLAfKBtnvrBO74fs1oOuP5is2REds75C5xfVTddSRcGI_0FCCB3P5-VV1QQvAzNybHF3K23u2wHbR3PgUDkBiUCOsN5Riuju-Pwss6N14z/s320/smqmark.png" /></div></td> <td id="tdreg"><b>Solution? </b><br />
</td></tr>
</table><b>You need to define the TERM environment variable.</b><br />
<pre style="font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; color: #000000;
font-size: 12px;border: 1px dashed #999999;line-height: 14px;
background-color: #eee; padding: 5px; overflow: auto; width: 100%">$ export TERM=xterm
$ vi instructions.txt
</pre>Minima-Imagemhttp://www.blogger.com/profile/03798032367384388122noreply@blogger.com0tag:blogger.com,1999:blog-7852430287014615375.post-76714697857533793202012-06-27T16:14:00.003-07:002012-06-27T16:20:18.429-07:00ON SQL functions<div class="separator" style="clear: both; text-align: center;float:right; margin-left:1em; margin-bottom:1em;"><img border="0" height="138" width="150" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjkYjrl9J3CDalSLS0kqNM_60ztBeXIW4-3CYX-hVK4V-6tX-mCHH-tZTg8WQJq0uklwxCHzCT8uZykKD1f3krY3Oiix5d-lYqoNotvCMY6fZgHCGv-16w5uyUzWydqHwbA98QxaGg4xbDS/s150/SQL-Functions.png" /></div><br />
<br />
<br />
Building examples of using SQL functions supported by Oracle<br />
<b><a href="http://www.dbms-notes.com/2012/04/sql-functions-11gr2.html" target=_blank>Check the post here (in construction)</a></b>Minima-Imagemhttp://www.blogger.com/profile/03798032367384388122noreply@blogger.com0tag:blogger.com,1999:blog-7852430287014615375.post-49316218056866732692012-04-06T11:23:00.000-07:002012-04-06T11:23:16.611-07:00Data Warehouse: Concepts on ETL Processes (Pivoting) (II)<div class="separator" style="clear: both; text-align: center; float:right;"><img border="0" height="78" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7et2WtvbHj-TgGXo-iw2HNsWQBtVPGmYhTcYQEzHu9Wbrzm5cq0EaiiDqYg31BNURmiUfjkxhLoRMPE-qXq0HOw1YYH5mKLCvnXatuaTvmS9iOxdVP9MP4oOhIGUmHxpeorMhaHNI8l2s/s400/oracle-11g-logo.gif" width="246" /></div><br />
<div class="separator" style="clear: right; text-align: center; float: right; margin-left:1em; margin-bottom:1em;"><img border="0" height="77" width="331" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhyEMJXWiKVQH1kFlpXpHejfY_vQ-VnesU82qvusvPZUwWLunVlBTs4sRv2kzczPUiPOEHaHnY6ym64PQ-m62xylyWJ5Lt0GOh4hCE2OmlNO_3D6A83lC4G_aU3oOSlAFQ4_T1pz2eQejy0/s400/ETL_Transformation.png" /></div><br />
<br />
<div class="separator" style="clear: both; text-align: center; float: right;"><table><tr><td><br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://www.oracle.com/pls/db112/to_toc?pathname=server.112/e25554/toc.htm" imageanchor="1" style="clear:right; float:right; margin-left:1em; margin-bottom:1em" target=_blank><img border="0" height="242" width="200" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi0UuoQEfHH1J1tlvHBxax-ufjIITpcb7leMpG64y70lGe4wmYhVzc4NjlQnmyvxCo6FB8RRVbK66KCDmDmZdJ5kTx9NFAYH7Eew7yi3qG29xRkN2ZrvpFF_LAoYxbn-YlEDfBuUFj2pXY5/s400/DWH+Guide.png" /></a></div></td></tr>
<tr><td></td></tr>
</table></div><br />
<br />
<br />
<br />
<br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: Medium;"><br />
Transforming presentation: What is a <b>Pivoting operation</b>?</span><br />
<blockquote><ul><li>It is a key technique in DWH: allow you to transform multiple rows of input into fewer and generally wider rows</li>
<li>Allow to <b>transform the presentation of the data</b>, <b>rotating rows into columns</b> and presenting data in a crosstabular format, or vice-versa.</li>
<li>When pivoting, an aggregation operator is applied for each item in the pivot column value list</li>
<li>Use the <b><code>pivot_clause</b></code> of the <b><code>SELECT</b></code> statement to write crosstabulation queries that rotate rows into columns, aggregating data in the process of the rotation.<br />
<li>Pivoting: <b>Transactional format <=> Crosstabular (relational) format</b> </li><br />
<br />
</ul></blockquote><span style="color: blue; font-size: normal;">Consider the example below:</span> <blockquote><ul><li>The view SALES_VIEW displays data in a <b>transactional format</b></li>
<li>It lists amount and quantity sold by product, country, quarter and channel.</li>
<li>In this case, a product may be sold through various channels in a given country and a given quarter of year.</li>
<li>If you need to see the sales by channel_id on a <b>relational or crosstabular format</b>, you can use a pivoting operation:</li>
</ul></blockquote><pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;">SQL> CREATE VIEW sales_view AS
SELECT
prod_name product, country_name country, channel_id channel,
SUBSTR(calendar_quarter_desc, 6,2) quarter,
SUM(amount_sold) amount_sold, SUM(quantity_sold) quantity_sold
FROM sales, times, customers, countries, products
WHERE sales.time_id = times.time_id AND
sales.prod_id = products.prod_id AND
sales.cust_id = customers.cust_id AND
customers.country_id = countries.country_id
GROUP BY prod_name, country_name, channel_id,
SUBSTR(calendar_quarter_desc, 6, 2);
</pre><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiJEqzskQhbL2hg6Hto85cnL4O6syzFGxvKb9lWbaH_NV7vaSUw9WgBjsShLMSoTyqtWdylr4xDIQjIArTNRWC5kKvmrrmysvaJioW1oxSq7X8hXFUiOu7zR1ZBtxacQweeeytNojvCbVfq/s1600/pivoting-operation---salesview-1.png" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="290" width="700" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiJEqzskQhbL2hg6Hto85cnL4O6syzFGxvKb9lWbaH_NV7vaSUw9WgBjsShLMSoTyqtWdylr4xDIQjIArTNRWC5kKvmrrmysvaJioW1oxSq7X8hXFUiOu7zR1ZBtxacQweeeytNojvCbVfq/s700/pivoting-operation---salesview-1.png" /></a></div><blockquote><ul><li>Using the PIVOT clause you can reconstruct the data on a cross tabular form:</li>
</ul></blockquote><pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;">SQL> SELECT * FROM
(SELECT product, channel, amount_sold
FROM sales_view
WHERE country = 'United States of America'
) S PIVOT (SUM(amount_sold)
FOR CHANNEL IN (3 AS DIRECT_SALES, 4 AS INTERNET_SALES,
5 AS CATALOG_SALES, 9 AS TELESALES))
ORDER BY product;
PRODUCT DIRECT_SALES INTERNET_SALES CATALOG_SALES TELESALES
------------------------- ---------------------- ---------------------- ---------------------- ------------------
...
Home Theatre Package... 2052150.1 310431.22
Internal 6X CD-ROM 135284.16 15839.73
Internal 8X CD-ROM 158157.23 24398.22
Keyboard Wrist Rest 110554 22477.41 1354.87
...
71 rows selected
</pre><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgotv45dDyRxj4uCtmLY8rG_nVaNcmy8ThBQodKyLUfnOcfFvs1Dq3Y9OQGjYkQcs_-mPAs-IqaZt_awQmaZb9_5Y8kmIt9JjGCpkfRLfwv3x4FvbhCs0cMiExWogcbk6gHNq2iZd55IwuX/s1600/pivoting-operation---salesview-2.png" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="249" width="700" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgotv45dDyRxj4uCtmLY8rG_nVaNcmy8ThBQodKyLUfnOcfFvs1Dq3Y9OQGjYkQcs_-mPAs-IqaZt_awQmaZb9_5Y8kmIt9JjGCpkfRLfwv3x4FvbhCs0cMiExWogcbk6gHNq2iZd55IwuX/s700/pivoting-operation---salesview-2.png" /></a></div>Minima-Imagemhttp://www.blogger.com/profile/03798032367384388122noreply@blogger.com0tag:blogger.com,1999:blog-7852430287014615375.post-54651620159557087902012-04-05T23:08:00.000-07:002012-04-08T23:56:23.091-07:00Oracle Data Intergrator (ODI)<div class="separator" style="clear: both; float: right;"><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7et2WtvbHj-TgGXo-iw2HNsWQBtVPGmYhTcYQEzHu9Wbrzm5cq0EaiiDqYg31BNURmiUfjkxhLoRMPE-qXq0HOw1YYH5mKLCvnXatuaTvmS9iOxdVP9MP4oOhIGUmHxpeorMhaHNI8l2s/s1600/oracle-11g-logo.gif" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" height="78" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7et2WtvbHj-TgGXo-iw2HNsWQBtVPGmYhTcYQEzHu9Wbrzm5cq0EaiiDqYg31BNURmiUfjkxhLoRMPE-qXq0HOw1YYH5mKLCvnXatuaTvmS9iOxdVP9MP4oOhIGUmHxpeorMhaHNI8l2s/s400/oracle-11g-logo.gif" width="246" /></a></div><div class="separator" style="clear: both; text-align: center; float: right;"><table><tr><td><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhOBExyycJ5uRqVoSbp45pU8hxWF4aFwA9L-eB6AdpYKV7QPPw_ZI9RZtfbornVZzcY2fWBm-ieT6lhjcDFpM695F6d2gnjFEHEwFt6Sox8MOYfjVWWkWxH-PYtraed56RSxkKP9cgfCO6-/s1600/ODI-Architecture-wp.png" imageanchor="1" style="clear:right; float:right; margin-left:1em; margin-bottom:1em"><img border="0" height="149" width="200" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhOBExyycJ5uRqVoSbp45pU8hxWF4aFwA9L-eB6AdpYKV7QPPw_ZI9RZtfbornVZzcY2fWBm-ieT6lhjcDFpM695F6d2gnjFEHEwFt6Sox8MOYfjVWWkWxH-PYtraed56RSxkKP9cgfCO6-/s400/ODI-Architecture-wp.png" /></a></div></td></tr>
<tr><td></td></tr>
</table></div></div><br />
<br />
<span style="color: blue;font-size: medium;">Data Integration and ODI - Concepts</span><br />
<br />
Integration projects can be:<br />
<ul><li><b>Data Oriented,</b> especially those involving large data volumes. </li>
<li><b>Event Oriented</b> - Based on an <b>Event Driven Architecture (EDA)</b> or a </li>
<li><b>Service Oriented</b> - Suitable to a <b>Service Oriented Architecture (SOA)</b>, for asynchronous or synchronous integration.</li>
</ul><br />
<b>Oracle Data Integrator: </b><br />
<ul><li>combines all the elements of data integration: data movement, data synchronization, data quality, data management, and data services.</li>
<li>transforms large volumes of data efficiently, </li>
<li>processes events in real time through its advanced Changed Data Capture (CDC) capability, and</li>
<li>provides data services to the Oracle SOA Suite.</li>
<li>provides robust data integrity control features, assuring the consistency and correctness of data</li>
</ul><br />
<span style="color: blue;font-size: medium;">ODI Studio - Interface and Concepts</span><br />
<blockquote><b>Declarative Design</b><br />
<ul><li>Declarative Design is a <b>design method that focuses on “What” to do</b> (the Declarative Rules) rather than “How” to do it (the Process).</li>
<li>Declarative Design in ODI uses the well known relational paradigm to <b>declare in the form of an Interface the declarative rules</b> for a data integration task, which includes designation of sources, targets, and transformations.</li>
<li>Declarative rules often apply to metadata to transform data and are <b>usually described in natural language by business users</b>. </li>
<li>In a typical data integration project (such as a Data Warehouse project), <b>these rules are defined during the specification phase</b> in documents written by business analysts in conjunction with project managers. </li>
<li><b>They can very often be implemented using SQL expressions,</b> provided that the metadata they refer to is known and qualified in a metadata repository.</li>
<li><b>Types</b> of declarative rules: <span style="color: blue;"><b>Mappings, Joins, Filters</b> and <b>Constraints</b></span></li>
</ul></blockquote><br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgIJjbMs4maIFEPvsBmaKvCIp-yzFliat0AQZe-rZlqZP7fL2uz1NyseOVUhQBjpZdg3NPMhCxBNTJ6FKdNvez4emlcV7kqXT_WcArF8m2ISKASoUnHO3bdEcNrbra8HYs8CRwlL-1TtgHv/s1600/ODI---Project-1.png" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="516" width="693" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgIJjbMs4maIFEPvsBmaKvCIp-yzFliat0AQZe-rZlqZP7fL2uz1NyseOVUhQBjpZdg3NPMhCxBNTJ6FKdNvez4emlcV7kqXT_WcArF8m2ISKASoUnHO3bdEcNrbra8HYs8CRwlL-1TtgHv/s700/ODI---Project-1.png" /></a></div><br />
<b>Integration Project</b><br />
<br />
Suggested steps for the <b>life cycle</b> of an <b>integration project:</b> <br />
1. Create a new project and <b>import Knowledge Modules</b>.<br />
2. Define the project organization and practises using folders, markers and documentation.<br />
3. Create reusable components: interfaces, procedures, variables, sequences. Perform unitary tests.<br />
4. Assemble these components into packages. Perform integration tests.<br />
5. Release the work in scenarios<br />
<br />
<br />
<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj5ffzPXBuPB-EAzay0HspIhVzrFwDB9-D2oYUsc4ZM-ONKmmDd3qxfdGhINd5pM5dIj5jXFeadt5i67TQXLQDDwYlb1ABF3-OWgytB5uf266gNhYIT_cHm1m_V2l7V5AAxc1swsdG3H614/s1600/ODI---Packages-2.png" imageanchor="1" style="clear:left; float:left;margin-right:1em; margin-bottom:1em"><img border="0" height="113" width="236" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj5ffzPXBuPB-EAzay0HspIhVzrFwDB9-D2oYUsc4ZM-ONKmmDd3qxfdGhINd5pM5dIj5jXFeadt5i67TQXLQDDwYlb1ABF3-OWgytB5uf266gNhYIT_cHm1m_V2l7V5AAxc1swsdG3H614/s400/ODI---Packages-2.png" /></a></div><br />
<br />
<b>Package</b><br />
<b>Package is a workflow</b>, made up of a sequence of steps organized into an <b>execution diagram</b>.<br />
Each step can either succeed or fail its execution.<br />
Packages assemble and reference other component from a project (interfaces, procedures, variables, etc)<br />
<b>Types of steps</b>: Flow (Interface), Procedue, Variable, ODI tools (allow access to API commands, OS system calls), models, sub-models, and Datastores.<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEidLtpaDjk-Gq8LU8EXPqncdP4cu9Dhyphenhyphene-xyOePFGluxzM0R5VN-ewtb_CjvjiczHHzG8GdX0ETDD2iYsOqFhaePGwDEgbrhxz6j1mTGh_SS20yJ93JEvomfXlQYLN3qUGXCTtgkXoQQWLc/s1600/Packages-1.png" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="473" width="700" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEidLtpaDjk-Gq8LU8EXPqncdP4cu9Dhyphenhyphene-xyOePFGluxzM0R5VN-ewtb_CjvjiczHHzG8GdX0ETDD2iYsOqFhaePGwDEgbrhxz6j1mTGh_SS20yJ93JEvomfXlQYLN3qUGXCTtgkXoQQWLc/s700/Packages-1.png" /></a></div><blockquote><br />
The Package above (Load Customers) specifies a <b>workflow</b> that loads a sequence of tables:<br />
<blockquote>(1) Initialization step: Execute procedure <code>Initialization</code> (type Procedure)<br />
(2) Execute interface Load Countries (type: Flow(interface)), linked object: TRG_COUNTRY<br />
(3) Execute interface Load Regions (type: Flow(interface)), linked object: TRG_REGION<br />
(4) Execute interface Load Cities (type: Flow(interface)), linked object: TRG_CITY<br />
(5) refresh variable "CUST_FLAG". Load Customers? (type: Evaluate Variable), linked objects: CUST_FLAG<br />
IF CUST_FLAG = 1 (true) THEN Load Customers, ELSE Final Step<br />
(6) Final Step (type procedure)<br />
(7) If any of the steps above fails,the Package runs the "Send Admin email" step that sends an email using an ODI tool.<br />
</blockquote><br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgd4lfJghQsi7EucaYRESeGjCsiKLs38otMnhJTDX5rx-P6qqRbNWA9a8BRH03HI4DL1KUzSF4tr3HLlJpb-bar921kPTSxQXsglqmzCyxbeRf9BrtkC5qp136-ivnfixCQ2_PvnjechuBF/s1600/ODI--Interfaces---2.png" imageanchor="1" style="clear:left; float:left;margin-right:1em; margin-bottom:1em"><img border="0" height="98" width="200" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgd4lfJghQsi7EucaYRESeGjCsiKLs38otMnhJTDX5rx-P6qqRbNWA9a8BRH03HI4DL1KUzSF4tr3HLlJpb-bar921kPTSxQXsglqmzCyxbeRf9BrtkC5qp136-ivnfixCQ2_PvnjechuBF/s400/ODI--Interfaces---2.png" /></a></div><br />
<br />
<b>Interface</b><br />
It is <b>set of declarative rules</b> that describe the loading of a datastore or a temporary target structure from one or more source datastores.<br />
An interface is a <b>reusable dataflow</b>. <br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiQTkv5U2HdKzZrZvdClRZ97PtbRW-o9m1fOtAjwS5PfTrnUbfMIqZBfeILTW1V8k6_uvDpxqs5tPAoKNc2D6sU9qbZbKCGtkoPgmIaC7ZKfl3QV9w1846MMgE61fqGV2TDGIPH8I-E4VUZ/s1600/ODI--Interface---1.png" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="472" width="700" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiQTkv5U2HdKzZrZvdClRZ97PtbRW-o9m1fOtAjwS5PfTrnUbfMIqZBfeILTW1V8k6_uvDpxqs5tPAoKNc2D6sU9qbZbKCGtkoPgmIaC7ZKfl3QV9w1846MMgE61fqGV2TDGIPH8I-E4VUZ/s700/ODI--Interface---1.png" /></a></div><b>Components of an Integration Interface: </b><br />
<ul><li><b>Target Datastore</b> - element that will be loaded by the interface. </li>
<ul><li> May be permanent (defined in a model) or temporary (created by the interface)</li>
</ul><li><b>Datasets </b>- One target is loaded with data coming from various datasets</li>
<ul><li>each dataset corresponds to one diagram of source datastores and the mappings used to load the target datastore from these source datastores.</li>
</ul><li>Diagram of Source Datastores</li>
<li><b>Mapping </b>- defines the transformations performed on one or various source coluns to load one target column</li>
<li><b>Staging area</b></li>
<li><b>Flow</b></li>
<li><b>Control</b></li>
</ul><br />
<b>In the example above, </b><br />
<ul><li>The interface defines a set of rules to load the target <code><b>TRG_CUSTOMER</code></b> Datastore with data coming from three source datastores (<code><b>SRC_AGEGROUP, SRC_CUSTOMER, SRC_SALES_PERSON</code></b>)</li>
<li>It defines a <b>Join </b>between <code><b>SRC_CUSTOMER</code></b> and <code><b>SRC_SALES_PERSON</code></b> </li>
<li>it defines a <b>Lookup</b>: <code><b>CUST.AGE BETWEEN AGE_GROUP_AGE_MIN and AGE_GROUP.AGE_MAX</code></b></li>
<li>Define <b>Mappings </b>between the attributes in the sources datastores and attributes in the target datastore</li>
</ul><blockquote><b>Lookup</b></blockquote><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjPO-Hsz4hc69dprlg5oytGbF22w52Sjc1M-SHFVQwj_G5lZk1RCU88n-q7dR4GIVpYyKiD_uF0bJt1GORKoIbdmrfbKep3hMn-xpuS1AhmxaDHWSQ7EpmGAbojVUn2GkX77PWPco5cmDfQ/s1600/ODI--Lookup-Interface1.png" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="449" width="700" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjPO-Hsz4hc69dprlg5oytGbF22w52Sjc1M-SHFVQwj_G5lZk1RCU88n-q7dR4GIVpYyKiD_uF0bJt1GORKoIbdmrfbKep3hMn-xpuS1AhmxaDHWSQ7EpmGAbojVUn2GkX77PWPco5cmDfQ/s700/ODI--Lookup-Interface1.png" /></a></div><blockquote><b>Join</b></blockquote><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhv4RNppEZTc3KWjbI0xvz3eTPcWe0CYAPlNtUqfLtTHVJKyJEWqArzd5kZ9K1BJhh6_rZtigexjtm-IsJwuUsDIhW8zh5rxKN08LMyKVmSdRU25EbaCOTiOFtM7dD5O7srnT8pFPKL5hLD/s1600/ODI--Join-Interface1.png" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="429" width="700" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhv4RNppEZTc3KWjbI0xvz3eTPcWe0CYAPlNtUqfLtTHVJKyJEWqArzd5kZ9K1BJhh6_rZtigexjtm-IsJwuUsDIhW8zh5rxKN08LMyKVmSdRU25EbaCOTiOFtM7dD5O7srnT8pFPKL5hLD/s700/ODI--Join-Interface1.png" /></a></div><blockquote><b>Mapping</b></blockquote><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjoUhIrS7l6FBQ4gOEiO_u4IM0d4wVK-0VyKUzmA-T_HFEGkDfDhokjPISesQbssfbGvYjmIwnYTVgqTqaO01Clq4ePz7JiB5Q3kTF_6AyC-Q55cdg0oRArhs17LWoJarFH0jMMabdhHsMb/s1600/ODI--Interface---3.png" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="450" width="700" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjoUhIrS7l6FBQ4gOEiO_u4IM0d4wVK-0VyKUzmA-T_HFEGkDfDhokjPISesQbssfbGvYjmIwnYTVgqTqaO01Clq4ePz7JiB5Q3kTF_6AyC-Q55cdg0oRArhs17LWoJarFH0jMMabdhHsMb/s700/ODI--Interface---3.png" /></a></div><br />
<br />
<b>Interface dataflow</b><br />
<ul><li>Automatically generated by ODI from the declarative rules in the interface</li>
<li>Based on the configuration files and context (target, sources, where is the staging area, etc)</li>
<li>ODI automatically computes the flow depending on the configuration in the interface's diagram. </li>
<li>It proposes default KMs for the data flow. </li>
<li>The Flow tab enables you to view the data flow and select the KMs used to load and integrate data.</li>
</ul><br />
In the flow, the following items appear:<br />
<b>Source Sets:</b><br />
<ul><li>Source Datastores that are within the same dataset, located on the same physical data server and which are joined with Joins located on the Source are grouped in a single source set in the flow diagram. </li>
<li>A source set represents a group of datastores that can be extracted at the same time.</li>
</ul><b>DataSets:</b> <br />
<ul><li>Datasets appear as yellow boxes in the Staging Area.</li>
</ul><b>Staging Area:</b><br />
<ul><li>Appears as a box that includes the different datasets, the target (if located on the same data server), and possibly some of the sources (if located on<br />
the same data server)</li>
</ul><b>Target:</b> <br />
<ul><li>Appears as a separate box if it is located in a different schema from the staging area</li>
</ul><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj9pCW8VW8LJcwjIrnWOgKw_c_rFFVrRdRETgkntNfFBgBgeww9tJUDMa08zB0SoTSTNdfOKFZKTDxgDoYYB8RhWT-xIKxqE7rlbImI5LpOcmy_6DZ_S5WS6U1JuQ-pRYSFsNgub6-pyRLa/s1600/ODI--dataflow--1.png" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="472" width="700" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj9pCW8VW8LJcwjIrnWOgKw_c_rFFVrRdRETgkntNfFBgBgeww9tJUDMa08zB0SoTSTNdfOKFZKTDxgDoYYB8RhWT-xIKxqE7rlbImI5LpOcmy_6DZ_S5WS6U1JuQ-pRYSFsNgub6-pyRLa/s700/ODI--dataflow--1.png" /></a></div><br />
<br />
<br />
You use the following KMs in the flow:<br />
<ul><li><b>Loading KM (LKM):</b></li>
<ul><li>Define how data is moved.</li>
<li>One LKM is selected for each Source Set for moving data from the sources to the staging area. </li>
<li>It can be also selected to move data from the Staging Area - when different from the Target - to the Target, when a<br />
single technology IKM is selected for the Staging Area.</li>
</ul><li><b>Integration KM (IKM):</b> They define how data is integrated into the target. One IKM is typically<br />
selected on the Target. When the staging area is different from the target, the<br />
selected IKM can be a multi-technology IKM that moves and integrates data from<br />
the Staging Area into the Target.</li>
</ul><br />
<br />
<b>Procedure</b><br />
A Procedure is a <b>reusable component that groups a sequence of operations</b> that do not fit in the interface concept.<br />
Examples of procedures:<br />
■ wait and unzip a file<br />
■ send a batch of files via FTP<br />
■ receive emails<br />
■ purge a database<br />
</blockquote><br />
<br />
<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;float:left;margin-right:1em; margin-bottom:1em;"><img border="0" height="79" width="218" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhGokXCjvL71XQrAnMitIH4B3TxAmwg4tVdqW6qUqixY6NJWZcGqkXpKMa0Abnq3OfFt5rVHOSDPn6gLm_JNtOT-VjZ_jB60pKBZl3IIW9FotnoqyMRDKMu-mJjt61gjUIYbgm5Z7jcObW8/s400/ODI---Knowledge-Modules-1.png" /></div><br />
<span style="color: blue;font-size: medium;">Knowledge Modules</span><br />
<ul><li>Knowledge Modules (KM) implement “how” the integration processes occur</li>
<li>KMs are components of ODI <b>Open Connector technology</b>. </li>
<li>Contain the knowledge required by ODI to <b>perform a specific set of tasks</b> against a <b>specific technology</b> or set of technologies.</li>
<li>When starting an integration project, you must import the Knowledge Module appropriate for your project.</li>
<li>It is possible to import additional KMs after setting up the project, and it is possible to change the KMs used afterwards</li>
<li>Start with Generic KMs. The SQL KMs work with almost all technologies</li>
<li>Start with simple KMs.</li>
<li>Select KMs that match your source/target combinations to increase performance</li>
<li>Select KMs according to your infrastructure limitations</li>
<li>Select JKMs and SKMs only if you need them</li>
<li>Encrypting a Knowledge Module (KM) allows you to protect valuable code</li>
<li>Oracle Data Integrator uses a DES Encryption algorithm based on a personal encryption key. </li>
<li>This key can be saved in a file and can be reused to perform encryption or decryption operations.</li>
</ul><br />
ODI uses six (6) types of Knowledge Modules:<br />
■ <b>RKM (Reverse Knowledge Modules)</b> Perform a customized reverse-engineering of data models for a specific technology. <br />
■ RKMs are used in <b>data models</b>. <br />
<div style="text-align: center;"><i>RKM <reversed technology> [(reverse method)]</i><br />
<b><i>i.e. RKM Oracle, RKM Netezza</b></i></div><br />
■ <b>LKM (Loading Knowledge Modules)</b> are used to extract data from source systems (files, middleware, database, etc.). <br />
■ LKMs are used in <b>interfaces</b>.<br />
<div style="text-align: center;"><i>LKM <source technology> to <target technology> [(loading method)]</i> <br />
i.e. <i><b>LKM Oracle to Oracle (DBLink), LKM File to Oracle (SQLLDR), LKM SQL to SQL</b></i>, etc </div><br />
■ <b>JKM (Journalizing Knowledge Modules)</b> are used to create a journal of data modifications (insert, update and delete) of the source databases to keep track of<br />
the changes. <br />
■ JKMs are used in data models and used for Changed Data Capture. <br />
<div style="text-align: center;"><i>JKM <journalized technology> <journalizing mode> (<journalizing method>)</i><br />
<i><b>JKM Oracle11g Consistent (Streams)<br />
JFM Oracle Simple<br />
JKM DB2 400 Simple (journal)</b></i><br />
</div><br />
■ <b>IKM (Integration Knowledge Modules)</b> are used to integrate (load) data to the target tables. <br />
■ IKMs are used in interfaces. <br />
<div style="text-align: center;"><i>IKM [<staging technology>] <target technology> [<integration mode>] [(<integration method>)].</i><br />
<i><b>i.e. IKM Oracle Incremental Update (MERGE)<br />
IKM SQL to File Append<br />
IKM SQL Incremental Update<br />
IKM SQL to SQL Append</b></i></div><br />
■ <b>CKM (Check Knowledge Modules</b>) are used to check that constraints on the sources and targets are not violated. <br />
■ CKMs are used in data model’s static check and interfaces flow checks. <br />
<div style="text-align: center;"><i>CKM <staging technology></i><br />
<i><b>i.e. CKM SQL, CKM Oracle</b></i></div><br />
■ <b>SKM (Service Knowledge Modules)</b> are used to generate the code required for creating data services. <br />
■ SKMs are used in data models. <br />Minima-Imagemhttp://www.blogger.com/profile/03798032367384388122noreply@blogger.com1tag:blogger.com,1999:blog-7852430287014615375.post-70197989862096058392012-04-04T18:09:00.000-07:002012-04-05T17:09:23.203-07:00California Central Valley: Dreaming images<blockquote><span style="color: blue;"><b>(coffee break)</b></span><br />
</blockquote><blockquote>I have admired <a href="http://www.romanloranc.com/index.html" target=_blank>Roman Loranc</a>'s photographs for a long time.<br />
Born in Poland, Loranc moved to California years ago and has been making incredible photographs.<br />
Check his website and the interview with him below<br />
</Blockquote><div class="separator" style="clear: both; text-align: center;"><a href="http://www.romanloranc.com/cal_rural.html#" imageanchor="1" style="margin-left:1em; margin-right:1em" target=_blank><img border="0" height="324" width="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj8174hDalbMg305eB2vNS_8PfEI3EOYZRrl1AYmiWGn3atuyzerCEN-AiylP4ljoHOK7HkKhCdJ3-E3wjIiMnfSCZ3cjRctiNYS9zOPcLqfLYkXgSvt-Tro7ok3c5BekjSOTbwXx9EPCrp/s400/crucified_lg.jpg" /></a></div><br />
<br />
<div class="separator" style="clear: both; text-align: center; float: center;"><iframe src="http://player.vimeo.com/video/13845816" width="489" height="275" frameborder="0" webkitAllowFullScreen mozallowfullscreen allowFullScreen></iframe><br />
</div>Minima-Imagemhttp://www.blogger.com/profile/03798032367384388122noreply@blogger.com0tag:blogger.com,1999:blog-7852430287014615375.post-39635044032870800292012-04-03T12:40:00.000-07:002012-04-06T11:23:44.091-07:00Data Warehouse: Concepts on ETL Processes (Transforming)<div class="separator" style="clear: both; text-align: center; float:right;"><img border="0" height="78" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7et2WtvbHj-TgGXo-iw2HNsWQBtVPGmYhTcYQEzHu9Wbrzm5cq0EaiiDqYg31BNURmiUfjkxhLoRMPE-qXq0HOw1YYH5mKLCvnXatuaTvmS9iOxdVP9MP4oOhIGUmHxpeorMhaHNI8l2s/s400/oracle-11g-logo.gif" width="246" /></div><br />
<div class="separator" style="clear: right; text-align: center; float: right; margin-left:1em; margin-bottom:1em;"><img border="0" height="77" width="331" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhyEMJXWiKVQH1kFlpXpHejfY_vQ-VnesU82qvusvPZUwWLunVlBTs4sRv2kzczPUiPOEHaHnY6ym64PQ-m62xylyWJ5Lt0GOh4hCE2OmlNO_3D6A83lC4G_aU3oOSlAFQ4_T1pz2eQejy0/s400/ETL_Transformation.png" /></div><br />
<br />
<div class="separator" style="clear: both; text-align: center; float: right;"><table><tr><td><br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://www.oracle.com/pls/db112/to_toc?pathname=server.112/e25554/toc.htm" imageanchor="1" style="clear:right; float:right; margin-left:1em; margin-bottom:1em" target=_blank><img border="0" height="242" width="200" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi0UuoQEfHH1J1tlvHBxax-ufjIITpcb7leMpG64y70lGe4wmYhVzc4NjlQnmyvxCo6FB8RRVbK66KCDmDmZdJ5kTx9NFAYH7Eew7yi3qG29xRkN2ZrvpFF_LAoYxbn-YlEDfBuUFj2pXY5/s400/DWH+Guide.png" /></a></div></td></tr>
<tr><td></td></tr>
</table></div><br />
<br />
<br />
<br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: Medium;"><br />
Data Warehouse: Four (4) typical transformation and loading scenarios</span><br />
<blockquote>(a) <b>Key Lookup</b> Scenario<br />
(b) <b>Business Rule Violation</b> Scenario<br />
(c) <b>Data error</b> scenario<br />
(d) <b>Pivoting</b> Scenario<br />
<br />
<br />
<br />
<span style="color: blue;"><b>(a) key Lookup Scenario</b></span><ul><li>You need to <b>load sales transaction data</b> into a retail Data Warehouse</li>
<li>In the <b>operational system</b>, each product is identified by Uniform Price Codes (UPCs); </li>
<li>In the <b>Data Warehouse</b>, product is identified by product_id.</li>
<li>You need to</li>
</ul><blockquote><b>(1)</b> Map UPC --> PRODUCT_ID; and <br />
<b>(2)</b>As you load the source data, lookup the map table and transform the data accordingly<br />
</blockquote><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjVD_qdno4Sn_vjnhY-kbQZ9Rq6d_JIR59BGUw9FRuwIfK7gWmcZ4uNJK4ms2TVhnIFYTGZovut2xaCRcdiQsbr5BrRczfqjaHlGjWC5tMsnuNyDnYnpWZgmBnSxGoqLcqE4AaLj_VAJ9i_/s1600/Data-Warehouse---ETL---Load-KeyLookupScenario.png" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="257" width="700" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjVD_qdno4Sn_vjnhY-kbQZ9Rq6d_JIR59BGUw9FRuwIfK7gWmcZ4uNJK4ms2TVhnIFYTGZovut2xaCRcdiQsbr5BrRczfqjaHlGjWC5tMsnuNyDnYnpWZgmBnSxGoqLcqE4AaLj_VAJ9i_/s700/Data-Warehouse---ETL---Load-KeyLookupScenario.png" /></a></div><br />
<br />
<span style="color:blue;"><b>(b) Business Rule Violation Scenario</b></span><br />
<ul><li><b>Some UPC Codes are invalid</b></li>
<li>In which ways can you implement the transformation and handle the invalid UPCs?</li>
<li>i.e: new sales data might not have valid UPC codes</li>
</ul><blockquote><b>(1) </b>use <code>Create Table As Select (CTAS)</code> to identify and log invalid UPCs<br />
<b>(2) </b>use an outer join (+) and convert invalid UPCs to NULL<br />
<b>(3) </b>use multi-table INSERT and separate the invalid UPCs to a different table<br />
</blockquote><br />
<b>(1) use CTAS to identify and log invalid UPCs</b><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgtK6Sy70LVWf8DFozSojGNZJ9Noxi-IQx43Iv9gz3v8Gq9HC9YCLAS5QGlEXLhFAAxeC2N1dRpLWmIi_GjQbdWLNXRKawLxZh3OBJ0ri_9-u_wfeclYBDrvAxdXxHPbhW7mz5aKSjye-rl/s1600/Data-Warehouse---ETL---BusinessVilation-1-Scenario.png" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="216" width="700" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgtK6Sy70LVWf8DFozSojGNZJ9Noxi-IQx43Iv9gz3v8Gq9HC9YCLAS5QGlEXLhFAAxeC2N1dRpLWmIi_GjQbdWLNXRKawLxZh3OBJ0ri_9-u_wfeclYBDrvAxdXxHPbhW7mz5aKSjye-rl/s700/Data-Warehouse---ETL---BusinessVilation-1-Scenario.png" /></a></div><br />
<b>(2) use an outer join (+) and convert invalid UPCs to NULL</b><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjy6Mi_oj47-hizo9IKtOE5TRw82DbpjEUTuBb842SYhW0NQpAwVTkwcVK9j3zF4qBb7EW5YsSBeFQbNFZ7cqraOAn38NUGIbIcvbFcs9I_ExhSVFYrUShfE6_yuky-fKNSC76JjrGcFnLU/s1600/Data-Warehouse---ETL---BusinessVilation-2-Scenario.png" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="229" width="700" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjy6Mi_oj47-hizo9IKtOE5TRw82DbpjEUTuBb842SYhW0NQpAwVTkwcVK9j3zF4qBb7EW5YsSBeFQbNFZ7cqraOAn38NUGIbIcvbFcs9I_ExhSVFYrUShfE6_yuky-fKNSC76JjrGcFnLU/s700/Data-Warehouse---ETL---BusinessVilation-2-Scenario.png" /></a></div><br />
<b>(3)use multi-table INSERT and separate the invalid UPCs to a different table</b><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEip_Sv1eoI3LQ0kKKkTwGjIVZrHeXpnzoZVgQCbqaAD_-tn7Fpr2tlrIPs-3YThxGR-NgHXJuIteWTrgexSp23JCDMFu4qBBLgmvFhyphenhyphenzNDBRm0g-PLqCBlPW3dPPe3YMZ_81SvO8cJ7sfVC/s1600/Data-Warehouse---ETL---BusinessVilation-3-Scenario.png" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="340" width="700" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEip_Sv1eoI3LQ0kKKkTwGjIVZrHeXpnzoZVgQCbqaAD_-tn7Fpr2tlrIPs-3YThxGR-NgHXJuIteWTrgexSp23JCDMFu4qBBLgmvFhyphenhyphenzNDBRm0g-PLqCBlPW3dPPe3YMZ_81SvO8cJ7sfVC/s700/Data-Warehouse---ETL---BusinessVilation-3-Scenario.png" /></a></div><br />
<br />
<br />
<span style="color:blue;"><b>(c) Data Error Scenarios</b></span><br />
<ul><li>If the <b>quality of the data</b> is unknown, you may face unexpected errors, such as a a failed datatype conversion, or a constraint violation.</li>
<li>Such errors can happen independently of whether the UPC for the particular record is or not valid.</li>
<li>Here you can use a DML error logging table to capture unexpected errors.</li>
</ul><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh4jGh6PnkHatC3cjqs3uftEOZL4vPovCQto7U9zt-tS-K1K-p-Pn08LZyRBZLxNV7sifIOhvpHTZZCa1EAC4cqLjOMWO4E6igUd7uDtH2ksPSRlVsllePvYkLawnpkwRX_EgAlsZlbO8Ee/s1600/Data-Warehouse---ETL---Data-Errors-Scenario.png" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="363" width="700" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh4jGh6PnkHatC3cjqs3uftEOZL4vPovCQto7U9zt-tS-K1K-p-Pn08LZyRBZLxNV7sifIOhvpHTZZCa1EAC4cqLjOMWO4E6igUd7uDtH2ksPSRlVsllePvYkLawnpkwRX_EgAlsZlbO8Ee/s700/Data-Warehouse---ETL---Data-Errors-Scenario.png" /></a></div></blockquote>Minima-Imagemhttp://www.blogger.com/profile/03798032367384388122noreply@blogger.com1tag:blogger.com,1999:blog-7852430287014615375.post-54591712278372294192012-04-02T15:00:00.000-07:002012-04-02T15:00:33.237-07:00Data Warehouse with Oracle Data Integrator (ODI) (II)<div class="separator" style="clear: both; float: right;"><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7et2WtvbHj-TgGXo-iw2HNsWQBtVPGmYhTcYQEzHu9Wbrzm5cq0EaiiDqYg31BNURmiUfjkxhLoRMPE-qXq0HOw1YYH5mKLCvnXatuaTvmS9iOxdVP9MP4oOhIGUmHxpeorMhaHNI8l2s/s1600/oracle-11g-logo.gif" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" height="78" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7et2WtvbHj-TgGXo-iw2HNsWQBtVPGmYhTcYQEzHu9Wbrzm5cq0EaiiDqYg31BNURmiUfjkxhLoRMPE-qXq0HOw1YYH5mKLCvnXatuaTvmS9iOxdVP9MP4oOhIGUmHxpeorMhaHNI8l2s/s400/oracle-11g-logo.gif" width="246" /></a></div><div class="separator" style="clear: both; text-align: center; float: right;"><table><tr><td><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhOBExyycJ5uRqVoSbp45pU8hxWF4aFwA9L-eB6AdpYKV7QPPw_ZI9RZtfbornVZzcY2fWBm-ieT6lhjcDFpM695F6d2gnjFEHEwFt6Sox8MOYfjVWWkWxH-PYtraed56RSxkKP9cgfCO6-/s1600/ODI-Architecture-wp.png" imageanchor="1" style="clear:right; float:right; margin-left:1em; margin-bottom:1em"><img border="0" height="149" width="200" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhOBExyycJ5uRqVoSbp45pU8hxWF4aFwA9L-eB6AdpYKV7QPPw_ZI9RZtfbornVZzcY2fWBm-ieT6lhjcDFpM695F6d2gnjFEHEwFt6Sox8MOYfjVWWkWxH-PYtraed56RSxkKP9cgfCO6-/s400/ODI-Architecture-wp.png" /></a></div></td></tr>
<tr><td><br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://www.oracle.com/technetwork/middleware/data-integrator/overview/best-practices-for-realtime-data-wa-132882.pdf" imageanchor="1" style="clear:right; float:right; margin-left:1em; margin-bottom:1em" target=_blank><img border="0" height="193" width="200" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh9A3xaVGwmfoC13en8Q_dh0ibZjS5pffrYlUSqeMz3wseKDvQDIzzyK6q4Prf91wKmTj_lxP2uncLlvmvAlSlqN4BO1NYDhMkSJ2gwHbSluCUOF1oT3nkfYSNuyTjt5LLTEnY-__dTE1ZR/s200/ODI++Best+Practices+wp.png" /></a></div></td></tr>
</table></div></div><br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: medium;"><br />
The need for automated integration solutions</span><br />
<blockquote>Challenges to data integration: <br />
<ul><li>Terabytes to Pentabytes of data</li>
<li>Fenomenal data explosion</li>
<li>DI/ETL market growing at 15%</li>
<li>Most enterprises looking to moving away from scripts to solutions</li>
<li>Demand to near real-time/real-time data integration</li>
<li>Explosion in the number of sources: from 10s to 1000s</li>
</ul><br />
Use of ETL tools: (<a href="http://event.on24.com/r.htm?e=400786&s=1&k=B4CF4AAAD703A48326265B64D567EC91&partnerref=DIForestweb_otn" target=_blank>Oct 2011 Forrester survey</a>)<br />
<ul><li>90% of enterprise still make use of batch data loading into DW or BI platform</li>
<li>50% performing real-time/near real-time integration</li>
<li>47% performing batch database migrations</li>
</ul><br />
New data integration requirements:<br />
<ul><li>support for all types of data (structured, semi-structured, unstructured)</li>
<li>Support for Big Data (integrate with Hadoop)</li>
</ul><br />
Top three benefits found of using Oracle Data Integrator <br />
<ul><li>improvement in project completion (resource management, ability to reuse codes, reduce manual hand-coding, reducing error and troubleshooting)</li>
<li>shorter reporting cycle</li>
<li>Deferred hardware upgrade</li>
</ul>( source: <a href="http://event.on24.com/r.htm?e=400786&s=1&k=B4CF4AAAD703A48326265B64D567EC91&partnerref=DIForestweb_otn" target=_blank>here</a> )<br />
</blockquote><br />
<br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: medium;"><br />
<b>About Oracle Data Integrator</b></span><br />
<ul><li>As with the Oracle Warehouse Builder (OWB), ODI uses the database (ISO-92 RDBMS) as the ETL engine</li>
<li>ODI supports ISO-92 RDBMS (Oracle, DB2, SQL Server, etc)</li>
<li>Supports batch, event-based and real-time integration</li>
<li>Extensible through <b>Knowledge Modules</b></li>
<li>ODI Engine runs directly on the source and target</li>
<li>ODI strong on heterogeneity of application sources</li>
<li>ODI includes application adapters for various application sources</li>
</ul><br />
<b>Benefits and improvements:</b><br />
<ul><li>Simpliies creation of data flows through declarative methodology, reducing the number of steps<br />
</li>
<li>Unifies integration tooling across unstructured, semi-structured and structured data</li>
<li>Extends functionality via Knowledge Modules for integrating to applications out-of-the box, providing new customizations</li>
<li>integrates management using OEM to simplify operations across all middleware, applications, database</li>
</ul><br />
<br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: medium;"><br />
<b>Using ODI: Software Architecture</b></span><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjz54AcifnW4AAA6a61UhhUuDxidhpkawUk4sSJ7scdM8yo1sDc5TQj5iD_PhQxFXgQydS_qnZ84iC9Gvx8UsXRkkNgZ2u1XY5aoh0G1GhigJ4aVy6lJDCMkkIq4qWNtmmyqMopX8OCcEjK/s1600/odi-architecture1.png" imageanchor="1" style="clear:right; float:right; margin-left:1em; margin-bottom:1em"><img border="0" height="208" width="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjz54AcifnW4AAA6a61UhhUuDxidhpkawUk4sSJ7scdM8yo1sDc5TQj5iD_PhQxFXgQydS_qnZ84iC9Gvx8UsXRkkNgZ2u1XY5aoh0G1GhigJ4aVy6lJDCMkkIq4qWNtmmyqMopX8OCcEjK/s400/odi-architecture1.png" /></a></div><br />
<br />
<br />
<br />
<b>ODI architecture is build around several components:</b><br />
<ul><li><b>ODI Studio</b> - Graphical client</li>
<li><b>Agents</b> - Java modules that run in source, target, or WebLogic server</li>
<li><b>ODI Console</b> - Web console runs on WebLogic server</li>
<li><b>Extension</b> for the Fusion Middleware Control Console</li>
<li><b>Repositories</b> - Master and Work repositories</li>
</ul><br />
<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiNELRfrLw9i-ngylU1ynrd8Xo7bTK8h4EjEoTPaB77vpu62l2Ah-IOVdSVQbYeT1y4ovV3iHV5nDkfwos1jq6DGI_OvN29JtsbMuAqhYiAsNSIk0pb_zcFsjFq5W8DS_zWZzeYFyy-wAC-/s1600/odi-arch+ODI+Studio1.png" imageanchor="1" style="clear:left; float:left;margin-right:1em; margin-bottom:1em"><img border="0" height="104" width="200" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiNELRfrLw9i-ngylU1ynrd8Xo7bTK8h4EjEoTPaB77vpu62l2Ah-IOVdSVQbYeT1y4ovV3iHV5nDkfwos1jq6DGI_OvN29JtsbMuAqhYiAsNSIk0pb_zcFsjFq5W8DS_zWZzeYFyy-wAC-/s200/odi-arch+ODI+Studio1.png" /></a></div><br />
<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center; float: center;margin-left:1em; margin-right:1em;"><img border="0" height="261" width="600" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgNIXCF7JLmdwmdMbbmXy1pI1eZSPSiKsv-znUjE53QCN43YQJeTq018mp3r_zgMumNZUxCrTb0cqZWP92EpdYMN9Cs-FzJpYLIuZnzZA_fzptDV6scf1neMZ-Mx01WWxeqxMI2hWQOhgzo/s600/ODI+Studio+-+Modules1.png" /></div><br />
<table><tr> <td><br />
<b>Designer<br />
module</b><br />
</td> <td><ul><li>Defines <b>declarative rules</b> for <b>data transformation</b> and <b>data integrity</b>. </li>
<li>All project development occurs here</li>
<li>Database and applications metadata imported and defined here</li>
<li>Use metadata and rules to generate data integration scenarios or load plans for production</li>
<li>Core module for developers and metadata administrators</li>
</ul></td> </tr>
<tr> <td><br />
<b>Operator<br />
module</b><br />
</td> <td><ul><li>Monitors <b>data integration processes</b> in production</li>
<li>Display <b>execution logs</b> (error counts, number of processed rows, execution statistics, executed code, etc)</li>
<li>Can be <b>used for debugging</b> on design time</li>
</ul></td> </tr>
<tr> <td><br />
<b>Topology<br />
module</b><br />
</td> <td><ul><li>defines physical and logical architecture of the infrastructure</li>
<li>Infrastructure or project administrators register servers, database schemas, database catalogs and agents in the master repository </li>
</ul></td> </tr>
<tr> <td><b>Security<br />
module</b></td> <td><ul><li>Manages user profiles and privileges</li>
<li>Assign access authorization to objects and features</li>
</ul></td> </tr>
</table><br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj_UCrEsb5IdzRG2agAb1rEKkJswN5uAiDgXNewcl0kEDgZgWwmJ84seFS6_ifNl5OOy4zUVs_ffUsU-rO77x02zCj0JAgOI6_Z-z6iuD37CfhpCRuvDg71jsejpOPzC3DPTS6EWTmufOBB/s1600/odi-arch+Agents1.png" imageanchor="1" style="clear:left; float:left;margin-right:5em; margin-bottom:1em"><img border="0" height="104" width="200" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj_UCrEsb5IdzRG2agAb1rEKkJswN5uAiDgXNewcl0kEDgZgWwmJ84seFS6_ifNl5OOy4zUVs_ffUsU-rO77x02zCj0JAgOI6_Z-z6iuD37CfhpCRuvDg71jsejpOPzC3DPTS6EWTmufOBB/s200/odi-arch+Agents1.png" /></a></div><br />
<br />
<ul><li>The <b>Agent</b> coordinates the execution of the ODI scenarios. It</li>
</ul><blockquote>(a) <b>retrieves the code</b> stored in the ODI repository, <br />
(b) <b>connects</b> to the various source and target systems and<br />
(c) <b>orchestrates</b> the overall data integration process<br />
(d) when execution is completed, the agent u<b>pdates the execution logs</b> in the repository and <b>reports error messages</b> and <b>executions statistic</b>s. These can be reviewed in the Operator navigator at ODI Studio, or through the web interfaces.<br />
<br />
<b>Two types of Agents: </b><br />
<ul><li><b>Standalone Agent</b> - can be installed on the source or target systems. Requires a JVM.</li>
<li><b>Java EE Agent</b> - Deployed on Oracle WebLogic Server. </li>
</ul></blockquote><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgvzvgKeOv4VRXQwIItwQoLYAIVa-tGaXZI2NFPjIOELhm-3FEUi3IHZiSKvpp7-jIifQIj7AQvBxbDMqKFDO4tE_03nDTY8uhOiAsg0P1n0A8NAUdSb2Hq3w0uenLnujFeb7k2hJmfbACB/s1600/odi-arch+Console1.png" imageanchor="1" style="clear:left; float:left;margin-right:1em; margin-bottom:1em"><img border="0" height="104" width="200" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgvzvgKeOv4VRXQwIItwQoLYAIVa-tGaXZI2NFPjIOELhm-3FEUi3IHZiSKvpp7-jIifQIj7AQvBxbDMqKFDO4tE_03nDTY8uhOiAsg0P1n0A8NAUdSb2Hq3w0uenLnujFeb7k2hJmfbACB/s200/odi-arch+Console1.png" /></a></div><br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhpa11BDYhfaVihtscigpsZn4ebzPStmNTNre1ki8bM-cpPEFl-8HmkTzv1ByfJuwkwU65oZqbul-USsGzDlEyAuzymKlrYKPwtZJYQxbDvHGXlCbkTqj55Nl-fLu0rILAl7e34FZJytEh1/s1600/odi-arch+Repository1.png" imageanchor="1" style="clear:left; float:left;margin-right:5em; margin-bottom:1em"><img border="0" height="104" width="200" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhpa11BDYhfaVihtscigpsZn4ebzPStmNTNre1ki8bM-cpPEFl-8HmkTzv1ByfJuwkwU65oZqbul-USsGzDlEyAuzymKlrYKPwtZJYQxbDvHGXlCbkTqj55Nl-fLu0rILAl7e34FZJytEh1/s200/odi-arch+Repository1.png" /></a></div><br />
<br />
<blockquote><ul><li>The ODI Repository consists of a <b>Master Repository</b> and one or more <b>Work repositories</b>.</li>
<li>Repositories are sets of tables that can be stored in RDBMS (i.e. Oracle, SQL Server, DB2, etc).</li>
<li>All objects used by the various ODI modules are stored in the repositories</li>
</ul><br />
<ul><li>The <b>Master Repository</b> contains </li>
<ul><li> Security information (user profiles and privileges)</li>
<li>topology information</li>
<li>source code for all version of all ODI objects</li>
</ul></ul><br />
<ul><li><b>Several work repositories</b> can coexist in the same installation (development, quality control, production)</li>
<b>
<li></b>Work Repositories store information for</li>
<ul><li><b>Models</b> (i.e. metadata) - datastores, columns, data integrity constraints, cross references, data lineage and impact analysis</li>
<li> <b>Projects</b> - interfaces, packages, procedures, folders, knowlege models, variables</li>
<li> <b>Runtime informatio</b>n - scenarios, load plans, scheduling information and logs</li>
</ul></ul></blockquote><br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgsIuHuc93kI9P0XTAgSTExPbmUmXX2vsCso4BQdGkUbefgBoMna6vqtNtqNgISypT-HlFalRYbUn47McfTwmuP8WEv1J4kIDg0PZ59r3ZtE076137J_NdzQbxQkApk8yUn3oelERNYTA6K/s1600/ODI-Architecture-Dev-Prod.png" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="397" width="600" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgsIuHuc93kI9P0XTAgSTExPbmUmXX2vsCso4BQdGkUbefgBoMna6vqtNtqNgISypT-HlFalRYbUn47McfTwmuP8WEv1J4kIDg0PZ59r3ZtE076137J_NdzQbxQkApk8yUn3oelERNYTA6K/s600/ODI-Architecture-Dev-Prod.png" /></a></div><br />Minima-Imagemhttp://www.blogger.com/profile/03798032367384388122noreply@blogger.com0tag:blogger.com,1999:blog-7852430287014615375.post-76716486750848593972012-04-01T12:21:00.000-07:002012-04-05T17:09:40.087-07:00Big Data, Deep Data, and who needs apples?<blockquote>So, they took as input "Big Data" from a double-hammer and not much later they had <span style="color:blue;"><code><b>"f = ma"</code></b></span> as output. <br />
Or how to generate new laws from context-less data?<br />
<ul><li>mentioned in Bradley Voytek's "<a href="http://radar.oreilly.com/2012/03/data-science-deep-data-information-paradox.html" target=_blank>Automated science, deep data and the paradox of information</a>" </li>
<li>and <a href="http://creativemachines.cornell.edu/sites/default/files/Science09_Schmidt.pdf" target=_blank>here</a> is the original article ("<a href="http://creativemachines.cornell.edu/sites/default/files/Science09_Schmidt.pdf" target=_blank>Distilling Free-Form Natural Laws from Experimental Data</a>")</li>
</ul>I enjoyed <a href="http://www.radiolab.org/2010/apr/05/limits-of-science/" target=blank>this</a> story... <br />
</blockquote><embed src="http://www.radiolab.org/media/audioplayer/player5.swf" width="620" height="429" wmode="transparent" allowscriptaccess="always" quality="high" flashvars="file=http://www.radiolab.org/audio/xspf/91712/&repeat=list&autostart=false&popurl=http://www.radiolab.org/audio/xspf/91712/%3Fdownload%3Dhttp%3A//www.podtrac.com/pts/redirect.mp3/audio.wnyc.org/radiolab/radiolab041610c.mp3"></embed><script type="text/javascript">(function(){var s=function(){__flash__removeCallback=function(i,n){if(i)i[n]=null;};window.setTimeout(s,10);};s();})();</script><br />
<br />
<br />
<br />Minima-Imagemhttp://www.blogger.com/profile/03798032367384388122noreply@blogger.com0tag:blogger.com,1999:blog-7852430287014615375.post-71699432559473014102012-03-30T14:49:00.000-07:002012-04-02T15:00:48.785-07:00Data Warehouse with Oracle Data Integrator (ODI)<div class="separator" style="clear: both; float: right;"><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7et2WtvbHj-TgGXo-iw2HNsWQBtVPGmYhTcYQEzHu9Wbrzm5cq0EaiiDqYg31BNURmiUfjkxhLoRMPE-qXq0HOw1YYH5mKLCvnXatuaTvmS9iOxdVP9MP4oOhIGUmHxpeorMhaHNI8l2s/s1600/oracle-11g-logo.gif" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" height="78" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7et2WtvbHj-TgGXo-iw2HNsWQBtVPGmYhTcYQEzHu9Wbrzm5cq0EaiiDqYg31BNURmiUfjkxhLoRMPE-qXq0HOw1YYH5mKLCvnXatuaTvmS9iOxdVP9MP4oOhIGUmHxpeorMhaHNI8l2s/s400/oracle-11g-logo.gif" width="246" /></a></div><br />
<div class="separator" style="clear: both; text-align: center; float: right;"><table><tr><td><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgbC8FEft9hpUOZpmK055nLZpj0PcFB-y2VGr2oAq_ldZ4yOUQ0Wj6HmtkwcCCwf2O_-_jyYmk5JMH6fo3E9du_ZGUbHUDOIyO_Q6ftWA7Q-NSBhgEGBYcU4Pjuf8T4NTRfBYH_TADCHZSg/s1600/ODI++Best+Practices+wp.png" imageanchor="1" style="clear:right; float:right; margin-left:1em; margin-bottom:1em"><img border="0" height="193" width="200" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgbC8FEft9hpUOZpmK055nLZpj0PcFB-y2VGr2oAq_ldZ4yOUQ0Wj6HmtkwcCCwf2O_-_jyYmk5JMH6fo3E9du_ZGUbHUDOIyO_Q6ftWA7Q-NSBhgEGBYcU4Pjuf8T4NTRfBYH_TADCHZSg/s200/ODI++Best+Practices+wp.png" /></a></div></td></tr>
<tr><td></td></tr>
</table></div></div><br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: medium;"><br />
Building a <b>Data Warehouse:</b> typical steps and milestones</span><br />
<blockquote><ul><li>Research and specify <b>business needs</b> (<b>Key Indicators</b>)</li>
<li>Identify <b>data sources</b> relevant to generate key indicators</li>
<li>Define <b>business rules</b> to <b>transform source information</b> into <b>key indicators</b></li>
<li><b>Model</b> the <b>data structure</b> of the <b>target</b> warehouse to store the key indicators</li>
<li>Populate the indicators by<b> implementing business rules</b></li>
<li>Measure the overall <b>accuracy of the data</b> by setting up <b>data quality rules</b></li>
<li><b>Develop reports</b> on key indicators</li>
<li>Make <b>key indicators and metadata available to business users</b> through <b>ad-hoc query tools</b> or <b>predefined reports</b></li>
<li><b>Measure</b> business users’ <b>satisfaction</b> and add/modify key indicators</li>
</ul></blockquote><br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: medium;"><br />
Using <b>Oracle Data Integrator (ODI)</b> in a <b>Data Warehouse project:</b> actors involved and some assigned tasks</span><br />
<blockquote><table><tr><td width="10%"><br />
<div class="separator" style="clear: both; text-align: center; float:left;margin-right:1em; margin-bottom:1em;"><img border="0" height="64" width="64" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjrG2Q5155vxyntc3iUo38dm0DsJZjZ1Ucpht-fVXlAdHDymnwUoE8DgxaZQjBpaeI4JD-Xsv74BgMjW7j_ouwEOYv7VgpAOqJeYjfREsZ03XClScSm5lS95Fo2zuIZZPurNNxGibOdomsS/s100/icon+-+business+users.png" /><br />
Business User</div></td> <td> <ul><li>Access the final calculated key indicators</li>
<li>Use reports and ad-hoc queries</li>
<li>May need to understand the definition of an indicator</li>
<li>May need to be aware of <b>data quality issues</b></li>
<ul><span style="color:blue;">
<li>when was the last time the table was updated?</li>
<li>How many records were added, update, removed in the table?</li>
<li>What are the rules that calculate a particular indicator?</li>
<li>Where does the data come from, and how is it transformed?</li>
</span></ul></ul></td> </tr>
<tr><td><div class="separator" style="clear: both; text-align: center;float:left;margin-right:1em; margin-bottom:1em;"><img border="0" height="64" width="64" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgpRfcj5c8PoI-XGCbBZ3wCocZinVQG6RlmRNyf7jAufj2-jZ7oNXAx1LplXOF5SagQPp31Rfe-Xt4v2AZQs3Zk0dZlu9ifpsV9i051cR0hxPnsaJIIeougT1G0GMv1R7VEXlEPqyC_dcUH/s100/icon+-+business+analyst.png" /><br />
Business Analyst</div></td> <td><ul><li>Define the key indicators</li>
<li>Identify the <b>source applications</b></li>
<ul><span style="color:blue;">
<li>How many different source applications need to be considered?</li>
<li>Is the data needed for key indicators available in the selected pool of source applications?</li>
<li>What data quality issues are present in the source systems?</li>
</span></ul><li><b>Specify business rules</b> to transform source data into meaningful target indicators</li>
<ul><span style="color:blue;">
<li>Projects can use the <b>ODI Designer</b> to directly specify the business rules</li>
<li><b>For each target table</b>, specify also: </li>
<ul><li><b>Target datastore</b> - name the target datastore </li>
<li><b>Description of transformation</b> - describe its purpose </li>
<li><b>Integration strategy</b> - How data should be written to target (replace table, append, update, etc). Each strategy specified will correspond to an <b>ODI Integration Knowledge Module</b>. </li>
<li><b>Refresh frequency</b></li>
<li><b>Dependencies</b> - what datastores need to be loaded or jobs executed prior to this one </li>
<li><b>Source datastores</b> - source databases, applications, etc used </li>
<li><b>Source ODI Model</b> - </li>
<li><b>Datastore name, Fiel Mappings and transformations, Links or Join criteria, Filters, Data Quality requirements, constraint names and expressions</b>, etc</li>
</ul></span></ul><li>Maintain translation data from operational semantics to the Data Warehouse semantic</li>
</ul></td> </tr>
<tr><td><div class="separator" style="clear: both; text-align: center;float:left;margin-right:1em; margin-bottom:1em;"><img border="0" height="64" width="64" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhjwA51CnwSVgEUCeI5VOgqy6_V6tGjSnUfBC4G6mqfLxLqo6AQ3FaS1nfxMfXSydssTDQH9X6h6V-3F0zCD3VXqOme115QV4JHuqhSRrHEkAHp2vP5900K8HD-TG24S_AcFjSoqRU-P_hP/s100/icon+-+developer.png" /><br />
Developer</div></td> <td><ul><li>Implement the business rules as specified by business analysts</li>
<li>Provide executable scenarios to the production team</li>
<li>Must understand infrastructure details and have business knowledge of source applications</li>
</ul></td> </tr>
<tr><td><div class="separator" style="clear: both; text-align: center;float:left;margin-right:1em; margin-bottom:1em;"><img border="0" height="54" width="64" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjoQ6jlYNxa2H9X8_GAH5UivwOisXK3TtccNwvA0ugvf4_GhjQn_wipK-WQEseDdP3stZpZomBwSDcOqu38-rQLnke3CRk3cHO5uWzOaZ4W6Kqc9jB-5KbiMO-_mOWoOO9szwiQLX-JN8yQ/s100/icon+-+metadata+admin.png" /><br />
Metadata Administrator</div></td> <td><ul><li><b>Reverse engineer</b> source and target applications</li>
<ul><li>Understand content and structure of source applications</li>
<li>Connect to source applications and capture their metadata</li>
<li><b>Define data quality business rules (specified by Business Analyst) in ODI repository</b></li>
<ul><span style="color:blue;">
<li>What level of Data Quality is required?</li>
<li>who are the business owners of source data?</li>
<li>What should be done with rejected records?</li>
<li>There should be an error recycling strategy?</li>
<li>How would business users modify erroneous source data?</li>
<li>Should a GUI be provided for source data correction?</li>
</span></ul></ul><li>Guarantee the overall consistency of Metadata in the various environments (dev, test, prod: repository)</li>
<li>Participate in the data modeling of key indicators</li>
<li>Add comments, descriptions and integrity rules (PK, FK, Check, etc)in the metadata</li>
<li>Provide version management</li>
</ul></td> </tr>
<tr><td><div class="separator" style="clear: both; text-align: center;float:left;margin-right:1em; margin-bottom:1em;"><img border="0" height="64" width="64" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhCGFN3m16Drz9XyJTwXlrNRJLVXVuo1kkcSquEwV25zMP85qVvSIgMLKfm5fJv9DNuJSjTIFxa0DTEdaGPXFzo8xGrJGxZhH2AasEv4Ah7rxxOoHLDfAaNAk7hBk0yAdCGzLuAO1r_amGk/s100/icon+-+dba.png" /><br />
Database Administrator</div></td> <td><ul><li>Define technical database structure supporting the various layers in the data warehouse project (and ODI structure)</li>
<li>Create database profiles needed by ODI </li>
<li><b>Create schemas and databases for the staging areas</b></li>
<ul><span style="color:blue;">
<li>Describe columns inside the data dictionary of database - COMMENT ON TABLE/COLUMN</li>
<li>Avoid using PKs of source systems as PKs in target tables. Use counter or identity columns instead.</li>
<li>Design Referential Integrity and reverse engineerd FKs in ODI models.</li>
<li>Do not implement RIs on target database (for performance). Data quality control should guarantee data integrity.</li>
<li>Standardize obejct naming conventions</li>
</span> </ul><li>Distributed and maintain the descriptions of the environments (topology)</li>
</ul></td> </tr>
<tr><td><div class="separator" style="clear: both; text-align: center;float:left;margin-right:1em; margin-bottom:1em;"><img border="0" height="64" width="64" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh1A2umSGxYaL5bcjpTM-hjNQa1n3OWQ7G8dDe1KaaD3BacjX5LJVcsPZUytwDJ7Jjtbi5cHMVWrC9kI3KOLk1sdzrTqiZjYN_dZblmNlhtc8fU0R-fCFNNEEla5eNNoLBk-lKfujwKLdtF/s100/icon+sys+admin1.png" /><br />
System Admin</div></td> <td><ul><li>Maintain technical resources for the Data Warehouse project</li>
<li>May install and monitor schedule agents</li>
<li>May backup and restore repositories</li>
<li>Install and monitor ODI console</li>
<li>Setup the various enviroments (dev, test, prod)</li>
</ul></td> </tr>
<tr><td><div class="separator" style="clear: both; text-align: center;float:left;margin-right:1em; margin-bottom:1em;"><img border="0" height="64" width="64" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhTObeAOd6J04_Z4ZcsDoJ2IvYRepcn-AHhkmZrjzRBJUPM1nzvEmQJ79X39JqyoM-yyN5vPp7m0Y96C2UT1XU4GkEtmqbR-CwDfkzWvcw90eaW-lbTv4H9B3Mf5ZRpbd8M_MgDJOzJs6dV/s100/icon+-+secadmin1.png" /><br />
Security Admin</div></td> <td><ul><li>Define the security policy for the ODI Repository</li>
<li>Creates ODI users and grant rights on models, projects and contexts</li>
</ul></td> </tr>
<tr><td><div class="separator" style="clear: both; text-align: center;float:left;margin-right:1em; margin-bottom:1em;"><img border="0" height="60" width="64" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi8O5SwIArCK8ttd7Te-hTp85_NOh9XeeNdPu0nUV990F_t2RZpUsZJQTdPTgb_B6YZ_f_WIQT8M_EluUTP5Vwa_JPfLvlz3XBdRJneLfNnwiZKWpDYyKl2tzELVxeKOJ5cdZ3i6XqNDQ-F/s400/icon+-+operator.png" /><br />
Operator</div></td> <td><ul><li>Import released and tested scenarios into production environment</li>
<li>Schedule the execution of production scenarios</li>
<li>Monitor execution logs and restart failed sessions</li>
</ul></td> </tr>
</table></blockquote><br />
<br />
<br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: medium;"><br />
Conceptual Architecture of an ODI solution</span><br />
<blockquote><ul><li>The ODI Repository is the central component.</li>
<li>ODI Repository stores configuration information about</li>
<ul><li>IT infrastructure and topology</li>
<li>metadata of all applications</li>
<li>projects</li>
<li>Interfaces</li>
<li>Models</li>
<li>Packages</li>
<li>Scenarios </li>
<li>Solutions - versioning control</li>
</ul><li>You can have various repositories within an IT infrastructure, linked ti separated environments that exchange metadata and scenarios (i.e. development, test, user acceptance test, production, hot fix)</li>
</ul></blockquote><div class="separator" style="clear: both; text-align: center;"><span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: normal;"><br />
Example: two environments, components, actors and tasks</span><br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiAZaEWcK3p2h9qZkWs3BwVM7uzUDNOdaMhYrD8jesZHUdMkoVRIi1C15cRnbB923pSDogl8Z5XtxxYI8wSy7fM8gjGDVPjtYsm9VjvzpQEwtShadLOmzPgtUN5lQBp9u2ktCpmQqRDpHGM/s1600/ODI-Architecture-Dev-Prod.png" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="397" width="600" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiAZaEWcK3p2h9qZkWs3BwVM7uzUDNOdaMhYrD8jesZHUdMkoVRIi1C15cRnbB923pSDogl8Z5XtxxYI8wSy7fM8gjGDVPjtYsm9VjvzpQEwtShadLOmzPgtUN5lQBp9u2ktCpmQqRDpHGM/s600/ODI-Architecture-Dev-Prod.png" /></a><br />
(source: <a href="http://www.oracle.com/technetwork/middleware/data-integrator/overview/odi-bestpractices-datawarehouse-whi-129686.pdf" target=_blank>Oracle 2008</a>)</div><br />
<br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: medium;"><br />
Suggested environment configuration for a Data Warehouse project with ODI:</span><br />
<blockquote><br />
(1) A <b>single master repository:</b> <br />
<ul><li>holds all the <b>topology and security</b> information. </li>
<li><b>All</b> the<b> work repositories are registered here.</b> </li>
<li>Contains <b>all the versions</b> of objects that are committed by the designers.</li>
</ul><br />
(2) <b>“Development” work repository:</b> <br />
<ul><li>Shared by all ODI designers </li>
<li>Holds all the projects and models under <b>development</b>.</li>
</ul><br />
(3) <b>“Testing” work repository</b> <br />
<ul><li>shared by the IT testing team. </li>
<li>Contains all the projects and models <b>being tested</b> for future release.</li>
</ul><br />
(4) <b>“User Acceptance Tests” work repository:</b> <br />
<ul><li>shared by the IT testing team and business analysts. </li>
<li>Contains all the projects and models <b>about to be released</b>. </li>
<li>Business analysts will use the ODI Console on top of this repository to validate the scenarios and transformations before releasing them to production.</li>
</ul><br />
(5) <b>“Production” work repository</b> <br />
<ul><li>shared by the production team, the operators and the business analysts. </li>
<li>Contains all the projects and models in read-only mode for metadata lineage, as well as all the released scenarios.</li>
</ul><br />
(6) <b>“Hot fix” work repository:</b> <br />
<ul><li>shared by the maintenance team and the development team. </li>
<li>Usually empty, but whenever a critical error happens in production, the maintenance team restores the corresponding projects and models in this repository and performs the corrections with the help of the development team.</li>
<li>Once the problems are solved, the scenarios are released directly to the production repository and the new models and projects are versioned in the master repository.</li>
</ul></blockquote><br />
<br />
<div class="separator" style="clear: both; text-align: center;float: center;"><span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: normal;"><br />
Typical environment (I): </span><br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhsAHsAjx-xZmOosDhAIivPKOD1FNk1grBJBzb0Up8KUZhvJM54Y9ZTcLyLFUBzoMJ0ZR_Tb9gtqWh4KNqdYNh_B6EcTbXNZODlM1zMV0ma6nsfZ8qjCLc7wUFO-Ne3S33P3AJkO78qxS3t/s1600/ODI-Architecture-Teams-Repositories-bw.png" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="334" width="600" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhsAHsAjx-xZmOosDhAIivPKOD1FNk1grBJBzb0Up8KUZhvJM54Y9ZTcLyLFUBzoMJ0ZR_Tb9gtqWh4KNqdYNh_B6EcTbXNZODlM1zMV0ma6nsfZ8qjCLc7wUFO-Ne3S33P3AJkO78qxS3t/s600/ODI-Architecture-Teams-Repositories-bw.png" /></a><br />
(source: <a href="http://www.oracle.com/technetwork/middleware/data-integrator/learnmore/odi-best-practice-data-warehouse-168255.pdf" target=_blank>Oracle 2010</a>)</div><br />
<br />
<div class="separator" style="clear: both; text-align: center; float:center;"><span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: normal;"><br />
Typical environment (II): Separate Master Repository for Production</span><br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiZUtuqscY8Pv06FWZU6kOhmDHVJYCgWIhtVtTm5JMatLLHM9dn5leYe6jpZ7nQF1c6KDs4psnNED5auk_pq7OlPuq4i8eI8B6wAasiRJ0lJi7GDzjszAea7ODmBFW8168OxVnPtvb0KFVT/s1600/ODI-Architecture-Teams-Repositories-bw2.png" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="334" width="600" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiZUtuqscY8Pv06FWZU6kOhmDHVJYCgWIhtVtTm5JMatLLHM9dn5leYe6jpZ7nQF1c6KDs4psnNED5auk_pq7OlPuq4i8eI8B6wAasiRJ0lJi7GDzjszAea7ODmBFW8168OxVnPtvb0KFVT/s600/ODI-Architecture-Teams-Repositories-bw2.png" /></a><br />
(source: <a href="http://www.oracle.com/technetwork/middleware/data-integrator/learnmore/odi-best-practice-data-warehouse-168255.pdf" target=_blank>Oracle 2010</a>)</div>Minima-Imagemhttp://www.blogger.com/profile/03798032367384388122noreply@blogger.com1tag:blogger.com,1999:blog-7852430287014615375.post-90927314465691685652012-03-28T17:08:00.000-07:002012-04-05T17:10:11.299-07:00Wanderlust<blockquote><span style="color: blue;"><b>(coffee break)</b></span><br />
</blockquote><blockquote>Spending lots of your time configuring RAC, freshing MVs, checking Grid Control charts..<br />
So I go for a walk. <a href="http://www.amazon.com/Wanderlust-History-Walking-Rebecca-Solnit/dp/0140286012" target=_blank>Wander about</a>, wonder... <br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://www.amazon.com/Wanderlust-History-Walking-Rebecca-Solnit/dp/0140286012" imageanchor="1" style="margin-left:1em; margin-right:1em" target=_blank><img border="0" height="354" width="265" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgQpml5KhV39MY9fSHCkbsT6oGgbTn7t-syAPZ5qsvWxkjFHtmeJ-peNlniHu9XMmKaiKw7oyARMJSr0PNiXlC8zsSXG6lmRNjO4JAazTdOJEsKnXdt7s3ZolOzQzd3S4FIdxpJ-mNKiFWm/s400/wanderlust.png" /></a></div></blockquote>Minima-Imagemhttp://www.blogger.com/profile/03798032367384388122noreply@blogger.com0tag:blogger.com,1999:blog-7852430287014615375.post-36096678642066530392012-03-21T13:45:00.000-07:002012-03-30T13:33:13.148-07:00Oracle On-line Analytic Processing (OLAP) (I)<div class="separator" style="clear: both; float: right;"><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7et2WtvbHj-TgGXo-iw2HNsWQBtVPGmYhTcYQEzHu9Wbrzm5cq0EaiiDqYg31BNURmiUfjkxhLoRMPE-qXq0HOw1YYH5mKLCvnXatuaTvmS9iOxdVP9MP4oOhIGUmHxpeorMhaHNI8l2s/s1600/oracle-11g-logo.gif" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" height="78" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7et2WtvbHj-TgGXo-iw2HNsWQBtVPGmYhTcYQEzHu9Wbrzm5cq0EaiiDqYg31BNURmiUfjkxhLoRMPE-qXq0HOw1YYH5mKLCvnXatuaTvmS9iOxdVP9MP4oOhIGUmHxpeorMhaHNI8l2s/s400/oracle-11g-logo.gif" width="246" /></a></div><div class="separator" style="clear: both; text-align: center; float: right;"><table><tr><td><br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://www.oracle.com/technetwork/database/options/olap/oracle-olap-11gr2-twp-132055.pdf" imageanchor="1" style="margin-left:1em; margin-right:1em" target=_blank><img border="0" height="120" width="200" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgjUXwAH6fNjYTCL178X67-MfxX_KNhWw1JQsCcEbjA_XOYb-1t98uw1i0WIY-xH_jEsYTPD4xKW7xA9TQvaWpiOCosZBBhC7UmLT7U7ZgCsYXfHnGik2Y5Yye9YKQbJXy9u2yciuoq0TKE/s200/2009-whitepaper.png" /></a></div></td></tr>
<tr><td><br />
<div class="separator" style="clear: both; text-align: center;margin-left:1em; margin-right:1em; float:right;"><a href="http://www.oracle.com/pls/db112/to_toc?pathname=olap.112/e17123/toc.htm" imageanchor="1" style="margin-left:1em; margin-right:1em" target=_blank><br />
<img border="0" height="143" width="200" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhwcthuqkI_XcVxE2wd6lurJLOTLSYSbx0a7rclqS2jcTLtwuVYGHJoedTCrTmAV_XDtzP9szrHnU773f59BfHyozqQaENhPrxNHsaBocoNNzc6ZEU8Kte7XnsKXVDFXynXm27EY6UQFmSu/s200/OLAP+-+UserGuide.png" /></a></div></td></tr>
</table></div></div><br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: medium;"><br />
<b>OLAP Cubes</b> can play <b>three (3) roles</b> in an Oracle Installation:</span><br />
<blockquote><br />
<b>(a)</b> Full-featured, fully <b>integrated multidimensional server</b><br />
<ul><li>Applications can <b>query all data</b> using a <b>dimensional query language</b> (<b>Oracle OLAP API</b> or <b>MDX</b>)</li>
<li> <b>OLAP engine</b> runs <b>within the kernel</b> of Oracle Database.</li>
<li><b>Dimensional objects (cubes, measures, dimensions, levels, hierarchies, attributes)</b> are stored in Oracle Database in their <b>native multidimensional format</b>.</li>
<li>Cubes and other dimensional objects are <b>first class data objects</b> represented in the data dictionary.</li>
<li>Data security is administered in the <b>standard way</b>, by granting and revoking privileges to users and roles.</li>
<li>Applications can query dimensional objects using SQL.</li>
</ul><br />
<b>(b) A Summary management solution</b><br />
<ul><li>The <b>Oracle cube</b> is used to manage summary data and is <b>accessed transparently</b> by SQL-based BI applications as a <b>Cube-organized Materialized View</b> using <b>Query rewrite</b> feature.</li>
<li><b>Cube-Organized MVs</b> were introduced on Oracle 11g, and play the same role as <b>table-based MVs</b></li>
<li><b>Cube-organized MVs</b> allow any application to <b>transparently access</b> summary data managed by the cube. </li>
<li><b>Cube-organized MVs</b> provide substantial query performance enhancement with much lower maintenance overhead than table-based MV alternatives</li>
<li>With Cube-organized MVs, applications query the detail tables and the database <b>automatically rewrites the query</b> to access summary data in the materialized view.</li>
</ul><br />
To expose the cube as a Materialized View, you can use the <b>Analytic Workspace Manager:</b><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhP6xbOSi2xelnCKi7KY-vpwEKgjuMy2sxwWLE20zuHe-nqt2p5eds_u4InJhfl0L3CWBSGSak2-6hgilp6JvK5BN2oNUXMPPDuKp03kvKVhlEJkfTJFHbV5Z6It1ZDkvK70T-X9EZ83ZfC/s1600/OLAP+-+AWM+-+Expose+Olap+Cube+as+Materialized+View.png" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="252" width="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhP6xbOSi2xelnCKi7KY-vpwEKgjuMy2sxwWLE20zuHe-nqt2p5eds_u4InJhfl0L3CWBSGSak2-6hgilp6JvK5BN2oNUXMPPDuKp03kvKVhlEJkfTJFHbV5Z6It1ZDkvK70T-X9EZ83ZfC/s400/OLAP+-+AWM+-+Expose+Olap+Cube+as+Materialized+View.png" /></a></div><br />
Both detail and summary data in the cube are exposed in the Materialized View. <br />
When the detail tables are queried, the optimizer rewrite the query to use the cube-organized materialized view <br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;">SQL> select t.calendar_year_name,
p.department_name,
cu.region_name,
sum(s.quantity) as quantity,
sum(s.sales) as sales
from times t,
customers cu,
products p,
sales_fact s
where cu.customer_key = s.customer
and p.item_key = s.product
and s.day_key = t.day_key
group by t.calendar_year_name, p.department_name, cu.region_name;
Explain plan:
OPERATION
-----------------------------------
SELECT STATEMENT
HASH
CUBE SCAN CB$SALES_CUBE
</pre><br />
<br />
<b>(c) A supplier of rich analytic content</b><br />
<ul><li>OLAP cubes are exposed as Cube views. </li>
<li>Cube views include detail and aggregate data as individual columns (measures)</li>
<li>Cube views can list complex analytic content, including hiearchical aggregations, statistical forecasts, additive and non-additive aggregations, calculated measures, etc</li>
</ul></blockquote><br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: medium;"><br />
<b>Cubes and Dimensions</b> seen from a <b>relational perspective</b></span><br />
<blockquote>Since Oracle provides a complete SQL interface to the OLAP cube: <br />
<ul><li>Cubes and Dimensionas can be thought of as relational objects.</li>
<li>Cubes as relational objects that offer improved performance and advanced analytic content, and</li>
<li>Dimensions as relational objects that include columns with information useful to creating hiearchical queries.</li>
<li>The cube is simply another data type in the database. </li>
</ul></blockquote><table><tr> <td><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiIWCw-z6v1Vsj8lckY7UAfYbbVD_QJm7fx6yX7nRcXmHVTOHiiS0Fk7QcPlhXCQVH-9JrJsJsee2IM-bUGUS0Hv1_RnJVq_XObFuAro-ZbZh6FMgBPO18WPDlRspGXLP7MTy1dZdePa64Z/s1600/OLAP+Cube+-+Dimensions+Exposed.png" imageanchor="1" style="clear:left; float:left;margin-right:1em; margin-bottom:1em"><img border="0" height="373" width="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiIWCw-z6v1Vsj8lckY7UAfYbbVD_QJm7fx6yX7nRcXmHVTOHiiS0Fk7QcPlhXCQVH-9JrJsJsee2IM-bUGUS0Hv1_RnJVq_XObFuAro-ZbZh6FMgBPO18WPDlRspGXLP7MTy1dZdePa64Z/s400/OLAP+Cube+-+Dimensions+Exposed.png" /></a></div></td> <td><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhx-9OaAZdrtu1RL7tYUs-JK-kezjwZ8Ldh69nFRgpvyJ4JB6Dpd7o_F6fGEQrmBcjs08mqVuZ5eh8G_VGB4cJLMs7DiXywylRZuqUTWcMNhzKDEZQJIcpsKE9CrEf0r9N09WG3-V9LXdch/s1600/StarSchema-OLAP+dimensions1.png" imageanchor="1" style="clear:right; float:right; margin-left:1em; margin-bottom:1em"><img border="0" height="400" width="391" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhx-9OaAZdrtu1RL7tYUs-JK-kezjwZ8Ldh69nFRgpvyJ4JB6Dpd7o_F6fGEQrmBcjs08mqVuZ5eh8G_VGB4cJLMs7DiXywylRZuqUTWcMNhzKDEZQJIcpsKE9CrEf0r9N09WG3-V9LXdch/s400/StarSchema-OLAP+dimensions1.png" /></a></div></td> </tr>
</table><br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: medium;"><br />
Some <b>benefits</b> <b>OLAP Cubes</b> bring to <b>Business Intelligence applications</b></span><br />
<blockquote><br />
<b>(a) Improved query performance for <span style="color: blue;"><b>Ad-hoc</b></span> query patterns</b><br />
<ul><li>As query patterns become less predictable, creation and maintenance of <b>materialized views</b> for specific queries becomes impractical. </li>
<li>Consider a data model with four dimensions (time, customer, product, channel), each with six levels of summarization (i.e in the time dimension: day, week, month, quarter, half year and year). </li>
<li>In this case, there are (4**6 -1) = 4095 possible combinations representing sumary level data that users might query.</li>
</ul><br />
<b>(b) Improved query performance for <span style="color: blue;">summary data</span></b><br />
<b>(c) Fast incremental update<br />
(d)Rich analytic content<br />
(e)Metadata that describes the logical business model and the relational representations of the cube</b><br />
</blockquote><br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: medium;"><br />
(Q) What <b>two categories</b> can <b>OLAP Metadata</b> be grouped into?</span><br />
<blockquote>(a) Metadata about the <b>Cube's structure</b>, data, and how it is calculated<br />
(b) Metadata about <b>how the cube is represented for query</b> using SQL cube views<br />
<ul><li>Description of the cube's structure include information on: <b>dimensions</b>, <b>hierarchies</b>, <b>levels</b>, <b>attributes</b> and <b>measures</b>.</li>
<li>For measures, metadata is available describing <b>how the cube is calculated</b> and the <b>calculation expression</b> of a measure.</li>
</ul><pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><b>SQL> select table_name from dict where table_name like '%CUBE%';</b>
TABLE_NAME
------------------------------
...
DBA_CUBES
DBA_CUBE_ATTRIBUTES
DBA_CUBE_ATTR_VISIBILITY
DBA_CUBE_BUILD_PROCESSES
DBA_CUBE_CALCULATED_MEMBERS
DBA_CUBE_DIMENSIONALITY
DBA_CUBE_DIMENSIONS
DBA_CUBE_DIM_LEVELS
DBA_CUBE_DIM_MODELS
DBA_CUBE_DIM_VIEWS
DBA_CUBE_DIM_VIEW_COLUMNS
TABLE_NAME
------------------------------
DBA_CUBE_HIERARCHIES
DBA_CUBE_HIER_LEVELS
DBA_CUBE_HIER_VIEWS
DBA_CUBE_HIER_VIEW_COLUMNS
DBA_CUBE_MEASURES
DBA_CUBE_VIEWS
DBA_CUBE_VIEW_COLUMNS
...
<b>=> Checking the existing dimensions in SALESTRACK analytic workspace </b>
<b>SQL> select * from <b>dba_cube_dimensions</b>;</b>
OWNER DIMENSION_NA DIMENSION_TYPE AW_NAME DEFAULT_HIERARCHY_NAME DESCRIPTION
---------- ------------ ----------------- ------------ ------------------------------ --------------------
OLAPTRAIN PRODUCT2 STANDARD SALESTRACK STANDARD product2
OLAPTRAIN CHANNEL STANDARD SALESTRACK SALES_CHANNEL Channel
OLAPTRAIN GEOGRAPHY STANDARD SALESTRACK REGIONAL Geography
OLAPTRAIN PRODUCT STANDARD SALESTRACK STANDARD Product
OLAPTRAIN TIME TIME SALESTRACK CALENDAR Time
</pre><br />
<b>You can use SQL Developer to:<br />
<ul><li>check existing hierachies, levels and order within hierarchies in the 'PRODUCT2' dimension.</li>
</ul></b><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjLjmcuNkRATt0JkxnBRWgPCIR4_aHhMS_w0OeRVj-JxfJ3ifnzvmihqzvaMrHtJtt8lWUj0gcgUmCp-IF9kafGQN5FOiOft53sgbFqrWR5hRdqDxv7fMDWR3OjQONzPgyQ5kis7yPQYxxL/s1600/hiearchy-levels1.png" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="201" width="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjLjmcuNkRATt0JkxnBRWgPCIR4_aHhMS_w0OeRVj-JxfJ3ifnzvmihqzvaMrHtJtt8lWUj0gcgUmCp-IF9kafGQN5FOiOft53sgbFqrWR5hRdqDxv7fMDWR3OjQONzPgyQ5kis7yPQYxxL/s400/hiearchy-levels1.png" /></a></div><br />
<ul><li><b>List</b> existing <b>cubes</b>, <b>measures within the cube</b>, whether a measure is<b> derived or stored</b> and the <b>equation that generates that measure:</li>
</ul></b><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEggjyEqn8bMnA_ZI8V_xiQtJCRUJ0mzFnxg1cC1JPANwt6ktbRo7kY_4XijiLYbcAZUhyJmaq-7YpRVSnz-I-Yo4SpW7mXgwgRr7icwd47hOzUzVl9Pbeb-zc1Zbf2tMwdfN_wjSykF5jsY/s1600/OLAP+Cube+measures1.png" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="231" width="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEggjyEqn8bMnA_ZI8V_xiQtJCRUJ0mzFnxg1cC1JPANwt6ktbRo7kY_4XijiLYbcAZUhyJmaq-7YpRVSnz-I-Yo4SpW7mXgwgRr7icwd47hOzUzVl9Pbeb-zc1Zbf2tMwdfN_wjSykF5jsY/s400/OLAP+Cube+measures1.png" /></a></div><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgUwUpjq9lbVIUJUv8_WhQzPG7-cbvql5-t-P90lpKzN-OjEskj_H7B7pemq4sE_SpS3OHMoVEnYPewsGBLZcNvfZ6Hp-j_bAOWqeKKtl7cUBmNtbrG5lvK_84oCMTZSFsHV-eZXQY8Cmgj/s1600/OLAP+Cube+SQL+Exposed1.png" imageanchor="1" style="clear:right; float:right; margin-left:1em; margin-bottom:1em"><img border="0" height="147" width="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgUwUpjq9lbVIUJUv8_WhQzPG7-cbvql5-t-P90lpKzN-OjEskj_H7B7pemq4sE_SpS3OHMoVEnYPewsGBLZcNvfZ6Hp-j_bAOWqeKKtl7cUBmNtbrG5lvK_84oCMTZSFsHV-eZXQY8Cmgj/s400/OLAP+Cube+SQL+Exposed1.png" /></a></div><br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: medium;">Cube Views</span><br />
<ul><li><b>OLAP Cubes</b> are exposed through <b>views</b> on a <b>star schema</b>. </li>
<li>As the structure of dimensions and cubes are updated,views are automatically maintained</li>
<li>If you add new measures, the cube view will be updated</li>
<li>Views are always in sync with the underlying dimensions and cubes</li>
<li>A single cube view exposes (as columns) <b>all the aggregations and all the calculations</b> for a cube</li>
</ul><pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><b>SQL> select * from dba_cube_views;</b>
OWNER CUBE_NAME VIEW_NAME
---------- --------------- -------------------
OLAPTRAIN FORECAST FORECAST_VIEW
OLAPTRAIN SALES_CUBE SALES_CUBE_VIEW
OLAPTRAIN NEW_CUBE NEW_CUBE_VIEW
<b>SQL> select owner, cube_name, view_name, column_name from dba_cube_view_columns;</b>
OWNER CUBE_NAME VIEW_NAME COLUMN_NAME
---------- --------------- ------------------------------ ------------------------------
OLAPTRAIN FORECAST FORECAST_VIEW BEST_FIT
OLAPTRAIN FORECAST FORECAST_VIEW LINEAR_REGRESSION
OLAPTRAIN SALES_CUBE SALES_CUBE_VIEW SALES
OLAPTRAIN SALES_CUBE SALES_CUBE_VIEW QUANTITY
OLAPTRAIN SALES_CUBE SALES_CUBE_VIEW SALES_YTD
OLAPTRAIN SALES_CUBE SALES_CUBE_VIEW SALES_YTD_PY
...
</pre><br />
<b>=> The Cube's SQL interface is also available in SQL Developer:</b><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhP2Pjq7DbmfiAxAcYQzoWyWtvgqkplr8CLecd5zu8ziX3KHyN5h3L9gXULrDtRKD2LsRsQi7O-xHtfDbn8MfPrryy53ZrM575mT7l3TiVzZCoCOLyqTcOE865jerRmBGv4lEnGy2HYPWDF/s1600/OLAP+Cube+SQL+Exposed2.png" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="317" width="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhP2Pjq7DbmfiAxAcYQzoWyWtvgqkplr8CLecd5zu8ziX3KHyN5h3L9gXULrDtRKD2LsRsQi7O-xHtfDbn8MfPrryy53ZrM575mT7l3TiVzZCoCOLyqTcOE865jerRmBGv4lEnGy2HYPWDF/s400/OLAP+Cube+SQL+Exposed2.png" /></a></div></blockquote>Minima-Imagemhttp://www.blogger.com/profile/03798032367384388122noreply@blogger.com0tag:blogger.com,1999:blog-7852430287014615375.post-46319785255948285662012-03-21T10:56:00.001-07:002012-04-04T15:11:12.329-07:00Using Oracle External Tables<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7et2WtvbHj-TgGXo-iw2HNsWQBtVPGmYhTcYQEzHu9Wbrzm5cq0EaiiDqYg31BNURmiUfjkxhLoRMPE-qXq0HOw1YYH5mKLCvnXatuaTvmS9iOxdVP9MP4oOhIGUmHxpeorMhaHNI8l2s/s1600/oracle-11g-logo.gif" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" height="78" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7et2WtvbHj-TgGXo-iw2HNsWQBtVPGmYhTcYQEzHu9Wbrzm5cq0EaiiDqYg31BNURmiUfjkxhLoRMPE-qXq0HOw1YYH5mKLCvnXatuaTvmS9iOxdVP9MP4oOhIGUmHxpeorMhaHNI8l2s/s400/oracle-11g-logo.gif" width="246" /></a></div><br />
<br />
<ul><li><b>External tables</b> do not reside in the database, and can be in any format for which an <b>access driver</b> is provided.</li>
<li>You <b>can</b> <i>select</i>, <i>join</i>, or <i>sort</i> external table data.</li>
<li>You <b>can also</b> <i>create views and synonyms</i> for external tables.</li>
<li>You <b>CANNOT</b> execute <i>DML</i> operations (UPDATE, INSERT, or DELETE) , and <b>CANNOT </b><i>create indexes</i> on external tables.</li>
</ul><br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace;">External tables and Data Warehouse</span><br />
<ul><li><b>Exporting</b> data from a database:</li>
<ul><li>External tables provide a framework to <b>unload</b> the result of an arbitrary SELECT statement into a <i>platform-independent Oracle-proprietary format</i> that can be used by <b>Oracle Data Pump</b>.</li>
</ul><li>External tables provide a valuable means for<b> performing basic extraction, transformation, and loading (ETL) tasks</b> that are common for data warehousing.</li>
</ul><br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace;">Creating External tables</span><br />
<ul><li><code>CREATE TABLE...ORGANIZATION EXTERNAL</code> statement.</li>
<li>This statement <b>creates only metadata in the data dictionary</b>.</li>
<li>External tables can be thought of as views that allows running any SQL query <b>against external data</b> without requiring that the external data first be loaded into the database.</li>
</ul><br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace;">Accessing External Data</span><br />
<ul><li>An <b>access driver</b> is the actual mechanism used to read the external data in the table.</li>
<li>When you use external tables to<b> unload data</b>, the metadata is automatically created based on the data types in the SELECT statement.</li>
</ul><br />
Oracle Database provides <b>two access drivers for external tables:</b><br />
<ul><li><b> ORACLE_LOADER driver </b>(<u>default</u>) </li>
<ul><li>allows the reading of data from external files using the Oracle loader technology. </li>
<li>provides data mapping capabilities which are a subset of the control file syntax of SQL*Loader utility. </li>
</ul><li><b> ORACLE_DATAPUMP driver</b></li>
<ul><li>lets you <b>unload data:</b> read data from the database and insert it into an external table, represented by one or more external files—and then reload it into an Oracle Database.</li>
</ul></ul><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjfbzMsN7D5F_d3pi3YZa8y3Ox09BSB2nCp_7kEhmCkjOA8U2CWLi_yd6dPC_c8oGaYIMI7j6ypvgK-vTinF_Xz3la0YwfTMxPiRPO-eCiVkJMdJVX-lz-9ZidwwVxvD-ruN_mw6usnrCyU/s1600/Unload_data1.png" imageanchor="1" style="clear:left; float:left;margin-right:1em; margin-bottom:1em"><img border="0" height="323" width="600" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjfbzMsN7D5F_d3pi3YZa8y3Ox09BSB2nCp_7kEhmCkjOA8U2CWLi_yd6dPC_c8oGaYIMI7j6ypvgK-vTinF_Xz3la0YwfTMxPiRPO-eCiVkJMdJVX-lz-9ZidwwVxvD-ruN_mw6usnrCyU/s600/Unload_data1.png" /></a></div><br />
<br />
<br />
<br />
<br />
<br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: large;"><br />
<b>Scenario 1:</b> <br />
Unload data to flat files and create External Table.<br />
<b>ORACLE_LOADER</b> driver</span><br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: normal;"><br />
Step 1 - Export data from table sh.customers</span><br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;">/* Step 1 - Export data from table sh.customers
Procedure UNLOAD_DATA receives table_name and table_owner as parameter and
unloads the table data to a flat file */
set serveroutput on
DECLARE
procedure unload_data(
p_table_name in varchar2,
p_owner in varchar2)
IS
sqltext varchar2(1000);
v_textrecord varchar2(2000);
cv sys_refcursor;
fdest utl_file.file_type;
type FileAttrRec is Record (
vfilexists BOOLEAN,
vfilelength number,
vblocksize binary_integer);
vfilerec fileattrrec;
vfilename varchar2(30);
procedure get_sql (
p_sqltext in out varchar2,
p_table_name in varchar2,
p_owner in varchar2)
IS
cursor c is
select column_name
from dba_tab_columns
where table_name = p_table_name and owner = p_owner
order by column_id;
i integer;
sql_stmt varchar2(2000);
from_clause varchar2(100);
begin
i :=0;
sql_stmt := 'SELECT ';
from_clause := 'FROM ' || p_owner ||'.' || p_table_name;
for r in c loop
if i = 0 then
sql_stmt := sql_stmt || ' ' || r.column_name;
i := 1;
else
sql_stmt := sql_stmt || '|| ''|''|| ' || r.column_name;
end if;
end loop;
sql_stmt := sql_stmt || ' as qrystr ' || chr(10) || from_clause;
p_sqltext := sql_stmt;
-- dbms_output.put_line( sql_stmt);
end get_sql;
begin
-- 1. Get Query SQL
dbms_output.enable(20000);
sqltext := '';
get_sql(sqltext, p_table_name, p_owner);
--dbms_output.put_line('Select stmt:' || sqltext);
-- 2. Open cursor for query. Write each row in the OS file
vfilename := 'unload_' || p_owner ||'_'|| p_table_name || '.dat';
utl_file.fgetattr('DATA_DUMP_DIR', vfilename, vfilerec.vfilexists,
vfilerec.vfilelength, vfilerec.vblocksize);
if vfilerec.vfilexists then
fdest := utl_file.fopen('DATA_PUMP_DIR', vfilename, 'a', 2048);
dbms_output.put_line('Destination file exists. Appending..');
else
fdest := utl_file.fopen('DATA_PUMP_DIR', vfilename, 'w', 2048);
end if;
open cv for sqltext;
LOOP
fetch cv into v_textrecord;
EXIT WHEN cv%NOTFOUND;
utl_file.put_line(fdest, v_textrecord, true);
END LOOP;
utl_file.fclose(fdest);
close cv;
END unload_data;
BEGIN
unload_data('CUSTOMERS' , 'SH');
END;
</pre><br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: normal;"><br />
Step 2 - Split unloaded file and create external table using the resulting files</span><br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;">$ ls -ltr
total 482928
...
-rw-r--r-- 1 oracle oinstall 10661903 2012-03-20 22:01 <b>unload_SH_CUSTOMERS.dat</b>
-- spliting the file in two smaller files
<b>$ split -30000 unload_SH_CUSTOMERS.dat unload_SH_CUSTOMERS.dat</b>
$ ls -ltr
total 503760
...
-rw-r--r-- 1 oracle oinstall 10661903 2012-03-20 22:01 unload_SH_CUSTOMERS.dat
-rw-r--r-- 1 oracle oinstall 5768182 2012-03-20 22:15 unload_SH_CUSTOMERS.dataa
-rw-r--r-- 1 oracle oinstall 4893721 2012-03-20 22:15 unload_SH_CUSTOMERS.datab
Step 2 - Create external table using unloaded file(s)
-- create the external table
SQL> Create table ext_customers1
(
"CUST_ID" NUMBER,
"CUST_FIRST_NAME" VARCHAR2(20 BYTE),
"CUST_LAST_NAME" VARCHAR2(40 BYTE),
"CUST_GENDER" CHAR(1 BYTE),
"CUST_YEAR_OF_BIRTH" NUMBER(4,0),
"CUST_MARITAL_STATUS" VARCHAR2(20 BYTE),
"CUST_STREET_ADDRESS" VARCHAR2(40 BYTE),
"CUST_POSTAL_CODE" VARCHAR2(10 BYTE),
"CUST_CITY" VARCHAR2(30 BYTE),
"CUST_CITY_ID" NUMBER,
"CUST_STATE_PROVINCE" VARCHAR2(40 BYTE),
"CUST_STATE_PROVINCE_ID" NUMBER,
"COUNTRY_ID" NUMBER,
"CUST_MAIN_PHONE_NUMBER" VARCHAR2(25 BYTE),
"CUST_INCOME_LEVEL" VARCHAR2(30 BYTE),
"CUST_CREDIT_LIMIT" NUMBER,
"CUST_EMAIL" VARCHAR2(30 BYTE),
"CUST_TOTAL" VARCHAR2(14 BYTE),
"CUST_TOTAL_ID" NUMBER,
"CUST_SRC_ID" NUMBER,
"CUST_EFF_FROM" DATE,
"CUST_EFF_TO" DATE,
"CUST_VALID" VARCHAR2(1 BYTE)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY data_pump_dir
ACCESS PARAMETERS
(
records delimited by newline
badfile data_pump_dir:'cust1ext%a_%p.bad'
logfile data_pump_dir:'cust1ext%a_%p.log'
fields terminated by '|'
missing field values are null
)
LOCATION ('unload_SH_CUSTOMERS.dataa', 'unload_SH_CUSTOMERS.datab')
)
PARALLEL
REJECT LIMIT UNLIMITED;
table EXT_CUSTOMERS1 created.
<b>SQL> select table_name, type_name, default_directory_name, reject_limit, access_parameters, property
from dba_external_tables;</b>
TABLE_NAME TYPE_NAME DEFAULT_DIRECTORY_NAME REJECT_LIMIT ACCESS_PARAMETERS
--------------- --------------- ------------------------ --------------- ------------------------------------------
EXT_CUSTOMERS1 ORACLE_LOADER DATA_PUMP_DIR UNLIMITED records delimited by newline
badfile data_pump_dir:'cust1ext%a_%p.bad'
logfile data_pump_dir:'cust1ext%a_%p.log'
fields terminated by '|'
missing filed values are null
<b>SQL> select count(*) from ext_customers1;</b>
COUNT(*)
----------
55500
</pre><br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiB7WCDTFUvoSYDt3VdUqdeYLHfWymfvOy5htGzb90lc4uDhZ0jsWhmOARX9z8HlwbYbqh1Nh7kvvITTUXHyqgFi1KGJdLqF2tWp-gDy-HyPqn5VBPJeR4jQjt-gxuCFFHWYCkyDLJRUU-2/s1600/Unload_data-datapump1.png" imageanchor="1" style="clear:left; float:left;margin-right:1em; margin-bottom:1em"><img border="0" height="314" width="600" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiB7WCDTFUvoSYDt3VdUqdeYLHfWymfvOy5htGzb90lc4uDhZ0jsWhmOARX9z8HlwbYbqh1Nh7kvvITTUXHyqgFi1KGJdLqF2tWp-gDy-HyPqn5VBPJeR4jQjt-gxuCFFHWYCkyDLJRUU-2/s600/Unload_data-datapump1.png" /></a></div><br />
<br />
<br />
<br />
<br />
<br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: large;"><br />
<b>Scenario 2</b>: Unloading and Loading Data with the <b>ORACLE_DATAPUMP</b> Access Driver</span><br />
<br />
<br />
<br />
<br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: normal;"> <br />
Step 1 <ul><li><b>(a)</b> Use CREATE TABLE ... ORGANIZATION EXTERNAL ... AS SELECT ...</li>
<li>This will create a file with the data resulting from the specified query. </li>
<li>The file is created with a binary format that can only be read by the <b>ORACLE_DATAPUMP access driver</b></li>
<li>The example below creates an external table and populates the dump file for the external table with the data from table sh.customers.</li>
</ul></span><br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;">connect sh/**;
<b>SQL> create table</b> customers1_xt
<b>organization external</b>
(
<b>type oracle_datapump</b>
default directory data_pump_dir
location ('customers1_xt.dmp')
)
as select * from sh.customers;
table created.
SQL>
<b>(b) Check the OS files created: </b>
$ ls -ltr
total 60672
-rw-r----- 1 oracle oinstall 10334208 2012-03-21 00:33 customers1_xt.dmp
-rw-r--r-- 1 oracle oinstall 123 2012-03-21 00:36 CUSTOMERS1_XT_7165.log
<b>(c) Compare descriptions of customers and customers1_xt tables.</b>
SQL> desc customers;
Name Null Type
---------------------- -------- ------------
CUST_ID NOT NULL NUMBER
CUST_FIRST_NAME NOT NULL VARCHAR2(20)
CUST_LAST_NAME NOT NULL VARCHAR2(40)
CUST_GENDER NOT NULL CHAR(1)
CUST_YEAR_OF_BIRTH NOT NULL NUMBER(4)
CUST_MARITAL_STATUS VARCHAR2(20)
CUST_STREET_ADDRESS NOT NULL VARCHAR2(40)
CUST_POSTAL_CODE NOT NULL VARCHAR2(10)
CUST_CITY NOT NULL VARCHAR2(30)
CUST_CITY_ID NOT NULL NUMBER
CUST_STATE_PROVINCE NOT NULL VARCHAR2(40)
CUST_STATE_PROVINCE_ID NOT NULL NUMBER
COUNTRY_ID NOT NULL NUMBER
CUST_MAIN_PHONE_NUMBER NOT NULL VARCHAR2(25)
CUST_INCOME_LEVEL VARCHAR2(30)
CUST_CREDIT_LIMIT NUMBER
CUST_EMAIL VARCHAR2(30)
CUST_TOTAL NOT NULL VARCHAR2(14)
CUST_TOTAL_ID NOT NULL NUMBER
CUST_SRC_ID NUMBER
CUST_EFF_FROM DATE
CUST_EFF_TO DATE
CUST_VALID VARCHAR2(1)
SQL> desc customers1_xt;
Name Null Type
---------------------- -------- ------------
CUST_ID NOT NULL NUMBER
CUST_FIRST_NAME NOT NULL VARCHAR2(20)
CUST_LAST_NAME NOT NULL VARCHAR2(40)
CUST_GENDER NOT NULL CHAR(1)
CUST_YEAR_OF_BIRTH NOT NULL NUMBER(4)
CUST_MARITAL_STATUS VARCHAR2(20)
CUST_STREET_ADDRESS NOT NULL VARCHAR2(40)
CUST_POSTAL_CODE NOT NULL VARCHAR2(10)
CUST_CITY NOT NULL VARCHAR2(30)
CUST_CITY_ID NOT NULL NUMBER
CUST_STATE_PROVINCE NOT NULL VARCHAR2(40)
CUST_STATE_PROVINCE_ID NOT NULL NUMBER
COUNTRY_ID NOT NULL NUMBER
CUST_MAIN_PHONE_NUMBER NOT NULL VARCHAR2(25)
CUST_INCOME_LEVEL VARCHAR2(30)
CUST_CREDIT_LIMIT NUMBER
CUST_EMAIL VARCHAR2(30)
CUST_TOTAL NOT NULL VARCHAR2(14)
CUST_TOTAL_ID NOT NULL NUMBER
CUST_SRC_ID NUMBER
CUST_EFF_FROM DATE
CUST_EFF_TO DATE
CUST_VALID VARCHAR2(1)
<b>(d) Check data on customers1_xt and compare with original table</b>
SQL> select count(*) from customers1_xt;
COUNT(*)
----------------------
55500
SQL> select * from customers minus select * from customers1_xt;
no rows selected
<b>(e) You CANNOT perform DML on the external table:</b>
SQL> delete from customers1_xt where cust_id = 100055;
delete from customers1_xt where cust_id = 100055
*
ERROR at line 1:
ORA-30657: operation not supported on external organized table
<b>(f) You can also Create an external table with multiple dump files and with a degree of parallelism higher than one.</b>
<b>SQL> create table</b> customers1_xtp3
<b>organization external</b>
(
<b>type oracle_datapump</b>
default directory data_pump_dir
<b>location('customers1_xtp31.dmp' ,'customers1_xtp32.dmp' ,'customers1_xtp33.dmp' )</b>
)
<b>parallel 3</b>
as select * from sh.customers;
$ ls -ltr
total 60672
-rw-r----- 1 oracle oinstall 10334208 2012-03-21 00:33 customers1_xt.dmp
-rw-r--r-- 1 oracle oinstall 123 2012-03-21 00:36 CUSTOMERS1_XT_7165.log
-rw-r--r-- 1 oracle oinstall 41 2012-03-21 10:13 CUSTOMERS1_XTP3_2690.log
-rw-r--r-- 1 oracle oinstall 41 2012-03-21 10:13 CUSTOMERS1_XTP3_2737.log
-rw-r--r-- 1 oracle oinstall 41 2012-03-21 10:13 CUSTOMERS1_XTP3_2739.log
-rw-r--r-- 1 oracle oinstall 41 2012-03-21 10:13 CUSTOMERS1_XTP3_2741.log
<b>-rw-r----- 1 oracle oinstall 2990080 2012-03-21 10:13 customers1_xtp32.dmp
-rw-r----- 1 oracle oinstall 3792896 2012-03-21 10:13 customers1_xtp33.dmp
-rw-r----- 1 oracle oinstall 3592192 2012-03-21 10:13 customers1_xtp31.dmp</b>
<b>(g) You can transfer or copy the dump file(s) and use them for another external table either in the same or in a different database.</b>
<b>SQL> Create table</b> customers2_xt
(
"CUST_ID" NUMBER,
"CUST_FIRST_NAME" VARCHAR2(20 BYTE),
"CUST_LAST_NAME" VARCHAR2(40 BYTE),
"CUST_GENDER" CHAR(1 BYTE),
"CUST_YEAR_OF_BIRTH" NUMBER(4,0),
"CUST_MARITAL_STATUS" VARCHAR2(20 BYTE),
"CUST_STREET_ADDRESS" VARCHAR2(40 BYTE),
"CUST_POSTAL_CODE" VARCHAR2(10 BYTE),
"CUST_CITY" VARCHAR2(30 BYTE),
"CUST_CITY_ID" NUMBER,
"CUST_STATE_PROVINCE" VARCHAR2(40 BYTE),
"CUST_STATE_PROVINCE_ID" NUMBER,
"COUNTRY_ID" NUMBER,
"CUST_MAIN_PHONE_NUMBER" VARCHAR2(25 BYTE),
"CUST_INCOME_LEVEL" VARCHAR2(30 BYTE),
"CUST_CREDIT_LIMIT" NUMBER,
"CUST_EMAIL" VARCHAR2(30 BYTE),
"CUST_TOTAL" VARCHAR2(14 BYTE),
"CUST_TOTAL_ID" NUMBER,
"CUST_SRC_ID" NUMBER,
"CUST_EFF_FROM" DATE,
"CUST_EFF_TO" DATE,
"CUST_VALID" VARCHAR2(1 BYTE)
)
ORGANIZATION EXTERNAL
(
<b>TYPE ORACLE_DATAPUMP</b>
DEFAULT DIRECTORY data_pump_dir
<b>LOCATION ('customers1_xtp31.dmp' ,'customers1_xtp32.dmp' ,'customers1_xtp33.dmp')</b>
);
<b>SQL> select count(*) from customers2_xt;</b>
COUNT(*)
----------------------
55500
<b>SQL> Select * from sh.customers minus select * from customers2_xt;</b>
no rows selected.
</pre>Minima-Imagemhttp://www.blogger.com/profile/03798032367384388122noreply@blogger.com0tag:blogger.com,1999:blog-7852430287014615375.post-49753770923182068772012-03-13T23:41:00.000-07:002012-03-31T10:37:19.879-07:00Data Warehouse: Basic Concepts (I)<div class="separator" style="clear: both; float: right;"><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7et2WtvbHj-TgGXo-iw2HNsWQBtVPGmYhTcYQEzHu9Wbrzm5cq0EaiiDqYg31BNURmiUfjkxhLoRMPE-qXq0HOw1YYH5mKLCvnXatuaTvmS9iOxdVP9MP4oOhIGUmHxpeorMhaHNI8l2s/s1600/oracle-11g-logo.gif" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" height="78" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7et2WtvbHj-TgGXo-iw2HNsWQBtVPGmYhTcYQEzHu9Wbrzm5cq0EaiiDqYg31BNURmiUfjkxhLoRMPE-qXq0HOw1YYH5mKLCvnXatuaTvmS9iOxdVP9MP4oOhIGUmHxpeorMhaHNI8l2s/s400/oracle-11g-logo.gif" width="246" /></a></div><br />
<div class="separator" style="clear: both; text-align: center; float: right;"><table><tr><td><br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://www.amazon.com/Data-Warehouse-Toolkit-Complete-Dimensional/dp/0471200247" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" height="200" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgsHL1fEXkiyArMB8sPj8XxSuBdwbYckmzABSGoXzh6EeQuYJZflndoiJjOvfc85GqVRNEHmCvub3XNCodL7Nkoizv9NxxbUbgcB2qywR-aTbwDhXtQXsbGD71K4-ul0Pz15Dz4_XLkTePU/s200/The-Data-Warehouse-Toolkit-Kimball-Ralph-9780471200246.jpg" width="160" /></a></div></td></tr>
<tr><td><a href="http://www.amazon.com/Data-Warehouse-Toolkit-Complete-Dimensional/dp/0471200247"><b>@Amazon</b></a></td></tr>
</table></div></div><br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: medium;"><br />
Common <b>requirements</b> of <b>Data Warehouse</b> systems</span><br />
<blockquote><ul><li>Must provide <b>easy access</b> to the organization's information</li>
<li><b>Show consistency</b> in the display of information</li>
<li>Must be <b>adaptive and resilient</b> to change</li>
<li>Must <b>safely keep</b> information</li>
<li>Must serve as a <b>foundation for improved decision making</b></li>
<li>Must be a<b>ccepted by the business community</b> in the enterprise</li>
</ul></blockquote><br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: medium;"><br />
Common components of a <b>Data Warehouse</b> (Kimball)</span><br />
<blockquote>Four components: <br />
(a) <b>Operational Source</b> System <br />
(b) <b>Data Staging </b>Area <br />
(3) <b>Data Presentation</b> Area <br />
(4) <b>Data Access</b> Tools<br />
<br />
"One of the biggest threats to DWH success is confusing the components' roles and functions." <br />
i.e. Don't give access to end users to the Staging Area. Don't build the DWH on a normalized design.</blockquote><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEje8vpQ2DNkfPqKrCZs8QvOGruJm6160zBaxihMc6RfT_XikzUEI_ZiW-DGZoCbbVcEP_G2Z5IpgJIhCzs7lpuRIROsUM84FjRcVUgEbekxMUTZcO7MDqF_ynVPVPUDzIfAM2pOkjujtf-L/s1600/DWH-Stages.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="424" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEje8vpQ2DNkfPqKrCZs8QvOGruJm6160zBaxihMc6RfT_XikzUEI_ZiW-DGZoCbbVcEP_G2Z5IpgJIhCzs7lpuRIROsUM84FjRcVUgEbekxMUTZcO7MDqF_ynVPVPUDzIfAM2pOkjujtf-L/s700/DWH-Stages.png" width="700" /></a></div><br />
<br />
<div class="separator" style="clear: both; float: center; text-align: center;"><img border="0" height="49" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh1obPX0ohePk8ipRmRioso5v8LtTHfy-pjoKTFbdv59HbC0uzZrTqaw3hCNzxZ2650_BzVWwMETrICntthQXh0h1zIon0f3igpWDpchpdi4hRVW4UMRaNfAMiBtweV25N4_NxU1pLJuAPP/s400/ETL-OperationalSystems1.png" width="400" /></div><br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: medium;"><br />
Characteristics of <b>source operational systems</b></span><br />
<blockquote><ul><li>Main priorities of source operational system are processing performance and availability</li>
<li><b>Queries</b> against source systems are <b>narrow, one-record-at-a-time queries</b>, and are <b>severy restricted in their demands</b> on the Operating System</li>
<li>Source systems are <b>not queried in broad and unexpected ways</b>: queries are <b>tunned</b>, <b>specific</b>, and <b>known before hand</b>.</li>
<li>Source systems <b>maintain little historical data</b></li>
</ul></blockquote><br />
<br />
<div class="separator" style="clear: both; float: center; text-align: center;"><img border="0" height="49" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjstNQeruJIaoR0UFEG_nslERQJcp3-DWz5_O6FNrqvLfmSBr_IBGKwdKVQhnHSNxrW-E_7EZxlZOowGei_nOHm7n518gyEs8l1mHHxE3xhcqAyahwwyL4Zps-E6AVStCvQHDYg1QURBWD2/s400/ETL-StagingArea1.png" width="400" /></div><span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: medium;"><br />
(Q) what are +two <b> key architectural requirements</b> for the <b>staging area</b>?</span><br />
<blockquote><ul><li>(a) Staging area is <b>off-limits to business users</b></li>
<li>(b) <b>Does not provide</b> query and presentation services</li>
<li>(c) <b>Does not</b> (should not) need to have a <b>normalized structure</b> to hold data</li>
</ul></blockquote><br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: large;"><br />
(Q) What are some <b>common characteristics of the staging area</b>?</span><br />
<blockquote><ul><li>Dominated by tasks such as <b>sorting and sequential processing</b></li>
<li>Often <b>not based on relational technology</b> but instead on many flat files</li>
<li>Usually most of the tasks of <b>extraction, transformation and loading (ETL)</b> of data from operational systems into the data warehouse is concentrated in the staging area.</li>
<li>Tables in the staging area should be segregated from the "live" data warehouse, i.e., they <b>should NOT</b> be available for end users' queries </li>
<li>A basic implementation here is to have an identical schema to the one that exists in the source operational system(s) but with some structural changes to the tables, such as <b>range partitioning</b>.</li>
<li>Alternatively, in some implementations all data transformation processing is done “on the fly” as data is extracted from the source system before it is inserted directly into the Presentation area. </li>
</ul></blockquote><br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: large;"><br />
(Q) What are <b>four common transformations</b> done (usually in the staging area) to the extracted data during the <b>ETL process</b>?</span><br />
<blockquote><ul><li><b>(a) Cleansing</b> (correct misspellings, resolve domain conflicts, deal with missing data, reformatting)</li>
<li><b>(b) Combining</b> data from multiple sources</li>
<li><b>(c) Deduplicating</b> data</li>
<li><b>(d) Assigning warehouse</b> keys</li>
</ul></blockquote><br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: large;"><br />
(Q) What are some <b>performance considerations</b> when planning the <b>loading </b>stage of the <b>ETL process</b>?</span><br />
<blockquote><ul><li>The goal in this phase is to <b>load the data</b> into the warehouse in <b>the most expedient manner.</b></li>
<li>Careful consideration should be given to where the data being-loaded resides and how you load it into the database.</li>
<li>For example, do not use a <b>serial database link</b> or a <b>single JDBC connection</b> to move large volumes of data. </li>
<li><b><i>Flat files are the most common and preferred mechanism of large volumes of data.</i></b></li>
</ul><br />
<ul><li>The <b>overall speed of your load</b> will be determined by </li>
<ul><li>(A) how quickly the <b>raw data can be read</b> from staging area and</li>
<li>(B) how fast it <b>can be processed and inserted</b> into the database. </li>
</ul><li>You should stage the raw data <b>across as many physical disks as possible</b> to ensure the reading it is not a bottleneck during the load.</li>
</ul></blockquote><br />
<div class="separator" style="clear: both; float: center; text-align: center;"><img border="0" height="49" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjb96V9X3_nqUc_fPdXd8i5O9zkq85BUNk3SCEdcXEtk_-xXkVvKAy3OYEa-Iy0TLxBSqeIiMecGogRghAgYA0aFaWDrHO46gEVWjAoojfNz3StorI8ZOu6Qypg-FJF4LQuL_0kkCJaKZB4/s400/ETL-PresentationArea1.png" width="400" /></div><span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: large;"><br />
(Q) What tasks are usually part of the loading of the <b>Presentation area</b>?</span><br />
<blockquote><ul><li>Load each data Mart</li>
<li>Index new data for query performance</li>
<li>Supply/compute appropriate aggregates (i.e. build or refresh <b>Materialized Views</b>)</li>
</ul></blockquote><br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: large;"><br />
(Q) <b>What is a Data Mart?</b> How it relates to the <b>Presentation Are</b>a and to the Data Warehouse?</span><br />
<blockquote><ul><li>A Data Mart is a section of the overall presentation area, which usually consists of a series of integrated data marts. </li>
<li>At its simplest form, it <b>presents the data from a single business process</b> </li>
<li>Modern Data Marts <b>may be updated</b>. Changes in lables, hierarchies, status, and corporate ownership may trigger changes</li>
<li><b>These</b> changes are<b> managed-load updates</b>, however. </li>
<li>The Data Warehouse presentation area in a large enterprise may consist of <b>20 or more similar-looking data marts</b>. </li>
<li>Each Data mart has <b>similar dimensional models</b>. </li>
<li>Each data mart <b>contains various fact tables</b>, each having 5-15 <b>dimension tables</b>, that <b>are often shared among the various fact tables.</b> </li>
</ul></blockquote><br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: large;"><br />
(Q) What is <b>dimensional modeling</b>? How does it relate to 3NF models?</span><br />
<blockquote><ul><li>The <b>key difference</b> between <b>dimensional and 3NF models</b> is the <b><u>degree of normalization</u></b>. </li>
<li><b>Both types</b> can be represented in E-R diagrams, but 3NF are intended to reduce data redundancy.</li>
<li>Dimensional model contains the same information as a normalized model.</li>
<li>Dimensional model's goals are <b>understandability, query performance and resilience to change</b>. </li>
<li>3NF modeling boosts performance of operational systems: an update or insert transaction only needs to touch the database in one place. </li>
<li>UPDATES or INSERTS do not have to touch(read/write) many different structures in the database. <br />
3NF models are too complex for DWH queries. </li>
<li>Dimensional modelings is applicable to both relational and multidimensional databases.</li>
</ul></blockquote><br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: large;"> <br />
(Q) What are some key goals of dimensional modeling?</span> <br />
<blockquote>Dimensional model's goals are (a) understandability, (b) query performance and (c) resilience to change. </blockquote><br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: large;"> <br />
(Q) What does the concept of <b>metadata</b> represent in a Data Warehouse context? </span> <br />
<blockquote>Metadata is all the information in the Data Warehouse environment that <b>is NOT the actual data</b> itself </blockquote><br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: large;"> <br />
(Q) What <b>types of metadata</b> are there?</span> <br />
<blockquote><ul><li><b>(1) Operational source system metadata</b> - Source schemas, copybooks that facilitate the extraction proces </li>
<li><b>(2) Staging area metadata</b> - Used to guide the transformation and loading processes. Includes</li>
<ul><li>staging file layouts</li>
<li>Target table layouts </li>
<li>transformation and cleaning rules </li>
<li>conformed dimension and fact definitions </li>
<li>aggregation definitions </li>
<li>ETL transmission schedules and run-log results </li>
</ul><li><b>(3) DBMS metadata</b> - System tables, partition settings, indexes and view definitions, security privileges and grants</li>
<li><b>(4) data access tools metadata</b> - Identify (a) business names and definitions for the presentation area's tables and columns; (b) application template specifications; (c) access and usage statistics</li>
<li><b>(5) Security Settings metadata</b> - From Source transactional data all the way to user desktops </li>
</ul></blockquote><br />
<br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: large;"> <br />
(Q) Consider the vocabulary of dimensional modeling: <b>What are Fact tables?</b></span> <br />
<blockquote><ul><li>Fact tables are the primary tables in a dimensional model, and <b>store numerical performance measurements</b> of the business</li>
<li>Measurement data resulting from a <b>business process</b> should be stored in a <b>single data mart</b>.</li>
<li>A <b>Fact represents a business measure</b>. </li>
<li>A <b>measurement </b>is taken at the intersection of all the dimensions (day, product, store, channel), which defines the grain of the measurement</li>
<li>All measurements in a <b>fact table</b> should have the same <b>grain</b>.</li>
<li>The example below shows the <b>SALES fact table</b> and its associated <b>dimension tables</b>.</ul><div class="separator" style="clear: both; text-align: center; float:center;"><img border="0" height="365" width="597" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgcR5no2lBxCI0piJ10OaMP5JcNbkunO4RVpiCFfjnaSu3hS6Rd-m4guFn-H8Zot8NOcRW7DZFOdmVNWnTUWcRmE9joainIcofIHN0vG5EBQ0TOCxWcU-nfDUf6TF9-TdHLIpeEqtLMVGFc/s600/SH-Schema1.png" /></div></blockquote><span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: large;"> (Q) What <b>types of facts</b> can be stored in a fact table? </span> <blockquote><ul><li><b>Additive</b> - can be rulled up. (i.e. <b><code>amount_sold, quantity_sold</code></b>)</li>
<li><b>Semiadditive</b> - can be added only along <b>some </b>of the dimensions</li>
<li><b>Nonadditive </b>- Cannot be added.. Averages and counts are possible.</li>
</ul></blockquote><span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: large;"> (Q)what are the <b>categories</b> in which <b>fact table grains</b> fall into?</span> <blockquote><ul><li><b>Transaction</b> - (i.e. <code><b>amount_sold and quantity_sold</code></b></li>
<li>Periodic Snapshot</li>
<li>Accumulating Snapshot </li>
</ul><ul><li>In the image below, quantity and amount are transactional additive facts</li>
</ul><div class="separator" style="clear: both; text-align: center; float: center;"><img border="0" height="361" width="600" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhoYqmLl2KNo08vCSb-Ge0nlFWWIteqQZ1A0-Q_HYNwaI1jiTNzrj206oOVN_qgzYm6_BW8VA_dC8CYk1F2gM7L7o8wTZ4hqCtnumQrP3MbKvqVZN0gRKKef0YlRrsWFERhVFJTXy1PdQCT/s600/DWH---Cube---Measurement1.png" /></div></blockquote><span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: large;"> (Q) <b>What are Dimension tables</b>?</span> <blockquote><ul><li>Contain the <b>textual descriptors</b> of the business</li>
<li>Tables should have as <b>many meaningful columns as possible</b></li>
<li>sometimes 50-100 attributes</li>
<li>attributes here are the <b>primary source of query constraints, groupings, and report</b> labels</li>
<li>Attributes often <b>represent hierarchical relationships</b> (i.e. product -> brand -> Category)</li>
<li>Dimension tables are typically<b> highly denormalized</b></li>
<li>Check the attributes of TIMES and CUSTOMERS in the example below. Denormalization is extensive.</li>
</ul><div class="separator" style="clear: both; text-align: center; float: center;"><img border="0" height="600" width="586" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg2UB4z6slXkRdTtkCqQPpFCsbgyvANjOC7eIc8ihCJouCi91vEtpoB3tuQH7GZYebhPw4eoFHNI7kXKyBVnJjn-GphDwEJCFVQP4SDjFlZRDt2lMljTU6pyN1W25vSy4w5PhBO2M0aGkZG/s600/SH--Star-Schema---dimensions1.png" /></div></blockquote><span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: large;"> (Q) How <b>dimension tables</b> improve the <b>quality </b>of a Data Warehouse?</span> <blockquote><ul><li>Dimension tables should have <b>many and robust attributes</b>, all relevant to the business. </li>
<li>Robust dimension attributes <b>deliver robust analytic slicing and dicing</b> capabilities </li>
<li>Dimensions implement the user interface to the Data Warehouse </li>
</ul></blockquote><span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: large;"> (Q) what characteristics <b>Dimension Attributes</b> should have?</span> <blockquote><ul><li>Attributes should consist of <b>real words rather than cryptic abbreviations</b></li>
</ul></blockquote><span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: large;"> (Q) When designing a Data Warehosue, <b>how can you decide</b> whether a <b>data field</b> extracted from an operational systems <b>belongs to a Fact table or to a Dimension table?</b></span> <blockquote><ul><li>Ask whether</li>
<ul><li>(a) field is a measurement that takes on lots of values and participates in calculations <b>(FACT)</b></li>
<li>(b) filed is a discretely valued description that is more or less constant and participate in constraints <b>(DIMENSION)</b></li>
</ul></ul></blockquote>Minima-Imagemhttp://www.blogger.com/profile/03798032367384388122noreply@blogger.com0tag:blogger.com,1999:blog-7852430287014615375.post-54016039061514933672012-03-11T11:40:00.000-07:002012-03-29T21:33:49.601-07:00Q&As: Data Warehousing and Partitioning (II)<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7et2WtvbHj-TgGXo-iw2HNsWQBtVPGmYhTcYQEzHu9Wbrzm5cq0EaiiDqYg31BNURmiUfjkxhLoRMPE-qXq0HOw1YYH5mKLCvnXatuaTvmS9iOxdVP9MP4oOhIGUmHxpeorMhaHNI8l2s/s1600/oracle-11g-logo.gif" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" height="78" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7et2WtvbHj-TgGXo-iw2HNsWQBtVPGmYhTcYQEzHu9Wbrzm5cq0EaiiDqYg31BNURmiUfjkxhLoRMPE-qXq0HOw1YYH5mKLCvnXatuaTvmS9iOxdVP9MP4oOhIGUmHxpeorMhaHNI8l2s/s400/oracle-11g-logo.gif" width="246" /></a></div><br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: large;"><br />
(Q) What is a <b>Partition-Wise Join</b>? How can SQL statements benefit from <b>Full Partition-Wise Joins</b>?</span><br />
<blockquote><ul><li>A P-W Join divides a large join into smaller joins between a pair of partitions from the two joined tables. </li>
<li>To use P-W Joins:</li>
<ul><li>(a) equipartition <b>BOTH </b>tables on their <b>JOIN </b>keys or</li>
<li>(b) use <b>Reference partitionin</b>g</li>
</ul><li>P-W Joins reduce query response time by <b>minimizing the amount of data exchanged among parallel execution servers</b> when joins execute in parallel</li>
<li><b>Full P-W Joins:</b> Divide a large join into smaller joins between a pair of partitions from the two joined tables</li>
<li>Tables <b>MUST</b> be <b>equipartitioned </b>on their join keys, <b>or</b> use <b>reference partitioning</b>.</li>
</ul></blockquote><br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: large;"><br />
(Q) What are the two<b> most common types of indexes</b> in <b>Data Warehouse</b> environments?</span><br />
<blockquote><ul><li><b>B-tree</b> and <b>bitmap</b> indexes</li>
<li>Both can be created as <b>local indexes</b> on a <b>partitioned</b> table, inheriting the table's partitining strategy</li>
<li><b>Local nonprefixed indexes</b> can improve performance because many index partitions can be scanned in parallel by range queries on the index key.</li>
</ul><pre style="font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; color: #000000;
font-size: 12px;border: 1px dashed #999999;line-height: 14px;
background-color: #eee; padding: 5px;">SQL> create index cust_last_name_ix ON customers(last_name) <b>LOCAL</b> PARALLEL NOLOGGING;</pre></blockquote><br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: large;"><br />
(Q) How can bitmap index improve performance in a <b>Data Warehouse</b> environment?</span><br />
<blockquote><ul><li>Bitmap indexes use a very efficient storage mechanism for <b>low cardinality columns</b>.</li>
<li><b>Fact table</b> foreign key column are ideal candidates for bitmap indexes, since generally there are few distinct values relative to the total number of rows in the fact table. (Because dimension tables should not have many rows)</li>
</ul></blockquote><br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: large;"><br />
(Q) What is<b> Partition Exchange Load (PEL)?</b> <b>When </b>is it used? What <b>benefits </b>does it provides?</span><br />
<blockquote><ul><li>Oracle strongly recommends that the larger tables or fact tables in a data warehouse are partitioned.</li>
<li>with a partitioned table, you can load data quickly and easily with minimal impact on business users by using the <b>exchange partition command</b>.</li>
<li>With the <b>exchange partition command</b> you can <b>swap the data in a non-partitioned table into a particular partition in your partitioned table</b>.</li>
<li>NO physical data movement occurs with the exchange partition command.</li>
<li>Instead the data dictionary is updated to exchange a pointer from the partition to the table and vice versa. </li>
<li>Because <b>there is no physical movement of data</b>, an <b>exchange does not generate redo and undo</b>, making it a VERY fast operation. </li>
</ul><ul><li>You can convert by <b>exchanging their data segments</b>:</li>
<ul><li>(a) a partition (or subpartition) <b>into a</b> nonpartitioned table and</li>
<li>(b) a nonpartitioned table <b>into a</b> partition (or subpartition) of a partitioned table</li>
</ul><li>In DWH environments, exchanging partitions <b>facilitates high-speed data loading</b> of new, incremental data into an existing partitioned table.</li>
<li>OLTP and DWH also can benefit from <b>exchanging old data partitions out of a partitioned table</b>. </li>
<li>In the latter case, the data is purged from the partitioned table without being deleted and can be archived separately afterwards.</li>
</ul></blockquote><br />
(1) create a separate table that looks exactly like a single partition in the existing table (including indexes, constraints and partition schema) <br />
(2) Load the data in the new table, build indexes, enable constraints. This will not impact current users of the existing table <br />
(3) Exchange the existing table partition with the newly created table <br />
<pre style="font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; color: #000000;
font-size: 12px;border: 1px dashed #999999;line-height: 14px;
background-color: #eee; padding: 5px;">i.e. (a) create new partition on existing table
SQL> Alter table sales <b>add partition</b> p_sales_jun_2007
values less than (to_date('01-JUL-2007', 'DD-MON-YYYY');
(b) create temp table that looks like existing partition
SQL> create table sales_jun_2007 COMPRESS FOR OLTP as select * from sales where 1=0;
(c) Populate table and create indexes and constraints
SQL> create bitmap index time_id_jun_2007_bix ON sales_jun_2007(time_id) nologging;
SQL> create bitmap index cust_id_jun_2007_bix ON sales_jun_2007(cust_id) nologging;
SQL> create bitmap index prod_id_jun_2007_bix ON sales_jun_2007(prod_id) nologging;
SQL> create bitmap index promo_id_jun_2007_bix ON sales_jun_2007(promo_id) nologging;
SQL> create bitmap index channel_id_jun_2007_bix ON sales_jun_2007(channel_id) nologging;
SQL> ALTER TABLE sales_jun_2007 ADD CONSTRAINT prod_id_fk
FOREIGN KEY (prod_id) REFERENCES products(prod_id);
SQL> ALTER TABLE sales_jun_2007 ADD CONSTRAINT cust_id_fk
FOREIGN KEY (cust_id) REFERENCES customers(cust_id);
SQL> ALTER TABLE sales_jun_2007 ADD CONSTRAINT promo_id_fk
FOREIGN KEY (promo_id) REFERENCES promotions(promo_id);
SQL> ALTER TABLE sales_jun_2007 ADD CONSTRAINT time_id_fk
FOREIGN KEY (time_id) REFERENCES times(time_id);
SQL> ALTER TABLE sales_jun_2007 ADD CONSTRAINT channel_id_fk
FOREIGN KEY (channel_id) REFERENCES channels(channel_id);
(d) Exchange partition
SQL> Alter table sales <b>EXCHANGE PARTITION</b> p_sales_jun_2007 <b>WITH TABLE</b> sales_jun_2007
including indexes;
</pre><span style="color: black; font-family: 'Courier New', Courier, monospace; font-size: large;"><br />
Partition Exchange Load (PEL)</span><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiE6kyrXwN63ufnxT33250KcAwsdKLa1-_eFJq8zb_uVCorMCxpnkgjPfLAwRogNawNdCetNXzfP8bv-RFXJvJbgvMnd-O82aY2RYx7j6ULOBeUEV3O9_rt_hsynfN7Joq9jsyYPQXPONVw/s1600/Partition-Exchange-1.png" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="816" width="600" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiE6kyrXwN63ufnxT33250KcAwsdKLa1-_eFJq8zb_uVCorMCxpnkgjPfLAwRogNawNdCetNXzfP8bv-RFXJvJbgvMnd-O82aY2RYx7j6ULOBeUEV3O9_rt_hsynfN7Joq9jsyYPQXPONVw/s900/Partition-Exchange-1.png" /></a></div><br />
<br />Minima-Imagemhttp://www.blogger.com/profile/03798032367384388122noreply@blogger.com0tag:blogger.com,1999:blog-7852430287014615375.post-75027007389702580172012-03-07T20:31:00.000-08:002012-03-13T21:55:05.263-07:00Data Guard: Switchover to a Physical Standby Database<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7et2WtvbHj-TgGXo-iw2HNsWQBtVPGmYhTcYQEzHu9Wbrzm5cq0EaiiDqYg31BNURmiUfjkxhLoRMPE-qXq0HOw1YYH5mKLCvnXatuaTvmS9iOxdVP9MP4oOhIGUmHxpeorMhaHNI8l2s/s1600/oracle-11g-logo.gif" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" height="78" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7et2WtvbHj-TgGXo-iw2HNsWQBtVPGmYhTcYQEzHu9Wbrzm5cq0EaiiDqYg31BNURmiUfjkxhLoRMPE-qXq0HOw1YYH5mKLCvnXatuaTvmS9iOxdVP9MP4oOhIGUmHxpeorMhaHNI8l2s/s400/oracle-11g-logo.gif" width="246" /></a></div><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEidQmHtc1gYxHzs122-mf2dSpxxx6P1yO8XizL7n4hQNVICwvVlMF1YOSmmIvbzVP-qBqbzExheKGZ79figGGmOEchqMkSfhxd_dDzQc-5y-pHr5NJX52L_HXdrdbE6bjQaHBwDZEBmohFR/s1600/DG-switchoverPhysical-1.png" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="341" width="700" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEidQmHtc1gYxHzs122-mf2dSpxxx6P1yO8XizL7n4hQNVICwvVlMF1YOSmmIvbzVP-qBqbzExheKGZ79figGGmOEchqMkSfhxd_dDzQc-5y-pHr5NJX52L_HXdrdbE6bjQaHBwDZEBmohFR/s700/DG-switchoverPhysical-1.png" /></a></div><br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: normal;"><b><br />
(Step 1) Verify that the primary database can be switched to a physical standby database</b></span><br />
<ul><li> A value of <code><b>TO STANDBY</code></b> or <code><b>SESSIONS ACTIVE</code></b> indicates that the primary can be switched to a standby role.</li>
</ul><pre style="font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; color: #000000;
font-size: 12px;border: 1px dashed #999999;line-height: 14px;
background-color: #eee; padding: 5px; overflow: auto; width: 100%"><b>SQL> select name, db_unique_name, switchover_status from v$database;</b>
NAME DB_UNIQUE_NAME SWITCHOVER_STATUS
--------- ------------------------------ --------------------
DG11G atlanta TO STANDBY
</pre><br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: normal;"><b><br />
(Step 2) Initiate the switchover of the primary database to a physical standby database</b></span><br />
<ul><li> After completion of this statement, the Data Guard configuration will have two physical standby databases and no primary.</li>
</ul><pre style="font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; color: #000000;
font-size: 12px;border: 1px dashed #999999;line-height: 14px;
background-color: #eee; padding: 5px; overflow: auto; width: 100%">SQL> alter database commit to switchover to physical standby with session shutdown;
Database altered.
</pre><br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: normal;"><b><br />
(Step 3) Shutdown and then mount the former primary database</b></span><br />
<ul><li> After completion of this statement, the original primary is a standby database.</li>
<li> You can see a graphical view of the switchover process <a href="http://www.dbms-notes.com/2012/03/data-guard-switchover-to-physical.html" target=_blank>here</a>.</li>
</ul><pre style="font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; color: #000000;
font-size: 12px;border: 1px dashed #999999;line-height: 14px;
background-color: #eee; padding: 5px; overflow: auto; width: 100%"><b>SQL> shutdown abort;</b>
ORACLE instance shut down.
<b>SQL> startup mount;</b>
ORACLE instance started.
...
Database mounted.
<b>SQL> select name, db_unique_name, switchover_status, database_role from v$database;</b>
NAME DB_UNIQUE_NAME SWITCHOVER_STATUS DATABASE_ROLE
--------- ------------------------------ -------------------- ----------------
DG11G atlanta TO PRIMARY PHYSICAL STANDBY
</pre><br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: normal;"><b><br />
(Step 4) Check that the switchover target is ready to be switched to the primary role</b></span><br />
<ul><li> After completion of this statement, the original standby is a primary database.</li>
</ul><pre style="font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; color: #000000;
font-size: 12px;border: 1px dashed #999999;line-height: 14px;
background-color: #eee; padding: 5px; overflow: auto; width: 100%"><b>SQL> select name, db_unique_name, switchover_status, database_role from v$database;</b>
NAME DB_UNIQUE_NAME SWITCHOVER_STATUS DATABASE_ROLE
--------- ------------------------------ -------------------- ----------------
DG11G fresno TO PRIMARY PHYSICAL STANDBY
</pre><br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: normal;"><b><br />
(Step 5) Switch the target physical standby database role to the primary role</b></span><br />
<pre style="font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; color: #000000;
font-size: 12px;border: 1px dashed #999999;line-height: 14px;
background-color: #eee; padding: 5px; overflow: auto; width: 100%"><b>SQL> alter database commit to switchover to primary with session shutdown;</b>
Database altered.
<b>SQL> select name, db_unique_name, switchover_status, database_role from v$database;</b>
NAME DB_UNIQUE_NAME SWITCHOVER_STATUS DATABASE_ROLE
--------- ------------------------------ -------------------- ----------------
DG11G fresno NOT ALLOWED PRIMARY
</pre><br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: normal;"><b><br />
(Step 6) Open the new primary database</b></span><br />
<pre style="font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; color: #000000;
font-size: 12px;border: 1px dashed #999999;line-height: 14px;
background-color: #eee; padding: 5px; overflow: auto; width: 100%"><b>SQL> alter database open;</b>
Database altered.
</pre><br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: normal;"><b><br />
(Step 7) Start Redo Apply on the new physical standby database</b></span><br />
<ul><li> You can see a graphical view of the switchover process <a href="http://www.dbms-notes.com/2012/03/data-guard-switchover-to-physical.html" target=_blank>here</a>.</li>
</ul><pre style="font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; color: #000000;
font-size: 12px;border: 1px dashed #999999;line-height: 14px;
background-color: #eee; padding: 5px; overflow: auto; width: 100%"><b>SQL> select name, db_unique_name, switchover_status, database_role from v$database;</b>
NAME DB_UNIQUE_NAME SWITCHOVER_STATUS DATABASE_ROLE
--------- ------------------------------ -------------------- ----------------
DG11G atlanta RECOVERY NEEDED PHYSICAL STANDBY
<b>SQL> alter database recover managed standby database using current logfile disconnect from session;</b>
Database altered.
</pre><br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEheie_xfDB3HcjNf6oO2NEZ0UPiDoi_koB5zp9CWRH5O3akbTa9AZ5SHUHaTNvQL7fFTyXKuo2E3NjL4knVZ-PVZRtmB60yC5RMu9uZjOP_zBuJSQP-ydW7bI1_J7swI11rUz5v0i0g8VJO/s1600/DG-switchoverPhysical-2.png" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="370" width="700" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEheie_xfDB3HcjNf6oO2NEZ0UPiDoi_koB5zp9CWRH5O3akbTa9AZ5SHUHaTNvQL7fFTyXKuo2E3NjL4knVZ-PVZRtmB60yC5RMu9uZjOP_zBuJSQP-ydW7bI1_J7swI11rUz5v0i0g8VJO/s700/DG-switchoverPhysical-2.png" /></a></div>Minima-Imagemhttp://www.blogger.com/profile/03798032367384388122noreply@blogger.com0tag:blogger.com,1999:blog-7852430287014615375.post-51030687600276954512012-03-07T11:04:00.000-08:002012-03-21T11:27:32.805-07:00Q&As: Data Warehosing and Partitioning (I)<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7et2WtvbHj-TgGXo-iw2HNsWQBtVPGmYhTcYQEzHu9Wbrzm5cq0EaiiDqYg31BNURmiUfjkxhLoRMPE-qXq0HOw1YYH5mKLCvnXatuaTvmS9iOxdVP9MP4oOhIGUmHxpeorMhaHNI8l2s/s1600/oracle-11g-logo.gif" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" height="78" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7et2WtvbHj-TgGXo-iw2HNsWQBtVPGmYhTcYQEzHu9Wbrzm5cq0EaiiDqYg31BNURmiUfjkxhLoRMPE-qXq0HOw1YYH5mKLCvnXatuaTvmS9iOxdVP9MP4oOhIGUmHxpeorMhaHNI8l2s/s400/oracle-11g-logo.gif" width="246" /></a></div><br />
<br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: large;"><br />
(Q) In which ways <b>partitions </b>facilitate administrative operations?</span><br />
<ul><li>Partitioning enable administrative operations to <b>work on subsets of data.</b> </li>
<li>You can <b>add</b>, <b>organize </b>or <b>drop </b>a partition with minimal or zero interruption to read-only applications</li>
<li><b>Adding a partition:</b> you can <b>defer segment creation</b>, which is useful when the segment is very large </li>
<li><b>Changing </b>partition <b>granularity</b>: You can <b>split or merge</b> partitions. </li>
<li><b>Swapping </b>partitions: you can easily add, remove, or swap a large amount of data quickly </li>
<li>Partitioning enhances data access and improve overall application performance:</li>
<ul><li><b>More users can query</b> the system, since each SQL is likely to access smaller amounts of data</li>
<li>Complex queries run faster. <b>Less IO access</b></li>
</ul></ul><br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: large;"><br />
(Q) List <b>three </b>advantages of using <b>partitioned objects</b></span><br />
<blockquote><b>(1) Backup and recovery </b>can be performed on a <b>lower level of granularity</b> (at the <b>partition level</b>)<br />
<b>(2)</b> <b>Part</b> of the database objects can be <b>placed in compressed storage</b>, while others remain uncompressed<br />
<b>(3)</b> You can <b>transparently </b>store data on <b>different storage</b> tiers to lower costs<br />
</blockquote><br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: large;"><br />
(Q) How can the database take advantage of <b>partitioning </b>when executing an SQL?</span><br />
<ul><li>Using <b>parallel executions</b> to speed up queries, DML and DDL.</li>
<li>Individual parallel execution servers can work on their own data sets, identified by partition boundaries</li>
</ul><br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: large;"><br />
(Q) what is <b>Partition pruning</b>?</span><br />
<ul><li>When executing a SQL statement, the <b>Optimizer </b>analyzes <b>FROM</b> and <b>WHERE</b> clauses to eliminate unneeded partitions from the execution plan's access list.</li>
<li>In the example below, the database <b>scans only four</b> partitions when executing the query. In an non-partitioned table, all data would have to be scanned.</li>
</ul><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjrjktNF18NpcI27rIZwF5yahLxKufXyfQLRpha8sw8cMz1OxFyRuj2RjJjRcPkXznwAPSYksI3EYkaiGzZNwJfXmbQaehCG7X0Ci2BiHp7pjgJwnAk1OvffmRpniz4zjwPjcymIXSGtxXp/s1600/Partition-Pruning-1.png" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="371" width="600" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjrjktNF18NpcI27rIZwF5yahLxKufXyfQLRpha8sw8cMz1OxFyRuj2RjJjRcPkXznwAPSYksI3EYkaiGzZNwJfXmbQaehCG7X0Ci2BiHp7pjgJwnAk1OvffmRpniz4zjwPjcymIXSGtxXp/s600/Partition-Pruning-1.png" /></a></div><br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: large;"><br />
(Q) What is the difference between <b>static </b>pruning and <b>dynamic pruning</b>?</span><br />
<ul><li><b>Static pruning</b> - occurs at <b>compile-time</b>.</li>
<ul><li>example: SQL stmt with WHERE clause with a constant literal on the partition key column</li>
</ul><li><b>Dynamic pruning</b> - occurs at <b>run-time</b>, when the exact partitions to be accessed are not known before hand</li>
<ul><li>example: SQL stmt with operator or function in the WHERE condition</li>
</ul></ul><br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: large;"><br />
(Q) How can you identify when <b>static pruning</b> is used in an execution plan?</span><br />
<blockquote><ul><li>In the execution plan, the <b><code>OPERATION</b></code> column shows <b><code>PARTITION RANGE SINGLE</b></code>, indicating that <b>only a single (or range of) partition(s)</b> is being accessed.</li>
<li>If <b><code>OPERATION = PARTITION RANGE ALL</b></code>, then <b>ALL</b> partitions are being accessed</li>
</ul></blockquote><pre style="font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; color: #000000;
font-size: 12px;border: 1px dashed #999999;line-height: 14px;
background-color: #eee; padding: 5px; overflow: auto; width: 100%"><b>select * from sales where time_id = to_date('01-jan-2001', 'dd-mon-yyyy');</b>
... Returns 766 rows
<b>explain plan for</b> select * from sales where time_id = to_date('01-jan-2001', 'dd-mon-yyyy');
plan FOR succeeded
<b>select * from table(dbms_xplan.display);</b>
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------
Plan hash value: 3733446669
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 766 | 22980 | 27 (0)| 00:00:01 | | |
| 1 | <b>PARTITION RANGE SINGLE</b> | | 766 | 22980 | 27 (0)| 00:00:01 | <b>17</b> | <b>17</b> |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 766 | 22980 | 27 (0)| 00:00:01 | 17 | 17 |
| 3 | BITMAP CONVERSION TO ROWIDS | | | | | | | |
|* 4 | BITMAP INDEX SINGLE VALUE | SALES_TIME_BIX | | | | | 17 | 17 |
---------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
----------------------------------------
4 - access("TIME_ID"=TO_DATE(' 2001-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
16 rows selected
</pre><br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: large;"><br />
(Q) What types of SQL statements are candidates for <b>partition pruning</b>?</span><br />
<ul><li>On <b>Range or List partitioned</b> columns: When you use <b>range</b>, <b>LIKE</b>, <b>equality</b>, and <b>IN-list</b> predicates</li>
<li>On <b>Hash Partitioned</b> columns: When you use <b>equlity</b> and <b>IN-list</b> predicates</li>
</ul><br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: large;"><br />
(Q) How can <b>datatype conversions</b> affect the type of <b>partition pruning</b> that may be used?</span><br />
<blockquote><ul><li>Datatype conversion <b>typically lead to dynamic</b>, when static pruning would have otherwise been possible</li>
<li>Static pruning provides<b> better performance</b> than dynamic pruning</li>
<li>example: Proper use of TO_DATE function guarantees that the database can uniquely determine the date value and thus the partition that should be accessed</li>
</ul></blockquote><pre style="font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; color: #000000;
font-size: 12px;border: 1px dashed #999999;line-height: 14px;
background-color: #eee; padding: 5px; overflow: auto; width: 100%">Compare the following queries:
<b>SELECT SUM(amount_sold) total_revenue FROM sh.sales</b>
<b>(clause 1) WHERE</b> time_id between '01-JAN-00' and '31-DEC-00' ;
<b>(clause 2) WHERE</b> time_id between '01-JAN-2000' and '31-DEC-2000' ;
<b>(clause 3) WHERE</b> time_id between to_date('01-JAN-2000', 'dd-MON-yyyy') and
to_date('31-DEC-2000', 'dd-MON-yyyy');
<b>(Clause 1:)</b>
Leads to <b>dynamic pruning</b>, because date needs to be <b>implicitly converted</b> <b>and does not</b> match <b>nls_date_format</b>
<b>SQL> explain plan for</b>
SELECT SUM(amount_sold) total_revenue
FROM sales
WHERE time_id between '01-JAN-00' and '31-DEC-00';
plan FOR succeeded.
<b>SQL> select * from table(dbms_xplan.display);</b>
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------
Plan hash value: 3772476237
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | <b>Pstart</b>| <b>Pstop </b>|
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 592 (19)| 00:00:08 | | |
| 1 | SORT AGGREGATE | | 1 | 13 | | | | |
|* 2 | FILTER | | | | | | | |
| 3 | PARTITION RANGE ITERATOR| | 230K| 2932K| 592 (19)| 00:00:08 | <b>KEY </b>| <b>KEY </b>|
|* 4 | TABLE ACCESS FULL | SALES | 230K| 2932K| 592 (19)| 00:00:08 | <b>KEY </b>| <b>KEY </b>|
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_DATE('01-JAN-00')<=TO_DATE('31-DEC-00'))
4 - filter("TIME_ID">='01-JAN-00' AND "TIME_ID"<='31-DEC-00')
17 rows selected
<b>(Clause 2:) </b>
Uses <b>STATIC </b>pruning, because although date needs to be implicitly converted is <b>DOES</b> match <b>nls_date_format</b>
<b>SQL> explain plan for </b>
SELECT SUM(amount_sold) total_revenue
FROM sales
WHERE time_id between '01-JAN-2000' and '31-DEC-2000';
plan FOR succeeded.
<b>SQL> select * from table(dbms_xplan.display);</b>
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
Plan hash value: 1500327972
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<b> Pstart| Pstop</b> |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 136 (11)| 00:00:02 | | |
| 1 | SORT AGGREGATE | | 1 | 13 | | | | |
| 2 | PARTITION RANGE ITERATOR| | 230K| 2932K| 136 (11)| 00:00:02 | <b>13 | 16</b> |
|* 3 | TABLE ACCESS FULL | SALES | 230K| 2932K| 136 (11)| 00:00:02 | <b>13 | 16</b> |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("TIME_ID"<=TO_DATE(' 2000-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
15 rows selected
<b>(Clause 3:)</b>
Uses STATIC pruning, because although date is explicitly converted
<b>SQL> Explain plan for</b>
SELECT SUM(amount_sold) total_revenue
FROM sales
WHERE time_id between to_date('01-JAN-2000', 'dd-MON-yyyy') and
to_date('31-DEC-2000', 'dd-MON-yyyy');
plan FOR succeeded.
<b>SQL> select * from table(dbms_xplan.display);</b>
PLAN_TABLE_OUTPUT
-------------------------------------------
Plan hash value: 1500327972
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | <b>Pstart| Pstop</b> |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 136 (11)| 00:00:02 | | |
| 1 | SORT AGGREGATE | | 1 | 13 | | | | |
| 2 | PARTITION RANGE ITERATOR| | 230K| 2932K| 136 (11)| 00:00:02 | <b>13 | 16</b> |
|* 3 | TABLE ACCESS FULL | SALES | 230K| 2932K| 136 (11)| 00:00:02 | <b> 13 | 16</b> |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("TIME_ID"<=TO_DATE(' 2000-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
15 rows selected
</pre>Minima-Imagemhttp://www.blogger.com/profile/03798032367384388122noreply@blogger.com0tag:blogger.com,1999:blog-7852430287014615375.post-8140751910913205092012-03-06T20:29:00.000-08:002012-03-13T21:54:30.764-07:00Data Guard: Switchover to a Physical Standby (II)<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7et2WtvbHj-TgGXo-iw2HNsWQBtVPGmYhTcYQEzHu9Wbrzm5cq0EaiiDqYg31BNURmiUfjkxhLoRMPE-qXq0HOw1YYH5mKLCvnXatuaTvmS9iOxdVP9MP4oOhIGUmHxpeorMhaHNI8l2s/s1600/oracle-11g-logo.gif" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" height="78" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7et2WtvbHj-TgGXo-iw2HNsWQBtVPGmYhTcYQEzHu9Wbrzm5cq0EaiiDqYg31BNURmiUfjkxhLoRMPE-qXq0HOw1YYH5mKLCvnXatuaTvmS9iOxdVP9MP4oOhIGUmHxpeorMhaHNI8l2s/s400/oracle-11g-logo.gif" width="246" /></a></div><br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhLkb9yXjPBzDh6hCJtfGluSeViaAviYgviHYrmhqxty8GUs17lkKZzBU1ejls6srpk8T5tCTa7mStgBfJyz5WwE5pCtUajRNqNuUUNk8V1imG0uN9kEDRodF0z9lULnjn3w5nAqEDKhw7b/s1600/DG-switchoverPhysical-1.png" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="341" width="700" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhLkb9yXjPBzDh6hCJtfGluSeViaAviYgviHYrmhqxty8GUs17lkKZzBU1ejls6srpk8T5tCTa7mStgBfJyz5WwE5pCtUajRNqNuUUNk8V1imG0uN9kEDRodF0z9lULnjn3w5nAqEDKhw7b/s700/DG-switchoverPhysical-1.png" /></a></div><br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjd-XrdIxb7aaooqS3er9fueS-gSO7-NlC44HV7c5BrsZt0RnNJCs0FV0h-A5RPvPcr5SUlnS30cUGyy5-Txy8dG3u1kxhA-IxEs6dS_BiWoMooEkZvzTzoRgo86TTdkBk_xa8MoMw02w-D/s1600/DG-switchoverPhysical-Sequence.png" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="700" width="490" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjd-XrdIxb7aaooqS3er9fueS-gSO7-NlC44HV7c5BrsZt0RnNJCs0FV0h-A5RPvPcr5SUlnS30cUGyy5-Txy8dG3u1kxhA-IxEs6dS_BiWoMooEkZvzTzoRgo86TTdkBk_xa8MoMw02w-D/s700/DG-switchoverPhysical-Sequence.png" /></a></div><br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhhKM2Xdsb8KfrVyElD2S6UTqNKdscNvjuV0HCPcLCDNNJfBiiFGbdk469npBMjJ3c-JPo22Y17WgpUQ2y-RnYyBxY1VhCFUMrnDSBw9Mr6YKXbg7KLBH5nHrO4DtDebfS4JPYQ6nn7XY2u/s1600/DG-switchoverPhysical-2.png" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="370" width="700" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhhKM2Xdsb8KfrVyElD2S6UTqNKdscNvjuV0HCPcLCDNNJfBiiFGbdk469npBMjJ3c-JPo22Y17WgpUQ2y-RnYyBxY1VhCFUMrnDSBw9Mr6YKXbg7KLBH5nHrO4DtDebfS4JPYQ6nn7XY2u/s700/DG-switchoverPhysical-2.png" /></a></div><br />Minima-Imagemhttp://www.blogger.com/profile/03798032367384388122noreply@blogger.com0tag:blogger.com,1999:blog-7852430287014615375.post-89532761479495479142012-03-06T17:18:00.000-08:002012-04-03T09:57:44.577-07:00Data Warehouse: Concepts on ETL Processes (Extraction)<div class="separator" style="clear: both; text-align: center; float:right;"><img border="0" height="78" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7et2WtvbHj-TgGXo-iw2HNsWQBtVPGmYhTcYQEzHu9Wbrzm5cq0EaiiDqYg31BNURmiUfjkxhLoRMPE-qXq0HOw1YYH5mKLCvnXatuaTvmS9iOxdVP9MP4oOhIGUmHxpeorMhaHNI8l2s/s400/oracle-11g-logo.gif" width="246" /></div><br />
<div class="separator" style="clear: both; text-align: center; float: right; margin-left:1em; margin-bottom:1em;"><img border="0" height="77" width="331" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgESRtrvwo1nfnM83_XN0Tx1fzQ73YGdLFu3nSPICX9z2Q_gzrqOOc-uQCbbjPOpwbp155uDfaZr2vmvXFAZm4BpsRvH61zxqASyWNoR5o_wCo6LYghlO1GYGEb3vbUC7U3Nf7R89DfAX3E/s400/ETL_Extraction.png" /></div><br />
<br />
<br />
<br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: large;"><br />
(Q) Why defining and creating the extraction process may be a very <b>time-consuming</b> stage in the ETL process?</span><br />
<blockquote><ul><li> Often <b>multiple and independent sources</b>. May be <b>poorly documented.</b> 3rd party applications may <b>not</b> be <b>modifiable</b>.</li>
<li>Business requirements on source may not accept an <b>extra overhead of the extraction</b> process, nor accept changes in its availability. </li>
<li>Often you <b>cannot add aditional logic to the source systems</b> to support incremental extraction of data.</li>
</ul></blockquote><br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: large;"><br />
(Q) What are the <b>two <u>Logical</u> extraction methods</b>?</span><br />
<blockquote><ul><li><b>Full </b>Extraction and <b>Incremental</b> Extraction</li>
<li><b>Full Extraction:</b></li>
<ul><li>ALL data is extracted from the source system. Here you don’t need to keep track of what has changed sinc e the last extraction.</li>
<li>However, it is more costly in space, since the staging area might need to be much larger</li>
</ul><li><b>Incremental Extraction</b>: </li>
<ul><li>Here you need to <b>keep track of all data that has changed</b> since the last extraction or some complex business event like the last booking day of a fiscal period. </li>
<li>This information may be provided by the source system (i.e. an application column, or a change table). In this case the source system would likely have to be modified to incorporate the change tracking functionality). </li>
<li>Alternatively, Oracle provides a<b> Change Data Capture (CDC) mechanism</b> (with Oracle Golden Gate)</li>
</ul></ul></blockquote><br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: large;"><br />
(Q) What <b>two methods of physical extraction</b> are there?</span><br />
<blockquote><ul><li><b>Online extraction:</b> </li>
<ul><li>Data is extracted directly from the source itself. <b>Extraction process connects directly to the source</b>. </li>
</ul><li><b>Offline extraction:</b> </li>
<ul><li>Data is staged explicitly outside the original source system. </li>
<li>In this case, <b>the data either has an exisitng structure</b> (i.e. redo logs, archive logs, or transportable tablespace) <b>or was created by an extraction routine</b>. (i.e stored in flat files)</li>
<li>The alternatives are: Flat files, dump files (imp/exp), redo and archived logs (how to read these?) or transportable tablespaces. </li>
</ul></ul></blockquote><br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: large;"><br />
(Q) What is the physical <b>extraction </b>technique <b>recommended </b>by Oracle most of the time? Why is it recommended?</span><br />
<blockquote><ul><li>Oracle recommends the use of <b>transportable tablespaces</b>. </li>
<li>(For this, either the source system is in an Oracle DB or an extraction routine was used to copy the data to a "staging" transportable tablespace. </li>
</ul></blockquote><br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: large;"><br />
(Q) What is the <b>key-enabling technology</b> for <b>providing near real-time, or on-time</b>, data warehousing?</span><br />
<blockquote><ul><li><b>Change Data Capture mechanism</b> </li>
<li>Used for <b>incremental extractions</b> (i.e, nightly extractions, which need to extract only data modified since last extraction). </li>
</ul></blockquote><br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: large;"><br />
(Q) What usually is the most challenging technical issue in data extraction?</span><br />
<blockquote><ul><li>Devising a mechanism of Change Data Capture. </ul></blockquote><span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: large;"> (Q) What are some techniques for <b>implementing a change capture mechanism</b> on Oracle source databases?</span> <blockquote><ul><li>You can use <b>timestamps, partitioning or triggers</b>. Each can be used either if the data is directly accessed through distributed queries or exported to flat file. </li>
<li>Timestamps columns are not always available in an operational source system. </li>
<li>In these cases, <i>how could a timestamp solution for change capture be implemented?</i></li>
<ul><li>You would have to add a <b>new column in every sytem table and write triggers</b> to update the new column each time an operation modifies the row data.</li>
<li>Note that trigger-based CDC mechanisms can affect performance of the source system. </li>
</ul></ul></blockquote><span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: large;"> (Q) What Oracle feature uses an <b>internalized trigger-based mechanism</b> to keep change capture?</span> <blockquote><ul><li><b>Materialized view logs</b> use an internalized trigger-based mechanism. </li>
<li>These logs are used by <b>Materialized views</b> to identify changed data. </li>
</ul></blockquote><span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: large;"> (Q) What are the <b>alternatives </b>available for <b>extracting </b>data into files if the <b>source system is an Oracle</b> database?</span> <blockquote><ul><li>You can use <b>SQL*Plus</b>, </li>
<li>Use <b>OCI </b>or a <b>Pro*C program</b>, </li>
<li>use the Export utility, </li>
<li>use External tables. </li>
</ul></blockquote><span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: large;"> (Q) You want to extract data using SQL*Plus from a single partitioned table. How can you parallelize this process?</span> <blockquote>If the table is range partitioned, you can initiate one SQL*Plus session for each partition. <pre style="font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; color: #000000;
font-size: 12px;border: 1px dashed #999999;line-height: 14px;
background-color: #eee; padding: 5px; overflow: auto; width: 100%">SPOOL order_jan.dat
Select * from orders PARTITION (orders_jan1998);
SPOOL OFF; </pre></blockquote><span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: large;"> (Q) You want to <b>extract data using SQL*Plus</b> from a single non-partitionied table. What two criteria can you use to <b>parallelize </b>this process?</span> <blockquote>Logical or physical criteria. <ul><li>Logical method: based on logical ranges of column values. </li>
</ul><pre style="font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; color: #000000;
font-size: 12px;border: 1px dashed #999999;line-height: 14px;
background-color: #eee; padding: 5px; overflow: auto; width: 100%">Select … where order_date
between to_date('01-JAN-99') and to_date('31-JAN-99');
</pre><ul><li>Physical method: based on a range of values. </li>
<li>Check the data dictionary and find the set of data blocks that make up the table. </li>
<li>From here, derive a set of rowid-range query.</li>
</ul><pre style="font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; color: #000000;
font-size: 12px;border: 1px dashed #999999;line-height: 14px;
background-color: #eee; padding: 5px; overflow: auto; width: 100%">Select … where order_date where rowid between v1 and v2;</pre></blockquote><span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: large;"> (Q) What are the differences between extracting data using data pump export utility and data pump external table mechanism?</span> <blockquote>
<li><b>Export utility</b>:</li>
<ul><li><b>Metadata + data are exported</b>. Output must be processed with the Import utility </li>
</ul><li><b>External table data pump utility</b>:</li>
<ul><li>Results of a SELECT can be stored in an oracle-internal data pump format and <b>processed as a regular external table</b>. </li>
<li>Metadata is NOT exported. Results can be stored in multiple extraction files. </li>
<li>If metadata is required, need to use the DBMS_METADATA package.</li>
</ul><pre style="font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; color: #000000;
font-size: 12px;border: 1px dashed #999999;line-height: 14px;
background-color: #eee; padding: 5px; overflow: auto; width: 100%">Set LONG 2000
Select dbms_metadata.get_ddl('table', 'extract_cust') from dual;
</pre></blockquote><span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: large;"> (Q) What is an important <b>scalability limitation</b> to extraction <b>through distributed operations</b>?</span> <blockquote><ul><li>The data is transported through a single Oracle Net connection.</li>
<li>For large amounts of data, file-based data extraction and transportation techniques are often more scalable. </li>
</ul></blockquote>Minima-Imagemhttp://www.blogger.com/profile/03798032367384388122noreply@blogger.com0tag:blogger.com,1999:blog-7852430287014615375.post-6394394886540303022012-03-05T18:39:00.000-08:002012-03-05T18:39:45.792-08:00Snapshot Standby: Role Transition cycle<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7et2WtvbHj-TgGXo-iw2HNsWQBtVPGmYhTcYQEzHu9Wbrzm5cq0EaiiDqYg31BNURmiUfjkxhLoRMPE-qXq0HOw1YYH5mKLCvnXatuaTvmS9iOxdVP9MP4oOhIGUmHxpeorMhaHNI8l2s/s1600/oracle-11g-logo.gif" imageanchor="1" style="clear:right; float:right; margin-left:1em; margin-bottom:1em"><img border="0" height="78" width="246" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7et2WtvbHj-TgGXo-iw2HNsWQBtVPGmYhTcYQEzHu9Wbrzm5cq0EaiiDqYg31BNURmiUfjkxhLoRMPE-qXq0HOw1YYH5mKLCvnXatuaTvmS9iOxdVP9MP4oOhIGUmHxpeorMhaHNI8l2s/s400/oracle-11g-logo.gif" /></a></div><br />
<br />
<br />
<br />
Create, test, resynchronize with production, create again<br />
<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiFKJYp2rQIDd9sDaSQej1TyKkQOsFJ6yTC_XtADXA0ntgZ9Coz4sFEJsp66YTmW9qdf8aWFW0ipWidZOESzVgCbMObC0gZ5qLp4mZ9NoYJsfmySkh8gGmRQ5gb0jSEe2WDA8fouZ-6HP3g/s1600/DG-snapshotroleTransition-2.png" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="365" width="800" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiFKJYp2rQIDd9sDaSQej1TyKkQOsFJ6yTC_XtADXA0ntgZ9Coz4sFEJsp66YTmW9qdf8aWFW0ipWidZOESzVgCbMObC0gZ5qLp4mZ9NoYJsfmySkh8gGmRQ5gb0jSEe2WDA8fouZ-6HP3g/s800/DG-snapshotroleTransition-2.png" /></a></div><br />Minima-Imagemhttp://www.blogger.com/profile/03798032367384388122noreply@blogger.com0tag:blogger.com,1999:blog-7852430287014615375.post-52443165667370913372012-03-01T18:45:00.000-08:002012-03-13T22:40:38.928-07:00Q&As: Data Guard, Role Transitions (I)<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7et2WtvbHj-TgGXo-iw2HNsWQBtVPGmYhTcYQEzHu9Wbrzm5cq0EaiiDqYg31BNURmiUfjkxhLoRMPE-qXq0HOw1YYH5mKLCvnXatuaTvmS9iOxdVP9MP4oOhIGUmHxpeorMhaHNI8l2s/s1600/oracle-11g-logo.gif" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" height="78" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7et2WtvbHj-TgGXo-iw2HNsWQBtVPGmYhTcYQEzHu9Wbrzm5cq0EaiiDqYg31BNURmiUfjkxhLoRMPE-qXq0HOw1YYH5mKLCvnXatuaTvmS9iOxdVP9MP4oOhIGUmHxpeorMhaHNI8l2s/s400/oracle-11g-logo.gif" width="246" /></a></div><br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: large;"><b><br />
(Q) Which 6 steps must be performed to prepare for a role transition?</b></span><br />
<blockquote><span style="color: black; font-size: normal;"><b><br />
(1) Verify that each database is properly configured for the role that it is about to assume</b></span><br />
<ul><li>Check that necessary initialization parameters are properly configured in each database</li>
<ul><li> <b><code>FAL_SERVER, DB_FILE_NAME_CONVERT, LOG_FILE_NAME_CONVERT</b></code></li>
<li> <b><code>LOG_ARCHIVE_DEST_n and LOG_ARVHIVE_DEST_STATE_n</b></code></li>
</ul><li>Check that <b>ONLINE</b> REDO LOGs and <b>STANDBY</b> REDO LOGs <b>exist</b> and are properly configured on all databases</li>
</ul><pre style="font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; color: #000000;
font-size: 12px;border: 1px dashed #999999;line-height: 14px;
background-color: #eee; padding: 5px; overflow: auto; width: 100%">SQL> select status, type, member, group#
2* from v$logfile;
STATUS TYPE MEMBER GROUP#
------- ------- ------------------------------------------------------------ ----------
ONLINE /u01/app/oracle/oradata/fresno/redo03.log 3
ONLINE /u01/app/oracle/oradata/fresno/redo02.log 2
ONLINE /u01/app/oracle/oradata/fresno/redo01.log 1
STANDBY /u01/app/oracle/oradata/fresno/standby_redo01.log 4
STANDBY /u01/app/oracle/oradata/fresno/standby_redo02.log 5
STANDBY /u01/app/oracle/oradata/fresno/standby_redo03.log 6
STANDBY /u01/app/oracle/oradata/fresno/standby_redo04.log 7
7 rows selected.
</pre><br />
<span style="color: black; font-size: normal;"><b><br />
(2) Verify that there are no <b>Redo Transport errors</b> or <b>Redo Gaps</b> at the standby database</b></span><br />
<ul><li>For <b><code>LOG_ARCHIVE_DEST_2</b></code>, <b><code>STATUS</b></code> should be <b>VALID</b> and <b><code>GAP_STATUS</b></code> should be <b>NO GAP</b></li>
</ul><pre style="font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; color: #000000;
font-size: 12px;border: 1px dashed #999999;line-height: 14px;
background-color: #eee; padding: 5px; overflow: auto; width: 100%"><b>(On the Physical Standby:)</b>
<b>SQL> select dest_name, status, type, gap_status
2 from v$archive_dest_status;</b>
DEST_NAME STATUS TYPE GAP_STATUS
------------------- --------- --------------- ----------
LOG_ARCHIVE_DEST_1 VALID LOCAL
<b>LOG_ARCHIVE_DEST_2 VALID UNKNOWN NO GAP</b>
...
STANDBY_ARCHIVE_DEST VALID UNKNOWN NO GAP
<b>(On the primary database)</b>
<b>SQL> select dest_name, status, type, gap_status
2* from v$archive_dest_status;
</b>
DEST_NAME STATUS TYPE GAP_STATUS
-------------------- --------- -------------- ------------------------
LOG_ARCHIVE_DEST_1 VALID LOCAL
<b>LOG_ARCHIVE_DEST_2 VALID PHYSICAL NO GAP</b>
LOG_ARCHIVE_DEST_3 INACTIVE LOCAL
</pre><br />
<span style="color: black; font-size: normal;"><b><br />
(3) Verify that <b>temporary files</b> exist on the Standby that match the temporary files on the primary database</b></span><br />
<br />
<span style="color: black; font-size: normal;"><b><br />
(4) Remove any <b>delay</b> in applying redo that may be in effect on the standby database that will become the new primary database</b></span><br />
<pre style="font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; color: #000000;
font-size: 12px;border: 1px dashed #999999;line-height: 14px;
background-color: #eee; padding: 5px; overflow: auto; width: 100%">SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY DISCONNECT FROM SESSION;</pre><br />
<span style="color: black; font-size: normal;"><b><br />
(5) Before performing a switchover from an Oracle RAC primary databsae to a physical standby, shut down all but one primary db instance</b></span><br />
<br />
<span style="color: black; font-size: normal;"><b><br />
(6) If the physical standby that will become primary on the switchover <b>is in real-time query mode</b>, consider bringing all instances of the standby database to the mounted but not open state. This will allow:</b></span><br />
<ul><li>(a) the fastest possible role transition and/li><br />
<li>(b) to cleanly terminate any user sessions connected to the physical standby prior to the role transition</li><br />
<br />
</ul></blockquote>Minima-Imagemhttp://www.blogger.com/profile/03798032367384388122noreply@blogger.com0tag:blogger.com,1999:blog-7852430287014615375.post-37210097946453846482012-02-29T11:09:00.000-08:002012-03-13T22:41:02.935-07:00Q&As: Fast Recovery Area (III)<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7et2WtvbHj-TgGXo-iw2HNsWQBtVPGmYhTcYQEzHu9Wbrzm5cq0EaiiDqYg31BNURmiUfjkxhLoRMPE-qXq0HOw1YYH5mKLCvnXatuaTvmS9iOxdVP9MP4oOhIGUmHxpeorMhaHNI8l2s/s1600/oracle-11g-logo.gif" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" height="78" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7et2WtvbHj-TgGXo-iw2HNsWQBtVPGmYhTcYQEzHu9Wbrzm5cq0EaiiDqYg31BNURmiUfjkxhLoRMPE-qXq0HOw1YYH5mKLCvnXatuaTvmS9iOxdVP9MP4oOhIGUmHxpeorMhaHNI8l2s/s400/oracle-11g-logo.gif" width="246" /></a></div><br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: large;"><b><br />
(Q) What types of database files can be stored in the FAST RECOVERY AREA?</b></span><br />
<ul><li> <b>control</b> file, <b>online</b> <b>redo</b> logs, <b>archived</b> <b>redo</b> logs, <b>flashback</b> logs, and <b>RMAN</b> <b>backups</b></li>
</ul><br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: large;"><b><br />
(Q) What two types of files, with respect to their use by the database, are kept in the FAST RECOVERY AREA?</b></span><br />
<ul><li><b>Permanent files</b> – Active files used by the database instance (control file, online redo logs)</li>
<li><b>Transient files</b> – backups that may be deleted according to the <b>retention policy</b></li>
</ul><br />
<br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: large;"><b><br />
(Q) What happens if the instance cannot write to a multiplexed copy of the control file stored in the fast recovery area?</b></span><br />
<ul><li>The <b>instance fails</b>.</li>
<li>Failure occurs <b>EVEN</b> if accessibel multiplexed copies are accessible outside the recovery area.</li>
</ul><br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: large;"><b><br />
(Q) What is the difference between FLASH RECOVERY AREA and FAST RECOVERY AREA?</b></span><br />
<ul><li>Just the name. FLASH became FAST with 11g</li>
</ul><br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: large;"><b><br />
(Q) When configuring the FAST RECOVERY AREA, what happens if you specify <code>DB_RECOVERY_FILE_DEST</code> but DO NOT specify <code>DB_RECOVERY_FILE_DEST_SIZE</code>?</b></span><br />
<ul><li>Specifying <code><b>DB_RECOVERY_FILE_DEST</code></b> without specifying <code><b>DB_RECOVERY_FILE_DEST_SIZE</code></b> is not allowed.</li>
</ul><br />
<br />
<br />
<br />
<br />Minima-Imagemhttp://www.blogger.com/profile/03798032367384388122noreply@blogger.com0tag:blogger.com,1999:blog-7852430287014615375.post-62466875126140244732012-02-16T20:14:00.000-08:002012-03-12T20:19:37.972-07:00DataGuard: Configuring a Physical Standby<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7et2WtvbHj-TgGXo-iw2HNsWQBtVPGmYhTcYQEzHu9Wbrzm5cq0EaiiDqYg31BNURmiUfjkxhLoRMPE-qXq0HOw1YYH5mKLCvnXatuaTvmS9iOxdVP9MP4oOhIGUmHxpeorMhaHNI8l2s/s1600/oracle-11g-logo.gif" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" height="78" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7et2WtvbHj-TgGXo-iw2HNsWQBtVPGmYhTcYQEzHu9Wbrzm5cq0EaiiDqYg31BNURmiUfjkxhLoRMPE-qXq0HOw1YYH5mKLCvnXatuaTvmS9iOxdVP9MP4oOhIGUmHxpeorMhaHNI8l2s/s400/oracle-11g-logo.gif" width="246" /></a></div><br />
Configure Primary Server<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;"></div><table><tbody>
<tr> <td><img border="0" height="138" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj23MdhqEIcpIXTiDL0XvMSI8vD0scuo63GjBWbJyI2IRTxg3nchQQ7Te39mjheB2hBkvBq5fGs1bpWz0I5CkWGksnU27LTAaUKnIl_yBllYWEn1Cnh-SxsvYoF9HgxL_1wljVdYxXTb9Qh/s600/cl-srv-setup.png" width="600" /></td> <td><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjU9Sht3z5uXtBBY2zJdK0KH0T56_dijLeMalfKNNbUSw2UOcW6tvZMOVJShg6Eh4zVeLFOa4BiaInffiNcZ0ULB9GobvcPuvOvjk0Q8G5rEe5Gi3ojp6btDZnKIMBDFJi5Uh1l2t9tSkCJ/s1600/em-dbcontrol-general.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="137" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjU9Sht3z5uXtBBY2zJdK0KH0T56_dijLeMalfKNNbUSw2UOcW6tvZMOVJShg6Eh4zVeLFOa4BiaInffiNcZ0ULB9GobvcPuvOvjk0Q8G5rEe5Gi3ojp6btDZnKIMBDFJi5Uh1l2t9tSkCJ/s200/em-dbcontrol-general.png" width="150" /></a></div></td> </tr>
</tbody></table><br />
<br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: large;"><b><br />
Primary Server Setup</b></span><br />
<blockquote><span style="color: blue; font-family: 'Courier New', Courier, monospace;"><b><br />
(1) Is database in Archivelog Mode?</b></span></blockquote><u>Home -> Availability -> Recovery Settings</u><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhqMAdoLViJ7WDqmHDIbVcYLb048K4tOyHYrgPJn5n6sHpzV7Ji5NClhwvgoTnad5WSLkdv-lF0Z-bGzR_BAj9LlLKFQAZ2LjRv1g5bKs-H4A9IHIxIoBaACijXRgeKZyLrmb-d_0DxKY5Z/s1600/em-dbcontrol-logmode1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="341" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhqMAdoLViJ7WDqmHDIbVcYLb048K4tOyHYrgPJn5n6sHpzV7Ji5NClhwvgoTnad5WSLkdv-lF0Z-bGzR_BAj9LlLKFQAZ2LjRv1g5bKs-H4A9IHIxIoBaACijXRgeKZyLrmb-d_0DxKY5Z/s600/em-dbcontrol-logmode1.png" width="600" /></a></div><pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><b>SQL> SELECT log_mode FROM v$database;</b>
LOG_MODE
------------
ARCHIVELOG
If database is in <b>Noarchivelog</b> mode, you need to switch it to <b>Archivelog</b> mode.
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;</pre><br />
<blockquote><span style="color: blue; font-family: 'Courier New', Courier, monospace;"><b><br />
(2) Is Force Logging enabled?</b></span></blockquote><pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><b>SQL> select force_logging from v$database;</b>
FOR
---
NO
<b>SQL> alter database force logging;</b>
Database altered.
</pre><br />
<blockquote><span style="color: blue; font-family: 'Courier New', Courier, monospace;"><b><br />
(3) Edit initialization parameters for the primary role</b></span></blockquote><div class="separator" style="clear: both; float: center; text-align: center;"></div><table><tbody align="center" style="font-family:courier; color:black; background-color:white">
<tr> <td width="25%">db_name</td> <td width="25%">db_unique_name</td> <td width="25%">log_archive_config</td> <td width="25%">log_archive_dest_1</td> </tr>
<tr> <td width="25%">log_archive_dest_2</td> <td width="25%">log_archive_dest_state_1</td> <td>log_archive_dest_state_2</td> <td width="25%">fal_server</td> </tr>
<tr> <td width="25%">db_file_name_convert</td> <td width="25%">log_file_name_convert</td> <td width="25%">remote_login_passwordfile</td></tr>
</tbody></table><br />
<u>Home -> Server -> Initialization Parameters</u><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg1F9FItiNdqvtmH1vULhQqeW0KSOSW0ryYWTW76cJMcqMWBYeyYPluhXzVjFwdtjR2g8hI-7dvwhu44su2GI1bg7kPQrhK1UILJchVFtGfDuv9wUQ-czJ-PJYnuSCj67xEZyoy46Ahv2qC/s1600/em-dbcontrol-dbIds.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="193" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg1F9FItiNdqvtmH1vULhQqeW0KSOSW0ryYWTW76cJMcqMWBYeyYPluhXzVjFwdtjR2g8hI-7dvwhu44su2GI1bg7kPQrhK1UILJchVFtGfDuv9wUQ-czJ-PJYnuSCj67xEZyoy46Ahv2qC/s600/em-dbcontrol-dbIds.png" width="600" /></a></div><pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><b>SQL> select name, db_unique_name from v$database;</b>
NAME DB_UNIQUE_NAME
--------- ------------------------------
DG11G atlanta
<b>SQL> show parameter db_name;</b>
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string dg11g
<b>SQL> show parameter db_unique_name;</b>
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string atlanta
</pre><br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><b>SQL> alter system set log_archive_config='dg_config=(atlanta, fresno)';</b>
System altered.
<b>SQL> show parameter log_archive_config</b>
NAME TYPE VALUE
---------------------------- ------- ------------------------------
log_archive_config string dg_config=(atlanta, fresno)
Obs.
In this configuration, primary=> atlanta; standby=> fresno
</pre><br />
<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><b>SQL> show parameter log_archive_dest_</b>
NAME TYPE VALUE
---------------------------- ----------- ----------------------------
log_archive_dest_ string location=use_db_recovery_file_
dest valid_for=(all_logfiles,
all_roles) db_unique_name=atla
nta
...
NAME TYPE VALUE
--------------------------- ----------- ------------------------------
log_archive_dest_2 string service=fresno async valid_for
=(online_logfiles, primary_rol
e) db_unique_name=fresno
...
</pre><u>Home -> Server -> Initialization Parameters</u><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhkLmQsygLDrUrMIceLYmfNnF3criBdybcIY3Lcie7YjbMr182h5L17nYIdMp-nSEnChjYC-sZ0bfObbuQLHMk9_S5307fXkwQlfvLAuEkq3xvP3g4coMRhOKQfr_RmuCETGnhUgH8trgqj/s1600/em-dbcontrol-dg-config-redoarchive1.png" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="148" width="600" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhkLmQsygLDrUrMIceLYmfNnF3criBdybcIY3Lcie7YjbMr182h5L17nYIdMp-nSEnChjYC-sZ0bfObbuQLHMk9_S5307fXkwQlfvLAuEkq3xvP3g4coMRhOKQfr_RmuCETGnhUgH8trgqj/s600/em-dbcontrol-dg-config-redoarchive1.png" /></a></div><br />
<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><b>SQL> show parameter fal_server </b>
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fal_server string fresno
</pre><u>Home -> Server -> Initialization Parameters</u><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgLPBGuxu-A1LrRMHtfeZiisc3NB2UqQitOrzd_HL2CAajOJyqOgOVAAvvSveatRSl_aCzMOMZuUvoh5eW3afCpandUTZY9ruF_KRJfItHk4RGmkbtG3kr9Tmp1xphrwfQnX-SdkvMRw8kk/s1600/em-dbcontrol-dg-config-redoarchive2.png" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="142" width="600" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgLPBGuxu-A1LrRMHtfeZiisc3NB2UqQitOrzd_HL2CAajOJyqOgOVAAvvSveatRSl_aCzMOMZuUvoh5eW3afCpandUTZY9ruF_KRJfItHk4RGmkbtG3kr9Tmp1xphrwfQnX-SdkvMRw8kk/s600/em-dbcontrol-dg-config-redoarchive2.png" /></a></div><br />
<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><b>SQL> show parameter remote_login_pa</b>
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile string EXCLUSIVE
</pre><br />
<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><b>SQL> alter system set db_file_name_convert='fresno', 'atlanta' scope=spfile; </b>
System altered.
<b>SQL> alter system set log_file_name_convert='fresno', 'atlanta' scope=spfile; </b>
System altered.
<b>SQL> alter system set standby_file_management=auto;</b>
System altered.
</pre><u>Home -> Server -> Initialization Parameters -> Standby Database</u><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEify-ng_cJm4cRvVERdAVTD3iPjEqiela7DhJ75pfRdT3GzvMopy91xIwI48TNL7M0PlLNqyL8NvD7xBi4EfsPL1I3Ag7JUNc0hlOZ-zCc4MCuUfhOVf_GZzTrTLzXg4yzU2ACWMtIsfGgF/s1600/em-dbcontrol-dg-config-standby2.png" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="253" width="600" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEify-ng_cJm4cRvVERdAVTD3iPjEqiela7DhJ75pfRdT3GzvMopy91xIwI48TNL7M0PlLNqyL8NvD7xBi4EfsPL1I3Ag7JUNc0hlOZ-zCc4MCuUfhOVf_GZzTrTLzXg4yzU2ACWMtIsfGgF/s600/em-dbcontrol-dg-config-standby2.png" /></a></div><br />
<blockquote><span style="color: blue; font-family: 'Courier New', Courier, monospace;"><b><br />
(4) Configure Oracle Net for Redo Transport</b></span></blockquote>Redo Transport moves archived redo logs from the primary to the standby database.<br />
The two databases need to be connected through Oracle Net. <br />
For this you can edit tnsnames.ora files in each server. i.e.<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"># Generated by Oracle configuration tools.
ATLANTA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = oe5srv.localdomain)(PORT = 1521))
)
(CONNECT_DATA =
(SID = DG11G)
)
)
FRESNO =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = oe5clt.localdomain)(PORT = 1521))
)
(CONNECT_DATA =
(SID = DG11G)
)
)
</pre><br />
<blockquote><span style="color: blue; font-family: 'Courier New', Courier, monospace;"><b><br />
(5) create a backup copy of the primary database datafiles</b></span></blockquote>Using RMAN:<br />
<br />
<u>Home -> Server -> Availability -> Schedule Backup</u><br />
<ul><li>Select a whole Database backup and enter host credentials</li>
</ul><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiyBq_zLq_qIelLjyGMLcdWqXZfocTayJqB2yISxm9CHCc3exm57y6CGKrGvc9HN0H3OxlGF6ghKiQfibzYlr1DFiBq2sxD3_vueBTjfuJrin61xeoWY4bNgwmz-xu1840YXA27D0QnHOt4/s1600/em-dbcontrol-dg-config-fullbackup-step1.png" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="321" width="600" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiyBq_zLq_qIelLjyGMLcdWqXZfocTayJqB2yISxm9CHCc3exm57y6CGKrGvc9HN0H3OxlGF6ghKiQfibzYlr1DFiBq2sxD3_vueBTjfuJrin61xeoWY4bNgwmz-xu1840YXA27D0QnHOt4/s600/em-dbcontrol-dg-config-fullbackup-step1.png" /></a></div><br />
<u>Home -> Server -> Availability -> Schedule Customized Backup -> Options</u><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhbs1JqsU5u9ksUq2IATzSD-M6SWZKRP1xR2lRL05jjYnfj5veehmZDnmU5jsZLMxAYPoS-kRhT_cct96fvyzxHHd3_HuddG4VGbTfWI7LA7Yb8v13o5cxzI77b4KwrfdMEx_nQ82pDyLDT/s1600/em-dbcontrol-dg-config-fullbackup-step2.png" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="467" width="600" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhbs1JqsU5u9ksUq2IATzSD-M6SWZKRP1xR2lRL05jjYnfj5veehmZDnmU5jsZLMxAYPoS-kRhT_cct96fvyzxHHd3_HuddG4VGbTfWI7LA7Yb8v13o5cxzI77b4KwrfdMEx_nQ82pDyLDT/s600/em-dbcontrol-dg-config-fullbackup-step2.png" /></a></div><br />
<u>Home -> Server -> Availability -> Schedule Customized Backup -> Settings</u><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjFhCMRN_GqWjOP7pnqDbMKwzCwleJxS6CLD8QjwlJFFkTklvSUETbQAQx-LhfsL28qXqTsXZHPSanP3_ft4m3VH3GWAJ2HfmCb7_KVxefC7KMiKwQEERKRQ7nn-rV1J6RKChievwyQyEB4/s1600/em-dbcontrol-dg-config-fullbackup-step3.png" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="246" width="600" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjFhCMRN_GqWjOP7pnqDbMKwzCwleJxS6CLD8QjwlJFFkTklvSUETbQAQx-LhfsL28qXqTsXZHPSanP3_ft4m3VH3GWAJ2HfmCb7_KVxefC7KMiKwQEERKRQ7nn-rV1J6RKChievwyQyEB4/s600/em-dbcontrol-dg-config-fullbackup-step3.png" /></a></div><br />
<u>Home -> Server -> Availability -> Schedule Backup -> Schedule</u><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEibXy6ECX_-Vo-BQeB7IZdakfcUPBL-ApUmo0Wrd0YUvgTCGHwli4e6_izDlI_igkqWIRSxlCETp8Yh6o2koQ1MMN-b0BULMmKJfQywJydSGPNE0yeYqJT7hILjALtI0wuCcV1mqUQLK_wG/s1600/em-dbcontrol-dg-config-fullbackup-step4.png" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="237" width="600" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEibXy6ECX_-Vo-BQeB7IZdakfcUPBL-ApUmo0Wrd0YUvgTCGHwli4e6_izDlI_igkqWIRSxlCETp8Yh6o2koQ1MMN-b0BULMmKJfQywJydSGPNE0yeYqJT7hILjALtI0wuCcV1mqUQLK_wG/s600/em-dbcontrol-dg-config-fullbackup-step4.png" /></a></div><br />
<u>Home -> Server -> Availability -> Schedule Backup -> Review</u><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiz3d5zzW4T-6EsUQdPxTVQDx296AkN8cI2Bkk3t5J_rYn-ohUhFWe-t-88twszYx2nirxZCqnUXWMaj8o7UOmqAwkbG40aESE8wXm-CVnn8eC4n1nGp5FHpftMVhIUS7yJGmVZ5BEJW6eX/s1600/em-dbcontrol-dg-config-fullbackup-step5.png" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="288" width="600" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiz3d5zzW4T-6EsUQdPxTVQDx296AkN8cI2Bkk3t5J_rYn-ohUhFWe-t-88twszYx2nirxZCqnUXWMaj8o7UOmqAwkbG40aESE8wXm-CVnn8eC4n1nGp5FHpftMVhIUS7yJGmVZ5BEJW6eX/s600/em-dbcontrol-dg-config-fullbackup-step5.png" /></a></div><br />
<u>Home -> Server -> Availability -> Schedule Backup -> Summary</u><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEieb5g_fT1LfxkPvoGlir_3kUZREJNu6LoBy6P1quEALWmZfQiOC6w8e_B37SgxvainF2pAUpU9W5VkmWxeoSBd_cchGp3rq563Hc4uQ9wWASao8vQYtvgyLMkMBM2eFM9Kdgo2kCMoS-ou/s1600/em-dbcontrol-dg-config-fullbackup-step6.png" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="288" width="600" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEieb5g_fT1LfxkPvoGlir_3kUZREJNu6LoBy6P1quEALWmZfQiOC6w8e_B37SgxvainF2pAUpU9W5VkmWxeoSBd_cchGp3rq563Hc4uQ9wWASao8vQYtvgyLMkMBM2eFM9Kdgo2kCMoS-ou/s600/em-dbcontrol-dg-config-fullbackup-step6.png" /></a></div><br />
<blockquote><span style="color: blue; font-family: 'Courier New', Courier, monospace;"><b><br />
(6) Create a Control file for the Physical Standby Database</b></span></blockquote><pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><b>SQL> alter database create standby controlfile as '/tmp/fresno_stdby.ctl';</b>
Database altered.
</pre><br />
<blockquote><span style="color: blue; font-family: 'Courier New', Courier, monospace;"><b><br />
(7) Create a Parameter File for the Physical Standby Database. <br />
Edit the Parameter file to configure the settings for the Standby</b></span></blockquote><pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><b>(3.1) Create a PFILE from the SPFILE used by the Primary DB</b>
<b>SQL> create pfile= '/tmp/initfresno.ora' from spfile;</b>
File created.
<b>(3.2) Modify the PFILE to be used on a Physical Standby</b>
$ cat initfresno.ora
dg11g.__db_cache_size=67108864
dg11g.__java_pool_size=4194304
dg11g.__large_pool_size=4194304
dg11g.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
dg11g.__pga_aggregate_target=150994944
dg11g.__sga_target=205520896
dg11g.__shared_io_pool_size=0
dg11g.__shared_pool_size=117440512
dg11g.__streams_pool_size=4194304
<b>*.audit_file_dest=</b>'/u01/app/oracle/admin/<b>fresno</b>/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
<b>*.control_files</b>='/u01/app/oracle/oradata/<b>fresno</b>/control01.ctl',
'/u01/app/oracle/flash_recovery_area/<b>fresno</b>/control02.ctl'
*.db_block_size=8192
*.db_domain=''
<b>*.db_file_name_convert</b>='atlanta', 'fresno'
*.db_name='dg11g'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4039114752
<b>*.db_unique_name='fresno'</b>
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dg11gXDB)'
*.fal_server='atlanta'#In case of a switchover, fetch records from the fal_server
*.log_archive_config='dg_config=(atlanta, fresno)'#db_unique_names of the databases in the DG configuration
<b>*.log_archive_dest_1</b>='location=use_db_recovery_file_dest valid_for=(all_logfiles, all_roles)
db_unique_name=fresno'#configure local redo log archiving. Use Fast recovery Area
<b>*.log_archive_dest_2</b>='service=atlanta async valid_for=(online_logfiles, primary_role)
db_unique_name=atlanta'#configure remote log archiving. Configure redo transport to physical standby
*.log_archive_format='%t_%s_%r.dbf'
<b>*.log_file_name_convert='atlanta', 'fresno'</b>
*.memory_target=356515840
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
</pre><br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: large;"><b><br />
Standby Server Setup</b></span><br />
<blockquote><span style="color: blue; font-family: 'Courier New', Courier, monospace;"><b><br />
(1) Create appropriate directories in the Standby server. Copy files from the prymary to the standby system.</b></span></blockquote><pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><b>(8.0) Create directories for fresno database files</b>
$ mkdir -p oradata/fresno
$ mkdir -p flash_recovery_area/fresno
$ mkdir -p admin/fresno/adump
<b>(8.1) Copy the standby control files to all locations (in the same server)</b>
$ scp oracle@oe5srv:/tmp/fresno.ctl /u01/app/oracle/oradata/fresno/control01.ctl
$ cp /u01/app/oracle/oradata/fresno/control01.ctl /u01/app/oracle/flash_recovery_area/fresno/control02.ctl
<b>(8.2) Copy Archivelogs and backups (in the same server)</b>
$ scp -r oracle@oe5srv:/u01/app/oracle/flash_recovery_area/ATLANTA/archivelog /u01/app/oracle/flash_recovery_area/fresno
$ scp -r oracle@oe5srv:/u01/app/oracle/flash_recovery_area/ATLANTA/backupset /u01/app/oracle/flash_recovery_area/fresno
<b>(8.3) Copy passwordfile and parameter file</b>
$ scp -r oracle@oe5srv:$ORACLE_HOME/dbs/orapwdg11g $ORACLE_HOME/dbs
$ scp -r oracle@oe5srv:/tmp/initfresno.ora /tmp/initfresno.ora</pre><blockquote><span style="color: blue; font-family: 'Courier New', Courier, monospace;"><b><br />
(2) Create SPFILE from the edited parameter file</b></span></blockquote><pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;">oracle@oe5clt:$ echo $ORACLE_SID
dg11g
oracle@oe5clt:$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Mar 8 00:37:44 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
<b>SQL> create spfile from pfile='/tmp/initfresno.ora';</b>
File created.
</pre><blockquote><span style="color: blue; font-family: 'Courier New', Courier, monospace;"><b><br />
(3) Restore backup</b></span></blockquote><pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><b>RMAN> startup mount; </b>
Oracle instance started
database mounted
<b>RMAN> restore database; </b>
Starting restore at 08-MAR-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/<b>fresno</b>/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/<b>fresno</b>/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/<b>fresno</b>/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/<b>fresno</b>/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/<b>FRESNO</b>/backupset/o1_mf_nnndf_<b>BACKUP_ATLANTA</b>_000_0_7ojnc7g1_.bkp
channel ORA_DISK_1: piece <b>handle=/u01/app/oracle/flash_recovery_area/<b>FRESNO</b>/backupset/o1_mf_nnndf_</b>BACKUP_ATLANTA_000_0_7ojnc7g1_.bkp tag=BACKUP_ATLANTA_000_030712102725
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:37
Finished restore at 08-MAR-12
</pre><blockquote><span style="color: blue; font-family: 'Courier New', Courier, monospace;"><b><br />
(4) Create Online and Standby Redo logs on the Standby Database</b></span></blockquote><pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><b>SQL> alter system set standby_file_management=manual;</b>
System altered.
<b>SQL> alter database add logfile('/u01/app/oracle/oradata/fresno/online_redo01.log') size 50m;</b>
Database altered.
<b>SQL> alter database add logfile('/u01/app/oracle/oradata/fresno/online_redo02.log') size 50m;</b>
Database altered.
<b>SQL> alter database add logfile('/u01/app/oracle/oradata/fresno/online_redo03.log') size 50m;</b>
Database altered.
<b>SQL> alter system set standby_file_management=auto;</b>
System altered.
<b>SQL> alter database add standby logfile('/u01/app/oracle/oradata/fresno/standby_redo01.log') size 50m;</b>
Database altered.
<b>SQL> alter database add standby logfile('/u01/app/oracle/oradata/fresno/standby_redo02.log') size 50m;</b>
Database altered.
<b>SQL> alter database add standby logfile('/u01/app/oracle/oradata/fresno/standby_redo03.log') size 50m;</b>
Database altered.
<b>SQL> alter database add standby logfile('/u01/app/oracle/oradata/fresno/standby_redo04.log') size 50m;</b>
Database altered.
</pre><br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: large;"><b><br />
Start Apply Service</b></span><br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;">SQL> Alter database recover managed standby database disconnect from session;
Database altered.
</pre><br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />Minima-Imagemhttp://www.blogger.com/profile/03798032367384388122noreply@blogger.com0tag:blogger.com,1999:blog-7852430287014615375.post-9771246285263564662012-02-13T16:10:00.000-08:002012-06-27T16:21:28.891-07:00SQL Functions (11gR2)<div class="separator" style="clear: both; text-align: center; float:right;"><img border="0" height="78" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7et2WtvbHj-TgGXo-iw2HNsWQBtVPGmYhTcYQEzHu9Wbrzm5cq0EaiiDqYg31BNURmiUfjkxhLoRMPE-qXq0HOw1YYH5mKLCvnXatuaTvmS9iOxdVP9MP4oOhIGUmHxpeorMhaHNI8l2s/s400/oracle-11g-logo.gif" width="246" /></div><div class="separator" style="clear: both; text-align: center;float:right; margin-left:1em; margin-bottom:1em;"><img border="0" height="184" width="200" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjqoLCdD23_k2LAu4IfND3Ygh_CrlWcFhmr3PUwE9m9N9LGaLk4hTHzpRkFt3Da6aonQ1RXBeS0gMPWvdlIZajD0_nQjUcU73PbuCtuk_db6WZkEO-XTJwg_hFcZTDH6MQ_A6IlN-p92aJ_/s200/SQL-Functions.png" /></div><br />
<br />
<br />
<a href="http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions.htm#i1482196" target=_blank><b style="background-color: white; font-size: large;"><span style="font-family: 'Courier New', Courier, monospace;"><br />
<table><tr> <td width="40%" >Single-row F</td> <td width="40%" >Aggregate F</td> <td width="25%" >Analytic F</td> </tr>
<tr> <td>Object Reference F</td> <td>Model F</td> <td>OLAP F</td> </tr>
</table></b></span></a><br />
<br />
<b style="background-color: white; font-size: large;"><span style="font-family: 'Courier New', Courier, monospace;">Single-row functions</span></b><br />
<br />
<ul><li><span style="background-color: white;"> <span style="color: blue;"><i><b>Numeric functions (26, 11gR2)</b></i></span></span></li>
</ul><blockquote class="tr_bq"><ul><li>ABS</li>
<li><span style="background-color: white;">ACOS</span></li>
<li><span style="background-color: white;"> ASIN</span></li>
<li><span style="background-color: white;">ATAN</span></li>
<li><span style="background-color: white;">ATAN2</span></li>
<li><span style="background-color: white;">BITAND</span></li>
<li><span style="background-color: white;">CEIL</span></li>
<li><span style="background-color: white;">COS</span></li>
<li><span style="background-color: white;">COSH</span></li>
<li><span style="background-color: white;">EXP</span></li>
<li><span style="background-color: white;">FLOOR</span></li>
<li><span style="background-color: white;">LN</span></li>
<li><span style="background-color: white;">LOG</span></li>
<li><span style="background-color: white;">MOD</span></li>
<li><span style="background-color: white;">NANVL</span></li>
<li><span style="background-color: white;">POWER</span></li>
<li><span style="background-color: white;">REMAINDER</span></li>
<li><span style="background-color: white;">ROUND (number)</span></li>
<li><span style="background-color: white;">SIGN</span></li>
<li><span style="background-color: white;">SIN</span></li>
<li><span style="background-color: white;">SINH</span></li>
<li><span style="background-color: white;">SQRT</span></li>
<li><span style="background-color: white;">TAN</span></li>
<li><span style="background-color: white;">TANH</span></li>
<li><span style="background-color: white;">TRUNC (number)</span></li>
<li><span style="background-color: white;">WIDTH BUCKET </span></li>
</ul></blockquote><ul><li><span style="background-color: white; color: blue;"><i><b> Character F returning character values (22, 11gR2)</b></i></span></li>
</ul><blockquote><ul><li><span style="background-color: white;">CHR</span></li>
<li><span style="background-color: white;">CONCAT</span></li>
<li><span style="background-color: white;">INITCAP</span></li>
<li><span style="background-color: white;">LOWER</span></li>
<li><span style="background-color: white;">LPAD</span></li>
<li><span style="background-color: white;">LTRIM</span></li>
<li><span style="background-color: white;">NCHR</span></li>
<li><span style="background-color: white;">NLS_INITCAP</span></li>
<li><span style="background-color: white;">NLS_LOWER</span></li>
<li><span style="background-color: white;">NLS_UPPER</span></li>
<li><span style="background-color: white;">NLSSORT</span></li>
<li><span style="background-color: white;">REGEXP_REPLACE</span></li>
<li><span style="background-color: white;">REGEXP_SUBSTR</span></li>
<li><span style="background-color: white;">REPLACE</span></li>
<li><span style="background-color: white;">RPAD</span></li>
<li><span style="background-color: white;">RTRIM</span></li>
<li><span style="background-color: white;">SOUNDEX</span></li>
<li><span style="background-color: white;">SUBSTR</span></li>
<li><span style="background-color: white;">TRANSLATE</span></li>
<li><span style="background-color: white;">TREAT</span></li>
<li><span style="background-color: white;">TRIM</span></li>
<li><span style="background-color: white;">UPPER</span></li>
</ul></blockquote><ul><li><span style="background-color: white; color: blue;"><b><i> Character F returning number values (5, 11gR2)</i></b></span></li>
</ul><blockquote><ul><li><span style="background-color: white;">ASCII</span></li>
<li><span style="background-color: white;">INSTR</span></li>
<li><span style="background-color: white;">LENGTH</span></li>
<li><span style="background-color: white;">REGEXP_COUNT</span></li>
<li><span style="background-color: white;">REGEXP_INSTR</span></li>
</ul></blockquote><br />
<ul><li><span style="background-color: white;"> <b><i><span style="color: blue;">NLS Character F (3, 11gR2)</span></i></b></span></li>
</ul><blockquote><ul><li><span style="background-color: white;">NLS_CHARSET_DECL_LEN</span></li>
<li><span style="background-color: white;">NLS_CHARSET_ID</span></li>
<li><span style="background-color: white;">NLS_CHARSET_NAME</span></li>
</ul></blockquote><br />
<ul><li><span style="background-color: white; color: blue;"><i><b>Datetime F (28, 11GR2)</b></i></span></li>
</ul><blockquote><ul><li><span style="background-color: white;"> ADD_MONTHS</span></li>
<li><span style="background-color: white;">CURRENT_DATE</span></li>
<li><span style="background-color: white;">CURRENT_TIMESTAMP</span></li>
<li><span style="background-color: white;">DBTIMEZONE</span></li>
<li><span style="background-color: white;">EXTRACT (datetime)</span></li>
<li><span style="background-color: white;">FROM_TZ</span></li>
<li><span style="background-color: white;">LAST_DAY</span></li>
<li><span style="background-color: white;">LOCALTIMESTAMP</span></li>
<li><span style="background-color: white;">MONTHS_BETWEEN</span></li>
<li><span style="background-color: white;">NEW_TIME</span></li>
<li><span style="background-color: white;">NEXT_DAY</span></li>
<li><span style="background-color: white;">NUMTODSINTERVAL</span></li>
<li><span style="background-color: white;">NUMTOYMINTERVAL</span></li>
<li><span style="background-color: white;">ORA_DST_AFFECTED</span></li>
<li><span style="background-color: white;">ORA_DST_CONVERT</span></li>
<li><span style="background-color: white;">ORA_DST_ERROR</span></li>
<li><span style="background-color: white;">ROUND (date)</span></li>
<li><span style="background-color: white;">SESSIONTIMEZONE</span></li>
<li><span style="background-color: white;">SYS_EXTRACT_UTC</span></li>
<li><span style="background-color: white;">SYSDATE</span></li>
<li><span style="background-color: white;">SYSTIMESTAMP</span></li>
<li><span style="background-color: white;">TO_CHAR (datetime)</span></li>
<li><span style="background-color: white;">TO_DSINTERVAL</span></li>
<li><span style="background-color: white;">TO_TIMESTAMP</span></li>
<li><span style="background-color: white;">TO_TIMESTAMP_TZ</span></li>
<li><span style="background-color: white;">TO_YMINTERVAL</span></li>
<li><span style="background-color: white;">TRUNC (date)</span></li>
<li><span style="background-color: white;">TZ_OFFSET</span></li>
</ul></blockquote><br />
<div><ul><li><span style="background-color: white; color: blue;"><i><b>General comparison F (2, 11gR2)</b></i></span></li>
</ul><blockquote><ul><li><span style="background-color: white;">GREATEST</span></li>
<li><span style="background-color: white;">LEAST</span></li>
</ul></blockquote></div><br />
<div><ul><li><span style="background-color: white;"> <b><i><span style="color: blue;">Conversion F (38, 11gR2)</span></i></b></span></li>
</ul><blockquote><ul><li><span style="background-color: white;">ASCIISTR</span></li>
<li><span style="background-color: white;">BIN_TO_NUM</span></li>
<li><span style="background-color: white;">CAST</span></li>
<li><span style="background-color: white;">CHARTOROWID</span></li>
<li><span style="background-color: white;">COMPOSE</span></li>
<li><span style="background-color: white;">CONVERT</span></li>
<li><span style="background-color: white;">DECOMPOSE</span></li>
<li><span style="background-color: white;">HEXTORAW</span></li>
<li><span style="background-color: white;">NUMTODSINTERVAL</span></li>
<li><span style="background-color: white;">NUMTOYMINTERVAL</span></li>
<li><span style="background-color: white;">RAWTOHEX</span></li>
<li><span style="background-color: white;">RAWTONHEX</span></li>
<li><span style="background-color: white;">ROWIDTOCAR</span></li>
<li><span style="background-color: white;">ROWIDTONCHAR</span></li>
<li><span style="background-color: white;">SCN_TO_TIMESTAMP</span></li>
<li><span style="background-color: white;">TIMESTAMP_TO_SCN</span></li>
<li><span style="background-color: white;">TO_BINARY_FLOAT</span></li>
<li><span style="background-color: white;">TO_BINARY_DOUBLE</span></li>
<li><span style="background-color: white;">TO_BLOB</span></li>
<li><span style="background-color: white;">TO_CHAR (character)</span></li>
<li><span style="background-color: white;">TO_CHAR (datetime)</span></li>
<li><span style="background-color: white;">TO_CHAR (number)</span></li>
<li><span style="background-color: white;">TO_CLOB</span></li>
<li><span style="background-color: white;">TO_DATE</span></li>
<li><span style="background-color: white;">TO_DSINTERVAL</span></li>
<li><span style="background-color: white;">TO_LOB</span></li>
<li><span style="background-color: white;">TO_MULTI_BYTE</span></li>
<li><span style="background-color: white;">TO_NCHAR (character)</span></li>
<li><span style="background-color: white;">TO_NCHAR (datetime)</span></li>
<li><span style="background-color: white;">TO_NCHAR (number)</span></li>
<li><span style="background-color: white;">TO_NCLOB</span></li>
<li><span style="background-color: white;">TO_NUMBER</span></li>
<li><span style="background-color: white;">TO_SINGLE_BYTE</span></li>
<li><span style="background-color: white;">TO_TIMESTAMP</span></li>
<li><span style="background-color: white;">TO_TIMESTAMP_TZ</span></li>
<li><span style="background-color: white;">TO_YMINTERVAL</span></li>
<li><span style="background-color: white;">TRANSLATE ... USING</span></li>
<li><span style="background-color: white;">UNISTR</span></li>
</ul></blockquote></div><br />
<div><ul><li><span style="background-color: white;"> <i><b><span style="color: blue;">Large Object F (3, 11gR2)</span></b></i></span></li>
</ul><blockquote><ul><li><span style="background-color: white;">BFILENAME</span></li>
<li><span style="background-color: white;">EMPTY_BLOB</span></li>
<li><span style="background-color: white;">EMPTY_CLOB</span></li>
</ul></blockquote></div><br />
<div><ul><li><span style="background-color: white; color: blue;"><i><b> Collection F (5, 11gR2)</b></i></span></li>
</ul><blockquote><ul><li><span style="background-color: white;">CARDINALITY</span></li>
<li><span style="background-color: white;">COLLECT</span></li>
<li><span style="background-color: white;">POWERMULTISET</span></li>
<li><span style="background-color: white;">POWERMULTISET_BY_CARDINALITY</span></li>
<li><span style="background-color: white;">SET</span></li>
</ul></blockquote></div><br />
<div><ul><li><span style="background-color: white; color: blue;"><i><b> Hierarchical F (1, 11gR2)</b></i></span></li>
</ul><blockquote><ul><li><span style="background-color: white;">SYS_CONNECT_BY_PATH</span></li>
<li><span style="background-color: white; color: blue;"><i><b> Data Mining F (12, 11gR2)</b></i></span></li>
<li><span style="background-color: white;">CLUSTER_ID</span></li>
<li><span style="background-color: white;">CLUSTER_PROBABILITY</span></li>
<li><span style="background-color: white;">CLUSTER_SET</span></li>
<li><span style="background-color: white;">FEATURE_ID</span></li>
<li><span style="background-color: white;">FEATURE_SET</span></li>
<li><span style="background-color: white;">FEATURE_VALUE</span></li>
<li><span style="background-color: white;">PREDICTION</span></li>
<li><span style="background-color: white;">PREDICTION_BOUNDS</span></li>
<li><span style="background-color: white;">PREDICTION_COST</span></li>
<li><span style="background-color: white;">PREDICTION_DETAILS</span></li>
<li><span style="background-color: white;">PREDICTION_PROBABILITY</span></li>
<li><span style="background-color: white;">PREDICTION_SET</span></li>
</ul></blockquote></div><br />
<div><ul><li><span style="background-color: white; color: blue;"><i><b> XML F (26, 11gR2)</b></i></span></li>
</ul><br />
<ul><li><span style="background-color: white; color: blue;"><b><i>Encoding and Decoding F (4, 11gR2)</i></b></span></li>
</ul><blockquote><ul><li><span style="background-color: white;">DECODE</span></li>
<li><span style="background-color: white;">DUMP</span></li>
<li><span style="background-color: white;">ORA_HASH</span></li>
<li><span style="background-color: white;">VSIZE</span></li>
</ul></blockquote></div><br />
<div><ul><li><span style="background-color: white; color: blue;"><i><b>NULL-related F (6, 11gR2)</b></i></span></li>
</ul><blockquote><ul><li><span style="background-color: white;">COALESCE</span></li>
<li><span style="background-color: white;">LNNVL</span></li>
<li><span style="background-color: white;">NANVL</span></li>
<li><span style="background-color: white;">NULLIF</span></li>
<li><span style="background-color: white;">NVL</span></li>
<li><span style="background-color: white;">NVL2</span></li>
</ul></blockquote></div><br />
<div><ul><li><span style="background-color: white; color: blue;"><i><b>Environment and Identifier F (6, 11gR2)</b></i></span></li>
</ul><blockquote><ul><li><span style="background-color: white;">SYS_CONTEXT</span></li>
<li><span style="background-color: white;">SYS_GUID</span></li>
<li><span style="background-color: white;">SYS_TYPEID</span></li>
<li><span style="background-color: white;">UID</span></li>
<li><span style="background-color: white;">USER</span></li>
<li><span style="background-color: white;">USERENV</span></li>
</ul></blockquote></div><div><ul></ul><br />
<br />
<b style="background-color: white; font-size: large;"><span style="font-family: 'Courier New', Courier, monospace;">Aggregate Functions</span></b><br />
<i><b><span style="color: blue;">(41, 11gR2)</span></b></i><br />
Operate on groups of rows<br />
Appear in SELECT, ORDER BY and HAVING<br />
<br />
<ul><li><span style="background-color: white;">AVG</span></li>
<li><span style="background-color: white;">COLLECT</span></li>
<li><span style="background-color: white;">CORR</span></li>
<li><span style="background-color: white;">CORR *</span></li>
<li><span style="background-color: white;">COUNT</span></li>
<li><span style="background-color: white;">COVAR POP</span></li>
<li><span style="background-color: white;">COVAR SAMP</span></li>
<li><span style="background-color: white;">CUME DIST</span></li>
<li><span style="background-color: white;">DENSE RANK</span></li>
<li><span style="background-color: white;">FIRST</span></li>
<li><span style="background-color: white;">GROUP_ID</span></li>
<li><span style="background-color: white;">GROUPING</span></li>
<li><span style="background-color: white;">GROUPING_ID</span></li>
<li><span style="background-color: white;">LAST</span></li>
<li><span style="background-color: white;">LISTAGG</span></li>
<li><span style="background-color: white;">MAX</span></li>
<li><span style="background-color: white;">MEDIAN</span></li>
<li><span style="background-color: white;">MIN</span></li>
<li><span style="background-color: white;">PERCENT_RANK</span></li>
<li><span style="background-color: white;">PERCENTILE_CONT</span></li>
<li><span style="background-color: white;">PERCENTILE_DISC</span></li>
<li><span style="background-color: white;">RANK</span></li>
<li><span style="background-color: white;">STATS_BINOMIAL_TEST</span></li>
<li><span style="background-color: white;">STATS_CROSSTAB</span></li>
<li><span style="background-color: white;">STATS_F_TEST</span></li>
<li><span style="background-color: white;">STATS_KS_TEST</span></li>
<li><span style="background-color: white;">STATS_MODE</span></li>
<li><span style="background-color: white;">STATS_MW_TEST</span></li>
<li><span style="background-color: white;">STATS_ONEW_WAY_ANOVA</span></li>
<li><span style="background-color: white;">STATS_T_TEST *</span></li>
<li><span style="background-color: white;">STATS_WSR_TEST</span></li>
<li><span style="background-color: white;">STDDEV</span></li>
<li><span style="background-color: white;">STDDEV_POP</span></li>
<li><span style="background-color: white;">STDDEV_SAMP</span></li>
<li><span style="background-color: white;">SUM</span></li>
<li><span style="background-color: white;">SYS_XMLAGG</span></li>
<li><span style="background-color: white;">VAR_POP</span></li>
<li><span style="background-color: white;">VAR_SAMP</span></li>
<li><span style="background-color: white;">VARIANCE</span></li>
<li><span style="background-color: white;">XMLAGG</span></li>
</ul><ul><li><span style="background-color: white;"><i><b><span style="color: blue;">Linear Regression Functions (9, 11gR2)</span></b></i></span></li>
</ul><blockquote><ul><li><span style="background-color: white;">REGR_SLOPE</span></li>
<li><span style="background-color: white;">REGR_INTERCEPT</span></li>
<li><span style="background-color: white;">REGR_COUNT</span></li>
<li><span style="background-color: white;">REGR_R2</span></li>
<li><span style="background-color: white;">REGR_AVGX</span></li>
<li><span style="background-color: white;">REGR_AVGY</span></li>
<li><span style="background-color: white;">REGR_SXX</span></li>
<li><span style="background-color: white;">REGR_SYY</span></li>
<li><span style="background-color: white;">REGR_SXY</span></li>
</ul></blockquote><br />
<br />
<b style="background-color: white; font-size: large;"><span style="font-family: 'Courier New', Courier, monospace;">Analytic Functions</span></b><br />
<b><i><span style="color: blue;">(32, 11gR2)</span></i></b><br />
Commonly used on Data Warehousing environments<br />
Analytic functions are the last set of operations performed in a query, followed only by the ORDER BY clause<br />
All joins and the WHERE, GROUP BY and HAVING clauses are completed prior to the analytic functions are processed<br />
Good to compute cumulative, moving, centered, and reporting aggregates<br />
<br />
<ul><li><b>AVG *</b></li>
<li>CORR *</li>
<li>COUNT *</li>
<li>COVAR_POP *</li>
<li>COVAR_SAMP *</li>
<li>CUME_DIST</li>
<li>DENSE_RANK</li>
<li>FIRST</li>
<li><b>FIRST_VALUE *</b></li>
<li>LAG</li>
<li>LAST</li>
<li><b>LAST_VALUE *</b></li>
<li>LEAD</li>
<li>LISTAGG</li>
<li>MAX *</li>
<li>MIN *</li>
<li>NTH VALUE *</li>
<li>NTILE</li>
<li>PERCENT_RANK</li>
<li>PERCENTILE_CONT</li>
<li>PERCENTILE_DISC</li>
<li><b>RANK</b></li>
<li>RATIO_TO_REPORT</li>
<li>ROW_NUMBER</li>
<li>STDDEV *</li>
<li>STDDEV_POP *</li>
<li>STDDEV_SAMP *</li>
<li>SUM *</li>
<li>VAR_POP *</li>
<li>VAR_SAMP *</li>
<li>VARIANCE *</li>
<li><b><i><span style="color: blue;">Linear Regression Functions *</span></i></b></li>
</ul><br />
<ul><li><b>AVG</b></li>
</ul><br />
<br />
<div class="separator" style="clear: both; text-align: center;float:right; margin-left:1em; margin-bottom:1em;"><img border="0" height="145" width="600" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi4adynOAnxuqEkEEGiskAdh1EbYNdNMtR57vvlcpwOmQVJO9DRTUfWFc-K_V0eCQjSCumHMNfYn1VK3KRDbgrGdH-CPTc6L8vkBm7_C3gtLRMHtejrYaMCK5GfY5ZbwcmR9B9TRThNxHQ8/s600/avg+analytic+function1.png" /></div><br />
<pre style="font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; color: #000000; font-size: 12px;border: 1px dashed #999999;line-height: 14px;
background-color: #eee; padding: 5px; overflow: auto; width: 100%"><b>(a) Reporting each employee's salary and the average salary of the employees within the same department.</b>
SELECT first_name || ' ' || last_name AS emp_name,
department_id,
salary,
<b>AVG (salary) OVER (PARTITION BY department_id) AS avg_dept_sal</b>
FROM hr.employees;
EMP_NAME DEPARTMENT_ID SALARY AVG_DEPT_SAL
------------------ ------------- ------- ------------
Adam Fripp 50 8200 3475.55556
Alana Walsh 50 3100 3475.55556
Alberto Errazuriz 80 12000 8955.88235
Alexander Hunold 60 9000 5760
Alexander Khoo 30 3100 4150
Alexis Bull 50 4100 3475.55556
Allan McEwen 80 9000 8955.88235
Alyssa Hutton 80 8800 8955.88235
...
</pre><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjJKakVIKtQNrhlCMyfazXNkXigAjYMEvCCYtgJIdeV7JWtre9TMRWhQr5qsJ3zdWJZ0vHpw76aesgX__zt7MYsrVwAjduMTdZq5ejBeAT1vWDqQXvVOVYcmz_Uv_x9LGR0-urWparUR5wi/s1600/avg-analytic-clause1.png" imageanchor="1" style="clear:right; float:right; margin-left:1em; margin-bottom:1em"><img border="0" height="184" width="700" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjJKakVIKtQNrhlCMyfazXNkXigAjYMEvCCYtgJIdeV7JWtre9TMRWhQr5qsJ3zdWJZ0vHpw76aesgX__zt7MYsrVwAjduMTdZq5ejBeAT1vWDqQXvVOVYcmz_Uv_x9LGR0-urWparUR5wi/s700/avg-analytic-clause1.png" /></a></div><br />
<pre style="font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; color: #000000; font-size: 12px;border: 1px dashed #999999;line-height: 14px;
background-color: #eee; padding: 5px; overflow: auto; width: 100%"><b>(a) Lists manager_id, employee's name, hire_date, salary and the average salary of all employees that have the same manager</b>
-- note the AVG is calculated over <b>all rows</b> within each partition.
-- <span style="color: blue;"><b>no windowing clause is present</b></span>
SELECT manager_id,
last_name,
hire_date,
salary,
<b> AVG (salary) OVER (PARTITION BY manager_id) AS c_mavg</b>
FROM hr.employees
ORDER BY manager_id, hire_date, salary;
MANAGER_ID LAST_NAME HIRE_DATE SALARY C_MAVG
---------- ------------------------- --------- ---------- ----------
100 De Haan 13-JAN-01 17000 11100
100 Raphaely 07-DEC-02 11000 11100
100 Kaufling 01-MAY-03 7900 11100
100 Hartstein 17-FEB-04 13000 11100
100 Weiss 18-JUL-04 8000 11100
...
100 Zlotkey 29-JAN-08 10500 11100
101 Mavris 07-JUN-02 6500 8983.2
...
101 Whalen 17-SEP-03 4400 8983.2
102 Hunold 03-JAN-06 9000 9000
103 Austin 25-JUN-05 4800 4950
...
(b)Lists employee salary and average salary of the employees reporting to the same manager <b>who were hired in the range just before through just after the employee</b>.
-- note the query has
-- 1. a <b>partition </b>clause (partition rows by manager)
-- 2. a <span style="color: blue;"><b>windowing </b>clause</span> (within each partition, AVG considers only the rows immediately preceding and following each employees' row).
SELECT manager_id,
last_name,
hire_date,
salary,
AVG (
salary)
<b>OVER (PARTITION BY manager_id
ORDER BY hire_date
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
AS c_mavg</b>
FROM hr.employees
ORDER BY manager_id, hire_date, salary;
MANAGER_ID LAST_NAME HIRE_DATE SALARY C_MAVG
---------- ------------------------- --------- ---------- ----------
100 De Haan 13-JAN-01 17000 14000
100 Raphaely 07-DEC-02 11000 11966.6667
...
100 Cambrault 15-OCT-07 11000 7766.66667
100 Mourgos 16-NOV-07 5800 9100
100 Zlotkey 29-JAN-08 10500 8150
101 Mavris 07-JUN-02 6500 8250
101 Baer 07-JUN-02 10000 9502.66667
...
101 Whalen 17-SEP-03 4400 8204
102 Hunold 03-JAN-06 9000 9000
103 Austin 25-JUN-05 4800 4800
...
</pre><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiDhxMVsq30STcGMazE39x_rFAuhKcBdlzEhb_hP2ULALrlNh5by23JSlvMxO09K3lDhaRQmSQlNZOkC4opMnanJKUCLvDBZMDcIp_SXOooEfEe83pLm6sRoq2Pp9CChgTTcyyzGTNSPJ_D/s1600/analytic_functions_windowing_clause.png" imageanchor="1" style="clear:right; float:right; margin-left:1em; margin-bottom:1em"><img border="0" height="334" width="700" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiDhxMVsq30STcGMazE39x_rFAuhKcBdlzEhb_hP2ULALrlNh5by23JSlvMxO09K3lDhaRQmSQlNZOkC4opMnanJKUCLvDBZMDcIp_SXOooEfEe83pLm6sRoq2Pp9CChgTTcyyzGTNSPJ_D/s700/analytic_functions_windowing_clause.png" /></a></div><br />
<br />
<br />
<ul><li><b>FIRST_VALUE and LAST_VALUE</b></li>
</ul><pre style="font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; color: #000000; font-size: 12px;border: 1px dashed #999999;line-height: 14px;
background-color: #eee; padding: 5px; overflow: auto; width: 100%"><b>(a) For each employee, return employee salary and the name of the employee that works in the same department and has the lowest salary</b>
SELECT department_id,
last_name,
salary,
<b>FIRST_VALUE (last_name)</b>
<b>OVER (PARTITION BY department_id
ORDER BY salary ASC, hire_date
ROWS UNBOUNDED PRECEDING)
AS lowest_sal</b>
FROM hr.employees
ORDER BY department_id, last_name;
DEPARTMENT_ID LAST_NAME SALARY LOWEST_SAL
------------- ------------------------- ---------- -------------------------
10 Whalen 4400 Whalen
20 Fay 6000 Fay
20 Hartstein 13000 Fay
30 Baida 2900 Colmenares
30 Colmenares 2500 Colmenares
30 Himuro 2600 Colmenares
30 Khoo 3100 Colmenares
30 Raphaely 11000 Colmenares
30 Tobias 2800 Colmenares
40 Mavris 6500 Mavris
...
80 Zlotkey 10500 Kumar
90 De Haan 17000 De Haan
90 King 24000 De Haan
90 Kochhar 17000 De Haan
100 Chen 8200 Popp
100 Faviet 9000 Popp
100 Greenberg 12008 Popp
100 Popp 6900 Popp
100 Sciarra 7700 Popp
100 Urman 7800 Popp
...
<b>(b) For each employee of department 50, return salary of employees on the previous and following rows of the record set.</b>
SELECT department_id,
last_name,
salary,
<b> FIRST_VALUE (salary)
OVER (ORDER BY salary ASC
ROWS between 1 preceding and current row) as previous_sal,
LAST_VALUE (salary)
OVER (ORDER BY salary ASC
ROWS between current row and 1 following) as next_sal
</b> FROM hr.employees
where department_id =50;
DEPARTMENT_ID LAST_NAME SALARY PREVIOUS_SAL NEXT_SAL
------------- ------------------------- ---------- ------------ ----------
50 Olson 2100 2100 2200
50 Philtanker 2200 2100 2200
50 Markle 2200 2200 2400
50 Gee 2400 2200 2400
50 Landry 2400 2400 2500
50 Patel 2500 2400 2500
50 Vargas 2500 2500 2500
50 Marlow 2500 2500 2500
50 Perkins 2500 2500 2500
50 Sullivan 2500 2500 2600
...
</pre><br />
<br />
<br />
<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;float:right; margin-left:1em; margin-bottom:1e;"><img border="0" height="110" width="600" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiC-Svn8p54wEy567g2qnifzkp2XRAQmP3Ja-JGwPMuf7wCHtgXsc8VygBm05m8yeazr8Lr72LjyyDZv7MMYRbV93lCnQ0QGoJ0lZULX_hGukBbilDz76F1_wLvyD-NZnengr5CQ6OJpMZU/s600/rank_analytic1.png" /></div><br />
<ul><li><b>RANK</b></li>
</ul><pre style="font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; color: #000000; font-size: 12px;border: 1px dashed #999999;line-height: 14px;
background-color: #eee; padding: 5px; overflow: auto; width: 100%">SELECT department_id,
last_name,
salary,
commission_pct,
RANK ()
OVER (PARTITION BY department_id
ORDER BY salary DESC, commission_pct DESC)
"Rank"
FROM hr.employees
WHERE department_id = 80;
</pre><br />
<br />
<b style="background-color: white; font-size: large;"><span style="font-family: 'Courier New', Courier, monospace;">Object Reference Functions</span></b><br />
<ul><li>DEREF</li>
<li>MAKE_REF</li>
<li>REF</li>
<li>REFTOHEX</li>
<li>VALUE</li>
</ul><br />
<b style="background-color: white; font-size: large;"><span style="font-family: 'Courier New', Courier, monospace;">Model Functions</span></b><br />
<ul><li>CV</li>
<li>ITERATION_NUMBER</li>
<li>PRESENTNNV</li>
<li>PRESENTV</li>
<li>PREVIOUS</li>
</ul><br />
<b style="background-color: white; font-size: large;"><span style="font-family: 'Courier New', Courier, monospace;">OLAP Functions</span></b><br />
<ul><li>CUBE_TABLE</li>
</ul><br />
<b style="background-color: white; font-size: large;"><span style="font-family: 'Courier New', Courier, monospace;">Data Cartridge Functions</span></b><br />
<ul><li>DATAOBJ_TO_PARTITION</li>
</ul><br />
<br />
<b style="background-color: white; font-size: large;"><span style="font-family: 'Courier New', Courier, monospace;">User-Defined Functions</span></b><br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
</div>Minima-Imagemhttp://www.blogger.com/profile/03798032367384388122noreply@blogger.com0