Category Archives: Database

说说Sql优化

Posted by on 2009/10/17 No comments

大概一个多月之前，接到过一个面试电话，其中有个问题是让我谈谈SQL优化，当时一时竟不知道怎么回答。这其中的不知道如何回答，并不是因为自己完全不懂SQL优化，而是在看过一些Oracle相关的书之后，深知这个话题不是一两句能够讲清的，所以不知道如何有条理的来描述SQL优化方法。 记得当时很不严密地说了两句，尽量使用索引，让SQL选择的集合尽量小。 这次事情之后，想起来有必要整理一下已知的关于SQL优化知识。

SQL优化是个系统工程，Tom老师的这篇文章说得很明白。它与数据库软件，操作系统及硬件都有非常大的关系，并没有一个包治百病的方案。因此在看过Oracle一些书之后，我便不再迷信网上到处转载的关于SQL优化的一些方法了。下面是我在看Sql Tuning这本书，整理出的一个学习优化SQL需要了解的东西。
需要进行SQL优化，首先需要了解SQL如何在数据库中执行。包括SQL解析，SQL转换，访问数据的路径，多张表之间采用何种方式联接，在不同数据集情况下执行计划会有什么不同，每一个SQL的命令对应于数据库的哪些操作（如order,having 等）。理解这些之后，还需要学习的就是如何通过一些方法来控制SQL的执行计划，如联接顺序等。在SQL Tuning一书中，看到过一些让我感觉匪夷所思的方法，目前还没读完，其中有许多有趣的东西，可惜只找到英文版，读着累。

其中的细节太多，下面说一个我了解的存在很多误解的东西。
Index在一些时候并不是最有效的方式，当SQL语句选取的数据集在超过全表的20%时，全表扫描的效率会高于使用Index，这也两种方式的数据访问有关。Index时，会先访问索引，取得Rowid（Rowid包括该条数据的物理地址信息），然后通过RowId读取访问该条记录所在的块。全表扫描则直接按顺序读取存储该表的数据块。Oracle在读取数据时，并不是一条一条读取数据，而是按块读取，因此有些时候一个数据块中可能会包含多条记录。再加入Oracle在全表扫描时可以并行读取数据块，速度远远高于小的IO，因此全表扫描速度比通过索引访问更快。

一个巧妙的Index

Posted by on 2009/04/30 1 comment

在一个论坛上看到一个我认为很精妙的创建Index的方法，后面的讨论更加精彩，于是把这个贴子里的精髓部分整理一下。
下面是一个创建索引的语句：
create index idx_prodd on prodd (status,’1′）;

创建这个索引的意义是什么呢？原来这是DBA为了让status为null时，Where子句中包括status时，SQL语句也能利用索引进行快速查找。
Oracle中创建B-tree索引时，这个column上为null值时是不会被索引，这时候以这个字段is null为条件查询时必须进行table full scan。
现在用这个语句创建一个联合索引，保证status为null时，这个值仍然会被索引，因此保证能够通过索引去查询。而另外一个方面另外一辅助常量字段1，对于索引的大小影响也不大，确实是一个很聪明的做法。
这样看来，在对表的字段进行设计时对于会出现在查询条件中的值应该尽量避免null值。如果某些字段可以为null的值，给它设定一个default值， 如‘NULL’，这样能有效保证查询的数据。
下面这个例子，可以考虑采用下面的方法达到相同效果：
alter table prodd modify status not null ;
alter table prodd modify status default ‘NULL‘;
然后将SQL的 where status is null 改成 where status = ’NULL‘。
虽然这样做会增加一点数据存储的空间，但是获得的好处远远大于这些存储开销。

Something about Database Design

Posted by on 2009/04/27 1 comment

I read some articles about how to design a relation database. There is a note extract from the articles. it is a good guide to design a database.

Normalisation is the term used to describe how you break a file down into tables to create a database.
The targets of normalization:
1.minimization of data redundancy
2.minimization of data restructuring
3.minimization of I/O by reduction of transaction sizes
4.enforcement of referential integrity

The First Normal Form (1NF) addresses the structure of an isolated table.
The Second (2NF), Third (3NF), and Boyce-Codd (BCNF) Normal Forms address one-to-one and one-to-many relationships.
The Fourth (4NF) and Fifth (5NF) Normal Forms deal with many-to-many relationships.

Normal Form define:
A table is said to be in First Normal Form (1NF), if all entries in it are scalar-valued. Relational database tables are 1NF by construction since vector-valued entries are forbidden.
A table is in Second Normal Form (2NF) if every non-key field is a fact about the entire key. In other words, a table is 2NF if it is 1NF and all non-key attributes are functionally dependent on the entire primary key (that is, the dependency is irreducible).
A relation is in Third Normal Form (3NF) if it is 2NF and none of its attributes is a fact about another non-key field. In other words, no non-key field functionally depends on any other non-key field.

Every field in a record must depend on The Key (1NF), the Whole Key (2NF), and Nothing But The Key (3NF).

Some tips:
1.Table name all caps.
2.An attribute is a descriptive or quantitative characteristic of an entity. Initial Cap.
3.PK is a uniquely identify each instance of an entity,should not change. The Integer datatype is more effecient than CHAR datatype.
should non-intelligent.
4.A relationship is a logical link between entities. one-to-many we can use FK to implements.
5.many-to-many relationship may be resolved by creating an intermediate entity known as a cross-reference(XREF) entity.
6.FK, the value is dependency on pk.
7.identifying or non-identifying.
8.Cardinality “How many instances of the child entity relate to each instance of the parent entity?”

the process of design a database.
Table define
Attribute define
Relationship define

All the things are picked up from the articles which refered in this post:10-useful-articles-about-database.

Ubuntu 8.10安装Oracle 10g笔记

Posted by on 2009/03/19 No comments

上周末重新换了一块硬盘，因此操作系统和数据库都要重装一遍。重新安装Oracle 10g的过程中遇到不少有趣的问题，在解决这些问题的过程中学到了不少东西。
安装过程主要是参照孙高勇的那份文档来做的，但是在Ubuntu 8.10上面还会遇到一些其它问题。
1、关于安装是OUI中文的问题
该问题困扰我很久，一直没找到解决办法，最终采用英文环境进行安装以避免安装过程中的中文乱码问题。

2、安装过程运行runInstaller过程中会出现unzip的错误
该问题是由于Oracle 10g安装文件中/install 下的unzip与Ubuntu 8.10自带的unzip版本不一致导致的，我的解决方法如下：
install$ mv unzip unzipbak
install$ ln -s /usr/bin/unzip unzip

3、运行runInstaller后出现如下报错：
Error in writing to directory /tmp/OraInstall2009-03-16_02-26-36PM. Please
ensure that this directory is writable and has at least 60 MB of disk space.

该问题通过修改/install下所有文件的权限得于解决

在解决安装过程出现的问题时，发现了自己现在有一个很不好的习惯：在出现问题是首先想到的不是分析问题并尝试解决，而是通过Google找答案。
查了一下google的web history，发现只要在上网，每天都用使用Google进行搜索。在这过程中很多问题或许能找到答案，但是对于问题的理解并不深刻，下次出现该问题仍然无法解决。
在解决第二个问题过程中，我在Google没有发现任何有效信息，最终还是自己静下心来分析出错的日志，最终确认是由于unzip的版本冲突导致该问题。
在这过程中给我印象非常深刻，很多的问题在分析日志的过程中肯定能找到线索， 这在使用Ubuntu的过程中非常重要。

SQL WHERE多个子句的优先级关系

Posted by on 2009/02/23 3 comments

Oracle where字句优先级
今天在复习Oracle SQL，发现了一个以前一直都没有在意的问题。SQL查询时WHERE后面带有多个子句时，各子句之间有一定的优化关系。大约半年前就碰到过这个问题，当时TSE发给我一个SQL，告诉我说系统存在严重的数据问题，因为这个SQL查出了很多有问题的数据。当时吓得我一身冷汗，然后自己马上连到线上数据库，按照他说的业务需求自己写了个SQL，并执行了一下。我的SQL执行得出的结果并没有他说的数据不对的问题。于是比较了两个SQL，发现在写OR子句的位置有区别。当时只是得出了WHERE子句有一定的关系，但是具体的关系并没有去深入研究。今天在看一个Presentation时终于注意到了一个确切的说法。 在SQL的WHERE后面的多个子句有如下的优先关系：
优先级
1 算术运算符
2 连接符
3 比较符
4 IS [NOT] NULL, LIKE, [NOT] IN
5 [NOT] BETWEEN
6 NOT
7 AND
8 OR

下面用一个例子来证明一下这之间的关系
SQL＞SELECT last_name, job_id, salary
2 FROM employees
3 WHERE job_id = ‘SA_REP’
4 OR job_id = ‘AD_PRES’
5 AND salary > 15000;

执行结果如下：SQL> /

LAST_NAME JOB_ID SALARY
————————- ———- ———-
King AD_PRES 24000
Tucker SA_REP 10000
Bernstein SA_REP 9500
Hall SA_REP 9000
Olsen SA_REP 8000
Cambrault SA_REP 7500
Tuvault SA_REP 7000
King SA_REP 10000
Sully SA_REP 9500
McEwen SA_REP 9000
Smith SA_REP 8000
Doran SA_REP 7500
Sewall SA_REP 7000
Vishney SA_REP 10500
Greene SA_REP 9500
Marvins SA_REP 7200
Lee SA_REP 6800
Ande SA_REP 6400
Banda SA_REP 6200
Ozer SA_REP 11500
Bloom SA_REP 10000
Fox SA_REP 9600
Smith SA_REP 7400
Bates SA_REP 7300
Kumar SA_REP 6100
Abel SA_REP 11000
Hutton SA_REP 8800
Taylor SA_REP 8600
Livingston SA_REP 8400
Grant SA_REP 7000
Johnson SA_REP 6200

31 rows selected

现在修改一下OR的位置再执行一次：
SQL> select last_name,job_id,salary from employees where job_id = ‘SA_REP’
2 and salary > 15000 or job_id = ‘AD_PRES’;

LAST_NAME JOB_ID SALARY
————————- ———- ———-
King AD_PRES 24000

发现两次查询的数据差别非常大。 下面来分析一下执行的过程，我在网上并没有找到确切的描述，下面这些都是我通过对数据观察推断出来的结论。

在第一个SQL中真正执行的应该是如下语句：
select last_name,job_id,salary from employees where job_id = ‘SA_REP’ or (job_id = ‘AD_PRES’ and salary > 15000)
因为AND条件比OR高，因此先进行运算
在第二个SQL中是按照where各子句的顺序过滤数据，在执行过程中job_id = ‘SA_REP’
and salary > 15000 没有取得符合条件的数据，只有job_id = ‘AD_PRES’子句找到了符合条件的数据。 下面执行一下语句验证一下：
SQL> select last_name,job_id,salary from employees where job_id=’AD_PRES’;

LAST_NAME JOB_ID SALARY
————————- ———- ———-
King AD_PRES 24000

其他几个运算符的优先级也可按照上面的方法来进行分析。

删除重复记录SQL

Posted by on 2008/10/11 No comments

几个月前和一位师兄打电话时问过我一个问题，如何删除一张表中所有重复的记录？ 当时一直没有想出来。
最近重新开始学习ORACLE SQL，回想起这个问题，花了不少时间想这个问题，终于在同事的提醒下想到了解决方法。
SQL> select * from temp;

AA BB
—————— ——————
abc abc
abc abc
xyz xyz
xyz xyz
opq opq

SQL> select max(rowid),aa,bb from temp group by aa,bb;

MAX(ROWID) AA BB
—————— —————— ——————
AAAMWPAABAAANlqAAB abc abc
AAAMWPAABAAANlqAAE opq opq
AAAMWPAABAAANlqAAD xyz xyz

SQL> delete from temp where (rowid,aa,bb) not in ( select max(rowid),aa,bb from temp group by aa,bb);

2 rows deleted.

SQL> commit;

Commit complete.

SQL> select * from temp;

AA BB
—————— ——————
abc abc
xyz xyz
opq opq

OK，经过测试，这个问题解决了。：） 开心ing