SQL优化009

课题：not in和not exists相关使用

提问1：not in 在RDBMS(如 oracle,  mysql, postgreSQL)里，能否使用到索引？

回答1：oracle 中，如果说not in（变量列表）无法走索引，这是事实。 但是not in（子查询)不一样，如果满足条件，确实是可以走索引。

提问2：oracle 中反连接与左连接性能比较，哪一个更为高效？

回答2：

（一）反连接更高效

1. 当右表（外部表）的数据量远远大于左表（内部表）时，反连接通常比左连接更高效。

2. 当需要找出两个表中不相关的记录，并且有合适的索引可以支持NOT EXISTS、NOT IN或MINUS等操作时，反连接通常比左连接更高效。              

3. 当左右表的连接列上没有合适的索引时，反连接可能比左连接更高效。

（二）左连接更高效

1. 需要查询左表中所有记录，并且关联右表的记录数量有限时，左连接通常比反连接更高效。

2. 当查询需要返回左表和关联的右表的值时，使用左连接比反连接更直观和方便。

3. 当右表中需要使用到的列已经在查询中进行了索引优化时，左连接通常比反连接更高效。

实际情况中，性能优化应该基于具体场景进行，可以通过测试和比较两种连接方式来确定最佳选择。

小结：

11g版本中，采用all_rows优化模式的not exists是最优性能的反连接查询表现出较高的效率。

如果副集合的连接列的数据不是来自于表而是来自于临时的集合，在用in、 exists的时候，需要增加提示/*+cardinality(t,n)*/(在9i以前的版本中最好增加rownum>0),而在使用反关连not in，not exists的时候，则不需要这些技巧。

当副集合的连接列的数据不是来自于表，而是来自于多个表的集合，直接采用not in 与not exists，并不能用到oracle的反关连技术，在11g版本中，not exists比not in表现出较高的效率，本节给出一个根据集合的运算公式，改写成多个当副集合的连接列的数据来自于表的反关连，最后在对这些返回集合进行集合运算，这样又能采用到oracle的反关连的优化算法。

变化主数据集与副数据集的比例，看到notin与not esists对这个数据特征变化不是象in 与 exists那样敏感。在本节中，没有例举更多的数据特征的例子，在具体版本，具体数据特征的情况下，最好在测试后，再决定是采用not in 还是 not exists。

实际上in not in exists noexists的执行计划受诸多如主副数据特征、一些系统参数 如sort_area,db_block_buffer,hash_area_size、还有数据库的版本等因素的影响，在具体环境里，到底采用哪一种方式，建议在多对比测试后，在选择合适的写法。

相关隐含参数_always_anti_join、_optimizer_null_aware_antijoin

SQL> select x.ksppinm ,y.KSPPSTVL,y.KSPPSTDVL from x$ksppi x, x$ksppcv  y where x.indx=y.indx and x.ksppinm='_always_anti_join';

_always_anti_join

CHOOSE

always use this method for anti-join when possible

SQL> select x.ksppinm ,y.KSPPSTVL,y.KSPPSTDVL from x$ksppi x, x$ksppcv  y where x.indx=y.indx and x.ksppinm='_optimizer_null_aware_antijoin';

取值为'TRUE'  或 'FALSE'。在Oracle 11gR2中，Oracle是否启用Null-Aware Anti Join受隐含参数_optimizer_null_aware_antijoin控制，其默认值为TRUE，表示启用Null-Aware Anti Join。FALSE表示Oracle就不能再用Null-Aware Anti Join，因为NOT IN对NULL值敏感，所以Oracle此时也不能用普通的反连接。

疑问：19C中反连接的实现是否与11g中一致，有待进一步验证，各位读者也可指点和提供参考。

参考：

https://cloud.tencent.com/developer/article/2321495

https://cloud.tencent.com/developer/article/1515843

https://blog.csdn.net/Tiwen818/article/details/7107242

https://blog.itpub.net/25923810/viewspace-2130979/