`

收获，不止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)
评论
发表评论

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

相关推荐

    收获不止Oracle--超清扫描PDF

    2. 优化SQL语句：对SQL查询语句进行简化和重写，减少不必要的计算和处理，例如减少子查询的使用，利用连接（JOIN）来替代子查询。 3. 正确使用索引：根据查询条件和数据分布创建合适的索引，并及时更新或删除不再...

    收获,不止Oracle01

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

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

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

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

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

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

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

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

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

    收获不知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优化

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

    Oracle初学者指南

    Oracle数据库是全球广泛使用的大型关系型数据库管理系统，尤其在企业级应用中占据重要地位。作为初学者，了解Oracle...Oracle的学习是一段充满挑战和收获的旅程，祝你在学习过程中不断进步，成为一名出色的数据库专家。

    通过JDBC连接oracle数据库的十大技巧

    通过JDBC连接Oracle数据库的十大技巧，为Java开发者提供了深入理解和优化数据库连接的关键知识点。以下是对这十大技巧的详细解析： ### 技巧一：在客户端软件开发中使用Thin驱动程序 Oracle为JDBC提供了多种驱动...

    oracle的原文书籍

    Oracle是世界上最广泛使用的数据库管理系统之一，它在企业级数据存储、管理和分析方面有着卓越的表现。Oracle的原文书籍通常包含了深入的技术细节，对于学习和掌握Oracle技术具有极高的价值。以下是一些从标题和描述...

    ORACLE命令行查看实际的执行计划

    在SQLPLUS中输入此命令，系统会记录接下来执行的SQL语句的详细信息，包括解析、执行和收获阶段的信息。 3. **执行需解释的SQL语句**： 在设置了AUTOTRACE之后，你可以输入你想分析的SQL语句。例如，`EXPLAIN PLAN ...

    oracle初学者指南 结局篇

    11. **数据库连接**：介绍多种连接Oracle数据库的方式，如SQL*Plus、ODBC、JDBC等，并演示如何建立和管理连接。 12. **数据库设计与架构**：讲解数据库设计的原则，如范式理论，以及如何构建高效、可扩展的数据库...

    oracle艰辛学习过程

    Oracle数据库系统是全球广泛使用的大型关系型数据库管理系统之一，它以其强大的功能、高效的数据处理能力和高度的稳定性赢得了业界的认可。"Oracle艰辛学习过程"这个标题暗示了掌握Oracle技术需要投入大量的时间和...

    Oracle数据库系统应用实例集锦与编程技巧

    Oracle数据库系统是全球广泛使用的大型关系型数据库管理系统之一，它为各类企业和组织提供了高效的数据存储、管理和处理能力。本资源“Oracle数据库系统应用实例集锦与编程技巧”是一份珍贵的资料，虽然由于是扫描版...

    Oracle课程目录

    - **LEFT JOIN和RIGHT JOIN的区别**：比较这两种连接方式的不同之处。 - **连接条件**：说明LEFT JOIN和RIGHT JOIN中的连接条件如何定义。 - **应用案例**：给出LEFT JOIN和RIGHT JOIN的实际应用场景。 #### 19. ...

    高性能SQL优化

    执行计划是评估SQL性能的重要工具，它显示了数据的访问方式（如全表扫描、索引访问）、连接操作方式（如嵌套循环、散列连接、排序合并连接）等。 优化器在SQL执行中起着决定性作用。Oracle有两种优化器：基于规则的...

    数据库组建动态网站（JSP+Oracle）

    4. **JSP 与数据库交互**：通过 JSP 使用 JDBC 连接 Oracle 数据库，执行 SQL 语句，例如 SELECT、INSERT、UPDATE 和 DELETE。学会使用 PreparedStatement 防止 SQL 注入，并处理结果集以展示在网页上。 5. **会话...

    UNIX、Oracle、CoreJava语言基础实训心得.docx

    - **SQL命令**：在Oracle数据库管理中，主要涉及到使用SQL语句进行数据操作，如通过JDBC（Java Database Connectivity）连接数据库，执行CRUD（Create, Read, Update, Delete）操作，创建表、插入数据、查询数据和...

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