Pythian Group

One common question I get is how to use partitioning instead of MERGE tables. The process I use involves using stored procedures to create and drop partitions. This article will go over the stored procedures I use; special thanks to Roland Bouman for taking a look and giving great feedback to optimize this process.

First, a simple table, not partitioned (yet):

use test;
DROP TABLE IF EXISTS my_part;
CREATE TABLE IF NOT EXISTS my_part (
  id int NOT NULL,
  creationDate datetime NOT NULL,
  PRIMARY KEY (id,creationDate)
) ENGINE=InnoDB;

In real, life there is more to the table than just id and creationDate. The most important part is that the partitioned field(s) need to be part of the primary key.
Read the rest of this entry . . .

Just got an interesting note on Twitter that you can’t call a stored procedure dynamically in Oracle from a PL/SQL block like passing the procedure name in a variable.

Well, yes we can!

And the answer is EXECUTE IMMEDIATE — it can be used to run anonymous PL/SQL blog and not just a SQL statement. However, you will want to think many many times before doing so… if you love your data.

Let’s create the test procedures:

SQL> create or replace procedure bingo as
  2  begin
  3  dbms_output.put_line('Bingo!');
  4  end;
  5  /

Procedure created.

SQL> create or replace procedure bambam as
  2  begin
  3  dbms_output.put_line('BAM BAM!');
  4  end;
  5  /

Procedure created.

Now let’s create a wrapper that we will call passing a procedure name: Read the rest of this entry . . .

When using Oracle, the data dictionary provides us with tons of tables and views, allowing us to fetch information about pretty much anything within the database. We do have information like that in MySQL 5.0 (and up) in the information_schema database, but it’s scattered through several different tables.

Sometimes a client asks us to change the datatype of a column, but forgets to mention the schema name, and sometimes even the table name. As you can imagine, having this kind of information is vital to locate the object and perform the requested action. This kind of behaviour must be related to Murphy’s Law.

In any case, I’d like to share with you a simple stored procedure that has helped us a lot in the past.

Read the rest of this entry . . .

In a nutshell: What’s New in MySQL 5.1.

Release notes: Changes in release 5.1.x (Production).

And yes, very early on (at about two minutes in), I talk about my take on Monty’s controversial post at Oops, we did it again.

To play the video directly, go to http://www.youtube.com/watch?v=Hs4S7vONGMQ. Or watch it embedded inline here:

Read the rest of this entry . . .