performance

Jonathan Lewis's picture

Cartesian join

Some time ago I pulled off the apocryphal “from 2 hours to 10 seconds” trick for a client using a technique that is conceptually very simple but, like my example from last week, falls outside the pattern of generic SQL. The problem (with some camouflage) is as follows: we have a data set with 8 “type” attributes which are all mandatory columns. We have a “types” table with the same 8 columns together with two more columns that are used to translate a combination of attributes into a specific category and “level of relevance”. The “type” columns in the types table are, however, allowed to be null although each row must have at least one column that is not null – i.e. there is no row where every “type” column is null.

Jonathan Lewis's picture

Not Exists

The following requirement appeared recently on OTN:

Jonathan Lewis's picture

Counting

There’s a live example on OTN at the moment of an interesting class of problem that can require some imaginative thinking. It revolves around a design that uses a row in one table to hold the low and high values for a range of values in another table. The problem is then simply to count the number of rows in the second table that fall into the range given by the first table. There’s an obvious query you can write (a join with inequality) but if you have to join each row in the first table to several million rows in the second table, then aggregate to count them, that’s an expensive strategy.  Here’s the query (with numbers of rows involved) that showed up on OTN; it’s an insert statement, and the problem is that it takes 7 hours to insert 37,600 rows:

marco's picture

Tuning Database XQuery Statements (2)

So we concluded the post “Tuning Database XQuery Statements (1)” with the following SQL statement…

marco's picture

Tuning Database XQuery Statements (1)

I had a question of a colleague to have a look at a statement that…

marco's picture

Presentation material & E-learning videos – In-Memory Column Store Workshop with Maria Colgan

You can now download and have another look at the presentations used during the In-Memory…

marco's picture

In Memory XML Performance (XVM)

I wouldn’t believe the bad XMLType performance statement given stated in Martin Preiss’ blog post,…

marco's picture

DBMS_INMEMORY_ADVISOR

When you follow the Oracle in-memory / optimizer team, then you have probably seen this…

Jonathan Lewis's picture

255 columns

You all know that having more than 255 columns in a table is a Bad Thing ™ – and surprisingly you don’t even have to get to 255 to hit the first bad thing about wide tables. If you’ve ever wondered what sorts of problems you can have, here are a few:

Charles Hooper's picture

To N or Not to N, is SYS_OP_C2C the Question; Oracle NVARCHAR Slow?

February 8, 2015 I was recently contacted about an odd performance issue with a software product named TDM, where certain SQL statements submitted to an Oracle 9.2.x database required roughly 30 seconds to execute when a nearly instantaneous response was expected.  The person who reported the issue to me provided a sample SQL statement that […]

Syndicate content