--优化总原则:按语句执行顺序,先过滤掉最容易过滤到的记录.

--优化规则：1：数据比较多的表应该先进行连接，数据比较少的表应该尽可能后进行连接。
--原理：你从一堆沙子里找针难，还是几粒沙子里面找针难。

--2 from后面写表的顺序时，选择小表作为驱动表，放在右端。如果有3个以上的表连接查询，
--那就需要选择交叉表作为基础表，交叉表是指被其他表所引用的表.

--3 where后的条件：表之间的连接必须写在其它where条件之前.那些可以过滤掉最大数量记录
--的条件必须写在where字句的末尾.一般tablename.columnname = '多少'字句放到最后面.

--4 子查询中,要减少对同一个表的查询次数.

--5 尽量用exists代替in,not exists 代替not in.因为使用not in语句将执行一个内部的排序和合并,而且它对子
--查询中的表执行了一个全表遍历),可以用外连接(+)(次高效)或exits代替(最高效)

--6使用索引提高效率.但是索引需要空间来存储,也需要定期维护,每当有记录在表中增减或索引列
--被修改时,索引本身也会被修改,这意味着每条记录的insert,update,delete将为此多付出4,5次的磁盘I/O.

--建分区表会不会需要额外的存储空间来存储?如果不需要的话，那都可以考虑了。(一般千万级以上的表可以考虑建分区表)

/*oracle排序通常发生在以下情况:
1 order by 字句
2 group by 字句
3 select distinct 字句
4 创建索引时
5 union、minus 操作
6 调用排序合并连接
**/

--sql优化方法：
--1查看explain,对表作统计
--2尝试在查询字段上建立索引
--3查看数据库初始参数是否有不合理的地方，调整。

--and not s.area_id like '2%'改成and substr(s.area_id,1,1) <> '2'
--有group by了，distinct 和order by多余

--------------------------------------讲join...-------------------------------------------

1 WHEN uses NESTED LOOP joins?
WHEN joining SMALL NUMBER OF ROWS,WITH a good driving condition BETWEEN two TABLES.you drive FROM THE
OUTER LOOP TO THE INNER LOOP,so THE ORDER OF THE TABLES IN THE execution PLAN IS important.
THE OUTER LOOP IS THE driving ROW SOURCE.it produces a SET OF ROWS FOR driving THE JOIN condition.
THE ROW SOURCE can be a TABLE ACCESSED USING an INDEX SCAN OR a FULL TABLE SCAN.(小表驱动原理)

THE INNER LOOP IS iterated FOR every ROW returned FROM THE OUTER LOOP,ideally BY an INDEX SCAN.
IF THE ACCESS path FOR THE INNER LOOP IS NOT dependent ON THE OUTER LOOP,you should USER
other JOIN methods WHEN two indepdent ROW sources are joined together.
(如果存取路径对于内部循环来说,不依赖于外部循环的时候,那么,你就应该使用其他的连接方法,也就是在
两个独立的行资源连接在一起的时候.)

2 WHEN uses HASH joins ?
HASH joins are used FOR large DATA SETS.uses THE smaller OF two TABLES OR DATA sources TO BUILD a
HASH TABLE ON THE JOIN KEY IN memory.it THEN scans THE large TABLE,probing THE HASH TABLE TO find THE joined ROWS.

具体包括下面两个情况:
1 a large amount OF DATA needs TO be joined.
2 a large fraction OF a SMALL TABLE needs TO be joind.

3 WHEN uses SORT MERGE joins ?
can be used TO JOIN ROWS FROM two independent sources.

SORT MERGE joins are useful WHEN THE JOIN condition BETWEEN two TABLES i s an inequality condition
LIKE <,<=,>, OR >=. SORT MERGE joins perform better THAN NESTED LOOP joins FOR large DATA SETS.

--------------表扫描方式:
1 FULL TABLE scans:READS ALL ROWS FROM a TABLE AND filters OUT that do NOT meet SELECT criteria.During a fulll TABLE SCAN,ALL BLOCK IN THE TABLE that UNDER THE HIGH water mark are scanned.THE hith water mark indicate THE amount OF used SPACE.
和初始化参数db_file_multiblock_read_count相关.USING multiblock READS means a FULL TABLE scans can be performed very efficiently.

a FULL TABLE SCAN IS faster FOR accessing large amounts OF DATA.
FULL TABLE SCAN IS cheaper THEN INDEX RANGE SCAN WHEN accessing a large fraction OF THE blocks IN a TABLE.
(当存取一个表的大部分数据快的时候，全表扫描比索引范围扫描花费的代价要小.)
this IS because FULL TABLE SCAN can USE large I/O calls,AND marking fewer large i/o calls IS cheaper THAN marking many smaller calls.

优化器在以下情况会使用full TABLE SCAN
(1) 缺乏索引
(2) large amount DATA
如果当优化器认为查询存取了表的大部分数据库块的时候,即使有索引有用的情况下,还是会使用full TABLE SCAN.
(3) SMALL TABLE
IF a TABLE contains LESS THAN db_file_multiblock_read_count blocks UNDER THE HIGH water mark
(4) HIGH DEGREE OF parallelism

2 ROWID scans:
THE ROWID OF a ROW specifies THE DATAFILE AND DATA BLOCK containing THE ROW AND THE LOCATION OF THE ROW IN that BLOCK.THE exact LOCATION OF THE ROW IN THE DATABASE IS specified.

3 INDEX scans:
INDEX UNIQUE scans:
INDEX RANGE scans:
FULL scans:
FAST FULL INDEX scans:
INDEX joins:
BITMAP INDEXES:
4 CLUSTER ACCESS:
5 HASH ACCESS:
6 SAMPLE TABLE scans:

---------------performance tuning
cpu
memory:
i/o subsystem
NETWORK

SE IN,exists在子查询中

IN certain cirsumstances, it IS better TO USE IN rather THEN EXISTS.
IN general, IF THE selective predicate IS IN THE subquery,THEN USE IN.
IF THE selective predicate IS IN THE PARENT QUERY,THEN USE EXISTS.

IN 适合于外表大而内表小的情况；exists适合于外表小而内表大的情况。

IN 和exists的区别 ：如果子查询得出的结果记录较少，主查询中的表较大且又有索引时应该用in,
反之如果外层的主查询记录较少,子查询的表大，又有索引时使用exists.区别主要是驱动顺序的改变，
如果是exists,那么以外层表为驱动表，先被访问，如果是in,那么先执行子查询。

/*
1: 减少不必要的全表扫描,快20倍
*/

/*
2:创建基于函数的索引
*/

/*
3:通过第三方工具,调整sql语句
*/

/*
从程序库缓存和stats$qls_summary抽取不友好的sql语句,并快速定位高频执行的语句,并首先对它们进行调整.
*/

/*
将全表扫描替换为索引范围扫描总体方针:
1 对于所有记录排序的表:读取表记录数40%以下的查询应使用索引范围扫描
2 读取表数据块数目7%以下的查询应该使用索引范围扫描
*/

/*
oracle并行查询只适用于执行全表扫描的查询,索引是并行查询的死对头
*/

当建立一个新的索引时,应该考虑的因素:
1 :THE most aggresive form OF this technique IS TO BUILD an INDEX-organized TABLE.but,
you must be careful that THE increased leaf SIZE OF an IOT does NOT undermine THE efforts TO reduce I/O.
2: USING a different INDEX TYPE:b-tree INDEX, BITMAP INDEX, FUNCTION-based INDEX,partitioned INDEX,REVERSE INDEX,
考虑应该建立哪类索引.
3 finding THE COST OF an INDEX:building AND maintaining an INDEX STRUCTURE can be expansive,AND it can
consume RESOURCE sucn AS DISK SPACE,cpu, AND i/o capacity.designer must CONSIDER that THE benefit OF ANY INDEX outweigh THE negatives OF INDEX maintaince.

IF you INSERT INTO a TABLE WITH three INDEXES,THEN it will be approximately 10 times slower THAN an INSERT INTO a TABLE WITH NO INDEX.
FOR DML,AND particulary fo INSERT-heavy application,THE INDEX design should be seriously reviewed.
经常说的建索引能够提高select,UPDATE,delete的效率,但不一定能提高insert的效率.所以对于一张经常要被insert的表,建索引时,要考虑清楚.

4 USING a REVERSE KEY INDEX OR USING a cycling sequenct TO prefix AND SEQUENCE VALUES.

5 ordering COLUMNS IN an INDEX(索引排序): (1) ORDER COLUMNS WITH most SELECTIVITY FIRST.
(2)ORDER COLUMNS TO reduce I/O BY clustering OR sorting DATA.

6 USING views:

7 开发人员必须有效地理解执行计划,必须要准备的事情:(1)good DATABASE connection managemtn (2) good CURSOR USAGE AND managemt

8实现应用:
(1) USER interface
(2) business logic code can be ON THE client server,AND THE DATABASE server.but,THE application server IS THE most common loaction FOR business logic code.
(3) SQL statements WITH decode CASE statments are very ofen candidates FOR optimization.

AT a peak period ON a web site,THE resoponse TIME will NOT execeed five seconds FOR a page REFRESH.

---------------------------------top 10 mistakes found in oracle systems

1 bad connection MANAGEMENT
2 bad USE OF cursors AND teh SHARED pool
3 bad SQL:USE ADDM TO 看high load SQL AND THE SQL tuning advisor can be used TO provide recommendations FOR improvement.
4 USE OF nonstandard initializtion PARAMETERS:
5 getting DATABASE I/o wrong
6 redo LOG setup problems
7 serializtion OF DATA blocks IN THE buffer CACHE due TO lack OF free LISTS,free LIST GROUPS,transactions slots(INITRANS),OR shortage OF ROLLBACK segments.
8 LONG FULL TABLE scans
9 HIGH amounts OF recursive(SYS)递归 SQL
10 迁移过程当中出现的问题:例如缺乏indexes OR incorrent STATISTICS.
addm会显示高负载的sql.