Re: [Vserver] FW: Oracle 10g... any Production Environments on VServer?
I'm not trying to convince you to switch to PG, I'm just curious if these features are available to me. BTW, one interesting feature that Oracle has is the ability to store hierarchical data in a flat db table and pull it out http://gppl.moonbone.ru/ EOQUOTE WHAT'S THIS This is a patch which allows PgSQL to make hierarchical queries a la Oracle do. EOQUOTE I thought they would include this in 8.x, but they didn't. Oh well, we'll wait another year or two then... -- Key fingerprint = 40D0 9FFB 9939 7320 8294 05E0 BCC7 02C4 75CC 50D9 We're giving you a new chance in life, and an opportunity to screw it up in a new, original way. ___ Vserver mailing list Vserver@list.linux-vserver.org http://list.linux-vserver.org/mailman/listinfo/vserver
[Vserver] FW: Oracle 10g... any Production Environments on VServer?
Sadly, Postgres is missing these important features; - bitmap indexes - OLAP query re-writing I'm a big postgres fan and closely follow the performance mailing list. These features sound intriguing so I'm going to enquire about their status. Interestingly, I've heard recent discussions about both bitmap indexes and materialized views and my belief was that many people on the list are using materialized views now, though I'm not sure if that is provided by a contributed module. I'm not trying to convince you to switch to PG, I'm just curious if these features are available to me. BTW, one interesting feature that Oracle has is the ability to store hierarchical data in a flat db table and pull it out in one query. For example: It can take this data: And sort it *correctly* like this: Id Parent Name Id Name 0 Home 0 Home 1 0 Documents1 Documents 2 0 Applications 4Work 3 0 Pictures 5Personal 4 1 Work 2 Applications 5 1 Personal 6Photoshop 6 2 Photoshop7OpenOffice.org 7 2 OpenOffice.org 3 Pictures 8 3 Family 8Family This takes several queries in PostgreSQL. [Pause (always Google before you post)] Oh, I knew bitmaps were fresh in my memory: http://sql-info.de/postgresql/news/weekly-news_2005-04-24.html ... Victor Y. Yegorov's on-disk bitmap indexes, which are a new index type intended to allow indexing low-cardinality attributes on large tables. The two features together should greatly improve performance of descision support / business intelligence workloads. And: http://jonathangardner.net/PostgreSQL/materialized_views/matviews.html Materialized views are certainly possible in PostgreSQL. Because of PostgreSQL's powerful PL/pgSQL language, and the functional trigger system, materialized views are somewhat easy to implement. Thanks for describing these features; they look like areas where I can improve some aspects of my application. -- Matthew Nuzum [EMAIL PROTECTED] www.followers.net - Makers of Elite Content Management System View samples of Elite CMS in action by visiting http://www.followers.net/portfolio/ ___ Vserver mailing list Vserver@list.linux-vserver.org http://list.linux-vserver.org/mailman/listinfo/vserver
Re: [Vserver] FW: Oracle 10g... any Production Environments on VServer?
Matthew Nuzum wrote: I'm a big postgres fan and closely follow the performance mailing list. These features sound intriguing so I'm going to enquire about their status. Ah, my plan is falling into place... rubs hands together features are available to me. BTW, one interesting feature that Oracle has is the ability to store hierarchical data in a flat db table and pull it out in one query. For example: [...] This takes several queries in PostgreSQL. It sounds great in theory, doesn't it? Then I found out that you can't use it for anything 'useful', for instance by passing in a table column alias to the START WITH from an outer query, which seemed to me the most natural way to use it; select t1.id, t1.name, tn.id as child_id from mytable t1 left join (select t2.id from mytable t2 start with t2.id = t1.id connect by prior id = parent_id ) tn on top_id = t1.id where t1.name like '%foo%'; That's not a valid query; in fact I couldn't really see a way I could use it to generically do 'recursive' joins, to pretend that a heirarchical relationship is a mapping table or something like that, even using views and such trickery. However, it sure is useful for indenting heirarchical results for a single heirarchy in display, like it's EXPLAIN PLAN statement. I've seen this a lot with Oracle. Some feature sounds great, then you try to use it and find it's not as useful as you thought, for a very trivial yet seemingly unsurmountable reason (and I refuse to learn any DB-specific 4GLs ;)). I was dumbfounded when a bug in functional indexes gave me bogus results for a query (if some silly conditions held), and there was simply no patch available for a supposedly stable database. So we had to upgrade to a new major version (and of course we found other bugs there too). Tangram has ways to work around this problem in a DB-independant way, so I'm not particularly worried :). The information you found about these features is interesting; it sure would be great if Pg is maturing enough to be a viable replacement! Thanks for the off-topic banter :) Sam. ___ Vserver mailing list Vserver@list.linux-vserver.org http://list.linux-vserver.org/mailman/listinfo/vserver
