Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.tools -> Re: Question on indexed nullable column
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> /
HEIGHT BLOCKS NAME PARTITION_NAMELF_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
HEIGHT BLOCKS NAME PARTITION_NAMELF_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
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...Received on Fri May 12 2000 - 00:00:00 CDT
> 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.
![]() |
![]() |