Crickman Peter W. (crickman@netnews.jhuapl.edu) wrote:

: Column Information:
:                                                              Key  Avg Count
: Column Name                 Type       Length Nulls Defaults Seq  Per Value
: node_1                      char            2  yes      no     1    16907.4
: node_2                      char            2  yes      no     2     3152.2
: node_3                      char            2  yes      no     3     3509.1
: node_4                      char            5  yes      no     4     2113.4
: node_5                      char            2  yes      no     5      963.6
: ap_id                       integer         4  yes      no            188.0
: ap_seq_no                   integer         1  yes      no           2111.4
: cp_id                       integer         4  yes      no            135.6
: cp_seq_no                   integer         1  yes      no           1191.9
: prim_id                     integer         4  yes      no            109.1
: prim_seq_no                 integer         2  yes      no             48.1
: link_type                   char            1  yes      no         186183.0
: link_id                     integer         4  yes      no           unique
: complete                    integer         1  yes      no          12316.3
: channel                     integer         2   no      no            572.9
: mt_no                       integer         2  yes      no            546.0
: prim_offset                 float           8  yes      no             12.0
: 
: Secondary indexes:
: Index Name                  Structure  Keyed On
: spec_link_id                hash       prim_id
: spec_link_id2               hash       link_type, link_id
: spec_link_id3               btree      mt_no
: 

Whats wrong with the picture above?

You have a unique link_id and are not even placing an index on it...  You do
when you provide both link_type and link_id but when they compose a hash index,
they don't behave as you might expect.

Also, you have node1-5 with decreasing frequency you have increase index 
components try your primary index on link_id.  They should probably be
the other way around unless you are always going to use partial keys.

If you are always going to use node1-5 together then make a hash index out of 
them and modify your number of pages to a power of 2. 


:   2> select link_id from sd_s2300
:   3> SD WHERE link_type = 'S' AND
:   4>  link_id NOT IN (SELECT link_id FROM spec_link sl,
:   5> mt_lib ml WHERE sl.mt_no = ml.mt_no AND
:   6> 'SD_' + varchar (ml.sd_table) = 'SD_S2300' AND
:   7> sl.link_type = 'S')


--
#include 
+--------------------Reply to jonm@ingres.com-------------------------+
| Then when the number of dwarfs dropped from 50 to 8. The other      |
| dwarfs looked *very* suspiciously at 'Hungry'                       |
+---------------------------------------------------------------------+
| Jon Machtynger(jonm@ingres.com)                                     |
| Bvd de la Woluwe 34 Bte. 13                                         |
| Brussels. Belgium.  Ph: 02-774 49 23 Fax: 02-773 28 09              |
+---------------------------------------------------------------------+

Ingres Q & A

To William's Home Page

© William Yuan 2000

Email William