Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Solving Basic Trouble Spots of SQL Queries

DZone's Guide to

Solving Basic Trouble Spots of SQL Queries

Learn about the best ways to solve common SQL query problems, like tuning queries and indexing columns, to achieve optimized performance.

· Performance Zone ·
Free Resource

The Basics of an Index

Indexes help you to retrieve your data quickly- they do a “binary” search on your Oracle tables.

Choosing What Columns to Index

Choose a column, or combination of columns, which have the most unique values.  Do not choose a column or columns which have mostly the same values. If a column has mostly the same values, an index will hurt performance. First, you should know how the table is going to be used, and what data you are going after in the table. 

For example, for an employee table, you want the index on social security numbers (a unique value). However, if you were going to search for a name, you would want an index specifically on the NAME column.

Querying the Oracle Table

Set up your query in such a way that Oracle will use the index of the columns that you are querying. While processing a query, Oracle ranks the indexes and determines which one to use. When setting a column equal to a literal string, this is the highest ranking, and chances are that the index to that column will be used. The next highest ranking is greater than or less than (>, <)A literal.

There are several factors in the Where clause of a query that can cause an index not to be used:

Comparing Incompatible Data

  Employee_num = ‘1’
  There will be an implicit to_char conversion used.
  plant_code = 1234
  There will be an implicit to_num conversion used.

  Problem:
  Select * from emp Where employee_number = ‘10’;
  Select * from emp Where hire_date = ’15-feb-01’;


 Solution:
 Select * from emp Where employee_number = 3;
 Select * from emp Where hire_date = to_date(’15-feb-01’);

Using is null and is not null

Select * FROM s_emp WHERE Title is not null;
Since the column title has null values and is compared to a null value, the index cannot be used.

Problem:
Select * from emp Where title is not null;
Select * from emp Where title is null;

Solution:
Select * from emp where title >= ‘ ‘;
Use an Oracle hint
select /*+ index (s_emp) */ from s_empwhere title is null;
Oracle hints are always enclosed in /*+ */ and must co directly after the select clause. The index hint causes indexes to be used.

Tuning of SQL Queries

  •  Find out what indexes are on the tables.

  • Run explain plan.

  • Make sure most restrictive indexes are being used by avoiding SQL trouble spots.

  • Make sure most restrictive indexes are being used by using Oracle hints. 

  • For multi-table joins, join everything that can be joined together.

  • Use unions instead of outer join.

  • Use the “exists” subquery if only selecting items from one table.

Find out what indexes are on this table--- CRUCIAL:

Select column_name, index_name from all_ind_columns Where table_name =‘VEHICLE’;

Sample output:

Column_name                                                  Index name
Assembly_location_code                        FKI_VEHICLE_1
Production_date                                       FK_PRODUCTION_DATE
Vehicle id                                                    FKI_PRODUCTION_DATE
Vehicle_id                                                  PK_VEHICLE

Run an explain plan on this query:

In explain.sql
DELETE FROM plan_table WHERE statement_id = 'TEST';
EXPLAIN PLAN SET statement_id = 'TEST' FOR
select count(*) from vehicle where assembly_location_code = 'AP24A' and production_date = '06-apr-01'; --> Notice that an index will still not be used.

Results of explain plan:

EXECUTION_PATH

SORT AGGREGATE
TABLE ACCESS FULL VEHICLE
SORT AGGREGATE
SORT AGGREGATE
TABLE ACCESS FULL VEHICLE --->Notice that the full table scan is performed.

We should use Enable indexes to be used – ELIMINATE "TABLE ACCESS FULL:"

Explain.sql looks like:
DELETE FROM plan_table WHERE statement_id = 'TEST';

EXPLAIN PLAN SET statement_id = 'TEST' FOR
select /*+ index(vehicle) */ count(*) from vehicle where assembly_location_code = 'AP24A' and production_date = '06-apr-01'; ----> Notice an index will be used now

Results of explain plan:

EXECUTION_PATH

TABLE ACCESS BY INDEX ROWID VEHICLE
INDEX RANGE SCAN FKI_PRODUCTION_DATE
SORT AGGREGATE
TABLE ACCESS BY INDEX ROWID VEHICLE
INDEX RANGE SCAN FKI_PRODUCTION_DATE

Force the use of the most restrictive index FKI_VEHICLE_1:

Explain.sql looks like:
DELETE FROM plan_table WHERE statement_id = 'TEST';

EXPLAIN PLAN SET statement_id = 'TEST' FOR
select /*+ index(vehicle FKI_VEHICLE_1) */ count(*) from vehicle
where assembly_location_code = 'AP24A' and production_date = '06-apr-01';
---> Notice the FKI_VEHICLE_1 hint will be used now.

Results of explain plan:

EXECUTION_PATH

TABLE ACCESS BY INDEX ROWID VEHICLE
INDEX RANGE SCAN FKI_VEHICLE_1
SORT AGGREGATE
TABLE ACCESS BY INDEX ROWID VEHICLE
INDEX RANGE SCAN FKI_VEHICLE_1 -----> Notice that the most restrictive index is used now.

Results of query time differences:
--------> This does not use an index
select count(*) from vehicle where assembly_location_code = 'AP24A' and production_date = '06-apr-01';

SQL> set timing on
SQL> @tt
COUNT(*)
----------
787
Elapsed: 00:00:10.00 ------> Notice it is 10 seconds.


----------> This is where the query does use an index
SQL> get tt.sql
select /*+ index (vehicle FKI_VEHICLE_1) */ count(*) from vehicle where assembly_location_code = 'AP24A' and production_date = '06-apr-01';

SQL> set timing on
SQL> @tt

COUNT(*)
----------
787

Elapsed: 00:00:00.88 ------> Notice it is less than 1 second
USE THE MOST SELECTIVE INDEX that will return the fewest records.

Topics:
performance ,sql ,sql queries ,indexing ,performance optimization

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}