This blog entry o ...

One of my favorite things to do (when I am not tuning and managing Oracle databases) is to don a scuba tank, mask, and exposure suit and slip beneath the waters surface. For a long time I wondered how to reconcile these two interests and then it came to me.

Over the decades I’ve worked with many data modelers – and I’ve learned something key to building a great model that is not always intuitively obvious: spend at least 50% of your data modeling time on the relationships. Often modelers are so overly concerned with the entities, attributes and unique identifiers or keys ...

The Major Enhancements in the 1.5 Release

Of course, we spent lots of time just fixing bugs and making lots of small changes. But there were some very critical "big ticket" enhancements that were requeste ...

In Quest SQL Optimizer, transformation rules relating to index usage are designed to guide the database SQL optimizer as to how it should use the indexes for a specific SQL statement.  Although those rules may familiar to most of SQL developers, but it is still worth to illustrate some commonly used rules for SQL beg ...

I hope that you have already heard about this new testing tool. If not, I offer a quick intro/overview ...

Now, for sure, we need really good tools to help get the job done, but we also need to think abo ...

Many times I am asked how to tailor the initialization parameters for a specific process or user. Essentially the easiest way to set custom initialization parameters for a particular session is to use the ALTER SESSION SET command in a logon trigger keyed to a specific schema or schemas. For example, say we had an application where we wanted to set ...

In old version of Oracle database, it is easy to control the join path by reordering the tables in table list after the FROM clause, but it is getting difficult to control the join path in today’s cost based SQL optimizer, I am going to introduce method that is applicable in most databases such as Oracle, Sybase, DB2 and SQL Server in the following:

To control a join path, we cannot tell the database SQL optimizer which path is the best one to select. Instead, we add something to the syntax of the SQL statement that causes an increase to the cost of the current join path selected by database SQL optimizer. Let’s take a look at the following example of two table join scenarios.

Last month I wrote about “Why Reverse Engineering is Always Worthwhile.” So the logical next issue to examine is forward engineering – its different approaches and its many comparative benefits. The best way to do this is via an analogical example

Oracle’s Oracle10g requires more memory than was ever required before. If you utilize any of the new features such as automatic storage management (ASM) and automatic shared memory management (ASMM) then you really need to pay attention to what memory is doing in 10g.

When an exception is raised, one of the most important pieces of information a programmer would love to get hold of is the line of code that caused the exception to be raised.  Before Oracle 10g came long, the only way to get this information was to allow the exception to go unhandled and then view the error stack. The following chain of program calls shows you what I mean.

With each new release of a database, be it Oracle or one of their competitors, we hear the cry that this release will make DBAs as we know them obsolete. We hear again and again how this automated feature set or that new GUI interface will automate the DBA job. So far, all of these claims of DBA obsolescence have been for naught, to mis-quote Samual Clements “The reports of the DBAs death are greatly ex ...

Welcome to my Toad World blog! I will offer on this blog a wide variety of tips and incredibly deep and surprising insights on the Oracle PL/SQL language (the object of my obsessive personality for the last 10+ years).  I look forward to your responses and critiques. That is: I hope you will engage with these ideas and feel perfectly comfortable with disagreeing with me, offering other, perhaps better, i ...

In many shops that I visit, neither the DBA’s nor the database developers place much value on performing data modeling. Often there are seemingly sound reasons for this exclusion: the historically high price of data modeling tools, data models are not required as a project milestone or deliverable, and quite often the database was simply either inherited or supports a third party application – ...