Oracle实际需要用到但常常被忽略的函数

1、Oracle中nvl()与nvl2()函数

函数nvl(expression1,expression2)根据参数1是否为null返回参数1或参数2的值；
函数nvl2(expression1,expression2,expression3)根据参数1是否为null返回参数2或参数3的值

【函数格式】：nvl(expression1,expression2)

若expression1值为null，则该函数返回expression2；
若expression1值不为null，则该函数返回expression1；
若expression1、expression2的值均为null，则该函数返回null。
【特别说明】：参数expression1和expression2可以是字符型、数字型或日期型，但参数expression1与expression2的类型必须一致

【函数格式】：nvl2(expression1,expression2,expression3)

若expression1值不为null，则该函数返回expression2值；
若expression1值为null，则该函数返回expression3值；
若expression1、expression2、expression3值均为null，则该函数返回null。
【特别说明】：
expression1的类型不需要与expression2和expression3的类型保持一致；
expression2与expression3的类型尽量保持一致，若不一致，当expression1为null时，则expression3会自动转换为expression2的类型，若两个数据类型之间无法转换，则会报错。

2、ALTER SESSION ENABLE PARALLEL DML

execute immediate ‘alter session enable parallel dml’; --修改会话并行DML，启用并行DML

并行处理，一般为CPU的倍数如：4，8等,在执行类型SQL必须先运行:alter session enable parallel dml

3、DBMS_STATS.GATHER_TABLE_STATS详解

作用:DBMS_STATS.GATHER_TABLE_STATS统计表,列,索引的统计信息（默认参数下是对表进行直方图信息收集，包含该表的自身-表的行数、数据块数、行长等信息；列的分析–列值的重复数、列上的空值、数据在列上的分布情况；索引的分析-索引页块的数量、索引的深度、索引聚合因子）

DBMS_STATS.GATHER_TABLE_STATS的语法如下:

DBMS_STATS.GATHER_TABLE_STATS ( ownname VARCHAR2,
tabname VARCHAR2, partname VARCHAR2,
estimate_percent NUMBER, block_sample BOOLEAN, method_opt
VARCHAR2, degree NUMBER, granularity VARCHAR2,
cascade BOOLEAN, stattab VARCHAR2, statid
VARCHAR2, statown VARCHAR2, no_invalidate BOOLEAN,
force BOOLEAN);

参数说明:

ownname:要分析表的拥有者

tabname:要分析的表名.

partname:分区的名字,只对分区表或分区索引有用.

estimate_percent:采样行的百分比,取值范围[0.000001,100],null为全部分析,不采样. 常量:DBMS_STATS.AUTO_SAMPLE_SIZE是默认值,由oracle决定最佳取采样值.

block_sapmple:是否用块采样代替行采样.

method_opt:决定histograms信息是怎样被统计的.method_opt的取值如下(默认值为FOR ALL COLUMNS SIZE AUTO):

for all columns:统计所有列的histograms.

for all indexed columns:统计所有indexed列的histograms.

for all hidden columns:统计你看不到列的histograms

for columns SIZE | REPEAT | AUTO | SKEWONLY:统计指定列的histograms.N的取值范围[1,254]; REPEAT上次统计过的histograms;AUTO由oracle决定N的大小;SKEWONLY multiple end-points with the same value which is what we define by "there is skew in thedata

degree:决定并行度.默认值为null.

granularity:Granularity of statistics to collect ,only pertinent if the table is partitioned.

cascade:是收集索引的信息.默认为FALSE.

stattab:指定要存储统计信息的表,statid如果多个表的统计信息存储在同一个stattab中用于进行区分.statown存储统计信息表的拥有者.以上三个参数若不指定,统计信息会直接更新到数据字典.

no_invalidate: Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE.

force:即使表锁住了也收集统计信息.

例子:

execute dbms_stats.gather_table_stats(ownname => 'owner',tabname => 'table_name' ,estimate_percent => null ,method_opt => 'for all indexed columns' ,cascade => true);

总结：
在Oracle数据库中，DBMS_STATS包是用于收集和优化数据库对象统计信息的工具。其中，GATHER_TABLE_STATS过程是用来收集指定表的统计信息。这些统计信息对于Oracle优化器来说至关重要，因为它们帮助优化器选择最优的执行计划来执行SQL语句。

实际案例：

DBMS_STATS.GATHER_TABLE_STATS('YPREPORT','T_F_CPMAIN_T90',CASCADE=>TRUE,NO_INVALIDATE=>FALSE,DEGREE=>8);

含义：
'YPREPORT'：这是表所在的模式（Schema）名称。在Oracle中，模式是一组数据库对象的集合，包括表、视图、索引等。这里是用户名
'T_F_CPMAIN_T90'：这是要收集统计信息的表名。
CASCADE=>TRUE：这个参数指定是否同时收集该表相关索引和列的统计信息。当设置为TRUE时，除了收集表的统计信息外，还会收集所有相关索引和列的统计信息。这对于确保优化器拥有完整的信息来制定执行计划非常重要。
NO_INVALIDATE=>FALSE：这个参数决定了在收集统计信息后是否使依赖于此表的SQL语句的执行计划失效。当设置为FALSE时（默认值），收集统计信息后会使依赖于此表的SQL语句的执行计划失效，从而迫使优化器在下次执行这些SQL语句时重新生成执行计划。这有助于确保SQL语句能够利用最新的统计信息来执行。

4、GREATEST函数

GREATEST函数取最大值
如 SELECT GREATEST(1, 5, 2, 4, 3) FROM DUAL;返回最大值5
需要注意的是，GREATEST函数只能用于比较数值、日期或字符类型的值，并且所有参数的类型必须相同。如果传递的参数类型不同，Oracle将尝试隐式转换类型，但是可能会产生错误结果。

5、COALESCE 函数

语法：COALESCE(expr1,expr2,…,exprN)
其中：expr1、expr2、…、exprN 是要检查的表达式，可以是列、函数或常量。
COALESCE函数返回第一个非空表达式。如果所有的表达式都是空值，则结果为空值。
用法
解决方案1：返回非空的列

SELECT COALESCE(column1, column2, column3) 
FROM table;

在这个例子中，COALESCE函数返回column1，如果column1是空值，则返回column2，如果column1和column2都是空值，则返回column3。

解决方案2：返回非空的表达式

SELECT COALESCE(column1 + 10, column2, column3) 
FROM table;

在这个例子中，COALESCE函数返回column1 + 10，如果column1 + 10是空值，则返回column2，如果column1 + 10和column2都是空值，则返回column3。