Conference orarep::nomahs::rdb_60

>For unique indexes, the size of ranked is more than 2 times the size of sorted.

This is not quite true. A ranked index need 4 more bytes per key (to hold entry
and leaf cardinalities) plus some more per node. So an index on a short key
(say a smallint) will be significantly larger when created as 'ranked'. On the
other hand, for longer keys, the difference will be proportionally less.

/albert

If i try the formula with an integer:

 (3*(4+1+18))+32 = 101 

I obtain the error:

-RDMS-F-INDEX_S_MIN, user requested node size of 101 bytes for index needing
104

If i assume that the length of an integer is 4, there is something wrong in the
formula. But it is working with a char field.

But my principal question was : when have we to use ranked indices ?

We have some customers who are planning to change all their sorted indices to
ranked...

Louis

    >when have we to use ranked indices
    
    	you never have to use them.  there are cases where a ranked index
    is a big win and other cases where it probably doesn't matter at all
    and probably cases where it may ultimately hurt.  
    
    	If you have indexes that contain many duplicates, the BBC duplicate
    handling should be a big win (better compression of the duplicate
    chain, data ordered by DBKEY, faster insert/delete).
    
    	If you have large indexes and complex queries, the ranked index may
    provide the optimizer with more valuable information to make the
    queries run faster.
    
    	All that said, since this is a new feature in Rdb, strongly
    recommend to the customers that they test their application, under
    load, with any new feature(s) that they are considering before going
    into production.

~There is a note in SQL Reference Manual specifying that 
~	"Oracle Rdb recommends using ranked sorted indexes"

We really said that?

~What does that mean ?

Well it means that we expect that SORTED RANKED btrees to provide runtime
benefits for our customers.  The advantages include better cardinality
management (which should lead to better optimizer estimates) and much better
duplicates management.

~Have we to always use ranked instead sorted ?

No.  There is no need to change if you find the current SORTED btree
acceptable.  Certainly the old style will be around for a long time.

~Only for duplicates...
~Only for range retrievals...
~Is there a rule ?

There is no rule.  Obviously if you have duplpicates indices it might be worth
experiementing with space savings of the new SORTED RANKED btree.

Ian

.4 
>	"Oracle Rdb recommends using ranked sorted indexes"
>
>We really said that?

Yes, have a look to page 6-284 of SQL Reference Manual

.2
>If i try the formula with an integer:
>
> (3*(4+1+18))+32 = 101 

What about this formula ?  
The size of an integer field is 4 bytes, isn't it ?

Thank you again for all your advices.

Louis

>If i try the formula with an integer:
>
> (3*(4+1+18))+32 = 101 

Where is this formula coming from ? 

/albert

.6 Where is this formula coming from ? 

page 4-35 Guide to Datatabase Design and definition

       Minimum Node Size = (3*(KeySize + No. of segments + Key Overhead))+32

Louis

~If i try the formula with an integer:
~
~ (3*(4+1+18))+32 = 101 
~
~I obtain the error:
~
~-RDMS-F-INDEX_S_MIN, user requested node size of 101 bytes for index needing
~104

Can you show me the script which procedured this error?  I do not get an error
at all.

I do not believe the enforce the minimum node size correctly for SORTED RANKD
btrees.  So your help is appreciated.

~If i assume that the length of an integer is 4, there is something wrong in the
~formula. But it is working with a char field.

If it works for CHAR(4) it should work for INTEGER.

~But my principal question was : when have we to use ranked indices ?

Never if you like.  However, the improved DUPLICATE handling is a positive
improvement.

~We have some customers who are planning to change all their sorted indices to
~ranked...

I recommend they wait until ECO 1 for Rdb7 becomes available.

Ian

>If i try the formula with an integer:
>
> (3*(4+1+18))+32 = 101 
>
>I obtain the error:
>
>-RDMS-F-INDEX_S_MIN, user requested node size of 101 bytes for index needing
>104

Well I just ran a quick test and got the right results:

SQL> cr ta t1 (f1 smallint, f2 integer, f3 bigint, f4 char (32));
SQL> cr index i2_t on t1(f2)
cont> type is sorted ranked
cont> node size 101
cont> store in a2;
SQL> sh ind i2_t
Indexes on table T1:
I2_T                            with column F2
  Duplicates are allowed
  Type is Ranked
    Duplicates are Compressed
  Compression is DISABLED
  Node size  101
Store clause:           STORE in a2

Can you show us the index definition that fails ?

/albert

.8
>Can you show me the script which procedured this error?  I do not get an error
at all.

$ SQL
drop data file sample;
create data file sample
create sto area lc1 file lc1
create sto area lc2 file lc2;

CREATE TABLE t1
   (
c1 integer, c2 varchar(4)
   );

CREATE unique INDEX x1
        ON t1 (c1)
        type is sorted ranked
        node size 101
        STORE in lc1;
CREATE INDEX x11
        ON t1 (c2)
        type is sorted ranked
        node size 101
        STORE in lc2;


The creation of x1 fails:
%RDB-E-NO_META_UPDATE, metadata update failed
-RDB-E-IMP_EXC, facility-specific limit exceeded
-RDMS-F-INDEX_S_MIN, user requested node size of 101 bytes for index needing 104


The creation of x11 works:
$ sql
SQL> disc all;
SQL> attach 'f sample';
SQL> sh index x11;
Indexes on table T1:
X11                             with column C2
  Duplicates are allowed
  Type is Ranked
    Duplicates are Compressed
  Compression is DISABLED
  Node size  101
Store clause:           STORE in lc2


It seems that the length of an integer field is 5 in this case...

>I recommend they wait until ECO 1 for Rdb7 becomes available.

At this time they are at test's step.
And all our customers using Rdb7 are very happy.
Only a big one is using Rdb7 in production. He is also very happy but he doesn't
use ranked indices.

Also i've some problem to find the way to use pseudo-ranked indices.
What have i to do to use them ?

Louis

    VARCHAR(4) occupies more than 4 bytes.  There is another 2 bytes for
    the size making it a total of 6 bytes.
    
    Steve.

smallint: 4 bytes used by the formula
integer : 5 bytes        "
bigint  : 9 bytes        "
varchar(4): only 4 bytes used by the formula

Louis

and char(4): 5 bytes used by the formula.

Louis

This is strange. Looks like the calculations are wrong for unique indices ...

>cr unique index i2_t3 on t1 (f2)
> type is sorted ranked
> node size 101
>store in a2;
>%RDB-E-NO_META_UPDATE, metadata update failed
>-RDB-E-IMP_EXC, facility-specific limit exceeded
>-RDMS-F-INDEX_S_MIN, user requested node size of 101 bytes for index needing
>104

The same index definition works OK without the UNIQUE specification.

>Also i've some problem to find the way to use pseudo-ranked indices.
>What have i to do to use them ?

Nothing. Rdb will use them automatically. Actually, the dynamic optimizer will
take advantage of the additional cardinality information to more accurately
pick the right index.

I guess one case where the ranked indices make no sense is when a table has
only one index.

That said and IMHO, the really interesting improvement is the bitmap indexing. 
maybe we should provide an index structure that includes the bitmap indexing 
only - but not the ranking information (make that one optional)

/albert

~-RDMS-F-INDEX_S_MIN, user requested node size of 101 bytes for index needing
~104

OK I see the problem.  In Rdb7 code was added to default a reasonable NODE
SIZE for UNIQUE indices.  It also validates the MIN node size if you provide
an explicit node size.  Alas it is using a value which has been one-plussed... 
so it thinks the field is 5 bytes instead of 4.

I also found that the algorithm for duplicates index doesn't incorporate the
added overhead for SORTED RANKED indices.  This means it is possible to create
a node size too small for a Btree. I'll look at fixing those for some Rdb7
ECO.

Ian