Re: SELECT statement efficiency question

From: Mladen Gogala <mgogala.SPAM_ME.NOT_at_verizon.net>
Date: Mon, 09 Apr 2007 04:36:13 GMT
Message-Id: <pan.2007.04.09.04.36.11@verizon.net>

On Sun, 08 Apr 2007 17:17:07 -0700, Charles Hooper wrote:

> Mladen, thanks for prompting the test. I was curious about this also.
>
> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.

Charles, I did my own brief test, much less refined then yours, but nevertheless confirming your results. Here is the test, I believe that it speaks for itself:

SQL> create table no_ind_tst(c1 number(10),c2 varchar2(5));

Table created.

SQL> create table ind_txt(c1 number(10) primary key,c2 varchar2(5));

Table created.

SQL> set timing on
SQL> declare
  2 ind number:=0;
  3 begin
  4 for i in 1..1000000
  5 loop
  6 insert into no_ind_tst values(i,'TEST');   7 end loop;
  8 commit;
  9 end;
 10 /

PL/SQL procedure successfully completed.

Elapsed: 00:01:06.07
SQL> declare
  2 ind number:=0;
  3 begin
  4 for i in 1..1000000
  5 loop
  6 insert into ind_txt values(i,'TEST');   7 end loop;
  8 commit;
  9 end;
 10 /

PL/SQL procedure successfully completed.

Elapsed: 00:01:46.50
SQL> SQL> select 106.5/66.07 from dual;

106.5/66.07

---

 1.61192674

Elapsed: 00:00:00.01
SQL> That means that the run time of the insert into the table with the index is 61% longer then without the index. I believe that it can make a huge difference when loading huge data sets. Typically, something like inserting a million records into the database would not be done during the peak usage time. For an on-line update from an interactive application, the difference practically doesn't exist. For a nightly batch job, the difference may be huge.

--
http://www.mladen-gogala.com

Received on Sun Apr 08 2007 - 23:36:13 CDT