Btree Secondary Indexes 
Author Message
 Btree Secondary Indexes

I executed the following two sql statements.  The table test_table had a  
significant amount of data in it.  Every column was populated with data.

create index test_idx01 on test_table
(column01,
 column02,
 column03,
 column04,
 column05)
with
 structure = btree,
 key=(column01),
 fillfactor=80,
 leaffill=80,
 nonleaffill=80

create index test_idx02 on test_table
(column01,
 column02,
 column03,
 column04,
 column05)
with
 structure = btree
 key=(column01,column02,column03,column04,column05),
 fillfactor=80,
 leaffill=80,
 nonleaffill=80

I would expect test_idx01 to be smaller in size than test_idx02 because  
more of the columns within the index are included within the key for  
test_idx02.  The size of the two indexes are identical.  I have tried  
this test several times with different tables and different versions of  
Ingres running on VAX and UNIX.  The results are always the same.  Can  
anyone explain why the size of the indexes would be the same?  This seems  
to go against what CA/Ingres teaches about btree indexes.

Nick Steblay
Guidant CPI



Tue, 20 Oct 1998 03:00:00 GMT
 Btree Secondary Indexes

I am misunderstanding how indexes that have a btree structure are  
structured.  I thought they had an identical structure to btree tables.  
 I thought that only the keyed values were carried in the index and leaf  
pages.  The other non-keyed columns in the index would only be in the  
data pages.  This would make test_idx01 smaller than test_idx02.

Nick Steblay
Guidant CPI

Quote:

> I executed the following two sql statements.  The table test_table had  
a
> significant amount of data in it.  Every column was populated with  
data.

> create index test_idx01 on test_table
> (column01,
>  column02,
>  column03,
>  column04,
>  column05)
> with
>  structure = btree,
>  key=(column01),
>  fillfactor=80,
>  leaffill=80,
>  nonleaffill=80

> create index test_idx02 on test_table
> (column01,
>  column02,
>  column03,
>  column04,
>  column05)
> with
>  structure = btree
>  key=(column01,column02,column03,column04,column05),
>  fillfactor=80,
>  leaffill=80,
>  nonleaffill=80

> I would expect test_idx01 to be smaller in size than test_idx02 because  
> more of the columns within the index are included within the key for
> test_idx02.  The size of the two indexes are identical.  I have tried  
> this test several times with different tables and different versions of  
> Ingres running on VAX and UNIX.  The results are always the same.  Can  
> anyone explain why the size of the indexes would be the same?  This  
seems
> to go against what CA/Ingres teaches about btree indexes.

Roy Answered:

- Show quoted text -

Quote:
>I guess that depends on what CA is saying should happen.  Basically the
>only differences between these two indices is that one is sorted on all
>columns and the other is sorted on only the first.  Other than that they  
>contain the same data and have the same fill factor so they should be  
the
>same size.

>I assume this is a contrived example.  If you have a secondary index
>keyed on all 5 columns you would get no additional benefit from having
>another secondary index keyed on any right-incomplete subkey of the  
first
>secondary index if it contains all the same data.

>========================================================================

>BKB Engineering Ltd.,                      NAIUA Member-at-Large
>11211-76th Avenue,
>Edmonton, Alberta                          http://www.tnc.com/bkb
>T6G 0K2                                    Tel: (403)497-2409
>Canada                                     FAX: (403)436-6055
>========================================================================



Tue, 20 Oct 1998 03:00:00 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. reverse order on secondary btree indexes

2. Adding Secondary Indexes without a Primary Index

3. Primary Index Vs Secondary Indices

4. Primary Index Vs Secondary Index

5. multi-column btree index for real values

6. Btree index extension question

7. Problem with btree index on 7.1.3

8. patch to allow btree indices on BYTEA

9. btree-indexing-engine

10. Database/Structures and Indexes - Btree?

11. pctfree setting for BTREE indexes

12. Displaying index BTree


 
Powered by phpBB® Forum Software