Typically, a clustered index will be created on the primary key of a table, and non-clustered indexes are used where needed.
Non-clustered indexes- Leaves are stored in b-tree
- Lower overhead on inserts, vs clustered
- Best for single key queries
- Last page of index can become a 'hot spot'
Clustered indexes- Records in table are sorted physically by key values
- Only one clustered index per table
- Higher overhead on inserts, if re-org on table is required
- Best for queries requesting a range of records
- Index must exist on same segment as table
Note! With "lock datapages" or "lock datarows" ... clustered indexes are sorted physically only upon creation. After that, the indexes behave like non-clustered indexes.
Adding a segment to a database
1> use dbname
2> go
1> sp_addsegment 'idx_seg1','dbname','device18'
2> go
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
Segment created.
1> use dbname
2> go
1> sp_dropsegment 'system','dbname','device18'
2> go
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
Segment reference to device dropped.
(return status = 0)
1> sp_dropsegment 'default','dbname','device18'
2> go
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
Segment reference to device dropped.
(return status = 0)
Adding a segment to a database
1> use dbname
2> go
1> sp_addsegment 'idx_seg1','dbname','device18'
2> go
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
Segment created.
1> use dbname
2> go
1> sp_dropsegment 'system','dbname','device18'
2> go
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
Segment reference to device dropped.
(return status = 0)
1> sp_dropsegment 'default','dbname','device18'
2> go
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
Segment reference to device dropped.
(return status = 0)
Dealing with a Corrupted Database
Hardware failures can result in databases that are corrupt and will not open
upon restart of the server. In some cases the database is marked suspect, and
then cannot be opened.
The best way to deal with a database in this state is to
nuke it and reload it from a backup. Here's a code snippet which will
force the drop to occur, when drop database fails.
/* note: X=the dbid of the database (from sysdatabases) */
use master
go
sp_configure "allow updates",1
go
begin tran
go
update sysdatabases set status = 320 where dbid = X
go
/* always make sure the status has been changed to 320 */
select dbid, status from sysdatabases where dbid = X
go
commit tran
go
sp_configure 'allow updates', 0
go
checkpoint
go
/* recycle the server */
dbcc dbrepair (database_name, dropdb)
go
/* now, recycle the server and rebuild the database */
Renaming a database
Renaming a database requires that it be put in single-user mode first.
-- Renaming a database
use master
go
sp_dboption 'warehouse003','single user',true
go
use warehouse003
go
checkpoint
go
use master
go
sp_renamedb 'warehouse003','warehouse009'
go
sp_dboption 'warehouse009','single user',false
go
use warehouse009
go
checkpoint
go
use master
go
sp_helpdb warehouse009
go
Dealing with a Server Failure
There are rare instances when the server crashes down so hard that it cannot
be started again. In the synopsis that follows, the crash was due to extremely
high database activity after the transaction log filled up - making it impossbile
to clear. The server was brought down, and could not be restarted. The trick here
was to bring up the server in "non-recovery" mode, and then clear the transaction
log using some tricks from the Sybase support team.
/* Note: dbname = the database name, X = the dbid */
/* In the runserver file, add the following flags: */
-m
-T3608 (recover master and nothing else)
-or-
-T3607 (no recovery)
/* Now, recycle the server */
Then, in isql:
sp_configure 'allow updates',1
go
update sysdatabases set status=-32768
where name = 'dbname'
go
select config_admin(1,102,1,0,null,null)
go
update sysdatabases set status=0 where dbid=X
go
/* recycle again, things should be OK */
Database creation
/* create a 2 gig device */
1> disk init name = 'Data01',
2> physname = '/dev/md/rdsk/d19',
3> vdevno = 6,
4> size = 1500M
5> go
1> disk init name = 'log01',
2> physname = '/dev/md/rdsk/d20',
3> size = 500M
5> go
/* create a 1 gig database, with a 50 mb transaction log */
/* for load clause allows quick creation when dump is available */
1> create database dbname
3> on Data01 = 1500M
4> log on Log01 = 500M
6> go
CREATE DATABASE: allocating 512000 pages on disk 'device18'
CREATE DATABASE: allocating 25600 pages on disk 'device8'
/* change the database owner */
use dbname
go
1> sp_changedbowner 'jmith'
2> go
/* set up automatic log truncate, for development mode */
use master
go
sp_dboption 'dbname','trunc log on chkpt',true
go
|
|
|
|
|
|
|
No comments:
Post a Comment