I have an ASA 7 DB that I need to build some new indexes on
for some large tables. The company is open 24x7 so the DB
is never taken down. Can I build the new indexes while the
DB is in use?

<kam> wrote:

Unlikely - you need an exclusive access to the table being indexed.

Dmitri.

As Dmitri indicated, a connection trying to do a CREATE INDEX will be
blocked by any connection that has even done a SELECT, let alone an
INSERT/UPDATE/DELETE, without doing a COMMIT or ROLLBACK.

At some point, you're going to have to make sure all other connections
have either (a) been dropped, or (b) done a COMMIT/ROLLBACK.

One way to do (a) is to stop dbsrv7 and bring up dbeng7 which won't
allow network connections, do your thing, and then switch back.

You can also do (a) with the programmer's friend,
p_drop_other_connections; just do this search in google.groups.com for
the code AND a description of how to use it to keep control for a
while:

p_drop_other_connections group:*.sqlanywhere.*

If you are optimistic, and if you think that eventually (b) will
happen sometime in the middle of the night, you can just start a
CREATE INDEX in a dbisql session, let it get blocked, and let it sit
there until it can run. Other than possibly taking a long time once it
does start to run (and blocking other users from touching the table at
all while it runs), the CREATE INDEX statement won't damage anything,
even if it fails.

(at this point, Glenn Paulley is going to yell at me for advocating
optimism

Breck

On 23 Oct 2007 09:34:13 -0700, kam wrote:

--
Breck Carter [Team iAnywhere]
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
The book: http://www.risingroad.com/SQL_Anywhe...ers_Guide.html
breck.carter (AT) risingroad (DOT) com

You can create indexes on a running production server
but you will require exclusive access to the table for
the duration of the index creation; which may be
protracted for tables with a large number of rows.

If you can take a table out of use, a LOCK TABLE
statement ahead of the CREATE INDEX can be
used to prevent access.

I don't know if this helps but ....

<kam> wrote