[20180611]函式與標量子查詢9.txt

[20180611]函式與標量子查詢9.txt

--//前幾天網友給一個連結,https://blogs.oracle.com/oraclemagazine/on-caching-and-evangelizing-sql
--//也證明我測試的例子.
--//連結:http://blog.itpub.net/267265/viewspace-2155558/=>[20180602]函式與標量子查詢3.txt

--//摘要如下:

When you're using a scalar subquery, Oracle Database will set up a small in-memory hash table for the subquery and its
results each time it runs the query. So, when you run the previous query, Oracle Database sets up in memory a hash table
that looks like this:

Oracle Database will use this hash table to remember the scalar subquery and the inputs to it—just :DEPTNO in this case
—and the output from it. At the beginning of every query execution, this cache is empty, but suppose you run the query
and the first PROJECTS row you retrieve has a DEPTNO value of 10. Oracle Database will assign the number 10 to a hash
value between 1 and 255 (the size of the hash table cache in Oracle Database 10g and Oracle Database 11g currently) and
will look in that hash table slot to see if the answer exists. In this case, it will not, so Oracle Database must run
the scalar subquery with the input of 10 to get the answer. If that answer (count) is 42, the hash table may look
something like this:

//注:補充說明我測試10.2.0.5,buckets=512而不是255.有機會測試11.2.0.4的情況.

Select count(*) from emp where emp.deptno = :deptno
:deptno     Count(*)

You'll have saved the DEPTNO value of 10 and the answer (count) of 42 in some slot—probably not the first or last slot,
but whatever slot the hash value 10 is assigned to. Now suppose the second row you get back from the PROJECTS table
includes a DEPTNO value of 20. Oracle Database will again look in the hash table after assigning the value 20, and it
will discover "no result in the cache yet." So it will run the scalar subquery, get the result, and put it into the hash
table cache. Now the cache may look like this:

Select count(*) from emp where emp.deptno = :deptno
:deptno     Count(*)
Select count(*) from emp where emp.deptno = :deptno
:deptno     Count(*)
…     …
10     42

Now suppose the query returns a third row and it again includes a DEPTNO value of 10. This time, Oracle Database will
see DEPTNO = 10, find that it already has that value in the hash table cache, and will simply return 42 from the cache
instead of executing the scalar subquery. In fact, it will never have to run that scalar subquery for the DEPTNO values
of 10 or 20 again for that query—it will already have the answer.

What happens if the number of unique DEPTNO values exceeds the size of the hash table? What if there are more than 255
values? Or, more generally, if more than one DEPTNO value is assigned to the same slot in the hash table, what happens
in a hash collision?

The answer is the same for all these questions and is rather simple: Oracle Database will not be able to cache the
second or nth value to that slot in the hash table. For example, what if the third row returned by the query contains
the DEPTNO = 30 value? Further, suppose that DEPTNO = 30 is to be assigned to exactly the same hash table slot as DEPTNO
= 10. The database won't be able to effectively cache DEPTNO = 30 in this case—the value will never make it into the
hash table. It will, however, be "partially cached." Oracle Database still has the hash table with all the previous
executions, but it also keeps the last scalar subquery result it had "next to" the hash table. That is, if the fourth
row also includes a DEPTNO = 30 value, Oracle Database will discover that the result is not in the hash table but is
"next to" the hash table, because the last time it ran the scalar subquery, it was run with an input of 30. On the other
hand, if the fourth row includes a DEPTNO = 40 value, Oracle Database will run the scalar subquery with the DEPTNO = 40
value (because it hasn't seen that value yet during this query execution) and overwrite the DEPTNO = 30 result. The next
time Oracle Database sees DEPTNO = 30 in the result set, it'll have to run that scalar subquery again.
--//注意理解這段話.這樣就很好理解為什麼我前面測試
select a ,(select sleep1(a) from dual) s from
(select 48 a from dual
union all
select 75 a from dual
union all
select 75 a from dual
union all
select 48 a from dual
union all
select 75 a from dual);
--//遞規測試是3.

So, all this discussion so far was a setup—a prelude, if you will—for what I really wanted to write about: how to
reduce the number of times a PL/SQL function invoked from SQL is called.