dcsimg
December 9, 2016
Hot Topics:

An Oracle Tune-Up: Tuning Your SQL for Maximum Performance

  • June 2, 2008
  • By David Thurmond
  • Send Email »
  • More Articles »

Searching For Answers

Suppose that you want to perform a search on the Employees table by first or last name. Although there now is a primary key on this table, it will not help in this case, because only the employee ID was included in the primary key. So, running a plan explanation on the following query:

set autotrace traceonly explain;
   select first_name, last_name, hire_date
      from employees
   where last_name like 'Doe%';
set autotrace off;

results in the following familiar and less-than-ideal results:

Execution Plan

----------------------------------------------------------

0   SELECT STATEMENT Optimizer=CHOOSE
    (Cost=18 Card=1 Bytes=19)
1 0 TABLE ACCESS (FULL) OF 'EMPLOYEES' (TABLE)
    (Cost=18 Card=1 Bytes=19)

If this search is part of an application, it is likely that a user will be staring at a screen, waiting patiently for results to return. How can you speed up this query's performance? Before, you created a primary key, but a table can only have one primary key. Besides, a primary key's main attribute is that it is unique, and it is entirely possible that a large corporation might have 15 employees named John Smith, so a primary key on first and last name would do no good. The solution is to create an index on the search fields as follows:

create index employees_idx01 on employees (last_name, first_name);

A full-table scan is like going through your neighborhood knocking on doors and asking if John Doe lives at that address. Having an index is like having a telephone directory with names in alphabetical order; it is easy to look up the name, because the names are sorted in alphabetical order, and once a name is found, the home address and phone number then can be found. In this case, creating this index yields the following new execution plan:

Execution Plan

----------------------------------------------------------

0   SELECT STATEMENT Optimizer=CHOOSE
    (Cost=2 Card=1 Bytes=19)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEES' (TABLE)
    (Cost=2 Card=1 Bytes=19)
2 1 INDEX (RANGE SCAN) OF 'EMPLOYEES_IDX01' (INDEX)
    (Cost=1 Card=1)

The index range scan on line 2 is like your search through the phone book; you must scan through the names to find the address of the row you want. Then, once the address is found, the data is retrieved by index row ID, which is the most efficient way to locate the data in the table.

Suppose you have a table of departments as shown below:

create table departments (
   department_id varchar2(10) not null,
   department_name varchar2(30)
);

alter table departments
add constraint department_pk primary key (department_id);

alter table employees
add constraint employees_fk1
foreign key (department_id)
references departments (department_id);

Now, suppose you want to find all employees in the Accounting department, as shown in the following query:

select e.employee_id, e.first_name, e.last_name
   from employees e, departments d
   where d.department_name = 'Accounting' and
   d.department_id = e.department_id;

This query has the following execution plan:

Execution Plan

----------------------------------------------------------

0   SELECT STATEMENT Optimizer=CHOOSE
    (Cost=22 Card=1 Bytes=49)
1 0 NESTED LOOPS
2 1 NESTED LOOPS
    (Cost=22 Card=1 Bytes=49)
3 2 TABLE ACCESS (FULL) OF 'EMPLOYEES' (TABLE)
    (Cost=18 Card=4 Bytes=100)

4 2 INDEX (UNIQUE SCAN) OF 'DEPARTMENT_PK' (INDEX (UNIQUE) )
    (Cost=0 Card=1)

5 1 TABLE ACCESS (BY INDEX ROWID) OF 'DEPARTMENTS' (TABLE)
    (Cost=1 Card=1 Bytes=24)

Obviously, the poorest-performing portion of this query is the full-table scan on the Employees table. Creating an index on this table to optimize a search by department_id as follows:

create index employees_idx02
   on employees (department_id, employee_id);

yields the following execution plan:

Execution Plan

----------------------------------------------------------

0   SELECT STATEMENT Optimizer=CHOOSE
    (Cost=7 Card=1 Bytes=49)
1 0 NESTED LOOPS
2 1 NESTED LOOPS
    (Cost=7 Card=1 Bytes=49)
3 2 VIEW OF 'index$_join$_001' (VIEW)
    (Cost=3 Card=4 Bytes=100)
4 3 HASH JOIN
5 4 INDEX (FAST FULL SCAN) OF 'EMPLOYEES_IDX01' (INDEX)
    (Cost=1 Card=4 Bytes=100)
6 4 INDEX (FAST FULL SCAN) OF 'EMPLOYEES_IDX02' (INDEX)
    (Cost=1 Card=4 Bytes=100)

7 2 INDEX (UNIQUE SCAN) OF 'DEPARTMENT_PK' (INDEX (UNIQUE) )
    (Cost=0 Card=1)

8 1 TABLE ACCESS (BY INDEX ROWID) OF 'DEPARTMENTS' (TABLE)
    (Cost=1 Card=1 Bytes=24)




Page 2 of 5



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Sitemap | Contact Us

Thanks for your registration, follow us on our social networks to keep up-to-date
Rocket Fuel