June 2010 - developers - lists.mariadb.org

[Maria-developers] Ideas for improving MariaDB/MySQL replication
by Kristian Nielsen 05 Jul '10

05 Jul '10

The three companies Continuent, Codership, and Monty Program are planning to start working on some enhancements to the replication system in MariaDB, together with anyone interested in joining in. At this stage, there are no fixed directions for the project, and to do this in as open a way possible with the maximum community involvement and interest, we agreed to start with an email discussion on the maria-developers@ mailing list. So consider it started! The plan so far is: 1) The parties taking this initiative, MP, Continuent, and Codership, present their own ideas in this thread on maria-developers@ (and everyone else who wants to chime in at this stage). 2) Once we have some concrete suggestions as a starting point, we use this to reach out in a broader way with some blog posts on planetmysql / planetmariadb to encourage further input and discussions for possible directions of the project. Eventually we want to end up with a list of the most important goals and a possible roadmap for replication enhancements. (It is best to have something concrete as a basis of a broad community discussion/process). To start of, here are some points of interest that I collected. Everyone please chime in with your own additional points, as well as comments and further details on these one. Three areas in particular seem to be of high interest in the community currently (not excluding any other areas): - High Availability * Most seems to focus on building HA solutions on top of MySQL replication, eg. MMM and Tungsten. * For this project, seems mainly to be to implement improvements to replication that help facilitate improving these on-top HA solutions. * Tools to automate (or help automate) failover from a master. * Better facilities to do initial setup of new slave without downtime, or re-sync of an old master or slave that has been outside of the replication topology for some period of time. - Performance, especially scalability * Multi-threaded slave SQL thread. * Store the binlog inside a transactional engine (eg. InnoDB) to reduce I/O, solve problems like group commit, and simplify crash recovery. - More pluggable replication * Make the replication code and APIs be more suitable for people to build extra functionality into or on top of the stock MySQL replication. * Better documentation of C++ APIs and binlog format. * Adding extra information to binlog that can be useful for non-standard replication stuff. For example column names (for RBR), checksums. * Refactoring the server code to be more modular with APIs more suitable for external usage. * Add support for replication plugins, types to be determined. For example binlog filtering plugins? It is also very important to consider the work that the replication team at MySQL is doing (and has done). I found a good deal of interesting information about this here: http://forge.mysql.com/wiki/MySQL_Replication:_Walk-through_of_the_new_5.1_…) This describes a number of 6.0/5.4 and preview features that we could merge and/or contribute to. Here are the highlights that I found: - Features included in 6.0/5.4 (which are cancelled I think, but presumably this will go in a milestone release): * CHANGE MASTER ... IGNORE_SERVER_IDS for better support of circular replication. * Replication heartbeat. * sync_relay_log_info, sync_master_info, sync_relay_log, relay_log_recovery for crash recovery on slave. * Binlog Performance Optimization (lock contention improvement). * Semi-synchronous Replication, with Pluggable Replication Architecture. http://forge.mysql.com/wiki/ReplicationFeatures/SemiSyncReplication - Feature previews: * Parallel slave application: WL#4648 http://forge.mysql.com/wiki/ReplicationFeatures/ParallelSlave * Time-delayed replication: WL#344 http://forge.mysql.com/wiki/ReplicationFeatures/DelayedReplication * Scriptable Replication: WL#4008 http://forge.mysql.com/wiki/ReplicationFeatures/ScriptableReplication * Synchronous Replication. Drizzle is also doing work on a new replication system. I read through the series of blog posts that Jay Pipes wrote on this subject. They mostly deal with how this is designed in terms of the Drizzle server code, and is low on detail about how the replication will actually work (the only thing I could really extract was that it is a form of row-based replication). If someone has links to information about this that I missed, it could be interesting. Let the discussion begin! - Kristian.

13 67

[Maria-developers] [5.3 merge] Item_in_subselect::init_left_expr_cache() question.
by Sergey Petrunya 03 Jul '10

03 Jul '10

Hi Timour, I'm having difficulties with finishing 5.2->5.3 merge. Could you please take a look at https://bugs.launchpad.net/maria/+bug/598972 ? The questions are in the bug entry. BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 1

[Maria-developers] WL#122 New (by Sergei): fix locking in XA tc_log
by worklog-noreply＠askmonty.org 30 Jun '10

30 Jun '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: fix locking in XA tc_log CREATION DATE..: Wed, 30 Jun 2010, 17:26 SUPERVISOR.....: Sergei IMPLEMENTOR....: Sergei COPIES TO......: CATEGORY.......: Server-Sprint TASK ID........: 122 (http://askmonty.org/worklog/?tid=122) VERSION........: Server-5.1 STATUS.........: Assigned PRIORITY.......: 90 WORKED HOURS...: 0 ESTIMATE.......: 8 (hours remain) ORIG. ESTIMATE.: 8 PROGRESS NOTES: DESCRIPTION: We need to analyze and fix the mutex lock order in TC_LOG code of the XA. It's needed to fix the https://bugs.launchpad.net/maria/+bug/578117 ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0

[Maria-developers] WL#99 Updated (by Sergei): dummy test task
by worklog-noreply＠askmonty.org 30 Jun '10

30 Jun '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: dummy test task CREATION DATE..: Thu, 04 Mar 2010, 07:18 SUPERVISOR.....: Knielsen IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Client-BackLog TASK ID........: 99 (http://askmonty.org/worklog/?tid=99) VERSION........: Server-9.x STATUS.........: Cancelled PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Sergei - Wed, 30 Jun 2010, 16:27)=-=- Version updated. --- /tmp/wklog.99.old.30336 2010-06-30 16:27:06.000000000 +0000 +++ /tmp/wklog.99.new.30336 2010-06-30 16:27:06.000000000 +0000 @@ -1 +1 @@ -Benchmarks-3.0 +Server-9.x -=-=(Sergei - Wed, 30 Jun 2010, 16:27)=-=- Status updated. --- /tmp/wklog.99.old.30336 2010-06-30 16:27:06.000000000 +0000 +++ /tmp/wklog.99.new.30336 2010-06-30 16:27:06.000000000 +0000 @@ -1 +1 @@ -Un-Assigned +Cancelled DESCRIPTION: test missing estimate ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0

[Maria-developers] Install MariaDB as a Windows service in the installer
by Bo Thorsen 30 Jun '10

30 Jun '10

Hi everyone, This patch adds a page to the Windows installer that asks the user if he wants to set up MariaDB as a Windows service. The way to do this is to hack the NSIS template for CPack. IMHO, this is a bad way, but it is the recommended (and only) way to do stuff like this. The patch in the attached file install-service.patch is the only thing necessary for the current sources. It's a one liner. So far so good :) The actual patch adds a file win\cmake\NSIS.template.in which is a copy of the NSIS.template.in from CMake. The attached patch NSIS.template.in.patch shows the diff with the code I have added. Bo Thorsen. Monty Program AB. -- MariaDB: MySQL replacement Community developed. Feature enhanced. Backward compatible.

2 1

[Maria-developers] WL#85 Updated (by Sergei): Partitioned Key Cache for MyISAM
by worklog-noreply＠askmonty.org 29 Jun '10

29 Jun '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Partitioned Key Cache for MyISAM CREATION DATE..: Sun, 14 Feb 2010, 00:10 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: Igor, Monty, Sergei CATEGORY.......: Server-Sprint TASK ID........: 85 (http://askmonty.org/worklog/?tid=85) VERSION........: Server-5.2 STATUS.........: Complete PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 80 (hours remain) ORIG. ESTIMATE.: 80 PROGRESS NOTES: -=-=(Sergei - Tue, 29 Jun 2010, 14:05)=-=- Status updated. --- /tmp/wklog.85.old.32131 2010-06-29 14:05:44.000000000 +0000 +++ /tmp/wklog.85.new.32131 2010-06-29 14:05:44.000000000 +0000 @@ -1 +1 @@ -Assigned +Complete -=-=(Igor - Tue, 16 Mar 2010, 19:34)=-=- High Level Description modified. --- /tmp/wklog.85.old.22371 2010-03-16 19:34:33.000000000 +0000 +++ /tmp/wklog.85.new.22371 2010-03-16 19:34:33.000000000 +0000 @@ -15,4 +15,5 @@ the chances for threads not compete for the same key cache lock better. The idea and the original of the partitioned key cache was provided by one of -our external contributers. +our external contributers (see the attached file segmented_keycache_v2.diff with +the original patch from the contributor). -=-=(Igor - Sun, 14 Feb 2010, 00:15)=-=- Category updated. --- /tmp/wklog.85.old.9810 2010-02-13 22:15:43.000000000 +0000 +++ /tmp/wklog.85.new.9810 2010-02-13 22:15:43.000000000 +0000 @@ -1 +1 @@ -Server-BackLog +Server-Sprint -=-=(Igor - Sun, 14 Feb 2010, 00:15)=-=- Version updated. --- /tmp/wklog.85.old.9810 2010-02-13 22:15:43.000000000 +0000 +++ /tmp/wklog.85.new.9810 2010-02-13 22:15:43.000000000 +0000 @@ -1 +1 @@ -Benchmarks-3.0 +Server-5.2 -=-=(Igor - Sun, 14 Feb 2010, 00:12)=-=- New attachment: 'segmented_keycache_v2.diff' DESCRIPTION: A partitioned key cache is a collection of structures for regular MyiSAM key caches called key cache partitions. Any page from a file can be placed into a buffer of only one partition. The number of the partition is calculated from the file number and the position of the page in the file, and it's always the same for the page. The function that maps pages into partitions takes care of even distribution of pages among partitions. Partition key cache mitigate one of the major problem of simple key cache: thread contention for key cache lock (mutex). Every call of a key cache interface function must acquire this lock. So threads compete for this lock even in the case when they have acquired shared locks for the file and pages they want read from are in the key cache buffers. When working with a partitioned key cache any key cache interface function that needs only one page has to acquire the key cache lock only for the partition the page is ascribed to. This makes the chances for threads not compete for the same key cache lock better. The idea and the original of the partitioned key cache was provided by one of our external contributers (see the attached file segmented_keycache_v2.diff with the original patch from the contributor). ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0

[Maria-developers] WL#85 Updated (by Sergei): Partitioned Key Cache for MyISAM
by worklog-noreply＠askmonty.org 29 Jun '10

29 Jun '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Partitioned Key Cache for MyISAM CREATION DATE..: Sun, 14 Feb 2010, 00:10 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: Igor, Monty, Sergei CATEGORY.......: Server-Sprint TASK ID........: 85 (http://askmonty.org/worklog/?tid=85) VERSION........: Server-5.2 STATUS.........: Complete PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 80 (hours remain) ORIG. ESTIMATE.: 80 PROGRESS NOTES: -=-=(Sergei - Tue, 29 Jun 2010, 14:05)=-=- Status updated. --- /tmp/wklog.85.old.32131 2010-06-29 14:05:44.000000000 +0000 +++ /tmp/wklog.85.new.32131 2010-06-29 14:05:44.000000000 +0000 @@ -1 +1 @@ -Assigned +Complete -=-=(Igor - Tue, 16 Mar 2010, 19:34)=-=- High Level Description modified. --- /tmp/wklog.85.old.22371 2010-03-16 19:34:33.000000000 +0000 +++ /tmp/wklog.85.new.22371 2010-03-16 19:34:33.000000000 +0000 @@ -15,4 +15,5 @@ the chances for threads not compete for the same key cache lock better. The idea and the original of the partitioned key cache was provided by one of -our external contributers. +our external contributers (see the attached file segmented_keycache_v2.diff with +the original patch from the contributor). -=-=(Igor - Sun, 14 Feb 2010, 00:15)=-=- Category updated. --- /tmp/wklog.85.old.9810 2010-02-13 22:15:43.000000000 +0000 +++ /tmp/wklog.85.new.9810 2010-02-13 22:15:43.000000000 +0000 @@ -1 +1 @@ -Server-BackLog +Server-Sprint -=-=(Igor - Sun, 14 Feb 2010, 00:15)=-=- Version updated. --- /tmp/wklog.85.old.9810 2010-02-13 22:15:43.000000000 +0000 +++ /tmp/wklog.85.new.9810 2010-02-13 22:15:43.000000000 +0000 @@ -1 +1 @@ -Benchmarks-3.0 +Server-5.2 -=-=(Igor - Sun, 14 Feb 2010, 00:12)=-=- New attachment: 'segmented_keycache_v2.diff' DESCRIPTION: A partitioned key cache is a collection of structures for regular MyiSAM key caches called key cache partitions. Any page from a file can be placed into a buffer of only one partition. The number of the partition is calculated from the file number and the position of the page in the file, and it's always the same for the page. The function that maps pages into partitions takes care of even distribution of pages among partitions. Partition key cache mitigate one of the major problem of simple key cache: thread contention for key cache lock (mutex). Every call of a key cache interface function must acquire this lock. So threads compete for this lock even in the case when they have acquired shared locks for the file and pages they want read from are in the key cache buffers. When working with a partitioned key cache any key cache interface function that needs only one page has to acquire the key cache lock only for the partition the page is ascribed to. This makes the chances for threads not compete for the same key cache lock better. The idea and the original of the partitioned key cache was provided by one of our external contributers (see the attached file segmented_keycache_v2.diff with the original patch from the contributor). ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0

[Maria-developers] WL#85 Updated (by Sergei): Partitioned Key Cache for MyISAM
by worklog-noreply＠askmonty.org 29 Jun '10

29 Jun '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Partitioned Key Cache for MyISAM CREATION DATE..: Sun, 14 Feb 2010, 00:10 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: Igor, Monty, Sergei CATEGORY.......: Server-Sprint TASK ID........: 85 (http://askmonty.org/worklog/?tid=85) VERSION........: Server-5.2 STATUS.........: Complete PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 80 (hours remain) ORIG. ESTIMATE.: 80 PROGRESS NOTES: -=-=(Sergei - Tue, 29 Jun 2010, 14:05)=-=- Status updated. --- /tmp/wklog.85.old.32131 2010-06-29 14:05:44.000000000 +0000 +++ /tmp/wklog.85.new.32131 2010-06-29 14:05:44.000000000 +0000 @@ -1 +1 @@ -Assigned +Complete -=-=(Igor - Tue, 16 Mar 2010, 19:34)=-=- High Level Description modified. --- /tmp/wklog.85.old.22371 2010-03-16 19:34:33.000000000 +0000 +++ /tmp/wklog.85.new.22371 2010-03-16 19:34:33.000000000 +0000 @@ -15,4 +15,5 @@ the chances for threads not compete for the same key cache lock better. The idea and the original of the partitioned key cache was provided by one of -our external contributers. +our external contributers (see the attached file segmented_keycache_v2.diff with +the original patch from the contributor). -=-=(Igor - Sun, 14 Feb 2010, 00:15)=-=- Category updated. --- /tmp/wklog.85.old.9810 2010-02-13 22:15:43.000000000 +0000 +++ /tmp/wklog.85.new.9810 2010-02-13 22:15:43.000000000 +0000 @@ -1 +1 @@ -Server-BackLog +Server-Sprint -=-=(Igor - Sun, 14 Feb 2010, 00:15)=-=- Version updated. --- /tmp/wklog.85.old.9810 2010-02-13 22:15:43.000000000 +0000 +++ /tmp/wklog.85.new.9810 2010-02-13 22:15:43.000000000 +0000 @@ -1 +1 @@ -Benchmarks-3.0 +Server-5.2 -=-=(Igor - Sun, 14 Feb 2010, 00:12)=-=- New attachment: 'segmented_keycache_v2.diff' DESCRIPTION: A partitioned key cache is a collection of structures for regular MyiSAM key caches called key cache partitions. Any page from a file can be placed into a buffer of only one partition. The number of the partition is calculated from the file number and the position of the page in the file, and it's always the same for the page. The function that maps pages into partitions takes care of even distribution of pages among partitions. Partition key cache mitigate one of the major problem of simple key cache: thread contention for key cache lock (mutex). Every call of a key cache interface function must acquire this lock. So threads compete for this lock even in the case when they have acquired shared locks for the file and pages they want read from are in the key cache buffers. When working with a partitioned key cache any key cache interface function that needs only one page has to acquire the key cache lock only for the partition the page is ascribed to. This makes the chances for threads not compete for the same key cache lock better. The idea and the original of the partitioned key cache was provided by one of our external contributers (see the attached file segmented_keycache_v2.diff with the original patch from the contributor). ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0

[Maria-developers] WL#112 Updated (by Sergei): Merge OQGraph into MariaDB
by worklog-noreply＠askmonty.org 29 Jun '10

29 Jun '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Merge OQGraph into MariaDB CREATION DATE..: Mon, 29 Mar 2010, 18:00 SUPERVISOR.....: Knielsen IMPLEMENTOR....: Knielsen COPIES TO......: CATEGORY.......: Server-Sprint TASK ID........: 112 (http://askmonty.org/worklog/?tid=112) VERSION........: Server-5.2 STATUS.........: Complete PRIORITY.......: 60 WORKED HOURS...: 13 ESTIMATE.......: 2 (hours remain) ORIG. ESTIMATE.: 15 PROGRESS NOTES: -=-=(Sergei - Tue, 29 Jun 2010, 14:04)=-=- Status updated. --- /tmp/wklog.112.old.32115 2010-06-29 14:04:40.000000000 +0000 +++ /tmp/wklog.112.new.32115 2010-06-29 14:04:40.000000000 +0000 @@ -1 +1 @@ -Code-Review +Complete -=-=(Knielsen - Tue, 06 Apr 2010, 15:28)=-=- Fixed all issues from first code review. Implement packaging for OQGraph in bakery. Set up buildbot hosts for including OQGraph, including binary packaging. -=-=(Knielsen - Wed, 31 Mar 2010, 13:38)=-=- Status updated. --- /tmp/wklog.112.old.12166 2010-03-31 13:38:25.000000000 +0000 +++ /tmp/wklog.112.new.12166 2010-03-31 13:38:25.000000000 +0000 @@ -1 +1 @@ -Assigned +Code-Review -=-=(Knielsen - Wed, 31 Mar 2010, 13:38)=-=- High-Level Specification modified. --- /tmp/wklog.112.old.12070 2010-03-31 13:38:08.000000000 +0000 +++ /tmp/wklog.112.new.12070 2010-03-31 13:38:08.000000000 +0000 @@ -15,3 +15,5 @@ Fix OQGraph plug.in to detect boost version >= 1.40.0, and only enable OQGraph if such boost is found. +Update the packaging in ourdelta/bakery to include the oqgraph_engine.so and +link with g++ rather than gcc. -=-=(Knielsen - Mon, 29 Mar 2010, 21:46)=-=- High-Level Specification modified. --- /tmp/wklog.112.old.31142 2010-03-29 21:46:11.000000000 +0000 +++ /tmp/wklog.112.new.31142 2010-03-29 21:46:11.000000000 +0000 @@ -1,28 +1,17 @@ Tasks: -Find the latest version of OQGraph to base this on (there should be a -Launchpad branch somewhere, match it up with what is in the OQGraph patch for -MySQL 5.0 in the ourdelta stuff). - -Extract the correct version of Boost from the MySQL 5.0 ourdelta patch. This -is a patched version of Boost fixing a bug that is supposedly fatal for -OQGraph (details are not known at the time of writing). +Base work on the Launchpad branch lp:~knielsen/maria/mariadb-5.1-oqgraph -Document in OQGraph README the need for boost of a specific version, and point -to where it can be obtained. Also include the patch for boost if the correct -base version of boost to do this against can be determined. +OQGraph requires Boost >= 1.40.0 (earlier versions have a bug that affects +OQGraph). -Install the patched boost in /usr/local/ on the build machines (release builds -and selected Buildbot slaves). +Document in OQGraph README the need for boost of a specific version, and point +to where it can be obtained. -Fix OQGraph plug.in to detect correct version of OQGraph that makes the build -not break. Check which version in Ubuntu starts working (I think it was -Jaunty), and require at least that version. - -Setup some repository or source tarball of the patched boost -somewhere. Preferably a Launchpad branch or similar (if upstream project can -be found). +Install the patched boost in /usr/local/include/boost on the build machines +(release builds and selected Buildbot slaves). G++ seems to by default look in +/usr/local/include, so that is sufficient to find it. -Setup in plug.in or /configure.in appropriate --with-boost=xxx. Or in a pinch, -we can make do with CFLAGS=-Ixxx, or even default look in /usr/local/. +Fix OQGraph plug.in to detect boost version >= 1.40.0, and only enable OQGraph +if such boost is found. -=-=(Knielsen - Mon, 29 Mar 2010, 18:09)=-=- High-Level Specification modified. --- /tmp/wklog.112.old.23061 2010-03-29 18:09:27.000000000 +0000 +++ /tmp/wklog.112.new.23061 2010-03-29 18:09:27.000000000 +0000 @@ -1 +1,28 @@ +Tasks: + +Find the latest version of OQGraph to base this on (there should be a +Launchpad branch somewhere, match it up with what is in the OQGraph patch for +MySQL 5.0 in the ourdelta stuff). + +Extract the correct version of Boost from the MySQL 5.0 ourdelta patch. This +is a patched version of Boost fixing a bug that is supposedly fatal for +OQGraph (details are not known at the time of writing). + +Document in OQGraph README the need for boost of a specific version, and point +to where it can be obtained. Also include the patch for boost if the correct +base version of boost to do this against can be determined. + +Install the patched boost in /usr/local/ on the build machines (release builds +and selected Buildbot slaves). + +Fix OQGraph plug.in to detect correct version of OQGraph that makes the build +not break. Check which version in Ubuntu starts working (I think it was +Jaunty), and require at least that version. + +Setup some repository or source tarball of the patched boost +somewhere. Preferably a Launchpad branch or similar (if upstream project can +be found). + +Setup in plug.in or /configure.in appropriate --with-boost=xxx. Or in a pinch, +we can make do with CFLAGS=-Ixxx, or even default look in /usr/local/. DESCRIPTION: Get the OQGraph storage engine merged into MariaDB, fixing the remaining problems blocking the merge. HIGH-LEVEL SPECIFICATION: Tasks: Base work on the Launchpad branch lp:~knielsen/maria/mariadb-5.1-oqgraph OQGraph requires Boost >= 1.40.0 (earlier versions have a bug that affects OQGraph). Document in OQGraph README the need for boost of a specific version, and point to where it can be obtained. Install the patched boost in /usr/local/include/boost on the build machines (release builds and selected Buildbot slaves). G++ seems to by default look in /usr/local/include, so that is sufficient to find it. Fix OQGraph plug.in to detect boost version >= 1.40.0, and only enable OQGraph if such boost is found. Update the packaging in ourdelta/bakery to include the oqgraph_engine.so and link with g++ rather than gcc. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0

[Maria-developers] WL#11 Updated (by Sergei): Connect by
by worklog-noreply＠askmonty.org 29 Jun '10

29 Jun '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Connect by CREATION DATE..: Thu, 26 Mar 2009, 00:30 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-BackLog TASK ID........: 11 (http://askmonty.org/worklog/?tid=11) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 220 (hours remain) ORIG. ESTIMATE.: 220 PROGRESS NOTES: -=-=(Sergei - Tue, 29 Jun 2010, 14:03)=-=- Category updated. --- /tmp/wklog.11.old.32063 2010-06-29 14:03:35.000000000 +0000 +++ /tmp/wklog.11.new.32063 2010-06-29 14:03:35.000000000 +0000 @@ -1 +1 @@ -Server-Sprint +Server-BackLog -=-=(Guest - Tue, 19 May 2009, 18:27)=-=- High Level Description modified. --- /tmp/wklog.11.old.21953 2009-05-19 18:27:14.000000000 +0300 +++ /tmp/wklog.11.new.21953 2009-05-19 18:27:14.000000000 +0300 @@ -1 +1,360 @@ -Add CONNECT BY syntax +<contents> +1. Background information +2. CONNECT BY semantics, properties and limitations +2.1 Additional CONNECT BY features +2.2 Limitations +3. Our implementation +3.1 Scope Questions +3.2 CONNECT BY execution +3.2.1 Straightforward (recursive) evaluation algorithm +3.2.2 Transitive-closure evaluation algorithms +3.2.3 Other algorithms +3.2.4 Loop detection +3.2.4.1 The upper bound of produced records +3.2.4.1 Straightforward approach: track chains +3.2.3 Improvements for straightforward execution strategy +3.3. Optimization +4. Use-cases dump +</contents> + +1. Background information +------------------------- +* CONNECT BY is a non-standard, Oracle's syntax. It is also supported by + EnterpriseDB (Q: any other implementations?) + +* PostgreSQL 8.4 (now beta) has support for SQL-standard compliant WITH + RECURSIVE (aka Common Table Expressions, CTE) query syntax: + http://www.postgresql.org/docs/8.4/static/queries-with.html + http://www.postgresql.org/about/news.1074 + http://archives.postgresql.org/pgsql-hackers/2008-02/msg00642.php + http://archives.postgresql.org/pgsql-patches/2008-05/msg00362.php + +* Evgen's attempt: + http://lists.mysql.com/internals/15569 + +DB2 and MS SQL support SQL standard's WITH RECURSIVE clause. + +2. CONNECT BY semantics, properties and limitations +--------------------------------------------------- +From Oracle's manual: + +<almost-quote> + + SELECT ... + FROM ... + WHERE ... + START WITH cond + CONNECT BY connect_cond + ORDER [SIBLINGS] BY + +In oracle, one expression in connect_cond must be + + PRIOR expr = expr + + or + + expr = PRIOR expr + +The manner in which Oracle processes a WHERE clause (if any) in a hierarchical +query depends on whether the WHERE clause contains a join: + + * If the WHERE predicate contains a join, Oracle applies the join predicates + before doing the CONNECT BY processing. + * If the WHERE clause does not contain a join, Oracle applies all predicates + other than the CONNECT BY predicates after doing the CONNECT BY processing + without affecting the other rows of the hierarchy. +</almost-quote> + +See http://www.adp-gmbh.ch/ora/sql/connect_by.html +http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96540/queries4a.htm + + +2.1 Additional CONNECT BY features +---------------------------------- + +LEVEL pseudocolumn + indicates ancestry depth of the record (inital row has level=1, its children + have level=2 and so forth). Can be used in CONNECT BY clause to limit + traversal depth. + +SYS_CONNECT_BY_PATH(column, 'char') + returns path from root to the node. + +NOCYCLE and CONNECT_BY_ISCYCLE + "With the 10g keyword NOCYCLE, hierarchical queries detect loops and do not + generate errors. CONNECT_BY_ISCYCLE pseudo-column is a flag that can be used + to detect which row is cycling" + http://www.dba-oracle.com/t_advanced_sql_connect_by_loop.htm + +ORDER SIBLINGS BY + CONNECT BY produces records in "children follow parents" order, with order + of the siblings unspecified. ORDER SIBLINGS BY orders siblings within each + "generation". + +2.2 Limitations +--------------- +Other limitations (which we might or might not want to replicate) + +* There is this error: + ORA-01437: cannot have join with CONNECT BY + Cause: A join operation was specified with a CONNECT BY clause. If a + CONNECT BY clause is used in a SELECT statement for a tree- + structured query, only one table may be referenced in the query. + Action: Remove either the CONNECT BY clause or the join operation from + the SQL statement. + It seems oracle had this limitation before version 10G + +* LEVEL cannot be used on the left side of IN-comparison if the right side is a + subquery +http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/sql_elements6a.htm#9547 + This seems to have been lifted in version 10? + +3. Our implementation +--------------------- + +3.1 Scope Questions +------------------- +* Are we sure we want CONNECT BY syntax and not SQL standard' one? (I'm not + suggesting one or the other, just want to make sure we've made a conscious + decision) + +* Any use-cases we need to make sure to handle well? + +Will we implement any of these features: + +* Output is ordered (children follow parents) +* "ORDER SIBLINGS BY" variant of ORDER BY +* NOCYCLE/CONNECT_BY_ISCYCLE + - It seems any checking for cycles will cause overhead. Do we implement a + mode for those who know what they are doing, where the server doesn't + actually check cycles but only reports error if it happened to enumerate, + say MAX(1M, #records_in_table * 10) records? (This doesn't guarantee that + there are no cycles, but this is just beyond what one could logically want) + +* Oracle's treatment of WHERE (if there's a join - the WHERE is applied after + connect by, otherwise before) [Yes] +* Can one use SYS_CONNECT_BY_PATH in the CONNECT BY expression? + + +3.2 CONNECT BY execution +------------------------ + +3.2.1 Straightforward (recursive) evaluation algorithm +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ +As specified in CONNECT BY definition, breadth-first, parent-to-children +traversal: + + start with a scan that retrieves records using the START WITH condition; + pass rows to ouptut and also record them (i.e. needed columns) in + some sort of growable, overflow-to-disk buffer in_buf; + + while(in_buf is not empty) + { + for each record in the buffer + { + do a scan based on CONNECT BY condition; + pass rows to output and also record them (i.e. needed columns) in + a growable, overflow-to-disk buffer out_buf; + } + in_buf= out_buf; + } + +This algorithm will produce rows in the required order. + +3.2.2 Transitive-closure evaluation algorithms +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ +When CONNECT BY clause refers only to current and PRIOR records (and doesn't +refer to connect path using LEVEL or SYS_CONNECT_BY_PATH functions), then +evaluation of CONNECT BY operation is equivalent to building a transitive +closure of a certain relation. + +TODO: can we use LEVEL/SYS_CONNECT_BY_PATH in select list with these + algorithms? looks like no? + +There are special algorithms to build transitive closure of relation that is +represented as a table of edges, e.g. Blocked Warshall Algorithm. + +Q: Do we investigate further in this direction? + +3.2.3 Other algorithms +---------------------- +To be resolved: Do we always start from the first clause and go to children? +Does it make sense to proceed in other direction, from children to parents? +Looks like no? TODO need definite answer. + +3.2.4 Loop detection +~~~~~~~~~~~~~~~~~~~~ +Transitive-closure algorithms can detect loops (it seems some of them can also +handle loop avoidance but that needs to be verified). + +Straightforward-evaluation algorithm will work forever if there is a loop, +hence will need assistance in loop detection/avoidance. + +3.2.4.1 The upper bound of produced records +------------------------------------------- +There is an upper bound of the amount of records CONNECT BY runtime can +generate without generating a loop. + +The worst case is when + * every record in a source table was in the parent generation (and thus has + started a parent->child->child->... chain) + * every chain is of #table-records length. + +example of such case: + + SELECT * FROM employees + START WITH true + CONNECT BY + PRIOR emp_id = (emp_id + 1) MOD $n_employees AND + length(SYS_CONNECT_BY_PATH('-')) = $n_employees -- guard againist + -- forming loops + +this gives that we can at most generate O(#table_records^2) records. This +limitation can be used as a primitive way to stop evaluation. + + +3.2.4.1 Straightforward approach: track chains +---------------------------------------------- +In general case, we will have to track which records we have seen across each +of the parent-child chains. The same record can show up in different chains +at different times and this won't form a loop: + + parent generation1 generation2 + + row1- --+---row2---- ---row3-- (chain1) + | + \--row3-+-- ---row2-- (chain2) + | + \- ---row4-- (chain3) + row4- ... + +Tracking can be done by +- Numbering the chains and using one structure (e.g temptable) to store + (rowid, chain#) pairs and check them for uniqueness. + +- Using per-chain data structure which we could serialize/deserialize. This + could be + - serializable hashtable + - ordered rowid list + - serializable sparse bitmap + +One can expect a lot of chains to have common starts (eg. look at chain2 and +chain3). I don't see how one could take advantage of that, though. + +3.2.3 Improvements for straightforward execution strategy +--------------------------------------------------------- + +* If the query is a join, it may make sense to materialize it join result + (including creation of appropriate index) so we're able to make + parent-to-child transitions faster. + This seems to be connected to Evgen's work on FROM subqueries. + +* If there is a suitable index, we can employ a variant of BatchedKeyAccess. + +* Part of CONNECT BY expression that places restrictions on subsequent + generation can be moved to the WHERE. If we do that, we get two recordsets: + +1. Initial START WITH recordset + +2. A recordset to be used to advance to subsequent generation + + +3.3. Optimization +----------------- +It seems it is nearly impossible to estimate how many iterations we'll have +to make and how many records we will end up producing. + +TODO: some bad estimates.. assume a fixed number of generations, reuse ref +accces estimations for fanount, which gives + + access_method_estimate ^ number_of_generations + +estimate? + +4. Use-cases dump +================= + +http://www.orafaq.com/usenet/comp.databases.oracle.server/2007/01/05/0264.htm: + select mak_xx,nr_porz,level lvl from spacer_strona + where nvl(dervlvl,0)<3 + start with mak_xx=125414 and nr_porz=0 + connect by mak_xx = prior derv_mak_xx and nr_porz = prior derv_nr_porz + and prior dervlvl=3 + + +http://www.orafaq.com/usenet/comp.databases.oracle.server/2007/01/04/0196.htm: + SELECT OPM_N_ID FROM RAFC_ADM.AFC_T_OPERATION_METIER START WITH OPM_N_ID IN + ( + SELECT OPM_N_ID FROM RAFC_ADM.AFC_T_OPERATION_METIER x + START WITH x.OPM_N_ID IN (4846) + CONNECT BY ((PRIOR x.OPM_MERE_OPM_N_ID = x.OPM_N_ID) + OR (PRIOR x.OPM_ANNULEE_OPM_N_ID = x.OPM_N_ID)) + ) + CONNECT BY ((PRIOR OPM_N_ID = OPM_MERE_OPM_N_ID) OR (PRIOR OPM_N_ID = +OPM_ANNULEE_OPM_N_ID)) + +http://forums.enterprisedb.com/posts/list/737.page: + select lpad(' ',2*(level-1)) || to_char(child) s + from x + start with parent is null + connect by prior child = parent; + + select * + from emp, dept + where dept.deptno = emp.deptno + start with mgr is null + connect by mgr = prior empno + +http://forums.oracle.com/forums/thread.jspa?threadID=623173: + SELECT cust_number + FROM customer + START WITH cust_number = '5568677999' + CONNECT BY PRIOR cust_number = cust_group_code. + +http://www.orafaq.com/forum/t/118879/0/ + SELECT COUNT(a.dataid), c.name + FROM dauditnew a, dtree b, kuaf c + WHERE a.auditdate > SYSDATE-10 AND a.auditstr IN ('Create', 'AddVersion') + AND a.dataid = b.dataid AND c.id = a.performerid + AND a.SUBTYPE = 0 + START WITH b.dataid = 6132086 CONNECT BY PRIOR a.dataid = b.parentid GROUP BY +c.name + + +http://www.postgresql-support.de/blog/blog_hans.html + SELECT METIER_ID||'|'||ORGANISATION_ID AS JOBORG + FROM INTRA_METIER,INTRA_ORGANISATION + WHERE METIER_ID IN( + SELECT METIER_ID + FROM INTRA_METIER + START WITH METIER_ID= '99533220-e8b2-4121-998c-808ea8ca2da7' + CONNECT BY METIER_ID= PRIOR PARENT_METIER_ID + ) AND ORGANISATION_ID IN ( + SELECT ORGANISATION_ID + FROM INTRA_ORGANISATION + START WITH ORGANISATION_ID='025ee58f-35a3-4183-8679-01472838f753' + CONNECT BY ORGANISATION_ID= PRIOR PARENT_ORGANISATION_ID + ); + +http://oracle.com + Oracle database uses CONNECT BY to generate EXPLAINs. + +http://practical-sql-tuning.blogspot.com/2009/01/use-of-statistically-incorrect.html + + select sum(human_cnt) from facts + where territory_id in (select territory_id + from dic$territory + start with territory_code = :code + connect by prior territory_id = territory_parent); + +http://www.dbasupport.com/forums/archive/index.php/t-30008.html + + + SELECT LEVEL,LPAD(' ',8*(LEVEL-1))||T_COM_OBJ.OBJ_NAME, T_COM_OBJ.OBJ_PARENT, +T_COM_OBJ.OBJ_ID + FROM VDR.T_COM_OBJ + START WITH T_COM_OBJ.OBJ_ID in (select obj_id obj_main from vdr.t_com_obj +where obj_id=obj_parent) + CONNECT BY PRIOR T_COM_OBJ.OBJ_ID = T_COM_OBJ.OBJ_PARENT + + DESCRIPTION: <contents> 1. Background information 2. CONNECT BY semantics, properties and limitations 2.1 Additional CONNECT BY features 2.2 Limitations 3. Our implementation 3.1 Scope Questions 3.2 CONNECT BY execution 3.2.1 Straightforward (recursive) evaluation algorithm 3.2.2 Transitive-closure evaluation algorithms 3.2.3 Other algorithms 3.2.4 Loop detection 3.2.4.1 The upper bound of produced records 3.2.4.1 Straightforward approach: track chains 3.2.3 Improvements for straightforward execution strategy 3.3. Optimization 4. Use-cases dump </contents> 1. Background information ------------------------- * CONNECT BY is a non-standard, Oracle's syntax. It is also supported by EnterpriseDB (Q: any other implementations?) * PostgreSQL 8.4 (now beta) has support for SQL-standard compliant WITH RECURSIVE (aka Common Table Expressions, CTE) query syntax: http://www.postgresql.org/docs/8.4/static/queries-with.html http://www.postgresql.org/about/news.1074 http://archives.postgresql.org/pgsql-hackers/2008-02/msg00642.php http://archives.postgresql.org/pgsql-patches/2008-05/msg00362.php * Evgen's attempt: http://lists.mysql.com/internals/15569 DB2 and MS SQL support SQL standard's WITH RECURSIVE clause. 2. CONNECT BY semantics, properties and limitations --------------------------------------------------- >From Oracle's manual: <almost-quote> SELECT ... FROM ... WHERE ... START WITH cond CONNECT BY connect_cond ORDER [SIBLINGS] BY In oracle, one expression in connect_cond must be PRIOR expr = expr or expr = PRIOR expr The manner in which Oracle processes a WHERE clause (if any) in a hierarchical query depends on whether the WHERE clause contains a join: * If the WHERE predicate contains a join, Oracle applies the join predicates before doing the CONNECT BY processing. * If the WHERE clause does not contain a join, Oracle applies all predicates other than the CONNECT BY predicates after doing the CONNECT BY processing without affecting the other rows of the hierarchy. </almost-quote> See http://www.adp-gmbh.ch/ora/sql/connect_by.html http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96540/queries4a… 2.1 Additional CONNECT BY features ---------------------------------- LEVEL pseudocolumn indicates ancestry depth of the record (inital row has level=1, its children have level=2 and so forth). Can be used in CONNECT BY clause to limit traversal depth. SYS_CONNECT_BY_PATH(column, 'char') returns path from root to the node. NOCYCLE and CONNECT_BY_ISCYCLE "With the 10g keyword NOCYCLE, hierarchical queries detect loops and do not generate errors. CONNECT_BY_ISCYCLE pseudo-column is a flag that can be used to detect which row is cycling" http://www.dba-oracle.com/t_advanced_sql_connect_by_loop.htm ORDER SIBLINGS BY CONNECT BY produces records in "children follow parents" order, with order of the siblings unspecified. ORDER SIBLINGS BY orders siblings within each "generation". 2.2 Limitations --------------- Other limitations (which we might or might not want to replicate) * There is this error: ORA-01437: cannot have join with CONNECT BY Cause: A join operation was specified with a CONNECT BY clause. If a CONNECT BY clause is used in a SELECT statement for a tree- structured query, only one table may be referenced in the query. Action: Remove either the CONNECT BY clause or the join operation from the SQL statement. It seems oracle had this limitation before version 10G * LEVEL cannot be used on the left side of IN-comparison if the right side is a subquery http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/sql_elements… This seems to have been lifted in version 10? 3. Our implementation --------------------- 3.1 Scope Questions ------------------- * Are we sure we want CONNECT BY syntax and not SQL standard' one? (I'm not suggesting one or the other, just want to make sure we've made a conscious decision) * Any use-cases we need to make sure to handle well? Will we implement any of these features: * Output is ordered (children follow parents) * "ORDER SIBLINGS BY" variant of ORDER BY * NOCYCLE/CONNECT_BY_ISCYCLE - It seems any checking for cycles will cause overhead. Do we implement a mode for those who know what they are doing, where the server doesn't actually check cycles but only reports error if it happened to enumerate, say MAX(1M, #records_in_table * 10) records? (This doesn't guarantee that there are no cycles, but this is just beyond what one could logically want) * Oracle's treatment of WHERE (if there's a join - the WHERE is applied after connect by, otherwise before) [Yes] * Can one use SYS_CONNECT_BY_PATH in the CONNECT BY expression? 3.2 CONNECT BY execution ------------------------ 3.2.1 Straightforward (recursive) evaluation algorithm ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ As specified in CONNECT BY definition, breadth-first, parent-to-children traversal: start with a scan that retrieves records using the START WITH condition; pass rows to ouptut and also record them (i.e. needed columns) in some sort of growable, overflow-to-disk buffer in_buf; while(in_buf is not empty) { for each record in the buffer { do a scan based on CONNECT BY condition; pass rows to output and also record them (i.e. needed columns) in a growable, overflow-to-disk buffer out_buf; } in_buf= out_buf; } This algorithm will produce rows in the required order. 3.2.2 Transitive-closure evaluation algorithms ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ When CONNECT BY clause refers only to current and PRIOR records (and doesn't refer to connect path using LEVEL or SYS_CONNECT_BY_PATH functions), then evaluation of CONNECT BY operation is equivalent to building a transitive closure of a certain relation. TODO: can we use LEVEL/SYS_CONNECT_BY_PATH in select list with these algorithms? looks like no? There are special algorithms to build transitive closure of relation that is represented as a table of edges, e.g. Blocked Warshall Algorithm. Q: Do we investigate further in this direction? 3.2.3 Other algorithms ---------------------- To be resolved: Do we always start from the first clause and go to children? Does it make sense to proceed in other direction, from children to parents? Looks like no? TODO need definite answer. 3.2.4 Loop detection ~~~~~~~~~~~~~~~~~~~~ Transitive-closure algorithms can detect loops (it seems some of them can also handle loop avoidance but that needs to be verified). Straightforward-evaluation algorithm will work forever if there is a loop, hence will need assistance in loop detection/avoidance. 3.2.4.1 The upper bound of produced records ------------------------------------------- There is an upper bound of the amount of records CONNECT BY runtime can generate without generating a loop. The worst case is when * every record in a source table was in the parent generation (and thus has started a parent->child->child->... chain) * every chain is of #table-records length. example of such case: SELECT * FROM employees START WITH true CONNECT BY PRIOR emp_id = (emp_id + 1) MOD $n_employees AND length(SYS_CONNECT_BY_PATH('-')) = $n_employees -- guard againist -- forming loops this gives that we can at most generate O(#table_records^2) records. This limitation can be used as a primitive way to stop evaluation. 3.2.4.1 Straightforward approach: track chains ---------------------------------------------- In general case, we will have to track which records we have seen across each of the parent-child chains. The same record can show up in different chains at different times and this won't form a loop: parent generation1 generation2 row1- --+---row2---- ---row3-- (chain1) | \--row3-+-- ---row2-- (chain2) | \- ---row4-- (chain3) row4- ... Tracking can be done by - Numbering the chains and using one structure (e.g temptable) to store (rowid, chain#) pairs and check them for uniqueness. - Using per-chain data structure which we could serialize/deserialize. This could be - serializable hashtable - ordered rowid list - serializable sparse bitmap One can expect a lot of chains to have common starts (eg. look at chain2 and chain3). I don't see how one could take advantage of that, though. 3.2.3 Improvements for straightforward execution strategy --------------------------------------------------------- * If the query is a join, it may make sense to materialize it join result (including creation of appropriate index) so we're able to make parent-to-child transitions faster. This seems to be connected to Evgen's work on FROM subqueries. * If there is a suitable index, we can employ a variant of BatchedKeyAccess. * Part of CONNECT BY expression that places restrictions on subsequent generation can be moved to the WHERE. If we do that, we get two recordsets: 1. Initial START WITH recordset 2. A recordset to be used to advance to subsequent generation 3.3. Optimization ----------------- It seems it is nearly impossible to estimate how many iterations we'll have to make and how many records we will end up producing. TODO: some bad estimates.. assume a fixed number of generations, reuse ref accces estimations for fanount, which gives access_method_estimate ^ number_of_generations estimate? 4. Use-cases dump ================= http://www.orafaq.com/usenet/comp.databases.oracle.server/2007/01/05/0264.h…: select mak_xx,nr_porz,level lvl from spacer_strona where nvl(dervlvl,0)<3 start with mak_xx=125414 and nr_porz=0 connect by mak_xx = prior derv_mak_xx and nr_porz = prior derv_nr_porz and prior dervlvl=3 http://www.orafaq.com/usenet/comp.databases.oracle.server/2007/01/04/0196.h…: SELECT OPM_N_ID FROM RAFC_ADM.AFC_T_OPERATION_METIER START WITH OPM_N_ID IN ( SELECT OPM_N_ID FROM RAFC_ADM.AFC_T_OPERATION_METIER x START WITH x.OPM_N_ID IN (4846) CONNECT BY ((PRIOR x.OPM_MERE_OPM_N_ID = x.OPM_N_ID) OR (PRIOR x.OPM_ANNULEE_OPM_N_ID = x.OPM_N_ID)) ) CONNECT BY ((PRIOR OPM_N_ID = OPM_MERE_OPM_N_ID) OR (PRIOR OPM_N_ID = OPM_ANNULEE_OPM_N_ID)) http://forums.enterprisedb.com/posts/list/737.page: select lpad(' ',2*(level-1)) || to_char(child) s from x start with parent is null connect by prior child = parent; select * from emp, dept where dept.deptno = emp.deptno start with mgr is null connect by mgr = prior empno http://forums.oracle.com/forums/thread.jspa?threadID=623173: SELECT cust_number FROM customer START WITH cust_number = '5568677999' CONNECT BY PRIOR cust_number = cust_group_code. http://www.orafaq.com/forum/t/118879/0/ SELECT COUNT(a.dataid), c.name FROM dauditnew a, dtree b, kuaf c WHERE a.auditdate > SYSDATE-10 AND a.auditstr IN ('Create', 'AddVersion') AND a.dataid = b.dataid AND c.id = a.performerid AND a.SUBTYPE = 0 START WITH b.dataid = 6132086 CONNECT BY PRIOR a.dataid = b.parentid GROUP BY c.name http://www.postgresql-support.de/blog/blog_hans.html SELECT METIER_ID||'|'||ORGANISATION_ID AS JOBORG FROM INTRA_METIER,INTRA_ORGANISATION WHERE METIER_ID IN( SELECT METIER_ID FROM INTRA_METIER START WITH METIER_ID= '99533220-e8b2-4121-998c-808ea8ca2da7' CONNECT BY METIER_ID= PRIOR PARENT_METIER_ID ) AND ORGANISATION_ID IN ( SELECT ORGANISATION_ID FROM INTRA_ORGANISATION START WITH ORGANISATION_ID='025ee58f-35a3-4183-8679-01472838f753' CONNECT BY ORGANISATION_ID= PRIOR PARENT_ORGANISATION_ID ); http://oracle.com Oracle database uses CONNECT BY to generate EXPLAINs. http://practical-sql-tuning.blogspot.com/2009/01/use-of-statistically-incor… select sum(human_cnt) from facts where territory_id in (select territory_id from dic$territory start with territory_code = :code connect by prior territory_id = territory_parent); http://www.dbasupport.com/forums/archive/index.php/t-30008.html SELECT LEVEL,LPAD(' ',8*(LEVEL-1))||T_COM_OBJ.OBJ_NAME, T_COM_OBJ.OBJ_PARENT, T_COM_OBJ.OBJ_ID FROM VDR.T_COM_OBJ START WITH T_COM_OBJ.OBJ_ID in (select obj_id obj_main from vdr.t_com_obj where obj_id=obj_parent) CONNECT BY PRIOR T_COM_OBJ.OBJ_ID = T_COM_OBJ.OBJ_PARENT ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0