A dumb mistake … again and again …

Not to long ago I was analyzing a performance issue on a customers system.
In the course of this I needed to see some aggregated data, so I opened a transaction where I can put in custom sql and ran it without removing the number of lines to be returned limitation.

The result was astonishing to me in the first place.

The statement looked something similar to this:

SELECT col1, count(*) as CNT
FROM t1
WHERE rownum <2
GROUP BY col1
ORDER BY count(*);

Obviously what I wanted was the col1-Value that appeared at least often in the table.
What I got as a result was this:

COL1   CNT
------- -----
val1     1

Since I knew that this specific value appeared several hundred times in the table this result was wrong. Or better it was the right answer to the wrong question.

The keypoint here is, when the database performs the restriction for the ROWNUM <1 predicate. And that is when it gathers the rows that would fit my other conditions. In my example there where no other conditions – so the database took rows as long as ROWNUM <1 was fullfilled. After it got one row this was the case so that one row was further processed with the GROUP BY and then with the ORDER BY clause.
Basically what I told the database was:
Perform that my query but touch only one row at all. And that the database did.

The Oracle explain plan clearly shows this, too:

create table t1 as select mod(rownum, 3) as col1, 'x' as col2 from dba_objects;
select col1, count(*) from t1 group by col1 order by count(*);
      COL1   COUNT(*)
---------- ----------
         0      15934
         1      15935
         2      15935

Now the query with rownum in place:

      COL1   COUNT(*)
---------- ----------
         1          1
Execution Plan
----------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (ORDER BY)
   2    1     SORT (GROUP BY)
   3    2       COUNT (STOPKEY)
   4    3         TABLE ACCESS (FULL) OF 'T1'

As it’s easy to see, the COUNT (STOPKEY) operation is the very first after accessing the table. So before anything else is done the database tries to obey the “touch just one row”-limitation.

In order to get the row 0 (that is the least often occuring value of col1) with the correct count(*) value we’ve to reformulate the statement like this:

select * from (
            select col1, count(*)
            from t1 group by col1
            order by count(*)
            ) where rownum <2;
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   COUNT (STOPKEY)
   2    1     VIEW
   3    2       SORT (ORDER BY STOPKEY)
   4    3         SORT (GROUP BY)
   5    4           TABLE ACCESS (FULL) OF 'T1'

The execution plan now shows that the row-number restriction is applied after all other predicates have been evaluated and thus giving us back just the first row that we wanted:

      COL1   COUNT(*)
---------- ----------
         0      15934

As this mistake happened to me (although I do SQL programming for years now) by accident, it might (and does) happen to others as well. So better double check if your SQL makes sense to you and the database the same way – otherwise the results will be dubious.

By the way: the very same behaviour can be found in MaxDB databases – actually my mistake happened to me on a MaxDB instance. I just took the Oracle explain plan here because it shows that nice “(STOPKEY)” entry to mark up the operation.

Best regards,

Lars

MaxDB Database Studio 7.7 is available!

I’ve recently written about it (Something new is coming up…).

We presented it on the Las Vegas TechEd (and it will also be shown in Munich).

One question was present all the time: When will the new MaxDB Tool for DBAs/Developers be finally available to the (SAP)-public?

Now there is an answer an it is: NOW!

The MaxDB Database Studio 7.7.03 Build 18 is available for download on the SAP Service Marketplace since last friday.
Four versions are available: Linux 32/64 Bit as well as Windows 32/64 Bit.
Since the Database Studio is completely Ecliipse-based (and thus Java-based) the Linux-Build should also run on the UNIX-derivates on which Eclipse works. Anyhow since only Linux and Windows are usually used for Workstations the Database Studio has been developed and tested only on these two plattforms.

For instructions where to download it and how to install it, please see note 1097311.

ATTENTION: be aware that once you install the Database Studio it will update the independent-part of your MaxDB installation to 7.7.03 Build 18. You may not want to do this on your production server. Therefore please try it out in a “sandbox”-environment.

That’s it for today. Go for it – play with it – have fun!

Lars

p.s.
as far as I’ve seen the download is currently only available via SAP Service Marketplace. Therefore at the moment you need to be a SAP customer to get your hands on this tool. Nevertheless the last thing I heard was that the DB Studio will also be available in the SDN Downloads later on.

Stay up-to-date! How to keep up with the latest changes in SAP notes.

One of my duties in SAP Support is to facilitate the knowlegde transfer from Development to the Supporters. For some time one of the measures I took was to send out every week an email that consisted of all changed/new notes of the last week. The list of notes was liked to the SAP Service Marketplace notessearch (http://service.sap.com/notes) so that my collegues were able to just click on each note and check what was new about it.

The major drawback of this approach to me is obvious:
I had to make up that list each and every week. So I looked out for an option to make my life easier and put more power into the hands of my collegues at the same time. Happily: this option is already present in the Service Marketplace notes search.

Above you see the notes search form how it appears in the Service Marketplace. I assume most of you already know this. Nevertheless there are some features that may be rather seldom used – like the search template feature.

To create a template for a search for the notes of a specific component (Application Area) created or changed in the last 7 days just follow 4 easy steps:

1) Enter the component, e.g. BC-DB-SDB* (don’t forget the * to include all components “below” the level specified)

2) Add Additional Criteria: just click on the little blue box and you get this dialog:

2) 1 – Select “Last 7 days” and “All changes” to make sure you get also those notes that just got released by the reviewer without any changes to its content.

2) 2 – Go back to the initial search form by clicking the “Select” Button.

Back on the initial search form we’re now ready to save this template. To do so, just click the “Save as Template” (3) Button. You may also want to change the number of notes displayed at a max on one page – but you can change this also later on.

After the template has been named and saved it is now available everytime just by clicking the “Load Template” 4) Button in the upper right corner of the search form. If you like to modify a template, just load it, make your changes and save it with the same name.

That way everybody can help himself with the “updated notes list” and I can spent more time doing other work (or leave early ;-))

Best regards,
Lars

p.s.
If you want to keep track of just one or a few notes that are important for you, you may want to go for a note subscription. How you do that is described here: A useful hint on notes