Friday, December 08, 2006

I’ve come up with an idea while the new 6 months Oracle DBA contractor is being interviewed to back fill for Antoinette while she is on maternity leave.

The idea is we may take this Opportunity to introduce a new database developer join our team.

We pay 6 months salary to the contractor and only get an actual 3 months of work since they need 3 months to get up to speed on our environment (together with the increased cost of hiring a contractor).

It’s better to give this opportunity to the new employee as a database developer which can learn our business, and then contribute to our company in long term.

Why we need a Database Developer and Database API?

Today some GUI logic is in the middle tier but most of the data logic is still in PL/SQL (let’s say eBay here).

PerformancePerformance is directly connected to stability and functionality, when db node running to it’s knee and hangs, you can do nothing, and cause some potential locks and bugs.

Dodgy SQL examples.

1) Filter data in java side, treat db as a black data store.We should always filter data from the database side, I knew it 10 years ago as a database developer.

SELECT I.*

FROM ABELISTING.XBNIDENTS I

WHERE I.PROVIDER <> 44

What we did before:

SELECT I.* FROM ABELISTING.XBNIDENTS I;

Get all the data returned from database,

And then filter the row <> 44 in Java side.

2) Cursor fetchesWhat we are doing:

• 1. get Max and Min xbnidentsid

SELECT MIN(XBNIDENTSID) AS MIN, MAX(XBNIDENTSID) AS MAX

FROM ABELISTING.XBNIDENTS

• 2. Split it into many 100 rows chunks by calculate each xbnidentsid range

• 3. run below SELECT many times, each run get 100 rows

SELECT I.*, R.RECOMMENDATIONSET

FROM ABELISTING.XBNIDENTS I

LEFT OUTER JOIN LIBRARYTHING.ISBNRECOMMENDATIONS R

ON I.XBNIDENTIFIER = R.ISBN

WHERE I.PROVIDER <> 44

AND I.XBNIDENTTYPECODE = 1

AND I.XBNIDENTSID < #xbnidentsidMax#

AND I.XBNIDENTSID >= #xbnidentsidMin#

It’s a nest loop join, cost 5 times more resource and latches.

Why not run a simple single SQL SELECT, hash join 2 tables, and use bulk cursor prefetch?

It’s been implementing to X-Team, the rows Prefetching.

2.1) change a simple UPDATE to 2 steps.1) select the PK_ID by some predicates, cached into Java server side, maybe run the SQL many times, return 100 rows each time.

2) process row by row by PK_ID

Why not run a single UPDATE SQL here?

3) Treat database as a black box or a human brain; actually, we control how database works, we need to have:• knowledge of what the database is capable of doing

StabilityRDBMS database and store procedure have been around for more than 20 years, and is proven stable. Building our core data process logic inside the database will be stable.

More components means more complexity, more coding and bugs, more maintenance job, and it’s not linear, it’s exponential.

All about database API's. The applications don't do table level(select/insert/update/delete) stuff, the apps don't even really *know* about tables.

On top of database schema we have an API that does the data stuff.In the application we call this API but have our own application logic as well(only application logic is in the application, data logic is -- well, right where it belongs -- next to the data, waiting for the 'next great programming paradigm to come along')

make software components modular software modules must carry out a very specific task(and be very efficient at carrying it out) each software module should be loosely coupled (to limit dependencies) It removes the need for triggers as all inserts, updates and deletes are wrapped in APIs. Instead of writing triggers you simply add the code into the API. I loath triggers. It prevents people who don't understand SQL writing stupid queries.All SQL would be written by PL/SQL developers or DBAs, reducing the likelyhood of dodgy queries. The underlying structure of the database is hidden from the users, so I can make structural changes without client applications being changed.The API implementation can be altered and tuned without affecting the client application. The same APIs are available to all applications that access the database. No duplication of effort. Flexibility and Elegant programming• PL/SQL is the most efficient language for data manipulation