Reader small image

You're reading from  Oracle Database 11gR2 Performance Tuning Cookbook

Product typeBook
Published inJan 2012
Reading LevelIntermediate
PublisherPackt
ISBN-139781849682602
Edition1st Edition
Languages
Right arrow
Author (1)
Ciro Fiorillo
Ciro Fiorillo
author image
Ciro Fiorillo

Ciro Fiorillo is an IT professional and consultant with experience of more than a decade in different roles (developer, analyst, DBA, project manager, data and software architect) among software industries. He has worked on different technologies and architectures, such as Oracle, SQL Server, Delphi, C# and .NET Framework, C/C++, Java, PHP, COBOL, Fortran, and Tibco. Ciro is currently employed as Lead Software and Data Architect with FinWin Srl, a software house specializing in banking and loans applications. As a freelancer he writes articles for websites and printed magazines about software and computing, participates in workshops, and teaches C++ and Fortran parallel programming with Intel Software tools. Ciro can be reached at ciro@cirofiorillo.com.
Read more about Ciro Fiorillo

Right arrow

Sorting and indexing


We have seen various aspects of indexing in Chapter 3, Optimizing Storage Structures. In this recipe, we will focus on how to use indexes to avoid sort operations.

How to do it...

The following steps will demonstrate how to use indexes and avoid sorts:

  1. Connect to the SH schema:

    CONNECT sh@TESTDB/sh
    
  2. Execute an ORDER BY query:

    SET AUTOT TRACE EXP STAT
    SELECT CUST_FIRST_NAME, CUST_LAST_NAME, CUST_CITY
    FROM CUSTOMERS
    ORDER BY CUST_CITY;
    
  3. Execute a SELECT DISTINCT query:

    SET AUTOT TRACE EXP STAT
    SELECT DISTINCT CUST_CITY FROM CUSTOMERS;
    
  4. Execute a GROUP BY query:

    SET AUTOT TRACE EXP STAT
    SELECT CUST_CITY, COUNT(*)
    FROM CUSTOMERS
    GROUP BY CUST_CITY;
    
  5. Add an index on the CUSTOMERS table:

    CREATE INDEX IX_CUST_CITY ON CUSTOMERS(
      CUST_CITY, CUST_LAST_NAME, CUST_FIRST_NAME);
    
  6. Execute an ORDER BY query (the same query as in step 2):

    SET AUTOT TRACE EXP STAT
    SELECT CUST_FIRST_NAME, CUST_LAST_NAME, CUST_CITY
    FROM CUSTOMERS
    ORDER BY CUST_CITY;
    
  7. Execute a SELECT DISTINCT query (the same...

lock icon
The rest of the page is locked
Previous PageNext Page
You have been reading a chapter from
Oracle Database 11gR2 Performance Tuning Cookbook
Published in: Jan 2012Publisher: PacktISBN-13: 9781849682602

Author (1)

author image
Ciro Fiorillo

Ciro Fiorillo is an IT professional and consultant with experience of more than a decade in different roles (developer, analyst, DBA, project manager, data and software architect) among software industries. He has worked on different technologies and architectures, such as Oracle, SQL Server, Delphi, C# and .NET Framework, C/C++, Java, PHP, COBOL, Fortran, and Tibco. Ciro is currently employed as Lead Software and Data Architect with FinWin Srl, a software house specializing in banking and loans applications. As a freelancer he writes articles for websites and printed magazines about software and computing, participates in workshops, and teaches C++ and Fortran parallel programming with Intel Software tools. Ciro can be reached at ciro@cirofiorillo.com.
Read more about Ciro Fiorillo