tag:blogger.com,1999:blog-227277132024-03-08T01:16:34.735+02:00Gints Plivna blogThese are my thoughts... Mostly about <a href = "http://www.oracle.com">Oracle</a> and related things.Gints Plivnahttp://www.blogger.com/profile/17716037729002528529noreply@blogger.comBlogger36125tag:blogger.com,1999:blog-22727713.post-63729834459207741522011-03-12T02:21:00.005+02:002011-03-12T04:53:45.019+02:00<h3>Indexes cannot be used for inequalities?</h3><br />For years and years I thought it was true. Now I'd say it's only partially true. OK <b>it is</b> true for b-tree indexes (at least there isn't any reasonable effect of using an index for "not eaquals to"), but for bitmaps it's a different answer though. And I mean generally YES, they can be used :)<br /><br />OK let's look at examples. I'll create a table and populate with almost all the same values but one. That's to make things more attractive for CBO to get the one and only value.<br /><pre>SQL> CREATE TABLE test (<br />2 id NUMBER PRIMARY KEY,<br />3 txt VARCHAR2(10) NOT NULL);<br /><br />Table created.<br /><br />SQL> INSERT INTO test<br />2 SELECT rownum, 'AAA'<br />3 FROM DUAL CONNECT BY LEVEL < 10000;<br /><br />9999 rows created.<br /><br />SQL> INSERT INTO test<br />2 VALUES (10000, 'BBB');<br /><br />1 row created.<br /></pre><br />So now I have 9999 of "AAA" and only one "BBB".<br />I won't explain with examples what one can get with b-tree indexes, the best is INDEX FULL SCAN and <a href="http://richardfoote.wordpress.com/2008/08/13/indexes-and-not-equal-not-now-john/">the example by Richard Foote is here</a>.<br /><br />OK but what if we try bitmap index? Let's create it:<br /><pre>SQL> CREATE BITMAP INDEX txt_idx ON test (txt) COMPUTE STATISTICS;<br /><br />Index created.</pre><br />Now let's see what we get for the query we know should get only 1 row:<br /><pre>SQL> SELECT * FROM test<br /> 2 WHERE txt <> 'AAA';<br /><br /> ID TXT<br />---------- ----------<br /> 10000 BBB<br /><br />--------------------------------------------------------------------------<br />| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<br />--------------------------------------------------------------------------<br />| 0 | SELECT STATEMENT | | 1 | 7 | 7 (0)| 00:00:01 |<br />|* 1 | TABLE ACCESS FULL| TEST | 1 | 7 | 7 (0)| 00:00:01 |<br />--------------------------------------------------------------------------<br />Predicate Information (identified by operation id):<br />---------------------------------------------------<br /><br /> 1 - filter("TXT"<>'AAA')<br /><br />Statistics<br />----------------------------------------------------------<br /> 23 consistent gets<br /> 1 rows processed</pre><br />Bang! Full scan. Not very promising. OK what if we try to hint the query?<br />At first let's give a soft hint - how about using an index?<br /><pre><br />SQL> SELECT /*+ index(test) */ * FROM test<br /> 2 WHERE txt <> 'AAA';<br /><br /> ID TXT<br />---------- ----------<br /> 10000 BBB<br /><br />--------------------------------------------------------------------------------<br />| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|<br />--------------------------------------------------------------------------------<br />| 0 | SELECT STATEMENT | | 1 | 7 | 826 (0)|<br />|* 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 7 | 826 (0)|<br />| 2 | INDEX FULL SCAN | SYS_C009499 | 10000 | | 26 (0)|<br />--------------------------------------------------------------------------------<br />Predicate Information (identified by operation id):<br />---------------------------------------------------<br /> 1 - filter("TXT"<>'AAA')<br />Statistics<br />----------------------------------------------------------<br /> 39 consistent gets<br /> 1 rows processed</pre><br />WoW! That's actually annoying. Bad plan, many consistent gets. Oracle is using primary key index, but not my brand new bitmap index! I'm disappointed.<br />OK now let's hammer it with the precise hint!<br /><pre>SQL> SELECT /*+ index(test txt_idx) */ * FROM test<br /> 2 WHERE txt <> 'AAA';<br /><br /> ID TXT<br />---------- ----------<br /> 10000 BBB<br /><br />-----------------------------------------------------------------------------<br />| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|<br />-----------------------------------------------------------------------------<br />| 0 | SELECT STATEMENT | | 1 | 7 | 36 (0)|<br />| 1 | TABLE ACCESS BY INDEX ROWID | TEST | 1 | 7 | 36 (0)|<br />| 2 | BITMAP CONVERSION TO ROWIDS| | | | |<br />|* 3 | BITMAP INDEX FULL SCAN | TXT_IDX | | | |<br />-----------------------------------------------------------------------------<br />Predicate Information (identified by operation id):<br />---------------------------------------------------<br /> 3 - filter("TXT"<>'AAA')<br /><br />Statistics<br />----------------------------------------------------------<br /> 2 consistent gets<br /> 1 rows processed</pre><br /><br />So that's it!! At last we get it! Only 2 consistent gets. And the cost is much less than using that stupid primary key index full scan. So that means Oracle even didn't consider using this plan at least for the query with just index hint. I looked at 10053 trace and found following for the query with just hint "index(test)":<br />The beginning looks promising:<br /><pre><br />Table Stats::<br /> Table: TEST Alias: A<br /> #Rows: 10000 #Blks: 20 AvgRowLen: 7.00<br />Index Stats::<br /> Index: SYS_C009499 Col#: 1 (NOT ANALYZED)<br /> LVLS: 1 #LB: 25 #DK: 100 LB/K: 1.00 DB/K: 1.00 CLUF: 800.00<br /> <b>User hint to use this index</b><br /> Index: TXT_IDX Col#: 2<br /> LVLS: 0 #LB: 1 #DK: 2 LB/K: 1.00 DB/K: 1.00 CLUF: 2.00<br /> <b>User hint to use this index</b><br /></pre><br />However in access path analysis Oracle simply doesn't even consider my bitmap index.<br /><pre><br />SINGLE TABLE ACCESS PATH<br /> Single Table Cardinality Estimation for TEST[A]<br /> Table: TEST Alias: A<br /> Card: Original: 10000.000000 Rounded: 1 Computed: 1.00 Non Adjusted: 1.00<br /> Access Path: index (FullScan)<br /> Index: SYS_C009499<br /> resc_io: 826.00 resc_cpu: 6932309<br /> ix_sel: 1.000000 ix_sel_with_filters: 1.000000<br /> Cost: 826.39 Resp: 826.39 Degree: 1<br /> Best:: AccessPath: IndexRange<br /> Index: SYS_C009499<br /> Cost: 826.39 Degree: 1 Resp: 826.39 Card: 1.00 Bytes: 0</pre><br /><br />Things however get brighter if we are just counting rows, then we don't need hint or anything, just run the select:<br /><pre>SQL> SELECT count(*) FROM test<br /> 2 WHERE txt <> 'AAA';<br /><br /> COUNT(*)<br />----------<br /> 1<br /><br />------------------------------------------------------------------------------<br />| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|<br />------------------------------------------------------------------------------<br />| 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)|<br />| 1 | SORT AGGREGATE | | 1 | 4 | |<br />| 2 | BITMAP CONVERSION COUNT | | 1 | 4 | 1 (0)|<br />|* 3 | BITMAP INDEX FAST FULL SCAN| TXT_IDX | | | |<br />------------------------------------------------------------------------------<br />Statistics<br />----------------------------------------------------------<br /> 3 consistent gets<br /> 1 rows processed</pre><br />So it is more or less OK that table full scan is better (i.e. cost less than) bitmap index full scan and then bitmap conversion to rowids and then access table by calculated rowids. However it is quite strange that for simple index hint Oracle doesn't even consider this access path however as we can see from CBO trace understands that bitmap index is one of the hinted ones.<br />Of course the usual problems with bitmap indexes remains - locking, concurrency issues etc.<br />And yea I just arrived from <a href="http://richardfoote.wordpress.com/oracle-index-internals-seminar/">Richard's seminar</a> in Tallinn. Many new things, fluent story, cool!! :)Gints Plivnahttp://www.blogger.com/profile/17716037729002528529noreply@blogger.com1tag:blogger.com,1999:blog-22727713.post-43570628290567021952010-02-17T16:58:00.002+02:002010-02-17T17:44:53.716+02:00<h3>Unbreakable Oracle documentation</h3><br />Yes, that's the fact - everyone knowing the real situation will start Homeric laughter. It is down, down, down and down again. To be true sometimes it is also up and running. Unfortunately "sometimes" seems to be more and more rarely. Just now the popular entry point <a href="http://tahiti.oracle.com/">tahiti.oracle.com</a> shows following:<br /><br />Access Denied<br />You don't have permission to access "http://www-portal-stage.oracle.com/splash/www/index.html" on this server.<br />Reference #18.3c55293e.1266419217.19d36a<br /><br />Interesting - is tooooo much to ask Oracle to provide just static htmls (OK there is advanced thing - search feature, however I could also easily use Google instead, at least it is less unbreakable ;) for its users continuously without interruptions more than not?<br />Or after the <a href="http://www.informationweek.com/news/global-cio/security/showArticle.jhtml?articleID=222002898&cid=nl_IW_btl_2010-01-05_t">grand results of maintenance income</a> there isn't any single $ left for an admin to monitor the resource? Or probably the last reliable box has gone to super functional and alike working <a href="https://support.oracle.com/CSP/ui/flash.html">MOS</a>?<div><br /></div><div>Chris Warticki <a href="http://blogs.oracle.com/Support/">in his blog</a> wrote what to do if MOS is down then what? Unfortunately the blog now is (seems to be censored and) closed and for a little while <a href="http://209.85.135.132/search?q=cache:Tbd0DOljVmkJ:blogs.oracle.com/Support/2010/02/if_mos_is_down_then_what.html+site:blogs.oracle.com+my+oracle+support+blog&cd=8&hl=lv&ct=clnk&gl=lv">pages are only accessible from google's cache</a>. Most of the people in oracle-l list say <a href="http://www.freelists.org/post/oracle-l/Oracle-documentation,6">they have downloaded oracle documentation locally</a> in a <a href="http://www.freelists.org/post/oracle-l/Oracle-documentation,3">recent discussion about oracle documentation</a>. However it is bit odd for a company providing leading database of the world having income with nine zeroes being unable to provide simple html files without continued interruptions... </div><div><br /></div><div>Probably that's the reason I more and more have to do something with SQL Server :)</div>Gints Plivnahttp://www.blogger.com/profile/17716037729002528529noreply@blogger.com2tag:blogger.com,1999:blog-22727713.post-66124600166062839602009-02-07T19:32:00.010+02:002009-02-08T16:19:01.114+02:00<h3>Woodwork</h3><br /><br /><p>Time after time I'm doing something different than databases and Oracle :) One of these things is woodwork. So around the New Year I finished the most recent one - a shelf. <em>Requirements, design and implementation</em> fully by me. Here it is just attached to the wall.</p><br /><p><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjdyVVpjRPAX_vAS2Vv2eJtQA3DiDcWUqjubCmSQv5CXBDB78fdTG4IT4di_DfqIQBzJB7krrEjvhG72igr1iEZn5TmjpmnOs5sHTP4hIUyJuhaVuQ62RjHT-WAaj0Ps06PwZJ8/s1600-h/shelf_1.jpg"><img id="BLOGGER_PHOTO_ID_5300117473552464402" style="WIDTH: 400px; HEIGHT: 300px" alt="wood shelf" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjdyVVpjRPAX_vAS2Vv2eJtQA3DiDcWUqjubCmSQv5CXBDB78fdTG4IT4di_DfqIQBzJB7krrEjvhG72igr1iEZn5TmjpmnOs5sHTP4hIUyJuhaVuQ62RjHT-WAaj0Ps06PwZJ8/s400/shelf_1.jpg" border="0" /></a><br />And now full with stuff. Most of pottery in this shelf is produced by my cousin and her husband.<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhIOF-OSSIj48Y4al4IiiwcMtlYpmh3guBt9xpYQi-22F55UajlKyJ9OAMTJpG0avcJNTzMQmj7TAAZ05WFM8E6tIOlEA14NWlTLQIms2R5pmzpYT212VdN6BiCkt3jksqjkEkH/s1600-h/shelf_2.jpg"><img id="BLOGGER_PHOTO_ID_5300120919843728562" style="WIDTH: 400px; HEIGHT: 300px" alt="wood shelf" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhIOF-OSSIj48Y4al4IiiwcMtlYpmh3guBt9xpYQi-22F55UajlKyJ9OAMTJpG0avcJNTzMQmj7TAAZ05WFM8E6tIOlEA14NWlTLQIms2R5pmzpYT212VdN6BiCkt3jksqjkEkH/s400/shelf_2.jpg" border="0" /></a><br />The hobby started already in school years when we had special woodwork lessons and this probably is one of the best results from these times - a turned wood vessel with amber decor on the top.<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi156WnkfHy7IWdMBTh5iTIRxV-HMeczfHNJUGqTjQVGZ2j2CPx6SJbSjmC8EbWww0vTWCXs0u7zif0X_GNh64qONWFUwlwW5cKAtF_UXEA9_XCImdPg_N6k1na5ANHMmtmxWjE/s1600-h/wood_vessel.jpg"><img id="BLOGGER_PHOTO_ID_5300129658612483154" style="WIDTH: 400px; CURSOR: hand; HEIGHT: 300px" alt="turned wood vessel" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi156WnkfHy7IWdMBTh5iTIRxV-HMeczfHNJUGqTjQVGZ2j2CPx6SJbSjmC8EbWww0vTWCXs0u7zif0X_GNh64qONWFUwlwW5cKAtF_UXEA9_XCImdPg_N6k1na5ANHMmtmxWjE/s400/wood_vessel.jpg" border="0" /></a><br />Nowadays there are fantastic possibilities to make something from wood on his own compared to Soviet and early post-Soviet times. Tools, paints, lacquers, various components and semi-finished products in shops are far and away more. For example the shelf above also was created using ground glued board, so actually the creation process is not complicated. <br />One of the most interesting tasks was to restore a few Soviet style chairs as old as myself (i.e ~30 years). Here is a photo with one old chair and one already restored.</p><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi97F6lzakeYGyiDfG3bD8_XA_INhsPKKQHbmFqANRcqXeWVS4BYm9_2HfVaCB2iEBvxY3ckUiLyHs_WnAvrk4j_ivT7urjpW8_lBQAwFK6hf1hSHXc6TMwHzpnVw5Q6afqCSq1/s1600-h/chairs.jpg"><img id="BLOGGER_PHOTO_ID_5300427897102907394" style="WIDTH: 400px; CURSOR: hand; HEIGHT: 268px" alt="Chairs" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi97F6lzakeYGyiDfG3bD8_XA_INhsPKKQHbmFqANRcqXeWVS4BYm9_2HfVaCB2iEBvxY3ckUiLyHs_WnAvrk4j_ivT7urjpW8_lBQAwFK6hf1hSHXc6TMwHzpnVw5Q6afqCSq1/s400/chairs.jpg" border="0" /></a>Gints Plivnahttp://www.blogger.com/profile/17716037729002528529noreply@blogger.com5tag:blogger.com,1999:blog-22727713.post-52454888273315294842009-01-31T22:46:00.002+02:002009-02-01T00:21:59.880+02:00<h3>Security through obscurity - object names</h3><br />This post was inspired by question in one of Latvian forums - can we use only numbers in column names. Of course we can do it using quotes but that's starting point into dangerous path of <a href="http://www.web-hits.org/txt/codingunmaintainable.html">how to write unmaintainable code</a>. But why stop only with tables like C34 and columns 1, 2, 3? There are ways beyond that!<br />So let's start with the same table names differing only in upper/lower case.<br /><pre>SQL> create table A (big number);<br />Table created.<br />SQL> create table "a" (small number);<br />Table created.</pre><br />So what are our tables A and a:<br /><pre>SQL> desc A<br /> Name Null? Type<br /> ----------------------- -------- -------<br /> BIG NUMBER<br />SQL> desc a<br /> Name Null? Type<br /> ----------------------- -------- -------<br /> BIG NUMBER</pre><br />A bit strange, isn't it? But that is because all nonquoted identifiers are converted to uppercase so we have to be precise:<br /><pre>SQL> desc "a"<br /> Name Null? Type<br /> ----------------------- -------- ------<br /> SMALL NUMBER</pre><br />But we can make things even more obscure! For example how about table and/or column name as space?<br /><pre>SQL> create table " " (A number, "a" number);<br />Table created.<br />SQL> select table_name from user_tables;<br />TABLE_NAME<br />------------------------------<br />a<br /><br />A<br />3 rows selected.</pre><br />Previous example with <i>2 rows instead</i> of reported three and next with lost columns definitely could make some nervous people start searching for Oracle bugs.<br /><pre>SQL> create table b (" " number, " " date);<br />Table created.<br />SQL> desc b<br /> Name Null? Type<br /> ----------------------- -------- ------<br /> NUMBER<br /> DATE</pre><br />But we can make things better. Why should we use only symbols on keybord? Remember the nice possibility of ALT key and numeric keypad keys. ALT + these keys gives us symbols according to their ASCII values, so just use your imagination. For example ALT+223 gives me this:<br /><pre>SQL> create table abnormal ("▀" number);<br />Table created.<br />SQL> desc abnormal<br /> Name Null? Type<br /> ----------------------- -------- -------<br /> ▀ NUMBER</pre><br />Nice little black square.<br />Next level might be making some columns a bit like <em>hidden</em>.<br /><pre>SQL> create table persons (name varchar2(10), "SYSDATE" date);<br />Table created.<br />SQL> insert into persons values ('John', sysdate-1);<br />1 row created.<br />SQL> select sysdate from dual;<br />SYSDATE<br />---------<br />31-JAN-09<br />SQL> select name, sysdate from persons;<br />NAME SYSDATE<br />---------- ---------<br />John 31-JAN-09</pre><br />So where our column sysdate was gone? We inserted value sysdate-1 and got back the same sysdate? Although our column was in upper case, so our query column should be used? Naah, we should enclose it in quotes.<br /><pre>SQL> select name, "SYSDATE" from persons;<br />NAME SYSDATE<br />---------- ---------<br />John 30-JAN-09</pre><br />So next time when you are starting to think about table name like C12, numeric column names, column names in quotes or something like that, don't be shy! Use the complete power of obscurity, so making your db more secure. If you'd try hard enough, you'd reach perfection when obscurity is so great that nobody, even you, won't be able to understand anything ;)<br />And I wasn't even suggesting to replace DUAL in your schema ;)Gints Plivnahttp://www.blogger.com/profile/17716037729002528529noreply@blogger.com0tag:blogger.com,1999:blog-22727713.post-8968870522966968892009-01-09T00:52:00.002+02:002009-01-09T02:01:46.488+02:00<h3>About experience</h3><p></p><p>Just a few days ago <a href="http://watsonldsbc.blogspot.com/">Ryan Watson</a> mentioned <a href="http://www.gplivna.eu/papers/choose_database.htm">one of my articles</a> in <a href="http://watsonldsbc.blogspot.com/2009/01/what-to-consider-when-choosing-database.html">his blog post</a>. It really wouldn't be anything important to write about but the first comment by <a href="http://www.blogger.com/profile/01136664173076859679">Kevin</a> really amused me and the comment was "Who is this guy and why is he qualified to comment on databases."<br /><br />Blahhh... My answer was "[..] why does it matter and isn't better just to make reasonable pros and/or cons about the subject?" and Kevin answered with "Experts carry more weight than a novice; a senior Oracle admin with 20 years of experience has more experience to draw on. This is why a senior DBA will make $100 k and novice $20 k."<br /><br />This was something so immediate and direct statement that let me started to think more about this subject. How much experience means to me? How much it means to other people (virtually) around me? Probably we all know one of the brightest stars in this area good old Don with <a href="http://forums.oracle.com/forums/message.jspa?messageID=3197529#3197529">one of his latest performances here</a>, but undoubtely for all of us - if we don't know other people and they say something, their experience somehow matters. So how much does it matter for me?<br /><br />1. I definitely have positive experience with some people and know their knowledge and writing style. They have credit in my eyes and I read each their article/statement/whatever else with more caution than on average. Even if the beginning of the message is somehow blurry or overall quality is not that perfect this time. They have to "apply efforts" to their articles to make me angry and sceptic.<br />2. I definitely have negative experience with a few people and know their usually blurry and washy posts without any resultant technical value. They have really minus credits in my eyes and even if this time they have written something technically sound I'm quite suspect about that. They have to write something really, really good to attract my attention other than sigh "usual crap".<br />3. I definitely have neutral experience with quite a lot of people. Let's say "nothing special" :) I read their articles only when they are directly related to my particular current needs.<br />4. People whom I've met the first time or haven't made any particular impression. These are the most complex part. What is my attitude to them? Analyzing my usual behaviour the conclusion is - I start reading the article with some interest and few starting paragraphs should attract my attention. They must be interesting or suitable for my needs otherwise I jump to next article. BUT. I've never (OK almost never :) read the author's CV, last rows about the author how cool he is or whatever else describing his experience. The content of the article is what matters for me.<br /><br />So people I don't know win over people with negative credit (whose articles I usually even don't read), but lose to people with positive credit (whose articles I usually try to read until the and to find out something valuable).<br /><br />And returning to the initial comments - what does 20 years of experience mean to me? Most probably nothing. At least initially. 20 years ago there was Oracle 6, how does it help for today's work? Most probably in almost no way. I'd say that a few years for an inquiring and smart mind is enough to deserve more than a person with 20 years experience stalled in Oracle 8. Actually he wins without any doubt at all.<br /><br />The (quality of) content is what matters - this is the conclusion. <em>At least for people I don't know</em> :)<br /></p>Gints Plivnahttp://www.blogger.com/profile/17716037729002528529noreply@blogger.com3tag:blogger.com,1999:blog-22727713.post-21743326168029346732008-11-23T15:00:00.003+02:002008-11-23T16:33:10.283+02:00<h3>Advanced Oracle Troubleshooting by Tanel Poder..</h3><p>.. is over. <a href="http://blog.tanelpoder.com/seminar/">His seminar</a> in Riga was real fun for two days. It was different from the <em>big guys'</em> seminars I've attended before (for example, <a href="http://asktom.oracle.com/">Thomas Kyte</a> and <a href="http://jonathanlewis.wordpress.com/">Jonathan Lewis</a>). Not in the way that it was or wasn't somehow better or not, but in the way that Tanel spoke more about:</p><ul><li>internal memory structures (x$ tables), process stacks, Oracle kernel functions and their mapping to SQL statement execution plan steps;</li><li>understanding structure of library cache, process state objects, buffer cache;</li><li>dumping cursors, trace buffers, process stack;</li><li>systematic troubleshooting a session and finding the root cause of slowness and what it is doing now;</li><li>and many more subjects.</li></ul><p>Lots of live demos, nothing like an Oracle documentation rephrase, narrative explanation of quite advanced technical things - these were the main characteristics of this seminar. </p><p>Some previous understanding of Operating systems basic functions, commands and Oracle architecture however is a must, otherwise you simply will be overwhelmed by new information and won't be able to follow the main idea. On the other hand I'm absolutely not a sysadmin and/or <em>true</em> DBA, but I understood let's say 95% of the material provided ;)</p><p>So the conclusion is - I definitely recommend it for the people interested in these topics.</p>Gints Plivnahttp://www.blogger.com/profile/17716037729002528529noreply@blogger.com0tag:blogger.com,1999:blog-22727713.post-892111059086678212008-11-20T22:34:00.003+02:002008-11-21T00:11:16.371+02:00<h3>Data modeling</h3><p>Time after time it is worth to remember (or learn) basic theory. One of them, which is very important when creating new systems, is data modeling. I'm quite sure most of the DBMS people have seen one or another data model with tables, columns, primary and foreign keys. However the <a href="http://en.wikipedia.org/wiki/Data_modeling">theory of data modeling</a> says that this is only <strong>one</strong> level out of <strong>three</strong>. Unfortunately I've seen quite many cases when people have forgotten two another levels. So let's remember what they are.</p><h4>Conceptual data model</h4><p>Conceptual data model contains entities, relationships and attributes. One can draw them using different notations for example ER modeling or UML, but the idea remains the same - this model <strong>doesn't know anything</strong> about the underlying DBMS. This model <strong>is the same</strong> for Oracle, SQL Server, DB2, MySQL, whatever else. So why is it important? </p><ul><li>It speaks in business language and uses terms only from business users, not any programmer's or IT person's hacks.</li><li>It is normalized, every piece of information is stored only once.</li><li>It displays common structures and tries to aggregate them, it is easier to find them in such model.</li><li>It is readable (after a short introduction probably) by every sensible customer.</li><li>It doesn't contain any reference to any particular DBMS, so one can easily reuse it for ultimately any DBMS.</li></ul><p>So the question - have you used it? If not - why? Are you aware of <a href="http://www.gplivna.eu/papers/data_waste_or_data_base.htm">possible data waste dangers</a>?</p><h4>Logical data model</h4><p>This is the model people usually use. It contains tables, columns, primary keys, foreign keys, unique keys. Some characteristics of it are:</p><ul><li>It is created on the basis of conceptual data model, but it might not correspond 1:1 to it. For example some entities might be split, some unioned.</li><li>It may contain some programmer's hacks for example different flag columns for easier data selection.</li><li>It is aware of used DBMS. For example data types for columns <strong>are</strong> DBMS specific.</li><li>It may have denormalized columns (i.e. the same facts stored in more than one column) for performance reasons.</li><li>Table and column names might not exactly mimic business terms i.e. they might be abbreviated, changed to conform <a href="http://www.gplivna.eu/papers/naming_conventions.htm">some naming rules</a> and/or DBMS restrictions, although for easier readability it is worth to retain some dependency.</li><li>It is based on conceptual data model but created <strong>keeping performance and most common possible SQL statements</strong> in mind.</li></ul><p>I'm quite sure you have used it, so the only question is - have you created it keeping performance and business critical SQL in mind?</p><h4>Physical data model</h4><p>What's that? Is there anything remaining to model at all? Yes it is. Heap table (i.e. table type which is used by default) is not the only available table type in Oracle. Partitioning is not the only option, which can be used in Oracle. So what is modeled here?</p><ul><li><a href="http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_7002.htm">Table type</a> - heap, index organized, clustered, sorted hash cluster, single table hash cluster there are many possible options.</li><li>Partitioning, compression, encryption - do you need it, can you afford it?</li><li>Indexes - will you use only <a href="http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_5011.htm">b-tree or something more</a>?</li><li>Will you use object tables?</li><li>What tablespaces will be created and on what devices/files?</li><li>Here comes the real strength (or weakness) of your chosen DBMS - if it offers all these features - why not at least consider them?</li></ul><p>If you are DBA you have definitely used some of the features mentioned above, but have you thought about non-default ones? Have you systematically gone through at least the most business critical tables and considered what kind of table type to use, what kind of indexes create?</p><p>Not that I'm suggesting to enforce anything just for the sake of completeness ;) but I'm sure I've read something like that somewhere "If you have only heap tables and b-tree indexes, most probably your schema is not optimal".</p><h4>Conclusion</h4><p>Sometimes conceptual modeling is called logical and logical physical and then of course question remains how should be called the real "physical modeling", but it is not so important. The important thing is - whatever DBMS you choose for a new application it is worth to go through all the data model steps. Go through the conceptual modeling to better understand your requirements, to better confirm them with your customer, to better find out common patterns. Of course logical modeling cannot be avoided if we'd like to get at least one table, however quite many people forget about referential integrity constraints and rely only on application enforcing it. Sighhhh... And at last don't forget about physical modeling because when your table will contain (hundreds) millions of rows in production it would be much harder to change it's type, move it somewhere else etc. </p>Gints Plivnahttp://www.blogger.com/profile/17716037729002528529noreply@blogger.com0tag:blogger.com,1999:blog-22727713.post-43557782026275307442008-10-14T18:32:00.018+03:002008-10-19T21:41:03.553+03:00<h3>Mortgage calculator using SQL Model clause</h3><br />Recently we had <a href="http://datubazes.wordpress.com/2008/10/17/oracle-diena-2008-pagajusi/">local Latvian Oracle day conference</a>. This year it was quite big event with 5 parallel sessions and more than 400 participants. I also was one of the presenters telling about analytic functions and SQL Model clause. During the process of understanding deeper and better Oracle SQL Model clause I've tried make also some a bit complex examples myself. Nowadays the actual problem is mortgage and of course the possibility to pay monthly payments (fortunately not for me :) ) on the background of world wide financial crisis.<br />So here is fully functional mortgage calculator using SQL Model clause. There are two variants:<ul><li><a href="http://en.wikipedia.org/wiki/Adjustable_rate_mortgage">Adjustable rate mortgage</a> with variable payment each month and</li><br /><li><a href="http://en.wikipedia.org/wiki/Fixed_rate_mortgage">Fixed rate mortgage</a> with fixed monthly payment.</li></ul>I've used <a href="http://www.mtgprofessor.com/formulas.htm">this site</a> to get formula for fixed monthly payment required to fully amortize a loan over a term of fixed months at a fixed monthly interest rate.<br />OK and now the scripts.<pre><span style="background-color: silver; font-family:courier new;"><br />set ver off <br />set lines 120 <br />undefine rem_loan year_int_rate term <br />SELECT m+1 month, <br /> to_char(rem_loan, '99999999.00') rem_loan, <br /> to_char(loan_paid_tot, '99999999.00') loan_paid_tot,<br /> to_char(mon_int, '999999.00') mon_int, <br /> to_char(tot_int, '99999999.00') tot_int, <br /> to_char(mon_paym, '99999999.00') mon_paym, <br /> to_char(mon_paym_tot, '99999999.00') mon_paym_tot, <br /> to_char(grand_tot, '99999999.00') grand_tot <br />FROM dual<br />MODEL <br />DIMENSION BY (-1 m) <br />MEASURES (&&rem_loan rem_loan, <br /> round(&&rem_loan*&&year_int_rate/100/12,2) mon_int, <br /> ceil(&&rem_loan/&&term*100)/100 mon_paym, <br /> (&&rem_loan/&&term*100)/100 loan_paid_tot, <br /> round(&&rem_loan*&&year_int_rate/12/100,2) tot_int, <br /> ceil(&&rem_loan/&&term*100)/100 + round(&&rem_loan*&&year_int_rate/100/12,2) mon_paym_tot,<br /> ceil(&&rem_loan/&&term*100)/100 + round(&&rem_loan*&&year_int_rate/100/12,2) grand_tot <br />)<br />RULES ITERATE (&&term) UNTIL (round(loan_paid_tot[iteration_number], 2) = &&rem_loan) (<br /> rem_loan[iteration_number] = rem_loan[iteration_number -1] - mon_paym[iteration_number - 1], <br /> mon_int[iteration_number] = round(rem_loan[iteration_number]*&&year_int_rate/100/12,2), <br /> mon_paym[iteration_number] = least(ceil(&&rem_loan/&&term*100)/100, rem_loan[iteration_number]), <br /> loan_paid_tot[iteration_number] = loan_paid_tot[iteration_number - 1] + mon_paym[iteration_number],<br /> tot_int[iteration_number] = tot_int[iteration_number - 1] + mon_int[iteration_number], <br /> mon_paym_tot[iteration_number] = mon_paym[iteration_number] + mon_int[iteration_number], <br /> grand_tot[iteration_number] = grand_tot[iteration_number - 1] + mon_paym_tot[iteration_number]); <br /></span></pre><br />Result for loan of 1000 money units (lats, euros, dollars, pounds whatever) for 10 % year rate with term of 7 months looks as follows:<pre><span style="background-color: silver; font-family:courier new;"><br />Enter value for rem_loan: 1000<br />Enter value for year_int_rate: 10<br />Enter value for term: 7<br /><br /> MONTH REM_LOAN LOAN_PAID_TO MON_INT TOT_INT MON_PAYM MON_PAYM_TOT GRAND_TOT<br />---------- ------------ ------------ ---------- ------------ ------------ ------------ ------------<br /> 0 1000.00 142.86 8.33 8.33 142.86 151.19 151.19<br /> 1 857.14 285.72 7.14 15.47 142.86 150.00 301.19<br /> 2 714.28 428.58 5.95 21.42 142.86 148.81 450.00<br /> 3 571.42 571.44 4.76 26.18 142.86 147.62 597.62<br /> 4 428.56 714.30 3.57 29.75 142.86 146.43 744.05<br /> 5 285.70 857.16 2.38 32.13 142.86 145.24 889.29<br /> 6 142.84 1000.00 1.19 33.32 142.84 144.03 1033.32<br /></span></pre>For fixed payment each month the script is as follows:<pre><span style="background-color: silver; font-family:courier new;"><br />SELECT m+1 month, <br /> to_char(rem_loan, '99999999.00') rem_loan, <br /> to_char(loan_paid_tot, '99999999.00') loan_paid_tot,<br /> to_char(mon_int, '999999.00') mon_int, <br /> to_char(tot_int, '99999999.00') tot_int, <br /> to_char(mon_paym, '99999999.00') mon_paym, <br /> to_char(mon_paym_tot, '99999999.00') mon_paym_tot, <br /> to_char(grand_tot, '99999999.00') grand_tot <br />FROM dual<br />MODEL <br />DIMENSION BY (-1 m) <br />MEASURES (&&rem_loan rem_loan, <br /> round(&&rem_loan*&&year_int_rate/100/12,2) mon_int, <br /> ceil(&&rem_loan*(&&year_int_rate/100/12*POWER((1+&&year_int_rate/100/12),&&term))/<br /> (POWER((1+&&year_int_rate/100/12),&&term)-1)*100)/100 - <br /> round(&&rem_loan*&&year_int_rate/100/12,2) mon_paym, <br /> ceil(&&rem_loan*(&&year_int_rate/100/12*POWER((1+&&year_int_rate/100/12),&&term))/<br /> (POWER((1+&&year_int_rate/100/12),&&term)-1)*100)/100 - <br /> round(&&rem_loan*&&year_int_rate/100/12,2) loan_paid_tot, <br /> round(&&rem_loan*&&year_int_rate/12/100,2) tot_int, <br /> ceil(&&rem_loan*(&&year_int_rate/100/12*POWER((1+&&year_int_rate/100/12),&&term))/<br /> (POWER((1+&&year_int_rate/100/12),&&term)-1)*100)/100 mon_paym_tot, <br /> ceil(&&rem_loan*(&&year_int_rate/100/12*POWER((1+&&year_int_rate/100/12),&&term))/<br /> (POWER((1+&&year_int_rate/100/12),&&term)-1)*100)/100 grand_tot <br />)<br />RULES ITERATE (&&term) UNTIL (round(loan_paid_tot[iteration_number], 2) = &&rem_loan) ( <br /> rem_loan[iteration_number] = rem_loan[iteration_number -1] - mon_paym[iteration_number - 1], <br /> mon_int[iteration_number] = round(rem_loan[iteration_number]*&&year_int_rate/100/12,2), <br /> mon_paym_tot[iteration_number] = least( <br /> ceil(&&rem_loan*(&&year_int_rate/100/12*POWER((1+&&year_int_rate/100/12),&&term))/ <br /> (POWER((1+&&year_int_rate/100/12),&&term)-1)*100)/100, <br /> rem_loan[iteration_number] + mon_int[iteration_number]), <br /> mon_paym[iteration_number] = mon_paym_tot[iteration_number] - mon_int[iteration_number], <br /> loan_paid_tot[iteration_number] = loan_paid_tot[iteration_number - 1] + mon_paym[iteration_number],<br /> tot_int[iteration_number] = tot_int[iteration_number - 1] + mon_int[iteration_number], <br /> grand_tot[iteration_number] = grand_tot[iteration_number - 1] + mon_paym_tot[iteration_number]); <br /></span></pre>Result for the same loan of 1000 money units (lats, euros, dollars, pounds whatever) for 10 % year rate with term of 7 months looks as follows:<pre><span style="background-color: silver; font-family:courier new;"><br /> MONTH REM_LOAN LOAN_PAID_TO MON_INT TOT_INT MON_PAYM MON_PAYM_TOT GRAND_TOT<br />---------- ------------ ------------ ---------- ------------ ------------ ------------ ------------<br /> 0 1000.00 139.33 8.33 8.33 139.33 147.66 147.66<br /> 1 860.67 279.82 7.17 15.50 140.49 147.66 295.32<br /> 2 720.18 421.48 6.00 21.50 141.66 147.66 442.98<br /> 3 578.52 564.32 4.82 26.32 142.84 147.66 590.64<br /> 4 435.68 708.35 3.63 29.95 144.03 147.66 738.30<br /> 5 291.65 853.58 2.43 32.38 145.23 147.66 885.96<br /> 6 146.42 1000.00 1.22 33.60 146.42 147.64 1033.60<br /></span></pre>During my research I've found invaluable these resources:<ul><li><a href="http://download.oracle.com/docs/cd/B19306_01/server.102/b14223/sqlmodel.htm">Oracle official documentation</a>;</li><br /><li><a href="http://rwijk.blogspot.com/">Rob van Wijk blog</a>;</li><br /><li><a href="http://volder-notes.blogspot.com/">Volder blog</a>.</li></ul>Gints Plivnahttp://www.blogger.com/profile/17716037729002528529noreply@blogger.com13tag:blogger.com,1999:blog-22727713.post-16958258121498462152008-07-14T23:42:00.002+03:002008-07-15T00:02:11.546+03:00<h3>One of 364 000 articles</h3><br /><br />According to google I've written another one of ~364 000 articles about <a href="http://www.gplivna.eu/papers/sql_join_types.htm">SQL join types</a>. So the question is why anyone should read it?<br />Here are a few reasons:<br /><ul><br /><li>It is 25 pages long;</li><br /><li>It contains join types metamodel or at least my attempt to create one;</li><br /><li>It tries to describe what are different join types and how they relate to each other;</li><br /><li>It contains visual pictures of cross, inner, and left, right, full outer joins and this time <a href="http://www.codinghorror.com/blog/archives/000976.html">not with Venn diagramms</a>, which I personally think are suitable for absolutely different operations (set operations);</li><br /><li>To describe theory it contains 44 examples, which are tested on Oracle, SQL Server and MySQL;</li><br /><li>It contains several links to other resources.</li><br /></ul><br />Because it is 25 pages I've decided not to put it here but <a href="http://www.gplivna.eu/papers/sql_join_types.htm">it is on my website</a>.<br /><br />I have one wish though - if You are reading this article and have access to another DBMS (than already mentioned in article i.e. Oracle, SQL Server, MySQL) and have a bit free time, please run examples on your DBMS and post results either here or send me to mail <a href="mailto:gints.plivna@gmail.com">gints.plivna@gmail.com</a>. Then I will put them in original article along with contributor's name.Gints Plivnahttp://www.blogger.com/profile/17716037729002528529noreply@blogger.com1tag:blogger.com,1999:blog-22727713.post-66465275802576577112008-07-05T16:28:00.008+03:002008-07-05T20:40:27.448+03:00<h3>Latvian Nationwide Song and Dance Celebration in Riga</h3>Today is the beginning of 24th song festival - unique celebration of choral songs, which started 135 years ago with the first song festival. In these times under the rule of Russian Czar, later they continued during the short freedom period as well as even in Soviet times. This year about 35 000 singers and dancers will come to Riga to sing and dance in various concerts. This means more than 2% of all Latvians are coming together to sing and dance and many many more are coming to see them on the concerts or via TV.<br /><h4>Brief history</h4>The first All Latvian Song Festival took place in Riga, year 1873. There were 1003 singers in these times. Under the Russian Czar there were 5 festivals. During the Latvian independence there were 4 festivals and participant count raised to more than 14 000 people. During the Soviet times these festivals partially become the weapon of Soviet propaganda, however these were like two-edged sword, for example, the 100th anniversary of song festival in 1973 was some kind of emotional uprising against Soviet regime. Since 1990 song festivals are held in independent state again and the participant count has raised to impressive more than 2% of all Latvians. The learning process of songs and dances is continued all the time and it is worth to mention that noone pays a single santīms (centime) to participants both for preparation process lasting a few years and also the very festival, although of course there is governmental material support for the organizational purposes of the festival.<br /><h4>Events</h4><p>During the festival there are many concerts and other kind of events. I'll mention a few of them. The first is participant parade. In the picture below one can look at one of the best choirs in the world "Kamēr", which has won many international awards and also takes part in this festival.<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgqAakvf7IhLMQ1b0pPft9WDgZ-i4YOLynlSbc_9spo_R9FazyE5fjryKNByLE0GkCjEeGDk7ez_dbQjyIhxgIxcABef9eTq78RSXb4kC65ROAXw4ifad-HSsDKon9u8sx0rCqO/s1600-h/043.jpg"><img id="BLOGGER_PHOTO_ID_5219530533512502450" style="CURSOR: hand" alt="Latvian Song Festival choir Kamēr" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgqAakvf7IhLMQ1b0pPft9WDgZ-i4YOLynlSbc_9spo_R9FazyE5fjryKNByLE0GkCjEeGDk7ez_dbQjyIhxgIxcABef9eTq78RSXb4kC65ROAXw4ifad-HSsDKon9u8sx0rCqO/s400/043.jpg" border="0" /></a><br />The second one is main dance concert. In recent times more than 10 000 dancers are taking part in it.<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiDcio9-qElBei00XtiilEQ4gDmwDEnf88F4eYqg6izlpzm7MS4nwR7F69QL_AkspcLvZFruUNnRbaqxbpU-3DHVGP4kplEJB-gfLXevFvLYVUYqsBQaLhonBK3gO9vRbCtf36m/s1600-h/083.jpg"><img id="BLOGGER_PHOTO_ID_5219532220289135282" style="CURSOR: hand" alt="Latvian Song and Dance Festival" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiDcio9-qElBei00XtiilEQ4gDmwDEnf88F4eYqg6izlpzm7MS4nwR7F69QL_AkspcLvZFruUNnRbaqxbpU-3DHVGP4kplEJB-gfLXevFvLYVUYqsBQaLhonBK3gO9vRbCtf36m/s400/083.jpg" border="0" /></a><br />The last and biggest event is the final concert with all the singers in one big choir. It is very impressive moment.<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgix6zyNTcp8r3DA-aAL7WXlo3BH5ueAQW5WN_g68IkLwR1uN7lnnx2O4rtTA9QJv5Q1pII5lRqiXphJtvpEEZID3PVU4cMRYp1ZsDcuczWvnA-XaXqNieRDt3cXbO_wn2L7plD/s1600-h/027.jpg"><img id="BLOGGER_PHOTO_ID_5219533514614970114" style="CURSOR: hand" alt="Latvian Song and Dance Festival" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgix6zyNTcp8r3DA-aAL7WXlo3BH5ueAQW5WN_g68IkLwR1uN7lnnx2O4rtTA9QJv5Q1pII5lRqiXphJtvpEEZID3PVU4cMRYp1ZsDcuczWvnA-XaXqNieRDt3cXbO_wn2L7plD/s400/027.jpg" border="0" /></a> </p><p><h4>Some links</h4><p></p><ul><li><a href="http://www.youtube.com/watch?v=WDhbvfvMd34">Gaismas pils (<em>Castle of Light</em>)</a> - one of the favourite Song Festival songs of all time, one of the symbols of resistance against oppressors</li><li>The official website of <a href="http://www.dziesmusvetki2008.lv/index.php?&99">Latvian Nationwide Song and Dance Celebration in Riga, July 5-12, 2008</a> </li><li><a href="http://flickr.com/photos/28241716@N04/sets/">Photo gallery of the current festival</a> </li><li><a href="http://www.dziesmusvetki2008.lv/index.php?&85">History of Song Festivals</a><br /></li></ul>Gints Plivnahttp://www.blogger.com/profile/17716037729002528529noreply@blogger.com2tag:blogger.com,1999:blog-22727713.post-43043314030879322242008-06-27T15:55:00.054+03:002008-07-02T01:10:49.689+03:00<h3>Trip to Estonia</h3><br />During my holidays I've been in a 4 days trip to Estonia. For Oracle adepts this is the country where <a href="http://blog.tanelpoder.com/">Tanel Poder</a> comes from ;)<br />The trip was made up by myself and my wife, during it we drove ~1700 km. The general idea was to drive around all Estonia (it is not very big country) and look at some (hopefully) most interesting places. Here are 26 most interesting photos:<br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhrejJu4npv7wxY4Ez1UblULz-_wCp_JGLyn5wZnBigABZVIYa9BhIWPSupjm5Ox0_o2as5Nsa-z_LZRHWgUa_f8f-ByHDYi299G-QZuCGpE3HxqCNQ8AxTTmlr9J2HQlvzrTV_/s1600-h/01_munamagi.jpg" target="_blank"><img style="float:left; margin:10px 10px 10px 10px;cursor:pointer; cursor:hand;" alt="A view from Munamagi hill" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhrejJu4npv7wxY4Ez1UblULz-_wCp_JGLyn5wZnBigABZVIYa9BhIWPSupjm5Ox0_o2as5Nsa-z_LZRHWgUa_f8f-ByHDYi299G-QZuCGpE3HxqCNQ8AxTTmlr9J2HQlvzrTV_/s400/01_munamagi.jpg" width="300" border="0" /></a><br />A view from Munamagi hill, the highest point in Baltics<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgOQC1qkaZw8P0dHrB3ICMPDzwFJlK30CqXIEf29OwCYfkrTWvdxUfBv1l_4PVa_n004HFMIBVUPdsF8KrXOdO4T19hrI_q0UusPXMg4Z01E6OKClyHdQBUfVfR2-bGoabSDahC/s1600-h/02_tartu_cathedral.jpg" target="_blank"><br /><img style="float:left; margin:10px 10px 10px 10px;cursor:pointer; cursor:hand;" alt="Tartu Dom Cathedral" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgOQC1qkaZw8P0dHrB3ICMPDzwFJlK30CqXIEf29OwCYfkrTWvdxUfBv1l_4PVa_n004HFMIBVUPdsF8KrXOdO4T19hrI_q0UusPXMg4Z01E6OKClyHdQBUfVfR2-bGoabSDahC/s400/02_tartu_cathedral.jpg" width="300" align="middle" border="0" /></a><br />Tartu Dom Cathedral, it is being rebuilt now<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhrLxqdqcxBmzjngEfTCAJo-EtC19KlhG2BRY_HaLdCbbYqCeDlj1fXb_KV_JPkicT0QTJEe6RBGS8A7m4njEklOyWcwFDvv-FX9m1jh9z15cWl5k9VdEIz6CUz6c2WDfMnjhyb/s1600-h/03_tartu_monument.jpg" target="_blank"><br /><img style="float:left; margin:10px 10px 10px 10px;cursor:pointer; cursor:hand;" alt="Tartu monument" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhrLxqdqcxBmzjngEfTCAJo-EtC19KlhG2BRY_HaLdCbbYqCeDlj1fXb_KV_JPkicT0QTJEe6RBGS8A7m4njEklOyWcwFDvv-FX9m1jh9z15cWl5k9VdEIz6CUz6c2WDfMnjhyb/s400/03_tartu_monument.jpg" width="300" align="middle" border="0" /></a><br />Interesting fountain in Tartu, second largest Estonian city<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh1rTKIxp4bPMXvelE_7km2Pkhu0Uj06N_eHA2QrxPhAS8B8ncOg8bFfoQJ3jFiL4YPwSqaS01Og6RaAahCoyKPbmb_SucZ2eJuRBHbgQNKuQmCPbCD2mP-gdL22oNrsZiveGg_/s1600-h/04_rainbow_over_peipsi.jpg" target="_blank"><br /><img style="float:left; margin:10px 10px 10px 10px;cursor:pointer; cursor:hand;" alt="Rainbow over Peipsi lake" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh1rTKIxp4bPMXvelE_7km2Pkhu0Uj06N_eHA2QrxPhAS8B8ncOg8bFfoQJ3jFiL4YPwSqaS01Og6RaAahCoyKPbmb_SucZ2eJuRBHbgQNKuQmCPbCD2mP-gdL22oNrsZiveGg_/s400/04_rainbow_over_peipsi.jpg" width="300" align="middle" border="0" /></a><br />Rainbow over Peipsi lake<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj_AXUruyVvty-iGEeMj-gzN0AZFPTVbm-JzeyqsYQuLO8VzRqIfZn-CJYBKo55h8Q3S423cKccpf3oYQXORWKVSIr7M4h06UoTANlH4EmkPZXztuUf4MhvD6kvulAKhqUNo1FD/s1600-h/05_street_at_peipsi.jpg" target="_blank"><br /><img style="float:left; margin:10px 10px 10px 10px;cursor:pointer; cursor:hand;" alt="Street at Peipsi" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj_AXUruyVvty-iGEeMj-gzN0AZFPTVbm-JzeyqsYQuLO8VzRqIfZn-CJYBKo55h8Q3S423cKccpf3oYQXORWKVSIr7M4h06UoTANlH4EmkPZXztuUf4MhvD6kvulAKhqUNo1FD/s400/05_street_at_peipsi.jpg" width="300" align="middle" border="0" /></a><br />There is 8 km long street through the old villages along the Peipsi lake. It looks like that all lengthways<br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEja1skrN3SnPiX-jmyxyp8er8nJfUuLY15-7D-WYYI7PAe1y6Qpl206s27Y5AzNvAgX9VUBC2LZM3yq82Sl5hG0bu0xurvG9ky_ufDpDdHaoZEdkabkDhMbPSxymKctLsDNDvz0/s1600-h/06_ivangorod_castle.jpg" target="_blank"><br /><img style="float:left; margin:10px 10px 10px 10px;cursor:pointer; cursor:hand;" alt="Ivangorod castle" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEja1skrN3SnPiX-jmyxyp8er8nJfUuLY15-7D-WYYI7PAe1y6Qpl206s27Y5AzNvAgX9VUBC2LZM3yq82Sl5hG0bu0xurvG9ky_ufDpDdHaoZEdkabkDhMbPSxymKctLsDNDvz0/s400/06_ivangorod_castle.jpg" width="300" align="middle" border="0" /></a><br />Ivangorod castle over the river in Russia. This castle is just in the opposite side of Narva river from the Narva castle<br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEikIA6nWAHFqhHAsLXO_QpeV-IA24TkO3lrVVr0dA9MyslY_Xi5YjArtCYZ1Jumxe0Jh46wJz0kaste9T930ZKayKeuLbDTE9VNpX7j2BPiP76TVve-Mj92Bx5k4YXYujDMQIaS/s1600-h/07_narva_castle.jpg" target="_blank"><br /><img style="float:left; margin:10px 10px 10px 10px;cursor:pointer; cursor:hand;" alt="Narva castle" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEikIA6nWAHFqhHAsLXO_QpeV-IA24TkO3lrVVr0dA9MyslY_Xi5YjArtCYZ1Jumxe0Jh46wJz0kaste9T930ZKayKeuLbDTE9VNpX7j2BPiP76TVve-Mj92Bx5k4YXYujDMQIaS/s400/07_narva_castle.jpg" width="300" align="middle" border="0" /></a><br />Narva castle. This castle is just in the opposite side of Narva river from the Ivangorod castle<br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjMbgpmudYEGs6Z1YfXqy34c5TS85pUqznojSXNnZ1Rv8Tjf230vm9DmqX-TFt22mDBabcnr0jFINI_z_eBKlklkozr-B4Pp2ls_agwwqaKpsa5Du4PRJE4zMMYJDZm0T3kwjC0/s1600-h/08_Aija_bells_in_narva_castle.jpg" target="_blank"><br /><img style="float:left; margin:10px 10px 10px 10px;cursor:pointer; cursor:hand;" alt="Bells in Narva castle" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjMbgpmudYEGs6Z1YfXqy34c5TS85pUqznojSXNnZ1Rv8Tjf230vm9DmqX-TFt22mDBabcnr0jFINI_z_eBKlklkozr-B4Pp2ls_agwwqaKpsa5Du4PRJE4zMMYJDZm0T3kwjC0/s400/08_Aija_bells_in_narva_castle.jpg" width="300" align="middle" border="0" /></a><br />Aija along with bells in Narva castle<br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjXlu5iIUXBpcE7Z9t01vetndl4ckzE0UAPYeUwglW9gkfREchDHQBgEXdGKX9Nb7l-NoKSkPSFJk1ZGAxUG14XVS4xQD5U7-2OWPC99Tjz-mkFqTqfjmS9OelaZOQdB9XmlqLJ/s1600-h/09_estonia_northern_coast.jpg" target="_blank"><br /><img style="float:left; margin:10px 10px 10px 10px;cursor:pointer; cursor:hand;" alt="Estonia northern coast" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjXlu5iIUXBpcE7Z9t01vetndl4ckzE0UAPYeUwglW9gkfREchDHQBgEXdGKX9Nb7l-NoKSkPSFJk1ZGAxUG14XVS4xQD5U7-2OWPC99Tjz-mkFqTqfjmS9OelaZOQdB9XmlqLJ/s400/09_estonia_northern_coast.jpg" width="300" align="middle" border="0" /></a><br />Northern coast (Gulf of Finland) mostly consists of limestone rocks up to 56 metres high<br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh1zSM2lSNuPH6XzF3kDfFJXiUkru4R0ZpcPVYjEVnezXLrFmA_YcwMAXEJcPNzhDZDV3gF0b2eC6e7djgCzA0o3sAvQ08O1KbedAy-LNf1Mu6OCa8_B4mbZYRNYzreFrbCMS9p/s1600-h/10_sunset_over_finnish_bay.jpg" target="_blank"><br /><img style="float:left; margin:10px 10px 10px 10px;cursor:pointer; cursor:hand;" alt="Sunset over Gulf of Finland" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh1zSM2lSNuPH6XzF3kDfFJXiUkru4R0ZpcPVYjEVnezXLrFmA_YcwMAXEJcPNzhDZDV3gF0b2eC6e7djgCzA0o3sAvQ08O1KbedAy-LNf1Mu6OCa8_B4mbZYRNYzreFrbCMS9p/s400/10_sunset_over_finnish_bay.jpg" width="300" align="middle" border="0" /></a><br />Sunset over Gulf of Finland<br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEivwSGJ2N7-o9O81ad3cc2mBmkI2G-br0ZcQKVNPUpjf0aCMGApojyhowxLLbRoupsZZ94qUMhIFoee9CZSnX3N-8lY9SsiKrwpC3O8xxkKvMr_kEj5aeZCTWZSqmPnk2ELn_LJ/s1600-h/11_valaste_waterfall.jpg" target="_blank"><br /><img style="float:left; margin:10px 10px 10px 10px;cursor:pointer; cursor:hand;" alt="Valaste waterfall" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEivwSGJ2N7-o9O81ad3cc2mBmkI2G-br0ZcQKVNPUpjf0aCMGApojyhowxLLbRoupsZZ94qUMhIFoee9CZSnX3N-8lY9SsiKrwpC3O8xxkKvMr_kEj5aeZCTWZSqmPnk2ELn_LJ/s400/11_valaste_waterfall.jpg" width="300" align="middle" border="0" /></a><br />Valaste waterfall is the highest waterfall of Estonia<br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjhXC9fWKrJ7-6bca9xIOkvHoEr8lBkwCmqX6xUS7lizt7mDpFViQaUpFogBOByQ0o0rqc6m4x7WF6RnmxapaYTJvNAQk5uSMgFwYY76zRqXMNU-URdVN7iC5grmdkzYC218tMY/s1600-h/12_kivioli_hill.jpg" target="_blank"><br /><img style="float:left; margin:10px 10px 10px 10px;cursor:pointer; cursor:hand;" alt="Kivioli hills" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjhXC9fWKrJ7-6bca9xIOkvHoEr8lBkwCmqX6xUS7lizt7mDpFViQaUpFogBOByQ0o0rqc6m4x7WF6RnmxapaYTJvNAQk5uSMgFwYY76zRqXMNU-URdVN7iC5grmdkzYC218tMY/s400/12_kivioli_hill.jpg" width="300" align="middle" border="0" /></a><br />Estonia has some kind of oil shale, this hill is man-made and consists of slag of oil shales. It is 116 metres high. The picture was taken from a bit lower hill of the same origin, just older one.<br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh6mFW2Oo5Pj8s3ucTkdVRfiumO_zgBVMDJ3dSe9ZxikLPSo2rvYBFVKaLteqqudFmJW0CYlQNhayCna4yEyTQdIxGzK-DiHifcSuh7Oi9h14fQVQj7gwYNwnn4uz3sRqz2I_xP/s1600-h/13_rakvere_symbol.jpg" target="_blank"><br /><img style="float:left; margin:10px 10px 10px 10px;cursor:pointer; cursor:hand;" alt="Symbol of Rakvere town" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh6mFW2Oo5Pj8s3ucTkdVRfiumO_zgBVMDJ3dSe9ZxikLPSo2rvYBFVKaLteqqudFmJW0CYlQNhayCna4yEyTQdIxGzK-DiHifcSuh7Oi9h14fQVQj7gwYNwnn4uz3sRqz2I_xP/s400/13_rakvere_symbol.jpg" width="300" align="middle" border="0" /></a><br />Symbol of Rakvere town<br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiSzaqRVlxMxBaHP4r74F0kkTuk3lahoSvHPoZJDhN3lZK5blF56pIojueagtSzp5wh7EdX8U_YjyaHtjru-WWKeKkKUlAm3tJfOgl5wBwoKPZydyEpLqcpfPAc_hY9ZDQQVp5j/s1600-h/14_bikes_at_palmse_manor.jpg" target="_blank"><br /><img style="float:left; margin:10px 10px 10px 10px;cursor:pointer; cursor:hand;" alt="Bikes at Palmse manor" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiSzaqRVlxMxBaHP4r74F0kkTuk3lahoSvHPoZJDhN3lZK5blF56pIojueagtSzp5wh7EdX8U_YjyaHtjru-WWKeKkKUlAm3tJfOgl5wBwoKPZydyEpLqcpfPAc_hY9ZDQQVp5j/s400/14_bikes_at_palmse_manor.jpg" width="300" align="middle" border="0" /></a><br />Bikes at Palmse manor, which is in the very centre of Lahemaa national park, a beautiful sightseeing place<br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgol4fRVcFqFtcH4a84Fv_IONGBIyYlI5wcaalSsJGtKHMfTOKfwFWSN2zcxdsLzODVtM84TVFBs_Zkr9N-rhgqiqEvRyKO9-tEEAb52KRYTd9s2K1nBZV_5oqR8AlyIRyeY65J/s1600-h/15_jagala_waterfall.jpg" target="_blank"><br /><img style="float:left; margin:10px 10px 10px 10px;cursor:pointer; cursor:hand;" alt="Jagala waterfall" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgol4fRVcFqFtcH4a84Fv_IONGBIyYlI5wcaalSsJGtKHMfTOKfwFWSN2zcxdsLzODVtM84TVFBs_Zkr9N-rhgqiqEvRyKO9-tEEAb52KRYTd9s2K1nBZV_5oqR8AlyIRyeY65J/s400/15_jagala_waterfall.jpg" width="300" align="middle" border="0" /></a><br />Jagala waterfall near from Tallinn<br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhkRK1K-7MTb1yY-t0mgDETWZPi5iQ2YIr-jmN5EAXnv6Lnw4LvSWj7HUdmcnRYiZeGVfb8GHZOBJ8zIjVO0yN8YH51H0IUEHlS0DvhRHAPIs4uyK0Nlas13IV2HESUXDwhaYfc/s1600-h/16_tallinn_old_town_towers.jpg" target="_blank"><br /><img style="float:left; margin:10px 10px 10px 10px;cursor:pointer; cursor:hand;" alt="Tallinn city wall" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhkRK1K-7MTb1yY-t0mgDETWZPi5iQ2YIr-jmN5EAXnv6Lnw4LvSWj7HUdmcnRYiZeGVfb8GHZOBJ8zIjVO0yN8YH51H0IUEHlS0DvhRHAPIs4uyK0Nlas13IV2HESUXDwhaYfc/s400/16_tallinn_old_town_towers.jpg" width="300" align="middle" border="0" /></a><br />Tallinn has more than 2km long old city wall with many towers and everything in very well preserved<br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEikkSTyX-CAt7jxRgS7zeex4eZ0oUzheYbnBd9ypKmIUFkU7y4BSBXmVGv8I4crbrvnQkTez7AnvuFXWjsVUSTSLOdCfw-vhY_p9K-lEdgEnmRpr63_FOVnFRUGQGIPwgO2SLvW/s1600-h/17_tallinn_old_town.jpg" target="_blank"><br /><img style="float:left; margin:10px 10px 10px 10px;cursor:pointer; cursor:hand;" alt="Tallinn old town" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEikkSTyX-CAt7jxRgS7zeex4eZ0oUzheYbnBd9ypKmIUFkU7y4BSBXmVGv8I4crbrvnQkTez7AnvuFXWjsVUSTSLOdCfw-vhY_p9K-lEdgEnmRpr63_FOVnFRUGQGIPwgO2SLvW/s400/17_tallinn_old_town.jpg" width="300" align="middle" border="0" /></a><br />Tallinn old town<br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhKQkXu_V26JFYyIsHsU-F3FILLeMAxNQnzHOXTnPU0lEGZeSx5wKFGsW7cnoRpVfwMy6qIOZav9sZrf1NXvmzrTt27bagAoVEsgFFc64iyEXs2c8QLl80OhSnidMCIP-NgqDm1/s1600-h/18_ferries_to_saaremaa.jpg" target="_blank"><br /><img style="float:left; margin:10px 10px 10px 10px;cursor:pointer; cursor:hand;" alt="Ferries to Saaremaa" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhKQkXu_V26JFYyIsHsU-F3FILLeMAxNQnzHOXTnPU0lEGZeSx5wKFGsW7cnoRpVfwMy6qIOZav9sZrf1NXvmzrTt27bagAoVEsgFFc64iyEXs2c8QLl80OhSnidMCIP-NgqDm1/s400/18_ferries_to_saaremaa.jpg" width="300" align="middle" border="0" /></a><br />There was a bit rainy when we crossed over to Saaremaa island (actually Muhu island, which is connected to Saaremaa by dam). The sail is very short just ~20 minutes.<br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgxYCmjgHuKzas-lWBHpkZf_a4ax35T8e9zeAHsqcN8ArNyP0BxrXplcuU92632_tNG6sUHL-1eAgxzXimBvDBTa7STut8WBJHLgj2Z4P9cIen8voSA2wbmmi1PaTd4aefPCPQE/s1600-h/19_13th_century_paintings_in_liiva_church.jpg" target="_blank"><br /><img style="float:left; margin:10px 10px 10px 10px;cursor:pointer; cursor:hand;" alt="Liiva church" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgxYCmjgHuKzas-lWBHpkZf_a4ax35T8e9zeAHsqcN8ArNyP0BxrXplcuU92632_tNG6sUHL-1eAgxzXimBvDBTa7STut8WBJHLgj2Z4P9cIen8voSA2wbmmi1PaTd4aefPCPQE/s400/19_13th_century_paintings_in_liiva_church.jpg" width="300" align="middle" border="0" /></a><br />13th century painting in Liiva church, Muhu island<br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEie7hT2IdUTC5chwwwS3NZlXDX5H0ZIpvob9au_kpQCFrgwD74TmQW9bKAA8xw18nG7E-4kXxEk6CTaNkS9e53QFxJK2ZDu9FOEgZ2-2BKFYFYsuzGi58s68SF7cUjYpDFWi-E1/s1600-h/20_maasilinn_stronghold.jpg" target="_blank"><br /><img style="float:left; margin:10px 10px 10px 10px;cursor:pointer; cursor:hand;" alt="Maasilinn stronghold" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEie7hT2IdUTC5chwwwS3NZlXDX5H0ZIpvob9au_kpQCFrgwD74TmQW9bKAA8xw18nG7E-4kXxEk6CTaNkS9e53QFxJK2ZDu9FOEgZ2-2BKFYFYsuzGi58s68SF7cUjYpDFWi-E1/s400/20_maasilinn_stronghold.jpg" width="300" align="middle" border="0" /></a><br />Maasilinn stronghold in Saaremaa<br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjUh6NSRA1sFlaUEe3LwScTJoXCOkmOcjmVz0UW7ji6FsRQ381EsfCW-djkaVx7Uzp5mS6vC1MNFs4scORTNuk7mYSXNPCWjgc1oSEZ2mhSvg40UEwLZVwCNeSrGdxT9rXrsSLW/s1600-h/21_angla_windmill.jpg" target="_blank"><br /><img style="float:left; margin:10px 10px 10px 10px;cursor:pointer; cursor:hand;" alt="Angla windmills" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjUh6NSRA1sFlaUEe3LwScTJoXCOkmOcjmVz0UW7ji6FsRQ381EsfCW-djkaVx7Uzp5mS6vC1MNFs4scORTNuk7mYSXNPCWjgc1oSEZ2mhSvg40UEwLZVwCNeSrGdxT9rXrsSLW/s400/21_angla_windmill.jpg" width="300" align="middle" border="0" /></a><br />Angla windmills. There were multitude of windmills in Saremaa (~800) these are just a few of forever gone old might.<br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi0o9cxrRBjf7HxK9mk1idKO_HijcrYBWCr9-DP2ySkV369O-Q9Tzvaqu9UCJ0xTexgtiFAxd6Vf52sLHGgcadhOQjkhN_ZKXXsOXFUYvVtJv0vhkzEePPVZnZCBFPLmX_1E82q/s1600-h/22_kaali_meteoritic_lake.jpg" target="_blank"><br /><img style="float:left; margin:10px 10px 10px 10px;cursor:pointer; cursor:hand;" alt="Kaali meteoritic lake" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi0o9cxrRBjf7HxK9mk1idKO_HijcrYBWCr9-DP2ySkV369O-Q9Tzvaqu9UCJ0xTexgtiFAxd6Vf52sLHGgcadhOQjkhN_ZKXXsOXFUYvVtJv0vhkzEePPVZnZCBFPLmX_1E82q/s400/22_kaali_meteoritic_lake.jpg" width="300" align="middle" border="0" /></a><br />It is very easy to get to this Kaali meteoritic lake, it is just 17 km from Kuressaare, centre of Saaremaa<br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjr2GSw4aBCgqMvPxQuROvckp4ZywcBVatZCpAuLhUFWXcV1cwx5IVNMukzbzo1P09UchbBSt2hdJpi-XN21UObF1DaPWyNlpyDPOfDx-kMaTbQ8fuelOJTCzeALzbLopRN6JVa/s1600-h/23_passage_in_kurressaare_castle.jpg" target="_blank"><br /><img style="float:left; margin:10px 10px 10px 10px;cursor:pointer; cursor:hand;" alt="Kuressaare castle" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjr2GSw4aBCgqMvPxQuROvckp4ZywcBVatZCpAuLhUFWXcV1cwx5IVNMukzbzo1P09UchbBSt2hdJpi-XN21UObF1DaPWyNlpyDPOfDx-kMaTbQ8fuelOJTCzeALzbLopRN6JVa/s400/23_passage_in_kurressaare_castle.jpg" width="300" align="middle" border="0" /></a><br />A passage in Kuressaare castle, Saremaa<br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhYhjRoRoTNAQb1y0qJ6l1Qqt81yXG6xOS_45RkhcvzSV-28rfLrWCpZ5biFXFSw1LQQOApsNEPzWU_GNTTtZLTCyHMkP0-TmNgpwClXwqu54gKye2Jcevm3Fh8tSJDwTSVPcgU/s1600-h/24_middle_age_toilett.jpg" target="_blank"><br /><img style="float:left; margin:10px 10px 10px 10px;cursor:pointer; cursor:hand;" alt="Toilett of Middle Ages" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhYhjRoRoTNAQb1y0qJ6l1Qqt81yXG6xOS_45RkhcvzSV-28rfLrWCpZ5biFXFSw1LQQOApsNEPzWU_GNTTtZLTCyHMkP0-TmNgpwClXwqu54gKye2Jcevm3Fh8tSJDwTSVPcgU/s400/24_middle_age_toilett.jpg" width="300" align="middle" border="0" /></a><br />Toilett of Middle Ages in Kuressaare castle, Saremaa<br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh9l5AgaNZUlTMxt6iFpmt8550fQzcHInWnlw2RdGzjLPSRSXCg6bZq3AM3A5cDcVW8tAjRsezd7eQK1Or3g-yk56ZLOuGN6OtNtUiptT61keJx58pwR9CsFxQVovmrZ4Arqyve/s1600-h/25_kihelkonna_church_inside.jpg" target="_blank"><br /><img style="float:left; margin:10px 10px 10px 10px;cursor:pointer; cursor:hand;" alt="Kihelkonna church" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh9l5AgaNZUlTMxt6iFpmt8550fQzcHInWnlw2RdGzjLPSRSXCg6bZq3AM3A5cDcVW8tAjRsezd7eQK1Or3g-yk56ZLOuGN6OtNtUiptT61keJx58pwR9CsFxQVovmrZ4Arqyve/s400/25_kihelkonna_church_inside.jpg" width="300" align="middle" border="0" /></a><br />Organ of Kihelkonna church, Saremaa<br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgzmm5drU4J15RsfJJ3uOpLo3BVbWx3e2Q5sZGgHqCX0HAQ17j7tbbGnV4DH7AoQcfBb2YPBDrszKuEMudqpuB-twVNiznbVZvby5z_oQCqAKF_VPrK0Tfqj9tABYr-vrSclupR/s1600-h/26_kihelkonna_church_outside.jpg" target="_blank"><br /><img style="float:left; margin:10px 10px 10px 10px;cursor:pointer; cursor:hand;" alt="Kihelkonna church" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgzmm5drU4J15RsfJJ3uOpLo3BVbWx3e2Q5sZGgHqCX0HAQ17j7tbbGnV4DH7AoQcfBb2YPBDrszKuEMudqpuB-twVNiznbVZvby5z_oQCqAKF_VPrK0Tfqj9tABYr-vrSclupR/s400/26_kihelkonna_church_outside.jpg" width="300" align="middle" border="0" /></a><br />Kihelkonna church, Saremaa. It was possible to climb up to the tower and look around through the windows<br /><table width=100%><tr><td><br />If you enjoyed these you can look also at <b><a href="http://gplivna.blogspot.com/2008/01/some-photos-ive-been-tagged-by-apc.html">some of my photos from Latvia</a></b>.<br /></td></tr></table>Gints Plivnahttp://www.blogger.com/profile/17716037729002528529noreply@blogger.com4tag:blogger.com,1999:blog-22727713.post-77266630115130292532008-06-26T14:20:00.002+03:002008-06-26T14:55:00.897+03:00<h3>New improved price list</h3><p>Obviously fluctuations of $ is affecting also Oracle ;) and as a result we have new global price list. Changes have affected also all Oracle database editions except Express, which is for free. So now Standard Edition One license is for 5 800 (was 4 995), Standard Edition is for 17 500 (was 15 000) and Enterprise Edition 47 500 (was 40 000) per processor. Personal Edition named user plus licence now is 460 US dollars (was 400). All new prices <a href="http://www.oracle.com/corporate/pricing/technology-price-list.pdf">are here</a>. These are the bad news.<br /></p><p>The good news are that Oracle has created very informative <a href="http://www.oracle.com/partners/sell/dist_agre/global/auth/pricetools.html">Pricing and Licensing Rules, Tools</a> page (at least I was not aware of that), which has many links to other valuable documents regarding Oracle pricing and licensing, for example, Oracle product <a href="http://solutions.oracle.com/opn_esrc/PRICE/PRICING_ENGINE/LOCALIZABLE_PRICE_LIST/OPN_LOCAL/LOCALIZABLE_EPLO_TECH_OPN.XLS">prices in other currencies</a> using Oracle exchange rates. </p><p>So get accustomed to new prices ;) and don't forget that there are other editions than Enterprise as well as there are such thing like term licensing (licenses for 1 till 5 years starting with 20% and ending with 70% of list price)!</p>Gints Plivnahttp://www.blogger.com/profile/17716037729002528529noreply@blogger.com2tag:blogger.com,1999:blog-22727713.post-91650143916279725882008-05-22T00:52:00.002+03:002008-05-22T01:16:47.131+03:00SQL Merge and Sequence gaps<br /><br />I hope You my dear reader isn't one of the people cursed by <a href="http://gplivna.eu/papers/gapless_sequences.htm">requirement for gapless sequences</a> ;)<br />Yesterday I just found another nail in the coffin for oracle sequence objects used as gapless sequence generators. Of course there are many causes not to do that, just like simple rollback, users changing their minds, network connection error, flushing cache etc. But I didn't know at least till yesterday, that MERGE statements generate gaps with quite big guarantee. It seems that Merge statement is generating the same number of calls for sequence next value as row count of source table or subquery.<br />Let's look at example:<br /><br /><span style="font-family:courier new;">CREATE TABLE target (pk number, data varchar2(10));</span><br /><span style="font-family:courier new;">CREATE TABLE source (pk_s number, data_s varchar2(10));</span><br /><span style="font-family:courier new;">INSERT INTO target VALUES (1, 'a');</span><br /><span style="font-family:courier new;">INSERT INTO target VALUES (3, 'c');</span><br /><span style="font-family:courier new;">INSERT INTO source VALUES (1, 'a');</span><br /><span style="font-family:courier new;">INSERT INTO source VALUES (2, 'b');</span><br /><span style="font-family:courier new;">INSERT INTO source VALUES (3, 'c');</span><br /><span style="font-family:courier new;">INSERT INTO source VALUES (4, 'd');</span><br /><span style="font-family:courier new;">COMMIT;</span><br /><span style="font-family:courier new;">CREATE SEQUENCE seq START WITH 5;</span><br /><br />So now we have 2 rows in target table, and 4 rows in source table. We'll compare them using data and data_s column. We'd insert only two rows, because another 2 already exists and even won't use WHEN MATCHED clause for update.<br /><br /><span style="font-family:courier new;">MERGE INTO target</span><br /><span style="font-family:courier new;">USING source</span><br /><span style="font-family:courier new;">ON (data = data_s)</span><br /><span style="font-family:courier new;">WHEN NOT MATCHED THEN </span><br /><span style="font-family:courier new;">INSERT VALUES (seq.nextval, data_s);</span><br /><span style="font-family:courier new;">SQL> SELECT * FROM target;</span><br /><span style="font-family:courier new;"> PK DATA</span><br /><span style="font-family:courier new;">---------- ----------</span><br /><span style="font-family:courier new;"> 1 a</span><br /><span style="font-family:courier new;"> 3 c</span><br /><span style="font-family:courier new;"> 7 d</span><br /><span style="font-family:courier new;"> 8 b</span><br /><br />Although the sequence should have started from 5 new pk values are 7 and 8. Let's add another row into source and look what happens:<br /><br /><span style="font-family:courier new;">INSERT INTO source VALUES (5, 'e');</span><br /><span style="font-family:courier new;">MERGE INTO target</span><br /><span style="font-family:courier new;">USING source</span><br /><span style="font-family:courier new;">ON (data = data_s)</span><br /><span style="font-family:courier new;">WHEN NOT MATCHED THEN </span><br /><span style="font-family:courier new;">INSERT VALUES (seq.nextval, data_s);</span><br /><span style="font-family:courier new;">SQL> SELECT * FROM target;<br /> PK DATA</span><br /><span style="font-family:courier new;">---------- ----------</span><br /><span style="font-family:courier new;"> 1 a</span><br /><span style="font-family:courier new;"> 3 c</span><br /><span style="font-family:courier new;"> 7 d</span><br /><span style="font-family:courier new;"> 8 b</span><br /><span style="font-family:courier new;"> 13 e</span><br /><br />So now the value is 13 i.e. 8 + count of rows from source table (5). Now let's add another row and delete all previous.<br /><br /><span style="font-family:courier new;">INSERT INTO source VALUES (6, 'f');</span><br /><span style="font-family:courier new;">DELETE source WHERE pk_s <=5;</span><br /><span style="font-family:courier new;">MERGE INTO target</span><br /><span style="font-family:courier new;">USING source</span><br /><span style="font-family:courier new;">ON (data = data_s)</span><br /><span style="font-family:courier new;">WHEN NOT MATCHED THEN </span><br /><span style="font-family:courier new;">INSERT VALUES (seq.nextval, data_s);</span><br /><span style="font-family:courier new;">SQL> SELECT * FROM target;<br /> PK DATA</span><br /><span style="font-family:courier new;">---------- ----------</span><br /><span style="font-family:courier new;"> 1 a</span><br /><span style="font-family:courier new;"> 3 c</span><br /><span style="font-family:courier new;"> 7 d</span><br /><span style="font-family:courier new;"> 8 b</span><br /><span style="font-family:courier new;"> 13 e</span><br /><span style="font-family:courier new;"> 14 f</span><br /><br />As we can see pk is incremented only by 1.<br />After my experiments I found also metalink note "Merge Increments SEQUENCE.NEXTVAL for Both Insert and Update" (Note:554656.1), which also assures this as expected situation and not bug. So another reason not to worry about sequence values, just uniqueness of them ;)Gints Plivnahttp://www.blogger.com/profile/17716037729002528529noreply@blogger.com2tag:blogger.com,1999:blog-22727713.post-30738922982990432342008-05-16T18:16:00.002+03:002008-05-16T18:47:35.164+03:00<span style="font-size:130%;">Minus All and Intersect All</span><br /><br /><br />Today I rediscovered <a href="https://mix.oracle.com/ideas">Oracle Mix ideas</a> and remembered my surprise that there are also <a href="http://gplivna.blogspot.com/2008/01/minus-all-and-intersect-all-in-oracle.html">Minus all and Intersect all operators in SQL standard</a> just like Union and Union all. I'll briefly remind You what exactly they were. Let's imagine we have two sets:<br /><br />T1: 1, 2, 2, 2, 3, 4, 4<br />T2: 2, 3, 4, 4, 4, 5<br /><br /><br />Then T1 INTERSECT T2 is 2, 3, 4<br />T1 INTERSECT ALL T2 would be 2, 3, 4, 4<br /><br /><br />T1 MINUS T2 is 1<br />T1 MINUS ALL T2 would be 1, 2, 2<br /><br /><br />So by default Intersect and Minus have DISTINCT operator removing all non-unique values. But in case we add keyword ALL then all values and their cardinalities are counted as well. I think these operators could be quite handy in process of analysing two data sets and finding what exactly the difference is. So if You are thinking the same way then I kindly ask you to <a href="https://mix.oracle.com/ideas/28856-add-except-minus-all-and-intersect-all-set-operators">support my idea about intersect all and minus all</a> with your voice in Oracle Mix.<br /><br />Thank You in advance for any decision :) and I hope sooner or later (better sooner of course ;) to find that in New features guide.Gints Plivnahttp://www.blogger.com/profile/17716037729002528529noreply@blogger.com0tag:blogger.com,1999:blog-22727713.post-39317423397918791682008-05-07T19:49:00.007+03:002008-05-08T01:12:43.790+03:00<h3>DBMSes are different...</h3>Recently had to do some work with SQL Server. As my experience with it is quite limited I've got a few interesting impressions, which probably are absolutely trivial for SQL Server adepts :)<br /><h4>Do you know that SELECT query can create new tables?</h4>OK in SQL Server it can. So I had to create backup of a table and firstly was quite frustrated not finding anything like CTAS (CREATE TABLE ... AS SELECT). I was on a wrong track however. The right syntax is SELECT INTO as in following example:<br /><br /><span style="font-family:courier new;">CREATE TABLE persons (<br />prs_id INT IDENTITY NOT NULL PRIMARY KEY,<br />prs_name VARCHAR(30) NOT NULL) ;<br /><br />INSERT INTO persons (prs_name) VALUES ('GINTS');<br />INSERT INTO persons (prs_name) VALUES ('JOE');<br /><br />SELECT *<br />INTO persons_bkp<br />FROM persons;<br /><br />SELECT * FROM persons_bkp;<br />-------------------------<br />1 GINTS<br />2 JOE<br /></span><br /><br />So till now I was under the impression that SELECT is DML statement however obviously that's not true at least for SQL Server.<br /><h4>Do you know that SQL Server can handle recursive queries?</h4><p>At least since version 2005 it can. Long ago I have heard that SQL Server has nothing similar to START WITH CONNECT BY. This site <a href="http://www.psoug.org/reference/sqlserver.html">comparing SQL Server and Oracle</a> seemed to approve my thoughts. However DBMSes are different and one has to look for different ways accomplishing the same goals. And in SQL Server one can use Common table expressions (aka query factoring clause or with clause in Oracle world) to get hierarchical results. And BTW here SQL Server "outfunctions" Oracle because it allows to recursively reference the same common table in its definition, which is not allowed by Oracle. Here are two articles if you are interested in how exactly it is done: </p><ul><li><a href="http://msdn.microsoft.com/en-us/library/ms186243.aspx">Recursive Queries Using Common Table Expressions</a> </li><li><a href="http://www.sqlservercentral.com/articles/Development/recursivequeriesinsqlserver2005/1760/">Recursive Queries in SQL Server 2005</a> </li></ul><p>So in conclusion these two features which are possible in both DBMSes, but are implemented in completely different ways supports my previous article that <a href="http://gplivna.eu/papers/choose_database.htm#_Toc183880189">all databases are different</a> :)</p>Gints Plivnahttp://www.blogger.com/profile/17716037729002528529noreply@blogger.com2tag:blogger.com,1999:blog-22727713.post-24432877292183260702008-01-30T23:39:00.000+02:002008-01-31T01:37:50.131+02:00<h3>The year has gone..</h3>..since I started blogging. Actually I started it because of real anger against Oracle. The reason was one of the most famous problems in OTN history - <a href="http://gplivna.blogspot.com/2007/01/five-million-and-counting-what_30.html">inability to change e-mails</a> ;) However about a month or two later <a href="http://gplivna.blogspot.com/2007/04/statistics-is-power.html">it was resolved</a>.<br />It seems the last year Oracle has made advances towards the community and:<br />1) listened to it (above mentioned case) as well as for example <a href="http://www.pythian.com/blogs/526/an-open-letter-to-larry-ellison-on-awr-and-ash-licensing">the case with AWR and ASH licensing</a>. Probably all these steps were already on their way but I'm quite sure blog entries accelerated the result.<br />2) created public resources (<a href="http://wiki.oracle.com/">oracle wiki</a>) probably not veeeeery actively used until now.<br />3) <a href="http://oracleappslab.com/2007/10/30/seriously-we-want-bloggers-at-openworld/">granted free access</a> for active members of public community to Oracle open world.<br />4) most probably made other positive steps I cannot remember or simply haven't heard about.<br /><br />Last year was also big improvement in so called Oracle blogosphere - <a href="http://orana.info/">orana.info</a> was founded along with some other aggregators, <a href="http://wiki.oracle.com/page/Blogging">which can be found on oracle wiki</a>.<br /><br />For me it was a year of realizing the fact that there are tremendous resources about databases and Oracle in English, but just few in my mother tongue Latvian. So as I don't think English is the only language in the world even for databases :) I started to <a href="http://datubazes.wordpress.com/">blog about databases in Latvian</a>. Quite a bit of free time goes there.<br /><br />So in my little anniversary I wish Oracle remember and support its community (probably sponsor a trip to Open world next year for bloggers khe khe :))) and myself to continue work about databases and Oracle resources especially in Latvian!Gints Plivnahttp://www.blogger.com/profile/17716037729002528529noreply@blogger.com3tag:blogger.com,1999:blog-22727713.post-53930250309756880112008-01-25T12:12:00.006+02:002008-07-14T23:41:56.590+03:00<h3>SQL Join Types</h3><br /><b>New enhanced 25 pages long article with 44 examples and visual pictures describing <a href="http://www.gplivna.eu/papers/sql_join_types.htm">SQL join types</a> can be found here.</b><br /><p>I'm studying a bit SQL joins and trying to make clear at least for me what they are and what the relationships among them are. As I've always thought that picture is worth hundred words I've tried to create ER diagram describing metainfo about joins. So the question for everyone is - is this diagram OK? And aren't there obvious bugs? :)<br />Probably from the strict viewpoint of Set theory and <a href="http://en.wikipedia.org/wiki/Relational_algebra">relational algebra</a> it is not OK, but I'm more concerned of practical SQL usage and understanding. In case you know similar diagramm or whatever else visual explanation of relationships among join types please leave comment with link.<br />So the diagram is as follows (please click on it to get bigger):<br /><br /><a title="SQL Join Types" href="http://datubazes.files.wordpress.com/2008/02/joins2.png"><img alt="SQL Join Types" src="http://datubazes.wordpress.com/files/2008/02/joins31.png" /></a><br /><br />Now for those that aren’t familiar with all these join types and terms in diagram above I'll try to give SQL examples (without data and results) for each one of them. If you'd like to have also examples with data cehck one of the links at the very end of this post.<br />Assume we have 2 tables - TableA (id, title) and TableB (id, description).<br />What the result are for each one of them you can find in other places for example: </p><ul><li><strong>Cross join:</strong></li></ul><p><span style="font-family:courier new;">SELECT * FROM TableA CROSS JOIN TableB </span></p><ul><li><strong>Join with restriction:</strong></li></ul><p>It is either natural join or qualified join. Each join with restriction has one cross join which is the degraded case of it. </p><ul><li><strong>Natural join:</strong></li></ul><p>Natural joins can be Inner or one of Outer joins, but they always are equi joins. Without explicitly specified inner or outer it is inner join. More about natural joins in <a href="http://gplivna.blogspot.com/2007/10/natural-joins-are-evil-motto-if-you.html">my previous blog post</a>. </p><p><span style="font-family:courier new;">SELECT * FROM TableA NATURAL JOIN TableB </span></p><ul><li><strong>Qualified join:</strong></li></ul><p>Qualified joins can be written either specifying join columns (named columns join) or explicitly writing join condition. </p><ul><li><strong>Join type: </strong></li></ul><p>Could not find anything better to somehow unite inner and various outer joins under one roof. </p><ul><li><strong>Inner join:</strong></li></ul><p><span style="font-family:courier new;">SELECT * FROM TableA NATURAL INNER JOIN TableB<br />SELECT * FROM TableA INNER JOIN TableB USING (id)<br />SELECT * FROM TableA INNER JOIN TableB ON (tablea.id = tableb.id) </span></p><ul><li><strong>Outer join:</strong></li></ul><p>Outer join is one of left, right or full outer join. </p><ul><li><strong>Left outer join:</strong></li></ul><p><span style="font-family:courier new;">SELECT * FROM TableA NATURAL LEFT OUTER JOIN TableB<br />SELECT * FROM TableA LEFT OUTER JOIN TableB USING (id)<br />SELECT * FROM TableA LEFT OUTER JOIN TableB ON (tablea.id = tableb.id) </span></p><ul><li><strong>Right outer join and Full outer join:</strong></li></ul><p>Take the same examples as from left outer join and just replace left with right or outer. </p><ul><li><strong>Qualified join expression:</strong></li></ul><p>There are two ways how more explicitly write join condition either specifying join columns or explicitly writing join condition. </p><ul><li><strong>Named columns join:</strong></li></ul><p>This join is written with "USING" clause. Named columns join always is equi join. </p><p><span style="font-family:courier new;">SELECT * FROM TableA INNER JOIN TableB USING (id) </span></p><ul><li><strong>Conditional join:</strong></li></ul><p>This join is the real one that I'd like to suggest using. With explicitly specifying what the join condition is. This also is the only join where one can use not only equi join but also another predicate operator than "=". </p><p><span style="font-family:courier new;">SELECT * FROM TableA INNER JOIN TableB ON (tablea.id = tableb.id) </span></p><ul><li><strong>Predicate operator type:</strong></li></ul><p>Based on predicate operator type joins can be classified as either equi or theta (nonequi) joins. </p><ul><li><strong>Equi join:</strong></li></ul><p>Equi join is join where operator used in join condition is equivalence ("=").<br />Both natural joins and Named columns joins are always equi joins. Examples: </p><p><span style="font-family:courier new;">SELECT * FROM TableA NATURAL LEFT OUTER JOIN TableB<br />SELECT * FROM TableA FULL OUTER JOIN TableB USING (id)<br />SELECT * FROM TableA INNER JOIN TableB ON (tablea.id = tableb.id) </span></p><ul><li><strong>Theta (Nonequi) join:</strong></li></ul><p>Equi join is join where operator used in join condition is something other than equivalence ("="). </p><p><span style="font-family:courier new;">SELECT * FROM TableA INNER JOIN TableB ON (tablea.id <></p><p>I've not showed here such beasts like UNION JOINS, SEMI JOINS and ANTI JOINS because the first one (UNION join) is neither in SQL standard nor in any database I worked with.<br />SEMI JOINS and ANTI JOINS are somehow less interesting for me. </p><p><strong>Further reading: </strong></p><ul><li><a href="http://v.hdm-stuttgart.de/~riekert/lehre/db-kelz/chap7.htm">SQL und relationale Algebra</a> (in German with for everyone understandable examples)</li><li><a href="http://en.wikipedia.org/wiki/Relational_algebra">Relational algebra, joins and join like operation from wikipedia</a></li><li>Why I think <a href="http://gplivna.blogspot.com/2007/10/natural-joins-are-evil-motto-if-you.html">natural joins are most unnatural joins ever</a>!<br /><br /></li></ul><br /></span>Gints Plivnahttp://www.blogger.com/profile/17716037729002528529noreply@blogger.com3tag:blogger.com,1999:blog-22727713.post-52012517177435909322008-01-13T14:26:00.000+02:002008-01-14T08:43:43.427+02:00<h3>Some photos</h3><br />I've been <a href="http://radiofreetooting.blogspot.com/2008/01/all-about-me.html">tagged by APC</a>. Usually I don't respond to <a href="http://www.cs.rutgers.edu/~watrous/chain-letters.html">chain letters</a> but this will be a bit of exception. However I won't tag anyone further. Looking at <a href="http://forums.speedguide.net/showthread.php?t=44882">usual curses</a> happening after NOT forwarding chain letters I hope all my Oracle installations won't silently transform to SQL Server ones ;)<br />So I won't tell anything about me, I'll just publish some of my best photos taken in various places in Latvia.<br /><table><br /><tr valign=bottom><br /><td><br /><a href="http://www.gplivna.eu/Blogspot/LilacsBig.jpg" target="_blank"><img border="0" width=180 src="http://www.gplivna.eu/Blogspot/Lilacs.jpg" alt="Lilacs in Dobele" /></a><br /></td><br /><td><br /><a href="http://www.gplivna.eu/Blogspot/OperaSquareBig.jpg" target="_blank"><br /><img border="0" width=180 align="middle" src="http://www.gplivna.eu/Blogspot/OperaSquare.jpg" alt="Opera square"/></a><br /></td><br /></tr><br /><tr valign=top><td>Lilacs in Dobele garden</td><td>Garden if front of National Opera</td></tr><br /><br /><tr valign=bottom><br /><td><br /><a href="http://www.gplivna.eu/Blogspot/LesserSpottedEagleNestBig.jpg" target="_blank"><img border="0" width=180 src="http://www.gplivna.eu/Blogspot/LesserSpottedEagleNest.jpg" alt="Nest of Lesser Spotted Eagle" /></a><br /></td><br /><td><br /><a href="http://www.gplivna.eu/Blogspot/HeathBig.jpg" target="_blank"><br /><img border="0" width=180 align="middle" src="http://www.gplivna.eu/Blogspot/Heath.jpg" alt="Heath" /></a><br /></td><br /></tr><br /><tr valign=top><td>Nest of Lesser Spotted Eagle</td><td>Airing trail in heath</td></tr><br /><br /><tr valign=bottom><br /><td><br /><a href="http://www.gplivna.eu/Blogspot/LakeInFogBig.jpg" target="_blank"><img border="0" width=180 src="http://www.gplivna.eu/Blogspot/LakeInFog.jpg" alt="A lake in fog" /></a><br /></td><br /><td><br /><a href="http://www.gplivna.eu/Blogspot/SunSetBig.jpg" target="_blank"><br /><img border="0" width=180 align="middle" src="http://www.gplivna.eu/Blogspot/SunSet.jpg" alt="Sun set" /></a><br /></td><br /></tr><br /><tr valign=top><td>A lake in fog</td><td>Sun set view over my country landed property</td></tr><br /><br /><tr valign=bottom><br /><td><br /><a href="http://www.gplivna.eu/Blogspot/AtteBig.jpg" target="_blank"><img border="0" width=180 src="http://www.gplivna.eu/Blogspot/Atte.jpg" alt="Open air museum in Atte" /></a><br /></td><br /><td><br /><a href="http://www.gplivna.eu/Blogspot/EasterEggsBig.jpg" target="_blank"><br /><img border="0" width=180 align="middle" src="http://www.gplivna.eu/Blogspot/EasterEggs.jpg" alt="Heath" /></a><br /></td><br /><tr valign=top><td>Open air museum in Atte</td><td>Easter eggs by my wife(colouring of course :)</td></tr><br /><br /><tr valign=bottom><br /><td><br /><a href="http://www.gplivna.eu/Blogspot/OperaHouseBig.jpg" target="_blank"><img border="0" width=180 src="http://www.gplivna.eu/Blogspot/OperaHouse.jpg" alt="National Opera House in Riga" /></a><br /></td><br /><td><br /><a href="http://www.gplivna.eu/Blogspot/RundalePalaceBig.jpg" target="_blank"><br /><img border="0" width=180 align="middle" src="http://www.gplivna.eu/Blogspot/RundalePalace.jpg" alt="Rundale Palace" /></a><br /></td><br /><tr valign=top><td>National Opera House in Riga</td><td>Rundale Palace</td></tr><br /><br /><tr valign=bottom><br /><td><br /><a href="http://www.gplivna.eu/Blogspot/RigaDomCathedralBig.jpg" target="_blank"><img border="0" width=180 src="http://www.gplivna.eu/Blogspot/RigaDomCathedral.jpg" alt="Riga Dom Cathedral" /></a><br /></td><br /><td><br /><a href="http://www.gplivna.eu/Blogspot/HouseOfBlackHeadsAndStPeterChurchBig.jpg" target="_blank"><br /><img border="0" width=180 align="middle" src="http://www.gplivna.eu/Blogspot/HouseOfBlackHeadsAndStPeterChurch.jpg" alt="House Of Blackheads and St.Peter church" /></a><br /></td><br /><tr valign=top><td>Riga Dom Cathedral - biggest church in Baltics</td><td>House Of Blackheads and St.Peter church</td></tr><br /><br /><tr valign=bottom><br /><td><br /><a href="http://www.gplivna.eu/Blogspot/RailRoadBridgeBig.jpg" target="_blank"><img border="0" width=180 src="http://www.gplivna.eu/Blogspot/RailRoadBridge.jpg" alt="Railroad bridge at night" /></a><br /></td><br /><td><br /><a href="http://www.gplivna.eu/Blogspot/ArtNoveauBig.jpg" target="_blank"><br /><img border="0" width=180 align="middle" src="http://www.gplivna.eu/Blogspot/ArtNoveau.jpg" alt="Art Noveau in Riga" /></a><br /></td><br /><tr valign=top><td>Railroad bridge at night</td><td>Art Noveau in Riga</td></tr><br /></table>Gints Plivnahttp://www.blogger.com/profile/17716037729002528529noreply@blogger.com0tag:blogger.com,1999:blog-22727713.post-77063952291429501502008-01-10T22:08:00.001+02:002008-01-13T17:25:01.594+02:00<span style="font-size:130%;">MINUS ALL and INTERSECT ALL in Oracle Revisited<br /></span><br />Yesterday <a href="http://gplivna.blogspot.com/2008/01/minus-all-and-intersect-all-in-oracle.html">I wrote an article</a> explaining that MINUS ALL and INTERSECT ALL is not possible in Oracle.<br />However today after a bit thinking I got insight - <strong>multiset operations! </strong>Yes these should be the right thing!<br />Since 10g Release 1 <a href="http://download.oracle.com/docs/cd/B14117_01/server.101/b10759/operators006.htm#sthref780">Oracle supports</a> MULTISET UNION/EXCEPT/INTERSECT for both ALL/DISTINCT. I have only to make some types, a bit type casting and everything should be ok!<br /><br />So I've started with the simple tables I've shown yesterday.<br /><pre><br />CREATE TABLE t1 AS SELECT mod(rownum, 1000) rn<br />FROM dba_source WHERE rownum <=50000;<br />CREATE TABLE t2 AS SELECT * FROM t1;<br /></pre><br /><br />Now I can query both tables and try to compare then. To use multiset operations I need to <a href="http://download.oracle.com/docs/cd/B14117_01/server.101/b10759/functions015.htm#SQLRF00613">cast subquery</a> as nested table. To be able to use such type casting I have to create two additional types. The first one is simple Object type containing records I'd like to compare. Second one is object table type, to which I'll cast my subqueries. So they are as follows:<br /><br /><pre><br />CREATE TYPE t1_type<br />AS OBJECT ( rn NUMBER);<br />/<br />CREATE TYPE t1_tab_type<br />AS TABLE OF t1_type;<br />/<br /></pre><br /><br />So far so good. Let's start building queries step by step. The first one just builds nested table from subquery.<br /><br /><pre><br />SQL> select cast(multiset(<br /> 2 select rn from t1 where rownum <=3<br /> 3 ) as t1_tab_type)<br /> 4 from dual<br /> 5 /<br /><br />CAST(MULTISET(SELECTRNFROMT1WHEREROWNUM<=3)AST1_TAB_TYPE)(RN)<br />---------------------------------------------------------------<br />T1_TAB_TYPE(T1_TYPE(1), T1_TYPE(2), T1_TYPE(3))<br /></pre><br />The second one already uses multiset intersect to get desired result.<br /><pre><br />SQL> select col1 multiset intersect distinct col2<br /> 2 from (<br /> 3 select cast(multiset(<br /> 4 select rn from t1 where rownum <=3<br /> 5 ) as t1_tab_type) col1,<br /> 6 cast(multiset(<br /> 7 select rn from t2 where rownum <=3<br /> 8 ) as t1_tab_type) col2<br /> 9 from dual<br /> 10 )<br /> 11 /<br /><br />COL1MULTISETINTERSECTDISTINCTCOL2(RN)<br />---------------------------------------------------<br />T1_TAB_TYPE(T1_TYPE(1), T1_TYPE(2), T1_TYPE(3))<br /></pre><br />Now we just need to add one final step - cast nested table rows back to normal rows. Here us helps select from table construct:<br /><pre><br />SQL> select * from table (<br /> 2 select col1 multiset intersect distinct col2<br /> 3 from (<br /> 4 select cast(multiset(<br /> 5 select rn from t1 where rownum <=3<br /> 6 ) as t1_tab_type) col1,<br /> 7 cast(multiset(<br /> 8 select rn from t2 where rownum <=3<br /> 9 ) as t1_tab_type) col2<br /> 10 from dual<br /> 11 )<br /> 12 )<br /> 13 /<br /><br /> RN<br />----------<br /> 1<br /> 2<br /> 3<br /></pre><br />Voila! Everything works!<br />However then I was torn with doubt - how it will work when not used as a toy but on real volumes? I created million rows worth table with wide column and got - yeahh I got following:<br /><br />ORA-22813: operand value exceeds system limits<br /><br />Not pleasing.<br /><br />Then I started to play with the tables defined above and got quite awful results. What is the performance of normal MINUS set operator?<br /><pre><br />SQL> select * from t1<br /> 2 minus<br /> 3 select * from t2 where rn <=998;<br /> RN<br />----------<br /> 999<br />Elapsed: 00:00:00.10<br /></pre><br /><br />What is the equivalent for multiset except distinct? I was a bit unpleasantly surprised again:<br /><pre><br />SQL> select * from table (<br /> 2 select col1 multiset except distinct col2<br /> 3 from (<br /> 4 select cast(multiset(<br /> 5 select rn from t1<br /> 6 ) as t1_tab_type) col1,<br /> 7 cast(multiset(<br /> 8 select rn from t2 where rn <= 998<br /> 9 ) as t1_tab_type) col2<br /> 10 from dual<br /> 11 )<br /> 12 )<br /> 13 /<br /> RN<br /> ----------<br /> 999<br /> Elapsed: 00:01:17.98<br /> </pre><br />However for multiset except all I was simply shocked!<br /><pre><br />SQL> select * from table (<br /> 2 select col1 multiset except all col2<br /> 3 from (<br /> 4 select cast(multiset(<br /> 5 select rn from t1<br /> 6 ) as t1_tab_type) col1,<br /> 7 cast(multiset(<br /> 8 select rn from t2 where rn <= 998<br /> 9 ) as t1_tab_type) col2<br /> 10 from dual<br /> 11 )<br /> 12 )<br /> 13 /<br /> RN<br /> ----------<br /> 999<br /> ...<br /> 999<br /> 50 rows selected.<br /> Elapsed: 00:06:05.39<br /> </pre><br />6 minutes for 2 * 50K rows!? Hmm is it implemented using bubble sort or what? :O<br /><br />So normal SQL minus needed 0.10 seconds, multiset minus distinct 1 minute 18 seconds, but multiset except all more than 6 minutes.<br /><br />Yeahh I got another strong argument <strong>NOT</strong> to use nested tables and their operations ;)Gints Plivnahttp://www.blogger.com/profile/17716037729002528529noreply@blogger.com0tag:blogger.com,1999:blog-22727713.post-22488619050346089882008-01-09T18:51:00.000+02:002008-01-09T19:04:09.030+02:00<h3>MINUS ALL and INTERSECT ALL in Oracle</h3><p>Recently I was a bit studying <a href="http://www.wiscorp.com/SQLStandards.html">SQL standard</a> and a lot to my surprise found that there exists not only UNION ALL set operator, but also INTERSECT ALL and EXCEPT ALL (in Oracle version it would be MINUS ALL).</p><p>So you'd say Oracle haven't them and it is also (although partially - somehow minus has been forgotten) documented in <a href="http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/ap_standard_sql004.htm#i7738">Oracle Support for Optional Features of SQL/Foundation:2003</a>.</p><p>Hmm, yea but a while ago I was doing <a href="http://www.gplivna.eu/papers/legacy_app_migration.htm">several data migrations</a> and then such set operators would really helped me. Now a bit what these non-existant (at least for Oracle) set operators are doing?</p><p>Imagine we have tables T1 and T2 with data as follows:</p><pre><span style="font-family:courier new;">T1: 1, 2, 2, 2, 3, 4, 4</span></pre><pre><span style="font-family:courier new;">T2: 2, 3, 4, 4, 4, 5</span></pre><p>So what is the result for SELECT * FROM T1 MINUS SELECT * FROM T2?</p><p><span style="font-family:courier new;">1</span></p><p>What <em>would be</em> the result for SELECT * FROM T1 <strong>MINUS ALL</strong> SELECT * FROM T2?</p><p><span style="font-family:courier new;">1, 2, 2</span></p><p>What is the result for SELECT * FROM T1 INTERSECT SELECT * FROM T2?</p><p><span style="font-family:courier new;">2, 3, 4</span></p><p>What <em>would be</em> the result for SELECT * FROM T1 <strong>INTERSECT ALL</strong> SELECT * FROM T2?</p><p><span style="font-family:courier new;">2, 3, 4, 4</span></p><p>As you can see ALL for EXCEPT/MINUS and INTERSECT retains cardinality and doesn't keep only unique records. Let's hope Oracle someday will stop this gap and give us this functionality :)<br /></p>Gints Plivnahttp://www.blogger.com/profile/17716037729002528529noreply@blogger.com6tag:blogger.com,1999:blog-22727713.post-83912177777735866562007-12-08T22:29:00.000+02:002007-12-08T22:45:36.065+02:00<h3>Oracle wiki</h3>I've thought for a while about creating an article about the resources available in Oracle problem solving process. Meanwhile <a href="http://wiki.oracle.com/">Oracle wiki</a> was born and already exists for about two months. As it is supposed to be shared and editable resource by everyone in Oracle community I thought that it would be a perfect place for my eventual article for two reasons:<br />1) more people hopefully will read it and probably get some help;<br />2) more people can add their suggestions how they solve Oracle related problems.<br />So my first contribution to Oracle wiki is <a href="http://wiki.oracle.com/page/How+to+solve+Oracle+Database+related+problems">How to solve Oracle Database related problems</a>. Everyone is invited to correct it and/or add some other resources. As well as for other Oracle wiki pages of course.Gints Plivnahttp://www.blogger.com/profile/17716037729002528529noreply@blogger.com0tag:blogger.com,1999:blog-22727713.post-60207649473908830792007-11-26T23:43:00.000+02:002007-11-26T23:55:38.276+02:00<h3>How to choose database</h3><br />Most of us (I mean readers of Oracle related blogs) most probably have already decided which DBMS to use. However the world is changing and sooner or later either you'll have to choose a new DBMS or defend your favourite one.<br />Quite often we hear such argument like DB X is cheaper than DB Y, therefore we have to take this one. And cheaper usually means only license cost. However license cost is only one <strong>part of</strong> <em>direct expenses</em> not to speak even about <em>indirect expenses</em>.<br />So <a href="http://www.gplivna.eu/papers/choose_database.htm">i've written an article</a> trying to explain more than 10 criteria which should be analyzed before final decision. Article contains also a list of more than 30 databases with links to their sites one can choose from.Gints Plivnahttp://www.blogger.com/profile/17716037729002528529noreply@blogger.com3tag:blogger.com,1999:blog-22727713.post-78124720886542470322007-11-04T19:34:00.000+02:002007-11-04T19:39:56.338+02:00<h3>The curse of gapless sequences</h3><br /><p>Gapless sequences are one of the best examples of <a href="http://gplivna.blogspot.com/2007/03/where-bad-performance-starts-my.html">bad requirements</a>. Initially it was thought as an article for my blog but the result was too big and I've put it <a href="http://www.gplivna.eu/papers/gapless_sequences.htm">on my site</a>.Gints Plivnahttp://www.blogger.com/profile/17716037729002528529noreply@blogger.com0tag:blogger.com,1999:blog-22727713.post-49251257586954680382007-10-25T15:12:00.000+03:002007-10-25T15:28:03.068+03:00<h3>Hints "stronger" than db parameters?</h3><p>Actually I've tested only one parameter so the plural in title is questionable generalization :)</p><p>Recently battling with ORA-04030: out of process memory errors I found at least for me quite interesting fact.<br />USE_HASH hint is more powerful than parameter _hash_join_enabled = false (for 10g) or hash_join_enabled = false (for 9i).<br />I was quite suprised because without this test case I'd thought completely opposite. Of course neither hash_join_enabled nor _hash_join_enabled should be often altered to different value than default "true", but in case of "false" and your code having hints use_hash, you'll have hash joins regardless of above mentioned db parameter setting. It might be quite nasty surprise in case you have eliminated hash joins because of possible bugs, for example.<br />I've tested this behaviour for 10.2.0.1.0 SE and 10.2.0.1.0 EE on Windows as well as 10.2.0.3.0 SE on AIX and 9.2.0.7.0 EE on windows, all had the same behaviour.<br />Here is my test case and 10046 event trace execution plan results on 10.2.0.1.0 SE/Windows (all other tests were similar):<br /><pre><br />SQL> select * from v$version;<br />BANNER<br />----------------------------------------------------------------<br />Oracle Database 10g Release 10.2.0.1.0 - Production<br />PL/SQL Release 10.2.0.1.0 - Production<br />CORE 10.2.0.1.0 Production<br />TNS for 32-bit Windows: Version 10.2.0.1.0 - Production<br />NLSRTL Version 10.2.0.1.0 - Production<br /><br />SQL> create table a as select rownum rn, object_name<br /> 2 from all_objects;<br /><br />Table created.<br /><br />SQL> alter session set events '10046 trace name context forever, level 1';<br /><br />Session altered.<br /><br />SQL> select count(*) from (<br /> 2 select *<br /> 3 from a a1, a a2<br /> 4 where a1.rn = a2.rn);<br /><br /> COUNT(*)<br />----------<br /> 60141<br /><br />Execution plan:<br />STAT #24 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=556 pr=73 pw=0 time=212727 us)'<br />STAT #24 id=2 cnt=60141 pid=1 pos=1 obj=0 op='HASH JOIN (cr=556 pr=73 pw=0 time=406508 us)'<br />STAT #24 id=3 cnt=60141 pid=2 pos=1 obj=176936 op='TABLE ACCESS FULL A (cr=278 pr=73 pw=0 time=61652 us)'<br />STAT #24 id=4 cnt=60141 pid=2 pos=2 obj=176936 op='TABLE ACCESS FULL A (cr=278 pr=0 pw=0 time=60192 us)'<br /><br /><br />SQL> alter session set "_hash_join_enabled" = false;<br /><br />Session altered.<br /><br />SQL> select count(*) cnt from (<br /> 2 select *<br /> 3 from a a1, a a2<br /> 4 where a1.rn = a2.rn);<br /><br /> CNT<br />----------<br /> 60141<br /><br />Execution plan:<br />STAT #12 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=556 pr=0 pw=0 time=374302 us)'<br />STAT #12 id=2 cnt=60141 pid=1 pos=1 obj=0 op='MERGE JOIN (cr=556 pr=0 pw=0 time=470826 us)'<br />STAT #12 id=3 cnt=60141 pid=2 pos=1 obj=0 op='SORT JOIN (cr=278 pr=0 pw=0 time=88667 us)'<br />STAT #12 id=4 cnt=60141 pid=3 pos=1 obj=176936 op='TABLE ACCESS FULL A (cr=278 pr=0 pw=0 time=44 us)'<br />STAT #12 id=5 cnt=60141 pid=2 pos=2 obj=0 op='SORT JOIN (cr=278 pr=0 pw=0 time=231092 us)'<br />STAT #12 id=6 cnt=60141 pid=5 pos=1 obj=176936 op='TABLE ACCESS FULL A (cr=278 pr=0 pw=0 time=60174 us)'<br /><br />SQL> select count(*) cnt from (<br /> 2 select /*+ full(a1) full(a2) use_hash(a1 a2)*/ *<br /> 3 from a a1, a a2<br /> 4 where a1.rn = a2.rn);<br /><br /> CNT<br />----------<br /> 60141<br /><br />Execution plan:<br />STAT #24 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=556 pr=73 pw=0 time=214116 us)'<br />STAT #24 id=2 cnt=60141 pid=1 pos=1 obj=0 op='HASH JOIN (cr=556 pr=73 pw=0 time=399462 us)'<br />STAT #24 id=3 cnt=60141 pid=2 pos=1 obj=176936 op='TABLE ACCESS FULL A (cr=278 pr=73 pw=0 time=60803 us)'<br />STAT #24 id=4 cnt=60141 pid=2 pos=2 obj=176936 op='TABLE ACCESS FULL A (cr=278 pr=0 pw=0 time=60188 us)'<br /></pre>Gints Plivnahttp://www.blogger.com/profile/17716037729002528529noreply@blogger.com1tag:blogger.com,1999:blog-22727713.post-82505032783904387982007-10-17T23:41:00.000+03:002007-10-17T23:50:33.218+03:00<span style="font-size:130%;">All latvian Oracle (and not only) users - UNITE!<br /></span><br />I'd like to announce two resources for people that like relational databases and speak Latvian.<br />The first one is <a href="http://groups.google.ca/group/lvoug">discussion group</a> for Latvian Oracle User Group. The near future plans are described <a href="http://groups.google.ca/group/lvoug/browse_frm/thread/2372a5195e9f753b">here</a> (in Latvian).<br />If you speak Latvian and work with Oracle then it is your duty :) to join it! Of course one can join even if he doesn't speak Latvian, however there will be a small problem - everyone will understand you, but it is doubtful that you will understand anything except some keywords like Oracle, SQL, etc :)<br /><br />The second resource is mine. And it is more general, it is <a href="http://datubazes.wordpress.com/">blog about relational databases</a> (in Latvian).<br />Recently I've searched Internet and was very unpleasantly surprised - I've found <a href="http://datubazes.wordpress.com/2007/10/10/db-resursi-latvija/">very few resources</a> devoted to relational databases. So not to make only complaints I've started to blog about relational databases in my mother tongue. I'm of course not living in illusions that I can do it only myself and therefore anyone that is at least a little bit eager to help me in this process and has some knowledge either in general SQL, Oracle, MS SQL Server, MySQL, PosgreSQL or another relational db is encouraged to join me and become author in this blog.Gints Plivnahttp://www.blogger.com/profile/17716037729002528529noreply@blogger.com0