Add a dbspace for storing indexes

[Former Member]

Is it good practice to have a dbspace for storing indexes only?

[Breck_Carter]

Generally, no. For most databases, separate dbspaces offer little benefit at the expense of extra administrative worry (one file is always easier than two).

If you have multiple physical disk drives AND you have control over where files are placed then putting the database and log on separate drives may offer a performance advantage (one is random disk movement, the other is sequential). Then another drive for indexes. If all the dbspaces are going to be on the same drive (or in the same RAID box) then there's no performance advantage.

If you are bumping up against space limitations then multiple dbspaces let you grow a larger database.

[MCMartin]

According to the documentation a table and its indexes have to be in the same file, so it is not possible to use an extra dbspace just for indexes.

Anyway check Brecks comment for more details, the documentation seems not to be fully exact about the above statement.

[Breck_Carter]

Not exactly.

The SQL Anywhere 16 Help topic Additional dbspaces considerations does say this: "Each table, together with its indexes, must be contained in a single database file."

However, this other Help topic CREATE INDEX statement says something different: "IN | ON clause By default, the index is placed in the same database file as its table or materialized view. You can place the index in a separate database file by specifying a dbspace name in which to put the index. This feature is useful mainly for large databases to circumvent file size limitations, or for performance improvements that might be achieved by using multiple disk devices."

Sooooo... it looks like the phrase "its indexes" must be interpreted as applying only to PRIMARY KEY, FOREIGN KEY and UNIQUE indexes since those constraints don't support an IN | ON dbspace clause.

CREATE DBSPACE dbspace_name_1 AS 'dbspace_file_1';
CREATE DBSPACE dbspace_name_2 AS 'dbspace_file_2';

CREATE TABLE t1 (
   pkey                INTEGER NOT NULL PRIMARY KEY,
   data                INTEGER NOT NULL )
   IN dbspace_name_1;

CREATE INDEX t1_data ON t1 ( data ) 
   IN dbspace_name_2;

INSERT t1 VALUES ( 1, 1 );
COMMIT;

SELECT * FROM t1;

pkey,data
1,1

[VolkerBarth]

The docs also tell the following:

SQL Anywhere uses physical and logical indexes. A physical index is the actual indexing structure as it is stored on disk. A logical index is a reference to a physical index. When you create a primary key, secondary key, foreign key, or unique constraint, the database server ensures referential integrity by creating a logical index for the constraint. Then, the database server looks to see if a physical index already exists that satisfies the constraint. If a qualifying physical index already exists, the database server points the logical index to it. If one does not exist, the database server creates a new physical index and then points the logical index to it.

Therefore, I would think that you could also create indexes for PKs, UNIQUE KEYs and FKs (which are logical indexes) pointing to physical indexes that reside on different dbspaces than the according table.

You would simply have to delay the definition of the PKs etc., and by creating according indizes on the other dbspace beforehand, such as (the untested):

CREATE TABLE t1 (
   pkey                INTEGER NOT NULL,
   data                INTEGER NOT NULL )
   IN dbspace_name_1;

CREATE UNIQUE INDEX t1_pkey ON t1 ( pkey )
   IN dbspace_name2;

ALTER TABLE t1
   ADD PRIMARY KEY ( pkey );

...

[Breck_Carter]

oooo clever... now you have to run it, and look in the system tables to see if t1_pkey is actually used for the PRIMARY KEY 🙂