Re: SELECT statement efficiency question

On Apr 9, 12:36 am, Mladen Gogala <mgogala.SPAM_ME...._at_verizon.net> wrote:
> 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.

(SNIP)
> 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

Nice short test that you provided that is easy to reproduce.

I ran your test here - simply because I was curious to see if the times of the two tests were being distorted by 1,000,000 hard parses, but that was not the case - 1,000,000 hard parses was actually closer to 2 hard parses. Elapsed: 00:00:46.83 for the first test and Elapsed: 00:01:49.67 for the second. I then considered the possibility that the performance related to index that was created for the primary was being adversely affected by the primary key's unique requirement, so I dropped the two tables, removed the primary key specification, and created a non-unique index on the same column. My timings for those runs are Elapsed: 00:00:48.55 for the first test run and Elapsed: 00:01:48.26 for the second. The primary key requirement had a slight impact, but not as much as I expected.

Thanks again for providing the test case.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Mon Apr 09 2007 - 07:53:25 CDT