Re: Question on indexed nullable column

From: Bob Fazio <rfazio_at_home.com.nospam>
Date: 2000/05/12
Message-ID: <1lJS4.185982$Tn4.1469562@news1.rdc2.pa.home.com>#1/1

Below might give you some insight.

SQLWKS> create table bob(a number,b number); Statement processed.
SQLWKS> create index a1 on bob(a);
Statement processed.
SQLWKS> create index a2 on bob(b);
Statement processed.

SQLWKS> begin
     2> for x in 1..1000
     3> loop
     4>  insert into bob values(null,1);
     5> end loop;
     6> end;
     7> /

Statement processed.
SQLWKS> commit;
Statement processed.
SQLWKS> analyze table bob compute statistics; Statement processed.
SQLWKS> validate index a1;
Statement processed.
SQLWKS> select * from index_stats;

HEIGHT     BLOCKS     NAME                           PARTITION_NAME

LF_ROWS LF_BLKS LF_ROWS_LE LF_BLK_LEN BR_ROWS BR_BLKS BR_ROWS_LE BR_BLK_LEN DEL_LF_ROW DEL_LF_ROW DISTINCT_K MOST_REPEA BTREE_SPAC USED_SPACE PCT_USED ROWS_PER_K BLKS_GETS_

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

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

         1          5 A1
0          1          0       1856          0          0          0
0          0          0          0          0       1856          0
0          0          1

1 row selected.
SQLWKS> validate index a2;
Statement processed.
SQLWKS> select * from index_stats;

HEIGHT     BLOCKS     NAME                           PARTITION_NAME

LF_ROWS LF_BLKS LF_ROWS_LE LF_BLK_LEN BR_ROWS BR_BLKS BR_ROWS_LE BR_BLK_LEN DEL_LF_ROW DEL_LF_ROW DISTINCT_K MOST_REPEA BTREE_SPAC USED_SPACE PCT_USED ROWS_PER_K BLKS_GETS_

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

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

         2         10 A2
1000          8      14000       1856          7          1        112
1888          0          0          1       1000      16736      14112
85       1000      502.5

1 row selected.

I know it is a little hard to read, but as you can see there are a lot of zero's in the first index.

The leaf rows for index a1 (the nulls) is actually zero for the 1's 1000.

Basically Oracle stores very little information in the rows that have a null, the bare minimum. That will actually increase performance not decrease it.

--
Robert Fazio, Oracle DBA
rfazio_at_home.com
remove nospam from reply address
http://24.8.218.197/
<gmei_at_my-deja.com> wrote in message news:8ff72c$tcf$1_at_nnrp1.deja.com...


> Hi:

>

> In Oracle database, if I do:

>

> create table T1 (

> C1  number(9) NOT NULL,

> C2  number(9));

>

> create index INDX1 on T1(C1);

> create index INDX2 on T1(C2);

>

> Is here a performance penalty, or other problems if an indexed column

> is nullable (such as C2 here)? If yes, why?

>

> Thanks.

>

>

> Sent via Deja.com http://www.deja.com/

> Before you buy.

Received on Fri May 12 2000 - 00:00:00 CDT