Re: SDriverOracle OffesetStrategy

anthony berglas <[email protected]> Thu, 7 Feb 2013 14:58:01 +1000
Newsgroups gmane.comp.java.orm.simpleorm
Message-ID <CA+_PZMeSLzNeOeUV-2qjNGXr82M-eKrXpdELHo4ap4eD9NarHg@mail.gmail.com> 
--f46d04479f95d60b6704d51b4ca6
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Have you ever had an issue with PostgreSql in production?  I recently used
it to build a text index, and it seemed quite good, although slightly
slower than MS SQL on several queries.

One trick I like about PostgreSql is that it barely buffers db blocks in
memory itself.  Instead it just relies on the O/S to buffer the files.
 Simple and fast.

Anthony

On Thu, Feb 7, 2013 at 2:17 AM, Franck Routier <[email protected]>wr=
ote:

>  Hi,
>
> Le 05/02/2013 00:40, anthony berglas a =E9crit :
>
>
>
> Again, it amazes me after all these years that Oracle does not support
> these facilities more easily.  Why put up a barrier for people to migrate
> to Oracle!
>
>  Well, I just discovered another gotcha with this 'rownum' approach.
>
> In this end, we embed a query within one (limit) or two (limit/offset)
> layers of Oracle specific query like this:
>
> select realquery.* from
> (select .... from ....) realquery
> where rownum < limit
>
> This is well, but... Oracle puts another gotcha here.
>
> In Postgresql, you can do this :
>
> select * from (
> select id, id from mytable) realquery;
>
> works fine.
>
> Doing the same in Oracle will result in 00918. 00000 -  "column
> ambiguously defined".
> You cannot select * from a subquery that as several columns named or
> aliased the same...
> This won't probably hurt anyone in Simpleorm, except if using a rawQuery,
> with several columns named the same, and with limit/offset, under Oracle.
>
> Well, guess what: we do that in our project... I will change this, as thi=
s
> has no real interest, but it was an automatically generated query, and it
> was convinient not to bother.
>
> I really prefer Postgresql in many ways. But our customers tend to have
> invested a lot in Oracle....
>
> Regards,
>
> Franck
>
>
>


--=20

Dr Anthony Berglas, [email protected]       Mobile: +61 4 4838 8874
Just because it is possible to push twigs along the ground with ones nose
does not necessarily mean that that is the best way to collect firewood.

--f46d04479f95d60b6704d51b4ca6
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable





<head>

<style type=3D"text/css">
<!--

/* start of attachment style */
       .ygrp-photo-title{
         clear: both;
         font-size: smaller;
         height: 15px;
         overflow: hidden;
         text-align: center;
         width: 75px;
       }
       div.ygrp-photo{
         background-position: center;
         background-repeat: no-repeat;
         background-color: white;
         border: 1px solid black;
         height: 62px;
         width: 62px;
       }

       div.photo-title=20
         a,
         div.photo-title a:active,
         div.photo-title a:hover,
         div.photo-title a:visited {
           text-decoration: none;=20
       }

       div.attach-table div.attach-row {
         clear: both;
       }

       div.attach-table div.attach-row div {
         float: left;
         /* margin: 2px;*/
       }

       p {
         clear: both;
         padding: 15px 0 3px 0;
	 overflow: hidden;
       }

       div.ygrp-file {
         width: 30px;
         valign: middle;
       }
       div.attach-table div.attach-row div div a {
         text-decoration: none;
       }

       div.attach-table div.attach-row div div span {
         font-weight: normal;
       }

       div.ygrp-file-title {
         font-weight: bold;
       }
 /* end of attachment style */
        -->
        </style>
        </head>
<html>
<head>
<style type=3D"text/css">
<!--
#ygrp-mkp {
  border: 1px solid #d8d8d8;
  font-family: Arial;
  margin: 10px 0;
  padding: 0 10px;
}

#ygrp-mkp hr {
  border: 1px solid #d8d8d8;
}

#ygrp-mkp #hd {
  color: #628c2a;
  font-size: 85%;
  font-weight: 700;
  line-height: 122%;
  margin: 10px 0;
}

#ygrp-mkp #ads {
  margin-bottom: 10px;
}

#ygrp-mkp .ad {
  padding: 0 0;
}

#ygrp-mkp .ad p {
  margin: 0;
}

#ygrp-mkp .ad a {
  color: #0000ff;
  text-decoration: none;
}
-->
</style>
</head>
<body>





Have you ever had an issue with PostgreSql in production? =A0I recently use=
d it to build a text index, and it seemed quite good, although slightly slo=
wer than MS SQL on several queries.<div><br></div><div>One trick I like abo=
ut=A0PostgreSql is that it barely buffers db blocks in memory itself. =A0In=
stead it just relies on the O/S to buffer the files. =A0Simple and fast.</d=
iv>

<div><br></div><div>Anthony<br><br><div class=3D"gmail_quote">On Thu, Feb 7=
, 2013 at 2:17 AM, Franck Routier <span dir=3D"ltr">&lt;<a href=3D"mailto:f=
[email protected]" target=3D"_blank">[email protected]</a>&gt;=
</span> wrote:<br>

<blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1p=
x #ccc solid;padding-left:1ex">
=20=20
=20=20=20=20
=20=20
  <div text=3D"#000000" bgcolor=3D"#FFFFFF">
    <div>Hi,<br>
      <br>
      Le 05/02/2013 00:40, anthony berglas a =E9crit=A0:<br>
    </div><div class=3D"im">
    <blockquote type=3D"cite">
      <span>=A0</span>
=20=20=20=20=20=20
          <div><br>
            <div>Again, it amazes me after all these years that Oracle
              does not support these facilities more easily. =A0Why put up
              a barrier for people to migrate to Oracle!</div>
            <br>
          </div>
        </blockquote></div></div>
=20=20=20=20=20=20
=20=20=20=20
    Well, I just discovered another gotcha with this &#39;rownum&#39; appro=
ach.<br>
    <br>
    In this end, we embed a query within one (limit) or two
    (limit/offset) layers of Oracle specific query like this:<br>
    <br>
    select realquery.* from <br>
    (select .... from ....) realquery<br>
    where rownum &lt; limit<br>
    <br>
    This is well, but... Oracle puts another gotcha here.<br>
    <br>
    In Postgresql, you can do this :<br>
    <br>
    select * from (<br>
    select id, id from mytable) realquery;<br>
    <br>
    works fine.<br>
    <br>
    Doing the same in Oracle will result in 00918. 00000 -=A0 &quot;column
    ambiguously defined&quot;.<br>
    You cannot select * from a subquery that as several columns named or
    aliased the same...<br>
    This won&#39;t probably hurt anyone in Simpleorm, except if using a
    rawQuery, with several columns named the same, and with
    limit/offset, under Oracle.<br>
    <br>
    Well, guess what: we do that in our project... I will change this,
    as this has no real interest, but it was an automatically generated
    query, and it was convinient not to bother.<br>
    <br>
    I really prefer Postgresql in many ways. But our customers tend to
    have invested a lot in Oracle....<br>
    <br>
    Regards,<br>
    <br>
    Franck<br>
    <br>
    <br>
=20=20

</blockquote></div><br><br clear=3D"all"><div><br></div>-- <br><p>
</p><p>Dr Anthony Berglas, <a href=3D"mailto:[email protected]" target=3D=
"_blank">[email protected]</a>=A0=A0=A0=A0=A0=A0 Mobile: +61 4 4838 8874<=
br>
Just because it is possible to push twigs along the ground with ones nose<b=
r>
does not necessarily mean that that is the best way to collect firewood.</p=
>
</div>




<!-- |**|begin egp html banner|**| -->

<br>

=20=20=20=20
=20=20=20=20
<br>

<!-- |**|end egp html banner|**| -->


<div width=3D"1" style=3D"color: white; clear: both;"/>__._,_.___</div>

<!-- Start Recommendations -->
<!-- End Recommendations -->



<!-- |**|begin egp html banner|**| -->

  <img src=3D"http://geo.yahoo.com/serv?s=3D97476590/grpId=3D7360226/grpspI=
d=3D1705006905/msgId=3D2080/stime=3D1360213139" width=3D"1" height=3D"1"> <=
br>

<!-- |**|end egp html banner|**| -->

=20=20
<!-- |**|begin egp html banner|**| -->

<br>
      <div style=3D"font-family: verdana; font-size: 77%; border-top: 1px s=
olid #666; padding: 5px 0;" >
      Your email settings: Individual Email|Traditional <br>
      <a href=3D"http://groups.yahoo.com/group/SimpleORM/join;_ylc=3DX3oDMT=
JmYmZyaXRnBF9TAzk3NDc2NTkwBGdycElkAzczNjAyMjYEZ3Jwc3BJZAMxNzA1MDA2OTA1BHNlY=
wNmdHIEc2xrA3N0bmdzBHN0aW1lAzEzNjAyMTMxMzk-">Change settings via the Web</a=
> (Yahoo! ID required) <br>
      Change settings via email: <a href=3D"mailto:SimpleORM-digest@yahoogr=
oups.com?subject=3DEmail Delivery: Digest">Switch delivery to Daily Digest<=
/a> | <a href =3D "mailto:[email protected]?subject=3D=
Change Delivery Format: Fully Featured">Switch to Fully Featured</a> <br>
           <a href=3D"http://groups.yahoo.com/group/SimpleORM;_ylc=3DX3oDMT=
JkdXQ5djZhBF9TAzk3NDc2NTkwBGdycElkAzczNjAyMjYEZ3Jwc3BJZAMxNzA1MDA2OTA1BHNlY=
wNmdHIEc2xrA2hwZgRzdGltZQMxMzYwMjEzMTM5">
        Visit Your Group=20
      </a> |
      <a href=3D"http://docs.yahoo.com/info/terms/">
        Yahoo! Groups Terms of Use
      </a> |
      <a href=3D"mailto:[email protected]?subject=3DUns=
ubscribe">
       Unsubscribe=20
      </a>=20
 <br>
    </div>
  <br>

<!-- |**|end egp html banner|**| -->


<div style=3D"color: white; clear: both;"/>__,_._,___</div>
</body>

</html>

--f46d04479f95d60b6704d51b4ca6--