SQL:Group Functions

不可否认SQL语句的功能非常强大,但是,在大多数的时候,我们还是会在使用SQL语句的过程中,调用数据库内嵌的函数,通过函数来强化我们SQL语句的功能,以达到我们预期的目的,尤其是在满足一些应用程序对统计报表的需求情况下。

首先,简单说说单行函数(Single-Row Functions)。在Oracle数据库里,单行函数大概可以细分为Character functionNumber functionDate function,Conversion function,General function。其实,Oracle数据库支持的函数足够多了,在这里不一一列举具体用法和使用规则了,我们应该清晰的一个概念就是:单行函数可以多次嵌套调用。

另外,大概说一下分组函数(Group Functions)。所谓分组函数跟单行函数的区别就是:单行函数是对数据源中的记录逐条分别作为函数的输入参数来处理,最终得到函数处理的结果。而分组函数就是把数据源中的记录按照某种特定的需求以分组的形式来作为函数的输入参数处理,最终得到处理的结果自然就是以组的形式区分开来。

来简单看一下,使用分组函数的语法规则:

SELECT    column, group_function(column)
FROM      table
[WHERE    condition]
[GROUP BY group_by_expression]
[ORDER BY column];

我们在使用分组函数的时候需要注意的几个地方就是:

①All columns in the SELECT list that are not in group functions must be in the GROUP BY clause.凡是出现在查询列表中的没有应用分组函数的字段必须要出现在GROUP BY子句中,否则报错。

②The GROUP BY column does not have to be in the SELECT list.反之,出现在GROUP BY从句中的字段并非一定要出现在查询列表中。并且,GROUP BY从句中可以出现排序列表,即可以对多列进行分组排序。

③You cannot use the WHERE clause to restrict groups.You cannot use group functions in the WHERE clause.You use the HAVING clause to restrict groups.我们不能在WHERE从句中对分组做限制,也不能在WHERE从句使用组函数,而应该通过在HAVING子句中使用分组函数来实现这一目的。

④ORDER BY从句同样可以使用分组函数,而且该分组函数同查询列表中使用的分组函数以及HAVING从句中的三类分组函数,均可以不同。

⑤分组函数只能嵌套调用一次。

10g utilities:How to use LogMiner II

在上一篇日志里:我们了解了怎么使用LogMiner工具的第一种使用方法。接着,我们来学习下LogMiner工具的第二种使用方式:
在使用LogMiner之前,我们要确定相关的参数,也就是简单配置一下LogMiner工具使用的“环境”
①配置一个用于将来存放LogMiner分析日志文件(online redo logs,archived logs)的结果路径和文件,这个路径和文件是映射在文件系统上的。这个文件可以认为是一个字典文件,但是这个字典文件跟数据库的数据字典文件完全是两码子事儿。对应的初始化参数是utl_file_dir,默认情况下,该参数没有配置。
②在调用LogMiner工具之前,在文件系统上生成这个字典文件。
③接下来调用LogMiner工具的方法跟第一种方法就完全类似了。
接下来,进入正式调用流程:
I 首先:以SYS用户登录数据库,检查utl_file_dir初始化参数是否设置,或者设置是否正确,捎带看下数据库版本,以及操作系统版本:

SQL> show user;
USER is "SYS"
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> !uname -a
Linux Oracle10g 2.6.9-55.ELsmp #1 SMP Wed May 2 14:28:44 EDT 2007 i686 i686 i386 GNU/Linux

确认初始化参数文件,utl_file_dir,并修改之,重启数据库使之生效,因为该参数不能直接动态修改:

SQL> show parameter utl_file_dir
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir                         string
SQL> !mkdir -p /u01/app/logmnr
SQL> alter system set utl_file_dir='/u01/app/logmnr/' scope=spfile;
System altered.
SQL> startup force;
ORACLE instance started.
Total System Global Area  343932928 bytes
Fixed Size                  1219304 bytes
Variable Size             125830424 bytes
Database Buffers          209715200 bytes
Redo Buffers                7168000 bytes
Database mounted.
Database opened.
SQL> show parameter utl_file_dir
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir                         string      /u01/app/logmnr/

II 好了,前期准备工作就绪。其次,开始调用dbms_logmnr_d函数包在操作系统生生成”字典文件”:

SQL> exec dbms_logmnr_d.build(dictionary_location=>'/u01/app/logmnr/',
dictionary_filename=>'dictionary.ora');
PL/SQL procedure successfully completed.
SQL>

至此,可以到操作系统上对应的路径上去瞅两眼,验证一下:

SQL> !
[oracle@Oracle10g ~]$ cd /u01/app/logmnr/ && ll
total 23724
-rw-r--r--  1 oracle oinstall 24257361 May 24 23:52 dictionary.ora
[oracle@Oracle10g logmnr]$

看到,达到预期目的。
III 然后,查看数据库当前日志文件情况,并开始调用LogMiner工具:

[oracle@Oracle10g logmnr]$ exit
exit
SQL> set linesize 120
SQL> select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1          8   52428800          1 YES INACTIVE                738316 24-MAY-10
         2          1          7   52428800          1 YES INACTIVE                718138 24-MAY-10
         3          1          9   52428800          1 NO  CURRENT                 758625 24-MAY-10
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1          8   52428800          1 YES INACTIVE                738316 24-MAY-10
         2          1         10   52428800          1 NO  CURRENT                 759039 24-MAY-10
         3          1          9   52428800          1 YES ACTIVE                  758625 24-MAY-10
SQL> col member for a50
SQL> select * from v$logfile;
    GROUP# STATUS  TYPE    MEMBER                                             IS_
---------- ------- ------- -------------------------------------------------- ---
         3         ONLINE  /u01/app/oradata/helen/redo03.log                  NO
         2         ONLINE  /u01/app/oradata/helen/redo02.log                  NO
         1 STALE   ONLINE  /u01/app/oradata/helen/redo01.log                  NO
SQL>

上述操作中,发现当前日志组为第三组,强制日志切换到第二组,并验证日志成员为/u01/app/oradata/helen/redo02.log,这个信息时尤为重要的,因为在下面的操作中,我们要在数据库里另起一个会话,做DML操作,数据库会把日志记录到该日志文件里。这些信息供我们将来分析用。
IV 新开会话,做DML操作,产生日志信息,添加当前日志文件,并开始调用LogMiner分析日志文件:
新开回话:

SQL> show user;
USER is "HR"
SQL> select * from test2;
        ID
----------
         1
         2
SQL> delete from test2 where id=2;
1 row deleted.
SQL> insert into test2 values(3);
1 row created.
SQL>

返回原来的会话,执行下述操作:

SQL> exec dbms_logmnr.add_logfile
(logfilename=>'/u01/app/oradata/helen/redo02.log',
options=>dbms_logmnr.removefile);
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.add_logfile
(logfilename=>'/u01/app/oradata/helen/redo02.log',
options=>dbms_logmnr.new);
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.start_logmnr
(dictfilename=>'/u01/app/logmnr/dictionary.ora');
PL/SQL procedure successfully completed.
SQL> set linesize 160
SQL> col sql_redo for a40
SQL> col sql_undo for a40
SQL> col operation for a10
SQL> col table_name for a5
SQL> select table_name,sql_redo,sql_undo,operation from v$logmnr_contents where username='HR';

TABLE SQL_REDO                                 SQL_UNDO                                 OPERATION
----- ---------------------------------------- ---------------------------------------- ----------
      set transaction read write;                                                       START
TEST2 delete from "HR"."TEST2" where "ID" = '2 insert into "HR"."TEST2"("ID") values (' DELETE
      ' and ROWID = 'AAANB9AAEAAAAGHAAB';      2');

TEST2 insert into "HR"."TEST2"("ID") values (' delete from "HR"."TEST2" where "ID" = '3 INSERT
      3');                                     ' and ROWID = 'AAANB9AAEAAAAGFAAA';

SQL>

至此,达到最终目的。

How to use order by clause ?

在我们写SQL语句的时候,会经常用到order by子句来对查询结果进行排序。在这里对order by子句的使用,作一个简单的总结:

1 SQL语句中,order by从句永远并且一定是放在所有从句的最后位置处,也就是说,一个复杂的SQL语句中,可能会包含诸如group by子句,Where 子句等等。但是,不管有多少个从句跟在FROM关键字后面,order by从句一定是出现在所有子句的最后端,因为order by是对所有查询结果集的排序。如:

SQL> SHOW USER;
USER is "HR"
SQL> SELECT employee_id,last_name
  2  FROM employees
  3  WHERE employee_id>200
  4  ORDER BY employee_id
  5  ;
EMPLOYEE_ID LAST_NAME
----------- -------------------------
        201 Hartstein
        202 Fay
        203 Mavris
        204 Baer
        205 Higgins
        206 Gietz
6 rows selected.
SQL>

可以看到:ORDER BY 子句是出现在整个SQL语句的最后位置处。否则,ORDER BY子句不放在该SQL语句的最后,就会报错!
2  在oracle 数据库中,order by从句默认是升序(ASCend)排序的,如果想做降序排序,那么就需要显示声明关键字(DESCend).在升序排序的时候,Oracle对于NULL值的处理是放在最后的。因为Oracle认为NULL值是一个未知的,未分配的值,作为无穷大 对待,自然升序的时候就放在最后,反之,降序排列时就放在首位。如:

SQL> SELECT employee_id,last_name,manager_id
  2  FROM employees
  3  ORDER BY manager_id DESC
  4  ;
EMPLOYEE_ID LAST_NAME                 MANAGER_ID
----------- ------------------------- ----------
        100 King
        206 Gietz                            205
        202 Fay                              201
......

可以验证:查询结果集中,员工编号为100的员工是BOSS,没有manager,即其MANAGER_ID为NULL,降序排列的时候,自然会作为第一条返回记录出现在整个结果集中。
3 order by可以用于查询列表中的字段名,也可用于字段别名、表达式、字段名在列表中出现的顺序,如:

SQL> SELECT employee_id,last_name,salary*12 "Annual_Salary"
  2  FROM employees
  3  where employee_id>200
  4  order by salary*12
  5  ;
EMPLOYEE_ID LAST_NAME                 Annual_Salary
----------- ------------------------- -------------
        202 Fay                               72000
        203 Mavris                            78000
        206 Gietz                             99600
        204 Baer                             120000
        205 Higgins                          144000
        201 Hartstein                        156000
6 rows selected.

上例是按表达式排序,看一个按字段别名排序的例子:

SQL> SELECT employee_id,last_name,salary*12 Annual_Salary
  2  FROM employees
  3  WHERE employee_id>200
  4  ORDER BY Annual_Salary
  5  ;
EMPLOYEE_ID LAST_NAME                 ANNUAL_SALARY
----------- ------------------------- -------------
        202 Fay                               72000
        203 Mavris                            78000
        206 Gietz                             99600
        204 Baer                             120000
        205 Higgins                          144000
        201 Hartstein                        156000
6 rows selected.
SQL>

注:在该示例SQL语句中,如果salary*12的别名Annual_Salary,加了双引号”Annual_Salary”,那么按别名排序的时候也必须带有双引号:
order by “Annual_Salary”
接下来,再看一个按字段在查询列表中出现的先后顺序排列的例子:

SQL> SELECT employee_id,last_name,salary*12 Annual_Salary
  2  FROM employees
  3  WHERE employee_id>200
  4  ORDER BY 3;
EMPLOYEE_ID LAST_NAME                 ANNUAL_SALARY
----------- ------------------------- -------------
        202 Fay                               72000
        203 Mavris                            78000
        206 Gietz                             99600
        204 Baer                             120000
        205 Higgins                          144000
        201 Hartstein                        156000
6 rows selected.
SQL>

4 order by 子句甚至可以是对那些没有出现在查询列表中出现的字段进行排序:ORDER BY子句只对查询结果进行排序,而不关心按排序的字段是否出现在查询列表中。如:

SQL> SELECT last_name,salary*12 Annual_Salary
  2  FROM employees
  3  WHERE salary*12>150000
  4  ORDER BY employee_id
  5  ;
LAST_NAME                 ANNUAL_SALARY
------------------------- -------------
King                             288000
Kochhar                          204000
De Haan                          204000
Russell                          168000
Partners                         162000
Hartstein                        156000
6 rows selected.
SQL>
----------------------------------------------
SQL> SELECT employee_id,last_name,salary*12 Annual_Salary
  2  FROM employees
  3  WHERE salary*12>150000
  4  ORDER BY employee_id
  5  ;
EMPLOYEE_ID LAST_NAME                 ANNUAL_SALARY
----------- ------------------------- -------------
        100 King                             288000
        101 Kochhar                          204000
        102 De Haan                          204000
        145 Russell                          168000
        146 Partners                         162000
        201 Hartstein                        156000
6 rows selected.
SQL>

可以验证,其实,这两个SQL语句的排序效果是同样的!
5 ORDER BY子句中,同样支持对多个字段排序,如果需要对不同字段进行排序的方式(升序或者降序)不同,则需要显示声明。如:

SQL> SELECT employee_id,manager_id,department_id
  2  FROM employees
  3  WHERE employee_id>200
  4  ORDER BY manager_id ASC,department_id DESC
  5  ;
EMPLOYEE_ID MANAGER_ID DEPARTMENT_ID
----------- ---------- -------------
        201        100            20
        205        101           110
        204        101            70
        203        101            40
        202        201            20
        206        205           110
6 rows selected.
SQL>

ORDER BY 子句的用法很灵活,我们可以根据不同的应用场景,写出满足不同需求的排序SQL语句。

10g utilities:How to use LogMiner I

对数据库做不完全恢复的时候,如果我们能够精确的定位到出错的时间点的话,那么,我们可以在很大的程度上一次性地保证不完全恢复成功。所以,问题也就是我们应该怎么能够准确定位这个人为错误(如误删除表,错误的提交DML)发生的时间?接下来,要跟大家分享一下关于10g里LogMiner这个“日志挖掘器”的用法,也就是,在Oracle 10g版本的数据库,应该怎么使用LogMiner: 首先:简单介绍一下LogMiner工具。LogMiner 是Oracle公司从产品8i以后提供的一个非常有实用价值的分析工具,使用该工具可以轻松获得Oracle Online redo logs或者Archived logs日志文件中的具体内容。日志文件中记录了所有对数据库的数据块儿进行过改变的操作,而Logminer正是可以挖掘日志文件的利器。 其次:该如何使用Logminer?在早期的数据库版本里,LogMiner在默认情况下,并没有安装,需要手工的以SYSDBA身份去执行$ORACLE_HOME/rdbms/admin/dbmslm.sql以及$ORACLE_HOME/rdbms/admin/dbmslmd.sql这两个脚本。前者是创建DBMS_LOGMNR package,后者用于创建DBMS_LOGMNR_D这个包。在10g版本的数据库,这两个PL/SQL 包默认已经创建,我们就无须再去执行那两个脚本,可以直接对其调用。 然后:需要说明的一点,在Oracle 10g数据库版本里面,默认情况下,联机日志文件记录的内容并非提供任何的Supplemental logging,也就是说默认情况下使用LogMiner只能看到DDL操作。要想看到DML操作的话,在DML操作产生新的日志记录之前我们要启用最低的Supplemental logging,而启用这一特性又分为在数据库级别以及表级别,以下简单演示在全库级别启用最低Supplemental logging: 确认当前数据库Supplemental logging:

SQL> select supplemental_log_data_min from v$database;
SUPPLEME
--------
NO

启用Supplemental logging,以便可以通过LogMiner看到日志文件中记录的关于DML操作的相关信息:

SQL> alter database add supplemental log data;
Database altered.
SQL> select supplemental_log_data_min from v$database;
SUPPLEME
--------
YES

日志“挖掘"结束,可以禁用Supplemental logging:

SQL> alter database drop supplemental log data;
Database altered.

接下来:该说说具体使用LogMiner的方法了: 使用方法1: 1.1确认数据库当前的日志文件信息:

SQL> select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1          5   52428800          1 YES INACTIVE                674382 16-MAY-10
         2          1          4   52428800          1 YES INACTIVE                660045 15-MAY-10
         3          1          6   52428800          1 NO  CURRENT                 691344 20-MAY-10
SQL> select * from v$logfile;
    GROUP# STATUS  TYPE    MEMBER                                   IS_
---------- ------- ------- ---------------------------------------- ---
         3         ONLINE  /u01/app/oradata/helen/redo03.log        NO
         2         ONLINE  /u01/app/oradata/helen/redo02.log        NO
         1         ONLINE  /u01/app/oradata/helen/redo01.log        NO

1.2从上面的结果可以确定当前数据库使用的是第3组日志,其对应的日志文件成员为/u01/app/oradata/helen/redo03.log ,也就是说,当前情况下,所有接下来对数据库的操作都将记录在第3个日志组所对应文件成员中,直到该日志组被写满为止。把该组的日志成员添加到LogMiner的"挖掘"范围之内:

SQL> exec dbms_logmnr.add_logfile
(logfilename=>'/u01/app/oradata/helen/redo03.log',
options=>dbms_logmnr.new);
PL/SQL procedure successfully completed.

可以通过v$logmnr_logs数据字典表验证:

SQL> select filename from v$logmnr_logs;
FILENAME
----------------------------------------
/u01/app/oradata/helen/redo03.log

想继续添加其他日志文件成员进来,只需重新执行上述PL/SQL package,把对应的参数修改为新的日志文件成员名称,并且把new以addfile取代。删除对某个日志文件成员的挖掘,只需以removefile取代new之。 1.3执行LogMiner:

SQL> exec dbms_logmnr.start_logmnr
(options=>dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.

1.4开启另外一个会话,而且一定是新开第二个会话,不能跟刚才执行的调用Logminer在同一个会话,并且执行DML操作:

SQL> conn hr/hr;
Connected.
SQL> insert into test values(20);
1 row created.
SQL> insert into test values(21);
1 row created.
SQL> delete from test where id=21;
1 row deleted.
SQL>

1.5这时HR执行的操作已经被记录到日志文件里了,再切回到第一个会话,执行下述操作:

 SQL> select timestamp,table_name,sql_redo,sql_undo,operation
  2  from v$logmnr_contents
  3  where username='HR'

TIMESTAMP           TABLE SQL_REDO                                      SQL_UNDO                                 OPERATIO
------------------- ----- --------------------------------------------- ---------------------------------------- --------
2010/05/20 01:56:33       set transaction read write;                                                            START
2010/05/20 01:56:33 TEST  insert into "HR"."TEST"("ID") values ('20');  delete from "HR"."TEST" where "ID" = '20 INSERT
                                                                        ' and ROWID = 'AAAM39AAEAAAAGNAAA';

2010/05/20 01:56:36 TEST  insert into "HR"."TEST"("ID") values ('21');  delete from "HR"."TEST" where "ID" = '21 INSERT
                                                                        ' and ROWID = 'AAAM39AAEAAAAGNAAB';

2010/05/20 01:56:42 TEST  delete from "HR"."TEST" where "ID" = '21' and insert into "HR"."TEST"("ID") values ('2 DELETE
                           ROWID = 'AAAM39AAEAAAAGNAAB';                1');

1.6显然,我们可以看到准确的有用信息,当然V$logmnr_contents字典表中还可能有我们更为关心的字段,我们可以根据需要自行选取查看。挖掘完成之后,可以显示停止对Logminer工具的调用,也可以断开当前会话隐式停止对Logminer工具的使用,因为V$logmnr_contents表里的内容是存放在当前会话PGA里的。这也是为什么在调用Logminer的时候,其他会话是看不到v$logmnr_contents表里的内容,也解释了在第4步骤中为什么必须新开一个会话的原因。

SQL> exec dbms_logmnr.end_logmnr;
PL/SQL procedure successfully completed.
SQL> select filename from v$logmnr_logs;
no rows selected

关于LogMiner使用的另外一种方法,将在另外一篇日志里同大家齐分享!

Stay up for what

NND,熬夜到现在,难道就是为了解决一个Oracle 10g 利用Export/Import 跨平台(Win32Linux)并且伴随数据库字符集的问题吗?最终,最终,还是搞定了,稍后会有详细文档,记录下来,算是给自己一个交待:其中包括碰到若干个从前从未碰到过的错误:EXP-00046错误,字符集不兼容问题…….

如果说是为了搞定上述若干问题,那么我想熬夜也值。

如果不是,那我还是趁现在赶紧上床,=_=