Oracle Scratchpad

November 16, 2006

11g benefits

Filed under: CBO,Infrastructure,Partitioning,Statistics — Jonathan Lewis @ 8:22 am BST Nov 16,2006

Posting from the UKOUG (UK Oracle User Group) conference. A couple of useful details from Tom Kyte’s technical keynote on “Things which might be in 11g but we’re not making any promises and you can’t hold us to it”.

The optimizer will be extended to allow us to collect some statistical information about correlated columns. This should help the optimizer to deal with combining predicates like: “Orders made in the last two weeks” with “Orders that have not yet been delivered” – if they’re recent orders, they’re more likely to be undelivered. I’m looking forward to seeing how far the Optimizer team has got with handling this rather difficult problem.

Partition handling: one of the current irritations for partitioning is that you have to disable referential integrity between partitioned tables if you want to drop old partitions. (Drop child partition Jan2001, followed by an attempt to drop the “obviously matching” parent partition Jan2001 currently results in Oracle error “ORA-02266: unique/primary keys in table referenced by enabled foreign keys”). 11g will give us the ability to declare that the partitioning of the child table is dependent on the partitioning of the parent table, and therefore guaranteed to be in-synch with the parent table. Apart from handling the drop partition problem, this should also help to avoid accidents that manage to disable partition-wise joins.

4 Comments »

  1. I saw that too and was particularly interested in the new partitioning options (like range:range and range by INTERVAL) and compression for tables in an OLTP environment (i.e. not requiring direct path operations for them to take advantage of it)…both great new features that may be in some future version which might be called 11g.

    Comment by Jeff Moss — November 16, 2006 @ 1:52 pm BST Nov 16,2006 | Reply

  2. [...] Footnote: 11g is expected to include a special ‘ref partitioning’ feature to deal with this specific issue. [...]

    Pingback by Drop parent partition « Oracle Scratchpad — December 11, 2006 @ 8:12 am BST Dec 11,2006 | Reply

  3. but in 11g it doesn’t work with interval partitioning :(
    in 12c it is supposed to.

    Comment by tinky2jed — November 22, 2013 @ 9:43 pm BST Nov 22,2013 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,257 other followers