Riesch Nicolas wrote:
> I have read the documentation many times, but I am note sure I have
> understood everything.
> Can you tell me if all the following affirmations are correct ?
>
> 1) the following two ways of creating a table are exactly the same ?:
>
> create table employees
> ( company_id int key not null,
> emp_id int key not null,
> name varchar(30) not null,
> birthdate date null
> )
>
> create table employees
> ( company_id int not null,
> emp_id int not null,
> name varchar(30) not null,
> birthdate date null,
> primary key (company_id, emp_id)
> )
>
> Is is correct to say that the PRIMARY KEY can be defined
> either with the
> "key" keyword at the column level, or with the "primary key
> (col1, col2,
> ...)" syntax ?
For now it is the same, but in future, the first version will not exist
any longer. It is non-standard --> forget the first version
> 2) A table is physically stored as a B-tree on disk.
> The rows are ordered physically with the same order as the
> PRIMARY KEY, as
> defined during the creation of the table.
> The physical ordering of rows on disks is called "clustered index" or
> "native key" in other database systems.
> So, with sapdb, the clustered index is always the PRIMARY KEY ?
A B*-tree is a logical point of view. For this you are right.
But the tree is not stored from left to right on consecutive pages on
disk.
If a new page is needed, it is used whereever it may be stored on disk.
It is inserted in the B*-tree whereever needed and there it belongs
to the logical sight from left to right, i.e. from small primkey-values
to higher ones.
If we would like to store one table in correct physically order each
insert
would need to push all rows behind it to the right...
> 3) So, is it impossible to define a table with rows having a
> particular
> physical order, and a PRIMARY KEY defining another order ?
> Suppose I have this table:
> create table employees
> ( emp_id int not null,
> name varchar(30) not null,
> birthdate date null,
> primary key (emp_id)
> )
>
> I want to have the rows of this table physically ordered on
> disk by the
> field "name".
> But if I create an index on "name", the physical order of the
> rows is not
> changed. It just creates another B-tree for the index,
> pointing on the real
> rows of the table.
> So, the only way I see to achieve this is to do the following :
>
> create table employees
> ( emp_id int not null,
> name varchar(30) not null,
> birthdate date null,
> primary key (name, emp_id)
> )
> create unique index pk_employees on employees (emp_id)
>
> Then I insert those data:
>
> insert into employees
> values (20, 'smith',null)
> insert into employees
> values (10, 'watson',null)
> insert into employees
> values (30, 'holmes',null)
>
> Then I display the content of the table with "select * from
> employees".
> The result is:
>
> 30 holmes <null>
> 20 smith <null>
> 10 watson <null>
>
> Here, we see that the physical order is alphabetically by
> name, as I wanted
> it to be.
>
> And the secondary unique index on "emp_id" is acting as the
> real logical
> "primary key".
>
> Is is right to say that what is defined as "primary key" in
> sapdb is in fact
> the "clusted index" (or "physical index", or "native key") of
> the table,
> which defines the physical order of the rows on disk ?
>
> Is there no other way to force another index than the
> "primary key" to be
> the "physical index" ?
As I wrote, a physical sequence on disk can never be assured.
But what do you expect if you store according to name? Why is it so
necessary for you?
Elke
SAP Labs Berlin
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
