`
varsoft
  • 浏览: 2501667 次
  • 性别: Icon_minigender_1
  • 来自: 上海
文章分类
社区版块
存档分类
最新评论

特殊SQL语句及优化原则

阅读更多
1.按姓氏笔画排序:
Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as
2.数据库加密:
select encrypt('原始密码')
select pwdencrypt('原始密码')
select pwdcompare('原始密码','加密后密码') = 1--相同;否则不相同 encrypt('原始密码')
select pwdencrypt('原始密码')
select pwdcompare('原始密码','加密后密码') = 1--相同;否则不相同
3.取回表中字段:
declare @list varchar(1000),@sql nvarchar(1000)
select @list=@list+','+b.name from sysobjects a,syscolumns b where a.id=b.id and a.name='表A'
set @sql='select '+right(@list,len(@list)-1)+' from 表A'
exec (@sql)
4.查看硬盘分区:
EXEC master..xp_fixeddrives
5.比较A,B表是否相等:
if (select checksum_agg(binary_checksum(*)) from A)
=
(select checksum_agg(binary_checksum(*)) from B)
print '相等'
else
print '不相等'
6.杀掉所有的事件探察器进程:
DECLARE hcforeach CURSOR GLOBAL FOR SELECT 'kill '+RTRIM(spid) FROM master.dbo.sysprocesses
WHERE program_name IN('SQL profiler',N'SQL 事件探查器')
EXEC sp_msforeach_worker '?'
7.记录搜索:
开头到N条记录
Select Top N * From 表
-------------------------------
N到M条记录(要有主索引ID)
Select Top M-N * From 表 Where ID in (Select Top M ID From 表) Order by ID Desc
----------------------------------
N到结尾记录
Select Top N * From 表 Order by ID Desc
8.如何修改数据库的名称:
sp_renamedb 'old_name', 'new_name'
9:获取当前数据库中的所有用户表
select Name from sysobjects where xtype='u' and status>=0
10:获取某一个表的所有字段
select name from syscolumns where id=object_id('表名')
11:查看与某一个表相关的视图、存储过程、函数
select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%表名%'
12:查看当前数据库中所有存储过程
select name as 存储过程名称 from sysobjects where xtype='P'
13:查询用户创建的所有数据库
select * from master..sysdatabases D where sid not in(select sid from master..syslogins where name='sa')
或者
select dbid, name AS DB_NAME from master..sysdatabases where sid <> 0x01
14:查询某一个表的字段和数据类型
select column_name,data_type from information_schema.columns
where table_name = '表名'
[n].[标题]:
Select * From TableName Order By CustomerName
[n].[标题]:
Select * From TableName Order By CustomerName
--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
Sql优化是一项复杂的工作,以下的一些基本原则是本人看书时所记录下来的,很明确且没什么废话:
1.索引的使用:
(1).当插入的数据为数据表中的记录数量的10%以上,首先需要删除该表的索引来提高数据的插入效率,当数据插入后,再建立索引。
(2).避免在索引列上使用函数或计算,在where子句中,如果索引是函数的一部分,优化器将不再使用索引而使用全表扫描。如:
低效:select * from dept where sal*12 >2500;
高效:select * from dept where sal>2500/12;
(3).避免在索引列上使用not和 “!=”,索引只能告诉什么存在于表中,而不能告诉什么不存在于表中,当数据库遇到not 和 “!=”时,就会停止使用索引而去执行全表扫描。
(4).索引列上>=代替>
低效:select * from emp where deptno > 3
高效:select * from emp where deptno >=4
两者的区别在于,前者dbms将直接跳到第一个deptno等于4的记录,而后者将首先定位到deptno等于3的记录并且向前扫描到第一个deptno大于3的。
(5).非要对一个使用函数的列启用索引,基于函数的索引是一个较好的方案。
2. 游标的使用:
当在海量的数据表中进行数据的删除、更新、插入操作时,用游标处理的效率是最慢的,但是游标又是必不可少的,所以正确使用游标十分重要:
(1). 在数据抽取的源表中使用时间戳,这样每天的维表数据维护只针对更新日期为最新时间的数据来进行,大大减少需要维护的数据记录数。
(2). 在insert和update维表时都加上一个条件来过滤维表中已经存在的记录,例如:
insert into dim_customer select * from ods_customer where ods_customer.code not exists (dim_customer.code)
ods_customer为数据源表。dim_customer为维表。
(3). 使用显式的游标,因为隐式的游标将会执行两次操作,第一次检索记录,第二次检查too many rows这个exception,而显式游标不执行第二次操作。
3.据抽取和上载时的sql优化:
(1). Where 子句中的连接顺序:
oracle采用自下而上的顺序解析where子句,根据这个原理,表之间的连接必须写在其他where条件之前,那些可以过滤掉大量记录的条件必须写在where子句的末尾。如:
低效:select * from emp e where sal>5000 and job = ‘manager’ and 25<(select count (*) from emp where mgr=e.empno);
高效:select * from emp e where 25<(select count(*) from emp where mgr=e.empno) and sal>5000 and job=’manager’;
(2). 删除全表时,用truncate 替代 delete,同时注意truncate只能在删除全表时适用,因为truncate是ddl而不是dml。
(3). 尽量多使用commit
只要有可能就在程序中对每个delete,insert,update操作尽量多使用commit,这样系统性能会因为commit所释放的资源而大大提高。
(4). 用exists替代in ,可以提高查询的效率。
(5). 用not exists 替代 not in
(6). 优化group by
提高group by语句的效率,可以将不需要的记录在group by之前过滤掉。如:
低效:select job, avg(sal) from emp group by job having job = ‘president’ or job=’manager’;
高效: select job, avg(sal) from emp havingjob=’president’ or job=’manager’ group by job;
(7). 有条件的使用union-all 替代 union:这样做排序就不必要了,效率会提高3到5倍。
(8). 分离表和索引
总是将你的表和索引建立在不同的表空间内,决不要将不属于oracle内部系统的对象存放到system表空间内。同时确保数据表空间和索引表空间置于不同的硬盘控制卡控制的硬盘上。

分享到:
评论

相关推荐

    SQL优化原则

    ### SQL优化原则及注意事项 #### 一、问题的提出与背景 在软件开发过程中,特别是在初期阶段,由于数据库中的数据量相对较少,开发者往往难以直观感受到不同SQL语句编写方式所带来的性能差异。然而,随着互联网...

    ORACLE_SQL语句优化技术分析

    ### ORACLE SQL语句优化技术分析 #### 一、选择最有效率的表名顺序(仅在基于规则的优化器中有效) 在Oracle数据库中,SQL语句的执行效率受到很多因素的影响,其中之一就是表名的顺序。Oracle的解析器会按照从右至...

    基于索引的SQL语句查询优化方法的研究.pdf

    在关系数据库系统中,SQL语句是进行数据操作的基本工具,而查询优化则是提高数据库性能的重要手段。本文将深入探讨基于索引的SQL语句查询优化方法。 首先,我们需要了解索引的概念及其工作原理。索引是一种特殊类型...

    OracleSql性能优化调整原则.doc

    本文将深入探讨文档中提及的关键知识点,包括优化器的选择、表访问方式的优化以及SQL语句的共享机制。 ### 一、选用适合的Oracle优化器 Oracle数据库提供了三种优化器选项:基于规则的优化器(RULE)、基于成本的...

    sql语句优化

    ### SQL语句优化 在数据库应用中,SQL(Structured Query Language)是用于管理关系型数据库的标准语言。随着数据量的增长及复杂查询需求的增加,如何有效地优化SQL语句成为提高数据库性能的关键因素之一。以下是对...

    mysql数据库sql优化原则(经验总结)

    在构造SQL语句的时候要养成良好的习惯。二、原则总结 原则1、仅列出需要查询的字段,这对速度不会明显的影响,主要是考虑节省应用程序服务器的内存。 原来语句: select * from admin 优化为: select admin_id,...

    Java EE中SQL语句的自动构造.pdf

    自动构造SQL语句的设计原则和实现过程中,一个关键步骤是利用Java EE中的HttpServletRequest对象来获取页面表单中用户输入的数据。通过调用request.getParameterNames()方法可以读取表单元素的名称,进而通过request...

    Oracle 性能优化之 SQL优化

    对于大型企业级应用而言,优化SQL语句不仅能够提升用户体验,还能显著降低服务器负载,从而节省硬件成本。本文将详细介绍如何针对Oracle数据库进行SQL优化,并给出具体的操作指南和最佳实践。 #### 二、SQL优化基本...

    oracle优化-SQL优化

    7. 数据表I/O优化原则包括变化表与基表的分离,大数据与常规数据的分离,以及索引数据和表数据的分离。 优化器选择: 1. Oracle提供了两种优化器,分别是基于规则的优化器(RBO)和基于代价的优化器(CBO)。在Oracle 8...

    ASP.NET面试宝典(附赠经典SQL语句查询)

    - SQL查询能力是ASP.NET开发人员必备的技能之一,面试中可能要求编写复杂的SQL查询语句,如JOIN操作、子查询、聚合函数、分组及排序等。 - ADO.NET组件如SqlConnection、SqlCommand、SqlDataReader的使用和理解。 ...

    【36】最新精选蚂蚁-MySQL语句性能优化视频教程下载 .txt

    因此,对SQL语句进行性能优化是非常必要的,它可以帮助我们更快地获取所需数据,并减少系统负载。 #### 2. 性能优化的基本原则 - **选择合适的索引**:合理利用索引可以显著提升查询速度。 - **避免全表扫描**:...

    ORACLE SQL性能优化系列

    Oracle的SQL性能优化是一个复杂但至关重要的主题,涵盖了优化器的选择、数据访问方式的改进和SQL语句的共享等多个方面。通过合理配置和管理,可以显著提升数据库的性能和响应能力。理解并应用这些原则,将有助于构建...

    SQL_优化原则和技巧.doc

    ### SQL优化原则和技巧 #### 一、表、索引相关知识及其在数据库内部相关的物理存储 ##### 1. 数据库中最小的物理存储单位:块(Block) - **定义**: 数据库最小的物理存储单位是块,对于Oracle 9i来说,默认大小为8...

    浅谈Oracle数据库SQL调整与优化.pdf

    首先,Oracle数据库的查询过程包括客户端发送SQL语句到服务器端,服务器进程接收并处理SQL语句,涉及查询缓存、语法合法性和语言含义检查、获取对象解析锁及数据访问权限核对,并确定最佳执行计划。在此过程中,绑定...

    SQL语句

    而“工具”可能指的是用来执行SQL语句的各种数据库管理工具,如MySQL Workbench、SQL Server Management Studio、Navicat等,这些工具可以帮助用户更高效地编写、测试和优化SQL查询。 在压缩包子文件的文件名称列表...

    SQL Server性能优化理论.pdf

    内部模式对象如索引的设置、是否采用分区表和分区索引来优化大容量数据存储、视图和T-SQL语句的编写等都会影响SQL Server的性能。 3. SQL Server性能分析工具 为了诊断和优化SQL Server性能,可以使用一系列的分析...

    oracle数据库索引与sql的优化

    2. **SQL语句优化示例:** - 使用`NOT EXISTS`代替`NOT IN`,因为`NOT IN`可能会导致全表扫描。 - 例如,查询部门表中没有员工的部门名称: ```sql SELECT dname, deptno FROM dept WHERE NOT EXISTS (SELECT ...

    Oracle语句优化53个规则详解

    ### Oracle语句优化53个规则详解 #### 1. 选用适合的ORACLE优化器 在Oracle数据库中,优化器的选择对于SQL语句的执行效率有着至关重要的影响。优化器负责决定SQL语句的最佳执行计划,即如何访问数据、使用哪些索引...

Global site tag (gtag.js) - Google Analytics