Oracle Mix: Two Ideas

I've added two ideas at Oracle Mix. The first one is UTL_FILE has the ability to read the contents of a directory and the second one is Add a pseudo-column that stores the date the record was INSERTed or UPDATEd.

I mentioned the second one before. The first one I've seen on the iloveplsqland.net site. So go and vote (or don't, but leave your comments).

11g New Feature: PIVOT

I do have an interview tomorrow. Woohoo!

So my go at the PIVOT operator, "new" in 11g.

pivot_clause
Datawarehouse Guide
SQL Reference Examples
Arup Nanda's Example

My example.

Let's create some data first:

CREATE TABLE transaction_types
(
  transactiontypecode VARCHAR2(10)
    CONSTRAINT pk_transactiontypecode PRIMARY KEY
);

INSERT INTO transaction_types( transactiontypecode )
VALUES ( 'DEBIT' );
INSERT INTO transaction_types( transactiontypecode )
VALUES ( 'CREDIT' );

CREATE TABLE transactions
(
  id NUMBER(10)
    CONSTRAINT pk_id PRIMARY KEY,
  transactiontypecode
    CONSTRAINT fk_ttcode_transactions REFERENCES transaction_types( transactiontypecode )
    CONSTRAINT nn_ttcode_transactions NOT NULL,
  amount NUMBER(16,2)
    CONSTRAINT nn_amount_trans NOT NULL
    CONSTRAINT ck_amount_trans CHECK ( amount >= 0 ),
  date_created DATE DEFAULT SYSDATE
    CONSTRAINT nn_datecreated_trans NOT NULL
);

INSERT INTO transactions
  ( id,
    transactiontypecode,
    amount )
VALUES
  ( 1,
    'DEBIT',
    44.44 );

INSERT INTO transactions
  ( id,
    transactiontypecode,
    amount )
VALUES
  ( 2,
    'DEBIT',
    20.34 );

INSERT INTO transactions
  ( id,
    transactiontypecode,
    amount )
VALUES
  ( 3,
    'CREDIT',
    5.60 );

INSERT INTO transactions
  ( id,
    transactiontypecode,
    amount )
VALUES
  ( 4,
    'DEBIT',
    67 );

INSERT INTO transactions
  ( id,
    transactiontypecode,
    amount )
VALUES
  ( 5,
    'DEBIT',
    234.55 );

INSERT INTO transactions
  ( id,
    transactiontypecode,
    amount )
VALUES
  ( 6,
    'CREDIT',
    76.55 );

INSERT INTO transactions
  ( id,
    transactiontypecode,
    amount )
VALUES
  ( 7,
    'DEBIT',
    3.45 );

So what's the big deal with PIVOT? I'm not sure yet other than it's something new and new is cool.

Basically, PIVOT allows you to pivot rows into columns. We often do this for reports we generate. Here's the old way:

SELECT
  TRUNC( date_created ) date_created,
  SUM( CASE
        WHEN transactiontypecode = 'DEBIT' THEN
          amount
       END ) debit_amount,
  SUM( CASE
        WHEN transactiontypecode = 'CREDIT' THEN
          amount
       END ) credit_amount
FROM transactions
GROUP BY 
  TRUNC( date_created )
ORDER BY date_created;

DATE_CREA DEBIT_AMOUNT CREDIT_AMOUNT
--------- ------------ -------------
31-JUL-08       369.78         82.15

Really, not that bad. But if I want to do COUNT and AVG, I have to create more CASE statements like the ones above. My query will go from 14 lines to 30 in a hurry.

Here's the new cool way:

SELECT *
FROM
  (
    SELECT 
      transactiontypecode tt,
      TRUNC( date_created ) date_created,
      amount 
    FROM transactions
  )
PIVOT
(
  SUM( amount ) total_amount
  FOR tt IN ( 'DEBIT' AS "DEBIT", 'CREDIT' AS "CREDIT" )
);

DATE_CREA DEBIT_TOTAL_AMOUNT CREDIT_TOTAL_AMOUNT
--------- ------------------ -------------------
31-JUL-08             369.78               82.15

It happens that is 14 rows as well. Now I'll add AVG and COUNT:

SELECT *
FROM
  (
    SELECT 
      transactiontypecode tt,
      TRUNC( date_created ) date_created,
      amount 
    FROM transactions
  )
PIVOT
(
  SUM( amount ) total_amount,
  COUNT( amount ) total_count,
  AVG( amount ) avg_amount
  FOR tt IN ( 'DEBIT' AS "DEBIT", 'CREDIT' AS "CREDIT" )
);

DATE_CREA      D_AMT      D_CNT      D_AVG      C_AMT      C_CNT      C_AVG
--------- ---------- ---------- ---------- ---------- ---------- ----------
31-JUL-08     369.78          5     73.956      82.15          2     41.075

Nice! Sixteen lines of SQL...not bad at all. That should make code a bit more readable (it'll fit on a single page)...I like it!

wordle

Cool stuff.

My del.ico.us cloud.



Do you think I need a job? ;)

11g New Feature: Virtual Columns

Not really so new as it's more than a year old...but I've finally had the opportunity to check it out. Well, opportunity at my last job which ended on Monday, but since I have so much free time now, I figured I give it a run.

Virtual Columns

Defined in the New Features doc as:

Virtual columns are defined by evaluating an expression the results of which become the metadata of the columns for tables. Virtual columns can be defined at table creation or modification time.

Virtual columns enable application developers to define computations and transformations as the column (metadata) definition of tables without space consumption. This makes application development easier and less error-prone, as well as enhances query optimization by providing additional statistics to the optimizer for these virtual columns.

So what's it good for?

One thing that I see is that any derived column or expression can be placed at the table level (and indexed too!). This as opposed to putting it in a view and taking the chance that the logic is repeated exactly in every location.

So here goes my example:

CREATE TABLE virtual_columns
(
  id NUMBER
    CONSTRAINT pk_id PRIMARY KEY,
  first_name VARCHAR2(30),
  middle_name VARCHAR2(20),
  last_name VARCHAR2(40),
  full_name AS
  ( CASE
      WHEN middle_name IS NULL THEN
        first_name || ' ' || last_name
      WHEN middle_name IS NOT NULL THEN
        first_name || ' ' || middle_name || ' ' || last_name
    END ) VIRTUAL
);

INSERT INTO virtual_columns
  ( id,
    first_name,
    middle_name,
    last_name )
VALUES
  ( 1,
    'CHET',
    NULL,
    'JUSTICE' );

INSERT INTO virtual_columns
  ( id,
    first_name,
    middle_name,
    last_name )
VALUES
  ( 2,
    'MICKEY',
    'CARL',
    'MOUSE' );

CJUSTICE@>SELECT * FROM virtual_columns;

        ID FIRST_NAME MIDD LAST_NAME  FULL_NAME
---------- ---------- ---- ---------- ------------------------------
         1 CHET            JUSTICE    CHET JUSTICE
         2 MICKEY     CARL MOUSE      MICKEY CARL MOUSE

2 rows selected.

Very cool stuff indeed! One thing that might be an issue is that if someone does a SELECT * from a table without any filters. If the expression is complex (SQL --> PL/SQL context switching), it might slow it down. Of course I have no evidence to back this up yet...just pondering.

Design: The Entity?

Last month, I posted my preference
for maintaining addresses (and other entity attributes) via an ENTITY table. I got some good feedback, but I would like more.

Option 1

Option 2

In short, I prefer Option 1 as it requires no further modifications (if adding another entity) and you won't have to write funky SQL to get all the addresses (though you would have to UNION the people and organizations table and any subsequent entity). The one bad thing perhaps is that you would have to keep a close eye on your code to prevent someone from subverting your process...I think anyway.

So please vote and sound off in comments as to the "Why" of your choice.

(If you choose neither, please, please, please explain!)

Don't Go Into the Office

Whenever my boss asks me to come with him...I will politely tell him I'm not going.

I was laid off yesterday.

I want to see how many jobs I can have in one years time apparently. I didn't undermine myself this time though.

So if you know of anything in the Tampa Bay area, please let me know.

Revolution Money Seeks Senior DBA

Here's your chance to work with 11g RAC.

Either email me (chet dot justice at gmail) your resume and I will pass it along, or drop me a line and I will provide you with the appropriate contact information.

Here are the details:

JOB SUMMARY

As a Database Administrator with Revolution Money Inc, you will be responsible for all aspects of administration and the deployment of the various database components of our products. Major duties are to include installation, configuration, integration, tuning, troubleshooting and support.

Joining our team means you will have the opportunity to implement and use advanced features such as clustering and replication. We operate in the following environment: Linux; Oracle 11g RAC on ASM with Oracle Data Guard and Oracle Streams; 7 x 24 on-call support.

DUTIES & ESSENTIAL JOB FUNCTIONS
• Responsible for 24X7 production support.
• Install and maintain Oracle servers and service pack deployments.
• Manage security of application access to databases.
• Facilitate root cause analysis on database issues.
• Maintaining database performance by developing tuning methodologies.
• Perform backup and recovery.
• Assist database development environment and life cycle within the organization using best practices to ensure the benefits of standardization, documentation and change management.
• Providing information by collecting, analyzing, and summarizing database performance and trends.
• Research, planning and implementation of database upgrades and patches.
• Maintain technical documentation relating to database management.
• Perform benchmarking analysis to properly size hardware platforms.

OTHER FUNCTIONS AND RESPONSIBILITIES

Assist our internal customers running Oracle Database products including Oracle Applications.

QUALIFICATIONS

Required:

• A Bachelor's Degree in computer science or equivalent is required along with 5+ years experience with Oracle as a DBA.
• Production experience managing and monitoring Oracle 10g RAC databases.
• Oracle OEM/Grid Control, RMAN
• Strong PL/SQL , UNIX Scripting, Perl
• Solid Linux/UNIX and networking experience.
• Must be highly motivated, and a quick learner.
• Strong interpersonal skills and the ability to work well in teams, as well as working within a fast-paced, dynamic environment are requirements.

Desirable Skills:

• Oracle 11g RAC databases
• Oracle Streams
• Oracle BI/Data Warehouse, Oracle Financials, OID, OWB

Foot In Mouth Syndrome

On Friday of last week we were doing our first code review (well, my first code review at this job). A fun 2600 line package, 42 pages.

As were going through one piece, there's an exception block, with an explicit ROLLBACK, some logging code, and then a COMMIT. Of course the logging code should be an autonomous transactions, but apparently it's a bit sketchy.

Anyway, I blather on about not needing an explicit ROLLBACK after the exception as everything is rolled back prior...

"no it's not."

Me: "Yes it is."

"No, it's not."

Me: "Yes, it is."

We then moved on. I go back to my desk to write up a quick test proving my point.

Of course I was wrong though. If you have thing one and thing two and the exception occurs on thing two, the data/record/whatever for thing one exists. And, if you are going to issue a COMMIT and you don't want that orphaned record out there, you must issue an explicit ROLLBACK.

I sent a note out to everyone saying that my colleague is right, but I was still irked by it somehow.

It just didn't feel right.

So I left for lunch for a bit, still thinking about if of course.

Wait, I was saying one thing but thinking about something else. I was thinking of a logical transaction. Thing two depends on thing one therefore you would never, ever want to COMMIT after the exception. The logging code through me off for some reason.

I of course had to send out another email explaining my thought process and my mistake, in essence making me seem the hard loser. Why do I have to do that? Just let it lie...

Oh well, I've done it before and I'll probably do it again. Thankfully I work with some good people so they won't hold it over my head for too long...

Fraud Analysis?

So we are pondering how to go about fraud analysis real time.

One thing currently holding us back is that all the key fields are encrypted (obviously). How do you go about doing fraud analysis with such seemingly high overhead?

My question to you is, do you know of any fraud related off-the-shelf tools or libraries out there? Preferably written in PL/SQL, but Java or C will do as well.

Commas: Before or After the Line?

I'm in a new group again which means I have to learn (and accept) other people's style. No one at WellCare put commas before the line (thankfully), but I've found a few here.

I've finally come to accept that this is just style and doesn't really matter, as long as the code does what it's intended to do.

Yes, it's silly, but we all have our little quirks right?

Pseudo Column ROWDATE?

I'm working a little bit in the datawarehouse again (by the way, is it two words or one?). A technical guy from Oracle showed up today to help us decide the best way to move forward on capturing changes. There were essentially 4 methods:

1. SQL and PL/SQL
2. Streams (CDC)
3. OWB/ODI
4. Logical Standby/Data Guard

As someone on the phone was talking, I started to wander...hmmm...what about some kind of pseudo column for that stored the last update (either INSERT or UPDATE) of a row?

Off the top of my head, I can think of rowid, rownum, and level. I'm sure there are others (feel free to comment).

Oracle, I'm sure, stores that information some where right?

I hadn't really given a thought as to the feasibility or the impact it might have, but that would make capturing changes a whole lot easier...

Count The K's

I have this really annoying co-worker who happens to be the DBA. Everytime he walks by my desk he pounces on my keyboard. I've learned to Windows Key + L to lock the computer when I hear him approaching, but occasionally I forget.

As I'm standing near my unlocked computer he starts typing furiously in my sql*plus session (shouldn't he know better?). I should know better...

Walking away he asks, "How many K's are in there?"

I ignored him, but then wondered myself...what's the best solution to this problem?

So, here it goes:

DECLARE
  l_count_k NUMBER := 0;
  l_string VARCHAR2(300);
  l_string_length INTEGER;
BEGIN
  l_string := 'W34 6OKWE;KTL;SERT;LKSERTSLKRLTKRTKL;KERTL;ERKT;LKERLTKERKT;EKT;LEKRT;LEK;
LTKERTKERKT;LEKRT;LEKRT;LER;LTKETK;ERKT;LEKRT;LEKT;LEKR;LTKETKERTKL;ERKTL;ERKTL;ET;LEKT;LEKRT;LERTL;
EKRL;TKELTERTKL;ETL;EKTL;EKTL;ETL;ERTL;EL;TERTRTL;ERTL;ERTL;ERTL;EKRT;LEKTL;EKT;LERTL;EKL;KER;LTKE;L
TKELTKER';

  l_string_length := LENGTH( l_string );

  FOR i IN 1..l_string_length LOOP
    IF SUBSTR( l_string, i, 1 ) IN ( 'K', 'k' ) THEN
      l_count_k := l_count_k + 1;
    END IF;
  END LOOP;
  
  dbms_output.put_line( 'Kk Count:  ' || l_count_k );
END;
/

Easy enough, 45.

Then I started thinkinhg...can I do this in pure SQL? Of course!

SELECT 
  SUM( CASE
         WHEN SUBSTR( UPPER( mystring ), rownum, 1 ) = 'K' THEN
           1
       END ) k
FROM
  dual,
  (
    SELECT 'W34 6OKWE;KTL;SERT;LKSERTSLKRLTKRTKL;KERTL;ERKT;LKERLTKERKT;EKT;LEKRT;LEK;
    LTKERTKERKT;LEKRT;LEKRT;LER;LTKETK;ERKT;LEKRT;LEKT;LEKR;LTKETKERTKL;ERKTL;ERKTL;ET;LEKT;LEKRT;LERTL;
    EKRL;TKELTERTKL;ETL;EKTL;EKTL;ETL;ERTL;EL;TERTRTL;ERTL;ERTL;ERTL;EKRT;LEKTL;EKT;LERTL;EKL;KER;LTKE;L
    TKELTKER' mystring
    FROM dual
  ) c
CONNECT BY LEVEL <= LENGTH( mystring )

COUNT_OF_K
----------
        45

1 row selected.

I'm sure many of you can do better than that. So let's see 'em. Prodlife, this isn't a complicators test either. ;)

Process

I read The Daily WTF, well, daily. On Thursday last week, there was a good one on process. Essentially, the entire process had to be followed when an error occurred at boot. F1 would have solved the problem immediately...

My first job I never really got to put anything into production, so I wasn't real familiar with it. My second job, I was the lone ranger, so I did everything myself (though I did not do development in production). My last job however, was full of "The Process."

Rightfully so, especially in a large environment (i.e. more than 1 developer), though I think it was a bit overdone. And up until one of my failed deployments, the deployment itself was done through the Change Request (CR). What I mean by that, is that the code was attached to the CR itself. Since I attached a newer version, which had not been QA'd, well, you get the picture. We finally moved to a system whereby the DBAs actually deployed from our source control system...thankfully.

Now I'm in an environment that's a mix between the last job and the second to last. Everything is QA'd, but there isn't this whole process surrounding deployments...yet. Fortunately we're small enough to deal with it.

What's the point? I'm not sure.

Perhaps it's that I've learned more what not to do from The Daily WTF...

Compound Triggers

Seriously, I don't like triggers. But if you have to maintain them, you might as well make the best of it.

While trying to figure out my problem the other day, I ran across Compound Triggers. I hadn't read about it in the 11g New Features guide, but since I don't use triggers, I wasn't sure if it was new or not. Apprently it is...

In essence, you can combine multiple triggers into one. I won't go into the gory details (because I don't know the gory details), but I will provide the example from the docs for your perusal.

CREATE TRIGGER compound_trigger
  FOR UPDATE OF sal ON emp
    COMPOUND TRIGGER

  -- Declaration Section
  -- Variables declared here have firing-statement duration.
  threshold CONSTANT SIMPLE_INTEGER := 200;

  BEFORE STATEMENT IS
  BEGIN
    ...
  END BEFORE STATEMENT;

  BEFORE EACH ROW IS
  BEGIN
    ...
  END BEFORE EACH ROW;

  AFTER EACH ROW IS
  BEGIN
    ...
  END AFTER EACH ROW;
END compound_trigger;
/

At the very minimum (if you have to use them), you might as well combine them into one and save a bit on maintenance/debugging!

Editorial Control Over Comments

I recently commented on a blog that's fed through OraNA. Actually, I left one, received a comment back by the author, and then commented again. The author has editorial review before posting comments publicly.

I have no problem with that, I practice it. Fortunately, I haven't had the tough choice of whether to publish or not. It can't be easy.

That said, my second comment, in response to his response to my initial comment (still with me? ;) was never published. This may have been a simple oversight (it was yesterday), but I have no idea (yet anyway).

I blog because I crave the challenge. I want people to tell me I'm completely full of sh*t. Seriously. As long as it doesn't get personal, I'm all for discussion.

Funny License Plate

H20UUP2

First to figure out wins absolutely nothing but praise!

Lookup Tables

Also known as reference, crosswalk and a few other names.

I love 'em.

I'm not afraid to use them. It certainly makes that table count go up, but you know what you're getting.

I have ADDRESS_TYPES, PHONE_TYPES, PERSON_TYPES (in an intersection table of course) and any other kind of TYPE you can imagine.

I could use CHECK constraints I guess, but if it's anything other than Y or N, I typically create a lookup table to go with the table.

Let's take an ADDRESS table. ADDRESSTYPECODE becomes an attribute of an address. It gets a Foreign Key to the ADDRESS_TYPES table and also (many seem to leave this one out), a NOT NULL constraint. Every address has to have an type.

To make it somewhat easier, I use codes (as opposed to IDs which I tend to associate with numbers) so a join isn't absolutely necessary. If 'HOME' is the ADDRESSTYPECODE, you would rarely need to join as it's self evident what that means. If the lookup table is large, I'd typically use ID (or numbers) for the key.

Like I said, it bumps up that table count and makes things look a bit "messy," but you know exactly what belongs in what column. And if you're using ApEx, administrative screens are a snap!

Just don't ask Duke Ganote whether type is a good name or not!