`

收获,不止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

    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....

    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)操作,创建表、插入数据、查询数据和...

    UNIX、Oracle、Core Java 语言基础实训心得.doc

    特别是,我了解了如何使用JDBC(Java Database Connectivity)连接Oracle数据库,这是Java程序与数据库通信的标准接口。 在Core Java部分,我深入学习了Java编程的基础,包括类、对象、字符串处理、输入/输出(I/O...

    【数据库技术】表设计优化与SQL性能提升:深入解析表设计、分区表、全局临时表及优化案例

    ③学会通过合理的表设计优化SQL查询性能,如减少全表扫描、避免不必要的表连接;④利用提供的监控脚本,及时发现并解决表设计中的潜在问题。 其他说明:本文不仅提供了理论知识,还结合了大量的实际案例和SQL脚本,...

    数据库实验指导书

    - **Oracle客户端配置**:学会配置Oracle客户端,如SQL*Plus、PL/SQL Developer或Oracle Enterprise Manager,以便能够连接到Oracle数据库服务器并执行SQL命令。 **知识点2:数据表的创建与管理** - **创建数据库表...

    藏经阁-DBA成长沉思录.pdf

    为解决这个问题,可以采用自连接或者临时表等方法,如文中提供的WA(Workaround)方案。 除此之外,本书还可能涵盖了其他主题,如性能优化、数据库架构设计、故障排查、备份恢复策略等,这些都是DBA日常工作中必不...

    作业说明1

    在本次的作业说明中,我们将关注点集中在数据库的使用以及如何根据个人需求调整项目,同时对项目的具体功能扩展进行了说明。首先,项目基于 PostgreSQL 数据库,这是一...希望大家能在这个大作业中有所收获,不断成长。

    数据库图形化界面navicat

    它支持多种数据库管理系统,包括MySQL、MariaDB、Oracle、SQL Server等,是IT专业人员和非专业用户进行数据库管理和维护的理想选择。 ### Navicat的功能特性 1. **多数据库连接**:Navicat允许用户同时连接到多个...

    0基础自学sql,资料包免费领,快速入门!

    此外,JOIN操作用于合并来自多个表的数据,如内连接(INNER JOIN)、左连接(LEFT JOIN)、右连接(RIGHT JOIN)和全连接(FULL JOIN),这对于处理复杂的数据关系至关重要。 SQL还支持事务处理,确保数据的一致性...

Global site tag (gtag.js) - Google Analytics