在SQL中,可以使用||来把多个列的结果进行字符串合并,然后再用DISTINCT来进行唯一性限制。
Sample: SELECT DISTINCT(KEY_1||KEY_2) FROM TABLE_A;
这样可以查出TABLE_A中所有唯一的KEY_1和KEY_2的组合。
INSERT INTO sm_users
(user_id, user_name, created_by, creation_date)
VALUES (1, 'Tang', -1, SYSDATE);
建议用如下方式操作:
DECLARE
v_user_id sm_users.user_id%TYPE;
v_user_name sm_users_user_name%TYPE;
v_created_by sm_users.created_by%TYPE;
v_creation_date sm_users.creation_date%TYPE;
BEGIN
INSERT INTO sm_users
(user_id, user_name, created_by, creation_date)
VALUES (v_user_id, v_user_name, v_created_by, v_creation_date);
1.3 Operator 的使用规范
比较容易写及清晰易懂
但效能是比较低的
ORACLE试图将其转换成多个表的连接,如果转换不成功则先执行IN里面的子
查询
,再
查询
外层的表记录,如果转换成功则直接采用多个表的连接方式
查询
。由此可见用IN的
SQL
至少多了一个转换的过程。一般的
SQL
都可以转换成功,但对于含有分组统计等方面的
SQL
就不能转换了。
NOT IN
此操作是强列推荐不使用的,因为不能应用表的索引。
推荐方案:用NOT EXISTS 或(Outer-Join+判断为空)方案代替
SELECT deptno
FROM dept
WHERE deptno NOT IN(SELECT deptno
FROM emp)
建议写成:
SELECT deptno
FROM dept, emp
WHERE dept.deptno = emp.deptno(+) AND emp.deptno IS NULL
永远不会用到索引的
推荐方案:用其它相同功能的操作运算代替,如:
a<>0 改为 a>0 or a<0
a<>’’ 改为 a>’’
IS NULL 或IS NOT NULL
一般是不会应用索引的,因为B-tree索引是不索引空值的。
推荐方案:用其它相同功能的操作运算代替,如:
a is not null 改为 a>0 或a>’’
不允许字段为空,而用一个default代替空值,如业扩申请中状态区位不允许为空, default为申请。
有索引就会采用索引查找
但有的情况下可以对它进行优化
如一个表有100万记录,一个数值型字段A,30万记录的A=0,30万记录的A=1,39万记录的A=2,1万记录的A=3。那么执行A>2与A>=3的效果就有很大的区别了,因为A>2时ORACLE会先找出为2的记录索引再进行比较,而A>=3时ORACLE则直接找到=3的记录索引。
LIKE
LIKE可以应用通配符
查询
,里面的通配符组合可能达到几乎是任意的
查询
,但是如果用得不好则会产生性能上的问题,如LIKE ‘%5400%’ 这种
查询
不会引用索引,而LIKE ‘X5400%’则会引用范围索引。性能肯定大大提高。
UNION
SQL
在运行时先取出数个
查询
的结果,再用排序空间进行排序删除重复的记录,最后返回结果集,如果表数据量大的话可能会导致用磁盘进行排序。
实际大部分应用中是不会产生重复的记录,推荐采用UNION ALL操作符替代UNION,因为UNION ALL操作只是简单的将两个结果
合并
后就返回。
Exists
示例:当有 A、B 两个结果集,当结果集 B 很大时,A 较小时,适用 exists,如:
SELECT *
FROM a
WHERE EXISTS(SELECT 1
FROM b
WHERE a.COLUMN = b.COLUMN);
当结果集 A 很大时,B 很小时,适用 in,如:
SELECT *
FROM a
WHERE a.COLUMN IN(SELECT b.COLUMN
FROM b)
1.4
SQL
书写的影响
同一功能同一性能不同写法
SQL
的影响
Select * from zl_yhjbqk
Select * from dlyx.zl_yhjbqk(带表所有者的前缀)
Select * from DLYX.ZL_YHJBQK(大写表名)
Select * from DLYX.ZL_YHJBQK(中间多了空格)
以上四个
SQL
在ORACLE分析整理之后产生的结果及执行的时间是一样的,但是从ORACLE共享内存SGA的原理,可以得出ORACLE对每个
SQL
都会对其进行一次分析,并且占用共享内存,如果将
SQL
的字符串及格式写得完全相同则ORACLE只会分析一次,共享内存也只会留下一次的分析结果,这不仅可以减少分析
SQL
的时间,而且可以减少共享内存重复的信息,ORACLE也可以准确统计
SQL
的执行频率。
WHERE后面的条件顺序影响
Select * from zl_yhjbqk where dy_dj = '1KV以下' and xh_bz=1
Select * from zl_yhjbqk where xh_bz=1 and dy_dj = '1KV以下'
以上两个
SQL
中dy_dj(电压等级)及xh_bz(销户标志)两个字段都没进行索引,所以执行的时候都是全表扫描,第一条
SQL
的dy_dj = '1KV以下'条件在记录集内比率为99%,而xh_bz=1的比率只为0.5%,在进行第一条
SQL
的时候99%条记录都进行dy_dj及xh_bz的比较,而在进行第二条
SQL
的时候0.5%条记录都进行dy_dj及xh_bz的比较,以此可以得出第二条
SQL
的CPU占用率明显比第一条低。
查询
表顺序的影响
在FROM后面的表中的列表顺序会对
SQL
执行性能影响,在没有索引及ORACLE没有对表进行统计分析的情况下ORACLE会按表出现的顺序进行链接,由此因为表的顺序不对会产生十分耗服务器资源的数据交叉。(注:如果对表进行了统计分析,ORACLE会自动先进小表的链接,再进行大表的链接)
对条件字段的一些优化
采用函数处理的字段不能利用索引,如:
substr(hbs_bh,1,4)=’5400’,优化处理:hbs_bh like ‘5400%’
trunc(sk_rq)=trunc(sysdate), 优化处理: sk_rq>=trunc(sysdate) and sk_rq<trunc(sysdate+1)
进行了显式或隐式的运算的字段不能进行索引,如:
ss_df+20>50,优化处理:ss_df>30
‘X’||hbs_bh>’X5400021452’,优化处理:hbs_bh>’5400021542’
sk_rq+5=sysdate,优化处理:sk_rq=sysdate-5
hbs_bh=5401002554,优化处理:hbs_bh=’ 5401002554’
注:此条件对hbs_bh 进行隐式的to_number转换,因为hbs_bh字段是字符型
条件内包括了多个本表的字段运算时不能进行索引,如:
ys_df>cx_df,无法进行优化
qc_bh||kh_bh=’5400250000’,优化处理:qc_bh=’5400’ and kh_bh=’250000’
HINT
是在ORACLE产生的
SQL
分析执行路径不满意的情况下要用到的。它可以对
SQL
进行以下方面的提示
目标方面的提示:
COST(按成本优化)
RULE(按规则优化)
CHOOSE(缺省)(ORACLE自动选择成本或规则进行优化)
ALL_ROWS(所有的行尽快返回)
FIRST_ROWS(第一行资料尽快返回)
执行方法的提示:
USE_NL(使用NESTED LOOPS方式联合)
USE_MERGE(使用MERGE JOIN方式联合)
USE_HASH(使用HASH JOIN方式联合)
索引提示:
INDEX(
TABLE
INDEX)(使用提示的表索引进行
查询
)
其它高级提示(如并行处理等等)
1.5 索引的规则:
建立索引常用的原则如下:
1. 表的主键、外键必须有索引
2. 数据量超过 1000 行的表应该有索引
3. 经常与其它表进行连接的表,在边接字段上应建立索引
4. 经常出现在 where 子句中的字段且过滤性极强的,特别是大表的字段,应该建立索引
5. 索引字段,尽量避免值为 null
6. 复合索引的建立需要仔细分析;尽量考虑用单字段索引代替:
正确选择复合索引中的第一个字段,一般是选择性较好的且在 where 子句中常的字段上。
复合索引的几个字段是否经常同时以and方式出现在where子句中?单字段
查询
是否极少甚至没有?如果是,则可以建立复合索引;否则考虑单字段索引。
如果复合索引中包含的字段经常单独出现在 where 子句中,则分解为多个单字段索引。
如果复合索引所包含的字段超过 3 个,那么仔细考虑其必要性,考虑减少复合的字段。
如果既有单字段索引,又有这几个字段上的复合索引,一般可以删除复合索引;
7. 频繁 DDL 的表,不要建立太多的索引
8. 删除无用的索引,避免对执行计划造成负面影响
9. 让
SQL
语句用上合理的索引,合理让
SQL
语句使用索引的原则如下:
首先,看是否用上了索引,对于该使用索引而没有用上索引的
SQL
语句,应该想办法用上索引。
其次,看是否用上正确的索引了,特别复杂的
SQL
语句,当其中 where 子句包含多个带有索引的字段时,更应该注意索引的选择是否合理。错误的索引不仅不会带来性能的提高,相反往往导致性能的降低。
针对如何用上合理的索引,以 Oracle 数据中的例子进行说明:
任何对列的操作都可能导致全表扫描,这里所谓的操作包括数据库函数、计算表达式等等,
查询
时要尽可能将操作移至等式的右边,甚至去掉函数。
避免不必要的类型转换,要了解“隐藏”的类型转换。
增加
查询
的范围,限制全范围的搜索。
索引选择性低,但资料分布差异很大时,仍然可以利用索引提高效率。
Oracle 优化器无法用上合理索引的情况下,利用 hint 强制指定索引。
使用复合索引且第一个索引字段没有出现在 where 中时,建议使用 hint 强制。
1.6 索引使用优化
建立Plan_
Table
CREATE
TABLE
PLAN_
TABLE
STATEMENT_ID VARCHAR2(30),
TIMESTAMP DATE,
REMARKS VARCHAR2(80),
OPERATION VARCHAR2(30),
OPTIONS VARCHAR2(30),
OBJECT_NODE VARCHAR2(128),
OBJECT_OWNER VARCHAR2(30),
OBJECT_NAME VARCHAR2(30),
OBJECT_INSTANCE NUMBER(38),
OBJECT_TYPE VARCHAR2(30),
OPTIMIZER VARCHAR2(255),
SEARCH_COLUMNS NUMBER(38),
ID NUMBER(38),
PARENT_ID NUMBER(38),
POSITION NUMBER(38),
OTHER LONG
Syntax
explain plan set statement_id = user_define for select ...
将结果显示
SELECT LPAD(' ', 2 *(LEVEL - 1)) || operation op, options, object_name,
POSITION
FROM plan_
table
START WITH ID = 0 AND STATEMENT_ID = user_define
CONNECT BY PRIOR ID = parent_id AND STATEMENT_ID = user_define
如要测试下面
SQL
:
SELECT c.short, a.cday, a.card_no, a.qty
FROM sales.stockiohis a, sales.product_info b, sales.vendor c
WHERE a.card_no = b.card_no
AND b.vendorid = c.vendorid
AND a.produce_no = '2007090001'
AND a.CATEGORY = '10'
AND a.iotype = '1'
新增文件:例 d:\mydoc\plan.
sql
'0001'为user_define为使用者自定义编号
EXPLAIN PLAN SET STATEMENT_ID = '0001' FOR
SELECT 'X'
FROM sales.stockiohis a ,sales.product_info b ,sales.vendor c
WHERE a.card_no = b.card_no AND b.vendorid = c.vendorid
AND a.produce_no = '2007090001' AND a.CATEGORY = '10' AND a.iotype = '1'
SET arraysize 1
SET line 100
COLUMN op format a40
COLUMN object_name format a20
COLUMN options format a20
SELECT LPAD(' ', 2 *(LEVEL - 1)) || operation op, options, object_name,
POSITION
FROM plan_
table
START WITH ID = 0 AND STATEMENT_ID = '0001'
CONNECT BY PRIOR ID = parent_id AND STATEMENT_ID = '0001'
DELETE FROM plan_
table
WHERE STATEMENT_ID = '0001'
COMMIT
1.7 避免不必要的排序
说明:对
查询
结果进行排序会大大的降低系统的性能,group与union都会对数据作排序,要耗费较多的内存,视状况用union all既可,不然有时数据太大又要进行union的排序,会导致Oracle数据库SORT_AREA_SIZE不足发生系统错误。
1.8 对于数字型的Primary Key,建议用序列 sequence 产生。
说明:除非是单据的单号,要求必须是
唯一
,并且依据流水号不可以跳号,不然在大量交易的表格中,不在乎跳耗时,要取得
唯一
的Primary Key 建议使用Oracle Sequence这样速度会较快,而且不会有锁定(Lock)的问题。
前景提要(文末提供源码下载)昨天我们学习了针对单列的数据进行获取
唯一
值的方法,今天我们提升下难度,来尝试下获取已
多列
为参照物,获取
唯一
值的方法,昨天有很多小伙伴说还可以用字典的方法更加的简单,其实,针对这个问题,我还是那句话,方法无绝对,多学点终究是好的,针对获取
唯一
值的方法,我后续几天会展开至少3种方法,大家可以选择自己喜欢的方法来玩。场景说明很明显今天的问题,我们无法用常规的去重的方式来实现了...
SELECT FNAME+' '+MINIT+' '+LNAME AS 'NAME' FROM
table
_name
--SELECT 列名1+' '+列名2+' '+列名3 AS '预期列名' FROM 表名(中间空格为美观,可去)
SQL
Server 按某一字段分组 取 最大 (小)值所在行的数据
-- 按某一字段分组 取 最大 (小)值所在行的数据
-- (爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 2007-10-23于浙江杭州)
数据如下:
name val memo
a 2 a2(a的第二个值)
a 1 a1--a的第一个值
a 3 a3...
--=============================================================================--標題:
合并
列值--整理:takako_mu--时间:2009-10-19--地点:昆山--=========================...
在
SQL
数据库中,UNIQUE约束是一种用于确保数据
唯一性
的重要工具。本文将深入探讨UNIQUE约束的概念、应用场景以及使用方法,以帮助读者更好地理解和利用UNIQUE约束来保证数据的一致性和准确性。获取更多技术资料,请点击!UNIQUE约束在
SQL
数据库中是确保数据
唯一性
的重要工具。通过使用UNIQUE约束,我们可以防止重复数据的插入或更新,保证数据的一致性和准确性。在设计数据库表和进行数据操作时,我们应充分利用UNIQUE约束来提高数据质量和
查询
效率。
照片:Luis Cortes on Unsplash
不使用DISTINCT关键字就能获得
唯一
记录! 🏆
在你的数据分析项目中,只要你需要从数据库中提取
唯一
记录,一个简单的答案就是使用DISTINCT!
毫无疑问,DISTINCT是用来返回
唯一
记录的,它的工作做得很好。但是,它不会告诉你,你使用的JOIN和过滤器是否正确,这实际上是造成重复的原因。
因此,我总结了3个最好的、安全的、节省时间的替代方法,