Question

执行计划里面的FILTER 与 NESTED LOOPS 有什么区别

Answer 1

Here is an example:
select * from xa
where a >= all(select e from xb)

with the following Plan:
[-] SELECT STATEMENT CHOOSE Cost = 3802 (Cost = 3802 Cardinality = 95 Bytes = 1140 )
[-] FILTER ( )
[ ] TABLE ACCESS FULL SQLEXPS.XA ANALYZED (Cost = 2 Cardinality = 95 Bytes = 1140 )
[ ] INDEX FULL SCAN SQLEXPS.XB_INX ANALYZED (Cost = 40 Cardinality = 819 Bytes = 5733 )

The way Oracle perform to search data is:
Performs a filter operation. It accepts row sources from XA Full Scan, eliminates rows from XB Index Full Scan and returns the remaining rows from XA Full Scan to the user or application issuing the statement.

Nested Loops is used to find matched rows from A table(normally full scan or index range scan) to index scan B table. There is no definite answer which is better, since normally it cannot replace each other unless you rewrite your SQL in significant differently syntax.