Index on NUMBER column on Oracle 10g

Question

currently we have a column with only integer values declared as NUMBER. At the same time it is our (only) index. I wonder if it would make a difference in performance if you declare the index as INTEGER? Or is Oracle smart enough to see that it is an integer? Thank you very much.

Answer 1

UPDATE: My test had a small problem. ( I tried for first table to insert 10M rows, but connect by has raised an insufficient memory exception. However probly was inserted 2-3M rows and then rolbaked. So, my first table had same number of rows, but more blocks.)

So, the assertions below are not verified.

The answer is yes.

(but how much you obtain from this, you should test with you critical operations.)

INTEGER is a subtype of NUMBER. But, surprisingly, subtipes of NUMBER are allways faster(need link here).

My test case:

create table fg_test(a number);

insert into fg_test 
select level from dual connect by level <= 1000000;
--1000000 rows inserted

create index fg_ix on fg_test(a);

select count(*) from fg_test;
-- >141 msecs

create table fg_test1(a INTEGER);

insert into fg_test1 
select level from dual connect by level <= 1000000;
--1000000 rows inserted

create index fg_ix1 on fg_test1(a);

select count(*) from fg_test1;
-- > 116 msecs

Explanation: select count(*) will do a fast full scan on the index. I ran the select count(*) muuuultiple times to see how what is best speed. In general, with INTEGER is faster. Best speed of INTEGER is better than best speed of NUMBER;

Answer 2

No, it won't.

Taking Florin's test tables, you can set up a small test harness that runs each query hundreds of times and averages the elapsed time. In my case, I ran both queries 500 times each.

Sometimes, the NUMBER version will run slightly faster (1.232 hundredths of a second vs 1.284 hundredths of a second).

SQL> ed
Wrote file afiedt.buf

  1  declare
  2    l_start_time number;
  3    l_end_time   number;
  4    l_cnt        number;
  5    l_iterations number := 500;
  6  begin
  7    l_start_time := dbms_utility.get_time();
  8    for i in 1 .. l_iterations
  9    loop
 10      select count(*)
 11        into l_cnt
 12        from fg_test;
 13    end loop;
 14    l_end_time := dbms_utility.get_time();
 15    dbms_output.put_line( 'Average elapsed (number) = ' ||
 16                             (l_end_time - l_start_time)/l_iterations ||
 17                             ' hundredths of a second.' );
 18    l_start_time := dbms_utility.get_time();
 19    for i in 1 .. l_iterations
 20    loop
 21      select count(*)
 22        into l_cnt
 23        from fg_test1;
 24    end loop;
 25    l_end_time := dbms_utility.get_time();
 26    dbms_output.put_line( 'Average elapsed (integer) = ' ||
 27                             (l_end_time - l_start_time)/l_iterations ||
 28                             ' hundredths of a second.' );
 29* end;
 30  /
Average elapsed (number) = 1.232 hundredths of a second.
Average elapsed (integer) = 1.284 hundredths of a second.

PL/SQL procedure successfully completed.

Elapsed: 00:00:12.60

If you immediately run the same code block again, however, you're just as likely to see the reverse where the integer version runs slightly faster.

SQL> /
Average elapsed (number) = 1.256 hundredths of a second.
Average elapsed (integer) = 1.22 hundredths of a second.

PL/SQL procedure successfully completed.

Elapsed: 00:00:12.38

Realistically, where you're trying to measure differences in milliseconds or fractions of milliseconds, you're well into the realm where system noise is going to come into play. Even though my machine is "idle" other than the test I'm running, there are thousands of reasons why the system might add an extra millisecond or two to an elapsed time to deal with some interrupt or to run some background thread that does something for the operating system.

This result makes sense when you consider that INTEGER is just a synonym for NUMBER(38)

SQL> desc fg_test1;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  NUMBER(38)

SQL> desc fg_test;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  NUMBER

Update:

Even using a NUMBER(6) (note that the INSERT has to be changed to load only 999,999 rows rather than 1 million), there is no change

Create the table

SQL> create table fg_test2(a number(6));

Table created.

Elapsed: 00:00:00.01

SQL> ed
Wrote file afiedt.buf

  1  insert into fg_test2
  2* select level from dual connect by level <= 1000000-1
SQL> /

999999 rows created.

Elapsed: 00:00:07.61

SQL> create index fg_ix2 on fg_test2(a);

Index created.

Elapsed: 00:00:00.01

Run the script. Note that there are no significant differences across any of the four runs and (by chance) in none of the four cases is the NUMBER(6) table the most efficient.

SQL> ed
Wrote file afiedt.buf

  1  declare
  2    l_start_time number;
  3    l_end_time   number;
  4    l_cnt        number;
  5    l_iterations number := 500;
  6  begin
  7    l_start_time := dbms_utility.get_time();
  8    for i in 1 .. l_iterations
  9    loop
 10      select count(*)
 11        into l_cnt
 12        from fg_test;
 13    end loop;
 14    l_end_time := dbms_utility.get_time();
 15    dbms_output.put_line( 'Average elapsed (number) = ' ||
 16                             (l_end_time - l_start_time)/l_iterations ||
 17                             ' hundredths of a second.' );
 18    l_start_time := dbms_utility.get_time();
 19    for i in 1 .. l_iterations
 20    loop
 21      select count(*)
 22        into l_cnt
 23        from fg_test1;
 24    end loop;
 25    l_end_time := dbms_utility.get_time();
 26    dbms_output.put_line( 'Average elapsed (integer) = ' ||
 27                             (l_end_time - l_start_time)/l_iterations ||
 28                             ' hundredths of a second.' );
 29    l_start_time := dbms_utility.get_time();
 30    for i in 1 .. l_iterations
 31    loop
 32      select count(*)
 33        into l_cnt
 34        from fg_test2;
 35    end loop;
 36    l_end_time := dbms_utility.get_time();
 37    dbms_output.put_line( 'Average elapsed (number(6)) = ' ||
 38                             (l_end_time - l_start_time)/l_iterations ||
 39                             ' hundredths of a second.' );
 40* end;
SQL> /
Average elapsed (number) = 1.236 hundredths of a second.
Average elapsed (integer) = 1.234 hundredths of a second.
Average elapsed (number(6)) = 1.306 hundredths of a second.

PL/SQL procedure successfully completed.

Elapsed: 00:00:18.89
SQL> /
Average elapsed (number) = 1.208 hundredths of a second.
Average elapsed (integer) = 1.228 hundredths of a second.
Average elapsed (number(6)) = 1.312 hundredths of a second.

PL/SQL procedure successfully completed.

Elapsed: 00:00:18.74
SQL> /
Average elapsed (number) = 1.208 hundredths of a second.
Average elapsed (integer) = 1.232 hundredths of a second.
Average elapsed (number(6)) = 1.288 hundredths of a second.

PL/SQL procedure successfully completed.

Elapsed: 00:00:18.66
SQL> /
Average elapsed (number) = 1.21 hundredths of a second.
Average elapsed (integer) = 1.22 hundredths of a second.
Average elapsed (number(6)) = 1.292 hundredths of a second.

PL/SQL procedure successfully completed.

Elapsed: 00:00:18.62