info.michael-simons.eu

Optimizing the Oracle Query Optimizer

The Oracle Query Optimizer does a great job in rewriting Queries. For example the nested subquery in

SELECT C.cust_last_name, C.country_id 
FROM    customers C 
WHERE EXISTS (SELECT 1 
                          FROM sales S 
                          WHERE S.quantity_sold > 1000 AND 
                                        S.cust_id = C.cust_id);

will be rewritten into the following query, using a semi-join:

SELECT C.cust_last_name, C.country_id 
FROM customers C, sales S 
WHERE S.quantity_sold > 1000 AND 
C.cust_id S= S.cust_id;

(Example from Optimizer Transformations: Subquery Unnesting part 1)

In the first form, the subquery will be invoked for each entry in customers, replacing the value of the correlated column C.cust_id. Thus the query cannot be parallelized among other implications. Most of the time, if the columns are correctly indexed, the semijoin will be much faster.

This form of rewriting is called “unnesting”.

Consider the following query:

SELECT C.cust_last_name, C.country_id 
FROM    customers C, (SELECT f_compute_some_complicated_value(s.quantity_sold), S.cust_id
                          FROM sales S                          
                          WHERE S.quantity_sold > 1000) S
WHERE  S.cust_id = C.cust_id

The subquery in the from clause its called a derived table. It will be rewritten as:

SELECT C.cust_last_name, C.country_id, f_compute_some_complicated_value(s.quantity_sold), S.cust_id
FROM    customers C, Sales S
WHERE  S.cust_id = C.cust_id 
AND S.quantity_sold > 1000)

This technique is called query merging. Here the join should also be faster in most cases.

Both techniques are good and will improve many queries.

But consider a scenario where the subqueries query remote tables (via database link for example) or call PL/SQL or even Java stored procedures and the main table (customes in the examples) contains many more rows than the joined table or the where clause is less selective then the where clause of the subquery: The subquery will be executed as many times as there are rows in the main table. This can lead to very poor performance.

Luckily, the optimizer can be given hints:

To stop unnesting, use the NO_UNNEST hint:

SELECT C.cust_last_name, C.country_id 
FROM    customers C 
WHERE EXISTS (SELECT /*+ NO_UNNEST */ 1 
                          FROM sales S 
                          WHERE S.quantity_sold > 1000 AND 
                                        S.cust_id = C.cust_id);

Furthermore, i’d rewrite the query like so:

SELECT C.cust_last_name, C.country_id 
FROM    customers C 
WHERE C.cust_id =ANY (SELECT /*+ NO_UNNEST */ S.cust_id 
                                       FROM sales S 
                                       WHERE S.quantity_sold > 1000);

Where the EXISTS clause triggers the subquery for each item in customes, the =ANY clause will trigger the subquery only once. Both queries are semantically identical.

To stop the merging of derived tables, use the NO_MERGE hint in the surrounding select clause:

SELECT /*+ NO_MERGE(S) */ C.cust_last_name, C.country_id 
FROM    customers C, (SELECT f_compute_some_complicated_value(s.quantity_sold), S.cust_id
                          FROM sales S                          
                          WHERE S.quantity_sold > 1000) S
WHERE  S.cust_id = C.cust_id

The NO_MERGE hint takes the alias of the derived table as a parameter.

Combining both techniques brought a complex query down from 40s to just about 1s. We are using an Oracle 10g Database, but the hints also apply to 11g.

Filed in English posts, Oracle | Comments (1) »

Unicode substrings in Ruby 1.8.x

Here is how to get correct unicode substrings in Ruby 1.8.x

require 'jcode'
$KCODE = 'u'
 
"öäüÖÄÜß".split(//)[4,2].join
# ÄÜ

Don’t know, if this performes well… In case you have better ideas, please drop me a comment.

Filed in English posts | Comments (0) »

Create reusable MySQL schema dumps

In case you need a MySQL schema transferred from one host to another and the schema names differ, you can ran into problems with a standard MySQL dump.

Use the following statement to create a schema dump that contains all table and view definitions as well as all stored procedures without a reference to the original schema:

 mysqldump -uroot -p name_of_the_original_schema --no-data --opt --routines | sed 's/`name_of_the_original_schema`.//g' > dump.sql

The dump will only contain the schema definition and no data. Calls to routines will not be prefixed with a schema name.

Filed in English posts, Shortcuts | Comments (0) »

iTunes terminal tipps 2010

4 years ago i had a first iTunes terminal tipp, here is a round up (all terminal tipps require Mac OS X):

Invert the iTunes Store links in the list

The littler arrows can either point to the iTunes store or to the same artist or album in your library. Invert their behavior to let them point to your files instead of the store:

defaults write com.apple.iTunes invertStoreLinks -bool YES

Undo this with

defaults write com.apple.iTunes invertStoreLinks -bool NO

Disable them with

defaults write com.apple.iTunes show-store-arrow-links -boolean FALSE

Enable them with

defaults write com.apple.iTunes show-store-arrow-links -boolean TRUE

Restore the horizontal stoplight

Again, Apple messes with it’s own UI guidelines. To restore sanity, use

defaults write com.apple.iTunes full-window -boolean TRUE

Reenable the vertical layout introduced with iTunes 10 through

defaults write com.apple.iTunes full-window -boolean FALSE

Enable half-star ratings

The following will let you rate your songs with half-star ratings (i.e 3.5 stars):

defaults write com.apple.iTunes allow-half-stars -bool TRUE

If you don’t like the fact, that the half-star ratings will round on any i* device, turn it off again:

defaults write com.apple.iTunes allow-half-stars -bool FALSE

Filed in Apple | Comments (0) »

Oracle “sleep” procedure: DBMS_LOCK.SLEEP

There’s a nice little “sleep” procedure in Oracle: A procedure that stops the execution of the current thread for n seconds.

Strangely, this method can be called in SQL*Plus like so:

EXEC dbms_lock.sleep(10);

but not in another stored procedure or function like so

CREATE OR REPLACE PROCEDURE FOOBAR AS
BEGIN
  DBMS_LOCK.SLEEP(1);
END;
/

To use “sleep” in your procedures or functions, login as administrator to your database (or ask you admin to to so) and create the following objects:

CREATE OR REPLACE PROCEDURE sleep(seconds NUMBER) AS
BEGIN
  DBMS_LOCK.SLEEP(seconds);
END;
/
 
CREATE OR REPLACE public synonym sleep FOR sleep;
 
GRANT EXECUTE ON sleep TO public;

and in your procedure just use “sleep”.

Filed in English posts, Oracle | Comments (18) »