oracle多表连接sql优化过程,使用正确的表连接顺序和表连接方式优化SQL

最新推荐文章于 2024-08-24 00:37:28 发布
最新推荐文章于 2024-08-24 00:37:28 发布
阅读量448 收藏
点赞数
文章标签： oracle多表连接sql优化过程

本文中的执行计划格式混乱，把执行计划复制到  UltraEdit 中就可以正常显示了。

1. 创建测试数据

create table lixia.t1 as select * from  dba_objects;

delete from lixia.t1 where object_id is null;

alter table lixia.t add constraint pk_t1 primary key(object_id);

exec dbms_stats.gather_table_stats('LIXIA','T1',cascade=>true,no_invalidate=> FALSE,estimate_percent => 100,method_opt=>'for all columns size repeat');

create table lixia.t2 as select * from  dba_objects;

delete from lixia.t2 where object_id is null;

alter table lixia.t2 add constraint pk_t2 primary key(object_id);

exec dbms_stats.gather_table_stats('LIXIA','T2',cascade=>true,no_invalidate=> FALSE,estimate_percent => 100,method_opt=>'for all columns size repeat');

create table lixia.t3 as select * from  dba_objects;

delete from lixia.t3 where object_id is null;

alter table lixia.t3 add constraint pk_t3 primary key(object_id);

create index lixia.idx_t3_1 on lixia.t3(owner);

exec dbms_stats.gather_table_stats('LIXIA','T3',cascade=>true,no_invalidate=> FALSE,estimate_percent => 100,method_opt=>'for all columns size repeat');

2. 数据分布情况

SQL> select owner,count(1) from dba_objects group by owner order by count(1) desc;

OWNER                            COUNT(1)

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

SYS                                 38008

PUBLIC                              34283

SYSMAN                               3554

APEX_030200                          2561

ORDSYS                               2513

MDSYS                                2009

XDB                                  1168

OLAPSYS                               721

SYSTEM                                637

CTXSYS                                389

WMSYS                                 332

OWNER                            COUNT(1)

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

EXFSYS                                312

ORDDATA                               257

QUEST                                 230

PERFSTAT                              148

DBSNMP                                 65

GGS                                    54

LIXIA                                  44

FLOWS_FILES                            13

OWBSYS_AUDIT                           12

OUTLN                                  10

ORDPLUGINS                             10

OWNER                            COUNT(1)

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

ORACLE_OCM                              8

SI_INFORMTN_SCHEMA                      8

SCOTT                                   6

APPQOSSYS                               5

OWBSYS                                  2

TEST                                    1

28 rows selected.

3. 使用提示强制使用错误的表连接顺序

select /*+ leading(t2,t1,t3) */  t1.object_id,t1.object_name,t2.owner,t3.STATUS

from lixia.t1 ,lixia.t2,lixia.t3

where  t1.object_id=t2.object_id

and  t2.object_id=t3.object_id

and  t1.object_id <= 178717

and  t3.owner='TEST';

Execution Plan

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

Plan hash value: 2663886062

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

| Id  | Operation                    | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT             |          |  3119 |   179K|       |  1058   (1)| 00:00:13 |

|*  1 |  HASH JOIN                   |          |  3119 |   179K|       |  1058   (1)| 00:00:13 |

|*  2 |   TABLE ACCESS BY INDEX ROWID| T3       |  3119 | 56142 |       |    92   (0)| 00:00:02 |

|*  3 |    INDEX RANGE SCAN          | IDX_T3_1 |  3120 |       |       |     8   (0)| 00:00:01 |

|*  4 |   HASH JOIN                  |          | 87332 |  3496K|  1968K|   966   (1)| 00:00:12 |

|*  5 |    TABLE ACCESS FULL         | T2       | 87333 |   938K|       |   348   (1)| 00:00:05 |

|*  6 |    TABLE ACCESS FULL         | T1       | 87335 |  2558K|       |   348   (1)| 00:00:05 |

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

Predicate Information (identified by operation id):

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

1 - access("T2"."OBJECT_ID"="T3"."OBJECT_ID")

2 - filter("T3"."OBJECT_ID"<=178717)

3 - access("T3"."OWNER"='TEST')

4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

5 - filter("T2"."OBJECT_ID"<=178717)

6 - filter("T1"."OBJECT_ID"<=178717)

Statistics

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

1  recursive calls

0  db block gets

2505  consistent gets

1247  physical reads

0  redo size

755  bytes sent via SQL*Net to client

524  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

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

| Id  | Operation                    | Name     | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |

PLAN_TABLE_OUTPUT

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

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

|   0 | SELECT STATEMENT             |          |      1 |        |       |       |  1058 (100)|          |      1 |00:00:00.13 |    2505 |   1247 |       |       |        |

|*  1 |  HASH JOIN                   |          |      1 |   3119 |   179K|       |  1058   (1)| 00:00:13 |      1 |00:00:00.13 |    2505 |   1247 |  1645K|  1645K|  628K (0)|

|*  2 |   TABLE ACCESS BY INDEX ROWID| T3       |      1 |   3119 | 56142 |       |    92   (0)| 00:00:02 |      1 |00:00:00.01 |       3 |      0 |       |       |        |

|*  3 |    INDEX RANGE SCAN          | IDX_T3_1 |      1 |   3120 |       |       |     8   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |      0 |       |       |        |

|*  4 |   HASH JOIN                  |          |      1 |  87332 |  3496K|  1968K|   966   (1)| 00:00:12 |  87340 |00:00:00.10 |    2502 |   1247 |  5508K|  2261K| 5674K (0)|

|*  5 |    TABLE ACCESS FULL         | T2       |      1 |  87333 |   938K|       |   348   (1)| 00:00:05 |  87340 |00:00:00.01 |    1252 |      0 |       |       |        |

|*  6 |    TABLE ACCESS FULL         | T1       |      1 |  87335 |  2558K|       |   348   (1)| 00:00:05 |  87341 |00:00:00.01 |    1250 |   1247 |       |       |        |

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

Predicate Information (identified by operation id):

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

1 - access("T2"."OBJECT_ID"="T3"."OBJECT_ID")

2 - filter("T3"."OBJECT_ID"<=178717)

3 - access("T3"."OWNER"='TEST')

4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

5 - filter("T2"."OBJECT_ID"<=178717)

6 - filter("T1"."OBJECT_ID"<=178717)

E-ROWS 列显示SQL最后返回 3119条数据，实际上只返回一条数据，由此我们看到当表的连接顺序错误时会导致优化器

估算出错误的返回记录数。

这里之所以会出现基数估算错误(为3119条记录)，原因就是固定了表连接的顺序导致优化器转换出的谓词有问题，

OWNER字段上的索引IDX_T3_1 选择性很差，优化器使用这个索引估算 T3表的基数计算出错误的基数，从而生成错误的谓词

access("T2"."OBJECT_ID"="T3"."OBJECT_ID")、  filter("T3"."OBJECT_ID"<=178717)、access("T3"."OWNER"='TEST')、

access("T1"."OBJECT_ID"="T2"."OBJECT_ID")、filter("T2"."OBJECT_ID"<=178717)、filter("T1"."OBJECT_ID"<=178717)。

正确的谓词请查看 第五部分SQL语句执行计划中的谓词。

Buffers 列显示总共有 2505个逻辑IO。

Reads 列显示总共有 1247个物理IO。

由于使用提示固定了表连接到顺序，导致使用了 HASH 连接产生了大量的逻辑读。

从执行计划中看到 SQL语句只返回一条数据，但是扫描T1表返回了 87335条记录、扫描T2表返回了 87333条记录、T1表和T2表

HASH 连接放回了 87332 条记录。由此判断读取了大量不需要的数据行。

返回一条记录产生了 2505 个逻辑IO，远高于返回记录数与逻辑IO 1/5的比值。

4. 删除提示让ORACLE 优化器生成正确的执行计划

select   t1.object_id,t1.object_name,t2.owner,t3.STATUS

from lixia.t1 ,lixia.t2,lixia.t3

where  t1.object_id=t2.object_id

and  t2.object_id=t3.object_id

and  t1.object_id <= 178717

and  t3.owner='TEST';

Execution Plan

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

Plan hash value: 863399664

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

| Id  | Operation                     | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT              |          |  3119 |   179K|   788   (1)| 00:00:10 |

|*  1 |  HASH JOIN                    |          |  3119 |   179K|   788   (1)| 00:00:10 |

|*  2 |   HASH JOIN                   |          |  3119 | 90451 |   440   (1)| 00:00:06 |

|*  3 |    TABLE ACCESS BY INDEX ROWID| T3       |  3119 | 56142 |    92   (0)| 00:00:02 |

|*  4 |     INDEX RANGE SCAN          | IDX_T3_1 |  3120 |       |     8   (0)| 00:00:01 |

|*  5 |    TABLE ACCESS FULL          | T2       | 87333 |   938K|   348   (1)| 00:00:05 |

|*  6 |   TABLE ACCESS FULL           | T1       | 87335 |  2558K|   348   (1)| 00:00:05 |

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

Predicate Information (identified by operation id):

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

1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

2 - access("T2"."OBJECT_ID"="T3"."OBJECT_ID")

3 - filter("T3"."OBJECT_ID"<=178717)

4 - access("T3"."OWNER"='TEST')

5 - filter("T2"."OBJECT_ID"<=178717)

6 - filter("T1"."OBJECT_ID"<=178717)

Statistics

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

1  recursive calls

0  db block gets

10  consistent gets

0  physical reads

0  redo size

755  bytes sent via SQL*Net to client

524  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

set autotrace trace 看到的不是真实的执行计划。

alter session set STATISTICS_LEVEL = ALL;

select   t1.object_id,t1.object_name,t2.owner,t3.STATUS

from lixia.t1 ,lixia.t2,lixia.t3

where  t1.object_id=t2.object_id

and  t2.object_id=t3.object_id

and  t1.object_id <= 178717

and  t3.owner='TEST';

select * from table(dbms_xplan.DISPLAY_CURSOR(null, null, 'ALLSTATS LAST Advanced'));

Plan hash value: 3864569537

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

| Id  | Operation                      | Name     | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |

PLAN_TABLE_OUTPUT

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

|   0 | SELECT STATEMENT               |          |      1 |        |       |    11 (100)|          |      1 |00:00:00.01 |      10 |

|   1 |  NESTED LOOPS                  |          |      1 |      1 |    59 |    11   (0)| 00:00:01 |      1 |00:00:00.01 |      10 |

|   2 |   NESTED LOOPS                 |          |      1 |      1 |    59 |    11   (0)| 00:00:01 |      1 |00:00:00.01 |       9 |

|   3 |    NESTED LOOPS                |          |      1 |      1 |    29 |    10   (0)| 00:00:01 |      1 |00:00:00.01 |       7 |

|*  4 |     TABLE ACCESS BY INDEX ROWID| T3       |      1 |      1 |    18 |     9   (0)| 00:00:01 |      1 |00:00:00.01 |       4 |

|*  5 |      INDEX RANGE SCAN          | IDX_T3_1 |      1 |      1 |       |     8   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |

|   6 |     TABLE ACCESS BY INDEX ROWID| T2       |      1 |      1 |    11 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |

|*  7 |      INDEX UNIQUE SCAN         | PK_T2    |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       2 |

|*  8 |    INDEX UNIQUE SCAN           | PK_T1    |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       2 |

|   9 |   TABLE ACCESS BY INDEX ROWID  | T1       |      1 |      1 |    30 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |

PLAN_TABLE_OUTPUT

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

Predicate Information (identified by operation id):

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

4 - filter("T3"."OBJECT_ID"<=178717)

5 - access("T3"."OWNER"='TEST')

7 - access("T2"."OBJECT_ID"="T3"."OBJECT_ID")

filter("T2"."OBJECT_ID"<=178717)

8 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

filter("T1"."OBJECT_ID"<=178717)

BUFFERS 列显示总共有 10 个逻辑IO。

当没有使用提示限制表的连接顺序时，优化器通过统计信息正确的估算出T3表和SQL最后只返回一条记录，并把通过

谓词条件推算出 T1和T2表也只需要返回一条有效的数据。选择使用嵌套循环连接和索引扫描。

5. 使用提示强制使用错误的表连接顺序，但与3不同的是使用主键对T3表的数据进行过滤能生成很优的执行计划

select /*+ leading(t2,t3,t1) */  t1.object_id,t1.object_name,t2.owner,t3.STATUS

from lixia.t1 ,lixia.t2,lixia.t3

where  t1.object_id=t2.object_id

and  t2.object_id=t3.object_id

and  t1.object_id <= 178717

and  t3.object_id=170832;

Execution Plan

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

Plan hash value: 2742238221

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

| Id  | Operation                     | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT              |       |     1 |    53 |     4   (0)| 00:00:01 |

|   1 |  NESTED LOOPS                 |       |     1 |    53 |     4   (0)| 00:00:01 |

|   2 |   NESTED LOOPS                |       |     1 |    23 |     3   (0)| 00:00:01 |

|   3 |    TABLE ACCESS BY INDEX ROWID| T2    |     1 |    11 |     2   (0)| 00:00:01 |

|*  4 |     INDEX UNIQUE SCAN         | PK_T2 |     1 |       |     1   (0)| 00:00:01 |

|   5 |    TABLE ACCESS BY INDEX ROWID| T3    |     1 |    12 |     1   (0)| 00:00:01 |

|*  6 |     INDEX UNIQUE SCAN         | PK_T3 |     1 |       |     0   (0)| 00:00:01 |

|   7 |   TABLE ACCESS BY INDEX ROWID | T1    |     1 |    30 |     1   (0)| 00:00:01 |

|*  8 |    INDEX UNIQUE SCAN          | PK_T1 |     1 |       |     0   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

4 - access("T2"."OBJECT_ID"=170832)

6 - access("T3"."OBJECT_ID"=170832)

8 - access("T1"."OBJECT_ID"=170832)

Statistics

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

1  recursive calls

0  db block gets

9  consistent gets

0  physical reads

0  redo size

755  bytes sent via SQL*Net to client

524  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

Plan hash value: 2742238221

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

| Id  | Operation                     | Name  | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |

PLAN_TABLE_OUTPUT

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

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

|   0 | SELECT STATEMENT              |       |      1 |        |       |     4 (100)|          |      1 |00:00:00.01 |       9 |

|   1 |  NESTED LOOPS                 |       |      1 |      1 |    53 |     4   (0)| 00:00:01 |      1 |00:00:00.01 |       9 |

|   2 |   NESTED LOOPS                |       |      1 |      1 |    23 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       6 |

|   3 |    TABLE ACCESS BY INDEX ROWID| T2    |      1 |      1 |    11 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |

|*  4 |     INDEX UNIQUE SCAN         | PK_T2 |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |

|   5 |    TABLE ACCESS BY INDEX ROWID| T3    |      1 |      1 |    12 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |

|*  6 |     INDEX UNIQUE SCAN         | PK_T3 |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       2 |

|   7 |   TABLE ACCESS BY INDEX ROWID | T1    |      1 |      1 |    30 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |

|*  8 |    INDEX UNIQUE SCAN          | PK_T1 |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       2 |

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

Predicate Information (identified by operation id):

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

4 - access("T2"."OBJECT_ID"=170832)

6 - access("T3"."OBJECT_ID"=170832)

8 - access("T1"."OBJECT_ID"=170832)

select /*+ leading(t2,t3,t1) */ t1.object_id,t1.object_name,t2.owner,t3.STATUS

from lixia.t1 ,lixia.t2,lixia.t3

where  t1.object_id=t2.object_id

and  t2.object_id=t3.object_id

and  t1.object_id >= 178717

and  t3.object_id <= 178717;

Execution Plan

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

Plan hash value: 2742238221

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

| Id  | Operation                     | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT              |       |     1 |    53 |     4   (0)| 00:00:01 |

|   1 |  NESTED LOOPS                 |       |     1 |    53 |     4   (0)| 00:00:01 |

|   2 |   NESTED LOOPS                |       |     1 |    23 |     3   (0)| 00:00:01 |

|   3 |    TABLE ACCESS BY INDEX ROWID| T2    |     1 |    11 |     2   (0)| 00:00:01 |

|*  4 |     INDEX UNIQUE SCAN         | PK_T2 |     1 |       |     1   (0)| 00:00:01 |

|   5 |    TABLE ACCESS BY INDEX ROWID| T3    |     1 |    12 |     1   (0)| 00:00:01 |

|*  6 |     INDEX UNIQUE SCAN         | PK_T3 |     1 |       |     0   (0)| 00:00:01 |

|   7 |   TABLE ACCESS BY INDEX ROWID | T1    |     1 |    30 |     1   (0)| 00:00:01 |

|*  8 |    INDEX UNIQUE SCAN          | PK_T1 |     1 |       |     0   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

4 - access("T2"."OBJECT_ID"=178717)

6 - access("T3"."OBJECT_ID"=178717)

filter("T2"."OBJECT_ID"="T3"."OBJECT_ID")

8 - access("T1"."OBJECT_ID"=178717)

filter("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Statistics

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

1  recursive calls

0  db block gets

9  consistent gets

0  physical reads

0  redo size

756  bytes sent via SQL*Net to client

524  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

Plan hash value: 2742238221

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

| Id  | Operation                     | Name  | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |

PLAN_TABLE_OUTPUT

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

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

|   0 | SELECT STATEMENT              |       |      1 |        |       |     4 (100)|          |      1 |00:00:00.01 |       9 |

|   1 |  NESTED LOOPS                 |       |      1 |      1 |    53 |     4   (0)| 00:00:01 |      1 |00:00:00.01 |       9 |

|   2 |   NESTED LOOPS                |       |      1 |      1 |    23 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       6 |

|   3 |    TABLE ACCESS BY INDEX ROWID| T2    |      1 |      1 |    11 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |

|*  4 |     INDEX UNIQUE SCAN         | PK_T2 |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |

|   5 |    TABLE ACCESS BY INDEX ROWID| T3    |      1 |      1 |    12 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |

|*  6 |     INDEX UNIQUE SCAN         | PK_T3 |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       2 |

|   7 |   TABLE ACCESS BY INDEX ROWID | T1    |      1 |      1 |    30 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |

|*  8 |    INDEX UNIQUE SCAN          | PK_T1 |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       2 |

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

Predicate Information (identified by operation id):

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

4 - access("T2"."OBJECT_ID"=178717)

6 - access("T3"."OBJECT_ID"=178717)

filter("T2"."OBJECT_ID"="T3"."OBJECT_ID")

8 - access("T1"."OBJECT_ID"=178717)

filter("T1"."OBJECT_ID"="T2"."OBJECT_ID")

虽然表连接的顺序是错误的，但ORACLE 优化器通过主键正确的估算出估算出 T1和T2表也只需要返回一条有效的数据。

选择使用嵌套循环连接和索引扫描。

6. 使用提示强制使用错误的表连接顺序，但与3不同的是SQL最终不返回数据优化器跳过了T1和T2表的全表扫描

select  /*+ leading(t2,t1,t3) */ t1.object_id,t1.object_name,t2.owner,t3.STATUS

from lixia.t2 ,lixia.t1,lixia.t3

where  t1.object_id=t2.object_id

and  t2.object_id=t3.object_id

and  t1.object_id <= 178717

and  t3.owner='test';

Execution Plan

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

Plan hash value: 2663886062

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

| Id  | Operation                    | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT             |          |     1 |    59 |       |   968   (1)| 00:00:12 |

|*  1 |  HASH JOIN                   |          |     1 |    59 |       |   968   (1)| 00:00:12 |

|*  2 |   TABLE ACCESS BY INDEX ROWID| T3       |     1 |    18 |       |     2   (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN          | IDX_T3_1 |     1 |       |       |     1   (0)| 00:00:01 |

|*  4 |   HASH JOIN                  |          | 87332 |  3496K|  1968K|   966   (1)| 00:00:12 |

|*  5 |    TABLE ACCESS FULL         | T2       | 87333 |   938K|       |   348   (1)| 00:00:05 |

|*  6 |    TABLE ACCESS FULL         | T1       | 87335 |  2558K|       |   348   (1)| 00:00:05 |

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

Predicate Information (identified by operation id):

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

1 - access("T2"."OBJECT_ID"="T3"."OBJECT_ID")

2 - filter("T3"."OBJECT_ID"<=178717)

3 - access("T3"."OWNER"='test')

4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

5 - filter("T2"."OBJECT_ID"<=178717)

6 - filter("T1"."OBJECT_ID"<=178717)

Statistics

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

1  recursive calls

0  db block gets

2  consistent gets

0  physical reads

0  redo size

545  bytes sent via SQL*Net to client

513  bytes received via SQL*Net from client

1  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

0  rows processed

Plan hash value: 2663886062

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

| Id  | Operation                    | Name     | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |

PLAN_TABLE_OUTPUT

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

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

|   0 | SELECT STATEMENT             |          |      1 |        |       |       |   968 (100)|          |      0 |00:00:00.01 |       2 |       |       |          |

|*  1 |  HASH JOIN                   |          |      1 |      1 |    59 |       |   968   (1)| 00:00:12 |      0 |00:00:00.01 |       2 |  1245K|  1245K|  410K (0)|

|*  2 |   TABLE ACCESS BY INDEX ROWID| T3       |      1 |      1 |    18 |       |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |       |       |          |

|*  3 |    INDEX RANGE SCAN          | IDX_T3_1 |      1 |      1 |       |       |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |       |       |          |

|*  4 |   HASH JOIN                  |          |      0 |  87332 |  3496K|  1968K|   966   (1)| 00:00:12 |      0 |00:00:00.01 |       0 |  5916K|  1857K|          |

|*  5 |    TABLE ACCESS FULL         | T2       |      0 |  87333 |   938K|       |   348   (1)| 00:00:05 |      0 |00:00:00.01 |       0 |       |       |          |

|*  6 |    TABLE ACCESS FULL         | T1       |      0 |  87335 |  2558K|       |   348   (1)| 00:00:05 |      0 |00:00:00.01 |       0 |       |       |          |

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

Predicate Information (identified by operation id):

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

1 - access("T2"."OBJECT_ID"="T3"."OBJECT_ID")

2 - filter("T3"."OBJECT_ID"<=178717)

3 - access("T3"."OWNER"='test')

4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

5 - filter("T2"."OBJECT_ID"<=178717)

6 - filter("T1"."OBJECT_ID"<=178717)

当返回结果为零行数据时，ORACLE 执行引擎自动取消了T1和T2表的全表扫描和T1、T2的HASH连接，只有2个逻辑IO。

7. T3表的 OBJECT_ID 列数据是唯一的，但建立非唯一索引在表连接顺序错误的情况下 ORACLE优化器仍能正确估算出

T3和T1表只需要返回一条记录，而使用索引扫描和嵌套选好连接

alter table lixia.t3 drop  primary key CASCADE;

create index lixia.idx_t3_2 on lixia.t3(object_id);

exec dbms_stats.gather_table_stats('LIXIA','T3',cascade=>true,no_invalidate=> FALSE,estimate_percent => 100,method_opt=>'for all columns size repeat');

alter session set STATISTICS_LEVEL = ALL;

select /*+ leading(t2,t3,t1) */  t1.object_id,t1.object_name,t2.owner,t3.STATUS

from lixia.t1 ,lixia.t2,lixia.t3

where  t1.object_id=t2.object_id

and  t2.object_id=t3.object_id

and  t1.object_id <= 178717

and  t3.object_id=170832;

select * from table(dbms_xplan.DISPLAY_CURSOR(null, null, 'ALLSTATS LAST Advanced'));

Plan hash value: 3080117625

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

| Id  | Operation                     | Name     | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |

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

|   0 | SELECT STATEMENT              |          |      1 |        |       |     5 (100)|          |      1 |00:00:00.01 |      10 |       |       |          |

|   1 |  MERGE JOIN CARTESIAN         |          |      1 |      1 |    53 |     5   (0)| 00:00:01 |      1 |00:00:00.01 |      10 |       |       |          |

|   2 |   NESTED LOOPS                |          |      1 |      1 |    23 |     4   (0)| 00:00:01 |      1 |00:00:00.01 |       7 |       |       |          |

|   3 |    TABLE ACCESS BY INDEX ROWID| T2       |      1 |      1 |    11 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |       |       |          |

|*  4 |     INDEX UNIQUE SCAN         | PK_T2    |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |       |       |          |

|   5 |    TABLE ACCESS BY INDEX ROWID| T3       |      1 |      1 |    12 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       4 |       |       |          |

|*  6 |     INDEX RANGE SCAN          | IDX_T3_2 |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |       |       |          |

|   7 |   BUFFER SORT                 |          |      1 |      1 |    30 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |  2048 |  2048 | 2048  (0)|

|   8 |    TABLE ACCESS BY INDEX ROWID| T1       |      1 |      1 |    30 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |       |       |          |

|*  9 |     INDEX UNIQUE SCAN         | PK_T1    |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       2 |       |       |          |

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

Predicate Information (identified by operation id):

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

4 - access("T2"."OBJECT_ID"=170832)

6 - access("T3"."OBJECT_ID"=170832)

9 - access("T1"."OBJECT_ID"=170832)

8. T3表的 OBJECT_ID 列数据有重复，建立非唯一索引在表连接顺序错误的情况下 ORACLE优化器仍能估算出

T3和T1表只需要返回一条记录(实际返回11条记录)，而使用索引扫描和嵌套选好连接。

由此判断在非唯一索引选择率很低的情况下，即便表连接顺序错误优化器也可以生产优良的执行计划。

索引选择率=1/(索引唯一值)*100

update lixia.t3  set object_id=20 where rownum<1100;

update lixia.t3 set object_id=170832 where rownum<11;

commit;

exec dbms_stats.gather_table_stats('LIXIA','T3',cascade=>true,no_invalidate=> FALSE,estimate_percent => 100,method_opt=>'for all columns size repeat');

select /*+ leading(t2,t3,t1) */  t1.object_id,t1.object_name,t2.owner,t3.STATUS

from lixia.t1 ,lixia.t2,lixia.t3

where  t1.object_id=t2.object_id

and  t2.object_id=t3.object_id

and  t1.object_id <= 178717

and  t3.object_id=170832;

select * from table(dbms_xplan.DISPLAY_CURSOR(null, null, 'ALLSTATS LAST Advanced'));

Plan hash value: 3080117625

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

| Id  | Operation                     | Name     | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |

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

|   0 | SELECT STATEMENT              |          |      1 |        |       |     5 (100)|          |     11 |00:00:00.01 |      12 |       |       |          |

|   1 |  MERGE JOIN CARTESIAN         |          |      1 |      1 |    53 |     5   (0)| 00:00:01 |     11 |00:00:00.01 |      12 |       |       |          |

|   2 |   NESTED LOOPS                |          |      1 |      1 |    23 |     4   (0)| 00:00:01 |     11 |00:00:00.01 |       9 |       |       |          |

|   3 |    TABLE ACCESS BY INDEX ROWID| T2       |      1 |      1 |    11 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |       |       |          |

|*  4 |     INDEX UNIQUE SCAN         | PK_T2    |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |       |       |          |

|   5 |    TABLE ACCESS BY INDEX ROWID| T3       |      1 |      1 |    12 |     2   (0)| 00:00:01 |     11 |00:00:00.01 |       6 |       |       |          |

|*  6 |     INDEX RANGE SCAN          | IDX_T3_2 |      1 |      1 |       |     1   (0)| 00:00:01 |     11 |00:00:00.01 |       3 |       |       |          |

|   7 |   BUFFER SORT                 |          |     11 |      1 |    30 |     3   (0)| 00:00:01 |     11 |00:00:00.01 |       3 |  2048 |  2048 | 2048  (0)|

|   8 |    TABLE ACCESS BY INDEX ROWID| T1       |      1 |      1 |    30 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |       |       |          |

|*  9 |     INDEX UNIQUE SCAN         | PK_T1    |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       2 |       |       |          |

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

Predicate Information (identified by operation id):

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

4 - access("T2"."OBJECT_ID"=170832)

6 - access("T3"."OBJECT_ID"=170832)

9 - access("T1"."OBJECT_ID"=170832)

select /*+ leading(t2,t3,t1) */ t1.object_id,t1.object_name,t2.owner,t3.STATUS

from lixia.t1 ,lixia.t2,lixia.t3

where  t1.object_id=t2.object_id

and  t2.object_id=t3.object_id

and  t1.object_id >= 178717

and  t3.object_id <= 178717;

Plan hash value: 3984383077

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

| Id  | Operation                      | Name     | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |

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

|   0 | SELECT STATEMENT               |          |      1 |        |       |     5 (100)|          |      1 |00:00:00.01 |      10 |

|   1 |  NESTED LOOPS                  |          |      1 |      1 |    53 |     5   (0)| 00:00:01 |      1 |00:00:00.01 |      10 |

|   2 |   NESTED LOOPS                 |          |      1 |      1 |    53 |     5   (0)| 00:00:01 |      1 |00:00:00.01 |       9 |

|   3 |    NESTED LOOPS                |          |      1 |      1 |    23 |     4   (0)| 00:00:01 |      1 |00:00:00.01 |       7 |

|   4 |     TABLE ACCESS BY INDEX ROWID| T2       |      1 |      1 |    11 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |

|*  5 |      INDEX UNIQUE SCAN         | PK_T2    |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |

|   6 |     TABLE ACCESS BY INDEX ROWID| T3       |      1 |      1 |    12 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       4 |

|*  7 |      INDEX RANGE SCAN          | IDX_T3_2 |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |

|*  8 |    INDEX UNIQUE SCAN           | PK_T1    |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       2 |

|   9 |   TABLE ACCESS BY INDEX ROWID  | T1       |      1 |      1 |    30 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |

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

Predicate Information (identified by operation id):

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

5 - access("T2"."OBJECT_ID"=178717)

7 - access("T3"."OBJECT_ID"=178717)

filter("T2"."OBJECT_ID"="T3"."OBJECT_ID")

8 - access("T1"."OBJECT_ID"=178717)

filter("T1"."OBJECT_ID"="T2"."OBJECT_ID")

select /*+ leading(t2,t3,t1) */  t1.object_id,t1.object_name,t2.owner,t3.STATUS

from lixia.t1 ,lixia.t2,lixia.t3

where  t1.object_id=t2.object_id

and  t2.object_id=t3.object_id

and  t1.object_id <= 178717

and  t3.object_id=20;

Plan hash value: 3080117625

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

| Id  | Operation                     | Name     | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |

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

|   0 | SELECT STATEMENT              |          |      1 |        |       |     5 (100)|          |   1089 |00:00:00.13 |     173 |     10 |       |       |          |

|   1 |  MERGE JOIN CARTESIAN         |          |      1 |      1 |    53 |     5   (0)| 00:00:01 |   1089 |00:00:00.13 |     173 |     10 |       |       |          |

|   2 |   NESTED LOOPS                |          |      1 |      1 |    23 |     4   (0)| 00:00:01 |   1089 |00:00:00.12 |     170 |      5 |       |       |          |

|   3 |    TABLE ACCESS BY INDEX ROWID| T2       |      1 |      1 |    11 |     2   (0)| 00:00:01 |      1 |00:00:00.11 |       3 |      5 |       |       |          |

|*  4 |     INDEX UNIQUE SCAN         | PK_T2    |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.09 |       2 |      4 |       |       |          |

|   5 |    TABLE ACCESS BY INDEX ROWID| T3       |      1 |      1 |    12 |     2   (0)| 00:00:01 |   1089 |00:00:00.01 |     167 |      0 |       |       |          |

|*  6 |     INDEX RANGE SCAN          | IDX_T3_2 |      1 |      1 |       |     1   (0)| 00:00:01 |   1089 |00:00:00.01 |      81 |      0 |       |       |          |

|   7 |   BUFFER SORT                 |          |   1089 |      1 |    30 |     3   (0)| 00:00:01 |   1089 |00:00:00.01 |       3 |      5 |  2048 |  2048 | 2048  (0)|

|   8 |    TABLE ACCESS BY INDEX ROWID| T1       |      1 |      1 |    30 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |      5 |       |       |          |

|*  9 |     INDEX UNIQUE SCAN         | PK_T1    |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       2 |      0 |       |       |          |

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

Predicate Information (identified by operation id):

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

4 - access("T2"."OBJECT_ID"=20)

6 - access("T3"."OBJECT_ID"=20)

9 - access("T1"."OBJECT_ID"=20)

上面这条SQL基数估算不准，估算为一条实际有1089条记录，但执行计划是正确的。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21582653/viewspace-2127548/，如需转载，请注明出处，否则将追究法律责任。

科技每日推送
关注
多步骤复杂 SQL 优化思路
terry_chengq的博客
12-18 1553
原文链接：http://c.raqsoft.com.cn/article/1576562814244?r=CGQ 原文链接可以查看更多更详细的sql优化示例 问题 先看数据：deliver 表是主表，一个客户会发生多次投递行为： deliverItem 表是从表，一个投递行为有多个投递项，delivered 是投递状态（1 表示未完成，2 表示投递完成）： 需求是统计每个客户下，...
SQL优化 第五章 表连接方法
songjian1104的专栏
06-09 593
参考资料： 本系列博客主要参考资料有CUUG冉乃纲老师数据库教学笔记，《SQL优化核心思想》（罗炳森，黄超，钟侥著），《PostgreSQL技术内幕：查询优化深度探索》（张树杰著），排名不分先后。 个人认为SQL优化有两大招，第一招，正确使用索引，这种方法简单快捷；第二招，就是更改表连接方法，这个有时候会比较复杂，很多时候，我们在和外连接，半连接，反连接，标量子查询做斗争。 下面是本章节...
oracle连接多张表优化,oracle 多表查询优化
weixin_29469949的博客
04-04 1890
ORACLE中的高速缓冲用于保存查询过的sql语句，单只对单表查询起作用单表查询优化：写查询语句时尽量统一规范，只要有一点不一样，都不能使用高速缓冲。多表优化：(1)FROM子句后面的表顺序ORACLE在解析sql语句的时候对FROM子句后面的表名是从右往左解析的，在对多表查询中，一定要把小表写在最右边，例如tableA有100w条记录 ，tableB有1w条记录select count(*) f...
SQL调优的最佳实践
最新发布
weixin_50737119的博客
08-24 758
sql优化的实践
oracle多表联立计算优化,多表关联SQL优化
weixin_42207707的博客
04-11 457
目前SQL如下：selectb.object_namefromtest_tab1a,test_tab2bwherea.object_name='TAB'anda.data_object_id=b.data_object_id在该语句中，首先对各个表的数据量进行分析。通过以上数据可以得出，由于data_object_id连接列是未建有索引，所以在扫描TEST_TAB2这张表时...
SQL表连接优化总结
thundermeng的博客
01-18 1442
SQL表连接优化总结 1． innerjoin  内联 返回匹配的数据 select * from tb1 inner join tb2 on tb1.c1=tb2.c1; 或 select * from tb1,tb2 where tb1.c1=tb2.c1; 2． leftjoin  左联 左边返回全部的数据，右边返回匹配的数据，右边没有的数据则返回NULL select * fr
Oracle 表三种连接方式使用介绍(sql优化)
09-10
这些连接方式各有特点，适用于不同的情景，理解它们的工作原理和适用条件对于SQL查询优化至关重要。 1. **Nested Loop Join** Nested Loop Join（嵌套循环连接）适用于被连接的数据子集较小的情况。它会遍历驱动...
SQL优化之针对count、表的连接顺序、条件顺序、in及exist的优化
09-10
本文将深入探讨四个关键的SQL优化策略：count、表的连接顺序、条件顺序以及in和exist的使用。 首先，让我们关注`count()`函数的优化。通常认为`count(*)`统计所有行，而`count(列名)`只计算指定列的非空值。许多人...
ORACLE数据库SQL优化---表连接类型.docx
12-30
Oracle数据库的SQL优化是数据库管理员和开发人员关注的重要领域，特别是在处理复杂的表连接时。本文主要探讨了Oracle数据库中表连接类型的优化，包括连接顺序、连接方法和访问单表的方法。 1. 表连接顺序： Oracle...
Oracle多表连接优化策略
"Oracle 多表查询优化涉及数据库性能、执行路径、高速缓冲区和SQL语句的共享机制。" Oracle数据库在处理多表查询时，其效率与单表查询相比有显著差异，主要因为Oracle对简单表提供了高速缓冲（Cache Buffering）...
Oracle中SQL语句连接字符串的符号使用介绍
12-15
在Oracle数据库中，SQL语句连接字符串的主要符号是双竖线 `||`，它用于将两个或多个字符串拼接在一起。这个操作符的功能类似于其他数据库系统中的 `CONCAT` 函数，但更简洁且易于使用。以下是对Oracle中SQL语句连接...
SQL优化-多表优化
qq_16268979的博客
06-09 498
前面已经写了一篇单表优化的博客，这里继续关于多表优化的内容。其实，SQL优化路很长，我也是初学，在这里记录的优化方案都是比较基础的，但是日常写SQL的过程中如果能够根据优化知识注意SQL的书写，那性能肯定是会有一定提升的。一起努力吧！ 这里附上官网上关于优化的描述（我用的5.7，这里就附上5.7）： https://dev.mysql.com/doc/refman/5.7/en/optimization.html 一、两表优化： CREATE TABLE teacher2( tid int(4) prim
多表连接SQL优化如何处理
cuixie2370的博客
01-15 170
select a.inst_id, count(*) from( SELECT L.USER_ID, R.RESP_APPL_ID,R.RESPONSIBILITY_ID, F...
多重表关联怎么关联_Mysql表关联时执行顺序
weixin_39640221的博客
12-09 263
left join 是left outer join的简写，它的全称是左外连接，是外连接中的一种。左(外)连接，左表(a_table)的记录将会全部表示出来，而右表(b_table)只会显示符合搜索条件的记录。右表记录不足的地方均为NULL。01左连接：left joinwithtemp1as(select1asid,12asnumunionselect2asi...
【Oracle】看懂执行计划之表连接
As before 如初
06-10 1093
简介   表连接，顾名思义多个表之间用用连接条件连接在一起，使用表连接的目标SQL其目的就是从多个表获取存储在这些表中不同维度的数据。 解析表连接SQL的要素 目标SQL文本的写法 SQL文本的写法决定表连接的类型：内连接、外连接、自连接 自连接：同一张表内的字段做连接 表连接顺序 不管目标SQL中有多少个表做连接，Oracle在实际执行该SQL时都只能先两两 连接，再依次同剩下的表做连接，直到目标SQL中所有的表都已连接完毕。 表连接方法 排序合并连接、嵌套循环连接、哈希连接和
greenplum 5.13 多表 join
一名数据库爱好者的专栏
11-15 2294
os: ubuntu 16.04 db: greenplum 5.13 192.168.0.99 mas 192.168.0.100 sla 192.168.0.101 seg1 192.168.0.102 seg2 目前 greenplum 在创建表时都需要手动指定分布列，如果不明确指定分布列，默认第一列为分布列。 暂时还没有类似 pgxl，citus的复制表，即表在所有节点上...
mysql多个连接执行顺序_MySQL 查询语句执行顺序疑问：多表关联时会先生成笛卡尔乘积？...
weixin_39614754的博客
01-21 457
背景之前一直以为 MySQL 的多表关联查询语句是首先对 FROM 语句的前两张表执行笛卡尔积，产生一张虚拟表，然后使用 ON 过滤和 OUTER JOIN 添加外部行，再使用过滤后的虚拟表跟第三张表进行笛卡尔乘积，重复执行上述步骤。下面是从网上搜到一些比较热门的 SQL 执行顺序的文章，大家应该很熟悉吧，尤其是下面那张鱼骨图。摘自：步步深入：MySQL 架构总览->查询执行流程->S...
oracle 数据迁移 怎么保证 和原表的数据顺序一致_如何做好数据迁移？
weixin_39692045的博客
12-04 556
随着业务发展，系统不断演化，应用职责也会随之发生变化，同时就会伴随着系统数据的改造、迁移、拆分、归档等需求，下面分享一下做数据迁移的一些经验。第一阶段，做好背景了解统计迁移范围包括但不限于迁移涉及的表、接口、接口调用上游业务方、如何测试等信息，最好做文档记录输出迁移范围。第二阶段，分析第一阶段结果确定迁移方案这里的分析需要注意的重点有两个：1 接口的迁移：接口的迁移如何保证平滑迁移，也就是上游调...
Oracle Sql高级编程 第2章 SQL 执行过程
Nathan Yan
06-30 982
Oracle架构基础 实例由系统全局内存区域(SGA）以及一系列的后台进程组成。每一个连接到数据库的用户都是通过一个客户端进程来进行管理的。客户端进程是与服务器进程相连接的。每个服务器进程都会被分配一块私有的内存区域，称为程序共享内存区域或进程共享内存区(PGA)。 SGA-共享池 共享池是内存中最关键的部分之一，特别是对于SQL的执行来说。你写SQL语句的方法并不仅仅会影响到这一句
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值