Markus Winand - SQL Performance Explained (vol. 1: Basic Indexing)
Here you can read online Markus Winand - SQL Performance Explained (vol. 1: Basic Indexing) full text of the book (entire story) in english for free. Download pdf and epub, get meaning, cover and reviews about this ebook. year: 2011, publisher: use-the-index-luke.com, genre: Computer. Description of the work, (preface) as well as reviews are available. Best literature library LitArk.com created for fans of good reading and offers a wide selection of genres:
Romance novel
Science fiction
Adventure
Detective
Science
History
Home and family
Prose
Art
Politics
Computer
Non-fiction
Religion
Business
Children
Humor
Choose a favorite category and find really read worthwhile books. Enjoy immersion in the world of imagination, feel the emotions of the characters or learn something new for yourself, make an fascinating discovery.
- Book:SQL Performance Explained (vol. 1: Basic Indexing)
- Author:
- Publisher:use-the-index-luke.com
- Genre:
- Year:2011
- Rating:4 / 5
- Favourites:Add to favourites
- Your mark:
SQL Performance Explained (vol. 1: Basic Indexing): summary, description and annotation
We offer to read an annotation, description, summary or preface (depends on what the author of the book "SQL Performance Explained (vol. 1: Basic Indexing)" wrote himself). If you haven't found the necessary information about the book — write in the comments, we will try to find it.
Markus Winand: author's other books
Who wrote SQL Performance Explained (vol. 1: Basic Indexing)? Find out the surname, the name of the author of the book and a list of all author's works by series.
SQL Performance Explained (vol. 1: Basic Indexing) — read online for free the complete book (whole text) full work
Below is the text of the book, divided by pages. System saving the place of the last page read, allows you to conveniently read the book "SQL Performance Explained (vol. 1: Basic Indexing)" online for free, without having to search again every time where you left off. Put a bookmark, and you can go to the page where you finished reading at any time.
Font size:
Interval:
Bookmark:
One of the major issues with mature software is that it is often surrounded by myths. The Oracle database seems to be particularly vulnerable to this phenomenon, probably because the name Oracle is used since 1979. There are some myth that were the best current practice at that time but are outdated since many releases.
In an attempt to thin out the dark woods of Oracle database myths, I list and explain some of the most common myth and misbelieves here.
The most prominent myth is that an index can become degenerated after a while and must be re-built , the number of entries in the index must typically grow by a factor of hundred to increase the index depth by one level.
Rebuilding an index might reduce the number of leaf nodes by about 20% - 30%. The most you can possibly expect from this reduction is 20%-30% for very expensive operations like a FULL INDEX SCAN. The typical INDEX UNIQUE SCAN gain of an index rebuild is 0%-2% because the depth of the index is not reduced by the rebuild.
Every time a compound index is created, the order of the columns must be chosen wisely. is devoted to this question.
However, there is the myth that you should always put the most selective column to the first position; that is just wrong. Others have tried to proof that before, but the myth persists.
The most important factor to consider when defining a concatenated index is the number of statements it can support.
After that, there are even reasons to put the least selective column first:
To utilize an
INDEX SKIP SCANTo accomplish a better compression
However, that's advanced stuff. But the most important factor...uhm, did I say that before?
I'm not the first to fight this myth. Here are some more references that disproof the myth:
Tom Kyte - "Expert Oracle Database Architecture" - Apress
Devotes the section "Myth: Most Discriminating Elements Should Be First" to this topic.
Tom Kyte also commented this on AskTom.
Guy Harrison - Oracle Performance Survival Guide - Prentice Hall
In "Guidelines for Concatenated Indexes":
Don't automatically put the most selective term first in a concatenated index. [...]
Jonathan Lewis - Author of "Cost-Based Oracle Fundamentals" - Apress
I didn't find it explicitly in his book, however, Jonathan Lewis blogged it:
One of the often-quoted fairy-tales about indexes was the directive to put the most selective column first. It was never a sensible rule of thumb (except, possibly, prior to version 6.0). But if you have a lot of code that uses this nvl() construct then there may actually be a good argument for adopting this approach. (Theres a better argument for fixing the code, of course).
The core of truth behind this myth is related to indexing independent range conditionsthat is the only case where the selectivity should influence the index design (see ).
The source of this myth is rather easy to understand when you look at the correctly expressed statement:
The Oracle database does not include rows into an index if all indexed columns are NULL.
The difference between the myth and the reality is smallit seems that they myth is a sloppy form of the truth.
The truth is that NULL can be indexed by adding another, not nullable, column to the index:
Read for the full story.
The core of truth behind the Dynamic SQL is Slow myth is rather simple; dynamic SQL can be slowwhen done wrong.
So, what's dynamic SQL? It's the opposite of embedding SQL directly into the program's source code. PL/SQL and other stored procedure dialects are good examples for embedded SQL but it's also possible to embed SQL into C and other languages. The benefit of embedded SQL is that it integrates very smoothly with the respective programming language. However, embedded SQL is compiled into the program. It can not change when the program runsit's static.
Dynamic SQL, to the contrary, is handled as string within the application. The program can change it at runtime. However, that's probably the cause of the myth. Consider the following example:
String sql = "SELECT first_name, last_name" + " FROM employees" + " WHERE employee_id = " + employeeId;ResultSet rs = con.executeQuery(sql);So, is that dynamic SQL? The actual SQL string is built dynamically during program runtime. However, that is not what's meant with dynamic SQL because the structure of the SQL statement doesn't change. That example should be changed to use that's all, nothing dynamic here.
Real dynamic SQL changes the structure of the statement during runtime. That's something that like a conditional where clause :
The code constructs an SQL statement to fetch employees based on any combination of three filter criteria. Although the code is rather awkward, the constructed SQL can be executed and the database will use the best index to retrieve the data. The problem with that code is not only the highlighted SQL Injection vulnerability, but also that it introduces a very high parsing overhead.
That means that the database has to recreate the execution plan every time, because the inlined search termsthat are different every timeprevents caching. explains parsing overhead in more detail.
Implementing the example with plain JDBC and bind parameters yields even more awkward codeit's omitted here. However, most ORM frameworks provide a sufficiently convenient way to dynamically create SQL using bind parameters. The following overview shows some samples:
The following sample demonstrates Hibernate's Criteria classes:
Criteria criteria = session.createCriteria(Employees.class);if (subsidiaryId != null) { criteria.add(Restrictions.eq("subsidiaryId", subsidiaryId));}if (employeeId != null) { criteria.add(Restrictions.eq("employeeId", employeeId));}if (lastName != null) { criteria.add( Restrictions.eq("lastName", lastName) .ignoreCase() );}When providing LAST_NAME only, the following SQL is generated by Hibernate (Oracle):
Please note that a bind parameter is used and the LOWER function to implement the .
The Java Persistence API (JPA) has a similar functionality:
However, it's less straight and doesn't support a native case-insensitive search that's probably good):
List predicates = new ArrayList();if (lastName != null) { predicates.add(queryBuilder.equal( queryBuilder.upper(r.get(Employees_.lastName)) , lastName. toUpperCase() ) );}if (employeeId != null) { predicates.add(queryBuilder.equal( r.get(Employees_.employeeId) , employeeId) );}if (subsidiaryId != null) { predicates.add(queryBuilder.equal( r.get(Employees_.subsidiaryId) , subsidiaryId) );}query.where(predicates.toArray(new Predicate[0]));Font size:
Interval:
Bookmark:
Similar books «SQL Performance Explained (vol. 1: Basic Indexing)»
Look at similar books to SQL Performance Explained (vol. 1: Basic Indexing). We have selected literature similar in name and meaning in the hope of providing readers with more options to find new, interesting, not yet read works.
Discussion, reviews of the book SQL Performance Explained (vol. 1: Basic Indexing) and just readers' own opinions. Leave your comments, write what you think about the work, its meaning or the main characters. Specify what exactly you liked and what you didn't like, and why you think so.