Re: SDriverOracle OffesetStrategy
Franck Routier <[email protected]> Fri, 08 Feb 2013 12:28:54 +0100
| Newsgroups | gmane.comp.java.orm.simpleorm |
|---|---|
| Message-ID | <[email protected]> |
--------------ms060803030104010602090607 Content-Type: multipart/alternative; boundary="------------010401040402050808000005" --------------010401040402050808000005 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: quoted-printable Hi Anthony, no, I never had any single incident in production with Postgresql=20 (approx. 20 real production servers + internal databases). The only point is to have a well tuned routine vacuum task (see=20 http://www.postgresql.org/docs/9.2/static/routine-vacuuming.html). Regards, Franck Le 07/02/2013 05:58, anthony berglas a =E9crit : > > Have you ever had an issue with PostgreSql in production? I recently=20 > used it to build a text index, and it seemed quite good, although=20 > slightly slower than MS SQL on several queries. > > > One trick I like about PostgreSql is that it barely buffers db blocks=20 > in memory itself. Instead it just relies on the O/S to buffer the=20 > files. Simple and fast. > > Anthony > > On Thu, Feb 7, 2013 at 2:17 AM, Franck Routier=20 > <[email protected] <mailto:[email protected]>> wrote: > > 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 this 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] <mailto:[email protected]>=20 > 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. > >=20 --------------010401040402050808000005 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable <html> <head> <meta content=3D"text/html; charset=3DISO-8859-1" http-equiv=3D"Content-Type"> </head> <body text=3D"#000000" bgcolor=3D"#FFFFFF"> <div class=3D"moz-cite-prefix">Hi Anthony,<br> <br> no, I never had any single incident in production with Postgresql (approx. 20 real production servers + internal databases).<br> The only point is to have a well tuned routine vacuum task (see <a class=3D"moz-txt-link-freetext" href=3D"http://www.postgresql.org/= docs/9.2/static/routine-vacuuming.html">http://www.postgresql.org/docs/9.2/= static/routine-vacuuming.html</a>).<br> <br> Regards,<br> <br> Franck<br> <br> Le 07/02/2013 05:58, anthony berglas a écrit :<br> </div> <blockquote cite=3D"mid:[email protected].= com" type=3D"cite"> <span style=3D"display:none"> </span> =20=20=20=20=20=20 <div id=3D"ygrp-text"> <p>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.</p> <div><br> </div> <div>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.= </div> <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"><<a moz-do-not-send=3D"true" href=3D"mailto:[email protected]" target=3D"_blank">[email protected]</a>></spa= n> wrote:<br> <blockquote class=3D"gmail_quote" style=3D"border-left:1px #ccc solid;"> <div text=3D"#000000" bgcolor=3D"#FFFFFF"> <div>Hi,<br> <br> Le 05/02/2013 00:40, anthony berglas a écrit&n= bsp;:<br> </div> <div class=3D"im"> <blockquote type=3D"cite"> <span> </span> <div><br> <div>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!</div> <br> </div> </blockquote> </div> </div> Well, I just discovered another gotcha with this 'rownum' approach.<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 < 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 - "column ambiguously defined".<br> You cannot select * from a subquery that as several columns named or aliased the same...<br> 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.<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> </blockquote> </div> <br> <br clear=3D"all"> <div><br> </div> -- <br> <p> </p> <p>Dr Anthony Berglas, <a moz-do-not-send=3D"true" href=3D"mailto:[email protected]" target=3D"_blank">ant= [email protected]</a> Mobile: +61 4 4838 8874<br> Just because it is possible to push twigs along the ground with ones nose<br> does not necessarily mean that that is the best way to collect firewood.</p> </div> </div> =20=20=20=20=20=20=20=20=20=20 =20=20=20=20=20=20 <!-- end group email --> </blockquote> <br> </body> </html> --------------010401040402050808000005-- --------------ms060803030104010602090607 Content-Type: application/pkcs7-signature; name="smime.p7s" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="smime.p7s" Content-Description: Signature cryptographique S/MIME MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEHAQAAoIINYjCC BjQwggQcoAMCAQICAR4wDQYJKoZIhvcNAQEFBQAwfTELMAkGA1UEBhMCSUwxFjAUBgNVBAoT DVN0YXJ0Q29tIEx0ZC4xKzApBgNVBAsTIlNlY3VyZSBEaWdpdGFsIENlcnRpZmljYXRlIFNp Z25pbmcxKTAnBgNVBAMTIFN0YXJ0Q29tIENlcnRpZmljYXRpb24gQXV0aG9yaXR5MB4XDTA3 MTAyNDIxMDE1NVoXDTE3MTAyNDIxMDE1NVowgYwxCzAJBgNVBAYTAklMMRYwFAYDVQQKEw1T dGFydENvbSBMdGQuMSswKQYDVQQLEyJTZWN1cmUgRGlnaXRhbCBDZXJ0aWZpY2F0ZSBTaWdu aW5nMTgwNgYDVQQDEy9TdGFydENvbSBDbGFzcyAxIFByaW1hcnkgSW50ZXJtZWRpYXRlIENs aWVudCBDQTCCASIwDQYJKoZIhvcNAQEBBQADggEPADCCAQoCggEBAMcJg8zOLdgasSmkLhOr lr6KMoOMpohBllVHrdRvEg/q6r8jR+EK75xCGhR8ToREoqe7zM9/UnC6TS2y9UKTpT1v7RSM zR0t6ndl0TWBuUr/UXBhPk+Kmy7bI4yW4urC+y7P3/1/X7U8ocb8VpH/Clt+4iq7nirMcNh6 qJR+xjOhV+VHzQMALuGYn5KZmc1NbJQYclsGkDxDz2UbFqE2+6vIZoL+jb9x4Pa5gNf1TwSD kOkikZB1xtB4ZqtXThaABSONdfmv/Z1pua3FYxnCFmdr/+N2JLKutIxMYqQOJebr/f/h5t95 m4JgrM3Y/w7YX9d7YAL9jvN4SydHsU6n65cCAwEAAaOCAa0wggGpMA8GA1UdEwEB/wQFMAMB Af8wDgYDVR0PAQH/BAQDAgEGMB0GA1UdDgQWBBRTcu2SnODaywFcfH6WNU7y1LhRgjAfBgNV HSMEGDAWgBROC+8apEBbpRdphzDKNGhD0EGu8jBmBggrBgEFBQcBAQRaMFgwJwYIKwYBBQUH MAGGG2h0dHA6Ly9vY3NwLnN0YXJ0c3NsLmNvbS9jYTAtBggrBgEFBQcwAoYhaHR0cDovL3d3 dy5zdGFydHNzbC5jb20vc2ZzY2EuY3J0MFsGA1UdHwRUMFIwJ6AloCOGIWh0dHA6Ly93d3cu c3RhcnRzc2wuY29tL3Nmc2NhLmNybDAnoCWgI4YhaHR0cDovL2NybC5zdGFydHNzbC5jb20v c2ZzY2EuY3JsMIGABgNVHSAEeTB3MHUGCysGAQQBgbU3AQIBMGYwLgYIKwYBBQUHAgEWImh0 dHA6Ly93d3cuc3RhcnRzc2wuY29tL3BvbGljeS5wZGYwNAYIKwYBBQUHAgEWKGh0dHA6Ly93 d3cuc3RhcnRzc2wuY29tL2ludGVybWVkaWF0ZS5wZGYwDQYJKoZIhvcNAQEFBQADggIBAAqD CH14qywGXLhjjF6uHLkjd02hcdh9hrw+VUsv+q1eeQWB21jWj3kJ96AUlPCoEGZ/ynJNScWy 6QMVQjbbMXltUfO4n4bGGdKo3awPWp61tjAFgraLJgDk+DsSvUD6EowjMTNx25GQgyYJ5RPI zKKR9tQW8gGK+2+RHxkUCTbYFnL6kl8Ch507rUdPPipJ9CgJFws3kDS3gOS5WFMxcjO5DwKf KSETEPrHh7p5shuuNktvsv6hxHTLhiMKX893gxdT3XLS9OKmCv87vkINQcNEcIIoFWbP9HOR z9v3vQwR4e3ksLc2JZOAFK+ssS5XMEoznzpihEP0PLc4dCBYjbvSD7kxgDwZ+Aj8Q9PkbvE9 sIPP7ON0fz095HdThKjiVJe6vofq+n6b1NBc8XdrQvBmunwxD5nvtTW4vtN6VY7mUCmxsCie uoBJ9OlqmsVWQvifIYf40dJPZkk9YgGTzWLpXDSfLSplbY2LL9C9U0ptvjcDjefLTvqSFc7t w1sEhF0n/qpA2r0GpvkLRDmcSwVyPvmjFBGqUp/pNy8ZuPGQmHwFi2/14+xeSUDG2bwnsYJQ G2EdJCB6luQ57GEnTA/yKZSTKI8dDQa8Sd3zfXb19mOgSF0bBdXbuKhEpuP9wirslFe6fQ1t 5j5R0xi72MZ8ikMu1RQZKCyDbMwazlHiMIIHJjCCBg6gAwIBAgIDBNFnMA0GCSqGSIb3DQEB BQUAMIGMMQswCQYDVQQGEwJJTDEWMBQGA1UEChMNU3RhcnRDb20gTHRkLjErMCkGA1UECxMi U2VjdXJlIERpZ2l0YWwgQ2VydGlmaWNhdGUgU2lnbmluZzE4MDYGA1UEAxMvU3RhcnRDb20g Q2xhc3MgMSBQcmltYXJ5IEludGVybWVkaWF0ZSBDbGllbnQgQ0EwHhcNMTIwODI5MjAxNTIz WhcNMTMwODMxMDg0MTExWjBnMRkwFwYDVQQNExBmQ0cxTUdlTUM1dkZVNVlKMSEwHwYDVQQD DBhmcmFuY2sucm91dGllckBheGVnZS5jb20xJzAlBgkqhkiG9w0BCQEWGGZyYW5jay5yb3V0 aWVyQGF4ZWdlLmNvbTCCASIwDQYJKoZIhvcNAQEBBQADggEPADCCAQoCggEBAK5kIRjbP769 72PM00wgFczTGqmiU5dAY8XlSziUNq434SJZ3rFZoL0MY6hqVb/p5+u+u6TdhsVywJclPaZE v+FgGtk74JvlX6oeSWJTOiGLTrk0TRwy7Q+80kbneFdhL+yk/ZTxxcj15IEOeWMuyG/IyePq H4GtZMkCpsuCOL5bkq9g/H0JHyLSM0tWDbMuD4vKbSeBkzwiz4pSwuANCFMFeRr3d9iZQU0P VzyKvu/cEUcdoDcVGMuMxX6FwgqrS5c70v/RPM8FafOrqprYfIxVQwxK9IhPgqM9kT6riQo6 OM62d2a21Y6iAeWe/1rssTKn+Stj8jH706LmVJlEGtcCAwEAAaOCA7MwggOvMAkGA1UdEwQC MAAwCwYDVR0PBAQDAgSwMB0GA1UdJQQWMBQGCCsGAQUFBwMCBggrBgEFBQcDBDAdBgNVHQ4E FgQUCfIkqGdMRvMTjM6TIpdcUPqdYW8wHwYDVR0jBBgwFoAUU3Ltkpzg2ssBXHx+ljVO8tS4 UYIwIwYDVR0RBBwwGoEYZnJhbmNrLnJvdXRpZXJAYXhlZ2UuY29tMIICIQYDVR0gBIICGDCC AhQwggIQBgsrBgEEAYG1NwECAjCCAf8wLgYIKwYBBQUHAgEWImh0dHA6Ly93d3cuc3RhcnRz c2wuY29tL3BvbGljeS5wZGYwNAYIKwYBBQUHAgEWKGh0dHA6Ly93d3cuc3RhcnRzc2wuY29t L2ludGVybWVkaWF0ZS5wZGYwgfcGCCsGAQUFBwICMIHqMCcWIFN0YXJ0Q29tIENlcnRpZmlj YXRpb24gQXV0aG9yaXR5MAMCAQEagb5UaGlzIGNlcnRpZmljYXRlIHdhcyBpc3N1ZWQgYWNj b3JkaW5nIHRvIHRoZSBDbGFzcyAxIFZhbGlkYXRpb24gcmVxdWlyZW1lbnRzIG9mIHRoZSBT dGFydENvbSBDQSBwb2xpY3ksIHJlbGlhbmNlIG9ubHkgZm9yIHRoZSBpbnRlbmRlZCBwdXJw b3NlIGluIGNvbXBsaWFuY2Ugb2YgdGhlIHJlbHlpbmcgcGFydHkgb2JsaWdhdGlvbnMuMIGc BggrBgEFBQcCAjCBjzAnFiBTdGFydENvbSBDZXJ0aWZpY2F0aW9uIEF1dGhvcml0eTADAgEC GmRMaWFiaWxpdHkgYW5kIHdhcnJhbnRpZXMgYXJlIGxpbWl0ZWQhIFNlZSBzZWN0aW9uICJM ZWdhbCBhbmQgTGltaXRhdGlvbnMiIG9mIHRoZSBTdGFydENvbSBDQSBwb2xpY3kuMDYGA1Ud HwQvMC0wK6ApoCeGJWh0dHA6Ly9jcmwuc3RhcnRzc2wuY29tL2NydHUxLWNybC5jcmwwgY4G CCsGAQUFBwEBBIGBMH8wOQYIKwYBBQUHMAGGLWh0dHA6Ly9vY3NwLnN0YXJ0c3NsLmNvbS9z dWIvY2xhc3MxL2NsaWVudC9jYTBCBggrBgEFBQcwAoY2aHR0cDovL2FpYS5zdGFydHNzbC5j b20vY2VydHMvc3ViLmNsYXNzMS5jbGllbnQuY2EuY3J0MCMGA1UdEgQcMBqGGGh0dHA6Ly93 d3cuc3RhcnRzc2wuY29tLzANBgkqhkiG9w0BAQUFAAOCAQEAGSGlokCrFniLxjc6W072LqWy ISRKEqeC9u0pYSoQGVPAy1mNbu28ThrfAk8umOfFEDkzVH0TXH33TbsCViwsE/rCk6FC0U+Q AMOn1Ajjf/9z7w8+zCHgmbLyABtYrmBO6AhCD7YLKYPE1VXkJvgdcHCKzVLIkvdIyKSkRn+R lSorTXhg64lR8vQSX8C9EAY0T/hQyjFS9SDcEhsfOjkYdAiG7fVBft24Gj+3oDgk2479W47l phG1aHhkflrDeho0h6LnGxY0+PATPuUkvDsr/RFE2VviGFH9UDxjNJmikrmTibCvS4seQFOm ppHmJzqIcxcn5dqeBDVQYZmLhDkszjGCA90wggPZAgEBMIGUMIGMMQswCQYDVQQGEwJJTDEW MBQGA1UEChMNU3RhcnRDb20gTHRkLjErMCkGA1UECxMiU2VjdXJlIERpZ2l0YWwgQ2VydGlm aWNhdGUgU2lnbmluZzE4MDYGA1UEAxMvU3RhcnRDb20gQ2xhc3MgMSBQcmltYXJ5IEludGVy bWVkaWF0ZSBDbGllbnQgQ0ECAwTRZzAJBgUrDgMCGgUAoIICHTAYBgkqhkiG9w0BCQMxCwYJ KoZIhvcNAQcBMBwGCSqGSIb3DQEJBTEPFw0xMzAyMDgxMTI4NTRaMCMGCSqGSIb3DQEJBDEW BBQGBS2HRVWAUt3X53moEZVtC1z0bjBsBgkqhkiG9w0BCQ8xXzBdMAsGCWCGSAFlAwQBKjAL BglghkgBZQMEAQIwCgYIKoZIhvcNAwcwDgYIKoZIhvcNAwICAgCAMA0GCCqGSIb3DQMCAgFA MAcGBSsOAwIHMA0GCCqGSIb3DQMCAgEoMIGlBgkrBgEEAYI3EAQxgZcwgZQwgYwxCzAJBgNV BAYTAklMMRYwFAYDVQQKEw1TdGFydENvbSBMdGQuMSswKQYDVQQLEyJTZWN1cmUgRGlnaXRh bCBDZXJ0aWZpY2F0ZSBTaWduaW5nMTgwNgYDVQQDEy9TdGFydENvbSBDbGFzcyAxIFByaW1h cnkgSW50ZXJtZWRpYXRlIENsaWVudCBDQQIDBNFnMIGnBgsqhkiG9w0BCRACCzGBl6CBlDCB jDELMAkGA1UEBhMCSUwxFjAUBgNVBAoTDVN0YXJ0Q29tIEx0ZC4xKzApBgNVBAsTIlNlY3Vy ZSBEaWdpdGFsIENlcnRpZmljYXRlIFNpZ25pbmcxODA2BgNVBAMTL1N0YXJ0Q29tIENsYXNz IDEgUHJpbWFyeSBJbnRlcm1lZGlhdGUgQ2xpZW50IENBAgME0WcwDQYJKoZIhvcNAQEBBQAE ggEAqedWE4LeByeu5yLje8x32GGDEDa8bNhZWlLacsM4IDfv0hb/Ed6xF3wxJpryx1Dr2k3c lNxHWZHMlCimqi6M1sUQEyyxPbTwRvKgMRgrLAzo8dWrM35Eq2RS5whsK2bDssTnxwdfaP/P dcOzzUgAIlJSfMg85BPDoEUExreem5p0/MG/nmrtXTJ9JCmYNNNhDnf5F1keakMcaiLNg9eR 9cmizeGolsSB/GAHdkg2SWX7GEepB+e1UHFpcsmPmsVY4EJOkhY02vTT9MrauVGsm3U4/Bw1 6DUPbMNjOeTvu9szdyI1cD/39BEtDllp3iEK6N7fD3z4VMm7WcZzjYW81wAAAAAAAA== --------------ms060803030104010602090607--