Tài liệu SQL Anywhere Studio 9- P9 pdf

Here is an example of an ALTER DBSPACE statement that adds 800 megabytes

to a main database file:

ALTER DBSPACE SYSTEM ADD 800 MB;

For more information about ALTER DBSPACE, see Section 10.6.1, “File Frag￾mentation,” earlier in this chapter.

Step 8: Defragment the hard drive. Disk fragmentation hurts performance, and

this is an excellent opportunity to make it go away. This step is performed after

the database is increased in size (Step 7) because some disk defragmentation

tools only work well on existing files.

Step 9: Examine the reload.sql file for logical problems, and edit the file to fix

them if necessary. You can perform this step any time after Step 2, and it is

completely optional. Sometimes, however, databases are subject to “schema

drift” over time, where errors and inconsistencies creep into the database

design. At this point in the process the entire schema is visible in the reload.sql

text file and you have an opportunity to check it and fix it.

Some problems can be easily repaired; for example, removing an unneces￾sary CHECK constraint, dropping a user id that is no longer used, or fixing an

option setting. Other problems are more difficult; for example, you can add a

column to a table, but deleting a column from a CREATE TABLE statement

may also require a change to the corresponding LOAD TABLE statement; see

Section 2.3, “LOAD TABLE,” for more information about how to skip an input

column with the special keyword "filler()".

Tip: At this point double-check the setting of database option OPTIMIZA￾TION_GOAL. Make sure the reload.sql file contains the statement SET OPTION

"PUBLIC"."OPTIMIZATION_GOAL" = 'all-rows' if that is what you want the setting

to be — and you probably do. In particular, check the value after unloading and

reloading to upgrade from an earlier version; the reload process may set this

option to the value you probably do not want: 'first-row'.

Step 10: Reload the database by running reload.sql via ISQL. This may be the

most time-consuming step of all, with Steps 2 and 8 (unload and defragment) in

close competition. Here is an example of a Windows batch file that runs ISQL

in batch mode to immediately execute the reload.sql file without any user

interaction:

"%ASANY9%\win32\dbisql.exe" -c "DSN=volume" c:\temp\reload.sql

Tip: Do not use the -ac, -an, or -ar options of dbunload.exe. These options

can be used to partially automate the unload and reload process, but they often

lead to problems and inefficiencies. In particular, they use an all-or-nothing

approach wherein a failure at any point in the process requires the whole thing

to be done over again. The step-by-step process described here is better

because it can be restarted at a point prior to the failure rather than backing up

to the beginning. This can make a big difference for a large database where the

unload and reload steps each take hours to complete and there is limited time

available to complete the task.

436 Chapter 10: Tuning

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

Step 11: Check to make sure everything’s okay. Here are some statements you

can run in ISQL to check for file, table, and index fragmentation:

SELECT DB_PROPERTY ( 'DBFileFragments' ) AS db_file_fragments;

CHECKPOINT;

SELECT * FROM p_table_fragmentation ( 'DBA' );

CALL p_index_fragmentation ( 'DBA' );

Following are the results; first of all, the entire 800MB database file is in one

single contiguous area on disk, and that’s good. Second, the application tables

all have one row segment per row, which is also good because it means there are

no row splits caused by short columns; there are a lot of extension pages but in

this case they’re required to store long column values (blobs). Finally, none of

the indexes have more than two levels, and their density measurements are all

close to 1, and those numbers indicate all is well with the indexes.

db_file_fragments

=================

1

table_name rows row_segments segments_per_row table_pages extension_pages

========== ===== ============ ================ =========== ===============

child 25000 25000 1.0 25000 25000

parent 5000 5000 1.0 5000 5000

table_name index_name rows leaf_pages levels density concerns

========== ========== ===== ========== ====== ======== =================

child child 25000 116 2 0.958616

child parent 25000 58 2 0.959599

parent parent 5000 17 2 0.944925

Step 12: At this point you can make the database available to other users; start

it with dbsrv9.exe if that’s what is done regularly. Here is an example of a Win￾dows batch file that starts the network server with support for TCP/IP

connections:

"%ASANY9%\win32\dbsrv9.exe" -x tcpip volume.db

10.7 CREATE INDEX

Indexes improve the performance of queries in many ways: They can speed up

the evaluation of predicates in FROM, WHERE, and HAVING clauses; they can

reduce the need for temporary work tables; they can eliminate sorting in

ORDER BY and GROUP BY clauses; they can speed up the calculation of the

MAX and MIN aggregate functions; and they can reduce the number of locks

required when a high isolation level is used.

Some indexes are automatically generated: A unique index is created for

each PRIMARY KEY and UNIQUE constraint, and a non-unique index is cre￾ated for each foreign key constraint. Other indexes are up to you; here is the

syntax for explicitly creating one:

<create_index> ::= CREATE

[ UNIQUE ]

[ CLUSTERED | NONCLUSTERED ]

INDEX <index_name>

ON [ <owner_name> "." ] <table_name>

<index_column_list>

[ <in_dbspace_clause> ]

Chapter 10: Tuning 437

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

<index_name> ::= <identifier> that is unique among indexes for this table

<owner_name> ::= <identifier>

<table_name> ::= <identifier>

<index_column_list> ::= "(" <index_column> { "," <index_column> } ")"

<index_column> ::= <existing_column_name> [ ASC | DESC ]

| <builtin_function_call> AS <new_column_name>

<builtin_function_call> ::= <builtin_function_name>

"(" [ <function_argument_list> ] ")"

<builtin_function_name> ::= <identifier> naming a SQL Anywhere scalar function

<function_argument_list> ::= <expression> { "," <expression> }

<expression> ::= see <expression> in Chapter 3, "Selecting"

<existing_column_name> ::= <identifier> naming an existing column in the table

<new_column_name> ::= <identifier> naming a COMPUTE column to be added to the table

<in_dbspace_clause> ::= ( IN | ON ) ( DEFAULT | <dbspace_name> )

<dbspace_name> ::= <identifier> -- SYSTEM is the DEFAULT name

Each index that you explicitly create for a single table must have a different

<index_name>. That restriction doesn’t apply to the index names that SQL

Anywhere generates for the indexes it creates automatically. These generated

index names show up when you call the built-in procedures sa_index_levels and

sa_index_density, or the p_index_fragmentation procedure described in Section

10.6.4, “Index Fragmentation.” Here is how those generated index names are

created:

The PRIMARY KEY index name will always be the same as the table

name even if an explicit CONSTRAINT name is specified.

A FOREIGN KEY index name will be the same as the role name if one is

defined, or the CONSTRAINT name if one is defined; otherwise it will be

the same as the name of the parent table in the foreign key relationship.

A UNIQUE constraint index name will be the same as the CONSTRAINT

name if one is defined, otherwise it is given a fancy name that looks like “t1

UNIQUE (c1,c2)” where t1 is the table name and “c1,c2” is the list of col￾umn names in the UNIQUE constraint itself.

Tip: Use meaningful names for all your indexes, and don’t make them the

same as the automatically generated names described above. Good names will

help you later, when you’re trying to remember why the indexes were created in

the first place, and when you’re trying to make sense of the output from proce￾dures like sa_index_levels.

Each index is defined as one or more columns in a single table. Two indexes

may overlap in terms of the columns they refer to, and they are redundant only

if they specify exactly the same set of columns, in the same order, with the same

sort specification ASC or DESC on each column; otherwise the two indexes are

different and they may both be useful in different circumstances.

The UNIQUE keyword specifies that every row in the table must have a

different set of values in the index columns. A NULL value in an index column

qualifies as being “different” from the values used in all other rows, including

other NULL values. A UNIQUE index based on columns that allow NULL val￾ues isn’t really “unique” in the way most people interpret it. For example, the

following INSERT statements do not generate any error because one of the

index columns is nullable, and multiple NULL values qualify as “unique”:

438 Chapter 10: Tuning

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.