start_asiq -n china-e290c040d -gu utility_db
dbisql -c "uid=dba;pwd=dba;eng=china-e290c040d;dbn=UTILITY_DB"
CREATE DATABASE 'C:\Program Files\Sybase\ASIQ-12_7\test\test.db'
message path 'C:\Program Files\Sybase\ASIQ-12_7\test\mega.iqmsg'
temporary path 'C:\Program Files\Sybase\ASIQ-12_7\test\test.iqtmp'
log on 'C:\Program Files\Sybase\ASIQ-12_7\test\test.log'
IQ PATH 'C:\Program Files\Sybase\ASIQ-12_7\test\test.iq'
collation '936ZHO'
BLANK PADDING ON
CASE IGNORE
IQ SIZE 44
IQ Page Size 131072;
--dbisql -c "uid=dba;pwd=dba;eng=china-e290c040d;dbn=UTILITY_DB" -q createdb.sql 1>createdb.out 2>createdb.err
--stop_asiq -force all 此命令win未找到
start_asiq @test.cfg test.db
dbisql -c "uid=DBA;pwd=SQL;eng=china-e290c040d;dbn=test"
dbo.sp_iqrowdensity ('column xiaoy.month.fiscal_period')
BACKUP DATABASE
TO 'C:\Program Files\Sybase\ASIQ-12_7\backup\testdb1210.bk'
WITH COMMENT 'test';
restore database 'C:\Program Files\Sybase\ASIQ-12_7\test1\test1.db'
from 'C:\Program Files\Sybase\ASIQ-12_7\backup\testdb1210.bk.1';
CONNECT USING 'uid=DBA;pwd=SQL;dbf=C:\Program Files\Sybase\ASIQ-12_7\test\test.db;links=tcpip{host=china-e290c040d;port=2639};eng=china-e290c040d_asiqdemo'
CONNECT USING 'uid=DBA;pwd=SQL;dbf=C:\Program Files\Sybase\ASIQ-12_7\test\test.db;links=tcpip{host=china-e290c040d;port=2639};eng=china-e290c040d_asiqdemo'
BACKUP DATABASE full
TO 'C:\Program Files\Sybase\ASIQ-12_7\backup\testdb1210.bk'
WITH COMMENT 'test';
CONNECT USING 'uid=dba;pwd=dba;dbn=utility_db'
stop database test on china-e290c040d_asiqdemo
--connect test
CONNECT USING 'uid=DBA;pwd=SQL;dbf=C:\Program Files\Sybase\ASIQ-12_7\test\test.db;links=tcpip{host=china-e290c040d;port=2639};eng=china-e290c040d_asiqdemo'
--backup db full
BACKUP DATABASE full
TO 'C:\Program Files\Sybase\ASIQ-12_7\backup\testdb1210.bk'
WITH COMMENT 'test';
--connect utility_db
CONNECT USING 'uid=dba;pwd=dba;dbn=utility_db'
--restore db
restore database 'C:\Program Files\Sybase\ASIQ-12_7\test\test.db'
from 'C:\Program Files\Sybase\ASIQ-12_7\backup\testdb1210.bk'
rename 'IQ_SYSTEM_MAIN' to 'C:\Program Files\Sybase\ASIQ-12_7\test1\test1.iq'
rename 'IQ_SYSTEM_MSG' to 'C:\Program Files\Sybase\ASIQ-12_7\test1\mega.iqmsg'
rename 'IQ_SYSTEM_TEMP' to 'C:\Program Files\Sybase\ASIQ-12_7\test1\test1.iqtmp';
到家目录下
dblog -t test1.log test1.db
Usage: dblog [options] database
@ expands from environment variable or file
20110901 09:42
在备份数据库之后运行此脚本,使用 RESTORE 的 RENAME 选项移动数据库或它的某个数据库空间的情况.
-- Get dbspace and IQ file names and add-- rename syntax including quotation marks
select 'rename' as 'restore ... rename' ,
dbf.dbfile_name as 'IQ file' , 'to' as 'to' ,
'''' + f.file_name + '''' as 'file_path'
from SYSFILE f, SYSDBFILE dbf
where f.store_type=2 and f.file_id=dbf.dbfile_id;
-- Send output to a file in proper format
-- without delimiters or extra quotation marks
output to "c:\restore.tst" delimited by ' ' quote '';
--This produces a restore.tst file like the following:
--rename IQ_SYSTEM_MAIN to '/dev/rdsk/c2t0d1s7'
--rename IQ_SYSTEM_TEMP to '/dev/rdsk/c2t1d1s7'
--rename IQ_SYSTEM_MSG to 'all_types.iqmsg'
监控磁盘空间使用情况 (未做测试,脚本执行环境不清,mailx此命令没有)
可以使用事件处理程序来监视磁盘空间使用情况,并在可用空间较少时向您发出通知。本节中的第一个示例对于在装载期间监控空间尤其有用。可以在启动装载之前启用事件处理程序,并在装载完成之后禁用事件处理程序。下面是示例事件处理程序代码。可以修改此代码以执行其它类型的监控。
-- This event handler sends email to the database
-- administrator whenever the IQ main DBSpace is more than
-- 95 percent full.
--This event handler runs every minute. The event handler uses
--sp_iqspaceused to sample the space usage. If the space is
--more than 95 percent full, a file that contains the date and
--time is created in the directory where iqsrv15 is
--running. The file contents are then mailed to the database
--administrator and the file is removed.
--This event can be enabled before a load and be used
--to monitor disk space usage during loading. The event can
--then be disabled after the load.
create event out_of_space
schedule
start time '1:00AM' every 1 minutes
handler
begin
declare mt unsigned bigint;
declare mu unsigned bigint;
declare tt unsigned bigint;
declare tu unsigned bigint;
call sp_iqspaceused(mt, mu, tt, tu);
if mu*100/mt > 95 then
call xp_cmdshell('date > ./temp_m_file');
call xp_cmdshell('mailx -s add_main_dbspace iqdba@iqdemo.com
< ./temp_m_file');
call xp_cmdshell('/bin/rm -rf ./temp_m_file');
end if;
if tu*100/tt > 95 then
call xp_cmdshell('date > ./temp_file');
call xp_cmdshell('mailx -s add_temp_dbspace iqdba@iqdemo.com
< ./temp_file');
call xp_cmdshell('/bin/rm -rf ./temp_file');
end if;
end
以下代码可创建一个基于计时器的事件,用于监控空间使用情况,以帮助避免执行非特权操作时空间不足状况可能导致的意外回退。会在示例iqdemo 数据库中创建 DBSpaceLogger 事件。
CREATE EVENT DBSpaceLogger
SCHEDULE START TIME '00:00:01' EVERY 300 SECONDS
HANDLER
BEGIN
DECLARE DBSpaceName VARCHAR(128);
DECLARE Usage SMALLINT;
DECLARE cursor_1 CURSOR FOR
SELECT DBSpaceName, Usage FROM sp_iqdbspace() WHERE Usage > 0 ORDER BY Usage FOR READ ONLY;
OPEN cursor_1;
idx1: LOOP
FETCH cursor_1 INTO DBSpaceName, Usage;
IF SQLCODE <> 0 THEN LEAVE idx1 END IF;
IF Usage >= 70 AND Usage < 80 THEN call dbo.sp_iqlogtoiqmsg('Information: DBSpace' + DBSpaceName + '''s usage is more than 70%');
ELSEIF Usage >= 80 AND Usage < 90 THEN call dbo.sp_iqlogtoiqmsg('Warning: DBSpace ' + DBSpaceName + '''s usage is more than 80%');
ELSEIF Usage >= 90 AND Usage < 100 THEN call dbo.sp_iqlogtoiqmsg('Critical Warning: DBSpace' + DBSpaceName + '''s usage is more than 90%');
END IF;
END LOOP;
CLOSE cursor_1;
END;
20110826 17:43
修改sybase用户 默认main数据库空间:
最佳实践是避免 在 IQ_SYSTEM_MAIN 中放置用户表或索引。管理员可以允许在 IQ_SYSTEM_MAIN 中创建用户表,特别是当这些表比较小且包含非常重要内容时。但是,推荐方法是在创建数据库之后,管理员应立即创建另一个 main 数据库空间 (用户 main 数据库空间),撤消PUBLIC 在数据库空间 IQ_SYSTEM_MAIN 中的 CREATE 特权,向选定用户或 PUBLIC 授予对新的 main 数据库空间的 CREATE 特权,并将PUBLIC.DEFAULT_DBSPACE 设置为新的用户数据库空间。例如:
CREATE DBSPACE user_main USING FILE user_main
'user_main1' SIZE 10000;
GRANT CREATE ON user_main TO PUBLIC;
REVOKE CREATE ON IQ_SYSTEM_MAIN FROM PUBLIC;
SET OPTION PUBLIC.DEFAULT_DBSPACE = 'user_main';
20110829 10:26
要显示当前数据库,请使用以下语法:select db_name()
要指定其它数据库,请使用以下语法:
select db_name([ database_id ])
连接:
eg.: dbisql -c "uid=DBA;pwd=SQL;eng=china-e290c040d;dbn=test"
一个连接字符串必须在一行中输入,并用分号分隔参数设置。
DatabaseFile (DBF)
EngineName (ENG)
DatabaseName (DBN)
通过在系统命令提示符下键入以下命令来启动 Interactive SQL:
dbisql -c "uid=userID;pwd=password" -host hostname
-port portnum -n servername dbfilename.db
eg.要连接到远程主机 fiona 上的演示数据库,请输入:
dbisql -c "uid=DBA;pwd=sql" -host fiona -port 1870
-n fiona_iqdemo $IQDIR15/demo/iqdemo.db
如果要连接到本地计算机上的数据库,则不必指定主机和端口。
20110829 16:55
创建对象或移动对象时,可以为表对象 (包括任何数据类型的列、索引、主键和外键、唯一约束、连接索引以及未分区的表)指定数据库空间。您必须在该数据库空间中具有 CREATE 特权。例如:CREATE TABLE tab1 (
col1 INT IN dsp1,
col2 VARCHAR(20),
col3 CLOB IN dsp2,
col4 DATE,
col5 BIGINT,
PRIMARY KEY (col5) IN dsp4) IN dsp3;
CREATE DATE INDEX col4_date ON tab1(col4) IN dsp5;
将得到如下数据分配:
| 数据库空间名称 |
数据 |
| dsp1 | col1 的 FP 索引 |
| dsp2 | col3 的 FP 索引 |
| dsp3 | col2、 col4 和 col5 的 FP 索引 |
| dsp4 | 主键 (col5 上的 HG) |
| dsp5 | DATE 索引 col4_date |
20110830 10:47
在Interactive SQL 中将查出的结果提取到文本文件
SELECT view_def FROM SYS.SYSVIEW;OUTPUT TO "c:\viewtext.sql"
FORMAT ASCII
下面是windows下用wordpad打开显示的部分结果:
'create view SYS.SYSIQMPXSERVER\x0a as select * from SYS.ISYSIQMPXSERVER'
'create view SYS.SYSEXTERNENVOBJECT\x0a as select * from SYS.ISYSEXTERNENVOBJECT'
'create view SYS.SYSSYNCPROFILE\x0a as select * from SYS.ISYSSYNCPROFILE'
'create view SYS.SYSIQBACKUPHISTORY\x0a as select * from SYS.ISYSIQBACKUPHISTORY'
'create view SYS.SYSIQBACKUPHISTORYDETAIL\x0a as select * from SYS.ISYSIQBACKUPHISTORYDETAIL'
'create view SYS.SYSTAB\x0a as select tab.*,\x0a tab.dbspace_id as file_id,\x0a case tab.table_type\x0a when 1 then ''BASE''\x0a when 2 then ''MAT VIEW''\x0a when 3 then ''GBL TEMP''\x0a when 4 then ''LCL TEMP''\x0a when 5 then ''TEXT''\x0a when 6 then ''TEXT GBL TEMP''\x0a when 7 then ''PARTITION''\x0a when 21 then ''VIEW''\x0a when 22 then ''JVT''\x0a else ''INVALID''\x0a end as table_type_str\x0a from SYS.ISYSTAB as tab'
'create view SYS.SYSTABCOL\x0a as select col.*\x0a from SYS.ISYSTABCOL as col'
20110830 11:33
选择索引类型
为任何给定列定义的索引集合可以对查询处理的速度产生巨大影响。主要有四种选择索引的标准:
• 唯一值数目
• 查询类型
• 磁盘空间使用情况
• 数据类型
索引中的唯一值数目
| 唯一值数目 |
建议的索引类型 |
| 低于 1,000 |
LF (如果表包含的行数小于25,000,则为 HG) |
| 1000 或更大 |
HG 和 / 或 HNG |
当 MINIMIZE_STORAGE 选项为 ON 时创建的列,或为其指定 IQ UNIQUE 65536 或更小的列将自动放置到缺省索引格式中,该索引已经过优化,可以减少存储,并提高特定查询类型的性能。
下面列出了一些具有不同唯一值数目的列的示例:
• 用于保存婚姻状况的列仅有几个唯一值 (单身、已婚或 NULL)
• 用于保存州名或省名的列所包含的唯一值数目小于 100
• 用于保存日期数据的列所包含的唯一值数目可能大于 100 但小于65536
• 用于保存帐户或社会保险号的列可能包含成千上万个唯一值
查询类型您应预先了解通常将如何查询列中的数据。例如:
• 该列是否包含在连接谓词中?
• 如果列具有大量唯一值,该列是否将在 GROUP BY 子句中使用,是否用作 COUNT DISTINCT 的参数和 / 或是否用于 SELECT DISTINCT投影?
• 该列是否将经常与其它具有相同数据类型、精度和标度的列进行比较?
通常,列中的数据类型就该列的使用方式给出了很好的指示。例如,日期列将可能在 WHERE 子句中用于范围搜索,包含价格或销售额的列可能会作为集合函数 (SUM、 AVG 等)的参数在投影中使用。
注释 Sybase IQ 仍可以解析索引类型不正确的列所涉及的查询,但不能以同等有效方式执行。
下表显示了基于查询的建议索引类型。此表首先列出对每种查询而言通常最快的索引,最慢的索引列在最后。选择索引类型时不应以这些建议作为唯一标准,还应考虑唯一值数目和磁盘空间。
| 查询用法类型 |
建议的索引类型 |
| 用于 SELECT 投影列表 |
缺省值 |
| 用于 SUM(A+B) 等计算表达式 |
缺省值 |
| 用作 AVG/SUM 参数 |
HNG、 LF、 HG 或缺省索引 |
| 用作 MIN/MAX 参数 |
LF、 HG、 HNG |
| 用作 COUNT 参数 |
缺省值 |
| 用作 COUNT DISTINCT°¢SELECT DISTINCT 或GROUP BY 参数 |
LF、 HG 或缺省索引 |
| 用作分析函数参数 |
LF 或缺省索引 |
| 如果字段不允许重复项 |
HG |
| 在即席连接条件中使用列 |
缺省索引、 HG 或 LF |
| 在连接索引中使用列 |
HG、 LF |
| 用作 WHERE 子句的 LIKE 参数 |
缺省值 |
| 用作 IN 参数 |
HG、 LF |
| 用于等于或不等于 (=、 !=) |
HG、 LF ;也可以使用 CMP |
| 用于 WHERE 子句中的范围谓词 (>、 <、 >=、<=、 BETWEEN) 用于 DATEPART 等于谓词、范围谓词和 IN 列表谓词在 CONTAINS 谓词中 |
LF、 HG 或 HNG ;也可以使用 CMP、 DATE、 TIME、DTTMDATE、 TIME、 DTTMWD、 TEXT |
为了优化查询性能,在连接谓词、子查询谓词、 GROUP BY 和DISTINCT 子句中使用的列都应具有 HG 或 LF 索引,因为 IQ 不包含除优化程序要使用的索引之外的任何其它统计信息。对高基数列使用HG,对低基数列使用 LF,但是,对于所含行数少于 100,000 的表,则应使用 HG。
上述估计通常有效;但是,其它因素可能具有更高的优先级:
• 对于范围谓词,更为重要的因素是唯一值数目。
• 对于集合函数 COUNT、 COUNT DISTINCT、 SUM、 MIN、 MAX 和AVG,为了使用除缺省索引以外的任何索引,必须能够使用单个表或连接索引解析整个查询。
• BIT 数据只能用于缺省索引;大于 255 字节的 VARBINARY 数据只能用于缺省索引类型、 TEXT 索引类型和 CMP 索引类型;大于 255 字节的 CHAR 和 VARCHAR 数据只能用于缺省索引类型、 CMP 索引类型、 TEXT 索引类型和 WD 索引类型;只有 LONG VARCHAR 数据才能用于缺省索引类型、 TEXT 索引类型和 WD 索引类型;只有 DATE数据才能用于 DATE 索引类型;只有 TIME 数据才能用于 TIME 索引类型;只有 DATETIME 和 TIMESTAMP 数据才能用于 DTTM 索引类型。
建立索引标准:磁盘空间使用情况下表相较于源数据库或平面文件中的列数据量对每种索引使用的空间大小进行了估计。
| 索引类型 |
估计空间与原始数据 |
注释 |
| Default |
小于或等于 |
如果不同值数目小于 255,此索引使用的空间会远远小于原始数 据空间 |
| High_Group |
从小于到大 2 倍 |
随着不同值数目的减少 (即,每组条目数的增加),使用的空间将根据原始数据大小按比例减小 |
| High_Non_Group |
小于或等于 |
在大多数情况下小于原始数据 |
| Low_Fast |
从小于到大 2 倍 |
与 High_Group 相同 |
| Date |
小于或等于 |
大于 High_Non_Group |
| Time |
小于或等于 |
大于 High_Non_Group |
| Datetime |
小于或等于 |
大于 High_Non_Group |
对于 LF 和 HG 索引,索引大小取决于唯一值数目。唯一值越多,索引所需的空间越大。
由于 CMP 索引始终是附加索引,因此不会节省磁盘空间。
索引中的数据类型缺省索引允许任何数据类型。有关每种数据类型支持的其它索引的列表,请参见下表。
HG、HNG、LF、WD、DATE、TIME、DTTM
组合索引类型如果将在多种查询类型中使用某一列,可能需要使用多种列索引类型。
| 现有索引 |
HG |
HNG |
LF |
CMP |
WD |
DATE、 TIME 或 DTTM |
| HG |
- | 1 | 2 | 1 | 1 | 1 |
| HNG |
1 | - | 1 |
1 |
2 |
2 |
| LF |
2 | 1 | - | 1 | 2 | 1 |
2 = 不建议使用的组合
a. CMP 索引适用于一对列。其中的每一列始终至少包含一个其它索引。
--引自《iqapgv1_zh_系统管理指南:第一卷》第6章
20110831 11:20
Sybase IQ 索引类型
快速投影 (FP) 缺省索引类型 (不理解)可以对所含不同值超过 64K 的列应用标识化。
当在 Sybase IQ 数据库中创建永久表时, IQ 将所有列值存储在缺省索引中。该缺省索引称为 FP (快速投影)索引,可优化投影并支持计算特定类型的搜索条件。
每一列都具有一个 FP 索引,每个 FP 都是一个包含 n 个固定长度条目的数组,其中 n 表示表中的行数。每个列值都按 RecordID 升序顺序进行有序存储。
利用少量非重复值或唯一值 (例如,状态、日期或月份字段)
,可以创建一个优化的 FP 格式,该格式将减少所需的磁盘页数,并极大地降低列和 I/O 投影开销所需的存储。
这些优化的 FP 索引包含以下两部分:一个查找表,其中列中的每个非重复值仅显示一次,以及逻辑值数组,其中逻辑数组的每个元素都是一个指向单元格值在查找表中的存储位置的键。
sp_iqindexmetadata 存储过程可以生成一个描述指定的索引或属于指定所有者或表的索引的报告。 sp_iqindexmetadata 输出显示给定索引是单字节索引、双字节索引、 3 字节索引还是平面样式的 FP 索引。有关详细信息,请参见 《参考:构件块、表和过程》中的“sp_iqindexmetadata 过程”。
FP(1) 索引当列值数少于 256 时,会创建单字节 FP 索引。每一行的实际键值存储在查找表中, FP 条目 (查找键)包含查找表中的索引。对于单字节 FP索引,每个 FP 条目各占 1 个字节。
如果将缺省 main 高速缓存设置为 32MB,将缺省 FP_LOOKUP_SIZE_PPM设置为 2500,使用 LOAD、 INSERT 或 UPDATE 向 char(2)、binary(2) 或小整数添加新的非重复值可将 FP(1) 转换为平面 FP。
FP(2) 索引如果列值数超过 256 但小于等于 65536,则会生成相同的查找表。每个FP 条目 (查找键)包含查找表中的索引,但各占 2 个字节而不是 1 个字节。
为避免将单字节条目转换为双字节条目所带来的开销,用户可以在创建表时将 IQ UNIQUE 指定为大于 256 且小于或等于 65536 的值。
FP(3) 索引如果离散列值个数超过 65536,则可以创建 3 字节 FP 索引。 FP(3) 索引在结构上与 FP(1) 和 FP(2) 索引类似,但主要具有下列区别:
• FP(3) 查找表的最大大小为 16777216,而不是 65536。
• FP(3) 索引缓冲区存储包含的每个查找键均为 3 个字节。
只要离散值个数不超过 16777216,3 字节索引则将值存储到某个列(列数据)中。仅当列数据大小大于 3 个字节时,用户才会对列创建 3 字节索引。因此,您不能对数据类型为 BIT、 TINYINT、 SMALLINT、CHAR(<=3)、 VARCHAR(<=3)、 BINARY(<=3) 和 VARBINARY(<=3) 的列创建 FP(3) 索引。对于 LONG VARCHAR 和 LONG VARBINARY 数据类型,Sybase IQ 同样不支持 FP(3) 索引。
要创建 FP(3) 索引,必须将 MINIMIZE_STORAGE 设置为 ON,或者必须使用介于 65537 和 16777216 之间 (包括边界值)的 IQ UNIQUE 约束值创建列。只要唯一值计数超过 65536,也可以从 FP(2) 索引创建 FP(3),但仅限表 6-7 所示的数据大小,且不包括数据类型 BIT、 TINYINT、SMALLINT、 CHAR(<=3)、 VARCHAR(<=3)、 BINARY(<=3) 和VARBINARY(<=3)。请参见 《Sybase IQ 系统管理指南》的第 9 章 “确保数据完整性”中的 “对列使用 IQ UNIQUE 约束”和 《Sybase IQ 参考手册》的第 2 章 “数据库选项”中的 “MINIMIZE_STORAGE 选项”。
平面 FP 索引如果非重复值数目超过 16777216,则不会创建任何查找表。每个 FP 条目都包含一个实际列单元值。如果 MINIMIZE_STORAGE 为 ON,则可以避免将查找 FP 条目转换为平面样式的开销。当特定字段的非重复行计数增至 16777216 以上时, FP(3)索引则自动转换为平面样式的 FP 索引。要创建平面 FP,请将 IQUNIQUE 值指定为大于 16777216 的值。
注释 当使用 DATE 数据类型创建表时,将对 DATE 字段创建一个双字节 FP 索引,该索引独立于数据库选项 MINIMIZE_STORAGE 中的设置。
要对 DATE 字段创建三字节 FP 或平面样式的 FP 索引,请在创建表时使用下列值:
• 对于 3 字节 FP 索引 — IQ UNIQUE 值介于 65537 和 16777216 之间。
• 对于平面样式的 FP 索引 — IQ UNIQUE 值大于 16777216。
Low_Fast (LF) 索引类型
此索引适用于所含唯一值非常少 (低于 1,000)的列,这些唯一值可能包括:性别、是 / 否、 True/False、相关项编号、工资级别等。 LF 是Sybase IQ 中的最快索引。当测试相等性时,只需查找一次即可快速提供结果集。要测试不等性,您可能需要多查找几次。使用此索引还可以快速计算 SUM、 AVG 和COUNT 等。随着列中唯一值数量的增多,性能将开始下降,插入和某些查询的内存和磁盘要求则开始增加。但是,当执行相等测试时,此索引的速度仍然最快,即使对于具有大量唯一值的列也是如此。
建议使用
请在下列情况下使用 LF 索引:
• 唯一值数目少于 1,000 的列。
• 唯一值数目少于 1,000 且在连接谓词中使用的列。
切勿对唯一值数目大于等于 10,000 的列使用 LF 索引。如果表具有的行数少于 25,000 行,请使用 HG 索引,因为使用该索引执行相同操作需要更少的磁盘 I/O 操作。
Low_Fast 的优缺点
下表列出了 Low_Fast 索引的优缺点。
表 6-9:LF 优点 / 缺点
优点
此索引速度非常快,对于单个表的SUM、 AVG、 COUNT、 COUNT DISTINCT、 MIN 和 MAX 运算尤其如此。
缺点
最多只能用于 10,000 个唯一值。如果列中的数据为 BIT、VARBINARY > 255 字节、 CHAR >255 字节或 VARCHAR > 255 字节,则不能使用此索引。
与其它索引比较
HNG/HG 考虑的主要因素是列中的唯一值数目。如果此数目较低,请使用 LF。
其它索引
High_Non_Group 索引类型也适用于 Low_Fast 列。
注释 如果唯一值数目较小 (小于 1,000)
,使用 LF 索引几乎总是最佳
方法。如果列显示在 WHERE 子句中,应首先考虑此索引。仅当唯一值数目较大时,才应考虑其它索引 (HG 和 HNG)
。对于具有大量唯一值的范围查询,也请考虑使用 HNG 索引。
High_Group (HG) 索引类型
High_Group 索引通常用于带有整数数据类型的连接列。由于该索引可有效处理 GROUP BY,因此其使用频率高于 High_Non_Group。建议使用
请在下列情况下使用 HG 索引:
• 将在连接谓词中使用该列
• 唯一值数目大于 1000 的列
使用多列 HG 索引可以提高带有对多个列的引用的 ORDER BY 查询的性能。这一更改对用户是透明的,但是可提高查询性能。有关示例,请参见 《性能和调优指南》的第 3 章 “优化查询和删除”中的 “增强ORDER BY 查询性能”。
注释 外键列需要其自己的单独 HG 索引。但是,如果存在连接索引,同一列不能同时具有显式创建的 HG 索引和外键约束。
High_Group 的优缺点
下表列出了 High_Group 索引的优缺点。
表 6-10:HG 优点 / 缺点
优点
可快速处理具有 GROUP BY 的查询。此索引有助于连接索引处理。它是其中一个针对在连接关系中使用的列而推荐的索引。另一个索引是 LF。
缺点
同 HNG 索引相比,此索引需要额外的磁盘空间 (可能需要三倍多的原始数据空间)。
此索引类型填充数据和删除的时间最长。要优化其删除性能,请参见 《性能和调优指南》中的 “优化删除操作”。
如果列中的数据为 BIT、 VARBINARY >255 字节、 CHAR > 255 字节或VARCHAR > 255 字节,则不能使用此索引。
不建议将此索引用于 FLOAT、 REAL 和DOUBLE 数据。
有关 HG 索引的装载性能改进的信息,请参见 《性能和调优指南》的第 4 章 “管理系统资源”中的 “改进的大型单 (事实)表装载”。
与其它索引比较
LF 唯一值数目是决定因素。如果列的唯一值数量较多,请使用High_Group。如果唯一值数量较少,请使用 Low_Fast。HNG 决定因素是列是否是连接列和 / 或是否在该列上处理 GROUP BY。
如果满足上述任一条件,请单独使用 High_Group,也可以与High_Non_Group 配合使用。在其它情况下,请使用 High_Non_Group 以节省磁盘空间。
其它索引
在某些情况下,可以在查询中使用符合 High_Group 索引条件的列,以便通过不同索引类型提高速度。在这种情况下,请为该列创建其它索引。
自动创建 High_Group 索引
当发出 CREATE INDEX 语句时, Sybase IQ 将在缺省情况下创建High_Group 索引,而不必指定索引类型。
Sybase IQ 自动为任何 UNIQUE、 FOREIGN KEY 或 PRIMARY KEY 约束创建 High_Group 索引。对于单列外键, Sybase IQ 创建一列非唯一的High_Group 索引。对于多列外键,将隐式创建非唯一的组合High_Group 索引。非唯一的 HG 索引允许重复值,并且以可选方式允许NULL。它为参照完整性提供构件块,并且可用于提高查询性能。
如果列定义允许使用空值并且不强制执行约束 (主键或唯一),则Sybase IQ 允许在用户创建的唯一多列 HG 索引的数据值中使用空值。有关详细信息,请参见 《参考:语句和选项》第 1 章 “SQL 语句”的CREATE INDEX 语句中的 “注意”一节。
由于非唯一的组合 High Group 索引可对连接和结果大小的基数提供更准确的估计,因此使用多列连接或多列 group by 子句进行查询可提高性能。此外,还可以优化推入和子查询。
High_Non_Group (HNG) 索引类型
当需要执行范围搜索时,请添加 HNG 索引。HNG 索引需要的磁盘空间比 HG 索引需要的磁盘空间大约少三倍。仅基于这一点,当不需要执行组操作时,请使用 HNG 索引,而不要使用 HG索引。相反,如果您确定 HG 索引可以更有效地处理要执行的查询,或者如果列属于连接的一部分和 / 或您希望强制执行唯一性,请使用 HG 索引。注释 对于连接四个或更多表的复杂即席查询,使用 HNG 索引代替 HG索引可能会严重降低性能。如果查询性能对应用程序中的此类查询非常重要,请同时选择 HG 和 HNG。
建议使用
请在下列情况下使用 HNG 索引:
• 唯一值数量较大 (大于 1000)
• 无需对列执行 GROUP BY
High_Non_Group 的优缺点
有关使用 High_Non_Group 索引的优缺点,请参见下表。
表 6-11:HNG 优点 / 缺点
优点
由于采用压缩算法,因此可以降低磁盘要求,而不会以牺牲性能为代价。如果列包含大量唯一值,此索引的速度最快,但下述情况例外。
缺点
不建议将此索引用于 GROUP BY 查询。如果强制执行唯一性则不可建立索引。如果列中的数据为 FLOAT、 REAL、DOUBLE、 BIT、 BINARY、VARBINARY、 CHAR > 255 字节或VARCHAR > 255 字节,则不能使用此索引。
与其它索引比较
• 同 HG 相比, HNG 需要较少的磁盘空间,但不能有效执行GROUP BY。
• 在 LF 和 HNG 之间选择时,唯一值数量是决定因素。如果唯一值数量大于 1000,请使用 HNG。
其它索引
High_Group 索引同样适用于 HNG 列。
比较 (CMP) 索引类型
比较 (CMP) 索引是两列之间的关系的索引。您可以对具有相同数据类型、精度和标度的任何两个不同列创建比较索引。 CMP 索引存储对该索引的两个列的二进制比较结果 (<、 > 或 =)。建议使用
可以在 NULL、 NOT NULL 或混合有 NULL 和 NOT NULL 的列上创建CMP 索引。 CMP 索引不能唯一。注意,数值数据类型和小数数据类型均视为相同类型。当精度和标度相同时,您可以对这些数据类型的列创建 CMP 索引。对于 CHAR、 VARCHAR、 BINARY 和 VARBINARY 列,精度表示具有相同的列宽。
例如,以下命令显示如何创建表以及在之后创建相应的比较索引:
CREATE TABLE f(c1 INT NOT NULL, c2 INT NULL, c3 CHAR(5),c4 CHAR(5))
CREATE CMP INDEX c1c2cmp ON f(c1, c2)
以下索引为非法索引,因为建立索引的列具有不同的数据类型、精度和标度:
CREATE CMP INDEX c1c3cmp ON f(c1, c3)
限制
以下限制适用于 CMP:
• 不能删除 CMP 索引。
• CMP 索引不能唯一。
• CMP 索引不能在基础连接索引中复制。
• 如果尝试更改或删除在 CMP 索引中定义的列,则会引发异常。
• 用户不能对在 CMP 索引中定义的现有列执行 ALTER TABLE MODIFY。
• CMP 索引不支持 BIT、 FLOAT、 DOUBLE 和 REAL 数据类型。
包含 (WD) 索引类型
使用包含 (WD) 索引,可以存储包含 CHAR、 VARCHAR 和 LONGVARCHAR 数据的列字符串中的单词。注释 要创建 LONG VARCHAR 列,您必须经过专门授权才能使用非结构化数据分析功能。有关非结构化数据分析选项的详细信息,请参见《Sybase IQ 中的非结构化数据分析》。
建议使用
使用 WD 索引以最快速度访问含有关键字列表的列 (例如,在书目记录或网页中)。
以下限制适用于 WD:
• 不能指定 UNIQUE 属性。
• WD 索引仅与 CONTAINS 或 LIKE 谓词一起使用。
•列名必须标识基表中的一个 CHAR、 VARCHAR 或 LONGVARCHAR 列。
• 允许的最小列宽为 3 个字节,允许的最大列宽为 LOB 列的最大宽度。 (最大长度为 4 GB 乘以数据库页大小。)
• 必须将分隔符列表包括在单引号中。 Sybase Central 创建索引向导在提示输入分隔符时不会指明这一点,如果省略分隔符,则会返回错误。
• 如果省略 DELIMITED BY 子句或指定的 separators-string 值为空字符串 (单引号),那么 Sybase IQ 将使用缺省分隔符集。缺省分隔符集包括非 7 位 ASCII 字母数字字符的所有 7 位 ASCII 字符,但连字符和单引号除外,在缺省情况下,连字符和单引号属于字词的一部分。缺省分隔符集中有 64 个分隔符。
• 如果指定了多个 DELIMITED BY 和 LIMIT 子句,则不会返回错误,但仅使用每种类型的最后一个子句。例如,下面两个语句返回相同结果:
语句 1:
CREATE WD INDEX c1wd on foo(c1)
DELIMITED BY 'f' LIMIT 40 LIMIT 99 DELIMITED BY 'g'
DELIMITED BY 'h';
语句 2:
CREATE WD INDEX c1wd on foo(c1)
DELIMITED BY 'h' LIMIT 99;
•在创建 WD 索引后,到其列的任何插入都将使用分隔符进行分析,并且在创建索引后,最大单词大小将无法更改。对于 CHAR 列, Sybase 建议您至少指定一个空格作为其中的一个分隔符或使用缺省分隔符集。Sybase IQ 会自动将 CHAR 列填充至最大列宽。如果列包含空白和字符数据,查询 WD 索引数据返回的结果可能会造成误解。例如,列 company_name 包含由分隔符分隔的两个单词,但第二个单词以空白填充:
'Concord' 'Farms
'
假设用户输入以下查询:
SELECT COUNT(*)FROM Customers WHERE CompanyName contains ('Farms')
分析程序确定字符串包含:
'Farms '
而不是:
'Farms'
并返回 1。您可以通过使用 VARCHAR 列而不是 CHAR 列避免此问题。
•sp_iqcheckdb (DBCC 一致性检查程序)的分配、检查、检验和修复模式支持 CHAR、 VARCHAR 和 LONG VARCHAR 列的 WD 索引。
WD 的优缺点
有关使用 WD 索引的优缺点,请参见下表。
| 优点 |
缺点 |
| 大量装载时可极大地提高性能。 使用此索引,某些 LIKE 谓词的执行速度将变快。 与此索引一起使用的 CONTAINS 谓词的优先级高于 LIKE 谓词。 是对关键字或部分 URL 建立索引的最佳方式。 |
潜在磁盘空间要求可能会非常大。 如果强制执行唯一性则不可建立索引。 只有列中的数据为 CHAR、VARCHAR或 LONG VARCHAR 时,才能使用此索引。 |
有关 WD 索引的装载性能改进的信息,请参见 《性能和调优指南》的
第 4 章 “管理系统资源”中的 “改进的大型单 (事实)表装载”
。
日期 (DATE)、时间 (TIME) 和日期时间 (DTTM) 索引类型
用于处理涉及日期、时间或日期时间数量的索引类型有三种:• DATE 索引用于对 DATE 数据类型的列处理涉及日期数量的特定查询。
• TIME 索引用于对 TIME 数据类型的列处理涉及时间数量的特定查询。
• DTTM 索引用于对 DATETIME 或 TIMESTAMP 数据类型的列处理涉及日期时间数量的特定查询。
建议使用
如果在含有日期和时间函数及运算的查询中使用 DATE、 TIME、DATETIME 或 TIMESTAMP 列,请在下列情况下使用 DATE、 TIME 或DTTM 索引:
• 使用 DATEPART 相等谓词 (=、 !=)、 DATEPART 范围谓词 (>、 <、>=、 <=、 !>、 !<、 BETWEEN)和 DATEPART IN 列表谓词的查询
• 使用范围谓词 (>、 <、 >=、 <=、 BETWEEN)的查询
注释 对于用于 DATE、 TIME、 DATETIME 或 TIMESTAMP 列的简单相等谓词 (无 DATEPART),使用 LF 和 HG 索引可获取最佳性能。如果 LF或 HG 索引不可用,则使用 DATE、 TIME 或 DTTM 索引获取结果。
对于相等谓词(包括连接条件)或 IN 谓词,如果在 GROUP BY 子句或WHERE/HAVING 子句中使用 DATE、 TIME、 DATETIME 或 TIMESTAMP列,该列则需要 LF 或 HG 索引,因为只有这些索引才能实现快速相等。
有关 DATE、 TIME、 DATETIME 和 TIMESTAMP 列的索引建议,另请参见第 228 页的 “其它索引”一节。
本节示例中所使用的表 tab 包含按如下方式定义的列:
CREATE TABLE tab
(col1 DATE,
col2 DATETIME,
col3 TIME);
使用 DATEPART 相等谓词、范围谓词和 IN 列表谓词查询对于使用相等谓词 (= 或 !=)的查询,如果比较一侧为 DATEPART 表达式或某些其它日期和时间函数 (例如, YEAR、 QUARTER、 DAY、MINUTE),比较的另一侧为常量表达式 (包括常量值或主变量),则使用 DATE、 TIME 或 DTTM 索引 (如果索引可用)获取结果集。
例如,在下列查询中使用 DATE、 TIME 或 DTTM 索引:
SELECT * FROM tab WHERE DATEPART(YEAR, col1) = 2002;
SELECT * FROM tab WHERE DATEPART(HOUR, col2) = 20;
SELECT * FROM tab WHERE MINUTE (col3) != 30;
SELECT * FROM tab WHERE DATEPART(MONTH, col2) = @tmon;
其中, @tmon 表示 INTEGER 主变量。用于与 DATE、TIME 和 DTTM 索引一起处理的相应 DATEPART 范围谓词和 IN 列表谓词包括:
•COMPARISON 条件 >、 <、 >=、 <=、 !>、 !<运算符一侧是日期 / 时间函数或 DATEPART 函数,其参数为表列或视图列。运算符另一侧是常量表达式,如整数或整数类型主变量。
例如,
DATEPART(WEEK, col1) !<23
DATEPART(YEAR, col1) = 2001
HOUR(col3) >= 1
•BETWEEN ... AND 条件BETWEEN 左侧是日期 / 时间函数或 DATEPART 函数,其参数为表列或视图列。 AND 两侧均为常量表达式,如整数或整数类型主变
量。例如,
DATEPART(YEAR, col1) BETWEEN host-var1 AND host-var2
•IN 条件IN 左侧是日期 / 时间函数或 DATEPART 函数,其参数为表列或视图列。 IN 列表中的值为常量表达式。例如,
DATEPART(MONTH, col1) IN (1999, 2001, 2003)
注释 DATE、 TIME 和 DTTM 索引不支持某些日期分量(Calyearofweek、 Calweekofyear、 Caldayofweek、 Dayofyear、Millisecond)。例如,
SELECT * FROM tab WHERE DATEPART(MILLISECOND, col3)= 100;
SELECT * FROM tab WHERE DATEPART(DAYOFYEAR, col1) <= 89;
在这些情况下,查询优化程序选择其它索引来获取结果。
使用范围谓词查询
在使用范围谓词的下列情况下,选择 DATE、 TIME 或 DTTM 索引处理查询:
•比较条件:
SELECT * FROM tab WHERE col1 < ‘2002/10/09’;
SELECT * FROM tab WHERE col2 >= ‘2002/01/0109:12:04.006’;
比较运算符一侧是列名,另一侧是常量表达式 (常量值或主变量)。
•Between 条件:
SELECT * FROM tab WHERE col3 BETWEEN ‘09:12:04.006’ AND ‘20:12:04.006’;
SELECT * FROM tab WHERE col2 BETWEEN tmp_datetime1 AND tmp_datetime2;
对于上述查询类型, DATE、 TIME 或 DTTM 索引通常快于 HNG 索引。
在下面三种特定情况中,使用 DATE 或 DTTM 索引可以极大地提高性能:
•谓词范围恰好是一年或多年 (实际开始日期是某一年的开始,并且实际结束日期是某一年的结束)。例如,
SELECT * FROM tab WHERE col1 BETWEEN ‘1993-01-01’ AND
‘1996-12-31’;
SELECT * FROM tab WHERE col1 >= ‘1993-01-01’ AND
col1 < ‘1997-01-01’;
SELECT * FROM tab WHERE col2 BETWEEN ‘1993-01-01
00:00:00.000000’ AND ‘1996-12-31 23:59:59.999999’;
•谓词范围恰好是一个月或同一年中的多个月 (实际开始日期是某个月的开始,并且实际结束日期是某个月的结束)。例如,
SELECT * FROM tab WHERE col1 > ‘1993-01-31’ AND
col1 <= ‘1993-06-31’;
SELECT * FROM tab WHERE col2 >= ‘1993-01-01
00:00:00.000000’ AND col1 < ‘1993-06-01
00:00:00.000000’;
•谓词范围恰好是一天。例如,
SELECT * FROM tab WHERE col2 >= ‘1993-01-31
00:00:00.000000’ AND
col2 <= ‘1993-01-31 23:59:59.999999’;
注释 在上面三种情况中,必须注意年范围、月范围和恰好一天的概念。例如,识别为年范围的 DTTM 索引包含四种情况:
col2 >
col2 <
’year1/12/31 23:59:59.999999’ and
’year2/01/01 00:00:00.000000’
col2 >= ’year1/01/01 00:00:00.000000’ and
col2 < ’year2/01/01 00:00:00.000000’
col2 > ’year1/12/31 23:59:59.999999’ and
col2 <= ’year2/12/31 23:59:59.999999’
col2 >= ’year1/01/01 00:00:00.000000’ and
col2 <= ’year2/12/31 23:59:59.999999’
以下示例中的范围不符合年范围:
col2 > ’year1/12/31 23:59:59.999999’ and
col2 <= ’year2/01/01 00:00:00.000000’
col2 > ’year1/01/01 00:00:00.000000’ and
col2 < ’year2/01/01 00:00:00.000000’
第一个范围不相符,因为除年范围之外,它还包括值 “year2/01/01
00:00:00:000000”。第二个范围缺少值 “year1/01/01 00:00:00.000000”。
类似具体信息也适用于 DTTM 和 DATE 索引的月范围和恰好一天。
如果较小的日期范围 (小于 60 个值)不适用于上面三种特定情况,使用 LF 和 HG 索引则快于 DATE 索引。
DATE/TIME/DTTM 的优缺点
有关使用 DATE、 TIME 或 DTTM 索引的优缺点,请参见下表。
表 6-13:DATE/TIME/DTTM 优点 / 缺点
优点
同其它索引类型相比,对日期、时间或日期时间数量的查询可以更快进行解析。可以创建和删除 DATE、 TIME 或DTTM 索引。
缺点
使用的磁盘空间大于 HNG 索引。实现快速相等仍需要 LF 或 HG 索引。仅当列数据的数据类型为 DATE、TIME、 DATETIME 或 TIMESTAMP 时,才能使用这些索引。
对 DATE/TIME/DTTM 索引的限制
当前,以下限制适用于 DATE、 TIME 和 DTTM 索引:
• 不能使用 UNIQUE 关键字。
• 只能对单个列创建。
• 不支持日期分量 Calyearofweek、 Calweekofyear、 Caldayofweek、Dayofyear 和 Millisecond。
与其它索引比较
DATE、TIME 和 DTTM 索引的性能与 HNG 索引相当。同 HNG 相比,在支持的情况中, DATE、 TIME 和 DTTM 索引的速度通常快于 (高达两倍)HNG。在 “建议使用”一节讨论的特殊情况中, DATE、 TIME 和 DTTM索引的性能甚至更佳。因此,通常不必在 DATE、 TIME、 DATETIME 或TIMESTAMP 数据类型的列上与 DATE、TIME 或 DTTM 索引一起使用 HNG索引。
其它索引
如果在 WHERE 子句、 ON 条件或 GROUP BY 子句中引用 DATE、TIME、 DATETIME 或 TIMESTAMP 数据类型的列,建议始终对这些列建立 DATE、 TIME 或 DTTM 索引。此外, HG 或 LF 索引也适用于 DATE、TIME、 DATETIME 或 TIMESTAMP 列,特别是对这些列计算相等谓词时。如果常常在 GROUP BY 子句中使用这些列,并且不同值数量少于 1000(即少于三年的时间),则还推荐使用 LF 索引。
TEXT 索引
注释 要使用 TEXT 索引,用户必须经过专门授权才能使用非结构化数据分析功能。请参见 《Sybase IQ 中的非结构化数据分析》。与在列字符串中使用关键字的包含 (WD) 索引不同,TEXT 索引可存储带索引的列中各个词的位置信息。与必须扫描表中所有值的查询相比,使用 TEXT 索引的查询可以更快地执行。
优化即席连接的性能
为了以最快速度处理即席连接,请对下列各项可能引用的所有列创建Low_Fast 或 High_Group 索引:• 即席连接查询的 WHERE 子句
• 集合函数之外的即席查询的 HAVING 子句条件
例如:
SELECT n_name, sum(l_extendedprice*(1-l_discount))
AS revenue
FROM customer, orders, lineitem, supplier,
nation, region
WHERE c_custkey
= o_custkey
AND o_orderkey
= l_orderkey
AND l_suppkey
= s_suppkey
AND c_nationkey
= s_nationkey
AND s_nationkey
= n_nationkey
AND n_regionkey
= r_regionkey
AND r_name
= 'ASIA'
AND o_orderdate
>= '1994-01-01'
AND o_orderdate
< '1995-01-01'
GROUP BY n_name
HAVING n_name LIKE "I%"
AND SUM(l_extendedprice*(1-l_discount)) > 0.50
ORDER BY 2 DESC
在此查询中引用的所有列 (l_extendedprice 和 l_discount 除外)都应具有 LF 或 HG 索引。
选择索引
以下快速示意图概述了如何选择索引类型。| 标识条件 |
选择的索引 |
| 指明对所有列自动创建的索引。 |
缺省索引 |
| 指明对具有 UNIQUE 或 PRIMARY KEY 约束的列自动创建的索引。 |
已强制执行 UNIQUE的 HG |
| 标识在连接谓词中使用的所有列,并根据唯一值数量选择索引类型。 |
HG或LF |
| 标识包含少量唯一值且尚未使用多个索引的列。 |
LF |
| 标识具有大量唯一值且包含在 SELECT DISTINCT 或DISTINCT COUNT 的选择列表的 GROUP BY 子句中的列。 |
HG |
| 标识可在即席查询的 WHERE 子句中使用且没有 HG或 LF 索引的列。 |
HG或LF |
| 标识具有大量唯一值且不会与 GROUP BY、 SELECT DISTINCT 或 DISTINCT COUNT 一起使用的列。 |
HNG |
| 标识可能需要经常比较的具有相同数据类型、精度和标度的列对。 |
CMP |
| 标识包含关键字或 URL 列表的列。 |
WD |
| 标识具有大量唯一值且不 会与 GROUP BY、SELECT DISTINCT 或 DISTINCT COUNT 一起使用的 DATE、TIME、 DATETIME 或 TIMESTAMP 列。 |
DATE、 TIME 或DTTM |
| 查看所有其余列,并根据唯一值数量、查询类型和磁盘空间确定其它索引。此外,对于所有列,确保您选择的索引类型允许该列的数据类型。 |
20110902 10:13 存储过程
在使用存储过程时,有两个系统存储过程非常有用:sp_iqprocedure 和sp_iqprocparm。 sp_iqprocedure 存储过程显示数据库中与系统过程和用户
定义过程有关的信息。 sp_iqprocparm 存储过程显示有关存储过程参数的
信息,包括以下列:
• proc_name
• proc_owner
• parm_name
• parm_type
• parm_mode
• domain_name
• width, scale
• default