LarsBlog

I recently had the pleasure(?) of optimising a query in Oracle, and discovered that splitting the query into two parts, each returning part of the required results, and then creating indexes to optimise these seperately was a workable strategy.  in this case, the two query parts were much easier/more practical to optimise via indexes than the whole query, and Oracle's query engine is smart enough to use both indexes when executing the original un-split query.

In my case, I had a query that was structured like this:

select * from items
    WHERE store_id = 12345
    and
    (
        (
            external_item_id = '67890' AND
                (
                    (topcategory IS NULL AND secondcategory IS NULL
                        and thirdcategory is null and fourthcategory is null)
                    OR
                    (topcategory = 'CARS' AND secondcategory = 'SPORTS'
                        and thirdcategory = 'NISSAN' and fourthcategory = '350Z')
                )
        )
        OR
        (
            topcategory = 'CARS' AND secondcategory = 'SPORTS' AND
                (
                    (thirdcategory IS NULL AND fourthcategory IS NULL)
                    OR
                    (thirdcategory IS 'NISSAN' AND fourthcategory IS NULL)
                    OR
                    (thirdcategory IS NULL AND fourthcategory IS '350Z')
                    OR
                    (thirdcategory IS 'NISSAN' AND fourthcategory IS '350Z')
                )
        )
    )

There was an existing index on the fields topcategory and secondcategory, but Oracle was performing a full table scan for the query because of all the ORs and the NULLs (Oracle doesn't include null values in it's indexes by default).  The table was quite large, and having to perform a full scan was expensive - the oracle 'explain plan' in SQL Developer showed a cost of around 722.

At first I thought there would be no way to force it to use an index.  I tried creating an index for store_id but it was not used because this column did not contain enough unique values.  I then found it helped conceptially to break the query into two subqueries, eg:

select * from items
    WHERE store_id = 12345 and external_item_id = '67890' AND
        (
            (topcategory IS NULL AND secondcategory IS NULL and
                thirdcategory is null and fourthcategory is null)
            OR
            (topcategory = 'CARS' AND secondcategory = 'SPORTS' and
                thirdcategory = 'NISSAN' and fourthcategory '350Z')
        )

and also:

select * from items
    WHERE store_id = 12345 and topcategory = 'CARS' AND secondcategory = 'SPORTS' AND
        (
            (thirdcategory IS NULL AND fourthcategory IS NULL)
            OR
            (thirdcategory IS 'NISSAN' AND fourthcategory IS NULL)
            OR
            (thirdcategory IS NULL AND fourthcategory IS '350Z')
            OR
            (thirdcategory IS 'NISSAN' AND fourthcategory IS '350Z')
        )

The second of these queries was already optimised by the existing index on topcategory and secondcategory.  To optimise the first query, I could add an index that encompassed both store_id and external_item_id.

Initially, I thought I would then restructure my SQL to use these two new queries as seperate sub-selects or in a UNION, then each of them would be able to utilise it's own indexes for faster execution.  But - it appears Oracle is smart enough to do this on it's own.  As soon as I created the index on store_id and external_item_id, the original/unchanged query cost came down from 722 to 14 by using my new index.  Yay!