Jonathan Lewis

Jonathan Lewis is well-known in the Oracle world as a freelance consultant with 22 years of experience with the Oracle RDBMS engine. His specialist skills are in the area of physical database design, and solving performance issues. Despite the differences in the software, he finds that the fundamental principles of solving performance issues don't really seem to change as you move from Oracle to SQL Server.

Jonathan is the author of 'Cost Based Oracle – Fundamentals' published by Apress, and 'Practical Oracle 8i – Designing Efficient Databases' published by Addison-Wesley, and has contributed to three other books about Oracle. His blog is at http://jonathanlewis.wordpress.com, where his first note on SQL Server 2008 appears at: http://jonathanlewis.wordpress.com/2010/02/04/sql-server/

15 January 2013
15 January 2013

Compression in Oracle – Part 1: Basic Table Compression

0
10
In this short series on compression in Oracle, we will be looking at the various types of compression used by traditional Oracle systems, this means: (i) basic table compression, (ii) OLTP table compression, and (iii) index compression. I won’t be discussing the hybrid columnar compression (HCC) that appeared with Exadata (though I may write up a mini-series on that topic… Read more
0
10
10 December 2012
10 December 2012

Statistics in Oracle and SQL Server

0
0
This video is a follow up to the webinar broadcast by Red Gate on 12 December 2012. Please leave a response and ask questions at the bottom of the page. Watch video This webinar features a demonstration of Schema Compare for Oracle. You can download a free 14 day trial here: Download Schema Compare for Oracle. The better the information… Read more
0
0
15 October 2012
15 October 2012

Temporary Tables in Oracle and SQL Server

0
0
This video is a follow up to the webinar broadcast by Red Gate on 13th September 2012. Please leave a response and ask questions at the bottom of the page. Watch video Summary Temporary tables are often used in applications for processes that are difficult to complete in a single pass. They are widely used in SQL Server development and… Read more
0
0
17 July 2012
17 July 2012

ANSI Outer 2

0
0
A comment on a recent post of mine pointed me to a question on the OTN SQL and PL/SQL Forum where someone had presented a well-written test case of an odd pattern of behaviour in ANSI SQL. I made a couple of brief comments on the thread, but thought it worth highlighting here as well. The scripts to create the required tables… Read more
0
0
29 May 2012
29 May 2012

Ch-ch-ch-ch-changes

0
0
For those not familiar with Richard Foote’s extensive blog about indexes (and if you’re not you should be) – the title of this note is a blatant hi-jacking of his preferred naming mechanism. It’s just a short note to remind myself (and my readers) that anything you know about Oracle, and anything published on the Internet – even by Oracle Corp. and… Read more
0
0
30 April 2012
30 April 2012

Oracle Heap Tables or SQL Server Clustered Indexes? Jonathan Lewis and Grant Fritchey Live Debate

0
0
The following video and set of resources are from the webinar broadcast by Red Gate on 7 June 2012. Oracle and SQL Server may both share a common language, but certain things are handled quite differently. Jonathan Lewis (OakTable Network, Oracle Ace Director) is used to seeing heap tables (almost) everywhere, but Grant Fritchey (Microsoft SQL Server MVP) is used… Read more
0
0
17 February 2012
17 February 2012

Session SQL

0
0
From time to time a request for a query to “show the current SQL” for each session appears on the internet. It’s not really difficult to write something suitable, but this particular request is an interesting demonstration of how much inertia there is in the Oracle world. Here’s a recent suggestion of what the query should look like: select sess.sid,… Read more
0
0
16 December 2011
16 December 2011

Indexes are Tables

0
0
Oracle’s optimizer has many strategies for acquiring the data you want with the minimum of work. Some of these strategies are really quite subtle and sophisticated – think of the Star Transformation, for example, which uses a two-phase approach to dimension tables; or the Index Join which does hash joins between indexes to avoid visiting tables. Despite all the clever things… Read more
0
0
23 June 2010
23 June 2010

Oracle to SQL Server, Crossing the Great Divide, Part 3

We soon learn, in SQL Server, that heaps are a bad thing, without necessarily understanding how or why. Jonathan Lewis is an Oracle expert who doesn't like to take such strictures for granted, especially when they don't apply to Oracle. Jonathan discovers much about how SQL Server places data, and concludes from his experiments that heaps perform badly in SQL Server because you cannot specify a fill factor for them.… Read more
26 May 2010
26 May 2010

Oracle to SQL Server: Crossing the Great Divide, Part 2

A well-known Oracle expert records faithfully his struggles with the unfamiliar : SQL Server. He now sets himself the task of creating a table with a million rows of random data. As one might expect, it is the lack of familiarity with the workarounds and tricks of SQL Server that trips him up. His journey brings us fresh insights, and a glimpse at the alternative-universe of Oracle.… Read more
28 April 2010
28 April 2010

Oracle to SQL Server: Crossing the Great Divide, Part 1

When a SQL expert moves from Oracle to SQL Server, he can spot obvious strengths and weaknesses in the product that are too familiar to be apparent to the SQL Server DBA. Jonathan Lewis is one such expert: In this article he records his train of thought whilst investigating the mechanics of the SQL Server database engine. The result makes interesting reading.… Read more
25 February 2010
25 February 2010

Designing Efficient SQL: A Visual Approach

Sometimes, it is a great idea to push away the keyboard when tackling the problems of an ill-performing, complex, query, and take up pencil and paper instead. By drawing a diagram to show of all the tables involved, the joins, the volume of data involved, and the indexes, you'll see more easily the relative efficiency of the possible paths that your query could take through the tables.… Read more