The forum will be down for maintenance over the weekend of August 18-20, 2017. The forum will be shut down on the evening (EDT) of Friday, August 18. Downtime is unknown but may be up to two days. The forum will be restarted as soon as maintenance is complete.

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.

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.

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):