Oracle之SQL语句性能优化

胡欢

791

48页

0次

2019-09-19

10墨值下载

1 48

Oracle SQL

1. ORACLE

ORACLE 3 :

a. RULE ( ) b. COST ( ) c. CHOOSE ( )

, init.ora OPTIMIZER_MODE ,

RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS . SQL (session)

(CBO, Cost-Based Optimizer) , analyze

, (object statistics) .

(CHOOSE),

analyze . table analyze , CBO , ,

RULE .

,ORACLE CHOOSE , (full table

scan) , CHOOSE , .

2. Table

ORACLE :

. ORACLE (database block)

b. ROWID

ROWID , , , ROWID

..ORACLE (INDEX) (ROWID)

. ROWID ,

3. SQL

SQL , , ORACLE SQL .

SGA(system global area) (shared buffer pool)

. , SQL ( ) ,

, ORACLE

. ORACLE SQL .

ORACLE (cache buffering) ,

init.ora , ,

, .

ORACLE SQL ,ORACLE .

,ORACLE , ,SQL

( , ).

A. :

2 48

SELECT * FROM EMP;

SELECT * from EMP;

Select * From Emp;

SELECT * FROM EMP;

B. :

Jack sal_limit private synonym

Work_city public synonym

Plant_detail public synonym

Jill sal_limit private synonym

Work_city public synonym

Plant_detail table owner

SQL .

SQL

select max(sal_cap) from sal_limit;

private synonym - sal_limit ,

select count(*0 from work_city where sdesc like 'NEW%';

public synonym - work_city

select a.sdesc,b.location from work_city a , plant_detail b where a.city_id =

b.city_id

jack private synonym plant_detail jill , .

C. SQL (bind variables)

SQL ( ), (

, )

select pin , name from people where pin = :blk1.pin;

select pin , name from people where pin = :blk1.ot_ind;

select pin , name from people where pin = :blk1.ov_ind;

4. ( )

ORACLE FROM , FROM

( driving table) . FROM ,

. ORACLE ,

. , (FROM ) ,

of 48

10墨值下载

oracle 优化性能优化

关注

评论