一切入一 http://space.itpub.net/13464361 空华三界。如风卷烟。 幻影六塵。犹汤沃雪。 廓然無际。唯一真心。 Copyright(C) 一切入一 SupeSite/X-Space Fri, 05 Dec 2008 20:58:13 GMT top n 查询之优化 http://space.itpub.net/13464361/viewspace-245875 提问：

应该不少人都遇到过这样的问题吧, 在数据库中选取 位于min行和max行 之间的所有数据,
通常DQL 书写如下.

SELECT * FROM (
SELECT a,b,c,...,rownum rowcount FROM
where ... and rownum < :ma...]]> blog weiran11 Sun, 20 Apr 2008 10:03:34 GMT rownum 和优化器 http://space.itpub.net/13464361/viewspace-244212 提问：

在9i的环境中，现在有个一个表A里面有大概10万行数据，其中有个字段B有10个值，每个值的行数比较平均，可以认为每个值都占1万行。
有条SQL为：
select * from A where B = xxx and rownum <= 100;
这...]]> blog weiran11 Sun, 20 Apr 2008 09:49:03 GMT 为何倒序索引没被使用 http://space.itpub.net/13464361/viewspace-232242 提问：

难道索引有优先级
drop table t;
create table t as select * from dba_objects;
create index t_idx on t(owner, object_type);
cre...]]> blog weiran11 Wed, 09 Apr 2008 11:17:41 GMT oracle statistics http://space.itpub.net/13464361/viewspace-232235 statistics: It is collected by Oracle about all of useful information
            and it is also used by Oracle to determine its activity

Type:
   1) wait event: v$system...]]> blog weiran11 Wed, 09 Apr 2008 10:55:29 GMT 思考 http://space.itpub.net/13464361/viewspace-230952
把遇到的事物都当作是对内心的历练和修养，则不起人我之见，而成内省之功]]> blog weiran11 Sun, 06 Apr 2008 23:51:29 GMT 发现数据库中的热表 http://space.itpub.net/13464361/viewspace-230923 发现数据库中的热表

1. 如果你使用的是10g，并且statistics_level=typical or all,那么所有的表实际上是monitoring的状态。此时可以通过dba_tab_modifications来查看每个表自上次gather statistics后发生过的动作，从而找出热表. 为了保证内...]]> blog weiran11 Fri, 04 Apr 2008 12:16:45 GMT 读Oracle的书 http://space.itpub.net/13464361/viewspace-230922
读Oracle的书要以‘我’为主，按自己的思维构架去读，将所读之书分崩离析，需要那些材料就去读那些，按自己的思维方式和习惯去构建搭建自己的Oracle知识体系，这样获得的知识才稳固，理解的才深刻，读书才能是自己的。否则读完后多半都还给作者了...]]> blog weiran11 Fri, 04 Apr 2008 02:15:16 GMT How to design index http://space.itpub.net/13464361/viewspace-230921

How to design index


Index design:
1) Think of which columns should be included.
unique column to enable index unique scan
most queried columns to enable index full scan or fast index full scan

2) T...]]> blog weiran11 Thu, 03 Apr 2008 02:11:51 GMT trace self or other session http://space.itpub.net/13464361/viewspace-219055 privilege related

trace session

show explain plan


1.
dba_roles -- list available role
dba_role_privs -- show who are granted what role
dba_sys_privs -- show who or which role are granted which system privilege blog weiran11 Thu, 27 Mar 2008 00:00:00 GMT Oracle 可用于查找问题的工具 http://space.itpub.net/13464361/viewspace-219056

这只是一篇个人小结，还有许多工具没被提到,也许以后会有续篇。

1. explain plan: show the explain plan without running the sql
step:
1) @?/rdbms/admin...]]> blog weiran11 Thu, 27 Mar 2008 00:00:00 GMT categories and some comparison of sybase/db2/oracle http://space.itpub.net/13464361/viewspace-219054 some oracle parameter/view categories

initial parameter file/monitoring/update comparision between sybase/db2/oracle


1. some category:
1.1 instance level:
parameter:
instance_name
db_name
blog weiran11 Sat, 22 Mar 2008 00:00:00 GMT logical structure design thinking http://space.itpub.net/13464361/viewspace-219051

数据库的logical structure design
1）ER design
2)Schema refinement(Table design)
3)define the views
4)index design

method of table design
1) decomposition
表设计问题：
1）某字段属...]]> blog weiran11 Wed, 19 Mar 2008 00:00:00 GMT something from oracle sample schema http://space.itpub.net/13464361/viewspace-219053

1. where is the demo script to create sample schema
$ORACLE_HOME/demo/schema

2. sqlplus set option
set feedback on/off (show how many records are returned)
set verify on/off (show the input of p...]]> blog weiran11 Wed, 19 Mar 2008 00:00:00 GMT DBA learning http://space.itpub.net/13464361/viewspace-219052 What and how to learn


DBA learning

DBA的学习离不开实际的工作需要。所以在阐述学习什么和如何学习之前有必要回顾DBA在工作中的主要责任和任务。大致来说DBA的工作分为两方面：开发和维护。
开发涉及以下方面：
1）设计：数据库的逻辑机...]]> blog weiran11 Mon, 17 Mar 2008 00:00:00 GMT 恭喜您成功申请ITPUB BLOG http://space.itpub.net/13464361/viewspace-219048 http://weiran11.itpub.net/
您BLOG的控制面板在
http://weiran11.itpub.net/admin.php
登陆口令就是论坛口令，BLOG的口令和论坛是...]]> blog weiran11 Fri, 14 Mar 2008 00:00:00 GMT something on NLS to begin with http://space.itpub.net/13464361/viewspace-219049

1. What's NLS
National Language Support. It control the database message characterset, date format, currency format, timezone,...etc

2. Where to set NL...]]> blog weiran11 Fri, 14 Mar 2008 00:00:00 GMT something about access method http://space.itpub.net/13464361/viewspace-219050

Access method

1. Full table scan
2. Full index scan/Fas full index scan:
a) used when index contain all of columns needed for the query
and when the query retu...]]> blog weiran11 Fri, 14 Mar 2008 00:00:00 GMT