|
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. |
|
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. 1
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
(19 Aug '13, 14:44)
Breck Carter
Replies hidden
2
The docs also tell the following:
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 ); ...
(20 Aug '13, 05:29)
Volker Barth
Comment Text Removed
2
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 :)
(20 Aug '13, 07:09)
Breck Carter
|
