Book Bonanza

I went a little crazy at Amazon.com last weekend.

I ordered these books Saturday and they all came in today. Where to start?

How many?

If you've ever seen...

There are now 76772 jobs remaining (current phase=A31):
   12 running, 14235 ready to run and 62525 waiting.

... you know you're in for a looooong weekend.

My Last Straw

I've had it with Adobe Acrobat Reader 7.0. Whenever I encounter a .pdf file that has both graphics and text on it, I can't seem to print it. No matter what I try to do, I just can't print the whole thing.

I recently upgraded to Acrobat Reader 7.0 on all my machines. My wife's computer is a W2K box that has the Brother HL-1440 printer attached to it. My laptop runs XP and I can normally print to the Brother with no problems over the network. My main box is a RH9 box that also prints to the same printer via the network.

I received an invoice from MySQL AB for the training I attended last week. I viewed it fine with Acrobat Reader 7.0 and then clicked "Print". Only the logos and lines on the document came out. So I tried a couple of the different "Comments and Forms" options in the Print dialog. Same thing. I figured my printer drivers might be out of date, so I updated them and tried again. Boxes and graphics only. Then I un-installed and re-installed Reader 7.0 with the most current updates. Same thing.

Next, I brought the document up on my laptop which also has Acrobat Reader 7.0 and tried printing to the network printer. Same deal. Then I tried printing the same file from my Linux box with Reader 7.0 with exactly the same result. Just for kicks, I brought up the file in xpdf and printed it, and voila, it prints fine. That ruled out the printer.

I thought maybe it was just the document I was trying to print. So I created a document with lines, graphics and text in Open Office and saved it as a .pdf. I tried it on all three computers; Reader 7.0 couldn't print, xpdf could.

Maybe it is the printer after all. I brought the same two documents to work and tried to print on an HP 8150. Still only those damn boxes and lines. Then I figured I'll downgrade to Reader 6, but I couldn't find it on Adobe's site.

My next step is seeing if there is a xpdf version for Windows.

New York Reflections

I guess you could call me a frequent visitor to New York. I’m in the city at least once a month for a user group meeting, training, or just as a plain old tourist. This week I’ve been one of the “commuter bots” that wake up at ungodly times, rush to the train station with their Starbucks Venti Moca something or another Latte and promptly go back to sleep for 72 minutes. I don’t know how these people do it every day, I couldn’t.

The training facility for this particular course is in lower Manhattan. I’m pretty comfortable with the subway system in New York, so I had a good idea which train to catch and that walking 3 or 4 blocks wouldn’t kill me. While walking around at lunch time one day, I strolled through Battery Park and came upon “The Sphere”. The Sphere was located at the World Trade Center four years ago when evil came to my backyard. This giant, scarred orb remains in this now vibrant community as a reminder that although time marches on, those who came before us will never be forgotten.

Next to “The Sphere” were impromptu tributes to those that died on that fateful day. A boy scout troop from somewhere in the Midwest had left a plaque. Somebody left a flower. There was a picture of somebody that sacrificed their life on that day to save others. The whole experience was overwhelming, more so, in my opinion, than seeing the empty spot where the WTC stood.

Walking the streets of New York on the weekend is different than during business hours. On the weekend, people are casually walking along drinking their $6 cup of coffee chatting about the weeks events and letting their dogs sniff every hydrant along the way. Rush hour turns these same people into machines silently screaming "GET OUT OF MY WAY!!!" with their eyes and expressionless face. On the weekend you can look lost and some Golden Retreiver walker will ask you where you are going and suggest you go up 41st Street instead of 42nd Street to avoid the crowds. On the weekday you have to ask three people which way to go and you'll be lucky if you get a point in the right direction. It's not that they're rude, they just have some place to be and the 5 train was delayed and they had to change to a local train at 14th street and ...

There's a lot of great things about New York, but I'm sure it's tough living there. If I could live and work North of City Hall, I think getting around would be pretty easy. You see, the Streets go East/West and the Avenues go North/South. The street numbers go up as you go North, the avenue numbers go up as you go West. Usually within a block you can tell if you are going in the wrong direction or not. Lower Manhattan is a differnt story. It's like another political party suddenly took control of the Street Naming Division and decided to start giving streets actual names instead of numbers. And forget about any grid of North/South and East/West.

I don't know if I am cut out for living in Manhattan. Sure it would be fun. For a while. I think I'd miss cutting the grass.

MySQL to an Oracle DBA, Part IV

Today is where the rubber meets the road in the Oracle/MySQL comparison; locking and transactions. At the end, I'll give you a little Oracle fun.

As I said yesterday, the different storage engines may or may not support transactions. MyISAM tables do not support transactions while InnoDB tables do. First, I create two tables; one as InnoDB, one as MyISAM:

system@localhost:world> create table city_myisam 
   engine = myisam as select * from city;
Query OK, 4063 rows affected (0.78 sec)
Records: 4063  Duplicates: 0  Warnings: 0

system@localhost:world> create table city_innodb 
    engine = innodb as select * from city;
Query OK, 4063 rows affected (0.53 sec)
Records: 4063  Duplicates: 0  Warnings: 0

Next, I set autocommit off.

system@localhost:world> set autocommit = 0;
Query OK, 0 rows affected (0.06 sec)

Now I'll delete some rows from the tables in a transaction and see what happens:

system@localhost:world> begin;
Query OK, 0 rows affected (0.00 sec)

system@localhost:world> delete from city_myisam 
   where country = 'FRA';
Query OK, 40 rows affected (0.11 sec)

system@localhost:world> delete from city_innodb 
   where country = 'FRA';
Query OK, 40 rows affected (0.09 sec)

system@localhost:world> rollback;
Query OK, 0 rows affected, 1 warning (0.05 sec)

system@localhost:world> show warnings;
+---------+------+---------------------------------------------------------------+
| Level   | Code | Message                                                       |
+---------+------+---------------------------------------------------------------+
| Warning | 1196 | Some non-transactional changed tables couldn't be rolled back |
+---------+------+---------------------------------------------------------------+
1 row in set (0.00 sec)

system@localhost:world> select count(*) from city_innodb 
   where country = 'FRA';
+----------+
| count(*) |
+----------+
|       40 |
+----------+
1 row in set (0.08 sec)

system@localhost:world> select count(*) from city_myisam 
   where country = 'FRA';
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.27 sec)

Aha, MySQL gives me a warning, but does it anyway. Note to self: no money transactions in MyISAM tables.

The next interesting thing was locking. The MyISAM storage engine only uses table level locks. When you update a row in a MyISAM table, you have to get an exclusive table level lock before you can update. These table level locks severly limit MyISAM scalability in a write intensive envrionment. The InnoDB engine, on the other hand, has row level locks, like Oracle.

The biggest Oracle misstatement today: Oracle will escalate to a table level lock when you delete more than 25% of the rows. Nope. In fact, I have a table with 5000 rows where the pk ranges from 1 to 5000:

SQL> l
  1  select count(*),
  2     min(x),
  3     max(x)
  4* from xyz
SQL> /

  COUNT(*)     MIN(X)     MAX(X)
---------- ---------- ----------
      5000          1       5000

In one session, I delete the first 4999 rows:

SQL> delete from xyz where x < 5000;

4999 rows deleted.

No problem. Now, from a different session I try to delete the row where x=4000.

SQL> delete from xyz where x=4000;

And, as expected, we are waiting on a lock from the first session:

SQL> l
  1  select waiting_Session wt, holding_session ho, lock_type lt,
  2     mode_held, mode_requested, lock_id1, lock_id2
  3* from dba_waiters
SQL> /

  WT   HO LT           MODE_HELD  MODE_REQUE LOCK_ID1 LOCK_ID2
---- ---- ------------ ---------- ---------- -------- --------
  24   25 Transaction  Exclusive  Exclusive    262148      163

But, if we try to delete the row that is not locked, it should go through with no issues.

SQL> delete from xyz where x=5000;

1 row deleted.

No problem, with no locks:

SQL> select waiting_Session wt, holding_session ho, lock_type lt,
  2     mode_held, mode_requested, lock_id1, lock_id2
  3  from dba_waiters
  4  /

no rows selected

MySQL to an Oracle DBA, Part III

Today is where we started digging our DBA teeth into MySQL. As with any database, the first thing you must do is install the software. MySQL comes in a couple different flavors depending on the platform and how you intend to use it. For example, on Linux you can install via RPM's, precompiled binaries, or source. On Windows you can install via a Windoz Installer, precompiled binaries, or source. While the quickest way to install is via RPMs or Windows Installer, you can build your mysql software tailored specifically for your hardware and the options you want to use. I chose to install from Windows Installer on Windows XP so I could work with my own laptop.

Once installed, you have to go through the configuration process. A lot of the configuration process is similar to Oracle; data goes here, logfiles (transaction logs) go on a seperate filesystem, the message logs go another place, etc. Similar to Oracle, you setup memory parameters and other features you want installed in the instance.

One interesting feature of MySQL is that you can store your tables using different "Storage Engines". A storage engine is basically the method used to access your data. You can use any number of storage engines in your database. The main storage engines are MyISAM, InnoDB, Memory, Merge, Berkley, and NDBCluster.

Why would you want to choose your storage engine? You can assign the storage engine based on what type of activity the table will have and the features you need.

Storage Engine	Advantages	Disadvantage
MyISAM	Extremely fast for Queries	Can't use transactions with, Concurrent INSERTs don't scale because the entire table is locked,Dirty Reads
InnoDB	Can use transactions, multi-versioning read consistency, DML scales well	Queries slower than MyISAM
Memory	Tables stored directly in memory. Access is really fast. Best for TEMP type tables	Tables stored directly in memory. DB Goes down, your data is toast.
Merge	Can present two identical tables as one table. Kind of like a materialized view, kind of like a partitioned table	Must be MyISAM tables
Berkley	Offers transactions.	Older technology
NDBCluster	Supports transactions and clusters, highly scalable	Not widely used.
Federated	Can store data on a seperate server	your I/O is limited by bandwidth

Last, but not least, we talked about security. We went through the same things you would do to secure an Oracle database; protect the root OS user, protect the mysql "root" (or SYS) user, use strong passwords, protect remote root logins, etc. One interesting concept is that MySQL users are identified not only by a username, but by the host they can login from. For example, jeffh@localhost is a totally different identity than jeffh@webserver. You grant permissions to both user depending on how you want to want them to access your data. If you want the root user to only login on the server itself, you only create root@localhost. This lets you setup access rules such as "When user jeffh is logged into the database on an internal domain, he can INSERT, UPDATE, DELETE, SELECT from my tables. When user jeffh is logged in from the VPN, he can only SELECT from my tables". That's kind of cool.

Two side notes: I leared about google sets and gvim (vi for windows).

MySQL to an Oracle DBA, Part II

Found out some things that you need to be aware of when converting from Oracle to MySQL. Let me create a table and show you some examples.

mysql> create table number_stuff (
    ->    description varchar(20) not null,
    ->    ti tinyint,
    ->    ival integer,
    ->    fl   float,
    ->    dc   decimal(15,5));
Query OK, 0 rows affected (0.13 sec)

This is kind of cool. You can insert multiple values in one shot.

mysql> insert into number_stuff values
    ->    ('123',123, 123, 123, 123),
    ->    ('384',384, 384, 384, 384),
    ->    ('12.5', 12.5, 12.5, 12.5, 12.5),
    ->    ('13.5', 13.5, 13.5, 13.5, 13.5),
    ->    ('14.5', 14.5, 14.5, 14.5, 14.5),
    ->    ('12345.67890',12345.67890, 12345.67890, 12345.67890, 12345.67890);
Query OK, 6 rows affected, 2 warnings (1.78 sec)
Records: 6  Duplicates: 0  Warnings: 2

mysql> show warnings
    -> ;
+---------+------+------------------------------------------------------+
| Level   | Code | Message                                              |
+---------+------+------------------------------------------------------+
| Warning | 1264 | Out of range value adjusted for column 'ti' at row 2 |
| Warning | 1264 | Out of range value adjusted for column 'ti' at row 6 |
+---------+------+------------------------------------------------------+
2 rows in set (0.00 sec)

Fair enough. We tried to insert values outside the bounds (-127 to 127) for TINYINT, and it makes sense that they’d fail. So let’s see what we got here:

mysql> select * from number_Stuff;
+-------------+------+-------+---------+-------------+
| description | ti   | ival  | fl      | dc          |
+-------------+------+-------+---------+-------------+
| 123         |  123 |   123 |     123 |   123.00000 |
| 384         |  127 |   384 |     384 |   384.00000 |
| 12.5        |   13 |    13 |    12.5 |    12.50000 |
| 13.5        |   14 |    14 |    13.5 |    13.50000 |
| 14.5        |   15 |    15 |    14.5 |    14.50000 |
| 12345.67890 |  127 | 12346 | 12345.7 | 12345.67890 |
+-------------+------+-------+---------+-------------+
6 rows in set (0.00 sec)

Our first row looks like it went in just fine.

The second row is what makes the DBA in me a little nervous. We tried to insert the integer value 384 into a TINYINT column and MySQL correctly gave us a warning. However, it then modified our data and put a 127 (the maximum value) into the TINYINT field.

Rows 3, 4, and 5 appear to have gone in without a hitch. But wait, it looks like MySQL insert the rounded number into the columns that contained integers. Hold on, you’re being paranoid, Oracle does the same thing:

SQL> create table xyz (x integer);

Table created.

SQL> insert into xyz (x) values (12.5);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from xyz;

         X
----------
        13

But what am I trying to show you with these numbers? Just that FLOAT and NUMBER round differently:

mysql> select description, round(fl), round(dc) from number_stuf
    -> where description in ('12.5','13.5', '14.5')
    -> ;
+-------------+-----------+-----------+
| description | round(fl) | round(dc) |
+-------------+-----------+-----------+
| 12.5        |        12 |        13 |
| 13.5        |        13 |        14 |
| 14.5        |        14 |        15 |
+-------------+-----------+-----------+
3 rows in set (0.00 sec)

Last, but not least, lets look at real numbers.

mysql> select description, fl, dc
    -> from number_stuff
    -> where description = '12345.67890';
+-------------+---------+-------------+
| description | fl      | dc          |
+-------------+---------+-------------+
| 12345.67890 | 12345.7 | 12345.67890 |
+-------------+---------+-------------+
1 row in set (0.00 sec)

Is that fl a display issue or a storage issue? Lets just use the TRUNCATE function to see what MySQL is really storing:

mysql> select description, fl, truncate(fl, 25) trunc_fl, dc
    -> from number_stuff
    -> where description = '12345.67890';
+-------------+---------+---------------------------------+-------------+
| description | fl      | trunc_fl                        | dc          |
+-------------+---------+---------------------------------+-------------+
| 12345.67890 | 12345.7 | 12345.6787109375000000000000000 | 12345.67890 |
+-------------+---------+---------------------------------+-------------+
1 row in set (0.00 sec)

Yup, that’s really what MySQL is storing, a wrong value. But does it really? Lets multiply by 10000 to see:

mysql> select description, fl, fl * 10000 big_fl, dc
    -> from number_stuff
    -> where description = '12345.67890';
+-------------+---------+-----------------+-------------+
| description | fl      | big_fl          | dc          |
+-------------+---------+-----------------+-------------+
| 12345.67890 | 12345.7 | 123456787.10938 | 12345.67890 |
+-------------+---------+-----------------+-------------+
1 row in set (0.00 sec)

Sure enough, that’s the value being stored.

Note to self: Don’t use FLOAT to store money or fractional shares.

As we were going through data types, I couldn’t understand why there were all these different INTEGER subtypes. The reason is that MySQL will take the entire 4 bytes in a 4 byte INTEGER even though it might not need it. For example, you create the table below:

CREATE TABLE xyz (
   id   BIGINT);

BIGINT is a data type that is an 8 byte integer. You intend to fill xyz.id with sequential numbers starting at 1. When you insert the value 1 into xyz.id, you have taken 8 bytes. This is different than Oracle in that the number data types are variable length. Is this good or bad, depends. It’s just something to be aware of.

MySQL to an Oracle DBA, Part I

As you may remember, I am at MySQL training this week. The first day was introductory material which included installing MySQL and query basics. Because we didn’t breeze through the Query portion, it gave me plenty of time to experiment.

SQL> select count(*) from country;

  COUNT(*)
----------
       239


mysql> select count(*) from country;
+----------+
| count(*) |
+----------+
|      239 |
+----------+
1 row in set (0.00 sec)

OK, that’s cool, that’s what I expected. Let’s do a simple aggregate function:

SQL> select continent, sum(population) TotalPop
  2  from country
  3  group by continent
  4  order by continent
  5  ;

CONTINENT                        TOTALPOP
------------------------------ ----------
Africa                          784475000
Antarctica                              0
Asia                           3705025700
Europe                          730074600
North America                   482993000
Oceania                          30401150
South America                   345780000

7 rows selected.

OK, that’s what we expected. Let’s try the same query in MySQL:

mysql> select continent, sum(population) TotalPop
    -> from country
    -> group by continent
    -> order by continent
    -> ;
+---------------+------------+
| continent     | TotalPop   |
+---------------+------------+
| Asia          | 3705025700 |
| Europe        |  730074600 |
| North America |  482993000 |
| Africa        |  784475000 |
| Oceania       |   30401150 |
| Antarctica    |          0 |
| South America |  345780000 |
+---------------+------------+
7 rows in set (0.00 sec)

Hmm, that’s interesting. Maybe it has something to do with default sorting order or something. I’ll have to ask Tobias (my Instructor) about that in the morning. Let’s try sorting by a number field:

SQL> select continent, sum(population) totalpop
  2  from country
  3  group by continent
  4  order by totalpop desc
  5  ;

CONTINENT                        TOTALPOP
------------------------------ ----------
Asia                           3705025700
Africa                          784475000
Europe                          730074600
North America                   482993000
South America                   345780000
Oceania                          30401150
Antarctica                              0

7 rows selected.

mysql> select continent, sum(population) totalpop
    -> from country
    -> group by continent
    -> order by totalpop desc
    -> ;
+---------------+------------+
| continent     | totalpop   |
+---------------+------------+
| Asia          | 3705025700 |
| Africa        |  784475000 |
| Europe        |  730074600 |
| North America |  482993000 |
| South America |  345780000 |
| Oceania       |   30401150 |
| Antarctica    |          0 |
+---------------+------------+
7 rows in set (0.02 sec)

Yeah, that’s we expected. Let’s try another query:

mysql> select continent, population totalpop
    -> from country
    -> group by continent
    -> order by totalpop desc
    -> ;
+---------------+----------+
| continent     | totalpop |
+---------------+----------+
| South America | 37032000 |
| Africa        | 31471000 |
| Asia          | 22720000 |
| Europe        | 15864000 |
| North America |   217000 |
| Oceania       |    68000 |
| Antarctica    |        0 |
+---------------+----------+
7 rows in set (0.00 sec)

Hmm, what’s weird about this query (assuming the data is correct)? Let’s try it in Oracle:

SQL> select continent, population totalpop
  2  from country
  3  group by continent
  4  order by totalpop desc
  5  ;
select continent, population totalpop
                  *
ERROR at line 1:
ORA-00979: not a GROUP BY expression

That’s right, our old friend didn’t even run the query for us.

Another interesting tidbit was with the DISTINCT Operator. In Oracle, the optimizer may determine DISTINCT values by sorting, whereas MySQL can use one of two algorithms. For example:

SQL> select distinct continent from country;

CONTINENT
------------------------------
Africa
Antarctica
Asia
Europe
North America
Oceania
South America

7 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6 Card=239 Bytes=7
          648)

   1    0   SORT (UNIQUE) (Cost=6 Card=239 Bytes=7648)
   2    1     TABLE ACCESS (FULL) OF 'COUNTRY' (TABLE) (Cost=5 Card=23
          9 Bytes=7648)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         16  consistent gets
          0  physical reads
          0  redo size
        665  bytes sent via SQL*Net to client
        507  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          7  rows processed

mysql> select distinct continent from country;
+---------------+
| continent     |
+---------------+
| Asia          |
| Europe        |
| North America |
| Africa        |
| Oceania       |
| South America |
| Antarctica    |
+---------------+
7 rows in set (0.00 sec)

BTW, another participant in the class suggested that Oracle always sends back the results of DISTINCT in sorted order. I didn’t think so, but wasn’t able to disprove it. Maybe I learned something new about Oracle today.

Edit: 09/19/2005 23:33 -
Tom correctly points out in the comments that Oracle doesn't always use a sort to determine duplicates. If I had that looked on metalink at doc 655027.999 or 249919.999 I would have confirmed my suspicians about always using ORDER BY. Bang head on desk, Bang head on desk, ...

We're doing MySQL

My manager called me into his office the other day and explained how we were going to put our first MySQL database up. It's a relatively simple app with 99% read activity and some batch loads in the night. This wasn't a total surprise as I did some investigation into MySQL about a year ago and determined that for the right types of application it would be adequate.

We've got some of the basics up in development already, but I feel like I'm a newbie all over again. When I can't figure something out, I say "That's how it works in Oracle" and need to step outside the box to figure it out.

This week is training in NYC. Five days of the basics will hopefully get me up to speed where I can be relatively proficient. Expect some blog entries next week about the differences between MySQL and Oracle.

Top N

A Frequently Asked Question on many Oracle related forums is “How can I get the first/last N rows in my table?” Sometimes that’s not an easy question to answer. I always ask the poster what they mean by “first”. Some people mean the order the records were inserted, some people mean the top-N rows. I’ve found it’s always easier to get the poster to explain what they are trying to do instead of guessing.

First/Last – in an ordered set
This one is easy. You take advantage of inline views and Oracle’s pseudocolumn ROWNUM. People usually get tripped up when they use ROWNUM because while ROWNUM is relevant in all queries, in this type of query it only makes sense when coupled with an ORDER BY.

For example:

SQL> select * from xyz;

         X          Y Z
---------- ---------- -------------------
       127          1 09/16/2005 14:46:50
       148         22 09/17/2005 14:48:42
       149         23 09/18/2005 14:48:42
       150         24 09/19/2005 14:48:42
       131          5 09/20/2005 14:46:50
       151         25 09/20/2005 14:48:42
       133          7 09/22/2005 14:46:50
       152         26 09/21/2005 14:48:42
       153         27 09/22/2005 14:48:42
       154         28 09/23/2005 14:48:42
       137         11 09/16/2005 14:46:50
       155         29 09/24/2005 14:48:42
       139         13 09/18/2005 14:46:50
       156         30 09/15/2005 14:48:42
       143         17 09/22/2005 14:46:50
       145         19 09/24/2005 14:46:50
       147         21 09/16/2005 14:48:42

17 rows selected.

SQL> select rownum, x, y, z from xyz;

    ROWNUM          X          Y Z
---------- ---------- ---------- -------------------
         1        127          1 09/16/2005 14:46:50
         2        148         22 09/17/2005 14:48:42
         3        149         23 09/18/2005 14:48:42
         4        150         24 09/19/2005 14:48:42
         5        131          5 09/20/2005 14:46:50
         6        151         25 09/20/2005 14:48:42
         7        133          7 09/22/2005 14:46:50
         8        152         26 09/21/2005 14:48:42
         9        153         27 09/22/2005 14:48:42
        10        154         28 09/23/2005 14:48:42
        11        137         11 09/16/2005 14:46:50
        12        155         29 09/24/2005 14:48:42
        13        139         13 09/18/2005 14:46:50
        14        156         30 09/15/2005 14:48:42
        15        143         17 09/22/2005 14:46:50
        16        145         19 09/24/2005 14:46:50
        17        147         21 09/16/2005 14:48:42

Here, ROWNUM does exactly what it’s supposed to do. It gives you a sequential number according to how the rows were pulled out of the table. There is obviously no logical order (to the human eye, anyway), but that’s how the rows come out. If you restrict by ROWNUM, you will certainly get a result, but probably not what you expect:

SQL> select * from xyz
  2  where rownum < 6
  3  /

         X          Y Z
---------- ---------- -------------------
       127          1 09/16/2005 14:46:50
       148         22 09/17/2005 14:48:42
       149         23 09/18/2005 14:48:42
       150         24 09/19/2005 14:48:42
       131          5 09/20/2005 14:46:50

So what to do? Just put an ORDER BY in?

SQL> select * from xyz
  2  where rownum < 6
  3  order by y
  4  /

         X          Y Z
---------- ---------- -------------------
       127          1 09/16/2005 14:46:50
       131          5 09/20/2005 14:46:50
       148         22 09/17/2005 14:48:42
       149         23 09/18/2005 14:48:42
       150         24 09/19/2005 14:48:42

Nope. But we’re getting closer. You have to use your ordered query as a subquery and then apply the filter.

SQL> select * from (
  2     select * from xyz
  3     order by y
  4  )
  5  where rownum < 6
  6  /

         X          Y Z
---------- ---------- -------------------
       127          1 09/16/2005 14:46:50
       131          5 09/20/2005 14:46:50
       133          7 09/22/2005 14:46:50
       137         11 09/16/2005 14:46:50
       139         13 09/18/2005 14:46:50

Bingo. That’s what we wanted. Now suppose we wanted a “window” from 6 to 10:

SQL> select o.x, o.y, o.z from    (
  2      select rownum r, i.x, i.y, i.z from (
  3         select x, y, z from xyz
  4         order by y ) i
  5      where rownum < 11 )o
  6  where o.r > 5
  7  /

         X          Y Z
---------- ---------- -------------------
       143         17 09/22/2005 14:46:50
       145         19 09/24/2005 14:46:50
       147         21 09/16/2005 14:48:42
       148         22 09/17/2005 14:48:42
       149         23 09/18/2005 14:48:42

First/Last – in insertion order
Since there is no inherent way for you to figure out the order the rows were put into a table, this one is a little trickier. The only way you can positively indicate the order the rows were inserted into the table is if you explicitly mark each record. Sometimes this may be a “DATE_INSERTED” field or it may be some other field that indicates order. In my example, I have created a BEFORE INSERT trigger to populate the Primary Key (x) with a value from a sequence:

create trigger xyz_bi
before insert
on xyz
for each row
declare
   pkval number;

begin
   select xyz_id.nextval into pkval from dual;

   :new.x := pkval;
end;

Therefore, I know my rows were inserted in the order according to X. I can then use my PK value to find out the last 10 rows inserted by:

SQL> select * from (
  2     select * from xyz
  3     order by x desc
  4     )
  5  where rownum < 11
  6  /

         X          Y Z
---------- ---------- -------------------
       156         30 09/15/2005 14:48:42
       155         29 09/24/2005 14:48:42
       154         28 09/23/2005 14:48:42
       153         27 09/22/2005 14:48:42
       152         26 09/21/2005 14:48:42
       151         25 09/20/2005 14:48:42
       150         24 09/19/2005 14:48:42
       149         23 09/18/2005 14:48:42
       148         22 09/17/2005 14:48:42
       147         21 09/16/2005 14:48:42

10 rows selected.

Recruiting Jack of all Technology

I got a cold call from a recruiter this morning. I was feeling rather playful, so I persued the conversation.

Recruiter: Hi, this is MaryJo Recruiter from XYZ firm. Do you have a couple of minutes to talk to me?
Me: Um, OK.

R: I am looking for a combination Database Administrator/Database Developer/Business Analyst for a financial company.
Me: Let me stop you right there. What kind of money are we talking?

I always like to ask this question to see what the jobs out there are paying.

R: Well, they didn't specify a range. I asked if I found somebody at 80K would they want to look at them and the client said yes.
Me: Oh, so they're really looking for 65K-75K.
R: Probably.
Me: What kind of skills?
R: They want 5 years of Oracle DBA, 5 years using VB and/or MS Access, and business analyst background.
Me: That's a pretty strange combination.
R: The position is not defined very well.
Me: I see.
R: It's a really good company. Would you be interested?
Me: You'd have to triple the money.

I always like to throw out some factor like double or triple to see what they say.

R: Oh....I'll pay referral fees.
Me: I see. I know several people in the industry and know of a couple looking right now. Tell me more about the position.
R: This position would report to the CIO. They would interface with the users to define the project and then implement the project with VB and Access.
Me: OK, so where does Oracle come into the picture?
R: Well, they're thinking they might have a lot of data and they will need Oracle.
Me: Oh. I think you will have a very hard time filling this position. There's not that many BA's that know how to program in VB/Access and setup an Oracle Database.
R: Do you know of anyone that might fit this position? I pay referral fees.
Me: I have a couple people in mind, but they would be more on the technical side.
R: Great! Can I send you my contact information?

So if anybody wants to do VB/Access/Oracle DBA/Business Analyst for $70K in Connecticut, drop me a line.

Goodbye DBAs

Forrester Research has jumped on the "zero administration" database bandwagon. Looks like I'll be retiring in 2009. Woo hoo!!!

Oracle WTF

Stumbled upon a funny blog by Willam Robertson, Oracle WTF. It's modled after The Daily WTF, but with an Oracle slant. Check it out.

Keep it in your pants

You know that gap between the elevator and the floor you are on? Ever wonder what would happen if you dropped something down there? I have had that very thought on more than one occasion.

I had something to do after work today, and I was already late when I packed up and hit the elevator button. My keys were in my hand and I was fiddling with them when the elevator door opened. As I stepped into the elevator, my keys dropped from my hand; keys on the floor and keyless remote dangling over “the gap”. In what seemed like slow motion, I bent down to pick them and just then the remote won out and they slipped down the gap.

All I could muster was “Oh Shit” as they banged their way down to some unknown place.

I immediately went to the security office hoping they could do something. The security guard paged building maintenance and Darrel says “no problem”. We take the elevator to the bottom floor, send it back up, and he uses his special key to get in the elevator shaft. We look around, but there’s no keys down there. About $3 in change, countless gum and candy wrappers, but no keys. Darrel says “Are you sure you were in this elevator?” We repeat the same procedure floor by floor thinking that maybe they got hung up on somewhere in the shaft. I wasn’t worried about the keys, because the maintenance guy said the elevator people would come in Monday and do a better search. If we can’t find them, I’ll have to call my security officer and let him know I lost my keys. After hours. Then another thought hits me; how do I get home?

As we’re on our pursuit, Darrel says somebody drops something down the shaft about once a month, which surprises me. He suggests we go back down to the basement and look around again. We look in the same shaft and still no dice. He suggests we take a peek in the other elevator shaft with the possibility that the keys bounced around in the shaft. Sure enough, there they were. He smiles at me and says “You were in this one, weren’t you?”

The only problem was my keyless entry remote didn’t survive the drop. As luck has it, I keep my keys to the car inside the car and use the remote to open the door. Quickly, I realize I’m taking the train home tonight.

Moral of the story: Keep you keys in your pants until you get to your car.

The Carolina Way

I love being at the beach. It gives me lots of time to catch up on non-technical reading. This year, I brought along The Carolina Way: Leadership Lessons from a Life in Coaching, by Dean Smith and Gerald Bell. A little disclosure here; I'm a Carolina fan and if Dean Smith said he made kids practice in bare feet, I'd think it was a good idea.

This book is more than your typical sports book. Sure, it's filled with stories about games, seasons, and individual players. Each chapter starts out with Coach Smith explaining a particular aspect of his coaching methodology. A Player's Perspective follows outlining how the particular aspect and Dr. Bell relates the aspect to the modern workplace. This book is all about building teams.

The Carolina Way can be summed up by these words: Play Hard; Play Together; Play Smart. For example, Coach Smith talks about recruiting players that will fit into his system. Dr. Bell then takes those ideas one step further and relates the recruiting to hiring workers. Another example is Dean Smith held regular one-on-one meetings with each of his players to discover their goals in life as well as basketball. Dr. Bell then outlines how you can pattern employee reviews around these same principals.

I personally got a lot out of this book and plan on implementing some of the strategies in my own team.

Blogger for Word

I am posting this blog entry using the new Blogger for Word add-on.  I downloaded it a couple days ago and tried to get it working, but had problems connecting to blogger.com.  I fired it up today, and voila! It works.

I tried some of the other editors out there, but just found it easier to use the blogger.com editor, except in cases of posting source code.  Lets see how this comes out:

FOR i IN 1..100 LOOP
   i:= i+1;
END LOOP;

OK, that should suffice.  Heck, I already know Word, let me try this for a couple days to see how it goes.