• [问题求助] 我需要下载文件与会员认证服务
    我需要下载文件与会员认证服务,请问使用哪个模板合适。
  • 2023.4月合集
    使用\copy导入数据无法处理ascii为00的特殊字符https://bbs.huaweicloud.com/forum/thread-0231115897962567019-1-1.html【执行报错】CCN排队导致执行超时canceling statement due to user requesthttps://bbs.huaweicloud.com/forum/thread-02109115906854091002-1-1.html【时区问题】MySQL到DWS数据迁移时间相差8小时https://bbs.huaweicloud.com/forum/thread-0254115908183734001-1-1.htmlopenGauss配置文件的备份与恢复https://bbs.huaweicloud.com/forum/thread-0248116565519490079-1-1.htmlDWS JDBC 开启SSL认证连接https://bbs.huaweicloud.com/forum/thread-0257116772322379001-1-1.html【驱动】现网局点通过中间件读取dws报错https://bbs.huaweicloud.com/forum/thread-0242116649903510002-1-1.html有关GaussDB(DWS)数据库服务器磁盘空间问题https://bbs.huaweicloud.com/forum/thread-0298116734647910003-1-1.htmlGaussDB(DWS)在哪个版本会支持analyze单分区?https://bbs.huaweicloud.com/forum/thread-0209116731893893001-1-1.htmlSpring Boot配置实现方案https://bbs.huaweicloud.com/forum/thread-0298117361781425028-1-1.html?fid=565【GaussDB】GaussDB如何并行创建索引https://bbs.huaweicloud.com/forum/thread-0232117855292536028-1-1.html【GaussDB】GaussDB里面TIMESTAMP是带时区吗?https://bbs.huaweicloud.com/forum/thread-0214117772407824017-1-1.html【GaussDB】GaussDB支持create index if not exists吗?https://bbs.huaweicloud.com/forum/thread-0214117771123580016-1-1.htmlPostgreSQL插件那么多,怎样管理最高效?https://bbs.huaweicloud.com/forum/thread-0214117699069066013-1-1.html【GaussDB】GaussDB怎么转数据格式啊?https://bbs.huaweicloud.com/forum/thread-0214117596865170003-1-1.htmlGaussDB服务的分布式版和GaussDB(DWS)服务实际上有啥区别吗https://bbs.huaweicloud.com/forum/thread-0209117340674705018-1-1.html
  • [运维管理] GaussDB(DWS)在哪个版本会支持analyze单分区?
    已知8.1.1版本中,analyze支持单分区收集统计信息的语法,但是功能上不支持。那么8.1.3或8.2.1版本中,analyze是否可以支持单分区收集统计信息。
  • [技术干货] MyBatis 数据缓存机制
    1、什么是缓存1.存在内存中的临时数据 2.将用户经常查询的数据放在缓存(内存)中,用户去查询数据就不用从磁盘上(关系型数据库 数据文件)查询,从缓存中查询,从而提高查询效率,解决了高并发系统的性能问题。2、为什么使用缓存减少和数据库的交互次数,减少系统开销,提高系统效率。3、什么样的数据能使用缓存?经常查询并且不经常改变的数据。4、Mybatis缓存MyBatis包含一个非常强大的查询缓存特性,它可以非常方便地定制和配置缓存。缓存可以极大地 提升查询效率。 MyBatis系统中默认定义了两级缓存:一级缓存和二级缓存 默认情况下,只有一级缓存开启。(SqlSession级别的缓存,也称为本地缓存) 二级缓存需要手动开启和配置,他是基于namespace级别的缓存。 为了提高扩展性,MyBatis定义了缓存接口Cache。我们可以通过实现Cache接口来自定义二 级缓存4.1、一级缓存一级缓存也叫本地缓存: 与数据库同一次会话期间查询到的数据会放在本地缓存中。 以后如果需要获取相同的数据,直接从缓存中拿,没必须再去查询数据库为了方便查看缓存的存在,我们需要打开日志,来查看具体sql的执行信息<settings> <setting name="logImpl" value="STDOUT_LOGGING"/></settings>首先我们先创建两个接口//根据id查询用户User selectUserId(@Param("id") int id);//更新数据int updateUser(User user);mapper.xml文件中:<select id="selectUserId" resultType="com.xsq.pojo.User"> select * from USER where id=#{id}</select><insert id="updateUser" parameterType="com.xsq.pojo.User"> update user set name=#{name},pwd=#{pwd} where id=#{id}</insert>我们先创建两个对象,都查询id为1的用户,这里我们用一串等号,更好分辨两次查询@Test public void test1(){ SqlSession sqlSession = MybatisUtil.getSqlSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); User user1 = mapper.selectUserId(1); System.out.println(user1); System.out.println("============================="); User user2 = mapper.selectUserId(1); System.out.println(user2); sqlSession.close(); }测试结果:很明显面对俩次的查询mybatis只执行了一次查询,这就是sqlsession开启了一级缓存的原因测试结果:很明显面对俩次的查询mybatis只执行了一次查询,这就是sqlsession开启了一级缓存的原因Opening JDBC ConnectionCreated connection 243745864.Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@e874448]==> Preparing: select * from USER where id=? ==> Parameters: 1(Integer)<== Columns: id, name, pwd<== Row: 1, 张三, 123456<== Total: 1User(id=1, name=张三, pwd=123456)=============================User(id=1, name=张三, pwd=123456)我们再进行测试,我们这次将update语句也插入到测试语句中去,看会有什么不一样的结果@Test public void test1(){ SqlSession sqlSession = MybatisUtil.getSqlSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); User user1 = mapper.selectUserId(1); System.out.println(user1); System.out.println("============================="); int i = mapper.updateUser(new User(2,"二哈","789465")); User user2 = mapper.selectUserId(1); System.out.println(user2); sqlSession.close(); }测试结果:这次很明显执行了三次sql,这就是insert,update,delete语句会刷新缓存,比如查询1号用户,然后更新2号用户,这时再查询1号用户,则会重新查询==> Preparing: select * from USER where id=? ==> Parameters: 1(Integer)<== Columns: id, name, pwd<== Row: 1, 张三, 123456<== Total: 1User(id=1, name=张三, pwd=123456)===============================> Preparing: update user set name=?,pwd=? where id=? ==> Parameters: 二哈(String), 789465(String), 2(Integer)<== Updates: 1==> Preparing: select * from USER where id=? ==> Parameters: 1(Integer)<== Columns: id, name, pwd<== Row: 1, 张三, 123456<== Total: 1User(id=1, name=张三, pwd=123456)我们可以在测试代码中加入sqlSession.clearCache()来清理缓存@Test public void test1(){ SqlSession sqlSession = MybatisUtil.getSqlSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); User user1 = mapper.selectUserId(1); System.out.println(user1); //手动清理缓存 sqlSession.clearCache(); System.out.println("============================="); User user2 = mapper.selectUserId(1); System.out.println(user2); sqlSession.close(); }测试结果:可以看到sql语句执行了俩次,说明缓存确实清空了Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@e874448]==> Preparing: select * from USER where id=? ==> Parameters: 1(Integer)<== Columns: id, name, pwd<== Row: 1, 张三, 123456<== Total: 1User(id=1, name=张三, pwd=123456)===============================> Preparing: select * from USER where id=? ==> Parameters: 1(Integer)<== Columns: id, name, pwd<== Row: 1, 张三, 123456<== Total: 1User(id=1, name=张三, pwd=123456)小结:查询不同东西会刷新缓存insert。update,delete语句会刷新缓存,比如查询1号用户,然后更新2号用户,这时候再查询1号用户,则会重新查询查询不同的Mapper.xm会刷新缓存一级缓存默认开启,只在一次sqlsession中有效,也就是拿到连接的一个过程中有效一级缓存就是一个Map4.2、二级缓存二级缓存也叫全局缓存,一级缓存作用域太低了,所有诞生了二级缓存基于namespace级别的缓存,一个名称空间,对应一个二级缓存;工作机制一个会话查询一条数据,这个数据就会被放在当前会话的一级缓存中;如果当前会话关闭了,这个会话对应的一级缓存就没了,但是我们想要的是,会话关闭了,一级缓存中的数据被保存到二级缓存中;新的会话被查询信息,就可以从二级缓存中获取内容;不同的mapper查出的数据会放在自己对应的缓存(map)中;具体步骤:1.在mybatis-config.xml文件中开启缓存<!--开启二级缓存--><setting name="cacheEnabled" value="true"/>2.在mapper.xml配置文件中使用二级缓存<!--在当前Mapper.xml文件中使用二级缓存--><cache eviction="FIFO" flushInterval="60000" size="512" readOnly="true"/>也可以直接在mapper.xml文件中加入,但是要记得实体类要序列化,不然容易会报Caused by: java.io.NotSerializableException: com.xsq.pojo.User异常<cache/>在实体类中实现序列化:@Data@AllArgsConstructor@NoArgsConstructorpublic class User implements Serializable { private int id; private String name; private String pwd;}3.接口和xml配置的sql语句与一级缓存的一致4.测试类:我们这里创建了两个连接对象,也就是一级缓存行不通了@Test public void test1(){ SqlSession sqlSession = MybatisUtil.getSqlSession(); SqlSession sqlSession2 = MybatisUtil.getSqlSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); User user1 = mapper.selectUserId(1); System.out.println(user1); //关闭 sqlSession.close(); System.out.println("============================="); UserMapper mapper2 = sqlSession2.getMapper(UserMapper.class); User user2 = mapper2.selectUserId(1); System.out.println(user2); sqlSession2.close(); }5.不开二级缓存的测试结果:要执行俩次sql语句==> Preparing: select * from USER where id=? ==> Parameters: 1(Integer)<== Columns: id, name, pwd<== Row: 1, 张三, 123456<== Total: 1User(id=1, name=张三, pwd=123456)Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@e874448]Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@e874448]Returned connection 243745864 to pool.=================================Opening JDBC ConnectionChecked out connection 243745864 from pool.Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@e874448]==> Preparing: select * from USER where id=? ==> Parameters: 1(Integer)<== Columns: id, name, pwd<== Row: 1, 张三, 123456<== Total: 1User(id=1, name=张三, pwd=123456)开了二级缓存的测试结果:==> Preparing: select * from USER where id=? ==> Parameters: 1(Integer)<== Columns: id, name, pwd<== Row: 1, 张三, 123456<== Total: 1User(id=1, name=张三, pwd=123456)小结:只要开启了二级缓存,在同一个Mapper文件下就有效;所有的数据都会先放在一级缓存中;只有当会话提交,或者关闭的时候,才会提交到二级缓存中。
  • [技术干货] Redis中的五种数据类型使用场景
    在Redis中有五种数据类型String----------字符串Hash------------字典List-------------列表Set--------------集合Sorted Set------有序集合一 数据类型String—字符串 string是redis最基本的类型,你可以理解成与Memcached一模一样的类型,一个key对应一个value。string类型是二进制安全的。意思是redis的string可以包含任何数据。比如jpg图片或者序列化的对象 。string类型是Redis最基本的数据类型,一个键最大能存储512MB。String 数据结构是简单的 key-value 类型,value 不仅可以是 String,也可以是数字(当数字类型用 Long 可以表示的时候encoding 就是整型,其他都存储在 sdshdr 当做字符串)。使用 Strings 类型,可以完全实现目前 Memcached 的功能,并且效率更高。还可以享受 Redis 的定时持久化(可以选择 RDB 模式或者 AOF 模式),操作日志及 Replication 等功能。除了提供与 Memcached 一样的 get、set、incr、decr 等操作外,Redis 还提供了下面一些操作:1.LEN:O(1)获取字符串长度2.APPEND :往字符串 append 内容,而且采用智能分配内存(每次2倍) 3.设置和获取字符串的某一段内容 4.设置及获取字符串的某一位(bit) 5.批量设置一系列字符串的内容 6.原子计数器 7.GET SET 命令的妙用,请于清空旧值的同时设置一个新值,配合原子计数器使用Hash——字典(哈希) Hash是一个健值对集合,是一个String类型的key与value的映射表,特别适合用于存储对象。 使用场景:存储、读取、修改用户属性 在 Memcached 中,我们经常将一些结构化的信息打包成 hashmap,在客户端序列化后存储为一个字符串的值(一般是 JSON 格式),比如用户的昵称、年龄、性别、积分等。这时候在需要修改其中某一项时,通常需要将字符串(JSON)取出来,然后进行反序列化,修改某一项的值,再序列化成字符串(JSON)存储回去。简单修改一个属性就干这么多事情,消耗必定是很大的,也不适用于一些可能并发操作的场合(比如两个并发的操作都需要修改积分)。而 Redis 的 Hash 结构可以使你像在数据库中 Update 一个属性一样只修改某一项属性值。List——列表 使用场景:微博 TimeLine、消息队列 List 说白了就是链表(redis 使用双端链表实现的 List),相信学过数据结构知识的人都应该能理解其结构。使用 List 结构,我们可以轻松地实现最新消息排行等功能(比如新浪微博的 TimeLine )。List 的另一个应用就是消息队列,可以利用 List 的 *PUSH 操作,将任务存在 List 中,然后工作线程再用 POP 操作将任务取出进行执行。Redis 还提供了操作 List 中某一段元素的 API,你可以直接查询,删除 List 中某一段的元素Set——集合 集合是通过哈希表实现的,所以添加,删除,查找的复杂度都是O(1) 使用场景:1.共同好友、二度好友 2.利用唯一性,可以统计访问网站的所有独立 IP 3.好友推荐的时候,根据 tag 求交集,大于某个 threshold 就可以推荐 Set 就是一个集合,集合的概念就是一堆不重复值的组合。利用 Redis 提供的 Set 数据结构,可以存储一些集合性的数据。比如在微博应用中,可以将一个用户所有的关注人存在一个集合中,将其所有粉丝存在一个集合。因为 Redis 非常人性化的为集合提供了求交集、并集、差集等操作,那么就可以非常方便的实现如共同关注、共同喜好、二度好友等功能,对上面的所有集合操作,你还可以使用不同的命令选择将结果返回给客户端还是存集到一个新的集合中。Sorted Set——有序集合 使用场景:1.带有权重的元素,比如一个游戏的用户得分排行榜 2.比较复杂的数据结构,一般用到的场景不算太多 和Sets相比,Sorted Sets是将 Set 中的元素增加了一个权重参数 score,使得集合中的元素能够按 score 进行有序排列,比如一个存储全班同学成绩的 Sorted Sets,其集合 value 可以是同学的学号,而 score 就可以是其考试得分,这样在数据插入集合的时候,就已经进行了天然的排序。另外还可以用 Sorted Sets 来做带权重的队列,比如普通消息的 score 为1,重要消息的 score 为2,然后工作线程可以选择按 score 的倒序来获取工作任务。让重要的任务优先执行。二 redis 其他功能使用场景订阅-发布系统 Pub/Sub 从字面上理解就是发布(Publish)与订阅(Subscribe),在 Redis 中,你可以设定对某一个 key 值进行消息发布及消息订阅,当一个 key 值上进行了消息发布后,所有订阅它的客户端都会收到相应的消息。这一功能最明显的用法就是用作实时消息系统,比如普通的即时聊天,群聊等功能。事务——Transactions 谁说 NoSQL 都不支持事务,虽然 Redis 的 Transactions 提供的并不是严格的 ACID 的事务(比如一串用 EXEC 提交执行的命令,在执行中服务器宕机,那么会有一部分命令执行了,剩下的没执行),但是这个 Transactions 还是提供了基本的命令打包执行的功能(在服务器不出问题的情况下,可以保证一连串的命令是顺序在一起执行的,中间不会有其它客户端命令插进来执行)。Redis 还提供了一个 Watch 功能,你可以对一个 key 进行 Watch,然后再执行 Transactions,在这过程中,如果这个 Watched 的值进行了修改,那么这个 Transactions 会发现并拒绝执行。作者:勿以浮沙筑高台 链接:cid:link_0 来源:简书 著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。
  • [技术干货] mysql、SQL Server、pgsql、oracle 的区别
    MySQL、SQL Server、PostgreSQL(简称pgSQL)和Oracle都是常见的关系型数据库管理系统(RDBMS),它们在数据存储、查询、管理等方面都有各自的特点和优势,下面是它们的主要区别:开发公司/组织:MySQL由Oracle公司开发,SQL Server由Microsoft公司开发,PostgreSQL由PostgreSQL全球开发组织开发,Oracle由Oracle公司开发。授权方式:MySQL和PostgreSQL采用开源授权方式,SQL Server和Oracle采用商业授权方式。数据库大小和性能:MySQL和PostgreSQL主要面向中小型应用,处理数据量较小,但是性能较为出色;SQL Server和Oracle主要面向大型企业级应用,可以处理大规模数据,具有更好的性能和可扩展性。编程语言支持:MySQL、PostgreSQL和SQL Server均支持多种编程语言的API,如C、Java、Python等;Oracle主要支持Java和PL/SQL语言。数据库特性:MySQL和PostgreSQL支持JSON数据类型、空间数据类型等特性,SQL Server和Oracle则支持XML、CLR等特性。高可用性:MySQL、PostgreSQL和SQL Server都支持高可用性的集群部署,而Oracle则提供更加复杂的高可用性和容灾解决方案。操作系统支持:MySQL、PostgreSQL和SQL Server支持多种操作系统,如Windows、Linux、Unix等;Oracle则主要支持Unix和Linux系统。接口和工具:MySQL和PostgreSQL提供了较为简单的命令行接口和可视化管理工具,SQL Server和Oracle则提供了更为丰富的管理工具和集成开发环境(IDE)。总的来说,MySQL、PostgreSQL和SQL Server适用于不同规模的企业应用,而Oracle则主要面向大型企业级应用。在选择数据库时,需要根据具体的业务需求、数据量、性能要求等因素进行综合考虑。
  • [技术干货] 头脑风暴 —— “数据库” 结合 “低代码平台” 能擦出怎样的火花
    数据库表单设计:低代码平台可以通过可视化界面,快速创建数据库表单,而数据库则可以作为数据存储的后端,完成数据的持久化存储和管理。数据库模型设计:低代码平台可以通过可视化界面,快速设计数据库模型,而数据库则可以作为数据存储的后端,支持数据的快速查询和分析。数据库数据同步:低代码平台可以通过集成数据库的API,实现数据的同步和流转,从而实现不同应用系统之间的数据共享和交互。数据库报表分析:低代码平台可以通过集成数据库的分析工具,实现数据的可视化分析和报表生成,从而帮助企业快速了解业务状况和趋势。数据库自动化测试:低代码平台可以通过集成数据库的自动化测试工具,快速进行数据库的自动化测试,保证数据库的稳定性和数据的一致性。数据库数据清洗:低代码平台可以通过集成数据库的数据清洗工具,对数据库中的数据进行清洗和去重,保证数据的质量和准确性。数据库安全管理:低代码平台可以通过集成数据库的安全管理工具,对数据库进行安全管理和权限控制,保证数据的安全性和隐私性。数据库备份和恢复:低代码平台可以通过集成数据库的备份和恢复工具,实现数据库的备份和恢复,保证数据的可靠性和可恢复性。数据库集成:低代码平台可以通过集成不同类型的数据库,实现多种数据源之间的集成和交互,从而满足企业对数据的多元化需求。数据库云化:低代码平台可以通过集成云数据库服务,实现数据库的云化部署和管理,从而提高数据库的可用性和可扩展性。数据库自动化运维:低代码平台可以通过集成数据库的自动化运维工具,实现数据库的自动化运维和监控,从而提高数据库的稳定性和可靠性。数据库AI应用:低代码平台可以通过集成数据库的AI应用工具,实现数据库中的数据挖掘和机器学习,从而帮助企业深入挖掘数据的价值。数据库实时监控:低代码平台可以通过集成数据库实时监控工具,实时监控数据库的运行状态和性能指标,及时发现和解决问题,保证数据库的稳定性和可靠性。数据库迁移和升级:低代码平台可以通过集成数据库迁移和升级工具,实现数据库的迁移和升级,保证数据库的数据迁移和平稳升级,避免数据丢失和业务中断。数据库规则引擎:低代码平台可以通过集成数据库规则引擎,实现数据库中的规则管理和自动化处理,从而提高数据的处理效率和准确性。数据库容灾和恢复:低代码平台可以通过集成数据库容灾和恢复工具,实现数据库的容灾和恢复,从而保证数据库的可用性和数据的安全性。数据库业务流程管理:低代码平台可以通过集成数据库业务流程管理工具,实现数据库中业务流程的可视化管理和自动化处理,从而提高业务流程的效率和可靠性。数据库实时分析:低代码平台可以通过集成数据库实时分析工具,实时分析数据库中的数据,帮助企业及时发现业务状况和趋势,从而做出更加明智的决策。数据库数据治理:低代码平台可以通过集成数据库数据治理工具,实现对数据库中的数据进行规范化和标准化管理,从而提高数据的质量和可靠性。数据库数据合规:低代码平台可以通过集成数据库数据合规工具,实现对数据库中的数据进行合规性检查和管理,从而保证数据的合法性和安全性。数据库自动化部署:低代码平台可以通过集成数据库自动化部署工具,实现数据库的自动化部署和配置,从而提高数据库的部署效率和可靠性。数据库多租户管理:低代码平台可以通过集成数据库多租户管理工具,实现数据库中的多租户管理和数据隔离,从而保证数据的安全性和隐私性。数据库数据追溯:低代码平台可以通过集成数据库数据追溯工具,实现对数据库中数据的追溯和溯源,从而保证数据的可追溯性和可信度。数据库性能优化:低代码平台可以通过集成数据库性能优化工具,实现对数据库的性能优化和调优,从而提高数据库的性能和效率。好了。我能想到的就是这么多了,欢迎大家留言补充,谢谢!
  • [技术干货] MySQL EXPLAIN 的简单用法
    基本用法MySQL中的EXPLAIN用于分析SELECT查询语句的执行情况,可以帮助我们确定查询语句的执行计划,从而进行优化。可以通过以下步骤来使用EXPLAIN:打开MySQL客户端,并连接到需要分析的数据库。 输入EXPLAIN关键字和需要分析的SELECT查询语句,例如:EXPLAIN SELECT * FROM user WHERE age > 18; 执行查询,MySQL会返回一张表格,其中包含了查询语句的执行计划和相关的统计信息。字段讲解分析表格中的信息,可以查看查询语句的执行顺序、使用的索引、扫描的行数等信息,从而确定是否需要进行优化。其中,常见的列信息如下:id:当前执行计划的标识符,可以用来表示查询语句的执行顺序。select_type:查询类型,表示当前操作是简单查询、联合查询、子查询等。table:当前操作所涉及的表名称。partitions:表示表分区的情况,如果没有分区则为NULL。type:访问类型,表示使用了哪种类型的索引,例如ALL、index、range等。possible_keys:可能使用到的索引,多个索引之间用逗号隔开。key:实际使用的索引,如果为NULL则表示没有使用索引。key_len:索引字段的长度。ref:表示哪个字段或常量与索引的哪个部分进行了比较。rows:扫描的行数。Extra:表示额外的信息,例如使用了哪个临时表、使用了哪个文件排序等。根据表格中的信息进行分析,可以确定查询语句的执行计划、优化方案和索引使用情况,从而优化查询语句的性能。总结EXPLAIN只是一种工具,它可以帮助我们分析查询语句的执行情况,但并不一定保证查询语句的性能一定会得到提升,需要根据具体情况进行优化。
  • [技术干货] MySQL性能分析小结
    MySQL数据库是常见的两个瓶颈是CPU和I/O的瓶颈,利用一些Linux常用的命令来查看Mysql对服务器的CUP和I/O使用情况,通过慢查询日志找出有待优化的sql,通过show processlist查看正在执行的sql的情况以及及时kill死锁的sql,通过EXPLAIN分析需要优化的sql语句,除此之外,MySQL内部配置参数也做相应的调整和优化。下面具体来看看:    预计阅读时间:8 分钟**1、查看系统性能的常用Linux命令**1) Uptime命令的显示结果包括服务器已经运行了多长时间,有多少登陆用户和对服务器性能的总体评估(load average)。load average值分别记录了上个1分钟,5分钟和15分钟间隔的负载情况,load average不是一个百分比,而是在队列中等待执行的进程的数量。2)Top命令显示了实际CPU使用情况,默认情况下,它显示了服务器上占用CPU的任务信息并且每5秒钟刷新一次。你可以通过多种方式分类它们,包括PID、时间和内存使用情况。3)iostat是sysstat包的一部分。Iostat显示自系统启动后的平均CPU时间(与uptime类似),它也可以显示磁盘子系统的使用情况,iostat可以用来监测CPU利用率和磁盘利用率。4)Vmstat命令提供了对进程、内存、页面I/O块和CPU等信息的监控,vmstat可以显示检测结果的平均值或者取样值,取样模式可以提供一个取样时间段内不同频率的监测结果。5)ps和pstree命令是系统分析最常用的基本命令,ps命令提供了一个正在运行的进程的列表,列出进程的数量取决于命令所附加的参数。例如ps –A 命令列出所有进程和它们相应的进程ID(PID),进程的PID是使用其他一些工具之前所必须了解的,例如pmap或者renice。6)sar程序也是sysstat安装包的一部分。sar命令用于收集、报告和保存系统的信息。Sar命令由三个应用组成:sar用与显示数据;sa1和sa2用于收集和存储数据。默认情况下,系统会在crontab中加入自动收集和分析的操作7)free命令显示系统的所有内存的使用情况,包括空闲内存、被使用的内存和交换内存空间。Free命令显示也包括一些内核使用的缓存和缓冲区的信息。当使用free命令的时候,需要记住linux的内存结构和虚拟内存的管理方法,比如空闲内存数量的限制,还有swap空间的使用并不标志一个内存瓶颈的出现。8)pmap命令显示一个或者多个进程使用内存的数量,你可以用这个工具来确定服务器上哪个进程占用了过多的内存从而导致内存瓶颈。9)strace截取和记录进程的系统调用信息,还包括进程接受的命令信号。这是一个有用的诊断和调试工具,系统管理员可以通过strace来解决程序上的问题。命令格式,需要指定需要监测的进程ID。这个多为开发人员使用。10)mpstat命令也是sysstat包的一部分。Mpstat命令用于监测一个多CPU系统中每个可用CPU的情况。Mpstat命令可以显示每个CPU或者所有CPU的运行情况,同时也可以像vmstat命令那样使用参数进行一定频率的采样结果的监测。2、数据库查询优化(1)性能瓶颈定位Mysql> show status —— 显示状态信息(扩展show status like ‘XXX’)Mysql> show variables —— 显示系统变量(扩展show variables like ‘XXX’)Mysql> show innodb status —— 显示InnoDB存储引擎的状态Mysql> show processlist —— 查看当前SQL执行,包括执行状态、是否锁表等Shell> mysqladmin variables -u username -p password —— 显示系统变量Shell> mysqladmin extended-status -u username -p password —— 显示状态信息我常用的主要有show status和show processlist。(2)慢查询日志分析法慢查询日志开启方法有两种,具体如下:方法1:在配置文件 my.cnf 或 my.ini 中在[mysqld]一行下面加入两个配置参数:(注:log-slow-queries参数为慢查询日志存放的位置)(注:long_query_time=2中的2表示查询超过两秒才记录)方法2:通过命令行设置变量来即时启动慢日志查询(3)explain(执行计划)分析查询通过explain命令可以得到:– 表的读取顺序– 数据读取操作的操作类型– 哪些索引可以使用– 哪些索引被实际使用– 表之间的引用– 每张表有多少行被优化器查询(4)profiling 分析查询profiling默认是关闭的,打开方式 :**3、小结**(1)慢查询(分析出问题的sql)开启慢查询日志,可以让MySQL记录下查询超过指定时间的语句,通过慢日志查询可以知道哪些SQL语句执行效率低下。(2)Explain(显示了mysql如何使用索引来处理select语句以及连接表,通过explain我们可以得知SQL语句的具体执行情况,还可以结合show命令查看执行状态。(3)Profile(查询到 SQL 会执行多少时间, 并看出CPU/Memory 使用量),通过profiling命令得到更准确的SQL执行消耗系统资源的信息————————————————版权声明:本文为CSDN博主「东晨雨」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。原文链接:https://blog.csdn.net/weixin_39076229/article/details/90384124
  • [技术干货] Oracle SQL优化
    1、SQL 语句尽量用大写的;因为 oracle 总是先解析 SQL 语句,把小写的字母转换成大写的再执行。2、使用表的别名:当在 SQL 语句中连接多个表时, 尽量使用表的别名并把别名前缀于每个列上。这样一来,就可以减少解析的时间并减少那些由列歧义引起的语法错误。3、选择最有效率的表名顺序(只在基于规则的优化器(RBO)中有效):ORACLE 的解析器按照从右到左的顺序处理 FROM 子句中的表名,FROM 子句中写在最后的表(基础表也称为驱动表,driving table)将被最先处理,在 FROM 子句中包含多个表的情况下,必须选择记录条数最少的表作为基础表。如果有 3 个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表。4、WHERE 子句中的连接顺序:ORACLE 采用自下而上的顺序解析 WHERE 子句,根据这个原理,表之间的连接必须写在其他WHERE 条件之前, 那些可以过滤掉最大数量记录的条件必须写在 WHERE 子句的末尾。5、SELECT 子句中避免使用 * :ORACLE 在解析的过程中, 会将’’ 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间。6、减少访问数据库的次数:ORACLE 在内部执行了许多工作: 解析 SQL 语句,估算索引的利用率,绑定变量,读数据块等。7、整合简单、无关联的数据库访问:如果有几个简单的数据库查询语句,可以整合到一个查询中(即使它们之间没有关系)。8、在 SQLPlus,SQLForms 和 ProC 中重新设置 ARRAYSIZE 参数,可以增加每次数据库访问的检索数据量,建议值为 200。9、删除重复记录:最高效的删除重复记录方法 :    DELETE FROM TEMP E    WHERE E.ROWID > (SELECT MIN(X.ROWID)    FROM TEMP1 X WHERE X.TEMP_NO = E.TEMP_NO);10、减少对表的查询:在含有子查询的 SQL 语句中,要特别注意减少对表的查询。    SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER) = ( SELECT    TAB_NAME,DB_VER FROM TAB_COLUMNS WHERE VERSION = 604);11、避免使用耗费资源的操作:带有 DISTINCT、UNION、MINUS、INTERSECT、ORDER BY 的 SQL 语句会启动 SQL 引擎执行耗费资源的排序(SORT)功能。DISTINCT 需要一次排序操作,而其他的至少需要执行两次排序。通常,带有 UNION、MINUS、INTERSECT 的 SQL 语句都可以用其他方式重写。如果数据库的 SORT_AREA_SIZE 调配得好,使用 UNION,MINUS,INTERSECT 也是可以考虑。12、优化 GROUP BY:提高 GROUP BY 语句的效率,可以通过将不需要的记录在 GROUP BY 之前过滤掉。下面两个查询返回相同结果但明显第二个效率更高。低效:    SELECT JOB,AVG(AGE) FROM TEMP    GROUP BY JOB HAVING JOB = ‘STUDENT’ OR JOB = ‘MANAGER’;    高效:    SELECT JOB,AVG(AGE) FROM EMP    WHERE JOB = ‘STUDENT’ OR JOB = ‘MANAGER’ GROUP BY JOB;    13、根据需要用 UNION ALL 替换 UNION: 当 SQL 语句需要 UNION 两个查询结果集合时,这两个结果集合会以 UNION-A    LL 的方式被合并,    然后在输出最终结果前进行排序。如果用 UNION ALL 替代 UNION, 这样排序    就不是必要了。    效率就会因此得到提高。需要注意 UNION ALL 将重复输出两个结果集合中相同    记录。因此要    从业务需求使用 UNION ALL 的可行性。UNION 将对结果集合排序,这个操作会    使用到    SORT_AREA_SIZE 这块内存。对于这块内存的优化也很重要。    低效:    SELECT USER_ID,BILL_ID FROM USER_TAB1 WHERE AGE = ‘20’    UNION    SELECT USER_ID,BILL_ID FROM USER_TAB2 WHERE AGE = ‘20’;    高效:    SELECT USER_ID,BILL_ID FROM USER_TAB1 WHERE AGE = ‘20’    UNION ALL    SELECT USER_ID,BILL_ID FROM USER_TAB2 WHERE AGE = ‘20’;    14、用 EXISTS 替换 DISTINCT:    当 SQL 包含一对多表查询时,避免在 SELECT 子句中使用 DISTINCT,一般用 E    XIST 替换,EXISTS    查询更为迅速,因为 RDBMS 核心模块将在子查询的条件一旦满足后,立刻返回    结果。 (低效):    SELECT DISTINCT USER_ID,BILL_ID FROM USER_TAB1 D,USER_TAB2 E    WHERE D.USER_ID= E.USER_ID;    (高效):    SELECT USER_ID,BILL_ID FROM USER_TAB1 D WHERE EXISTS(SELECT 1    FROM USER_TAB2 E WHERE E.USER_ID= D.USER_ID);15、尽量多使用 COMMIT:只要有可能,在程序中尽量多使用 COMMIT,这样程序的性能得到提高,需求也会因为 COMMIT所释放的资源而减少。COMMIT 所释放的资源:a. 回滚段上用于恢复数据的信息。b. 被程序语句获得的锁。c. redo log buffer 中的空间。d. ORACLE 为管理上述 3 种资源中的内部花销。16、用 Where 子句替换 HAVING 子句:避免使用 HAVING 子句,HAVING 只会在检索出所有记录之后才对结果集进行过滤。这个处理需要排序,总计等操作。最好能通过 WHERE 子句限制记录的数目。(非 oracle 中)on、where、having 这三个都可以加条件的子句中,on 是最先执行,where 次之,having 最后。a、on 是先把不符合条件的记录过滤后才进行统计,可以减少中间运算要处理的数据,速度是最快的;b、where 比 having 快点,因为它过滤数据后才进行 sum,在两个表联接时才用on 的,所以在一个表的时候,就剩下 where 跟 having 比较了。在单表查询统计的情况下,如果要过滤的条件没有涉及到要计算字段,那它们的结果是一样的,只是 where 可以使用 rushmore技术,而 having就不能,在速度上后者要慢。如果要涉及到计算的字段,就表示在没计算之前,这个字段的值是不确定的,where 的作用时间是在计算之前就完成的,而 having 就是在计算后才起作用的,所以在这种情况下,两者的结果会不同。在多表联接查询时,on 比 where 更早起作用。系统首先根据各个表之间的关联条件,把多个表合成一个临时表后,再由 where 进行过滤,然后再计算,计算完后再由 having进行过滤。17、用 TRUNCATE 替代 DELETE:当删除表中的记录时,在通常情况下, 回滚段(rollback segments)用来存放可以被恢复的信息。如果你没有 COMMIT 事务,ORACLE 会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况)。而当运用 TRUNCATE 时,回滚段不再存放任何可被恢复的信息。当命令运行后,数据不能被恢复。因此很少的资源被调用,执行时间也会很短。注:TRUNCATE 只在删除全表适用,TRUNCATE 是 DDL 不是 DML。18、使用 DECODE 函数来减少处理时间:使用 DECODE 函数可以避免重复扫描相同记录或重复连接相同的表。19、用 EXISTS 替代 IN、用 NOT EXISTS 替代 NOT IN:在基于基础表的查询中经常需要对另一个表进行联接。在这种情况下, 使用 EXISTS(或 NOTEXISTS)通常将提高查询的效率。在子查询中,NOT IN 子句将执行一个内部的排序和合并。无论在哪种情况下,NOT IN 都是最低效的(要对子查询中的表执行了一个全表遍历)。所以尽量将NOT IN 改写成外连接(Outer Joins)或 NOT EXISTS。(高效)    SELECT A.* FROM TEMP(基础表) A WHERE AGE > 0    AND EXISTS(SELECT 1 FROM TEMP1 WHERE A.ID= ID AND NAME=‘TOM’);    (低效)    SELECT A.* FROM TEMP(基础表) A WHERE AGE > 0    AND A.ID IN(SELECT ID FROM TEMP1 WHERE NAME =‘TOM’);20、用索引提高效率:索引是表的一个概念部分,用来提高检索数据的效率,ORACLE 使用了复杂的自平衡 B-tree 结构。通常通过索引查询数据比全表扫描要快。当 ORACLE 找出执行查询和 Update 语句的最佳路径时, ORACLE 优化器将使用索引。同样在联结多个表时使用索引也可以提高效率。另一个使用索引的好处是,它提供了主键(primary key)的唯一性验证。那些 LONG 或 LONG RAW数据类型, 你可以索引几乎所有的列。通常在大型表中使用索引特别有效。在扫描小表时,使用索引同样能提高效率。使用索引虽能得到查询效率的提高,但也必须注意到它的代价。索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时, 索引本身也会被修改。这意味着每条记录的 INSERT、DELETE、UPDATE 将为此多付出 4、5 次的磁盘 I/O。因为索引需要额外的存储空间和处理,一些不必要的索引反而会使查询反应时间变慢。定期的重构索引:ALTER INDEX REBUILD21、用>=替代>:高效:SELECT * FROM TEMP WHERE ID >=4;低效:SELECT * FROM TEMP WHERE ID >3;区别:前者 DBMS 将直接跳到第一个 ID 等于 4 的记录而后者将首先定位到 ID=3的记录并且向前扫描到第一个 ID 大于 3 的记录。22、避免在索引列上使用 NOT:NOT 会产生在和在索引列上使用函数相同的影响。当 ORACLE 遇到 NOT,就会停止使用索引转而执行全表扫描。23、避免在索引列上使用计算:WHERE 子句中,如果索引列是函数的一部分,优化器将不使用索引而使用全表扫描。低效:SELECT … FROM TEMP WHERE SAL * 12 > 25000;高效:SELECT … FROM TEMP WHERE SAL > 25000/12;24、用 UNION 替换 OR(适用于索引列):用 UNION 替换 WHERE 子句中的 OR 将会起到较好的效果。对索引列使用 O R 将造成全表扫描。注意,以上规则只针对多个索引列有效。如果有 column 没有被索引, 查询效率可能会因为你没有选择 OR 而降低。高效:    SELECT * FROM USER_TAB1 WHERE USER_ID = 10    UNION    SELECT * FROM USER_TAB1 WHERE AGE = 20;    低效:    SELECT * FROM USER_TAB1 WHERE USER_ID = 10 OR AGE = 20;25、避免在索引列上使用 IS NULL 和 IS NOT NULL避免在索引中使用任何可以为空的列,ORACLE 将无法使用该索引。对于单列索引,如果列包含空值,索引中将不存在此记录。对于复合索引,如果每个列都为空,索引中同样不存在此记录。如果至少有一个列不为空,则记录存在于索引中。如果唯一性索引建立在表的 A 列和 B 列上, 并且表中存在一条记录的 A,B 值为(123,null),ORACLE 将不接受下一条具有相同 A,B 值(123,null)的记录(插入)。然而如果所有的索引列都为空,ORACLE 将认为整个键值为空而空不等于空。因此你可以插入 10000条具有相同键值的记录,当然它们都是空! 因为空值不存在于索引列中,所以 WHERE 子句中对索引列进行空值比较将使 ORACLE 停用该索引。低效: (索引失效)SELECT … FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL;高效: (索引有效)SELECT … FROM DEPARTMENT WHERE DEPT_CODE >=0;26、总是使用索引的第一个列:如果索引是建立在多个列上,只有在它的第一个列(leading column)被 where 子句引用时,优化器才会选择使用该索引。当引用索引的第二个列时,优化器使用了全表扫描而忽略了索引。27、用 WHERE 替代 ORDER BY:ORDER BY 子句只在以下两种严格的条件下使用索引:(1)ORDER BY 中所有的列必须包含在相同的索引中并保持在索引中的排列顺序。(2)ORDER BY 中所有的列必须定义为非空。WHERE 子句使用的索引和 ORDER BY 子句中所使用的索引不能并列。例如:    USER_ID PK NOT NULL    USER_DESC NOT NULL    USER_TYPE NULL    低效: (索引不被使用)    SELECT USER_ID FROM USER_TAB ORDER BY USER_TYPE;    高效: (使用索引)    SELECT USER_ID FROM USER_TAB WHERE USER_TYPE> 0;28、避免改变索引列的类型:当比较不同数据类型的数据时, ORACLE 自动对列进行简单的类型转换。假设 USER_ID 是一个数值类型的索引列。SELECT … FROM USER_TAB WHERE USER_ID = ‘123’;实际上,经过 ORACLE 类型转换, 语句转化为:SELECT … FROM USER_TAB WHERE USER_ID = TO_NUMBER(‘123’);幸运的是,类型转换没有发生在索引列上,索引的用途没有被改变。现在,假设 USER_TYPE 是一个字符类型的索引列。SELECT … FROM USER_TAB WHERE USER_TYPE = 123 ;这个语句被 ORACLE 转换为:SELECT … FROM USER_TAB WHERE TO_NUMBER(USER_TYPE)=123;因为内部发生的类型转换, 这个索引将不会被用到! 为了避免 ORACLE 对你的SQL 进行隐式的类型转换, 最好把类型转换用显式表现出来。注:当字符和数值比较时, ORACLE 会优先转换数值类型到字符类型。SELECT … FROM USER_TAB WHERE TO_NUMBER(USER_TYPE)=123;29、WHERE 子句:某些 SELECT 语句中的 WHERE 子句不使用索引。(1)'!=‘不走索引。索引只能告诉我们什么存在于表中, 而不能告诉你什么不在表中。(2)’||‘是字符连接函数。就象其他函数那样, 停用了索引。(3)’+'是数学函数。和其他数学函数一样, 停用了索引。(4)相同的索引列不能互相比较,这将会启用全表扫描。30、 a.如果检索数据量超过 30%的表中记录数,使用索引将没有显著的效率提高。b.在特定情况下,使用索引也许会比全表扫描更慢,但这是同一个数量级上的区别。而通常情况下,使用索引比全表扫描要块几倍乃至几千倍。————————————————版权声明:本文为CSDN博主「天幕顽主」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。原文链接:https://blog.csdn.net/m0_51740882/article/details/125082830
  • [技术干货] Oracle 函数大全
    F.1字符函数——返回字符值(chr,concat,initcap,lower,lpad/rpad,nls_initcap,nls_lower,nls_upper,regexp_replace,regexp_substr,replace,trim/ltrim/rtrim,soundex,substr,translate,upper)说明:可以sql和plsql中使用CHR语法:  chr(x)功能:给出整数X,返回对应的ASCII码字符。CHR和ASCII是一对反函数。SQL> select chr(54740) 别名1,chr(65) 别名2 from dual;别名1  别名2赵       A--------------------------------------------------CONCAT语法: CONCAT(string1,string2)功能:连接两个字符串SQL> select concat('010-','88888888')||'23'  连接 from dual;连接010-8888888823--------------------------------------------------INITCAP语法:INITCAP(string)功能:返回字符单词首字母大写,其余小写,单词用空格和非字母字符分隔。SQL> select initcap('smith hEllo') upp from dual;UPPSmith Hello--------------------------------------------------LOWER    语法:LOWER(string)     功能:所以字母小写     SQL> select lower('AaBbCcDd') AaBbCcDd from dual;     AaBbCcDd     aabbccdd--------------------------------------------------LPAD/RPAD语法:LPAD/RPAD(string1,x[,string2])功能:在string1字符左边或右边粘贴数个string2字符,直到字符总字节数达到x字节。string2默认为空格。如果string2的长度要比X字符少,就按照需要进行复制。如果string2多于X字符,则仅string2前面的X各字符被使用。如果string1长度大于x,则返回string1左端x个字符。RPAD  在列的右边粘贴字符LPAD  在列的左边粘贴字符SQL> select lpad(rpad('gao',10,'*'),17,'*')from dual;LPAD(RPAD('GAO',1*******gao*******不够字符则用*来填满--------------------------------------------------NLS_INITCAP语法:NLS_INITCAP(string[,nlsparams])功能:返回字符串每个单词第一个字母大写而单词中的其他字母小写的string,nlsparams指定了不同于该会话缺省值的不同排序序列。如果不指定参数,则功能和INITCAP相同。Nlsparams可以使用的形式是:‘NLS_SORT=sort’ 这里sort制订了一个语言排序序列。--------------------------------------------------NLS_LOWER语法:NLS_LOWER(string[,nlsparams])功能:返回字符串中的所有字母都是小写形式的string。不是字母的字符不变。Nlsparams参数的形式与用途和NLS_INITCAP中的nlsparams参数是相同的。如果nlsparams没有被包含,那么NLS_LOWER所作的处理和LOWER相同。--------------------------------------------------NLS_UPPER语法:NLS_UPPER(string[,nlsparams])功能:返回字符串中的所有字母都是大写的形式的string。不是字母的字符不变。nlsparams参数的形式与用途和NLS_INITCAP中的相同。如果没有设定参数,则NLS_UPPER功能和UPPER相同。使用位置:过程性语句和SQL语句。--------------------------------------------------REGEXP_REPLACE语法:REGEXP_REPLACE(str1,pattem[,str2[,pos[,occ[,par]]]])功能:10g新增函数,扩展了REPLACE函数的功能,并且用于按照特定正则表达式的规则替换字符串。其中参数str1指定源字符表达式,pattem指定正则表达式,str2指定替换字符串,pos指定起始搜索位置,occ指定替换出现的第几个字符串,par指定默认匹配操作的文本串。select REGEXP_REPLACE(a,’(.)’,’\1’) a from count;A r g e n t i n a体会NVL为DECODE,只支持NVL()内不再有其它括号()select a,       instr(upper(a), 'NVL(', 1) a3,       instr(upper(a), ')',instr(upper(a), 'NVL(', 1),1) a4,       substr(a,instr(upper(a), 'NVL(', 1),instr(upper(a), ')',instr(upper(a), 'NVL(', 1),1)-instr(upper(a), 'NVL(', 1)+1) a41,       substr(a,instr(upper(a), 'NVL(', 1)+4,instr(upper(a), ')',instr(upper(a), 'NVL(', 1), 1)-instr(upper(a), 'NVL(', 1)-4) a5,       REGEXP_REPLACE(       substr(a,instr(upper(a), 'NVL(', 1)+4,instr(upper(a), ')',instr(upper(a), 'NVL(', 1), 1)-instr(upper(a), 'NVL(', 1)-4),       '(.*),(.*)','\2,\1'       ) a6,       REGEXP_REPLACE(       substr(a,instr(upper(a), 'NVL(', 1)+4,instr(upper(a), ')',instr(upper(a), 'NVL(', 1), 1)-instr(upper(a), 'NVL(', 1)-4),       '(.*),(.*)','decode(\1,null,\2,'''',\2,\1)'       ) a7,       substr(a,1,instr(upper(a), 'NVL(', 1)-1)||REGEXP_REPLACE(       substr(a,instr(upper(a), 'NVL(', 1)+4,instr(upper(a), ')',instr(upper(a), 'NVL(', 1), 1)-instr(upper(a), 'NVL(', 1)-4),       '(.*),(.*)','decode(\1,null,\2,'''',\2,\1)'       )||substr(a,instr(upper(a), ')',instr(upper(a), 'NVL(', 1), 1)+1) a8  from temp_liut a;--------------------------------------------------判断是否是数字regexp_replace(a, '\d+', '') is nullREGEXP_SUBSTR语法:REGEXP_SUBSTR(str1,pattem [,pos[,occ[,par]]])功能:10g新增函数,扩展了SUBSTR函数的功能,并且用于按照特定表达式的规则返回字符串的子串。其中参数str1指定源字符表达式,pattem指定规则表达式, pos指定起始搜索位置,occ指定替换出现的第几个字符串,par指定默认匹配操作的文本串。Select REGEXP_SUBSTR(‘http://www.oracle.com/products’,’http://([[:alnum:]]+\.?)’) a from dual;ahttp://www.oracle.com/--------------------------------------------------REPLACE语法:REPLACE(string,search_str[,replace_str])功能:把string中的所有的子字符串search_str用可选的replace_str替换,如果没有指定replace_str,所有的string中的子字符串search_str都将被删除。REPLACE是TRANSLATE所提供的功能的一个子集。REPLACE('string','s1','s2')string   希望被替换的字符或变量s1       被替换的字符串s2       要替换的字符串SQL> select replace('he lohe you','he','i') from dual;replace('he lohe you','he','i')i loi you--------------------------------------------------TRIM/LTRIM/RTRIM语法1:LTRIM/RTRIM(string1,[string2])语法2:trim([string2] from string1)语法1功能:中删除从左/右边算起出现在string1中的字符string2,string2如果是多个字符则逐个单字符比对删除,tring2被缺省设置为单个的空格。当遇到不在string2中的第一个字符,结果就被返回了;语法2功能:删除左右两边出现在string1中的字符string2,tring2必须为单字符,否则报错。select ltrim(rtrim('   gao qian jing   ',' '),' ') from dual;gao qian jingselect ltrim('abaaaabbbcda','ab') from dual;cdaselect trim('a' from 'abacda') from dual;bacd--------------------------------------------------SOUNDEX语法:  SOUNDEX(string)功能:  返回string的声音表示形式.这对于比较两个拼写不同但是发音类似的单词而言很有帮助,如果字符发音相同,则返回的结果会一致.SOUNDEX 返回一个与给定的字符串读音相同的字符串SQL> create table table1(xm varchar(8));SQL> insert into table1 values('weather');SQL> insert into table1 values('wether');SQL> insert into table1 values('gao');SQL> select xm from table1 where soundex(xm)=soundex('weather');XMweatherwether--------------------------------------------------SUBSTR语法:  SUBSTR(string,a[,b])功能:截取字符串,从第a个开始取b个字符,这个务必要注意,是字符。 vachar2最长4000个字节,GBK编码中一个中文字符占2个字节,韩文字符占4个字节,如果string是date或者number的数据类型,会自动转化为varchar2。SQL> select substr('13088888888',3,8) 截取字符串 from dual;截取字符串08888888select SUBSTR(t.a,4),a from temp_liut t;JAN-00  04-jan-00--------------------------------------------------TRANSLATE语法:  TRANSLATE(string,from_str,to_str)功能:  将字符string按照from_str与to_str的对应规则进行处理,返回将所出现的from_str中的每个字符替换为to_str中的相应字符以后的string. TRANSLATE是REPLACE所提供的功能的一个超集.如果from_str比to_str长,那么在from_str中而不在to_str中而外的字符将从string中被删除,因为它们没有相应的替换字符. to_str不能为空.Oracle把空字符串认为是NULL,并且如果TRANSLATE中的任何参数为NULL,那么结果也是NULL.Select TRANSLATE('2abc2234','01234abcde','99999XXXXX') tra from dual9XXX9999select replace(TRANSLATE('as中国fd1234','1234567890','0000000000'),'0') from dual;查找字符串',01234,2342,2,'中逗号出现次数select length(translate(',01234,2342,2,', 'a0123456789', ' ')) from dual;判断字符串是否是数字replace(translate(a, '0123456789', '0'),'0') is nullregexp_replace(a, '\d+', '') is nullUPPER语法: UPPER(string)功能: 所有字母大写.(不是字母的字符不变.如果string是CHAR数据类型的,那么结果也是CHAR类型的.如果string是VARCHAR2类型的,那么结果也是VARCHAR2类型的). SQL> select upper('AaBbCcDd') upper from dual;UPPERAABBCCDD--------------------------------------------------F.2    字符函数——返回数字(ascii,instr,instrb,length,lengthb,nls_sort)说明:可以sql和plsql中使用ASCII语法:  ASCII(string)功能: 返回string字符串首字符的十进制表示ascii码值。 CHR和ASCII是互为相反的函数.CHR得到给定字符编码的响应字符. ASCII得到给定字符的字符编码.SQL> select ascii('A') A,ascii('a') a,ascii('0') zero,ascii(' ') space from dual;A         A      ZERO     SPACE65        97        48        32--------------------------------------------------INSTR语法:  INSTR(str1, str2[,a,b])功能:  得到在str1中包含str2的位置. a>0,str1时从左边开始检查的,开始的位置为a;a<0,那么str1是从右边开始进行扫描的,开始的位置为a。第b次出现的位置将被返回. a和b都缺省设置为1,这将会返回在string1中第一次出现string2的位置.如果string2在a和b的规定下没有找到,那么返回0.位置的计算是相对于string1的开始位置的,不管a和b的取值是多少.INSTR(C1,C2,I,J) 在一个字符串中搜索指定的字符,返回发现指定的字符的位置;C1    被搜索的字符串C2    希望搜索的字符串I     搜索的开始位置,默认为1(如果为负数会从后向前搜索)J     出现的位置,默认为1SQL> select instr('oracle traning','ra',1,2) instring from dual;INSTRING9--------------------------------------------------INSTRB语法:  INSTRB(string1, string2[a,[b]])功能:  和INSTR相同,只是操作的对参数字符使用的位置的是字节.--------------------------------------------------LENGTH语法:  LENGTH(string)功能:  返回字符串的长度,特别注意的,对于空的字段,返回为空,而不是0。SELECT   LENGTH (' 130 ') 返回字符串长度  FROM DUAL;返回字符串长度5--------------------------------------------------LENGTHB语法:  LENGTHB(string)功能:  返回以字节为单位的string的长度.对于单字节字符集LENGTHB和LENGTH是一样的.--------------------------------------------------NLS_SORT语法: NLS_SORT(string[,nlsparams])功能: 得到用于排序string的字符串字节.所有的数值都被转换为字节字符串,这样在不同数据库之间就保持了一致性. Nlsparams的作用和NLS_INITCAP中的相同.如果忽略参数,会话使用缺省排序.--------------------------------------------------F.3    数学函数(abs,acos,asin,atan,atan2,ceil,cos,cosh,exp,floor,ln,log,mod,power,round,sign,sin,sinh,sqrt,tan,tanh,trunc)说明:数学函数的输入和输出都是数字型,并且多数函数精确到38位。函数cos\cosh\exp\ln\log\sin\sinh\sqrt\tan\tanh精确到36位,acos\asin\atan\atan2精确到30为。数学函数可以在sql语句和plsql块中引用。ABS语法:   ABS(x)功能:   得到x的绝对值.SQL> select abs(100),abs(-100) from dual;ABS(100) ABS(-100)100       100--------------------------------------------------ACOS语法:  ACOS(x)功能:  返回x的反余弦值. 输入x应该从-1到1之间的数,结果在0到pi之间,输出以弧度为单位.SQL> select acos(-1) from dual;ACOS(-1)3.1415927--------------------------------------------------ASIN语法:  ASIN(x)功能:  返回x的反正弦值. X的范围应该是-1到1之间,返回的结果在-pi/2到pi/2之间,以弧度为单位.SQL> select asin(0.5) from dual;ASIN(0.5).52359878--------------------------------------------------ATAN语法:  ATAN(x)功能:  计算x的反正切值.返回值在-pi/2到pi/2之间,单位是弧度.SQL> select atan(1) from dual;ATAN(1).78539816--------------------------------------------------ATAN2语法:  ATAN2(x,y)功能: 返回x除以y的反正切值.结果在负的pi/2到正的pi/2之间,单位是弧度.--------------------------------------------------CEIL语法:  CEIL(x)功能:  计算大于或等于x的最小整数值.SQL> select ceil(3.1415927) from dual;CEIL(3.1415927)      4--------------------------------------------------COS语法:  COS(x)功能:  返回x的余弦值. x的单位是弧度.SQL> select cos(-3.1415927) from dual;COS(-3.1415927)     -1--------------------------------------------------COSH语法:  COSH(x)功能:  计算x的双曲余弦值.SQL> select cosh(20) from dual;COSH(20)242582598--------------------------------------------------EXP语法:  EXP(x)功能:  计算e的x次幂. e为自然对数,约等于2.71828.SQL> select exp(2),exp(1) from dual;EXP(2)    EXP(1)7.3890561 2.7182818--------------------------------------------------FLOOR语法:  FLOOR(x)功能:  返回小于等于x的最大整数值.SQL> SELECT   FLOOR (2345.67), FLOOR (-2345.67) FROM dual;FLOOR(2345.67)   FLOOR (-2345.67)  2345                        -2346--------------------------------------------------LN语法:  LN(x)功能:  返回x的自然对数. x必须是正数,并且大于0SQL> select ln(1),ln(2),ln(2.7182818) from dual;LN(1)     LN(2) LN(2.7182818)0 .69314718     .99999999--------------------------------------------------LOG语法:  LOG(x,y)功能:  计算以x为底的y的对数.底必须大于0而且不等于1, y为任意正数.SQL> select log(2,1),log(2,4) from dual;LOG(2,1)  LOG(2,4)0         2--------------------------------------------------MOD语法:  MOD(x,y)功能:  返回x除以y的余数.如果y是0,则返回xSQL> select mod(10,3),mod(3,3),mod(2,3) from dual;MOD(10,3)  MOD(3,3)  MOD(2,3)1         0         2--------------------------------------------------POWER语法:  POWER(x,y)功能:  计算x的y次幂.POWER 返回n1的n2次方根SQL> select power(2,10),power(3,3) from dual;POWER(2,10) POWER(3,3)1024         27--------------------------------------------------ROUND语法:  ROUND(x[,y])功能:  四舍五入函数,y缺省值为0,x保留整数;y>0,x保留小数点右边y位;y<0,x保留小数点左边 |y| 位;可以对时间进行round,效果是只保留年月日。SELECT   ROUND (55.655, 2),   --55.66         ROUND (55.654, 2),   --55.65         ROUND (45.654, -1),  --50         ROUND (45.654, -2),  --0         ROUND (55.654, -2)   --100  FROM   DUAL;--------------------------------------------------SIGN语法:  SIGN(x)功能:  检测x的正负.如果x<0返回-1.如果x=0返回0.如果x>0返回1.SQL> select sign(123),sign(-100),sign(0) from dual;SIGN(123) SIGN(-100)   SIGN(0)1         -1         0常和decode 结合使用--------------------------------------------------SIN语法:SIN(x)功能:计算x的正弦值. X是一个以弧度表示的角度.SQL> select sin(1.57079) from dual;SIN(1.57079)   1--------------------------------------------------SINH语法:SINH(x)功能:返回x的双曲正弦值.SQL> select sin(20),sinh(20) from dual;SIN(20)  SINH(20).91294525 242582598--------------------------------------------------SQRT语法:  SQRT(x)功能:  返回x的平方根. x必须是正数.SQL> select sqrt(64),sqrt(10) from dual;SQRT(64)  SQRT(10)8 3.1622777--------------------------------------------------TAN语法:  TAN(x)功能:  计算x的正切值, x是一个以弧度位单位的角度.SQL> select tan(20),tan(10) from dual;TAN(20)   TAN(10)2.2371609 .64836083--------------------------------------------------TANH语法:  TANH(x)功能:  计算x的双曲正切值.SQL> select tanh(20),tan(20) from dual;TANH(20)   TAN(20)1 2.2371609--------------------------------------------------TRUNC语法:  TRUNC(x[,y])功能:  截取数字函数,只舍不入函数, y缺省值为0,x保留整数;y>0,x保留小数点右边y位;y<0,x保留小数点左边 |y| 位SELECT   TRUNC (55.655, 2),   --55.65         TRUNC (55.654, 2),   --55.65         TRUNC (45.654, -1),  --40         TRUNC (45.654, -2),  --0         TRUNC (55.654, -2)   --0  FROM   DUAL;SELECT   TRUNC (SYSDATE, 'DD'),  --当天         TRUNC (SYSDATE, 'MM'),  --本月第一天         TRUNC (SYSDATE, 'yyyy'),  --本年第一天         TRUNC (SYSDATE, 'day'),  --本周第一天         TRUNC (SYSDATE, 'q')  --本季度第一天  FROM   DUAL;--------------------------------------------------F.4  日期时间函数(add_months,current_date,current_timestamp,dbtimezone,extract,from_tz,last_day,months_between,new_time,next_day,numtodsinternal,numtoyminternal,round,sys_extract_utc,sysdate,systimestamp,to_dsinternal,to_timestamp,to_timestamp_tz,to_yminternal,trunc,tz_offset)说明:日期时间函数用于处理date和timestamp类型的数据,除了函数months_between返回数字外,其余均返回date类型,Oracle以7位数字格式来存放日期数据,包括世纪、年、月、日、小时、分钟、秒,并且默认日期显式格式为“DD-MON-YY”。ADD_MONTHS语法:ADD_MONTHS(d,x)功能:返回日期d加上x个月后的月份。x可以是任意整数。如果结果日期中的月份所包含的天数比d日期中的“日”分量要少。(即相加后的结果日期中的日分量信息已经超过该月的最后一天,例如,8月31日加上一个月之后得到9月31日,而9月只能有30天)返回结果月份的最后一天。使用位置:过程性语言和SQL语句。SQL> select to_char(add_months(to_date('199912','yyyymm'),2),'yyyymm') from dual;TO_CHA200002SQL> select to_char(add_months(to_date('199912','yyyymm'),-2),'yyyymm') from dual;TO_CHA199910--------------------------------------------------CURRENT_DATE语法: CURRENT_DATE功能:9i新增函数,返回当前会话时区所对应的日期时间。select CURRENT_DATE from dual;--------------------------------------------------CURRENT_TIMESTAMP语法:CURRENT_TIMESTAMP功能:9i新增函数,返回当前会话时区所对应的日期时间。select CURRENT_TIMESTAMP from dual;--------------------------------------------------DBTIMEZONE语法:DBTIMEZONE功能:返回数据库所在时区。select DBTIMEZONE from dual;--------------------------------------------------EXTRACT语法: EXTRACT(s)功能:9i新增函数,从日期时间值中取得所需要的特定数据Select extract(year from sysdate) year from dual;Yaer2013--------------------------------------------------FROM_TZ语法: FROM_TZ(s)功能:9i新增函数,将特定时区的TIMESTAMP值转换为TIMESTAMP WITH TIME ZONE值。Select from_tz(timestamp ‘2013-03-28 08:00:00’,’3:00’);--------------------------------------------------LAST_DAY语法:LAST_DAY(d)功能:计算包含日期的d的月份最后一天的日期.这个函数可以用来计算当月中剩余天数.使用位置:过程性语言和SQL语句。LAST_DAY返回日期的最后一天SQL> select to_char(sysdate,'yyyy.mm.dd') aa from dual;aa2004.05.09SQL> select last_day(sysdate) from dual;LAST_DAY(S31-5月 -04--------------------------------------------------LOCALTIMESTAMP语法:LOCALTIMESTAMP功能:9i新增函数,返回当前会话时区的日期时间。Select LOCALTIMESTAMP from dual;--------------------------------------------------MONTHS_BETWEEN语法:MONTHS_BETWEEN(date1,date2)功能:计算date1和date2之间相差的月数.如果date1<date2,则返回负数;如果date1,date2这两个日期中日分量信息是相同的,或者这两个日期都分别是所在月的最后一天,那么返回的结果是一个整数,否则包括一个小数,小数为富余天数除以31,Oracle以每月31天为准计算结果。SQL> select months_between('19-12月-1999','19-3月-1999') mon_between from dual;MON_BETWEEN  9SQL>selectmonths_between(to_date('2000.05.20','yyyy.mm.dd'),to_date('2005.05.20','yyyy.dd')) mon_betw from dual;MON_BETW-60--------------------------------------------------NEW_TIME语法:NEW_TIME(d,zone1,zone2)功能:计算当时区zone1中的日期和时间是d时候,返回时区zone2中的日期和时间. zone1和zone2是字符串. 给出在this时区=other时区的日期和时间。使用位置:过程性语言和SQL语句。NEW_TIME   (d,   ‘tz1’,   ‘tz2’)d::一个有效的日期型变量tz1   &   tz2::下表中的任一时区时区1   时区2       说明AST       ADT           大西洋标准时间BST       BDT           白令海标准时间CST       CDT           中部标准时间EST       EDT           东部标准时间GMT                     格林尼治标准时间HST       HDT           阿拉斯加—夏威夷标准时间MST       MDT           山区标准时间NST                     纽芬兰标准时间PST       PDT           太平洋标准时间YST       YDT           YUKON标准时间SQL> select to_char(sysdate,'yyyy.mm.dd hh24:mi:ss') bj_time,to_char(new_time2  (sysdate,'PDT','GMT'),'yyyy.mm.dd hh24:mi:ss') los_angles from dual;BJ_TIME             LOS_ANGLES2004.05.09 11:05:32 2004.05.09 18:05:32--------------------------------------------------NEXT_DAY语法:NEXT_DAY(d,string)功能: 给出日期d和星期string之后计算下一个星期的日期. String是星期几;当前会话的语言指定了一周中的某一天.返回值的时间分量与d的时间分量是相同的. String的内容可以忽略大小写.使用位置:过程性语言和SQL语句。NEXT_DAY(date,'day')SQL> select next_day('18-5月-2001','星期五') next_day from dual;NEXT_DAY25-5月 -01--------------------------------------------------NUMTODSINTERNAL语法:NUMTODSINTERNAL(n,char_expr)功能:将数字n转换为INTERNAL DAY TO SECOND格式, char_expr可以是DAY\HOUR\MINUTE或SECOND。Select NUMTODSINTERNAL(1000,’minute’) from dual;--------------------------------------------------NUMTOYMINTERNAL语法:NUMTOYMINTERNAL(n,char_expr)功能:将数字n转换为INTERVAL YEAR TO MONTH格式,char_expr可以是year或者month。Select NUMTOYMINTERNAL(100,’MONTH’) from dual;--------------------------------------------------ROUND语法:ROUND(d[,format])功能:将日期d按照由format指定的格式进行四舍五入处理处理.如果没有给format则使用缺省设置`DD`.使用位置:过程性语言和SQL语句。Select round(sysdate,’MONTH’) from dual;--------------------------------------------------SYS_EXTRACT_UTC语法:SYS_EXTRACT_UTC(date)功能:返回特定时区时间所对应的格林威治时间。Select SYS_EXTRACT_UTC(systimestamp) from dual;--------------------------------------------------SYSDATE语法: SYSDATE功能:取得当前的日期和时间,类型是DATE.它没有参数.但在分布式SQL语句中使用时,SYSDATE返回本地数据库的日期和时间.使用位置:过程性语言和SQL语句。SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;--------------------------------------------------SYSTIMESTAMP语法:SYSTIMESTAMP功能:9i新增函数,返回当前系统的日期时间及时区。Select systimestamp from dual;--------------------------------------------------TO_DSINTERNAL语法:TO_DSINTERNAL(char[,’nls_param’])功能:9i新增函数,将符合特定日期和时间格式的字符串转变为INTERVAL DAY TO SECOND类型。Select TO_DSINTERNAL(’58:10:10’) from dual;--------------------------------------------------TO_TIMESTAMP语法:TO_TIMESTAMP(char[fmt[,’nls_param’]])功能:9i新增函数,将符合特定日期和时间格式的字符串转变为TIMESTAMP类型。select systimestamp from dual1. 字符型转成timestampSelect TO_TIMESTAMP(’01-1月-03’) from dual;select to_timestamp('01-10月-08 07.46.41.000000000 上午','dd-MON-yy hh:mi:ss.ff AM') from dual;2. timestamp转成date型select cast(TO_TIMESTAMP('2015-10-01 21:11:11.328', 'yyyy-mm-dd hh24:mi:ss.ff') as date)from dual;3. date型转成timestampselect cast(sysdate as timestamp) date_to_timestampfrom dual;--------------------------------------------------TO_TIMESTAMP_TZ语法:TO_TIMESTAMP_TZ(char[fmt[,’nls_param’]])功能:9i新增函数,将符合特定日期和时间格式的字符串转变为TIMESTAMP WITH TIME ZONE类型。Select TO_TIMESTAMP_TZ(’20130101’,’yyyymmdd’) from dual;--------------------------------------------------TO_YMINTERNAL语法:TO_YMINTERNAL(char)功能:9i新增函数,将符合特定日期和时间格式的字符串转变为INTERVAL YEAR TO MONTH类型。select TO_TIMESTAMP('2015-10-01 21:11:11.328', 'yyyy-mm-dd hh24:mi:ss.ff') -       TO_TIMESTAMP('2015-10-01 11:11:11.328', 'yyyy-mm-dd hh24:mi:ss.ff')  from dual;--------------------------------------------------TRUNC语法:TRUNC(d,format)功能:截断日期时间数据,计算截尾到由format指定单位的日期d.缺省参数同ROUNG.使用位置:过程性语言和SQL语句。如果fmt='mi'表示保留分,截断秒,如此类推。SQL> select to_char(trunc(sysdate,'hh'),'yyyy.mm.dd hh24:mi:ss') hh,  2  to_char(trunc(sysdate,'mi'),'yyyy.mm.dd hh24:mi:ss') hhmm from dual;HH                  HHMM2004.05.09 11:00:00 2004.05.09 11:17:00--------------------------------------------------TZ_OFFSET语法:TO_OFFSET(time_zone_name||sessiontimezone||dbtimezone)功能:9i新增函数,返回特定时区与UTC相比的时区偏移。Select TO_OFFSET (’EST’) from dual;--------------------------------------------------F.5   转换函数(asciistr,bin_to_num,cast,chartorowid,compose,convert,decompose,hextoraw, INTERVAL,rawtonhex,rowidtochar,rowidtonchar,scn_to_timestamp,timestamp_to_scn,to_char,to_clob,to_date,to_lob,to_label,to_multi_byte,to_nchar,to_number,to_single_byte,translate...using,unistr)说明:用于将数值从一种数据类型转换为另一种数据类型。ASCIISTR语法:ASCIISTR(s)功能:9i新增函数,将任意字符集的字符串转变为数据库字符集的ASCII字符串。Select ASCIISTR (’中国’) 中 from dual;中\4E2D\56FD--------------------------------------------------BIN_TO_NUM语法:BIN_TO_NUM(expr[,expr]…)功能:9i新增函数,用于将位向量值转变为实际的数字值。Select BIN_TO_NUM(1,0,1,1,1) 中 from dual;中23--------------------------------------------------CAST语法:CAST(expr AS type_name)功能:用于将一个内置数据类型或集合类型转变为另一个内置数据类型或集合类型。可以作用于长度为0的空字段视图建表格之用。Select cast(SYSDATE AS VARCHAR2) 中 from dual;Create table tb_dual nologging as Select cast(null as varchar2(1)) from dual;--------------------------------------------------CHARTOROWID语法:CHARTOROWID(string)功能: 将字符数据类型转换为ROWID类型,把包含外部格式的ROWID的CHAR或VARCHAR2数值转换为内部的二进制格式.参数string必须是包含外部格式的ROWID的18字符的字符串.oracle7和oracle8中的外部格式是不同的.CHARTOROWID是ROWIDTOCHAR的反函数.使用位置:过程性语言和SQL语句。SQL> select rowid,rowidtochar(rowid),ename from scott.emp;ROWID              ROWIDTOCHAR(ROWID) ENAMEAAAAfKAACAAAAEqAAA AAAAfKAACAAAAEqAAA SMITHAAAAfKAACAAAAEqAAB AAAAfKAACAAAAEqAAB ALLENAAAAfKAACAAAAEqAAC AAAAfKAACAAAAEqAAC WARDAAAAfKAACAAAAEqAAD AAAAfKAACAAAAEqAAD JONES--------------------------------------------------COMPOSE语法:COMPOSE(string)功能:9i新增函数,用于将输入字符串转变为UNICODE字符串值。Select COMPOSE(‘o’||unistr(‘\0308’)) 中 from dual;中?--------------------------------------------------CONVERT语法:CONVERT(string,dest_set[,source_set])功能:将字符串string从source_set所表示的字符集转换为由dest_set所表示的字符集.如果source_set没有被指定,它缺省的被设置为数据库的字符集.使用位置:过程性语言和SQL语句。SQL> select convert('中国','US7ASCII','WE8ISO8859P1') "conversion" from dual;--------------------------------------------------DECOMPOSE语法:DECOMPOSE(string)功能:9i新增函数,用于分解字符串并返回相应的UNICODE字符串。Select COMPOSE(‘chateoux’) 中 from dual;--------------------------------------------------HEXTORAW语法:HEXTORAW(string)功能: 将string一个十六进制构成的字符串转换为二进制RAW数值. String中的每两个字符表示了结果RAW中的一个字节..HEXTORAW和RAWTOHEX为相反的两个函数.使用位置:过程性语言和SQL语句。Select HEXTORAW (‘AB56’) 中 from dual;--------------------------------------------------INTERVAL语法:INTERVAL 'integer [- integer]' {YEAR | MONTH} [(precision)][TO {YEAR | MONTH}]功能:该数据类型常用来表示一段时间差, 注意时间差只精确到年和月. precision为年或月的精确域, 有效范围是0到9, 默认值为2。INTERVAL '123-2' YEAR(3) TO MONTH表示: 123年2个月, "YEAR(3)" 表示年的精度为3, 可见"123"刚好为3为有效数值, 如果该处YEAR(n), n<3就会出错, 注意默认是2.INTERVAL '11:12:10.1234567' HOUR TO SECOND表示:小时,秒结果:+00 11:12:10.123457INTERVAL '123' YEAR(3)表示: 123年0个月INTERVAL '300' MONTH(3)表示: 300个月, 注意该处MONTH的精度是3啊.INTERVAL '4' YEAR表示: 4年, 同 INTERVAL '4-0' YEAR TO MONTH 是一样的INTERVAL '50' MONTH表示: 50个月, 同 INTERVAL '4-2' YEAR TO MONTH 是一样INTERVAL '123' YEAR表示: 该处表示有错误, 123精度是3了, 但系统默认是2, 所以该处应该写成 INTERVAL '123' YEAR(3) 或"3"改成大于3小于等于9的数值都可以的INTERVAL '5-3' YEAR TO MONTH + INTERVAL '20' MONTH =INTERVAL '6-11' YEAR TO MONTH表示: 5年3个月 + 20个月 = 6年11个月RAWTONHEX语法:RAWTONHEX(rawvalue)功能:9i新增函数,将RAW类数值rawvalue转换为一个相应的十六进制表示的字符串. rawvalue中的每个字节都被转换为一个双字节的字符串. RAWTOHEX和HEXTORAW是两个相反的函数.使用位置:过程性语言和SQL语句。Select rawtonhex(‘7D’) from dual;--------------------------------------------------ROWIDTOCHAR语法:ROWIDTOCHAR(rowid)功能:9i新增函数,将ROWID类型的数值rowid转换为varchar2的字符串表示,在oracle7和oracle8之间有些不一样的地方. ROWIDTOCHAR和CHARTOROWID是两个相反的函数.使用位置:过程性语言和SQL语句。--------------------------------------------------ROWIDTONCHAR语法:ROWIDTOCHAR(rowid)功能:9i新增函数,将ROWID类型的数值rowid转换为Nvarchar2的字符串表示,在oracle7和oracle8之间有些不一样的地方. ROWIDTOCHAR和CHARTOROWID是两个相反的函数.使用位置:过程性语言和SQL语句。--------------------------------------------------SCN_TO_TIMESTAMP语法:SCN_TO_TIMESTAMP(number)功能:10g新增函数,根据输入的scn值返回对应的大概日期时间,其中number用于指定scn值.使用位置:过程性语言和SQL语句。Select SCN_TO_TIMESTAMP(ora_rowscn) from emp;--------------------------------------------------TIMESTAMP_TO_SCN语法:TIMESTAMP_TO_SCN(timestamp)功能:10g新增函数,用于根据输入的timestamp返回所对应的scn值,其中timestamp、用于指定日期时间。使用位置:过程性语言和SQL语句。Select TIMESTAMP_TO_SCN(order_date) from emp;--------------------------------------------------TO_CHAR语法1:TO_CHAR(character)功能1:用于将NCHAR,NVARCHAR2,CLOB,NCLOB数据转变为数据库字符集数据,当用于NCHAR,NVARCHAR2,NCLOB时字符用单引号括起来,前面加上n。Select to_char(n’中国’) from dual;语法2:TO_CHAR(d [,format[,nlsparams]])功能2:将日期d转换为一个VARCHAR2类型的字符串.format指定日期格式,.如果没有给定format,使用的就是该会话的缺省日期格式.nlsparams指定NLS参数. nlsparams的格式是:“NLS_DATE_LANGUAGE”使用位置:过程性语言和SQL语句。select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;2004/05/09 21:14:41语法3:TO_CHAR(labels[,format])功能3:将MISLABEL的LABEL转换为一个VARCHAR2类型的变量.使用位置:在trusted数据库的过程性语句和SQL语句。语法4: TO_CHAR(num[,format[,nlsparams]])功能4:将NUMBER类型的参数num转换为一个VARCHAR2类型的变量.如果指定了format,那么它会控制这个转换处理.表5-5列除了可以使用的数字格式.如果没有指定format,它会控制这个转换过程.下面列出了可以使用的数字格式.如果没有指定format,那么结果字符串将包含和num中有效位的个数相同的字符. nlsparams用来指定小数点和千分位分隔符和货币符号.可以使用的格式:`NLS_NUMERIC_CHARS=”dg”NLS_CURRENCY=”string”d和g分别表示列小数点和千分位分隔符. String表示了货币的符号.例如,在美国小数点分隔符通常是一个句点(.),分组分隔符通常是一个逗号(,),而千分位符号通常是一个$.使用位置:过程性语言和SQL语句。SELECT TO_CHAR(TO_DATE('11-oct-2007'), 'fmDdthsp "of" Month, Year') FROM DUAL;以上正确,需要注意的是不属于转换日期格式标识符需要使用双引号,如上面的"of"SELECT promo_name, TRIM(TO_CHAR(promo_end_date,'Day')) ||', '||                   TRIM(TO_CHAR(promo_end_date,'Month')) ||' '||                   TRIM(TO_CHAR(promo_end_date,'DD, YYYY')) AS last_day       FROM promotions;等价于下面,fm有trim的作用去掉多余空格SELECT promo_name,TO_CHAR(promo_end_date,'fmDay')||','||                  TO_CHAR(promo_end_date,'fmMonth')||' '||                  TO_CHAR(promo_end_date,'fmDD, YYYY') AS last_day       FROM promotions;上述当中,Day\DAY\day等等转换后都是带空格的,而YYYY则不会。--将number格式转换为货币格式,前面均带空格select TO_CHAR(12345,'$99999D99') from dual;-- $12345.00SELECT TO_CHAR(1890.55,'$00G000D00') FROM DUAL;-- $01,890.55SELECT TO_CHAR(1890.55,'$99G999D99') FROM DUAL;--   $1,890.55--------------------------------------------------TO_CLOB语法:TO_CLOB (char)功能:9i新增函数,将字符串转变为CLOB类型。Char参数使用NCHAR,NVARCHAR2,NCLOB类型,字符串需要单引号括起来,且在前面加上n.Select TO_CLOB(n’中国’) from dual;--------------------------------------------------TO_DATE语法:TO_DATE(String[,format[,nlsparams]])功能:将符合特定日期格式的字符串转变为date类型. format是一个日期格式字符串.当不指定format的时候,使用该会话的缺省日期格式,需要特别注意的,缺省格式并不适用'2015-03-03'这种形式。Select to_date(‘20130101’,’yyyymmdd’) from dual;--正确SELECT TO_DATE('01/JANUARY/2007') FROM DUAL;--正确,缺省支持SELECT TO_DATE('01-JANUARY-2007') FROM DUAL;--正确,缺省支持SELECT TO_DATE('2015-03-03') FROM DUAL;--错误,缺省不支持--------------------------------------------------TO_LOB语法:TO_LOB (long_column)功能:9i新增函数,将LONG或LONG ROW列的数据转变为相应的LOB类型。但需要注意的是,在单纯的select语句中会报错,如例子所示。使用位置:过程性语言和SQL语句。例子:to_lob转化longselect VIEW_NAME,to_lob(text) text from user_views;  --会报错create table temp_liutao nologging as select VIEW_NAME,to_lob(text) text from user_views  --通过--------------------------------------------------TO_LABEL语法:TO_LABEL(String[,format])功能:将String转换为一个MLSLABEL类型的变量. String可以是VARCHAR2或者CHAR类型的参数.如果指定了format,那么它就会被用在转换中.如果没有指定format,那么使用缺省的转换格式.使用位置:过程性语言和SQL语句。--------------------------------------------------TO_MULTI_BYTE语法:TO_MULTI_BYTE(String)功能:计算所有单字节字符都替位换位等价的多字节字符的String.该函数只有当数据库字符集同时包含多字节和单字节的字符的时候有效.否则, String不会进行任何处理. TO_MULTI_BYTE和TO_SINGLE_BYTE是相反的两个函数.使用位置:过程性语言和SQL语句。SQL>  select to_multi_byte('高') from dual;TO高--------------------------------------------------TO_NCHAR语法1:TO_NCHAR(char)功能1:将字符串由数据库字符集转变为民族字符集。SQL>  select TO_NCHAR ('高') from dual;语法2:TO_NCHAR(date,[,fmt[,nls_param]])功能2:将日期时间值转变为民族字符集。SQL>  select TO_NCHAR (sysdate) from dual;语法3:TO_NCHAR(number)功能3:将数字值转变为民族字符集。SQL>  select TO_NCHAR (10) from dual;--------------------------------------------------TO_NUMBER语法: TO_NUMBER(String[,format[,nlsparams]])功能:将CHAR或者VARCHAR2类型的String转换为一个NUMBER类型的数值.如果指定了format,那么String应该遵循相应的数字格式. Nlsparams的行为方式和TO_CHAR中的完全相同.TO_NUMBER和TO_CHAR是两个相反的函数.使用位置:过程性语言和SQL语句。SQL> select to_number('1999') year from dual;     YEAR     1999--------------------------------------------------TO_SINGLE_BYTE语法: TO_SINGLE_BYTE(String )功能:计算String中所有多字节字符都替换为等价的单字节字符.该函数只有当数据库字符集同时包含多字节和单字节的字符的时候有效.否则, String不会进行任何处理.TO_MULTI_BYTE和TO_SINGLE_BYTE是相反的两个函数.使用位置:过程性语言和SQL语句。Select TO_SINGLE_BYTE(‘a b c’) from dual;--------------------------------------------------TRANSLATE…USING语法: TRANSLATE(str1 USING zfj)功能:将字符串转变为数据库字符集(char_cs)或民族字符集(nchar_cs)Select TRANSLATE(‘中国’ using nchar_cs) from dual;--------------------------------------------------UNISTR语法: UNISTR(str1)功能:9i新增函数,输入字符串返回相应的UNICODE字符Select UNISTR (‘\00D6’) from dual;--------------------------------------------------F.6  分组统计函数(avg,corr,count,covar_pop,covar_samp,cume_dist,dense_rank,first,group_id,grouping,grouping_id,glb,last,listagg,lub,max,min,percent_rank,percentile_cont,percentile_disc,rank,stddev,stddev_pop,stddev_samp,sum,var_pop,var_samp,variance)说明:分组函数也被称为多行函数,它会根据输入的多行数据返回一个结果。主要用于执行数据统计或汇总操作,并且分组函数只能出现在select语句选择列表、order by子句和having子句中。注意分组函数不能直接在plsql中引用,只能在内嵌select语句中使用。AVG语法:AVG([DISTINCT|ALL]col)功能:返回一列数据的平均值,缺省使用是ALL修饰符,all表示对所有的值求平均值,distinct排重后再求平均值使用位置:查询列表和GROUP BY子句.SQL> select avg(distinct sal) from gao.table3;AVG(DISTINCTSAL)         3333.33SQL> select avg(all sal) from gao.table3;AVG(ALLSAL)    2592.59--------------------------------------------------CORR语法:CORR([expr1,expr2)功能:返回成对数值的相关系数,其数值使用表达式”covar_pop(expr1,expr2)/(stddev_pop(expr1)*stddev_pop(expr2))”使用位置:查询列表和GROUP BY子句.SQL> select corr(list_,min_) from gao.table3;--------------------------------------------------COUNT语法:COUNT(*|[DISTINCT|ALL] col)功能:得到查询中行的数目.如果使用了*获得行的总数.如果在参数中传递的是选择列表,那么计算的是非空数值。我基于10G测试,有主键情况下,count(主键)最快,count(1)和count(*)调动主键统计,时间上一样;无主键情况下count(索引列)最快,但需要注意count(列名)统计不包括null,count(常量)和count(*)包括nullSelect count(distinct sal) from emp;--------------------------------------------------COVAR_POP语法:COVAR_POP(expr1,expr2)功能:返回成对数字的协方差,其数值使用表达式”(sum(expr1*expr2)-sum(expr1)*sum(expr2)/n)/n”Select COVAR_POP(column1,column2) from emp;--------------------------------------------------COVAR_SAMP语法:COVAR_SAMP(expr1,expr2)功能:返回成对数字的协方差,其数值使用表达式”(sum(expr1*expr2)-sum(expr1)*sum(expr2)/n)/n-1”Select COVAR_SAMP(column1,column2) from emp;--------------------------------------------------CUME_DIST语法:CUME_DIST(expr1,expr2…) within group (order by expr1,expr2…)功能:返回特定数值在一组行数据中的累积分布比例。Select CUME_DIST(2000) within group (order by sel) from emp;--------------------------------------------------DENSE_RANK语法:DENSE_RANK(expr1,expr2…) within group (order by expr1,expr2…)功能:返回特定数据在一组行数据中的等级。Select DENSE_RANK (5000) within group (order by sel) from emp;--------------------------------------------------FIRST语法:FIRST功能:9i新增,不能单独使用,必须与其他分组函数结合使用。通过使用该函数,可以取得排序等级的第一级,然后然后使用分组函数汇总该等级的数据。Select min(sal) keep (dense_rank first order by comm desc) from emp;--------------------------------------------------GROUP_ID语法:GROUP_ID功能:9i新增,用于区分分组结果中的重复行。Select deptno,job,avg(sal),group_id() from emp group by deptno,rollup(deptno,job);--------------------------------------------------GROUPING语法:GROUPING(expr)功能:用于确定统计结果是否使用了特定的表达式,返回0则用到了表达式,1则未用。例如:select corp_code,org_level,count(1),       grouping(corp_code),       grouping(org_level)  from tb_sys_organizationgroup by rollup(corp_code, org_level);select case grouping(corp_code)         when 1 then 'all_corp' else corp_code end corp_code,       case grouping(org_level)         when 1 then 'all_org' else org_level end org_level,       count(1)  from tb_sys_organizationgroup by rollup(corp_code, org_level);--------------------------------------------------GROUPING_ID语法:GROUPING_ID(expr1[,expr2]…)功能:9i新增,用于返回对应于特定行的grouping位向量的值。Select deptno,job,sum(sal),grouping_id(job,deptno) from emp group by rollup(deptno,jon)--------------------------------------------------GLB语法:GLB ([DISTINCT|ALL]label)功能:获得由label界定的最大下界.函数仅用于trusted oracle.使用位置:trusted数据库的选择列表和GROUP BY子句.--------------------------------------------------LAST语法:LAST功能:9i新增,不能单独使用,必须与其他分组函数结合使用。通过使用该函数,可以取得排序等级的最后一级,然后使用分组函数汇总该等级的数据。Select min(sal) keep (dense_rank last order by comm) from emp;--------------------------------------------------LISTAGG语法:listagg功能:列转行select listagg(o.rybs, ';') within group(order by o.rybs)  from gk_xszrr o where rownum <= 100;----------------------------------------------------LUB语法:LUB ([DISTINCT|ALL]label)功能:获得由label界定的最小上界.用于trusted oracle.数据库.使用位置:trusted数据库的选择列表和GROUP BY子句.过程性语言和SQL语句。--------------------------------------------------MAX语法:MAX([DISTINCT|ALL]col)功能:获得选择列表或表达式的最大值,ALL表示对所有的值求最大值,DISTINCT表示对不同的值求最大值,相同的只取一次使用位置:仅用于查询选择和GROUP BY子句.SQL> select max(distinct sal) from scott.emp;MAX(DISTINCTSAL)            5000--------------------------------------------------MIN语法:MIN([DISTINCT|ALL]col)功能:获得选择列表或表达式的最小值,ALL表示对所有的值求最小值,DISTINCT表示对不同的值求最小值,相同的只取一次使用位置:仅用于查询选择和GROUP BY子句.SQL> select min(all sal) from gao.table3;MIN(ALLSAL)    1111.11--------------------------------------------------PERCENT_RANK语法:PERCENT_RANK(expr1,expr2…)WITHIN GROUP (ORDER BY expr1,expr2…)功能:该函数用于返回特定数值在统计级别中所占的比例。SQL> select percent_rank(3000) within group(order by sal) from emp;--------------------------------------------------PERCENTILE_CONT语法:PERCENTILE_CONT(percent_expr)WITHIN GROUP (ORDER BY expr)功能:9i新增,用于返回在统计级别中处于某个百分点的特定数值(按照连续分布模型确定)。SQL> select percentile_cont(.6) within group(order by sal) from emp;--------------------------------------------------PERCENTILE_DISC语法:PERCENTILE_DISC(percent_expr)WITHIN GROUP (ORDER BY expr)功能:9i新增,用于返回在统计级别中处于某个百分点的特定数值(按照离散分布模型确定)。SQL> select percentile_cont(.6) within group(order by sal) from emp;--------------------------------------------------RANK语法:RANK(expr1,expr2…)WITHIN GROUP (ORDER BY expr1,expr2…)功能:该函数用于返回特定数值中所占据的等级。SQL> select rank(3000) within group(order by sal) from emp;--------------------------------------------------STDDEV语法:STDDEV([DISTINCT|ALL]col)功能:获得选择列表的标准差.标准差为方差(VARIANCE)的平方根, ALL表示对所有的值求标准差,DISTINCT表示只对不同的值求标准差.使用位置:仅用于查询选择和GROUP BY子句.SQL> select stddev(sal) from scott.emp;STDDEV(SAL)  1182.5032SQL> select stddev(distinct sal) from scott.emp;STDDEV(DISTINCTSAL)           1229.951--------------------------------------------------STDDEV_POP语法:STDDEV_POP(col)功能:返回统计标准差,其数值是统计方差的平方根.使用位置:仅用于查询选择和GROUP BY子句.SQL> select stddev_pop(sal) from scott.emp;--------------------------------------------------STDDEV_SAMP语法:STDDEV_SAMP(col)功能:返回采样标准差,其数值是采样方差的平方根.SQL> select stddev_samp(sal) from scott.emp;--------------------------------------------------SUM语法:SUM([DISTINCT|ALL]col)功能:返回选择的数值和总和使用位置:仅用于查询选择和GROUP BY子句.Select sum(sal) from emp;--------------------------------------------------VAR_POP语法:VAR_POP([DISTINCT|ALL]col)功能:返回统计方差.使用公式为(sum(expr*expr)-sum(expr)*sum(expr)/count(expr))/(count(expr)使用位置:仅用于查询选择和GROUP BY子句.SQL> select VAR_POP (sal) from scott.emp;--------------------------------------------------VAR_SAMP语法:VAR_SAMP([col)功能:返回采样方差.使用公式为(sum(expr*expr)-sum(expr)*sum(expr)/count(expr))/(count(expr-1)使用位置:仅用于查询选择和GROUP BY子句.SQL> select variance(sal) from scott.emp;--------------------------------------------------VARIANCE语法:VARIANCE([DISTINCT|ALL]col)功能:返回选择列或表达式的采样方差.使用公式为(sum(expr*expr)-sum(expr)*sum(expr)/count(expr))/(count(expr-1)使用位置:仅用于查询选择和GROUP BY子句.SQL> select variance(sal) from scott.emp;VARIANCE(SAL)    1398313.9--------------------------------------------------分组函数,除了count(*),count(1),其他分组函数都会忽略null行,包括count(列名)。--------------------------------------------------F.7  集合函数(cardinality,collect,powermultiset,powermultiset_by_cardinality,set)说明:10g新增,为了扩展集合类型(嵌套表和VARRAY)的功能,新增的针对集合类型的函数。CARDINALITY语法:CARDINALITY (nested_table)功能:10g新增函数,返回嵌套表的实际元素个数。SQL> select product_id,CARDINALITY(ad_text) from a;--------------------------------------------------COLLECT语法:COLLECT (column)功能:10g新增函数,用于根据输入列和被选中行建立嵌套表结果。SQL> select cast(COLLECT(ad_text) as t) from a;--t是嵌套表--------------------------------------------------POWERMULTISET语法:POWERMULTISET(expr)功能:10g新增函数,用于生成嵌套表的超集(包含所非空的嵌套表)。SQL> select cast(POWERMULTISET (ad_text) as t) from a;--t是嵌套表--------------------------------------------------POWERMULTISET_BY_CARDINALITY语法:POWERMULTISET_BY_CARDINALITY(expr,cardinatility)功能:10g新增函数,用于根据嵌套表和元素个数,生成嵌套表的超集(包含所非空的嵌套表)。SQL> select cast(POWERMULTISET_BY_CARDINALITY(ad_text) as t) from a;--t是嵌套表--------------------------------------------------SET语法:SET(nested_table)功能:改函数用于取消嵌套表中的重复结果,并生成新的嵌套表。SQL> select SET(nested_table) from a;--------------------------------------------------F.8  对象函数(deref,make_ref,ref,reftohex,value)说明:对象函数用于操纵REF对象。REF对象实际是指对象类型数据的指针。DEREF语法:DEREF(expr)功能:该函数用于返回参照对象exp所引用的对象实例。SQL> select DEREF(address).city from table_name;--------------------------------------------------MAKE_REF语法:MAKE_REF(object_table|object_view,key)功能:该函数可以基于对象视图或对象表(存在基于主键的对象标识符)的一行数据建立REF。SQL> select MAKE_REF(oc_inventocies,3003) from dual;--------------------------------------------------REF语法:REF(expr)功能:该函数用于返回对象行所对应的REF值。SQL> select REF(e) from table_name e;--------------------------------------------------REFTOHEX语法:REFTOHEX(expr)功能:该函数用于将REF值转变为十六进制字符串。SQL> select REFTOHEX(REF(e)) from table_name e;--------------------------------------------------VALUE语法:VALUE(expr)功能:该函数用于返回行对象所对应的对象实例数据,其中expr用于指定行对象的别名。SQL> select value(e).city from table_name e;--------------------------------------------------F.9  其他函数(bfilename,coalesce,decode,depth,dump,empty_clob/empty_blob,existsnode,extract,extractvalue,greatest,greatest_lb,least,least_ub,nls_charset_decl_len,nls_charser_id,nls_charser_name,nullif,nvl2,over,path,sys_connect_by_path,sys_context,sys_dburigen,sys_guid,sys_typeid,sys_xmlagg,sys_xmlgen,uid,updatexml,user,userenv,vsize,xmlagg,xmlcolatival,xmlconcat,xmlelement,xmlforest,xmlsequence,xmltransform)说明:除了上述涉及的函数外,Oracle还提供了一些单行函数。BFILENAME语法: BFILENAME(directory,file_name)功能:获得操作系统中与物理文件file_name相关的BFILE位置指示符. Directory是与OS路径相关的DIRECTORY类型对象,file_name是OS文件的名称。使用位置:过程性语言和SQL语句。SQL>insert into file_tb1 values(bfilename('lob_dir1','image1.gif'));--------------------------------------------------COALESCE语法:COALESCE(exp1,exp2,exp3,...)功能:9i新增,依次查找各参数,遇到非NULL则返回,各参数或表达式数据类型必须一致,如果都为null则返回null。Select COALESCE(v_e1,v_e2) from a;--------------------------------------------------DECODE语法:DECODE(base_expr,comparel,valuel,Compare2,value2,…default)功能:把base_expr与后面的每个compare(n)进行比较,如果匹配返回相应的value (n).如果没有发生匹配,则返回default,每个valuel数据类型必须一致,如果没有default则返回null。Select decode(a,’金’,1,’银’,2,0) from table_name;--------------------------------------------------DEPTH语法:DEPTH(n)功能:9i新增,用于返回xml方案under_path路径所对应的相对层数,其中参数n用于指定相对层数。Select fath(1),depth(2) from a;--------------------------------------------------DUMP语法:DUMP(expr[,number_format[,start_position][,length]])功能:获得有关expr的内部表示信息的VARCHAR2类型的数值. number_format指定了按照下面返回数值的基数(base):number_format    结果8                八进制表示10               十进制表示16               十六进制表示17               单字符默认的值是十进制.如果指定了start_position和length,那么返回从start_position开始的长为length的字节.缺省返回全部.数据类型按照下面规定的内部数据类型的编码作为一个数字进行返回.代码    数据类型1       VARCHAR22       NUMBER8       LONG12      DATE23      RAW69      ROWID96      CHAR106     MLSLABELSQL> col global_name for a30SQL> col dump_string for a50SQL> set lin 200SQL> select global_name,dump(global_name,1017,8,5) dump_string from global_name;GLOBAL_NAME                    DUMP_STRINGORACLE.WORLD                   Typ=1 Len=12 CharacterSet=ZHS16GBK: W,O,R,L,D--------------------------------------------------EMPTY_CLOB/EMPTY_BLOB语法:EMPTY_CLOB()     EMPTY_BLOB()功能:获得一个空的LOB提示符(locator) .EMOTY_CLOB返回一个字符指示符,而EMPTY_BLOB返回一个二进制指示符, 用来对大数据类型字段进行初始化操作的函数.使用位置:过程性语言和SQL语句.Select EMPTY_CLOB() from dual;--------------------------------------------------EXISTSNODE语法:EXISTSNODE(XMLType_instance,Xpatgh_string)功能:9i新增,用于确认xml节点路径是否存在,返回0表示不存在,1表示存在。.使用位置:过程性语言和SQL语句.Select EXISTSNODE(value(p),’/purchar/user’) from p;--------------------------------------------------EXTRACT语法:EXTRACT (XMLType_instance,Xpatgh_string)功能:9i新增,用于返回xml节点路径下的相应内容。.使用位置:过程性语言和SQL语句.Select EXTRACT (value(p),’/purchar/user’) from p;--------------------------------------------------EXTRACTVALUE语法:EXTRACTVALUE(XMLType_instance,Xpatgh_string)功能:9i新增,用于返回xml节点路径下的值。.使用位置:过程性语言和SQL语句.Select EXTRACTVALUE(value(p),’/purchar/user’) from p;--------------------------------------------------GREATEST语法:GREATEST(expr1[,expr2]…)功能:计算参数中最大的表达式.所有表达式的比较类型以expr1为准,比较字符的编码大小。使用位置:过程性语言和SQL语句.SQL> select greatest('AA','AB','AC') from dual;GRACSQL> select greatest('啊','安','天') from dual;GR安--------------------------------------------------GREATEST_LB语法:GREATEST_LB(label1[,label2]…)功能:返回标签(label)列表中最大的下界.每个标签必须拥有数据类型MLSLABEL、RAWMLSLABEL或者是一个表因字符串文字.函数只能用于truested oracle库.使用位置:过程性语言和SQL语句.--------------------------------------------------LEAST语法:LEAST(expr1[,expr2]…)功能:计算参数中最小的表达式.所有表达式的比较类型以expr1为准,比较字符的编码大小。使用位置:过程性语言和SQL语句.SQL> select least('啊','安','天') from dual;LE啊--------------------------------------------------LEAST_UB语法:LEAST_UB(label1[,label2]…)功能:与GREATEST_UB函数相似,本函数返回标签列表的最小上界.使用位置:过程性语言和SQL语句.--------------------------------------------------NLS_CHARSET_DECL_LEN语法:NLS_CHARSET_DECL_LEN(byte_count,charset_id)功能:该函数用于返回字节数在特定字符集中占有的字符个数。select NLS_CHARSET_DECL_LEN(200,nls_charset_id(‘zhs16gbkf1xed’)) from dual;--------------------------------------------------NLS_CHARSET_ID语法:NLS_CHARSET_ID(text)功能:该函数用于返回字符集的ID号。select NLS_CHARSET_ID( ‘zhs16gbkf1xed’) from dual;--------------------------------------------------NLS_CHARSET_NAME语法:NLS_CHARSET_NAME(number)功能:该函数用于返回字符集ID号所对应的字符集名。select NLS_CHARSET_NAME(852) from dual;--------------------------------------------------NULLIF语法:NULLIF (expr1, expr2) ->相等返回NULL,不等返回expr1功能:9i新增,用于比较表达式expr1和expr2,相等返回null,否则返回expr1.Select nullif(expr1, expr2) from table_name;--------------------------------------------------NVL语法:NVL (expr1, expr2)功能:用于将NULL转变为实际值,如果expr1是NULL,那么返回expr2,否则返回expr1,expr1、expr2两者必须为同类型或expr2可以隐式转换为expr1,否则会报错。Select nvl(column_name,0) from tbale_name;特别的date可以隐式转换为number,所以下面正确SELECT NVL(to_date('2017-01-01','yyyy-mm-dd')-sysdate,SYSDATE)  FROM dual;--------------------------------------------------NVL2语法:NVL2 (expr1, expr2, expr3)功能:9i新增,expr1不为NULL,返回expr2;expr1为NULL,返回expr3。expr1可以是任意数据类型;expr2与expr3可以是除LONG外的任意数据类型,但需要类型一致或expr3可以隐式转换为expr2。特别的date可以隐式转换为number,所以下面正确SELECT NVL2(to_date('01-jun-2016'),sysdate - to_date('01-jun-2016'),sysdate)  FROM dual;--------------------------------------------------OVER语法:sun/count ( * )          over ( partition by XXX      order by XXX )功能:此函数为分析函数,有别于本文介绍中的其他函数,更详细看本博客“分析函数”专题使用位置:过程性语言和SQL语句sum(sal) over (partition by deptno order by ename)COUNT( * )OVER (PARTITION BY class_idORDER BY ROWNUMROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)COUNT( * )OVER (PARTITION BY e.phoneORDER BY pp.sort, qs.user_id DESC, ROWNUMROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)--------------------------------------------------PATH语法:PATH(correction_integer)功能:9i新增,用于返回特定XML资源所对应的相对路径。Select path(1),depth(2) from table_name;--------------------------------------------------SYS_CONNECT_BY_PATH语法:SYS_CONNECT_BY_PATH(column,char)功能:9i新增(只适用于层次查询),用于返回从根到节点的列值路径。Select lpad(‘ ‘,2*level-1)||sys_connect_by_path(ename,’/’) from table_name start with ename=’scott’ connect by prior empno=mgr;--------------------------------------------------SYS_CONTEXT语法:sys_coniext(‘context’,’attribute’)功能:该函数用于返回应用上下文的特定属性值,获得系统信息,其中context为上下文名,而attribute为应用上下文名,此函数可以得到oracle主机及客户端的信息。SELECT   SYS_CONTEXT ('USERENV', 'TERMINAL') 客户端名称,         SYS_CONTEXT ('USERENV', 'LANGUAGE') 客户端语言,         SYS_CONTEXT ('USERENV', 'SESSIONID') sessionid,         SYS_CONTEXT ('USERENV', 'INSTANCE') instance,         SYS_CONTEXT ('USERENV', 'ENTRYID') entryid,         SYS_CONTEXT ('USERENV', 'ISDBA') isdba,         SYS_CONTEXT ('USERENV', 'NLS_TERRITORY') 地区,         SYS_CONTEXT ('USERENV', 'NLS_CURRENCY') 货币,         SYS_CONTEXT ('USERENV', 'NLS_CALENDAR') nls_calendar,         SYS_CONTEXT ('USERENV', 'NLS_DATE_FORMAT') 时间格式,         SYS_CONTEXT ('USERENV', 'NLS_DATE_LANGUAGE') 时间语言,         SYS_CONTEXT ('USERENV', 'NLS_SORT') nls_sort,         SYS_CONTEXT ('USERENV', 'CURRENT_USER') current_user,         SYS_CONTEXT ('USERENV', 'CURRENT_USERID') current_userid,         SYS_CONTEXT ('USERENV', 'SESSION_USER') session_user,         SYS_CONTEXT ('USERENV', 'SESSION_USERID') session_userid,         SYS_CONTEXT ('USERENV', 'PROXY_USER') proxy_user,         SYS_CONTEXT ('USERENV', 'PROXY_USERID') proxy_userid,         SYS_CONTEXT ('USERENV', 'DB_DOMAIN') db_domain,         SYS_CONTEXT ('USERENV', 'DB_NAME') 数据库名称,         SYS_CONTEXT ('USERENV', 'HOST') 客户端完成名称,         SYS_CONTEXT ('USERENV', 'OS_USER') 客户端用户,         SYS_CONTEXT ('USERENV', 'EXTERNAL_NAME') external_name,         SYS_CONTEXT ('USERENV', 'IP_ADDRESS') 客户端IP地址,         SYS_CONTEXT ('USERENV', 'NETWORK_PROTOCOL') 网络协议,         SYS_CONTEXT ('USERENV', 'BG_JOB_ID') bg_job_id,         SYS_CONTEXT ('USERENV', 'FG_JOB_ID') fg_job_id,         SYS_CONTEXT ('USERENV', 'AUTHENTICATION_TYPE') authentication_type,         SYS_CONTEXT ('USERENV', 'AUTHENTICATION_DATA') authentication_data  FROM   DUAL--------------------------------------------------SYS_DBURIGEN语法:SYS_DBURIGEN(column)功能:9i新增,根据列或属性生产类型为DBUriType的URL。Select SYS_DBURIGEN(ename) from emp;--------------------------------------------------SYS_GUID语法:SYS_GUID()功能:该函数用于生产类型为RAW的16字节的唯一标识符,每次调用该函数都会发生不同的RAW数据。Select SYS_GUID() from emp;--------------------------------------------------SYS_TYPEID语法:SYS_TYPEID(object_type_value)功能:该函数用于返回唯一的类型ID值。Select name, SYS_TYPEID(value(p)) from emp p;--------------------------------------------------SYS_XMLAGG语法:SYS_XMLAGG(expr[,fmt])功能:9i新增,用户汇总所有XML文档,并生成一个XML文档。Select SYS_XMLAGG(sys_xmlgen(ename)) from emp p;--------------------------------------------------SYS_XMLGEN语法:SYS_XMLGEN(expr[,fmt])功能:9i新增,根据数据库表的行和列生成一个XMLType实例。Select sys_xmlgen(ename) from emp p;--------------------------------------------------UID语法:UID功能:获得当前数据库用的惟一标识,标识是一个整数.使用位置:过程性语言和SQL语句.SQL> show userUSER 为"GAO"SQL> select username,user_id from dba_users where user_id=uid;USERNAME                         USER_IDGAO                                   25--------------------------------------------------UPDATEXML语法:UPDATEXML(XMLType_instance,Xpath_string,value_expr)功能:9i新增,用于更新特定XMLType实例相对应节点路径的内容。Update xmltable p set p=updatexml(value(p),’/pruch/user/text()’,’scott’)--------------------------------------------------USER语法:USER功能:取得当前oracle用户的名字,返回的结果是一个VARCHAR2型字符串.使用位置:过程性语言和SQL语句.SQL> select user from  dual;USERGAO--------------------------------------------------USERENV语法:USERENV(option)功能:根据参数option,取得一个有关当前会话信息的VARCHAR2数值.使用位置:过程性语言和SQL语句.返回当前用户环境的信息,opt可以是:ENTRYID,SESSIONID,TERMINAL,ISDBA,LABLE,LANGUAGE,CLIENT_INFO,LANG,VSIZEOPTION='ISDBA'若当前是DBA角色,则为TRUE,否则FALSE.OPTION='LANGUAGE'返回数据库的字符集.OPTION='SESSIONID'为当前会话标识符.OPTION='ENTRYID'返回可审计的会话标识符.OPTION='LANG'返回会话语言名称的ISO简记.OPTION='INSTANCE'返回当前的实例.OPTION='terminal'返回当前计算机名ISDBA  查看当前用户是否是DBA如果是则返回trueSQL> select userenv('isdba') from dual;USERENFALSESESSION返回会话标志SQL> select userenv('sessionid') from dual;USERENV('SESSIONID')                 152ENTRYID返回会话人口标志SQL> select userenv('entryid') from dual;USERENV('ENTRYID')                 0               INSTANCE返回当前INSTANCE的标志SQL> select userenv('instance') from dual;USERENV('INSTANCE')                  1LANGUAGE返回当前环境变量,包括语言、地区、字符集SQL> select userenv('language') from dual;USERENV('LANGUAGE')SIMPLIFIED CHINESE_CHINA.ZHS16GBKLANG返回当前环境的语言的缩写SQL> select userenv('lang') from dual;USERENV('LANG')ZHSTERMINAL返回用户的终端或机器的OS标示符SQL> select userenv('terminal') from dual;USERENV('TERMINAGAOCLIENT_INFO 返回由包DBMS_APPLICATION_INFO所存储的用户会话信息(64字节)Select userenv(‘CLIENT_INFO’) from dual;--------------------------------------------------VSIZE语法:VSIZE(value)功能:获得value的内部表示的字节数.如果value是NULL,结果是NULL.使用位置: SQL语句.SQL> select vsize(user),user from dual;VSIZE(USER) USER          6 SYSTEM--------------------------------------------------XMLAGG语法:XMLAGG(XMLType_instance [order by sort_list])功能:9i新增,用于汇总多个XML块,并生成XML文档。Select xmlagg(xmlelement(“employee”,ename||’ ’||sal)) from emp where deptno=10;--------------------------------------------------XMLCOLATTVAL语法:XMLCOLATTVAL(value_expr1[,value_expr2]…)功能:9i新增,用于生成XML块,并增加”column”作为属性名。Select xmlelement(“emp”, XMLCOLATTVAL(ename,sall)) from emp where deptno=10;--------------------------------------------------XMLCONCAT语法:XMLCONCAT(XMLType_instance1[,XMLType_instance2]…)功能:9i新增,用于连接多个XMLType实例,并生成一个新的XMLType实例。Select XMLCONCAT(xmlelement(‘ename’,ename), xmlelement(‘sal’,sal)) from emp where deptno=10;--------------------------------------------------XMLELEMENT语法:XMLELEMENT(identifier[,xml_attribute_clause][,value_expr])功能:9i新增,用于返回XMLType实例,其中参数identifier指定元素名,参数xml_attribute_clause指定元素属性子句,参数value_expr指定元素值。Select XMLELEMENT(‘date’,sysdate) from dual;Select XMLELEMENT(“emp”,xmlattributes(empno as “id”,ename)) from emp;--------------------------------------------------XMLFOREST语法:XMLPOREST(value_expr1[,value_expr2]…)功能:9i新增,用于返回XML块。Select xmlelement(‘ename’, XMLPOREST[ename,sal]) from emp where deptno=10;--------------------------------------------------XMLSEQUENCE语法:XMLSEQUENCE(xmltype_instance)功能:9i新增,用于返回xmltype实例中顶级节点一下的varray元素。Select XMLSEQUENCE(extract(value(x),’/purorder/line/*’)) from emp p where deptno=10;--------------------------------------------------XMLTRANSFORM语法:XMLTRANSFORM(xmltype_instance,xsl_ss)功能:9i新增,用于将xmltype实例按照XSL样式进行转换,并生成新的xmltype实例。Select XMLTRANSFORM(w.warehouse_spec,x.coll).getclobval from warehouse w,xsl_tab x where w.name=x.name;--------------------------------------------------
  • [问题求助] 请问大家像这种实例搜索不到是怎么回事啊
    创建了数据库但是DLV数据连接搜索不到
  • [技术干货] Oracle SQL优化器
    1.优化器定义:优化器(optimizer)是oracle数据库内置的一个核心子系统。优化器的目的是按照一定的判断原则来得到它认为的目标SQL在当前的情形下的最高效的执行路径,也就是为了得到目标SQL的最佳执行计划。依据所选择执行计划时所用的判断原则,oracle数据库里的优化器又分为RBO(基于原则的优化器)和CBO(基于成本的优化器,SQL的成本根据统计信息算出)两种。ORACLE数据库SQL语句的执行过程:分类:Oracle数据库中的优化器分为RBO和CBORBO:基于规则的优化器RBO是一种适合于OLTP类型SQL语句的优化器。相对于CBO而言,RBO有着先天的缺陷,一旦SQL语句的执行计划出现问题,将很难调整。RBO最大的缺点是以oracle内置代码的规则作为判断标准,而并没有考虑到实际目标表的数据量以及数据分布情况。CBO:基于成本的优化器CBO优化器根据SQL语句生成一组可能被使用的执行计划,估算出每个执行计划的代价,并调用计划生成器(Plan Generator)生成执行计划,比较执行计划的代价,最终选择选择一个代价最小的执行计划。查询优化器由查询转换器(Query Transform)、代价估算器(Estimator)和计划生成器(Plan Generator)组成。oracle在未引入系统统计信息之前,CBO所计算的成本值实际全是基于IO计算的。比较:CBO优于RBO是因为RBO是一种呆板、过时的优化器,它只认规则,对数据不敏感。毕竟规则是死的,数据是变化的,这样生成的执行计划往往是不可靠的,不是最优的。2.CBO优化器的基本概念2.1 集的势(cardinality)Cardinality是CBO特有的概念,指集合所包含的记录数,即结果集行数。Cardinality实际上表示对目标SQL某个具体执行步骤的执行结果所包含的记录数的估算,当然,如果针对整个目标SQL,那么此时的cardinality就表示对该SQL最终执行结果所包含的记录数的估算。Cardinality和成本值得估算息息相关,因为oracle得到的制定结果集所需要消耗的IO资源可以近似的看成随着结果集所包含的记录数递增而递增。所以,SQL编写的一个原则就是“尽早的过滤更多的数据”。2.2 可选择率(Selectivity)Selectivity也是CBO特有的概念,它是指“施加指定谓语条件后返回的结果集的记录数占未施加任何谓语条件的原始结果集的记录数的比率”,取值范围为0~1,其值越小,代表可选择性越好。Selectivity也可成本值得估算息息相关,可选择率越大,意味着所返回的结果集的cardinality越大,所以估算的成本就越大。实际上CBO就是利用selectivity来计算对应结果集的cardinality的,即:Computed cardinality=original*selectivityCardinility和selectivity的值会直接影响CBO对于相关执行步骤成本的估算,进而影响CBO对于目标SQL的执行计划的选择。可用以下公式老表示:Selectivity=施加指定谓语条件后返回的结果集的记录数/未施加任何谓语条件的原始结果集的记录数Selectivity = 1/num_distinct2.3 可传递性可传递性也是CBO的特有属性,它是查询转换中所做的第一件事情,其含义是CBO会对目标SQL做等价改写,进而提供更多的执行路径给目标CBO,增加得到最佳执行计划的可能性。RBO不会对目标SQL做等价改写。Oracle里可传递性分为以下3种情况:1)简单谓语传递比如原目标SQL中的谓语条件是“t1.c1=t2.c1 and t1.c1=10”,则CBO可能会给谓语条件额外加上“t2.c1=10”。2)连接谓语传递比如原目标SQL中的谓语条件是“t1.c1=t2.c1 and t2.c1=t3.c1”,则CBO可能会给谓语条件额外加上“t1.c1=t3.c1”。3)外链接谓语传递比如原目标SQL中的谓语条件是“t1.c1=t2.c1(+) and t1.c1=10”,则CBO可能会给谓语条件额外加上“t2.c1(+)=10”。2.4 CBO的局限性1)CBO会默认目标SQL语句where条件中出现的各个列之间出现是独立的,没有任何关联。并且CBO会根据这个前提条件来计算selectivity和cardinality,进而估算成本并选择执行计划。2)CBO会假设所有的目标SQL都是独立运行的,并且互不干扰,但实际情况却不完全是这样。3)CBO在解析多表关联的目标SQL时,可能会漏选正确的执行计划。3.优化器基础知识3.1 优化器的模式优化器模式用于决定oracle在解析目标SQL时所选择的优化器类型,以及选择使用CBO时计算成本的侧重点。在oracle数据库中,优化器模式由参数OPTIMIZER_MODE的值决定,通常OPTIMIZER_MODE的值为RULE,CHOOSE,FIRST_ROWS_n(N=1、10、100、1000),FIRST_ROWS或ALL_ROWS。OPTIMIZER_MODE的值得各个含义如下:1)RULE:RULE表示优化器使用RBO来解析目标SQL,此时目标SQL所涉及的各个对象的统计信息对于RBO来说将毫无意义。2)CHOOSE:CHOOSE是oracle 9i中OPTIMIZER_MODE的默认值,他表示oracle在解析目标SQL时到底使用CBO还是RBO取决于目标SQL所涉及对象是否有统计信息。3)FIRST_ROWS_n(N=1、10、100、1000)4)FIRST_ROWS:FIRST_ROWS是一个在oracle 9i中就过时的一个参数,他表示oracle在解析目标SQL时会联合使用CBO和RBO。在大部分情况下,oracle还是会选用CBO作为解析目标SQL,此时oracle的侧重点是以最快的相应速度返回前n行。在一些特俗情况下,oracle会选用RBO来解析目标SQL而不考虑成本。5)ALL_ROWS:ALL_ROWS是oracle 10g及以后oracle的版本中OPTIMIZER_MODE的默认值,它表示oracle会使用CBO来解析目标SQL,此时CBO计算目标SQL的各个执行路径的成本的侧重点是最佳吞吐量。当OPTIMIZER_MODE为FIRST_ROWS时,CBO计算成本侧重于最快响应时间;当OPTIMIZER_MODE为ALL_ROWS时,CBO计算成本侧重于最佳吞吐量。3.2 结果集(row source)结果集是指包含指定执行结果的集合。对于优化器而言,结果集对应SQL执行计划的执行步骤。执行计划的各个步骤的输出结果集和输入结果集可以通过执行计划分析出。3.3 访问数据的方法优化器访问数据的方法有3种,一种是直接访问表;一种是访问索引,直接从索引中取值;另一种是先访问索引,再回表。3.3.1 访问表的方法访问表的方法2种:全表扫描;rowid扫描。(1)全表扫描全表扫面会从表所占用的第一个extent的第一个块开始扫描,一直到该表的最高水位线为止,这个范围的所有的数据块,oracle都会读到。(2)rowid访问Rowid访问是指oracle在访问目标表的数据时,直接通过数据所在的rowid去定位并访问这些数据。Oracle中rowid访问有两层含义:一是根据用户SQL输入的rowid值直接去读取数据记录;另一种是先去访问相关索引,然后根据访问索引所返回的rowid回表去读取具体的记录数。对于oracle数据库中的堆表,可以通过oracle内置的rowid伪列得到对应的rowid值,然后我们可以通过dbms_rowid包中的相关方法(dbms_rowid.rowid_relative_fno:文件号;dbms_rowid.rowid_block_number:数据块号;dbms_rowid.rowid_row_number:数据块中行号)通过上述取得的rowid值取得数据行的实际物理位置。例:SQL> select empno,ename,rowid, dbms_rowid.rowid_relative_fno(rowid)||'_'||dbms_rowid.rowid_block_number(rowid)||'_'||dbms_rowid.rowid_row_number(rowid) localtion     from emp;    EMPNO    ENAME       ROWID                        LOCALTION---------- ------------------------------ -------------------------------------------------- ----------------------------########## SMITH     AAAMfMAAEAAAAAgAAA             4_32_0..............########## MILLER    AAAMfMAAEAAAAAgAAN             4_32_1314 rows selected.3.3.2 访问索引的方法使用B-TREE索引的优势有以下几点:a、所有的叶子快都在同一层,即所有的叶子块距离根节点的距离是相同的。b、Oracle保证所有的B-tree索引都是自平衡的,即不可能出现不同的索引叶子块不处于同一层的情况。c、通过B-tree索引访问数据不会因为表数据量的增加效率明显降低,这也是走索引与全表扫描最大的区别。(1)索引唯一性扫描:索引唯一性扫描(index unique scan)是针对唯一性索引(unique index)的扫描。它仅仅适用于where条件是等值查询的目标SQL。(2)索引范围扫描:索引范围扫描(index range scan)适用于所有的B-tree索引,当扫描的对象是唯一性索引时,此时目标SQL的where条件一定是范围条件,当扫描的对象是非唯一性索引时,此时目标SQL的where条件没有限制。(3)索引全扫描:索引全扫描(index full scan)适用于所有类型的B-tree索引(唯一性索引和非唯一性索引),所谓索引全扫描是指要扫描索引的所有叶子块的所有行。(4)索引快速扫描:索引快速扫描(index fast full scan)适用于所有类型的B-tree索引(唯一性索引和非唯一性索引),索引快速扫描要扫描索引的所有叶子块的所有行。索引快速扫描与索引全扫描有以下区别:#索引快速扫描仅仅适用于CBO。#索引快速扫描可以使用多块读,同时也可以并行执行。#索引快速扫描的结果不一定是有序的。(5)索引跳跃式扫描索引跳跃式扫描(index skip scan)适用于所有复合类型的B-tree索引(包括唯一性索引与非唯一性索引)。3.3.3 表连接通常情况下,我们可以人为Oracle的表连接分为内连接和外连接两种连接方式。3.3.3.1 表连接类型(a)内连接(inner join)内连接是指表连接的连接结果只包含那些完全满足连接条件的记录。对于SQL而言,只要其where条件中没有定义那些外连接关键字(如left outer join、right outer join、full outer join)外的所有连接类型定义为内连接。(b)外连接(outer join)外连接是对内连接的一种扩展,它是指表连接的结果除了包括那些满足条件的连接结果外还包含驱动表中不满足连接条件的结果。标准SQL的外连接分为左连接(left outer join)、右连接(righr outer join)、全连接(full outer join)三种。全连接可以近似看成是先做左连接union再做右连接,这里所说的近似是因为oracle实际的处理并不是这样做的,因为union要对结果集进行排序,而全连接并不需要排序。3.3.3.2 表连接方法优化器在解析含有表连接的目标SQL时,当根据目标SQL的SQL文本决定了表的连接类型后,接下来还要决定表的连接方法。Oracle中两表的连接方法有“排序合并”,“嵌套循环”,“哈希连接”,“笛卡尔连接”四种。(a)排序合并连接(sort merge join)排序合并连接是一种两表做表连接时用排序操作(sort)和合并操作(merge)来得到表连接结果的方法。排序合并连接的适用场景总结入下:b.1)通常情况下,排序合并连接的执行效率不如哈希连接,但是前者使用范围广,因为hash连接只适用于等值连接,而排序合并还适用于其他连接(比如<、>、<=、>=)。b.2)通常情况下,排序合并不适合OLTP类型连接,本质是排序对于OLTP类型来说成本是昂贵的。但是,如果能避免排序,也是可以适合于OLTP系统的。(b)嵌套循环连接(nested loops join)嵌套循环连接是两个表在做连接时采用两层嵌套循环(外层循环和内层循环)的方式来得到表连接结果的方法。嵌套循环连接的适用场景总结入下:b.1)如果驱动表所对应的驱动结果集的记录较少,同时被驱动表的连接列上又存在唯一性索引(或者被驱动表的连接列上存在选择性较好的非唯一性索引),那么此时使用嵌套循环的执行效率会非常高。如果驱动表所对应的驱动结果集数量较多,那么即使被驱动表的连接列上存在唯一性索引,那么执行效率也不会很高。b.2)只要驱动结果集的记录较少,那么就具备了做嵌套循环的前提条件,而驱动表所对应的驱动结果集是在对驱动表应用了目标SQL之后所得到的结果集,所以大表也会可以作为驱动表的。b.3)嵌套循环可以实现快速响应。嵌套循环在访问被驱动表时,如果被驱动表有索引,将会采用单块读的方式访问索引,同时,如果返回结果集列不在索引中取得,嵌套循环连接也要采用单块读的方式回表。(c)hash连接(hash join)Hash连接是指两表在做连接时主要依靠哈希算法来取得结果集的方法hash连接的适用场景总结入下:b.1)哈希连接不一定需要排序,大部分情况下。b.2)哈希连接的驱动表所对应的选择列可选择性应尽可能好,因为这个可选择性会影响到hash bucket的记录数,而hash bucket的记录数又会直接影响从该hash backet中查找匹配记录数的效率。b.3)哈希连接只适用于CBO,同时哈希连接也只适用于等值连接。b.4)哈希连接适合于小表和大表做表连接且连接结果集记录数较多的情形,特别是在小表的连接列可选择性特别好的情况下,这时hash连接的执行时间近似于对大表的执行时间。b.5)当两表做hash连接时,如果在施加了目标SQL指定的谓语条件后所得到的数量较小的那个结果集所对应的hash table能够完全被容纳在内存中(pga),此时hash连接执行效率会非常高。(d)笛卡尔连接(cross join)笛卡尔连接又称笛卡尔积(cartesian product),它是一种两表在做连接时没有任何连接条件的表连接方法。笛卡尔连接总结如下:b.1)笛卡尔连接出现一般都是目标SQL中漏写了连接条件,所以笛卡尔连接一般都是不好的,除非刻意这样做(比如有时利用笛卡尔积来减少对目标SQL中大表的全表扫描次数)。b.2)有时候笛卡尔连接的出现时因为目标SQL中使用了ordered hint,同时该SQL中文本委会相邻的两个表又没有直接的连接条件。b.3)有时候笛卡尔连接出现时因为统计信息不准。————————————————版权声明:本文为CSDN博主「Cpanling」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。原文链接:https://blog.csdn.net/weixin_50742675/article/details/128249825
  • [技术干货] Oracle 高阶函数
    1. Ratio_to_report语法:RATIO_TO_REPORT ( ratio_expression ) OVER ( [ PARTITION BY partition_expression ] )用途:ratio_to_report是一个分析函数,用来计算一个值在一个数据集中所占的比例。如果expr为null,则整个函数的值也会为null。这个数据由query_patition_clause决定,如果不写这个子句,这个函数会计算查询后返回的所有行。根据over窗口函数的作用区间,求出作用区间中的单个值在整个区间的总值的比重。ratio_to_report()括号中是分子,over()括号中就是分母,分母缺失就是整个占比。注意    expr表达式不能进行ratio_to_report函数的嵌套,但是可以使用其他的函数;    当sal的值乘以一个常数值的时候并不影响计算结果,计算结果与sal没有乘100的计算结果一致。    select ename,sal,depno,ratio_to_report(sal * 100) over(partition by deptno)          from empover()中为空值时,表示占所有的百分比。例子    --求每个月销售额占比         select area,month,sum(amount) amount,                round(ratio_to_report(sum(amount)) over(partition by area),4) * 100 ||'%' area_pct         from testa         group by area, month;    --查询每个员工占所在部门的工资比例         select e.department_id,e.employee_id,e.salary,sum(e.salary) over(partition by e.department_id) sum_salary,           round(ratio_to_report(e.salary) over(partition by e.department_id),4) * 100 || '%' ratio_salary         from employees e;2. Coalsece语法:Coalesce(expr1,expr2,...,exprn)含义:coaleace返回参数列表中第一个非空表达式,必须指定最少两个参数,如果所有的参数都是null,则返回null.oracle使用短路运算,它依次对每个表达式求值判断是否为空值,不是对所有表达式都求职后灾进行判断第一个非空值。coalesce函数是nvl函数的扩展。可以使用coalesce函数替换一个case语句,例;    Coalesce(expr1,expr2);         --等价于         Case when expr1 is not null         Then expr1         Else         Expr2         End;注意:空值加任何值都是空值,空值乘以任何值都是空值,以此类推;         所有表达式必须是相同类型的,或者可以隐性转换为相同的数据类型空值相关的函数NvlNvl(commission_pct,0)如果第一个参数为null,则返回第二个参数,如果第一个参数为非null,则返回第一个参数。    --示例:计算年薪(工资+提成)    Select last_name,salary,nvl(commission_pct,0),    (salary * 12) + (salary * 12 *nvl(commission_pct,0)) annual_salary    From employees    Where last_name=’Maltos’;Nvl2Nvl2()commission_pct,’sal_comm’,’sal’)如果第一个参数为null,则返回第三个参数,如果第一个参数为非null,则返回第二个参数    --示例:         Select last_name,salary,commission_pct,         Nvl2(commission_pct,’sal+comm’,’sal’) income         From employees         Where department_id in(50,80);NullifNullif(length(firsy_name),length(last_name))如果两个参数值相等,则返回null,如果两个参数不等,则返回第一个表达式值    --示例:         SELECT FIRST_NAME ,LENGTH(FIRST_NAME) "EXPR1",                LAST_NAME, LENGTH(LAST_NAME) "expr2",           nullif(length(first_name),length(last_name)) result         FROM EMPLOYEES;If-THEN-ELSE------条件表达式CASE语句(SQL标准,编写复杂)    --示例:         SELECT  LAST_NAME,JOB_ID,SALARY,                CASE JOB_ID         WHEN 'IT_PROG'         THEN 1.10*SALARY                            WHEN 'ST_CLERK'         THEN 1.15*SALARY                          WHEN 'SA_REP'         THEN 1.20*SALARY            ELSE SALARY END "REVISED SALARY"         FROM EMPLOYEES;decode函数DECODE(COL|EXPRESSION,SERACH1,RESULT1[SEARCH2,RESULT1,...,][,DEFAULT])如果 search1 = expression 则返回result1,如果 search2 = expression 则返回result2DECODE函数的作用:它可以将输入数值与函数中的参数列表相比较,根据输入值返回一个对应值。函数的参数列表是由若干数值及其对应结果值组成的若干序偶形式。当然,如果未能与任何一个实参序偶匹配成功,则函数也有默认的返回值。区别于SQL的其它函数,DECODE函数还能识别和操作空值。————————————————版权声明:本文为CSDN博主「Cpanling」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。原文链接:https://blog.csdn.net/weixin_50742675/article/details/126242292
  • [SQL] Spring 事务失效的常见原因及解决方案
    Spring 事务失效的常见原因1.数据库引擎不支持事务:某些数据库引擎(如MyISAM)不支持事务,如果使用这些引擎,则无法使用Spring事务。 2.事务传播机制设置不正确:事务传播机制(Propagation)是指当一个事务方法调用另一个事务方法时,如何处理事务的传播。如果事务传播机制设置不正确,可能会导致事务失效。 3.未捕获异常:当一个未捕获的异常被抛出时,Spring默认会回滚事务,但是如果在方法中捕获了异常并且没有抛出,那么事务就不会回滚。 4.没有使用代理对象:Spring的事务管理是通过代理实现的,如果在调用事务方法时没有使用代理对象,那么事务就不会生效。 5.同一类中的事务方法之间相互调用:当同一个类中的方法相互调用时,Spring默认不会使用代理对象,因此事务也不会生效。 6.数据库隔离级别不正确:数据库的隔离级别会影响事务的提交和回滚。如果数据库隔离级别设置不正确,可能会导致事务失效。常见事务失效的解决方案确认数据库引擎是否支持事务。确认事务传播机制是否设置正确。确认方法中是否捕获异常并处理。确认是否使用了代理对象来调用事务方法。确认是否在同一个类中相互调用事务方法。确认数据库隔离级别是否正确。