1.这个实验有以下场景和实验结论：
CBO没有索引的情况下：IN与EXISTS效率一样（IN:00:00:00.85 EXISTS:00:00:00.85）
CBO在列上创建索引后：IN与EXISTS效率一样（IN:00:00:00.16 EXISTS:00:00:00.16）
RBO没有索引的情况下：IN效率高于EXISTS（IN:00:00:01.20 EXISTS:00:01:21.50）
RBO在列上创建索引后：EXISTS效率高于IN（IN:00:18:16.76 EXISTS:00:01:04.62）
2.用到的实验SQL语句如下：
SELECT count(*)
FROM big_table a
WHERE a.object_name = 'T'
AND EXISTS
(SELECT 1
FROM big_table1 b
WHERE a.id = b.id)
/
SELECT count(*)
FROM big_table a
WHERE a.object_name = 'T'
AND a.id IN
(SELECT b.id
FROM big_table1 b)
/
SELECT /*+ RULE */ count(*)
FROM big_table1 a
WHERE a.object_name = 'T'
AND EXISTS
(SELECT 1
FROM big_table b
WHERE a.id = b.id)
/
SELECT /*+ RULE */ count(*)
FROM big_table1 a
WHERE a.object_name = 'T'
AND a.id IN
(SELECT b.id
FROM big_table b)
/
3.CBO没有索引的情况下：IN与EXISTS效率一样（IN:00:00:00.85 EXISTS:00:00:00.85）
sec@ora10g> SELECT count(*)
FROM big_table a
WHERE a.object_name = 'T'
AND EXISTS
(SELECT 1
FROM big_table1 b
WHERE a.id = b.id)
/
2 3 4 5 6 7 8
COUNT(*)
----------
86
Elapsed: 00:00:00.85
Execution Plan
----------------------------------------------------------
Plan hash value: 2715735594
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 36 | | 34170 (1)| 00:06:51 |
| 1 | SORT AGGREGATE | | 1 | 36 | | | |
|* 2 | HASH JOIN SEMI | | 100K| 3516K| 3424K| 34170 (1)| 00:06:51 |
|* 3 | TABLE ACCESS FULL| BIG_TABLE | 100K| 2246K| | 29868 (1)| 00:05:59 |
| 4 | TABLE ACCESS FULL| BIG_TABLE1 | 971K| 12M| | 2974 (1)| 00:00:36 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."ID"="B"."ID")
3 - filter("A"."OBJECT_NAME"='T')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
148401 consistent gets
0 physical reads
692 redo size
515 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
sec@ora10g> SELECT count(*)
FROM big_table a
WHERE a.object_name = 'T'
AND a.id IN
(SELECT b.id
FROM big_table1 b)
/
2 3 4 5 6 7
COUNT(*)
----------
86
Elapsed: 00:00:00.85
Execution Plan
----------------------------------------------------------
Plan hash value: 2715735594
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 36 | | 34170 (1)| 00:06:51 |
| 1 | SORT AGGREGATE | | 1 | 36 | | | |
|* 2 | HASH JOIN SEMI | | 100K| 3516K| 3424K| 34170 (1)| 00:06:51 |
|* 3 | TABLE ACCESS FULL| BIG_TABLE | 100K| 2246K| | 29868 (1)| 00:05:59 |
| 4 | TABLE ACCESS FULL| BIG_TABLE1 | 971K| 12M| | 2974 (1)| 00:00:36 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."ID"="B"."ID")
3 - filter("A"."OBJECT_NAME"='T')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
148401 consistent gets
0 physical reads
648 redo size
515 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
4.CBO在列上创建索引后：IN与EXISTS效率一样（IN:00:00:00.16 EXISTS:00:00:00.16）
create index i_big_table on big_table(object_name) parallel 8;
sec@ora10g> SELECT count(*)
FROM big_table a
WHERE a.object_name = 'T'
AND EXISTS
(SELECT 1
FROM big_table1 b
WHERE a.id = b.id)
/
2 3 4 5 6 7 8
COUNT(*)
----------
86
Elapsed: 00:00:00.16
Execution Plan
----------------------------------------------------------
Plan hash value: 290523449
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 36 | | 33983 (1)| 00:06:48 |
| 1 | SORT AGGREGATE | | 1 | 36 | | | |
|* 2 | HASH JOIN SEMI | | 100K| 3516K| 3424K| 33983 (1)| 00:06:48 |
| 3 | TABLE ACCESS BY INDEX ROWID| BIG_TABLE | 100K| 2246K| | 29681 (1)| 00:05:57 |
|* 4 | INDEX RANGE SCAN | I_BIG_TABLE | 40009 | | | 6 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | BIG_TABLE1 | 971K| 12M| | 2974 (1)| 00:00:36 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."ID"="B"."ID")
4 - access("A"."OBJECT_NAME"='T')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
14246 consistent gets
0 physical reads
0 redo size
515 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
sec@ora10g> SELECT count(*)
FROM big_table a
WHERE a.object_name = 'T'
AND a.id IN
(SELECT b.id
FROM big_table1 b)
/
2 3 4 5 6 7
COUNT(*)
----------
86
Elapsed: 00:00:00.16
Execution Plan
----------------------------------------------------------
Plan hash value: 290523449
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 36 | | 33983 (1)| 00:06:48 |
| 1 | SORT AGGREGATE | | 1 | 36 | | | |
|* 2 | HASH JOIN SEMI | | 100K| 3516K| 3424K| 33983 (1)| 00:06:48 |
| 3 | TABLE ACCESS BY INDEX ROWID| BIG_TABLE | 100K| 2246K| | 29681 (1)| 00:05:57 |
|* 4 | INDEX RANGE SCAN | I_BIG_TABLE | 40009 | | | 6 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | BIG_TABLE1 | 971K| 12M| | 2974 (1)| 00:00:36 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."ID"="B"."ID")
4 - access("A"."OBJECT_NAME"='T')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
14246 consistent gets
0 physical reads
0 redo size
515 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
5.RBO没有索引的情况下：IN效率高于EXISTS（IN:00:00:01.20 EXISTS:00:01:21.50）
sec@ora10g> SELECT /*+ RULE */ count(*)
FROM big_table a
WHERE a.object_name = 'T'
AND a.id IN
(SELECT b.id
FROM big_table1 b)
/
2 3 4 5 6 7
COUNT(*)
----------
86
Elapsed: 00:00:01.20
Execution Plan
----------------------------------------------------------
Plan hash value: 1142876600
---------------------------------------------
| Id | Operation | Name |
---------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | VIEW | |
| 3 | SORT UNIQUE | |
| 4 | MERGE JOIN | |
| 5 | SORT JOIN | |
|* 6 | TABLE ACCESS FULL| BIG_TABLE |
|* 7 | SORT JOIN | |
| 8 | TABLE ACCESS FULL| BIG_TABLE1 |
---------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter("A"."OBJECT_NAME"='T')
7 - access("A"."ID"="B"."ID")
filter("A"."ID"="B"."ID")
Note
-----
- rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
148401 consistent gets
0 physical reads
692 redo size
515 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
1 rows processed
sec@ora10g> SELECT /*+ RULE */ count(*)
FROM big_table a
WHERE a.object_name = 'T'
AND EXISTS
(SELECT 1
FROM big_table1 b
WHERE a.id = b.id)
/
2 3 4 5 6 7 8
COUNT(*)
----------
86
Elapsed: 00:01:21.50
Execution Plan
----------------------------------------------------------
Plan hash value: 432798818
------------------------------------------
| Id | Operation | Name |
------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
|* 2 | FILTER | |
|* 3 | TABLE ACCESS FULL| BIG_TABLE |
|* 4 | TABLE ACCESS FULL| BIG_TABLE1 |
------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( EXISTS (SELECT 0 FROM "BIG_TABLE1" "B" WHERE
"B"."ID"=:B1))
3 - filter("A"."OBJECT_NAME"='T')
4 - filter("B"."ID"=:B1)
Note
-----
- rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
11133507 consistent gets
0 physical reads
868 redo size
515 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
6.RBO在列上创建索引后：EXISTS效率高于IN（IN:00:18:16.76 EXISTS:00:01:04.62）
sec@ora10g> create index i_big_table on big_table(object_name) parallel 8;
sec@ora10g> SELECT /*+ RULE */ count(*)
FROM big_table a
WHERE a.object_name = 'T'
AND EXISTS
(SELECT 1
FROM big_table1 b
WHERE a.id = b.id)
/
2 3 4 5 6 7 8
COUNT(*)
----------
86
Elapsed: 00:01:04.62
Execution Plan
----------------------------------------------------------
Plan hash value: 2377613799
-----------------------------------------------------
| Id | Operation | Name |
-----------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
|* 2 | FILTER | |
| 3 | TABLE ACCESS BY INDEX ROWID| BIG_TABLE |
|* 4 | INDEX RANGE SCAN | I_BIG_TABLE |
|* 5 | TABLE ACCESS FULL | BIG_TABLE1 |
-----------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( EXISTS (SELECT 0 FROM "BIG_TABLE1" "B" WHERE
"B"."ID"=:B1))
4 - access("A"."OBJECT_NAME"='T')
5 - filter("B"."ID"=:B1)
Note
-----
- rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
10999352 consistent gets
0 physical reads
0 redo size
515 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
sec@ora10g> SELECT /*+ RULE */ count(*)
FROM big_table a
WHERE a.object_name = 'T'
AND a.id IN
(SELECT b.id
FROM big_table1 b)
/
2 3 4 5 6 7
COUNT(*)
----------
86
Elapsed: 00:18:16.76
Execution Plan
----------------------------------------------------------
Plan hash value: 4287364898
------------------------------------------------------
| Id | Operation | Name |
------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | VIEW | |
| 3 | SORT UNIQUE | |
|* 4 | TABLE ACCESS BY INDEX ROWID| BIG_TABLE |
| 5 | NESTED LOOPS | |
| 6 | TABLE ACCESS FULL | BIG_TABLE1 |
|* 7 | INDEX RANGE SCAN | I_BIG_TABLE |
------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("A"."ID"="B"."ID")
7 - access("A"."OBJECT_NAME"='T')
Note
-----
- rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
869012509 consistent gets
0 physical reads
0 redo size
515 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
-- The End --
CBO没有索引的情况下：IN与EXISTS效率一样（IN:00:00:00.85 EXISTS:00:00:00.85）
CBO在列上创建索引后：IN与EXISTS效率一样（IN:00:00:00.16 EXISTS:00:00:00.16）
RBO没有索引的情况下：IN效率高于EXISTS（IN:00:00:01.20 EXISTS:00:01:21.50）
RBO在列上创建索引后：EXISTS效率高于IN（IN:00:18:16.76 EXISTS:00:01:04.62）
2.用到的实验SQL语句如下：
SELECT count(*)
FROM big_table a
WHERE a.object_name = 'T'
AND EXISTS
(SELECT 1
FROM big_table1 b
WHERE a.id = b.id)
/
SELECT count(*)
FROM big_table a
WHERE a.object_name = 'T'
AND a.id IN
(SELECT b.id
FROM big_table1 b)
/
SELECT /*+ RULE */ count(*)
FROM big_table1 a
WHERE a.object_name = 'T'
AND EXISTS
(SELECT 1
FROM big_table b
WHERE a.id = b.id)
/
SELECT /*+ RULE */ count(*)
FROM big_table1 a
WHERE a.object_name = 'T'
AND a.id IN
(SELECT b.id
FROM big_table b)
/
3.CBO没有索引的情况下：IN与EXISTS效率一样（IN:00:00:00.85 EXISTS:00:00:00.85）
sec@ora10g> SELECT count(*)
FROM big_table a
WHERE a.object_name = 'T'
AND EXISTS
(SELECT 1
FROM big_table1 b
WHERE a.id = b.id)
/
2 3 4 5 6 7 8
COUNT(*)
----------
86
Elapsed: 00:00:00.85
Execution Plan
----------------------------------------------------------
Plan hash value: 2715735594
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 36 | | 34170 (1)| 00:06:51 |
| 1 | SORT AGGREGATE | | 1 | 36 | | | |
|* 2 | HASH JOIN SEMI | | 100K| 3516K| 3424K| 34170 (1)| 00:06:51 |
|* 3 | TABLE ACCESS FULL| BIG_TABLE | 100K| 2246K| | 29868 (1)| 00:05:59 |
| 4 | TABLE ACCESS FULL| BIG_TABLE1 | 971K| 12M| | 2974 (1)| 00:00:36 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."ID"="B"."ID")
3 - filter("A"."OBJECT_NAME"='T')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
148401 consistent gets
0 physical reads
692 redo size
515 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
sec@ora10g> SELECT count(*)
FROM big_table a
WHERE a.object_name = 'T'
AND a.id IN
(SELECT b.id
FROM big_table1 b)
/
2 3 4 5 6 7
COUNT(*)
----------
86
Elapsed: 00:00:00.85
Execution Plan
----------------------------------------------------------
Plan hash value: 2715735594
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 36 | | 34170 (1)| 00:06:51 |
| 1 | SORT AGGREGATE | | 1 | 36 | | | |
|* 2 | HASH JOIN SEMI | | 100K| 3516K| 3424K| 34170 (1)| 00:06:51 |
|* 3 | TABLE ACCESS FULL| BIG_TABLE | 100K| 2246K| | 29868 (1)| 00:05:59 |
| 4 | TABLE ACCESS FULL| BIG_TABLE1 | 971K| 12M| | 2974 (1)| 00:00:36 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."ID"="B"."ID")
3 - filter("A"."OBJECT_NAME"='T')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
148401 consistent gets
0 physical reads
648 redo size
515 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
4.CBO在列上创建索引后：IN与EXISTS效率一样（IN:00:00:00.16 EXISTS:00:00:00.16）
create index i_big_table on big_table(object_name) parallel 8;
sec@ora10g> SELECT count(*)
FROM big_table a
WHERE a.object_name = 'T'
AND EXISTS
(SELECT 1
FROM big_table1 b
WHERE a.id = b.id)
/
2 3 4 5 6 7 8
COUNT(*)
----------
86
Elapsed: 00:00:00.16
Execution Plan
----------------------------------------------------------
Plan hash value: 290523449
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 36 | | 33983 (1)| 00:06:48 |
| 1 | SORT AGGREGATE | | 1 | 36 | | | |
|* 2 | HASH JOIN SEMI | | 100K| 3516K| 3424K| 33983 (1)| 00:06:48 |
| 3 | TABLE ACCESS BY INDEX ROWID| BIG_TABLE | 100K| 2246K| | 29681 (1)| 00:05:57 |
|* 4 | INDEX RANGE SCAN | I_BIG_TABLE | 40009 | | | 6 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | BIG_TABLE1 | 971K| 12M| | 2974 (1)| 00:00:36 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."ID"="B"."ID")
4 - access("A"."OBJECT_NAME"='T')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
14246 consistent gets
0 physical reads
0 redo size
515 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
sec@ora10g> SELECT count(*)
FROM big_table a
WHERE a.object_name = 'T'
AND a.id IN
(SELECT b.id
FROM big_table1 b)
/
2 3 4 5 6 7
COUNT(*)
----------
86
Elapsed: 00:00:00.16
Execution Plan
----------------------------------------------------------
Plan hash value: 290523449
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 36 | | 33983 (1)| 00:06:48 |
| 1 | SORT AGGREGATE | | 1 | 36 | | | |
|* 2 | HASH JOIN SEMI | | 100K| 3516K| 3424K| 33983 (1)| 00:06:48 |
| 3 | TABLE ACCESS BY INDEX ROWID| BIG_TABLE | 100K| 2246K| | 29681 (1)| 00:05:57 |
|* 4 | INDEX RANGE SCAN | I_BIG_TABLE | 40009 | | | 6 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | BIG_TABLE1 | 971K| 12M| | 2974 (1)| 00:00:36 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."ID"="B"."ID")
4 - access("A"."OBJECT_NAME"='T')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
14246 consistent gets
0 physical reads
0 redo size
515 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
5.RBO没有索引的情况下：IN效率高于EXISTS（IN:00:00:01.20 EXISTS:00:01:21.50）
sec@ora10g> SELECT /*+ RULE */ count(*)
FROM big_table a
WHERE a.object_name = 'T'
AND a.id IN
(SELECT b.id
FROM big_table1 b)
/
2 3 4 5 6 7
COUNT(*)
----------
86
Elapsed: 00:00:01.20
Execution Plan
----------------------------------------------------------
Plan hash value: 1142876600
---------------------------------------------
| Id | Operation | Name |
---------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | VIEW | |
| 3 | SORT UNIQUE | |
| 4 | MERGE JOIN | |
| 5 | SORT JOIN | |
|* 6 | TABLE ACCESS FULL| BIG_TABLE |
|* 7 | SORT JOIN | |
| 8 | TABLE ACCESS FULL| BIG_TABLE1 |
---------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter("A"."OBJECT_NAME"='T')
7 - access("A"."ID"="B"."ID")
filter("A"."ID"="B"."ID")
Note
-----
- rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
148401 consistent gets
0 physical reads
692 redo size
515 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
1 rows processed
sec@ora10g> SELECT /*+ RULE */ count(*)
FROM big_table a
WHERE a.object_name = 'T'
AND EXISTS
(SELECT 1
FROM big_table1 b
WHERE a.id = b.id)
/
2 3 4 5 6 7 8
COUNT(*)
----------
86
Elapsed: 00:01:21.50
Execution Plan
----------------------------------------------------------
Plan hash value: 432798818
------------------------------------------
| Id | Operation | Name |
------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
|* 2 | FILTER | |
|* 3 | TABLE ACCESS FULL| BIG_TABLE |
|* 4 | TABLE ACCESS FULL| BIG_TABLE1 |
------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( EXISTS (SELECT 0 FROM "BIG_TABLE1" "B" WHERE
"B"."ID"=:B1))
3 - filter("A"."OBJECT_NAME"='T')
4 - filter("B"."ID"=:B1)
Note
-----
- rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
11133507 consistent gets
0 physical reads
868 redo size
515 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
6.RBO在列上创建索引后：EXISTS效率高于IN（IN:00:18:16.76 EXISTS:00:01:04.62）
sec@ora10g> create index i_big_table on big_table(object_name) parallel 8;
sec@ora10g> SELECT /*+ RULE */ count(*)
FROM big_table a
WHERE a.object_name = 'T'
AND EXISTS
(SELECT 1
FROM big_table1 b
WHERE a.id = b.id)
/
2 3 4 5 6 7 8
COUNT(*)
----------
86
Elapsed: 00:01:04.62
Execution Plan
----------------------------------------------------------
Plan hash value: 2377613799
-----------------------------------------------------
| Id | Operation | Name |
-----------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
|* 2 | FILTER | |
| 3 | TABLE ACCESS BY INDEX ROWID| BIG_TABLE |
|* 4 | INDEX RANGE SCAN | I_BIG_TABLE |
|* 5 | TABLE ACCESS FULL | BIG_TABLE1 |
-----------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( EXISTS (SELECT 0 FROM "BIG_TABLE1" "B" WHERE
"B"."ID"=:B1))
4 - access("A"."OBJECT_NAME"='T')
5 - filter("B"."ID"=:B1)
Note
-----
- rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
10999352 consistent gets
0 physical reads
0 redo size
515 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
sec@ora10g> SELECT /*+ RULE */ count(*)
FROM big_table a
WHERE a.object_name = 'T'
AND a.id IN
(SELECT b.id
FROM big_table1 b)
/
2 3 4 5 6 7
COUNT(*)
----------
86
Elapsed: 00:18:16.76
Execution Plan
----------------------------------------------------------
Plan hash value: 4287364898
------------------------------------------------------
| Id | Operation | Name |
------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | VIEW | |
| 3 | SORT UNIQUE | |
|* 4 | TABLE ACCESS BY INDEX ROWID| BIG_TABLE |
| 5 | NESTED LOOPS | |
| 6 | TABLE ACCESS FULL | BIG_TABLE1 |
|* 7 | INDEX RANGE SCAN | I_BIG_TABLE |
------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("A"."ID"="B"."ID")
7 - access("A"."OBJECT_NAME"='T')
Note
-----
- rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
869012509 consistent gets
0 physical reads
0 redo size
515 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
-- The End --
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/70034820/viewspace-2993994/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
最新文章
- 中国OCM联盟隆重落户ITPUB社区，百位OCM已入驻，欢迎更多的OCM加入我们哦！
- OCMU联盟-一个有温度的联盟
- 丢掉盔甲，我们依然是骄傲的骑士——Secooler“三问四看”话认证培训
- 【数据安全】一次惊心动魄的ASM磁盘头损坏故障处理过程带来的深思
- 【参数】恢复db_recovery_file_dest_size参数为默认值“0”方法
- 【如何做快乐的DBA】ITPUB社区第一期博客专家专题“如何做快乐的DBA”
- 【Bitmap Index】B-Tree索引与Bitmap位图索引的锁代价比较研究
- 【RMAN】使用RMAN的Duplicate功能创建物理DataGuard报错（ORA-17627、ORA-17629）处理
- 【CUBE】Oracle分组函数之CUBE魅力
- 【ROLLUP】Oracle分组函数之ROLLUP魅力
