T-SQL象数组一样处理字符串

    T-SQL对字符串的处理能力比较弱,比如要循环遍历象1,2,3,4,5这样的字符串,如果用数组的话,遍历很简单,但是T-SQL不支持数组,所以处理下来比较麻烦。下边的函数,实现了象数组一样去处理字符串。

一、按指定符号分割字符串,返回分割后的元素个数,方法很简单,就是看字符串中存在多少个分隔符号,然后再加一,就是要求的结果。

Create function Get_StrArrayLength
(
@str varchar(1024), --要分割的字符串
@split varchar(10) --分隔符号
)
returns int
as
begin
declare @location int
declare @start int
declare @length int

set @str=ltrim(rtrim(@str))
set @location=charindex(@split,@str)
set @length=1
while @location<>0
begin
set @start=@location+1
set @location=charindex(@split,@str,@start)
set @length=@length+1
end
return @length
end

调用示例:select dbo.Get_StrArrayLength('78,1,2,3',',')
返回值:4

二、按指定符号分割字符串,返回分割后指定索引的第几个元素,象数组一样方便

Create function Get_StrArrayStrOfIndex
(
@str varchar(1024), --要分割的字符串
@split varchar(10), --分隔符号
@index int --取第几个元素
)
returns varchar(1024)
as
begin
declare @location int
declare @start int
declare @next int
declare @seed int

set @str=ltrim(rtrim(@str))
set @start=1
set @next=1
set @seed=len(@split)

set @location=charindex(@split,@str)
while @location<>0 and @index>@next
begin
set @start=@location+@seed
set @location=charindex(@split,@str,@start)
set @next=@next+1
end
if @location =0 select @location =len(@str)+1
--这儿存在两种情况:1、字符串不存在分隔符号 2、字符串中存在分隔符号,跳出while循环后,@location为0,那默认为字符串后边有一个分隔符号.

return substring(@str,@start,@location-@start)
end
调用示例:select dbo.Get_StrArrayStrOfIndex('8,9,4',',',2)
返回值:9

三、结合上边两个函数,象数组一样遍历字符串中的元素

declare @str varchar(50)
set @str='1,2,3,4,5'
declare @next int
set @next=1
while @next<=dbo.Get_StrArrayLength(@str,',')
begin
print dbo.Get_StrArrayStrOfIndex(@str,',',@next)
set @next=@next+1
end

调用结果:
1
2
3
4
5

索引在数据库中的应用分析

     索引是提高数据查询最有效的方法,也是最难全面掌握的技术,因为正确的索引可能使效率提高10000倍,而无效的索引可能是浪费了数据库空间,甚至大大降低查询性能。

索引的管理成本
1、 存储索引的磁盘空间
2、 执行数据修改操作(Insert、Update、Delete)产生的索引维护
3、 在数据处理时回需额外的回退空间

实际数据修改测试:
一个表有字段A、B、C,同时进行插入10000行记录测试
在没有建索引时平均完成时间是2.9秒
在对A字段建索引后平均完成时间是6.7秒
在对A字段和B字段建索引后平均完成时间是10.3秒
在对A字段、B字段和C字段都建索引后平均完成时间是11.7秒
从以上测试结果可以明显看出索引对数据修改产生的影响

索引按存储方法分类

B*树索引
B*树索引是最常用的索引,其存储结构类似书的索引结构,有分支和叶两种类型的存储数据块,分支块相当于书的大目录,叶块相当于索引到的具体的书页。一般索引及唯一约束索引都使用B*树索引。

位图索引
位图索引储存主要用来节省空间,减少ORACLE对数据块的访问,它采用位图偏移方式来与表的行ID号对应,采用位图索引一般是重复值太多的表字段。位图索引在实际密集型OLTP(数据事务处理)中用得比较少,因为OLTP会对表进行大量的删除、修改、新建操作,ORACLE每次进行操作都会对要操作的数据块加锁,所以多人操作很容易产生数据块锁等待甚至死锁现象。在OLAP(数据分析处理)中应用位图有优势,因为OLAP中大部分是对数据库的查询操作,而且一般采用数据仓库技术,所以大量数据采用位图索引节省空间比较明显。

索引按功能分类

唯一索引:有两个作用,一个是数据约束,一个是数据索引,其中数据约束主要用来保证数据的完整性,唯一索引产生的索引记录中每一条记录都对应一个唯一的ROWID。
主关键字索引:产生的索引同唯一索引,只不过它是在数据库建立主关键字时系统自动建立的。
一般索引:不产生数据约束作用,其功能主要是对字段建立索引表,以提高数据查询速度。

索引按索引对象分类

单列索引(表单个字段的索引)
多列索引(表多个字段的索引)
函数索引(对字段进行函数运算的索引)

建立函数索引的方法:
create index 收费日期索引 on GC_DFSS(trunc(sk_rq))
create index 完全客户编号索引 on yhzl(qc_bh||kh_bh)

在对函数进行了索引后,如果当前会话要引用应设置当前会话的query_rewrite_enabled为TRUE。
alter session set query_rewrite_enabled=true

注:如果对用户函数进行索引的话,那用户函数应加上 deterministic参数,意思是函数在输入值固定的情况下返回值也固定。例:

create or replace function trunc_add(input_date date)return date deterministic
as
begin
return trunc(input_date+1);
end trunc_add;

应用索引的扫描分类

INDEX UNIQUE SCAN(按索引唯一值扫描)
select * from zl_yhjbqk where hbs_bh='5420016000'

INDEX RANGE SCAN(按索引值范围扫描)
select * from zl_yhjbqk where hbs_bh>'5420016000'
select * from zl_yhjbqk where qc_bh>'7001'

INDEX FAST FULL SCAN(按索引值快速全部扫描)
select hbs_bh from zl_yhjbqk order by hbs_bh
select count(*) from zl_yhjbqk
select qc_bh from zl_yhjbqk group by qc_bh

什么情况下应该建立索引

表的主关键字
自动建立唯一索引如zl_yhjbqk(用户基本情况)中的hbs_bh(户标识编号)

表的字段唯一约束
orACLE利用索引来保证数据的完整性,如lc_hj(流程环节)中的lc_bh+hj_sx(流程编号+环节顺序)

直接条件查询的字段
在SQL中用于条件约束的字段,如zl_yhjbqk(用户基本情况)中的qc_bh(区册编号)
select * from zl_yhjbqk where qc_bh=’7001’

查询中与其它表关联的字段
字段常常建立了外键关系,如zl_ydcf(用电成份)中的jldb_bh(计量点表编号)
select * from zl_ydcf a,zl_yhdb b where a.jldb_bh=b.jldb_bh and b.jldb_bh=’540100214511’

查询中排序的字段
排序的字段如果通过索引去访问那将大大提高排序速度
select * from zl_yhjbqk order by qc_bh(建立qc_bh索引)
select * from zl_yhjbqk where qc_bh='7001' order by cb_sx(建立qc_bh+cb_sx索引,注:只是一个索引,其中包括qc_bh和cb_sx字段)

查询中统计或分组统计的字段
select max(hbs_bh) from zl_yhjbqk
select qc_bh,count(*) from zl_yhjbqk group by qc_bh

什么情况下应不建或少建索引

表记录太少
如果一个表只有5条记录,采用索引去访问记录的话,那首先需访问索引表,再通过索引表访问数据表,一般索引表与数据表不在同一个数据块,这种情况下ORACLE至少要往返读取数据块两次。而不用索引的情况下ORACLE会将所有的数据一次读出,处理速度显然会比用索引快。
如表zl_sybm(使用部门)一般只有几条记录,除了主关键字外对任何一个字段建索引都不会产生性能优化,实际上如果对这个表进行了统计分析后ORACLE也不会用你建的索引,而是自动执行全表访问。如:
select * from zl_sybm where sydw_bh='5401'(对sydw_bh建立索引不会产生性能优化)

经常插入、删除、修改的表
对一些经常处理的业务表应在查询允许的情况下尽量减少索引,如zl_yhbm,gc_dfss,gc_dfys,gc_fpdy等业务表。

数据重复且分布平均的表字段
假如一个表有10万行记录,有一个字段A只有T和F两种值,且每个值的分布概率大约为50%,那么对这种表A字段建索引一般不会提高数据库的查询速度。

经常和主字段一块查询但主字段索引值比较多的表字段
如gc_dfss(电费实收)表经常按收费序号、户标识编号、抄表日期、电费发生年月、操作标志来具体查询某一笔收款的情况,如果将所有的字段都建在一个索引里那将会增加数据的修改、插入、删除时间,从实际上分析一笔收款如果按收费序号索引就已经将记录减少到只有几条,如果再按后面的几个字段索引查询将对性能不产生太大的影响。

如何只通过索引返回结果

一个索引一般包括单个或多个字段,如果能不访问表直接应用索引就返回结果那将大大提高数据库查询的性能。对比以下三个SQL,其中对表zl_yhjbqk的hbs_bh和qc_bh字段建立了索引:
1 select hbs_bh,qc_bh,xh_bz from zl_yhjbqk where qc_bh=’7001’

执行路径:
Select STATEMENT, GOAL = CHOOSE 11 265 5565
TABLE ACCESS BY INDEX ROWID DLYX ZL_YHJBQK 11 265 5565
INDEX RANGE SCAN DLYX 区册索引 1 265
平均执行时间(0.078秒)

2 select hbs_bh,qc_bh from zl_yhjbqk where qc_bh=’7001’

执行路径:
Select STATEMENT, GOAL = CHOOSE 11 265 371
0
TABLE ACCESS BY INDEX ROWID DLYX ZL_YHJBQK 11 265 3710
INDEX RANGE SCAN DLYX 区册索引 1 265
平均执行时间(0.078秒)

3 select qc_bh from zl_yhjbqk where qc_bh=’7001’
执行路径:
Select STATEMENT, GOAL = CHOOSE 1 265 1060
INDEX RANGE SCAN DLYX 区册索引 1 265 1060
平均执行时间(0.062秒)

从执行结果可以看出第三条SQL的效率最高。执行路径可以看出第1、2条SQL都多执行了TABLE ACCESS BY INDEX ROWID(通过ROWID访问表) 这个步骤,因为返回的结果列中包括当前使用索引(qc_bh)中未索引的列(hbs_bh,xh_bz),而第3条SQL直接通过QC_BH返回了结果,这就是通过索引直接返回结果的方法。

如何重建索引

alter index 表电量结果表主键 rebuild

如何快速新建大数据量表的索引

如果一个表的记录达到100万以上的话,要对其中一个字段建索引可能要花很长的时间,甚至导致服务器数据库死机,因为在建索引的时候ORACLE要将索引字段所有的内容取出并进行全面排序,数据量大的话可能导致服务器排序内存不足而引用磁盘交换空间进行,这将严重影响服务器数据库的工作。解决方法是增大数据库启动初始化中的排序内存参数,如果要进行大量的索引修改可以设置10M以上的排序内存(ORACLE缺省大小为64K),在索引建立完成后应将参数修改回来,因为在实际OLTP数据库应用中一般不会用到这么大的排序内存。

聚集索引和非聚集索引的区别

        聚集索引和非聚集索引的根本区别是表记录的排列顺序和与索引的排列顺序是否一致,聚集索引表记录的排列顺序与索引的排列顺序一致,优点是查询速度快,因为一旦具有第一个索引值的纪录被找到,具有连续索引值的记录也一定物理的紧跟其后。
聚集索引的缺点是对表进行修改速度较慢,这是为了保持表中的记录的物理顺序与索引的顺序一致,而把记录插入到数据页的相应位置,必须在数据页中进行数据重排,降低了执行速度。建议使用聚集索引的场合为:
  a.此列包含有限数目的不同值;
  b.查询的结果返回一个区间的值;
  c.查询的结果返回某值相同的大量结果集。

  非聚集索引指定了表中记录的逻辑顺序,但记录的物理顺序和索引的顺序不一致,聚集索引和非聚集索引都采用了B+树的结构,但非聚集索引的叶子层并不与实际的数据页相重叠,而采用叶子层包含一个指向表中的记录在数据页中的指针的方式。非聚集索引比聚集索引层次多,添加记录不会引起数据顺序的重组。建议使用非聚集索引的场合为:
  a.此列包含了大量数目不同的值;
  b.查询的结束返回的是少量的结果集;
  c.order by 子句中使用了该列。

--不用索引查询
Select * FROM IndexTestTable WHIT(INDEX(0))
Where Status='B'

--创建聚集索引
Create CLUSTERED INDEX icIndexTestTable
ON IndexTestTable(Status)
GO

--使用索引查询
Select * FROM IndexTestTable WITH(INDEX(icIndexTestTable))
Where Status='B'

更改 SQL Server 2000 验证模式

方法1:直接在企业管理器中修改

1.在服务器端使用企业管理器   
2.展开"SQL Server组",鼠标右键点击SQL Server服务器的名称,选择"属性",再选择"安全性"选项卡   
3.在"身份验证"下,选择"SQL Server和 Windows ".   
4.重新启动SQL Server服务

方法2:通过注册表修改

1.点击"开始"-"运行",输入regedit,回车进入注册表编辑器   
2.依次展开注册表项,浏览到以下注册表键:  
  [HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSSQLServerMSSQLServer]   
3.在屏幕右方找到名称"LoginMode",双击编辑双字节值   
4.将原值从1改为2,点击"确定"(1表示仅windows,2表示SQL Server和 Windows )   
5.关闭注册表编辑器   
6.重新启动SQL Server服务.