Oracle SQL 优化学习记录

ziyoo0830

4545

87页

243次

2019-12-23

10墨值下载

Oracle SQL 优化学习记录

第0章环境

第1章 SQL优化必懂概念

1.1、基数（CARDINALITY）

某个列的唯一键（Distinct_Keys）的数量；

主键列的基数等于表的总行数；

一般情况下，当查询结果是返回表中5%以内的数据时，应该走索引；当查询结果返回的是超过表

中5%的数据时，应该走全表扫描；



1.2、选择性（SELECTIVITY)

基数与总行数的比值再乘以100%就是某个列的选择性；

1.3、收集表统计信息

备份scott用户：

oracle*o11g-/home/oracle>$expdp \' / as sysdba \' dumpfile=expdp_scott.dump

logfile=expdp_scott.log schemas=scott

初始化环境：

oracle*o11g-/home/oracle>$sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Sep 27 13:08:43 2019

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@o11g>grant dba to scott;

Grant succeeded.

SYS@o11g>alter user scott account unlock ;

SYS@o11g>alter user scott identified by tiger;

SYS@o11g>conn scott/tiger;

SCOTT@o11g>create table test as select * from dba_objects ;

1.4、获取表每个列的基数与选择性



1.5、什么样的列必须创建索引呢？

当一个列出现在where条件中，该列没有创建索引并且选择性大于20%，那么该列就必须创建索

引；



只有大表才会产生性能问题



begin

dbms_stats.gather_table_stats(ownname     => 'SCOTT',

               tabname     => 'TEST',

               estimate_percent => 100,

               method_opt    => 'for all columns size ?

1/auto',

               no_invalidate  => FALSE,

               degree      => 1,

               cascade     => TRUE);

end;

SCOTT@o11g>select a.column_name,

   b.num_rows,

   a.num_distinct Cardinality,

   round(a.num_distinct / b.num_rows * 100, 2) selectivity

from dba_tab_col_statistics a, dba_tables b

where a.owner = b.owner

 and a.table_name = b.table_name

 and a.owner = 'SCOTT'

   and a.table_name = 'TEST';

COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY

--------------- ---------- ----------- -----------

OWNER   86963  30    .03

OBJECT_NAME   86963 52421   60.28

SUBOBJECT_NAME   86963  142    .16

OBJECT_ID   86963 86963    100

DATA_OBJECT_ID   86963 9077   10.44

OBJECT_TYPE   86963  45    .05

CREATED   86963  925   1.06

LAST_DDL_TIME   86963 1026   1.18

TIMESTAMP   86963 1065   1.22

STATUS   86963   1 0

TEMPORARY   86963   2 0

GENERATED   86963   2 0

SECONDARY   86963   2 0

NAMESPACE   86963  21    .02

EDITION_NAME   86963   0 0

15 rows selected.

of 87

10墨值下载

oracle 性能优化特别推荐

关注

评论