将Java进行到底
将Java进行到底
posts - 15,  comments - 66,  trackbacks - 0

Sql优化是一项复杂的工作,以下的一些基本原则是本人看书时所记录下来的,很明确且没什么废话:

1.  索引的使用:

1.当插入的数据为数据表中的记录数量的10%以上,首先需要删除该表的索引来提高数据的插入效率,当数据插入后,再建立索引。

2.避免在索引列上使用函数或计算,在where子句中,如果索引是函数的一部分,优化器将不再使用索引而使用全表扫描。如:

低效:select * from dept where sal*12 >2500;

高效:select * from dept where sal>2500/12;

(3).避免在索引列上使用not !=”,索引只能告诉什么存在于表中,而不能告诉什么不存在于表中,当数据库遇到not !=”时,就会停止使用索引而去执行全表扫描。

(4).索引列上>=代替>

 低效:select * from emp where deptno > 3

 高效:select * from emp where deptno >=4

两者的区别在于,前者dbms将直接跳到第一个deptno等于4的记录,而后者将首先定位到deptno等于3的记录并且向前扫描到第一个deptno大于3的。

(5).非要对一个使用函数的列启用索引,基于函数的索引是一个较好的方案。

2. 游标的使用:

   当在海量的数据表中进行数据的删除、更新、插入操作时,用游标处理的效率是最慢的,但是游标又是必不可少的,所以正确使用游标十分重要:

   (1). 在数据抽取的源表中使用时间戳,这样每天的维表数据维护只针对更新日期为最新时间的数据来进行,大大减少需要维护的数据记录数。

   (2). insertupdate维表时都加上一个条件来过滤维表中已经存在的记录,例如:

insert into dim_customer select * from ods_customer where ods_customer.code not exists (dim_customer.code)

 ods_customer为数据源表。dim_customer为维表。

   (3). 使用显式的游标,因为隐式的游标将会执行两次操作,第一次检索记录,第二次检查too many rows这个exception,而显式游标不执行第二次操作。

3.  据抽取和上载时的sql优化:

(1). Where 子句中的连接顺序:

oracle采用自下而上的顺序解析where子句,根据这个原理,表之间的连接必须写在其他where条件之前,那些可以过滤掉大量记录的条件必须写在where子句的末尾。如:

低效:select * from emp e where sal>5000 and job = ‘manager’ and 25<(select count (*) from emp where mgr=e.empno);

高效:select * from emp e where 25<(select count(*) from emp where mgr=e.empno) and sal>5000 and job=’manager’;

   (2). 删除全表时,用truncate 替代 delete,同时注意truncate只能在删除全表时适用,因为truncateddl而不是dml

   (3). 尽量多使用commit

只要有可能就在程序中对每个delete,insert,update操作尽量多使用commit,这样系统性能会因为commit所释放的资源而大大提高。

   (4). exists替代in ,可以提高查询的效率。

   (5). not exists 替代 not in

   (6). 优化group by

提高group by语句的效率,可以将不需要的记录在group by之前过滤掉。如:

低效:select job, avg(sal) from emp group by job having job = ‘president’ or job=’manager’;

高效: select job, avg(sal) from emp having  job=’president’ or job=’manager’ group by job;

   (7). 有条件的使用union-all 替代 union:这样做排序就不必要了,效率会提高35倍。

   (8). 分离表和索引

       总是将你的表和索引建立在不同的表空间内,决不要将不属于oracle内部系统的对象存放到system表空间内。同时确保数据表空间和索引表空间置于不同的硬盘控制卡控制的硬盘上。


转自:http://blog.csdn.net/eigo/archive/2006/03/02/614157.aspx
posted @ 2006-03-04 20:34 风萧萧 阅读(448) | 评论 (0)编辑 收藏

/*
建表:
dept:
 deptno(primary key),dname,loc
emp:
 empno(primary key),ename,job,mgr,sal,deptno
*/

1 列出emp表中各部门的部门号,最高工资,最低工资
select max(sal) as 最高工资,min(sal) as 最低工资,deptno from emp group by deptno;

2 列出emp表中各部门job为'CLERK'的员工的最低工资,最高工资
select max(sal) as 最高工资,min(sal) as 最低工资,deptno as 部门号 from emp where job = 'CLERK' group by deptno;

3 对于emp中最低工资小于1000的部门,列出job为'CLERK'的员工的部门号,最低工资,最高工资
select max(sal) as 最高工资,min(sal) as 最低工资,deptno as 部门号 from emp as b
where job='CLERK' and 1000>(select min(sal) from emp as a where a.deptno=b.deptno) group by b.deptno

4 根据部门号由高而低,工资有低而高列出每个员工的姓名,部门号,工资
select deptno as 部门号,ename as 姓名,sal as 工资 from emp order by deptno desc,sal asc

5 写出对上题的另一解决方法
(请补充)

6 列出'张三'所在部门中每个员工的姓名与部门号
select ename,deptno from emp where deptno = (select deptno from emp where ename = '张三')

7 列出每个员工的姓名,工作,部门号,部门名
select ename,job,emp.deptno,dept.dname from emp,dept where emp.deptno=dept.deptno

8 列出emp中工作为'CLERK'的员工的姓名,工作,部门号,部门名
select ename,job,dept.deptno,dname from emp,dept where dept.deptno=emp.deptno and job='CLERK'

9 对于emp中有管理者的员工,列出姓名,管理者姓名(管理者外键为mgr)
select a.ename as 姓名,b.ename as 管理者 from emp as a,emp as b where a.mgr is not null and a.mgr=b.empno

10 对于dept表中,列出所有部门名,部门号,同时列出各部门工作为'CLERK'的员工名与工作
select dname as 部门名,dept.deptno as 部门号,ename as 员工名,job as 工作 from dept,emp
where dept.deptno *= emp.deptno and job = 'CLERK'

11 对于工资高于本部门平均水平的员工,列出部门号,姓名,工资,按部门号排序
select a.deptno as 部门号,a.ename as 姓名,a.sal as 工资 from emp as a
where a.sal>(select avg(sal) from emp as b where a.deptno=b.deptno) order by a.deptno

12 对于emp,列出各个部门中平均工资高于本部门平均水平的员工数和部门号,按部门号排序
select count(a.sal) as 员工数,a.deptno as 部门号 from emp as a
where a.sal>(select avg(sal) from emp as b where a.deptno=b.deptno) group by a.deptno order by a.deptno

13 对于emp中工资高于本部门平均水平,人数多与1人的,列出部门号,人数,按部门号排序
select count(a.empno) as 员工数,a.deptno as 部门号,avg(sal) as 平均工资 from emp as a
where (select count(c.empno) from emp as c where c.deptno=a.deptno and c.sal>(select avg(sal) from emp as b where c.deptno=b.deptno))>1
group by a.deptno order by a.deptno

14 对于emp中低于自己工资至少5人的员工,列出其部门号,姓名,工资,以及工资少于自己的人数
select a.deptno,a.ename,a.sal,(select count(b.ename) from emp as b where b.sal<a.sal) as 人数 from emp as a
where (select count(b.ename) from emp as b where b.sal<a.sal)>5


转自:http://blog.csdn.net/woolceo/archive/2006/03/02/614094.aspx

posted @ 2006-03-04 20:31 风萧萧 阅读(2023) | 评论 (1)编辑 收藏
在开发部署PORTAL项目时,遇到异常:
Exception:weblogic.management.ApplicationException: prepare failed for content_repo.jar Module: content_repo.jar Error: Exception preparing module: EJBModule(content_repo.jar,status=NEW) Unable to deploy EJB: content_repo.jar from content_repo.jar: Class not found: com.bea.content.repo.i18n.RepoExceptionTextFormatter java.lang.NoClassDefFoundError: Class not found: com.bea.content.repo.i18n.RepoExceptionTextFormatter at weblogic.ejb20.compliance.EJBComplianceChecker.check([Ljava.lang.Object;)V(EJBComplianceChecker.java:287)

我在weblogic81 sp3的doc中没有发现com.bea.content.repo.i18n这个package.

重新安装了weblogic sp4,就不再出现这个错误了。
posted @ 2006-02-15 23:14 风萧萧 阅读(602) | 评论 (0)编辑 收藏

Problem Statement

     When editing a single line of text, there are four keys that can be used to move the cursor: end, home, left-arrow and right-arrow. As you would expect, left-arrow and right-arrow move the cursor one character left or one character right, unless the cursor is at the beginning of the line or the end of the line, respectively, in which case the keystrokes do nothing (the cursor does not wrap to the previous or next line). The home key moves the cursor to the beginning of the line, and the end key moves the cursor to the end of the line.

You will be given a int, N, representing the number of character in a line of text. The cursor is always between two adjacent characters, at the beginning of the line, or at the end of the line. It starts before the first character, at position 0. The position after the last character on the line is position N. You should simulate a series of keystrokes and return the final position of the cursor. You will be given a String where characters of the String represent the keystrokes made, in order. 'L' and 'R' represent left and right, while 'H' and 'E' represent home and end.

Definition

    
Class: CursorPosition
Method: getPosition
Parameters: String, int
Returns: int
Method signature: int getPosition(String keystrokes, int N)
(be sure your method is public)
    

Constraints

- keystrokes will be contain between 1 and 50 'L', 'R', 'H', and 'E' characters, inclusive.
- N will be between 1 and 100, inclusive.

Examples

0)
    
"ERLLL"
10
Returns: 7
First, we go to the end of the line at position 10. Then, the right-arrow does nothing because we are already at the end of the line. Finally, three left-arrows brings us to position 7.
1)
    
"EHHEEHLLLLRRRRRR"
2
Returns: 2
All the right-arrows at the end ensure that we end up at the end of the line.
2)
    
"ELLLELLRRRRLRLRLLLRLLLRLLLLRLLRRRL"
10
Returns: 3
3)
    
"RRLEERLLLLRLLRLRRRLRLRLRLRLLLLL"
19
Returns: 12

This problem statement is the exclusive and proprietary property of TopCoder, Inc. Any unauthorized use or reproduction of this information without the prior written consent of TopCoder, Inc. is strictly prohibited. (c)2003, TopCoder, Inc. All rights reserved.

答案:


 1
 2public class CursorPosition {
 3    public int getPosition(String keystrokes, int N){
 4        int position = 0;
 5        String s = "";
 6        for(int i = 0; i < keystrokes.length(); i++){
 7            s = keystrokes.substring(i, i+1);
 8            if("L".equals(s)){
 9                if(position == 0continue;
10                position--;
11            }

12            if("R".equals(s)){
13                if(position == N) continue;
14                position++;
15            }

16            if("H".equals(s)){
17                position = 0;
18            }

19            if("E".equals(s)){
20                position = N;
21            }

22
23        }

24
25        return position;
26
27    }

28    /**
29     * @param args
30     */

31    public static void main(String[] args) {
32        CursorPosition cursorPosition = new CursorPosition();
33        int cursor = cursorPosition.getPosition("ERLLL"10);
34        System.out.println("cursor:" + cursor);
35    }

36
37}

38
posted @ 2005-11-27 23:42 风萧萧 阅读(921) | 评论 (2)编辑 收藏

Problem Statement

     A square matrix is a grid of NxN numbers. For example, the following is a 3x3 matrix:
 4 3 5
 2 4 5
 0 1 9
One way to represent a matrix of numbers, each of which is between 0 and 9 inclusive, is as a row-major String. To generate the String, simply concatenate all of the elements from the first row followed by the second row and so on, without any spaces. For example, the above matrix would be represented as "435245019".

You will be given a square matrix as a row-major String. Your task is to convert it into a String[], where each element represents one row of the original matrix. Element i of the String[] represents row i of the matrix. You should not include any spaces in your return. Hence, for the above String, you would return {"435","245","019"}. If the input does not represent a square matrix because the number of characters is not a perfect square, return an empty String[], {}.

Definition

    
Class: MatrixTool
Method: convert
Parameters: String
Returns: String[]
Method signature: String[] convert(String s)
(be sure your method is public)
    

Constraints

- s will contain between 1 and 50 digits, inclusive.

Examples

0)
    
"435245019"
Returns: {"435", "245", "019" }
The example above.
1)
    
"9"
Returns: {"9" }
2)
    
"0123456789"
Returns: { }
This input has 10 digits, and 10 is not a perfect square.
3)
    
"3357002966366183191503444273807479559869883303524"
Returns: {"3357002", "9663661", "8319150", "3444273", "8074795", "5986988", "3303524" }

This problem statement is the exclusive and proprietary property of TopCoder, Inc. Any unauthorized use or reproduction of this information without the prior written consent of TopCoder, Inc. is strictly prohibited. (c)2003, TopCoder, Inc. All rights reserved.

答案:


 1public class MatrixTool {
 2
 3    public String[] convert(String s){
 4        if (s == null || s.length() == 0 || s.length() > 50){
 5            return new String[]{};
 6        }

 7        int length = s.length();
 8        int n = (int)Math.sqrt(length);
 9        if(n*== length){
10            String[] result = new String[n];
11            for(int i = 0; i < n; i++){
12                result[i] = s.substring(i*n, i*+ n);
13            }

14            return result;
15        }
else {
16            return new String[]{};
17        }

18    }

19
20    /**
21     * @param args
22     */

23    public static void main(String[] args) {
24        MatrixTool matrix = new MatrixTool();
25        String[] result = matrix.convert("3357002966366183191503444273807479559869883303524");
26        for(int i = 0; i < result.length; i++){
27            System.out.println(result[i]);
28        }

29    }

30
31}

32
posted @ 2005-11-27 23:40 风萧萧 阅读(705) | 评论 (0)编辑 收藏
     摘要: Problem Statement      A simple line drawing program uses a blank 20 x 20 pixel canvas and a directional cursor that starts at the upper left corner pointing straight down. T...  阅读全文
posted @ 2005-11-27 23:37 风萧萧 阅读(1144) | 评论 (0)编辑 收藏

<2005年11月>
303112345
6789101112
13141516171819
20212223242526
27282930123
45678910

常用链接

留言簿(8)

随笔分类

随笔档案

文章分类

文章档案

相册

收藏夹

myfriends

opensource

搜索

  •  

最新评论

阅读排行榜

评论排行榜