OVERVIEW

BY NEED

BY ROLE

BY INDUSTRY

Jonathan Lewis

Jonathan Lewis is a well-known figure in the Oracle world with more than 30 years’ experience using the software. He has published three books about Oracle – the most recent being “Oracle Core” published by Apress Nov 2011 – and contributed to three others. He has posted more than 1,400 articles on his blog and has spent a lot of his time contributing to newsgroups, forums, and User Group magazines and events around the world. Jonathan has been self-employed for most of his time in the IT industry. For the last 25 years he has specialised in short-term assignments – typically of a design, review, or troubleshooting nature – often spending no more than two or three days on a client site to address problems. After visiting more than 50 countries (and more than a dozen US states) to talk about or troubleshoot Oracle systems, he has finally gone into near-retirement, though still writes articles and does a little consulting over the internet. 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. His blog can be found at http://jonathanlewis.wordpress.com.
01 October 2013
01 October 2013

Histograms Part 2

0
5
In part 1 of this series we discussed the reasons why we might want to create some histograms to help the optimizer deal with skewed data distribution. We used an example of a simple status column in an orders table to demonstrate principles, talked about frequency histograms in particular, and highlighted a couple of problems associated with histograms. In part… Read more
0
5
28 August 2013
28 August 2013

Histograms Part 1 – Why?

0
7
In this short series on histograms we will be looking at the reasons why we need histograms and the methods Oracle has to create them. We will examine the costs of creating them and the potential they introduce for undesirable overheads and then we will review their potential for giving you stability problems in your execution plans. This overview w… Read more
0
7
27 February 2013
27 February 2013

Compression in Oracle – part 5: Costs of Index Compression

0
1
In the previous article in this series we looked at index compression, and the way in which Oracle stored index data for a compressed index, and we discovered that Oracle keeps two row directories the “main” directory and the “prefix” directory with slightly different structures even though there is still only a single “row heap”. We also examined the way… Read more
0
1
13 February 2013
13 February 2013

Compression in Oracle – part 4: Basic Index Compression

0
3
In the first three parts (Part 1: Basic Table Compression, Part 2: Read-Only Data, Part 3: OLTP Compression) of this series we examined table compression – both basic and the separately licensed OLTP compression. In this article we move on to index compression which, as we shall see, uses the same “deduplication” techniques as table compression but includes a cou… Read more
0
3
15 January 2013
15 January 2013

Compression in Oracle – Part 1: Basic Table Compression

0
17
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
17
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
1
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
1
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

0
39
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
0
39
26 May 2010
26 May 2010

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

0
37
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
0
37
28 April 2010
28 April 2010

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

0
124
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
0
124
25 February 2010
25 February 2010

Designing Efficient SQL: A Visual Approach

0
108
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
0
108