The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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

asked 12 Aug '13, 12:11

pLee's gravatar image

pLee
21691119
accept rate: 0%


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.

permanent link

answered 12 Aug '13, 12:59

Breck%20Carter's gravatar image

Breck Carter
26.6k418576824
accept rate: 21%

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.

permanent link

answered 19 Aug '13, 11:20

Martin's gravatar image

Martin
8.6k114149237
accept rate: 14%

edited 21 Aug '13, 03:50

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:

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

...
(20 Aug '13, 05:29) Volker Barth
Comment Text Removed
1

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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×17

question asked: 12 Aug '13, 12:11

question was seen: 657 times

last updated: 21 Aug '13, 03:50