`

收获，不止Oracle之表连接

 
阅读更多 
DROP TABLE T1 PURGE;
DROP TABLE T2 PURGE;

CREATE TABLE T1
(
ID NUMBER NOT NULL,
N NUMBER,
CONTENTS VARCHAR2(4000)
);

CREATE TABLE T2
(
ID NUMBER NOT NULL,
T1_ID NUMBER NOT NULL,
N NUMBER,
CONTENTS VARCHAR2(4000)
);

EXECUTE DBMS_RANDOM.SEED(0);

INSERT INTO T1
SELECT ROWNUM,ROWNUM,DBMS_RANDOM.STRING('A',50)
FROM DUAL
CONNECT BY LEVEL <= 100
ORDER BY DBMS_RANDOM.RANDOM;

INSERT INTO T2
SELECT ROWNUM,ROWNUM,ROWNUM,DBMS_RANDOM.STRING('B',50)
FROM DUAL
CONNECT BY LEVEL <= 100000
ORDER BY DBMS_RANDOM.RANDOM;

admin@ORCL> SELECT COUNT(*) FROM T1;

  COUNT(*)
----------
       100

admin@ORCL> SELECT COUNT(*) FROM T2;

  COUNT(*)
----------
    100000
    
admin@ORCL> SELECT /*+LEADING(T1) USE_NL(T2)*/*
  2  FROM T1,T2
  3  WHERE T1.ID = T2.T1_ID

--STARTS指的表访问次数,E-ROWS指的每个操作估算返回行数，A-ROWS指每步操作真实返回行数
--Leading 表示强制先访问T1表，也就是把T1作为驱动表
--执行之前需要set serveroutput off
--要显示比较详细的执行计划，两种方式：
--1.alter session set statistics=ALL
--2.执行的语句需要加上/*+ gather_plan_statistics */
admin@ORCL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));


-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   1 |  NESTED LOOPS      |      |      1 |    100 |    100 |00:00:00.81 |     100K|
|   2 |   TABLE ACCESS FULL| T1   |      1 |    100 |    100 |00:00:00.01 |      14 |
|*  3 |   TABLE ACCESS FULL| T2   |    100 |      1 |    100 |00:00:00.81 |     100K|
-------------------------------------------------------------------------------------

--下面的结果可以看出
--在嵌套循环中，驱动表返回多少条记录，被驱动表就访问多少次
admin@ORCL> SELECT /*+LEADING(T1) USE_NL(T2)*/* FROM T1,T2 WHERE T1.ID = T2.T1_ID AND T1.N = 9999999;


admin@ORCL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   1 |  NESTED LOOPS      |      |      1 |      1 |      0 |00:00:00.01 |       7 |
|*  2 |   TABLE ACCESS FULL| T1   |      1 |      1 |      0 |00:00:00.01 |       7 |
|*  3 |   TABLE ACCESS FULL| T2   |      0 |      1 |      0 |00:00:00.01 |       0 |
-------------------------------------------------------------------------------------

--来测试下，hash join的执行计划
--得出结论，在Hash连接中，驱动表与被驱动表都只会访问0次或1次
SELECT /*+LEADING(T1) USE_HASH(T2)*/* FROM T1,T2
WHERE T1.ID = T2.T1_ID;

admin@ORCL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN         |      |      1 |    100 |    100 |00:00:00.06 |    1019 |   741K|   741K| 1134K (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |    100 |    100 |00:00:00.01 |       7 |       |       |          |
|   3 |   TABLE ACCESS FULL| T2   |      1 |    100K|    100K|00:00:00.01 |    1012 |       |       |          |
----------------------------------------------------------------------------------------------------------------


--查看排序合并的表访问次数
--T1,T2表只会访问0次或1次
--另外一个重要的概念：排序合并连接根本没有驱动表的概念，而嵌套循环和哈希连接要考虑驱动和被驱动的情况
SELECT /*+ ORDERED USE_MERGE(T2) */*
FROM T1,T2
WHERE T1.ID = T2.T1_ID;

admin@ORCL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
|   1 |  MERGE JOIN         |      |      1 |    100 |    100 |00:00:00.08 |    1012 |       |       |          |
|   2 |   SORT JOIN         |      |      1 |    100 |    100 |00:00:00.01 |       7 |  9216 |  9216 | 8192  (0)|
|   3 |    TABLE ACCESS FULL| T1   |      1 |    100 |    100 |00:00:00.01 |       7 |       |       |          |
|*  4 |   SORT JOIN         |      |    100 |    100K|    100 |00:00:00.08 |    1005 |  9124K|  1177K| 8110K (0)|
|   5 |    TABLE ACCESS FULL| T2   |      1 |    100K|    100K|00:00:00.01 |    1005 |       |       |          |
-----------------------------------------------------------------------------------------------------------------

已选择22行。


--对比下，驱动表选择的性能比较
--T1在前时产生了100K的逻辑读，而t2在前时产生了700K的逻辑读
--证明了嵌套循环需要特别注意驱动表顺序，小的结果集先访问，大的结果集后访问。
SELECT /*+LEADING(T1) USE_NL(T2)*/*
    FROM T1,T2
    WHERE T1.ID = T2.T1_ID

-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   1 |  NESTED LOOPS      |      |      1 |    100 |    100 |00:00:00.80 |     100K|
|   2 |   TABLE ACCESS FULL| T1   |      1 |    100 |    100 |00:00:00.01 |      14 |
|*  3 |   TABLE ACCESS FULL| T2   |    100 |      1 |    100 |00:00:00.80 |     100K|
-------------------------------------------------------------------------------------

SELECT /*+LEADING(T2) USE_NL(T1)*/*
    FROM T1,T2
    WHERE T1.ID = T2.T1_ID

-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   1 |  NESTED LOOPS      |      |      1 |    100 |    100 |00:00:01.43 |     701K|
|   2 |   TABLE ACCESS FULL| T2   |      1 |    100K|    100K|00:00:00.01 |    1012 |
|*  3 |   TABLE ACCESS FULL| T1   |    100K|      1 |    100 |00:00:01.37 |     700K|
-------------------------------------------------------------------------------------

--加上过滤条件后，看下执行计划.
--发现T2的 TABLE ACCESS FULL步骤的逻辑读从700k减少到1006,这也说明了T2的全表访问实际上是依据T1的返回记录数去访问T2表

SELECT /*+LEADING(T1) USE_NL(T2)*/*
    FROM T1,T2
    WHERE T1.ID = T2.T1_ID AND T1.n = 19;
    
-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   1 |  NESTED LOOPS      |      |      1 |      1 |      1 |00:00:00.02 |    1014 |
|*  2 |   TABLE ACCESS FULL| T1   |      1 |      1 |      1 |00:00:00.01 |       8 |
|*  3 |   TABLE ACCESS FULL| T2   |      1 |      1 |      1 |00:00:00.02 |    1006 |
-------------------------------------------------------------------------------------

--对比下hash join的驱动表，执行效率

SELECT /*+LEADING(T1) USE_HASH(T2)*/*
    FROM T1,T2
    WHERE T1.ID = T2.T1_ID
----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN         |      |      1 |    100 |    100 |00:00:00.06 |    1019 |   741K|   741K| 1133K (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |    100 |    100 |00:00:00.01 |       7 |       |       |          |
|   3 |   TABLE ACCESS FULL| T2   |      1 |    100K|    100K|00:00:00.01 |    1012 |       |       |          |
----------------------------------------------------------------------------------------------------------------

SELECT /*+LEADING(T2) USE_HASH(T1)*/*
    FROM T1,T2
    WHERE T1.ID = T2.T1_ID

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN         |      |      1 |    100 |    100 |00:00:00.10 |    1019 |  9471K|  1956K|   10M (0)|
|   2 |   TABLE ACCESS FULL| T2   |      1 |    100K|    100K|00:00:00.01 |    1005 |       |       |          |
|   3 |   TABLE ACCESS FULL| T1   |      1 |    100 |    100 |00:00:00.01 |      14 |       |       |          |
----------------------------------------------------------------------------------------------------------------

--对比上面的没有过滤条件的hash join
--逻辑读几乎一样,但排序使用的内存小了不少,而T1和T2的逻辑读都没有发生变化
--是否意味着T1,T2表时载入内存后，进行过滤后，然后进行排序？
SELECT /*+LEADING(T1) USE_HASH(T2)*/*
    FROM T1,T2
    WHERE T1.ID = T2.T1_ID AND T1.N = 19;
    
----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN         |      |      1 |      1 |      1 |00:00:00.07 |    1013 |   741K|   741K|  289K (0)|
|*  2 |   TABLE ACCESS FULL| T1   |      1 |      1 |      1 |00:00:00.01 |       7 |       |       |          |
|   3 |   TABLE ACCESS FULL| T2   |      1 |    100K|    100K|00:00:00.01 |    1006 |       |       |          |
----------------------------------------------------------------------------------------------------------------


--排序合并的表驱动顺序

SELECT /*+LEADING(T1) USE_MERGE(T2)*/*
    FROM T1,T2
    WHERE T1.ID = T2.T1_ID AND T1.N = 19;
    
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
|   1 |  MERGE JOIN         |      |      1 |      1 |      1 |00:00:00.08 |    1012 |       |       |          |
|   2 |   SORT JOIN         |      |      1 |      1 |      1 |00:00:00.01 |       7 |  2048 |  2048 | 2048  (0)|
|*  3 |    TABLE ACCESS FULL| T1   |      1 |      1 |      1 |00:00:00.01 |       7 |       |       |          |
|*  4 |   SORT JOIN         |      |      1 |    100K|      1 |00:00:00.08 |    1005 |  9124K|  1177K| 8110K (0)|
|   5 |    TABLE ACCESS FULL| T2   |      1 |    100K|    100K|00:00:00.01 |    1005 |       |       |          |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("T1"."N"=19)
   4 - access("T1"."ID"="T2"."T1_ID")
       filter("T1"."ID"="T2"."T1_ID")
--对比上面的结果，消耗资源一摸一样，说明排序合并表驱动表是哪一个不影响
SELECT /*+LEADING(T2) USE_MERGE(T1)*/*
    FROM T1,T2
    WHERE T1.ID = T2.T1_ID AND T1.N = 19;

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
|   1 |  MERGE JOIN         |      |      1 |      1 |      1 |00:00:00.12 |    1012 |       |       |          |
|   2 |   SORT JOIN         |      |      1 |    100K|     20 |00:00:00.11 |    1005 |  9124K|  1177K| 8110K (0)|
|   3 |    TABLE ACCESS FULL| T2   |      1 |    100K|    100K|00:00:00.01 |    1005 |       |       |          |
|*  4 |   SORT JOIN         |      |     20 |      1 |      1 |00:00:00.01 |       7 |  2048 |  2048 | 2048  (0)|
|*  5 |    TABLE ACCESS FULL| T1   |      1 |      1 |      1 |00:00:00.01 |       7 |       |       |          |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T1"."ID"="T2"."T1_ID")
       filter("T1"."ID"="T2"."T1_ID")
   5 - filter("T1"."N"=19)
--去掉过滤条件，发现只是T1表的表访问逻辑读增加了,T1表的cost仍然一样
SELECT /*+LEADING(T1) USE_MERGE(T2)*/*
    FROM T1,T2
    WHERE T1.ID = T2.T1_ID

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
|   1 |  MERGE JOIN         |      |      1 |    100 |    100 |00:00:00.10 |    1012 |       |       |          |
|   2 |   SORT JOIN         |      |      1 |    100 |    100 |00:00:00.01 |       7 |  9216 |  9216 | 8192  (0)|
|   3 |    TABLE ACCESS FULL| T1   |      1 |    100 |    100 |00:00:00.01 |       7 |       |       |          |
|*  4 |   SORT JOIN         |      |    100 |    100K|    100 |00:00:00.10 |    1005 |  9124K|  1177K| 8110K (0)|
|   5 |    TABLE ACCESS FULL| T2   |      1 |    100K|    100K|00:00:00.01 |    1005 |       |       |          |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T1"."ID"="T2"."T1_ID")
       filter("T1"."ID"="T2"."T1_ID")
       
--测试下，合并排序只取部分字段
--会发现表T2的内存排序大小从8110K降到了1621K。应该是由于查询中没有用到T2表的字段，只是连接时用到了T2.T1_ID
--所以只缓存此字段即可
SELECT /*+LEADING(T1) USE_MERGE(T2)*/T1.ID
    FROM T1,T2
    WHERE T1.ID = T2.T1_ID AND T1.N = 19;

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
|   1 |  MERGE JOIN         |      |      1 |      1 |      1 |00:00:00.07 |    1012 |       |       |          |
|   2 |   SORT JOIN         |      |      1 |      1 |      1 |00:00:00.01 |       7 |  2048 |  2048 | 2048  (0)|
|*  3 |    TABLE ACCESS FULL| T1   |      1 |      1 |      1 |00:00:00.01 |       7 |       |       |          |
|*  4 |   SORT JOIN         |      |      1 |    100K|      1 |00:00:00.07 |    1005 |  1824K|   650K| 1621K (0)|
|   5 |    TABLE ACCESS FULL| T2   |      1 |    100K|    100K|00:00:00.01 |    1005 |       |       |          |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("T1"."N"=19)
   4 - access("T1"."ID"="T2"."T1_ID")
       filter("T1"."ID"="T2"."T1_ID")

--来测试下hash表，是不是同样的效果
--结果并不是这样，对于hash join,虽然减少了查询字段，但资源的消耗没有减少。这点有点费解
SELECT /*+LEADING(T1) USE_HASH(T2)*/T1.ID
    FROM T1,T2
    WHERE T1.ID = T2.T1_ID
    
----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN         |      |      1 |    100 |    100 |00:00:00.07 |    1019 |  1066K|  1066K| 1149K (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |    100 |    100 |00:00:00.01 |       7 |       |       |          |
|   3 |   TABLE ACCESS FULL| T2   |      1 |    100K|    100K|00:00:00.01 |    1012 |       |       |          |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."ID"="T2"."T1_ID")


--来看下hash join与排序合并连接的限制
--hash连接不支持不等值连接，不支持><连接方式，不支持like
SELECT /*+LEADING(T1) USE_HASH(T2)*/T1.ID
    FROM T1,T2
    WHERE T1.ID <> T2.T1_ID AND T1.N = 19;

-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   1 |  NESTED LOOPS      |      |      1 |    100K|  99999 |00:00:00.10 |    7613 |
|*  2 |   TABLE ACCESS FULL| T1   |      1 |      1 |      1 |00:00:00.01 |       8 |
|*  3 |   TABLE ACCESS FULL| T2   |      1 |    100K|  99999 |00:00:00.01 |    7605 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("T1"."N"=19)
   3 - filter("T1"."ID"<>"T2"."T1_ID")



--排序合并连接不支持<>连接方式，也不支持like,但支持><之类的连接方式
SELECT /*+LEADING(T1) USE_MERGE(T2)*/*
    FROM T1,T2
    WHERE T1.ID <> T2.T1_ID AND T1.N = 19;


SELECT /*+LEADING(T1) USE_MERGE(T2)*/*     FROM T1,T2     WHERE T1.ID <>
T2.T1_ID AND T1.N = 19

-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   1 |  NESTED LOOPS      |      |      1 |    100K|  99999 |00:00:00.10 |    7613 |
|*  2 |   TABLE ACCESS FULL| T1   |      1 |      1 |      1 |00:00:00.01 |       8 |
|*  3 |   TABLE ACCESS FULL| T2   |      1 |    100K|  99999 |00:00:00.01 |    7605 |
-------------------------------------------------------------------------------------


--学习下如何优化表连接
--什么时候适合于NL
/*
两表关联返回少量记录，最佳情况是驱动表仅仅返回1条或者少量几条，而被驱动表也仅仅匹配少量几条数据，这种情况即使T1表和
T2表奇大无比，也是非常迅速的。
调优方式：驱动表的限制条件上有索引
被驱动表的连接条件上所在的列也有索引
*/


SELECT /*+LEADING(T1) USE_NL(T2)*/T1.ID
    FROM T1,T2
    WHERE T1.ID = T2.T1_ID AND T1.n = 19;

-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   1 |  NESTED LOOPS      |      |      1 |      1 |      1 |00:00:00.02 |    1014 |
|*  2 |   TABLE ACCESS FULL| T1   |      1 |      1 |      1 |00:00:00.01 |       8 |
|*  3 |   TABLE ACCESS FULL| T2   |      1 |      1 |      1 |00:00:00.02 |    1006 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("T1"."N"=19)
   3 - filter("T1"."ID"="T2"."T1_ID")

--创建索引后，返现访问T1表的效率略有提示，但整体效率仍没有太大提高。这是因为表T1只有100条数据
CREATE INDEX IDX_T1_N ON T1(N);
SELECT /*+LEADING(T1) USE_NL(T2)*/T1.ID
    FROM T1,T2
    WHERE T1.ID = T2.T1_ID AND T1.n = 19;

---------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   1 |  NESTED LOOPS                |          |      1 |      1 |      1 |00:00:00.01 |    1009 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1       |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  3 |    INDEX RANGE SCAN          | IDX_T1_N |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  4 |   TABLE ACCESS FULL          | T2       |      1 |      1 |      1 |00:00:00.01 |    1006 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("T1"."N"=19)
   4 - filter("T1"."ID"="T2"."T1_ID")

--在T2表的连接字段上创建索引,执行效率高了很多
CREATE INDEX IDX_T2_ID ON T2(T1_ID);
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-------------------------------------------------------------------------------------------------------------
|   1 |  NESTED LOOPS                |           |      1 |      1 |      1 |00:00:00.03 |       6 |   4 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1        |      1 |      1 |      1 |00:00:00.01 |       3 |   0 |
|*  3 |    INDEX RANGE SCAN          | IDX_T1_N  |      1 |      1 |      1 |00:00:00.01 |       2 |   0 |
|*  4 |   INDEX RANGE SCAN           | IDX_T2_ID |      1 |      1 |      1 |00:00:00.03 |       3 |   4 |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("T1"."N"=19)
   4 - access("T1"."ID"="T2"."T1_ID")

--hash连接，排序合并连接
/*
对于HASH连接或者排序合并索引，可以考虑通过PGA参数的调整避免排序尺寸过大在磁盘上排序
HASH连接，需要在HASH_AREA_SIZE中完成
特别注意：连接条件的索引对它们是起不到传递的作用
但若限制条件上如果有合适的索引可以快速检索到少量数据，也是可以提升性能的。
因此hash 连接，可以理解为单表索引的设置技巧

对于排序合并连接与hash连接有点差别：
排序合并连接上的连接条件虽然没有检索作用，但却有消除排序的作用

另外Oracle对于排序合并连接有点缺陷，当两个表都有索引时，Oracle只能消除一张表的排

*/

CREATE INDEX IDX_T1_ID ON T1(ID);
DROP INDEX IDX_T1_N;
DROP INDEX IDX_T1_ID

 SELECT /*+LEADING(T1) USE_MERGE(T2)*/*
 FROM T1,T2
 WHERE T1.ID = T2.T1_ID; 
    


-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------
|   1 |  MERGE JOIN                  |           |      1 |      1 |      1 |00:00:00.07 |    1009 |    |          |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| T1        |      1 |      1 |      1 |00:00:00.01 |       4 |    |          |          |
|   3 |    INDEX FULL SCAN           | IDX_T1_ID |      1 |    100 |    100 |00:00:00.01 |       2 |    |          |          |
|*  4 |   SORT JOIN                  |           |      1 |    100K|      1 |00:00:00.07 |    1005 |  9124K|  1177K| 8110K (0)|
|   5 |    TABLE ACCESS FULL         | T2        |      1 |    100K|    100K|00:00:00.01 |    1005 |    |          |          |
-------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("T1"."N"=19)
   4 - access("T1"."ID"="T2"."T1_ID")
       filter("T1"."ID"="T2"."T1_ID")

 

分享到：
| 收获，不止Oracle之索引篇(2)
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    收获,不止Oracle01

    　这部分知识就是oracle的物理体系结构、逻辑体系结构、表、索引以及表连接五大部分。通过阅读这些章节，读者将会在最短时间内以一种有史以来最轻松的方式，完成对oracle数据库的整体认识，不仅能在工作中解决常规...

    收获不止oracle 两部合集 3-1

    这部分知识就是Oracle的物理体系结构、逻辑体系结构、表、索引以及表连接五大部分。通过阅读这些章节，读者将会在最短时间内以一种有史以来最轻松的方式，完成对Oracle数据库的整体认识，不仅能在工作中解决常规问题...

    收获不止oracle 两部合集 3-2

    这部分知识就是Oracle的物理体系结构、逻辑体系结构、表、索引以及表连接五大部分。通过阅读这些章节，读者将会在最短时间内以一种有史以来最轻松的方式，完成对Oracle数据库的整体认识，不仅能在工作中解决常规问题...

    收获不止oracle 两部合集 3-3

    这部分知识就是Oracle的物理体系结构、逻辑体系结构、表、索引以及表连接五大部分。通过阅读这些章节，读者将会在最短时间内以一种有史以来最轻松的方式，完成对Oracle数据库的整体认识，不仅能在工作中解决常规问题...

    收获不知Oracle

    3.2.4 农场之表空间的分类 93 3.2.4.1 表空间与系统农场93 3.2.4.2 表空间与临时农场93 3.2.4.3 表空间与回滚农场94 3.2.5 逻辑结构之初次体会 94 3.2.5.1 逻辑结构之BLOCK 94 3.2.5.2 逻辑结构之TABLESPACE 95 3.2....

    收获不止SQL优化

    11.3　从案例学表连接优化要点 （三刀三斧四式走天下） 317 11.3.1　一次Nested Loops Join的优化全过程 318 11.3.2　一次Hash Join 的 优化全过程 320 11.3.3　一次 Merge Sort Join 的优化全过程 324 11.3.4　一次...

    收获，不止SQL优化 PDF 带书签 第三部分

    教你体系结构、逻辑结构、表设计、索引设计、表连接这五大要领。这么多套路，这又是要干嘛？别急，这是教你如何解决问题，准确地说，是如何不改写即完成SQL优化。 随后《收获，不止SQL优化——抓住SQL的本质》指引...

    收获，不止SQL优化

    教你体系结构、逻辑结构、表设计、索引设计、表连接这五大要领。这么多套路，这又是要干嘛？别急，这是教你如何解决问题，准确地说，是如何不改写即完成SQL优化。, 随后《收获，不止SQL优化——抓住SQL的本质》指引...

    JavaWeb线上餐馆系统部分源码

    收获：做完请此项目，能够熟练掌握JavaWeb的内容，且能够将Ajax烂熟于心，明白Ajax在项目中是如何运用的，且能够知道一个项目是如何从无到有，如何开发的。 阅读建议： 此资源虽说只是一个简单的JavaWeb项目，但它...

    JAVA自学之路

    所以，动手吧，跟着做，一行一行的跟着敲，一个项目敲完了，比如坦克，再试着加一些自己的功能，按照自己的思路敲一些代码，收获远比干听大的多。 如果遇到，暂时对于一些思路不能理解的同学，我一般都会劝告说，...

Magicbox
Global site tag (gtag.js) - Google Analytics