EXISTS, NOT EXISTS, IN, and NOT IN

最新推荐文章于 2023-12-13 15:36:33 发布

cuilansong1576

最新推荐文章于 2023-12-13 15:36:33 发布

阅读量99

点赞数

文章标签：数据库

Optimizing SQL usually gives the most significant results when DBAs are called upon to “make the system run faster.” Using tools like Statspack or Enterprise Manager, it is often easy to find the slow SQL. But how do you make the queries run faster? That is the challenge! In this paper we will discuss the semi-join and the anti-join, two powerful SQL constructs Oracle offers for use in your quest for faster queries. In particular, we will define these two terms, discuss when and why you might want to use the [NOT] EXISTS or [NOT] IN constructs, and demonstrate how you can use optimizer hints and make minor query changes in order to enable Oracle to use some very powerful and efficient access paths.

For certain classes of queries, these features can dramatically reduce logical reads, physical reads, CPU time, and elapsed time. But beware! There are some obscure (and not always easy to find) requirements that must be met in order for Oracle to deploy the semi- and anti-join access paths. If you fail to dot an “I” or cross a “T,” you could be banging your head against the wall for hours. In this paper we will look at SQL from a real application and demonstrate the “before” and “after” versions of queries that ran orders of magnitude faster once semi-joins and anti-joins were implemented correctly.

Before we get started, I want to point out that Oracle Corporation has improved its optimization techniques significantly over the years, specifically in the area of semi-joins and anti-joins. This paper was written using Oracle 9i release 9.2.0.4, and all examples were validated in such an environment. If you are using a newer or older release of Oracle, it is quite possible that you will see different results. Please do not take anything you read in this paper—or in any paper for that matter—for granted. Validate every hypothesis in your environment before relying upon it.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22920863/viewspace-660793/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22920863/viewspace-660793/