Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Rajesh.

Asked: September 27, 2010 - 1:35 pm UTC

Last updated: January 16, 2014 - 6:54 pm UTC

Version: 10.2.0

Viewed 10K+ times! This question is

You Asked

Tom:




I am reading this posting
http://groups.google.com/group/comp.databases.oracle.server/browse_frm/thread/a8a0c094234adb7d/397d52697493fada?q=lewis+kyte+scalar+subquery+caching&rnum=1&hl=en&pli=1 Refering to Jonathan reply "If so, the cache is probably
a hash table of 256 rows in 8i and 9i,
and 1024 rows in 10g" what does this Hash table denotes? Where i can see this Hash table in Oracle?


and Tom said...

You cannot 'see' the hash table anywhere, it is an internal data structure that lives in your session memory for the duration of the query. Once the query is finished - it goes away.

It is a cache associated with your query - nothing more, nothing less.

You can "see" it in action by measuring how many times your function is called, for example:

ops$tkyte%ORA11GR2> create or replace function f( x in varchar2 ) return number
  2  as
  3  begin
  4          dbms_application_info.set_client_info(userenv('client_info')+1 );
  5          return length(x);
  6  end;
  7  /

Function created.

<b>create a function that basically counts how many times it has been called storing the results in CLIENT_INFO in v$session..</b>


ops$tkyte%ORA11GR2> exec :cpu := dbms_utility.get_cpu_time; dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> set autotrace traceonly statistics;
ops$tkyte%ORA11GR2> select owner, f(owner) from stage;

71653 rows selected.


Statistics
----------------------------------------------------------
         64  recursive calls
          0  db block gets
       5821  consistent gets
       1279  physical reads
          0  redo size
    1345846  bytes sent via SQL*Net to client
      52955  bytes received via SQL*Net from client
       4778  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      71653  rows processed

ops$tkyte%ORA11GR2> set autotrace off
ops$tkyte%ORA11GR2> select dbms_utility.get_cpu_time-:cpu cpu_hsecs, userenv('client_info') from dual;

 CPU_HSECS USERENV('CLIENT_INFO')
---------- ----------------------------------------------------------------
       164 71653

<b>Now, on my system stage is a copy of all_objects.  As you can see the function was called ONCE PER ROW - over and over again.</b>

ops$tkyte%ORA11GR2> exec :cpu := dbms_utility.get_cpu_time; dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> set autotrace traceonly statistics;
ops$tkyte%ORA11GR2> select owner, <b>(select f(owner) from dual)</b> f from stage;

71653 rows selected.


Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
       5856  consistent gets
       1020  physical reads
          0  redo size
    1345839  bytes sent via SQL*Net to client
      52955  bytes received via SQL*Net from client
       4778  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      71653  rows processed

ops$tkyte%ORA11GR2> set autotrace off
ops$tkyte%ORA11GR2> select dbms_utility.get_cpu_time-:cpu cpu_hsecs, userenv('client_info') from dual;

 CPU_HSECS USERENV('CLIENT_INFO')
---------- ----------------------------------------------------------------
        25 69

<b>now it was called 69 times - the effect of this scalar subquery caching is now apparent</b>




Rating

  (25 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

HASH Table Size in 8i, 9i, 10g

Rajesh waran, September 28, 2010 - 6:02 am UTC

Tom, Sorry to bother you. Refering to Jonathan's reply How he concludes that HASH table has 256 rows in 8i and 9i, and 1024 rows in 10g. Is that mentioned somewhere in Oracle product documentation?
Tom Kyte
September 28, 2010 - 9:11 am UTC

It is not, it is an educated guess based on observation and carefully controlled testing on his part.

CPU Time on Result_Cache in 11G Optimizer

Rajeshwaran, Jeyabal, August 13, 2011 - 1:54 pm UTC

Tom:

I was reading your post on Oracle Magazine,

http://www.oracle.com/technetwork/issue-archive/2011/11-sep/o51asktom-453438.html

<quote>
Furthermore, if you were to run this query again—as shown in Listing 6—you’d discover that it calls the function zero times, but the CPU time (0.63 seconds) is still longer than the CPU time for the scalar subquery cache example in Listing 3.

Code Listing 6: Rerunning the function result cache solution 

SQL> begin
   2   :cpu := dbms_utility.get_cpu_time; 
   3   dbms_application_info.set_client_info(0);
   4 end;
   5 /
PL/SQL procedure successfully completed.

SQL> select owner, f(owner) from stage;
...
72841 rows selected.

SQL> select 
  2  dbms_utility.get_cpu_time-:cpu cpu_hsecs, 
  3  userenv('client_info') 
  4  from dual;

 CPU_HSECS   USERENV('CLIENT_INFO')
 ---------   -----------------------
        63                         0

</quote>

Question

1) Why the CPU time is 63 still the context switching from SQL to PL/SQL is Zero. Is that due to time spend in reading the query results from result_cache?
Tom Kyte
August 14, 2011 - 8:30 pm UTC

1) that is the context switching from plsql to sql - there where 72,841 calls to plsql from sql - the plsql engine got the result from the cache - but we still had to go from sql to plsql 10s of thousands of times.

That is why you probably always want to use the scalar subquery caching - EVEN when you use function result caching - to avoid that switch.

oracle magazine article

george joseph, August 15, 2011 - 12:30 pm UTC

Hi Tom
In the article on Oracle Magazine, how is it that with scalar subquery caching enabled the number of function calls resulted in only 66 calls when the distinct number of owners was 32 as was demostrated in the query involving the result cache

george joseph, August 15, 2011 - 12:31 pm UTC

sorry meant to say resulted in 66 calls when i expected only 32
Tom Kyte
August 16, 2011 - 1:57 am UTC

that is all explained in the article.

start reading at the top of the article again, starting with:

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?


When not to use Scalar subquery Caching?

David Grimberg, September 19, 2011 - 1:08 pm UTC

Tom,

I just finished reading your Oracle Magazine article on Scalar Subquery Caching:
http://www.oracle.com/technetwork/issue-archive/2011/11-sep/o51asktom-453438.html
You appear to be advocating using it for all function calls in a SQL statement.

Are there situations where one shouldn't use this technique?

If there arn't such situations, then why doesn't the SQL optimizer just do this for us behind the scenes?
Tom Kyte
September 19, 2011 - 5:58 pm UTC

You appear to be advocating using it for all function calls in a SQL statement.


for all PLSQL function calls, yes.

Are there situations where one shouldn't use this technique?


not that I've encountered yet.

If there arn't such situations, then why doesn't the SQL optimizer just do this for us behind the scenes?


maybe one day it will - the optimizer guys read my stuff too every now and then and we talk...

Non scalar subquery caching

Matt Birchall, November 17, 2011 - 10:40 am UTC

Running Oracle standard edition v11.2.0.1
I have been experiencing interesting behaviour with some queries generated by Business Objects. The query contains a subquery and is of the form...
Select ... from FACT_TABLE WHERE P_ID IN (SELECT P_ID FROM BRIDGE_TABLE WHERE S_ID IN (12345))
Does Oracle cache the results of the non-scalar sub-query in a similar fashion to the scalar sub-query caching or does it remember the number of rows returned for future queries?
If I clear the SGA cache and run the query I see one ("good") query plan and the query returns in a few millseconds. If I then re-run the same query and examine the plan (via Toad's session browser) I see a different ("bad") plan and it takes several minutes.
(I have the server CURSOR_SHARING set to EXACT)
Tom Kyte
November 17, 2011 - 7:24 pm UTC

no, it doesn't - but it usually doesn't matter..


And it would not affect the query plan at all one way or the other, scalar subquery caching does not affect the plan.

However, bind peeking would and could. Is P_ID a bind variable or a column in a table?

Need more information here - sounds like it could be bind peeking.

No bind variables...

Matt Birchall, November 18, 2011 - 7:17 am UTC

Thanks for your reply.
P_ID is a column in FACT_TABLE. The value of S_ID is passed as a literal (e.g. 12345) value in the query (this is the way that BO works but I can also reproduce the behaviour in Toad) so bind variables don't appear to be relevant as far as I can see.
The thing that changes the behaviour is that if I use a value for the literal that returns 2 rows for P_ID from the subquery (after flushing the cache) then the second execution has a different query plan to the initial plan. If I use a value that returns 80 rows then the query plan remains unchanged between executions. The statistics on BRIDGE_TABLE suggest that there should be 80 rows on average from the subquery. It seems that Oracle is observing and remembering that the query returned an atypical set (or it's caching the results) and it then tries to improve the query plan.
The behaviour is completely consistent and predictable but I am trying to understand what can prompt a new version of a query plan between consecutive executions of an identical query.
Tom Kyte
November 21, 2011 - 1:18 pm UTC

can you use sqlplus and dbms_xplan in the manner I did here:

http://asktom.oracle.com/Misc/something-new-i-learned-about-estimated.html

to see if it is cardinality feedback?

post your results...

Cardinality Feedback!

Matt Birchall, November 22, 2011 - 5:19 am UTC

Output from XPLAN (below) does indeed show cardinality feedback. Unfortunately it produces a worse query plan (query runs in 3mins 50 secs rather than 172ms) for these parameters. It expects to find practically no data in ARTICLE_REFERENCE when there are actually 4k rows that would be picked up in a single dip with the original plan.
This is another degree of freedom in the system that I need to manage (or at least think about) ( http://en.wikipedia.org/wiki/Variety_%28cybernetics%29 )

Q1. Can this feature be disabled? Not sure if this is the right thing but I want to know what options I have.
Q2. If bind variables were used then are we less likely to see the feature in action?

Initial Plan (SGA Flushed)...

SQL_ID 8c2h5htdx25z5, child number 0
-------------------------------------
SELECT DISTINCT COUNT(DISTINCT ARTICLE.ARTICLE_ID) , DIM_DATE.YEAR
, PUBLICATION.TITLE , max( PUBLISHER.PUBLISHER_NAME ) ,
Count(ARTICLE_REFERENCE.PUBLICATION_ID) FROM ARTICLE_REFERENCE INNER
JOIN ARTICLE ON (ARTICLE.ARTICLE_ID=ARTICLE_REFERENCE.ARTICLE_ID and
ARTICLE.PUBLICATION_ID=ARTICLE_REFERENCE.PUBLICATION_ID) INNER JOIN
VW_PUBLICATION_DATE ON (VW_PUBLICATION_DATE.PUBLICATION_ID=ARTICLE.PUBLI
CATION_ID and VW_PUBLICATION_DATE.PUBLICATION_DATE_ID=ARTICLE.PUBLICATIO
N_DATE_ID) INNER JOIN PUBLICATION ON
(PUBLICATION.PUBLICATION_ID=VW_PUBLICATION_DATE.PUBLICATION_ID)
INNER JOIN PUBLISHER ON (PUBLISHER.PUBLISHER_ID=PUBLICATION.PUBLICATION_
COPYRIGHT_ORG_ID) INNER JOIN DIM_DATE ON
(DIM_DATE.DATE_ID=VW_PUBLICATION_DATE.PUBLICATION_DATE_ID) WHERE (
( ARTICLE.PUBLICATION_ID IN ( select publication_id from
PUBLICATION_ACAD_SUBJ where academic_subject_id in (11359221) ) )
AND DIM_DATE.YEAR >= 2000 AND ARTICLE.DOCUMENT_TYPE_ID IN
( 7847539

Plan hash value: 3391746572

-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 24674 (100)| |
| 1 | HASH UNIQUE | | 98758 | 14M| | 24674 (1)| 00:04:57 |
| 2 | SORT GROUP BY | | 98758 | 14M| 15M| 24674 (1)| 00:04:57 |
| 3 | NESTED LOOPS | | 98758 | 14M| | 21425 (1)| 00:04:18 |
| 4 | NESTED LOOPS | | 4395 | 588K| | 8237 (1)| 00:01:39 |
| 5 | NESTED LOOPS | | 4395 | 442K| | 8236 (1)| 00:01:39 |
| 6 | NESTED LOOPS | | 4395 | 248K| | 8236 (1)| 00:01:39 |
|* 7 | HASH JOIN | | 4773 | 223K| 3224K| 8236 (1)| 00:01:39 |
| 8 | JOIN FILTER CREATE | :BF0000 | 68615 | 2412K| | 3684 (1)| 00:00:45 |
| 9 | NESTED LOOPS | | 68615 | 2412K| | 3684 (1)| 00:00:45 |
|* 10 | INDEX RANGE SCAN | PUBLICATION_ACAD_SUBJ_PK | 80 | 960 | | 2 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | PK__ARTICLE | 855 | 20520 | | 12 (0)| 00:00:01 |
| 12 | VIEW | VW_PUBLICATION_DATE | 565K| 6629K| | 3750 (1)| 00:00:45 |
| 13 | HASH UNIQUE | | 565K| 6629K| 10M| 3750 (1)| 00:00:45 |
| 14 | JOIN FILTER USE | :BF0000 | 565K| 6629K| | 1167 (1)| 00:00:15 |
| 15 | INDEX FAST FULL SCAN| PUBLICATION_DATE | 565K| 6629K| | 1167 (1)| 00:00:15 |
|* 16 | INDEX UNIQUE SCAN | DIM_DATE_PK | 1 | 10 | | 0 (0)| |
|* 17 | INDEX UNIQUE SCAN | PUBLICATION_PK | 1 | 45 | | 0 (0)| |
|* 18 | INDEX UNIQUE SCAN | PK_PUBLISHER | 1 | 34 | | 0 (0)| |
|* 19 | INDEX RANGE SCAN | PK_ARTICLE_REFERENCE | 22 | 264 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

7 - access("VW_PUBLICATION_DATE"."PUBLICATION_ID"="ARTICLE"."PUBLICATION_ID" AND
"VW_PUBLICATION_DATE"."PUBLICATION_DATE_ID"="ARTICLE"."PUBLICATION_DATE_ID")
10 - access("ACADEMIC_SUBJECT_ID"=11359221)
11 - access("ARTICLE"."PUBLICATION_ID"="PUBLICATION_ID")
filter(("ARTICLE"."DOCUMENT_TYPE_ID"=7847539 OR "ARTICLE"."DOCUMENT_TYPE_ID"=7848189))
16 - access("DIM_DATE"."DATE_ID"="VW_PUBLICATION_DATE"."PUBLICATION_DATE_ID")
filter("DIM_DATE"."YEAR">=2000)
17 - access("PUBLICATION"."PUBLICATION_ID"="VW_PUBLICATION_DATE"."PUBLICATION_ID")
18 - access("PUBLISHER"."PUBLISHER_ID"="PUBLICATION"."PUBLICATION_COPYRIGHT_ORG_ID")
19 - access("ARTICLE"."PUBLICATION_ID"="ARTICLE_REFERENCE"."PUBLICATION_ID" AND
"ARTICLE"."ARTICLE_ID"="ARTICLE_REFERENCE"."ARTICLE_ID")

With Cardinality Feedback...

SQL_ID 8c2h5htdx25z5, child number 1
-------------------------------------
SELECT DISTINCT COUNT(DISTINCT ARTICLE.ARTICLE_ID) , DIM_DATE.YEAR
, PUBLICATION.TITLE , max( PUBLISHER.PUBLISHER_NAME ) ,
Count(ARTICLE_REFERENCE.PUBLICATION_ID) FROM ARTICLE_REFERENCE INNER
JOIN ARTICLE ON (ARTICLE.ARTICLE_ID=ARTICLE_REFERENCE.ARTICLE_ID and
ARTICLE.PUBLICATION_ID=ARTICLE_REFERENCE.PUBLICATION_ID) INNER JOIN
VW_PUBLICATION_DATE ON (VW_PUBLICATION_DATE.PUBLICATION_ID=ARTICLE.PUBLI
CATION_ID and VW_PUBLICATION_DATE.PUBLICATION_DATE_ID=ARTICLE.PUBLICATIO
N_DATE_ID) INNER JOIN PUBLICATION ON
(PUBLICATION.PUBLICATION_ID=VW_PUBLICATION_DATE.PUBLICATION_ID)
INNER JOIN PUBLISHER ON (PUBLISHER.PUBLISHER_ID=PUBLICATION.PUBLICATION_
COPYRIGHT_ORG_ID) INNER JOIN DIM_DATE ON
(DIM_DATE.DATE_ID=VW_PUBLICATION_DATE.PUBLICATION_DATE_ID) WHERE (
( ARTICLE.PUBLICATION_ID IN ( select publication_id from
PUBLICATION_ACAD_SUBJ where academic_subject_id in (11359221) ) )
AND DIM_DATE.YEAR >= 2000 AND ARTICLE.DOCUMENT_TYPE_ID IN
( 7847539

Plan hash value: 82017851

-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 4168 (100)| |
| 1 | HASH UNIQUE | | 7 | 1043 | | 4168 (1)| 00:00:51 |
| 2 | SORT GROUP BY | | 7 | 1043 | | 4168 (1)| 00:00:51 |
| 3 | NESTED LOOPS | | 2 | 298 | | 4167 (1)| 00:00:51 |
| 4 | NESTED LOOPS | | 1 | 137 | | 4164 (1)| 00:00:50 |
| 5 | NESTED LOOPS | | 1 | 103 | | 4164 (1)| 00:00:50 |
| 6 | NESTED LOOPS | | 1 | 58 | | 4164 (1)| 00:00:50 |
| 7 | NESTED LOOPS | | 1 | 48 | | 4164 (1)| 00:00:50 |
| 8 | NESTED LOOPS | | 9 | 324 | | 4164 (1)| 00:00:50 |
| 9 | VIEW | VW_PUBLICATION_DATE | 9 | 108 | | 3750 (1)| 00:00:45 |
| 10 | HASH UNIQUE | | 9 | 108 | 10M| 3750 (1)| 00:00:45 |
| 11 | INDEX FAST FULL SCAN| PUBLICATION_DATE | 565K| 6629K| | 1167 (1)| 00:00:15 |
|* 12 | INDEX RANGE SCAN | PK__ARTICLE | 1 | 24 | | 12 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | PUBLICATION_ACAD_SUBJ_PK | 1 | 12 | | 0 (0)| |
|* 14 | INDEX UNIQUE SCAN | DIM_DATE_PK | 1 | 10 | | 0 (0)| |
|* 15 | INDEX UNIQUE SCAN | PUBLICATION_PK | 1 | 45 | | 0 (0)| |
|* 16 | INDEX UNIQUE SCAN | PK_PUBLISHER | 1 | 34 | | 0 (0)| |
|* 17 | INDEX RANGE SCAN | PK_ARTICLE_REFERENCE | 22 | 264 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

12 - access("VW_PUBLICATION_DATE"."PUBLICATION_ID"="ARTICLE"."PUBLICATION_ID")
filter((INTERNAL_FUNCTION("ARTICLE"."DOCUMENT_TYPE_ID") AND
"VW_PUBLICATION_DATE"."PUBLICATION_DATE_ID"="ARTICLE"."PUBLICATION_DATE_ID"))
13 - access("ACADEMIC_SUBJECT_ID"=11359221 AND "ARTICLE"."PUBLICATION_ID"="PUBLICATION_ID")
14 - access("DIM_DATE"."DATE_ID"="VW_PUBLICATION_DATE"."PUBLICATION_DATE_ID")
filter("DIM_DATE"."YEAR">=2000)
15 - access("PUBLICATION"."PUBLICATION_ID"="VW_PUBLICATION_DATE"."PUBLICATION_ID")
16 - access("PUBLISHER"."PUBLISHER_ID"="PUBLICATION"."PUBLICATION_COPYRIGHT_ORG_ID")
17 - access("ARTICLE"."PUBLICATION_ID"="ARTICLE_REFERENCE"."PUBLICATION_ID" AND
"ARTICLE"."ARTICLE_ID"="ARTICLE_REFERENCE"."ARTICLE_ID")

Note
-----
- cardinality feedback used for this statement

Tom Kyte
November 22, 2011 - 8:38 am UTC

q1) yes, google it up... It is an underscore parameter

q2) no, the article I referred you to used a bind

http://asktom.oracle.com/Misc/something-new-i-learned-about-estimated.html


Scalar subquery caching

Lal Cyril, February 20, 2012 - 12:33 am UTC

Tom,
Is the scalar subquery caching applicable for user defined functions only? Can it be used for system functions like to_date,to_char etc?

Tom Kyte
February 20, 2012 - 2:47 am UTC

it can - 


ops$tkyte%ORA11GR2> /*
ops$tkyte%ORA11GR2> drop table t;
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create table t as select created from all_objects;
ops$tkyte%ORA11GR2> */
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> @trace
ops$tkyte%ORA11GR2> alter session set events '10046 trace name context forever, level 12';

Session altered.

ops$tkyte%ORA11GR2> begin
  2  for i in 1 .. 10
  3  loop
  4          for x in ( select to_date( to_char( created, 'dd-mon-yyyy' ), 'dd-mon-yyyy' ) from t )
  5          loop
  6                  null;
  7          end loop;
  8          for x in ( select (select to_date( to_char( created, 'dd-mon-yyyy' ), 'dd-mon-yyyy' ) from dual) x from t )
  9          loop
 10                  null;
 11          end loop;
 12  end loop;
 13  end;
 14  /

PL/SQL procedure successfully completed.




SELECT TO_DATE( TO_CHAR( CREATED, 'dd-mon-yyyy' ), 'dd-mon-yyyy' ) FROM T

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute     10      0.00       0.00          0          0          0           0
Fetch     7230      1.47       1.51          0       8520          0      722820
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     7241      1.47       1.52          0       8521          0      722820
********************************************************************************
SELECT (SELECT TO_DATE( TO_CHAR( CREATED, 'dd-mon-yyyy' ), 'dd-mon-yyyy' )
FROM
 DUAL) X FROM T 


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          2          0           0
Execute     10      0.00       0.00          0          0          0           0
Fetch     7230      0.40       0.43          0       8520          0      722820
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     7241      0.40       0.43          0       8522          0      722820

Scalar subquery caching

Lal Cyril, February 21, 2012 - 6:30 am UTC

Tom,
Thank you very much for the immediate response.

I have a table called TCC_CACHE_TEST having the following structure

desc tcc_cache_test
Name       Null     Type       
---------- -------- ---------- 
PERS_ID    NOT NULL NUMBER(12) 
LOC_SYS_ID NOT NULL NUMBER(12) 
UTC                 DATE


This table stores the activity start times of each person in utc. (roster system) and also the loc_sys_id (location code) of each person.
The following package is used to find the ltc time. 

create or replace PACKAGE tcc_dtm AS
/*Normal way*/
FUNCTION get_local_dtm (i_dtm_utc IN DATE, i_loc_sys_id IN NUMBER) RETURN DATE; 
/*Using Deterministic*/
FUNCTION get_local_dtm_dm (i_dtm_utc IN DATE,i_loc_sys_id IN NUMBER) RETURN DATE DETERMINISTIC; 
/*Using Result cache*/
FUNCTION get_local_dtm_rs ( i_dtm_utc IN DATE,i_loc_sys_id IN NUMBER) RETURN DATE RESULT_CACHE; 
END;

The table has 2528424 records and the distinct utc,loc_sys_id values is 32630 records.

I did a comparison for the function calls with scalar subquery caching, deterministic, result cache options in a similar way as you
showed in the oracle magazine article on scalar subquery caching

The following are the different scenarios i tried and the corresponding queries

Normal Execution select pers_id, loc_sys_id, utc, tcc_dtm.get_local_dtm(utc,loc_sys_id) LTC
from TCC_CACHE_TEST;

Scalar Subquery Caching
select pers_id, loc_sys_id, utc, (select tcc_dtm.get_local_dtm(utc,loc_sys_id) from dual) LTC
From TCC_CACHE_TEST;

Deterministic Caching
select pers_id, loc_sys_id, utc, tcc_dtm.get_local_dtm_dm(utc,loc_sys_id) LTC
From TCC_CACHE_TEST;

Deterministic Caching second execution with ordering
select x.pers_id, x.loc_sys_id, x.utc, tcc_dtm.get_local_dtm_dm(x.utc,x.loc_sys_id) LTC
from (select pers_id, loc_sys_id, utc from TCC_CACHE_TEST where rownum>0 order by utc,loc_sys_id)x;

Deterministic Caching second execution with ordering + scalar subquery caching
select x.pers_id, x.loc_sys_id, x.utc, (select tcc_dtm.get_local_dtm_dm(x.utc,x.loc_sys_id) from dual) LTC
from (select pers_id, loc_sys_id, utc
from TCC_CACHE_TEST
where rownum>0 order by utc,loc_sys_id)x;

Result Cachingselect pers_id, loc_sys_id, utc, tcc_dtm.get_local_dtm_rs(utc,loc_sys_id) LTC
From TCC_CACHE_TEST;

Result Caching second execution
select pers_id, loc_sys_id, utc, tcc_dtm.get_local_dtm_rs(utc,loc_sys_id) LTC
From TCC_CACHE_TEST;

Result Caching second execution + scalar subquery caching
select pers_id, loc_sys_id, utc, (select tcc_dtm.get_local_dtm_rs(utc,loc_sys_id) from dual) LTC
from TCC_CACHE_TEST;


Results for the above execution
Execution type        Elapsed   CPU_HSECS Function calls
----------------------------------------------------------------------------------
Normal Execution       00:08:36.07  37006  2528423
----------------------------------------------------------------------------------
Scalar Subquery Caching      00:05:06.17  15460  1014065
----------------------------------------------------------------------------------
Deterministic Caching      00:08:39.98  37662  2527091
----------------------------------------------------------------------------------
Deterministic Caching second 
execution with ordering      00:04:19.93  6029  199341
----------------------------------------------------------------------------------
Deterministic Caching second execution 
with ordering + scalar subquery caching  00:02:59.92  2114  32630
----------------------------------------------------------------------------------
Result Caching        00:04:33.75  4764  32630
----------------------------------------------------------------------------------
Result Caching second execution    00:04:19.25  4038  0
----------------------------------------------------------------------------------
Result Caching second execution + 
scalar subquery caching      00:04:16.01  3043  0
----------------------------------------------------------------------------------


The following are the results if the above queries inserts the data into a table

Execution type        Elapsed   CPU_HSECS Function calls
----------------------------------------------------------------------------------
Normal Execution       00:04:35.76  25427 2528424
----------------------------------------------------------------------------------
Scalar Subquery Caching      00:02:11.85  10544 1014065
----------------------------------------------------------------------------------
Deterministic Caching table     00:02:14.09  10435 1014207
----------------------------------------------------------------------------------
Deterministic Caching second 
execution with ordering      00:04:39.07  25836 2528330
----------------------------------------------------------------------------------
Deterministic Caching second execution 
with ordering + scalar subquery caching  00:00:44.48  770  32630
----------------------------------------------------------------------------------
Result Caching        00:01:01.44  2168 32630
----------------------------------------------------------------------------------
Result Caching second execution    00:00:54.22  1610 0
----------------------------------------------------------------------------------
Result Caching second execution + 
scalar subquery caching      00:00:48.24  887  0
----------------------------------------------------------------------------------


In my test the best results are shown for
Deterministic Caching second execution with ordering + scalar subquery caching and the second best option is
Result Caching second execution + scalar subquery caching.
(The result cache size was found to be 34MB)

I have the following doubts

1) Why the number of executions of Scalar Subquery Caching is high (1014065 times instead of 32630)? Is it due to the hash table size?
If yes can we increase the hash table size?
or scalar subquery caching is best for few rows (<1000)?
2) Why Deterministic Caching second execution with ordering executed 199341 times instead of 32630 times?
3) Why Deterministic Caching second execution with ordering executed 2528330 times (more than without ordering 1014207) when the data was inserted to table
instead of 32630 times?
4) Based on the above results which option should i choose?

Tom Kyte
February 21, 2012 - 9:42 pm UTC

1) due to hash collisions and the size of the hash table - yes.

we can cache at most 255 entries PLUS the last one we executed. By sorting the data - you ensured we executed the function ONCE per unique set of values. Unsorted - we cached what we could - and had the last executed value setting around too.

No, you cannot influence the size of the cached set of values.

2) It is order dependent as well - the more 'ordered' the data, the better it did at caching.

3) It all depends on the plans being used and when they decide to reuse values. as mentioned 'deterministic does save some calls, but not nearly to the scale that scalar subquery caching can'

4) it depends. I'd like to see the function itself before I comment ;)

Scalar subquery caching

Lal Cyril, February 22, 2012 - 4:31 am UTC

Tom,
Thanks once again for the quick answer.

The following is the code for the conversion function

FUNCTION get_local_dtm
  ( i_dtm_utc     DATE,
    i_loc_sys_id  NUMBER
  )
RETURN DATE 
IS
  CURSOR c1
  ( c1_dtm_utc    IN DATE,
    c1_loc_sys_id IN NUMBER
  )
  IS
    SELECT c1_dtm_utc+var_dec_days
    FROM   utc_variances
    WHERE  ( --c1_dtm_utc BETWEEN eff_fm_dtm_utc AND eff_to_dtm_utc
             trunc(eff_fm_dtm_utc, 'MI')  <= c1_dtm_utc
             AND trunc(eff_to_dtm_utc, 'MI') > c1_dtm_utc
           )
      AND  ( ctry_cd, tmzn_no, tmzn_sfx ) IN
           ( SELECT ctry_cd, tmzn_no, tmzn_sfx
             FROM   cities
             WHERE  loc_sys_id = c1_loc_sys_id
             UNION
             SELECT ctry_cd, tmzn_no, tmzn_sfx
             FROM   stations
             WHERE  loc_sys_id = c1_loc_sys_id
           )
    ;
  rtnval          DATE ;
BEGIN
   dbms_application_info.set_client_info
        (userenv('client_info')+1 );
  OPEN  c1(i_dtm_utc,i_loc_sys_id) ;
  FETCH c1 INTO rtnval ;
  CLOSE c1 ;
  RETURN rtnval ;
END get_local_dtm ;


I feel that the best option is to use the function result cache + scalar subquery.
Since the data in cities,stations and utc_variances are sort of master data (very less inserts/updates).
What is your suggestion?

I couldn't understand the reason why
Deterministic Caching second execution with ordering + scalar subquery caching
performed better than
Result Caching second execution + scalar subquery caching
Tom Kyte
February 23, 2012 - 7:14 pm UTC

a) that function is NOT deterministic. Anytime you have a function that queries a table and that query impacts the answer - the function *by definition* is not deterministic.

b) please use select into, you have an inadvertent bug in your code without it. What happens when this code *doesn't* find anything? what happens when two rows might match? select into will take care of that for you and is actually at least as fast and typically faster than open/fetch/close. It is certainly less buggy.


c) instead of:

trunc(eff_fm_dtm_utc, 'MI') <= c1_dtm_utc
AND trunc(eff_to_dtm_utc, 'MI') > c1_dtm_utc

you should apply the function to the bind (do not put the function on the database column - obviating indexes, making cardinality estimates really hard, put the function on the bind variable cl_dtm_utc). It will be a different function of course, but put the function there.

You do not need a function at all on the eff_to_dtm_utc. If trunc(eff_to_dtm_utc) > c1_dtm_utc then eff_to_dtm_utc is > c1_dtm_utc as well...


d) why is this even a function - why isn't it just a scalar subquery in a view in the first place? What is plsql even being used.


any reason you cannot just using TIMESTAMP functions? do you really need a lookup table?



my suggestion:

scalar subquery caching of the modified (fix the where clause) sql statement without ANY plsql at all.


Scalar subquery caching

Lal Cyril, February 24, 2012 - 7:29 am UTC

Tom,
Thanks very much for the reply.
I modified the code based on your suggestions. (removed the trunc function and used the into clause).

a) that function is NOT deterministic. Anytime you have a function that queries a table and that query impacts the answer - the function *by definition* is not deterministic.
For a given input utc time there will be only one ltc value as output. Hence it can be made deterministic right? Any issues in that?

d) why is this even a function - why isn't it just a scalar subquery in a view in the first place? What is plsql even being used.
any reason you cannot just using TIMESTAMP functions? do you really need a lookup table?

I created the following view 

create or replace view tcc_vw as 
SELECT var_dec_days,eff_fm_dtm_utc,eff_to_dtm_utc,loc_sys_id
    FROM   utc_variances a,cities b
    where a.ctry_cd = b.ctry_cd
      and a.tmzn_no = b.tmzn_no
      and a.tmzn_sfx = b.tmzn_sfx
union all
SELECT var_dec_days,eff_fm_dtm_utc,eff_to_dtm_utc,loc_sys_id
    FROM   utc_variances a,stations b
    where a.ctry_cd = b.ctry_cd
      and a.tmzn_no = b.tmzn_no
      and a.tmzn_sfx = b.tmzn_sfx;


and changed the query as shown below 

select  pers_id, a.loc_sys_id, utc, /*tcc_dtm.get_local_dtm_opt(utc,loc_sys_id)*/ a.utc+b.var_dec_days LTC  
from TCC_CACHE_TEST a,tcc_vw b
where a.loc_sys_id = b.loc_sys_id
and b.eff_fm_dtm_utc  <= a.utc 
AND b.eff_to_dtm_utc > a.utc;


Is this what you meant by "changing to a scalar subquery in a view"?

How can i use the timestamp function in this case?

"my suggestion:
scalar subquery caching of the modified (fix the where clause) sql statement without ANY plsql at all. "

Is it the same as the above query with the view?

Also for a quick work around can i enable result cache for the function. Any issues in that?
Since the code change required will be minimum.
Tom Kyte
February 25, 2012 - 5:12 am UTC

a) For a given input utc time there will be only one ltc value as output. Hence it can be made deterministic right? Any issues in that?


deterministic means that if I send this a given input X and it returns me a given output Y, it will ALWAYS RETURN Y when sent X.

If I truncate table utc_variances - will it still return Y? No, no it will not. This function IS NOT DETERMINISTIC. The output of this function depends on the contents of many tables - if any of the tables are modified in any way - the output could change.

this function is definitely NOT DETERMINISTIC.

If your function includes SELECT - it is not going to be deterministic in general.

d) you have a simple select into query - that is all you needed to put into your query - the entire original query. If you have a function that is JUST A SELECT INTO and you are calling it from SQL - you should just move the query right into the SQL (or even better yet in most cases - JOIN TO THE TABLES, do not use a scalar subquery at all!)




result set caching does nothing for the context switching, that is what scalar subquery caching does.

you can do result set caching, that will help some
you can do scalar subquery caching of a result set cached function, that will do more.
you can rewrite this without using any plsql, that will be even better.
you can rewrite this as a join - that will likely be the best if you get lots of rows out of this query.

Scalar SubQuery or Outer Join

A reader, April 20, 2012 - 2:45 pm UTC

I am trying to find out whether it's better to use Scalar Subquery or outer join. I want to get the result set and use it to write to a file.
I am sorry, I don't have access to the trace file directory, so I am unable to run TKPROF

When we compare query 1a and query 1b, which one will be better?
Also, when we compare query 2a and query 2b, which one will be better? Query 2 has an aggregate function.

DROP TABLE SK_DTL;
DROP TABLE SK_HDR;
DROP TABLE SK_VENDOR;

CREATE TABLE SK_VENDOR
(VENDOR_ID NUMBER(10) NOT NULL PRIMARY KEY,
VENDOR_NAME VARCHAR2(100) NOT NULL);



CREATE TABLE SK_HDR
(HDR_ID NUMBER(10) NOT NULL PRIMARY KEY,
VENDOR_ID NUMBER(10) REFERENCES SK_VENDOR(VENDOR_ID),
HDR_DATE DATE NOT NULL);


CREATE TABLE SK_DTL
(DTL_ID NUMBER(10) NOT NULL PRIMARY KEY,
HDR_ID NUMBER(10) NOT NULL REFERENCES SK_HDR(HDR_ID),
ITEM_ID NUMBER(10) NOT NULL,
AMOUNT NUMBER(10,2) NOT NULL);

CREATE INDEX SK_DTL_I1 ON SK_DTL(HDR_ID);


INSERT INTO SK_VENDOR
(VENDOR_ID, VENDOR_NAME)
(SELECT OBJECT_ID, OBJECT_NAME
FROM ALL_OBJECTS
WHERE ROWNUM <= 5000);


INSERT INTO SK_HDR
(HDR_ID, VENDOR_ID, HDR_DATE)
(SELECT ROWNUM, DECODE(MOD(VENDOR_ID,10),
0, TO_NUMBER(NULL),
VENDOR_ID),
SYSDATE
FROM SK_VENDOR);


INSERT INTO SK_DTL
(DTL_ID, HDR_ID, ITEM_ID, AMOUNT)
(SELECT ROWNUM, HDR_ID, HDR_ID * LEVEL, HDR_ID * LEVEL * 1/3
FROM SK_HDR, (SELECT LEVEL L FROM DUAL CONNECT BY LEVEL <= 10));

COMMIT;

EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'SK_VENDOR');
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'SK_HDR');
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'SK_DTL');


--Query 1a)
SELECT H.HDR_ID, H.HDR_DATE, H.VENDOR_ID,
(SELECT V.VENDOR_NAME
FROM SK_VENDOR V
WHERE V.VENDOR_ID = H.VENDOR_ID) VENDOR_NAME,
D.ITEM_ID,
D.AMOUNT
FROM SK_DTL D, SK_HDR H
WHERE D.HDR_ID = H.HDR_ID;


--Query 1b)
SELECT H.HDR_ID, H.HDR_DATE, H.VENDOR_ID,
V.VENDOR_NAME,
D.ITEM_ID,
D.AMOUNT
FROM SK_VENDOR V, SK_DTL D, SK_HDR H
WHERE V.VENDOR_ID(+) = H.VENDOR_ID
AND D.HDR_ID = H.HDR_ID;

--Query 2a)
SELECT R.HDR_ID, R.HDR_DATE,
(SELECT V.VENDOR_NAME
FROM SK_VENDOR V
WHERE V.VENDOR_ID = R.VENDOR_ID) VENDOR_NAME,
R.AMOUNT
FROM (SELECT H.HDR_ID, H.HDR_DATE, H.VENDOR_ID, SUM(D.AMOUNT) AMOUNT
FROM SK_DTL D, SK_HDR H
WHERE D.HDR_ID = H.HDR_ID
GROUP BY H.HDR_ID, H.HDR_DATE, H.VENDOR_ID) R

--Query 2b)
SELECT H.HDR_ID, H.HDR_DATE, V.VENDOR_NAME, H.VENDOR_ID, SUM(D.AMOUNT) AMOUNT
FROM SK_VENDOR V, SK_DTL D, SK_HDR H
WHERE V.VENDOR_ID(+) = H.VENDOR_ID
AND D.HDR_ID = H.HDR_ID
GROUP BY H.HDR_ID, H.HDR_DATE, V.VENDOR_NAME, H.VENDOR_ID


Tom Kyte
April 22, 2012 - 9:00 am UTC

I am trying to find out whether it's better to use Scalar Subquery or outer
join. I want to get the result set and use it to write to a file.

it depends.

In your case, since your goal is to get to the last row as fast as possible - the time to the first row is not so relevant - almost certainly the outer join (no scalar subquery) will rule. Big bulky operations is what you are looking for.

Scalar SubQuery or Outer Join

A reader, April 20, 2012 - 3:54 pm UTC

When I run auto trace, I see that consistent gets are way too high for scalar subquery. Does it mean that it's best to use Outer join when possible than using scalar sub query?

Query 1a) results

SQL> SELECT H.HDR_ID, H.HDR_DATE, H.VENDOR_ID,
  2         (SELECT V.VENDOR_NAME
  3          FROM SK_VENDOR V
  4          WHERE V.VENDOR_ID = H.VENDOR_ID) VENDOR_NAME,
  5         D.ITEM_ID,
  6         D.AMOUNT
  7  FROM SK_DTL D, SK_HDR H
  8  WHERE D.HDR_ID = H.HDR_ID;

50000 rows selected.


Execution Plan
----------------------------------------------------------                      
Plan hash value: 2241286480                                                     
                                                                                
--------------------------------------------------------------------------------
--------------                                                                  
                                                                                
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |                                                                  
                                                                                
----------------------------------------------------------------------------------------------                                                                  
                                                                                
|   0 | SELECT STATEMENT            |                | 50000 |  1171K|    50   (4)| 00:00:01 |                                                                  
                                                                                
|   1 |  TABLE ACCESS BY INDEX ROWID| SK_VENDOR      |     1 |    20 |     1   (0)| 00:00:01 |                                                                  
                                                                                
|*  2 |   INDEX UNIQUE SCAN         | SYS_C001147487 |     1 |       |     1   (0)| 00:00:01 |                                                                  
                                                                                
|*  3 |  HASH JOIN                  |                | 50000 |  1171K|    50   (4)| 00:00:01 |                                                                  
                                                                                
|   4 |   TABLE ACCESS FULL         | SK_HDR         |  5000 | 80000 |    24   (0)| 00:00:01 |                                                                  
                                                                                
|   5 |   TABLE ACCESS FULL         | SK_DTL         | 50000 |   390K|    25   (4)| 00:00:01 |                                                                  
                                                                                
--------------------------------------------------------------------------------
--------------                                                                  
                                                                                
                                                                                
Predicate Information (identified by operation id):                             
---------------------------------------------------                             
                                                                                
   2 - access("V"."VENDOR_ID"=:B1)                                              
   3 - access("D"."HDR_ID"="H"."HDR_ID")                                        


Statistics
----------------------------------------------------------                      
          0  recursive calls                                                    
          0  db block gets                                                      
      47922  consistent gets                                                    
          0  physical reads                                                     
          0  redo size                                                          
    1418440  bytes sent via SQL*Net to client                                   
      23583  bytes received via SQL*Net from client                             
       3335  SQL*Net roundtrips to/from client                                  
          0  sorts (memory)                                                     
          0  sorts (disk)                                                       
      50000  rows processed                                                     

SQL>

Query 1b) results

SQL> SELECT H.HDR_ID, H.HDR_DATE, H.VENDOR_ID,
  2         V.VENDOR_NAME,
  3         D.ITEM_ID,
  4         D.AMOUNT
  5  FROM SK_VENDOR V, SK_DTL D, SK_HDR H
  6  WHERE V.VENDOR_ID(+) = H.VENDOR_ID
  7   AND  D.HDR_ID = H.HDR_ID;

50000 rows selected.


Execution Plan
----------------------------------------------------------                      
Plan hash value: 1991126885                                                     
                                                                                
--------------------------------------------------------------------------------
-                                                                               
                                                                                
| Id  | Operation           | Name      | Rows  | Bytes | Cost (%CPU)| Time     |                                                                               
                                                                                
---------------------------------------------------------------------------------                                                                               
                                                                                
|   0 | SELECT STATEMENT    |           | 50000 |  2148K|    75   (4)| 00:00:02 |                                                                               
                                                                                
|*  1 |  HASH JOIN          |           | 50000 |  2148K|    75   (4)| 00:00:02 |                                                                               
                                                                                
|*  2 |   HASH JOIN OUTER   |           |  5000 |   175K|    49   (3)| 00:00:01 |                                                                               
                                                                                
|   3 |    TABLE ACCESS FULL| SK_HDR    |  5000 | 80000 |    24   (0)| 00:00:01 |                                                                               
                                                                                
|   4 |    TABLE ACCESS FULL| SK_VENDOR |  5000 |    97K|    24   (0)| 00:00:01 |                                                                               
                                                                                
|   5 |   TABLE ACCESS FULL | SK_DTL    | 50000 |   390K|    25   (4)| 00:00:01 |                                                                               
                                                                                
---------------------------------------------------------------------------------                                                                               
                                                                                
                                                                                
Predicate Information (identified by operation id):                             
---------------------------------------------------                             
                                                                                
   1 - access("D"."HDR_ID"="H"."HDR_ID")                                        
   2 - access("V"."VENDOR_ID"(+)="H"."VENDOR_ID")                               


Statistics
----------------------------------------------------------                      
          1  recursive calls                                                    
          0  db block gets                                                      
       3474  consistent gets                                                    
          0  physical reads                                                     
          0  redo size                                                          
    1418441  bytes sent via SQL*Net to client                                   
      23583  bytes received via SQL*Net from client                             
       3335  SQL*Net roundtrips to/from client                                  
          0  sorts (memory)                                                     
          0  sorts (disk)                                                       
      50000  rows processed

Query 2a) results

SQL> SELECT R.HDR_ID, R.HDR_DATE,
  2         (SELECT V.VENDOR_NAME
  3          FROM SK_VENDOR V
  4          WHERE V.VENDOR_ID = R.VENDOR_ID) VENDOR_NAME,
  5         R.AMOUNT
  6  FROM (SELECT H.HDR_ID, H.HDR_DATE, H.VENDOR_ID, SUM(D.AMOUNT) AMOUNT
  7        FROM SK_DTL D, SK_HDR H
  8        WHERE D.HDR_ID = H.HDR_ID
  9        GROUP BY H.HDR_ID, H.HDR_DATE, H.VENDOR_ID) R;

5000 rows selected.


Execution Plan
----------------------------------------------------------                      
Plan hash value: 4220201437                                                     
                                                                                
------------------------------------------------------------------------------------------------------                                                          
                                                                                
| Id  | Operation                   | Name           | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |                                                          
                                                                                
------------------------------------------------------------------------------------------------------                                                          
                                                                                
|   0 | SELECT STATEMENT            |                | 50000 |  2343K|       |   264   (3)| 00:00:04 |                                                          
                                                                                
|   1 |  TABLE ACCESS BY INDEX ROWID| SK_VENDOR      |     1 |    20 |       |     1   (0)| 00:00:01 |                                                          
                                                                                
|*  2 |   INDEX UNIQUE SCAN         | SYS_C001147487 |     1 |       |       |     1   (0)| 00:00:01 |                                                          
                                                                                
|   3 |  VIEW                       |                | 50000 |  2343K|       |   264   (3)| 00:00:04 |                                                          
                                                                                
|   4 |   HASH GROUP BY             |                | 50000 |  1074K|  1584K|   264   (3)| 00:00:04 |                                                          
                                                                                
|*  5 |    HASH JOIN                |                | 50000 |  1074K|       |    50   (4)| 00:00:01 |                                                          
                                                                                
|   6 |     TABLE ACCESS FULL       | SK_HDR         |  5000 | 80000 |       |    24   (0)| 00:00:01 |                                                          
                                                                                
|   7 |     TABLE ACCESS FULL       | SK_DTL         | 50000 |   292K|       |    25   (4)| 00:00:01 |                                                          
                                                                                
------------------------------------------------------------------------------------------------------                                                          
                                                                                
                                                                                
Predicate Information (identified by operation id):                             
---------------------------------------------------                             
                                                                                
   2 - access("V"."VENDOR_ID"=:B1)                                              
   5 - access("D"."HDR_ID"="H"."HDR_ID")                                        


Statistics
----------------------------------------------------------                      
          1  recursive calls                                                    
          0  db block gets                                                      
       5671  consistent gets                                                    
          0  physical reads                                                     
          0  redo size                                                          
     134936  bytes sent via SQL*Net to client                                   
       2583  bytes received via SQL*Net from client                             
        335  SQL*Net roundtrips to/from client                                  
          0  sorts (memory)                                                     
          0  sorts (disk)                                                       
       5000  rows processed

Query 2b) results

SQL>    SELECT H.HDR_ID, H.HDR_DATE, V.VENDOR_NAME, H.VENDOR_ID, SUM(D.AMOUNT) AMOUNT
  2  FROM SK_VENDOR V, SK_DTL D, SK_HDR H
  3  WHERE V.VENDOR_ID(+) = H.VENDOR_ID
  4   AND  D.HDR_ID = H.HDR_ID
  5  GROUP BY  H.HDR_ID, H.HDR_DATE, V.VENDOR_NAME, H.VENDOR_ID;

5000 rows selected.


Execution Plan
----------------------------------------------------------                      
Plan hash value: 3593103821                                                     
                                                                                
------------------------------------------------------------------------------------------                                                                      
                                                                                
| Id  | Operation            | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |                                                                      
                                                                                
------------------------------------------------------------------------------------------                                                                      
                                                                                
|   0 | SELECT STATEMENT     |           | 50000 |  2050K|       |   419   (2)| 00:00:06 |                                                                      
                                                                                
|   1 |  HASH GROUP BY       |           | 50000 |  2050K|  2576K|   419   (2)| 00:00:06 |                                                                      
                                                                                
|*  2 |   HASH JOIN          |           | 50000 |  2050K|       |    75   (4)| 00:00:02 |                                                                      
                                                                                
|*  3 |    HASH JOIN OUTER   |           |  5000 |   175K|       |    49   (3)| 00:00:01 |                                                                      
                                                                                
|   4 |     TABLE ACCESS FULL| SK_HDR    |  5000 | 80000 |       |    24   (0)| 00:00:01 |                                                                      
                                                                                
|   5 |     TABLE ACCESS FULL| SK_VENDOR |  5000 |    97K|       |    24   (0)| 00:00:01 |                                                                      
                                                                                
|   6 |    TABLE ACCESS FULL | SK_DTL    | 50000 |   292K|       |    25   (4)| 00:00:01 |                                                                      
                                                                                
------------------------------------------------------------------------------------------                                                                      
                                                                                
                                                                                
Predicate Information (identified by operation id):                             
---------------------------------------------------                             
                                                                                
   2 - access("D"."HDR_ID"="H"."HDR_ID")                                        
   3 - access("V"."VENDOR_ID"(+)="H"."VENDOR_ID")                               


Statistics
----------------------------------------------------------                      
          1  recursive calls                                                    
          0  db block gets                                                      
        143  consistent gets                                                    
          0  physical reads                                                     
          0  redo size                                                          
     153238  bytes sent via SQL*Net to client                                   
       2583  bytes received via SQL*Net from client                             
        335  SQL*Net roundtrips to/from client                                  
          0  sorts (memory)                                                     
          0  sorts (disk)                                                       
       5000  rows processed

Tom Kyte
April 22, 2012 - 9:02 am UTC

When I run auto trace, I see that consistent gets are way too high for scalar subquery. Does it mean that it's best to use Outer join when possible than using scalar sub query?

nothing in programming is 100% good
nothing in programming is 100% bad

scalar subqueries rock and roll - SOMETIMES - when you are trying to get the first rows as fast as possible. Do this, pretend this query is for an OLTP app and your goal is to get the first 10 rows for display on the screen as fast as possible. Now which query is "better"

there is no single best way for anything (in programming)

Aggregate before or after Outer Join?

A reader, April 23, 2012 - 8:17 am UTC

If I have a table that has thousands of rows and I am outer joining to another table, is it better to do the SUM before outer join or it really does not matter.

Example queries

Query 1) Outer join after SUMMING UP
SELECT R.HDR_ID, R.HDR_DATE,
       V.VENDOR_NAME VENDOR_NAME,
       R.AMOUNT
FROM SK_VENDOR V,
     (SELECT H.HDR_ID, H.HDR_DATE, H.VENDOR_ID, SUM(D.AMOUNT) AMOUNT
      FROM SK_DTL D, SK_HDR H
      WHERE D.HDR_ID = H.HDR_ID
      GROUP BY H.HDR_ID, H.HDR_DATE, H.VENDOR_ID) R
WHERE V.VENDOR_ID(+) = R.VENDOR_ID;

Query 2) Outer join before SUMMING UP 
   
SELECT H.HDR_ID, H.HDR_DATE, V.VENDOR_NAME, H.VENDOR_ID, SUM(D.AMOUNT) AMOUNT
FROM SK_VENDOR V, SK_DTL D, SK_HDR H
WHERE V.VENDOR_ID(+) = H.VENDOR_ID
AND  D.HDR_ID = H.HDR_ID
GROUP BY  H.HDR_ID, H.HDR_DATE, V.VENDOR_NAME, H.VENDOR_ID;

Tom Kyte
April 23, 2012 - 1:28 pm UTC

In general, those two queries are not semantically equivalent - not knowing the schema and constraints - I cannot tell if they are interchangeable even.

given the teeny tiny size of the tables - it probably won't matter - but - if you prove to yourself (via the business rules) that the two queries are in fact the same - do what I would do....


benchmark them - just run them, measure the resources used (sql_trace+tkprof would be really nice) and see which is "better"

Cardinality Feedback 11.2.0.1 Vs 11.2.0.2

Rajeshwaran, Jeyabal, April 23, 2012 - 8:39 am UTC

Tom:

I was reading your link about cardinality feedback and results looks very different in 11.2.0.1 and 11.2.0.2 database. Can you help me what i am doing wrong here.

Cardinality feedback is not happening on 11.2.0.2 but the same is working in 11.2.0.1. Is that something got changed in 11.2.0.2 ?

http://tkyte.blogspot.com/2010/04/something-new-i-learned-about-estimated.html 


app_ods@DV04> create or replace type mytab is table of varchar2(20);
  2  /

Type created.

Elapsed: 00:00:00.57
app_ods@DV04>
app_ods@DV04> create or replace function fnc
  2  (p_in varchar2,p_delim varchar2 default ',')
  3  return mytab
  4  pipelined
  5  is
  6     l_n number;
  7     l_str long := p_in || p_delim;
  8  begin
  9     loop
 10             l_n := instr(l_str,p_delim);
 11             exit when nvl(l_n,0) = 0;
 12             pipe row ( substr(l_str,1, l_n - 1 ) );
 13             l_str := substr(l_str,l_n+1);
 14     end loop;
 15     return;
 16  end fnc;
 17  /

Function created.

Elapsed: 00:00:00.59
app_ods@DV04> set serveroutput off;
app_ods@DV04> variable txt varchar2(30);
app_ods@DV04> exec :txt := 'a,b,c';

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.28
app_ods@DV04> select * from table(cast(fnc(:txt) as mytab)) t;

COLUMN_VALUE
--------------------
a
b
c

Elapsed: 00:00:00.57
app_ods@DV04>
app_ods@DV04> select * from table( dbms_xplan.display_cursor );

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  1ft6an5fghf54, child number 0
-------------------------------------
select * from table(cast(fnc(:txt) as mytab)) t

Plan hash value: 4243326481

------------------------------------------------------------------------------------------
| Id  | Operation                         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |      |       |       |    29 (100)|          |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| FNC  |  8168 | 16336 |    29   (0)| 00:00:01 |
------------------------------------------------------------------------------------------


13 rows selected.

Elapsed: 00:00:00.60
app_ods@DV04> select * from table(cast(fnc(:txt) as mytab)) t;

COLUMN_VALUE
--------------------
a
b
c

Elapsed: 00:00:00.56
app_ods@DV04> select * from table( dbms_xplan.display_cursor );

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  1ft6an5fghf54, child number 0
-------------------------------------
select * from table(cast(fnc(:txt) as mytab)) t

Plan hash value: 4243326481

------------------------------------------------------------------------------------------
| Id  | Operation                         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |      |       |       |    29 (100)|          |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| FNC  |  8168 | 16336 |    29   (0)| 00:00:01 |
------------------------------------------------------------------------------------------


13 rows selected.

Elapsed: 00:00:01.25
app_ods@DV04> select * from table(cast(fnc(:txt) as mytab)) t;

COLUMN_VALUE
--------------------
a
b
c

Elapsed: 00:00:00.56
app_ods@DV04> select * from table( dbms_xplan.display_cursor );

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  1ft6an5fghf54, child number 0
-------------------------------------
select * from table(cast(fnc(:txt) as mytab)) t

Plan hash value: 4243326481

------------------------------------------------------------------------------------------
| Id  | Operation                         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |      |       |       |    29 (100)|          |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| FNC  |  8168 | 16336 |    29   (0)| 00:00:01 |
------------------------------------------------------------------------------------------


13 rows selected.

Elapsed: 00:00:01.34
app_ods@DV04>
app_ods@DV04> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

Elapsed: 00:00:00.87
app_ods@DV04>


You see Cardinality feedback not happened. but the same is happening in 11.2.0.1

rajesh@ORA11GR2> set serveroutput off;
rajesh@ORA11GR2> variable txt varchar2(30);
rajesh@ORA11GR2> exec :txt := 'a,b,c';

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
rajesh@ORA11GR2> select * from table(cast(fnc(:txt) as mytab)) t;

COLUMN_VALUE
--------------------
a
b
c

Elapsed: 00:00:00.03
rajesh@ORA11GR2>
rajesh@ORA11GR2> select * from table ( dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
SQL_ID  1ft6an5fghf54, child number 0
-------------------------------------
select * from table(cast(fnc(:txt) as mytab)) t

Plan hash value: 4243326481

------------------------------------------------------------------------------------------
| Id  | Operation                         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |      |       |       |    29 (100)|          |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| FNC  |  8168 | 16336 |    29   (0)| 00:00:01 |
------------------------------------------------------------------------------------------


13 rows selected.

Elapsed: 00:00:01.23
rajesh@ORA11GR2> select * from table(cast(fnc(:txt) as mytab)) t;

COLUMN_VALUE
--------------------
a
b
c

Elapsed: 00:00:00.01
rajesh@ORA11GR2> select * from table ( dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
SQL_ID  1ft6an5fghf54, child number 1
-------------------------------------
select * from table(cast(fnc(:txt) as mytab)) t

Plan hash value: 4243326481

------------------------------------------------------------------------------------------
| Id  | Operation                         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |      |       |       |    29 (100)|          |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| FNC  |     6 |    12 |    29   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Note
-----
   - cardinality feedback used for this statement


17 rows selected.

Elapsed: 00:00:01.12
rajesh@ORA11GR2>
rajesh@ORA11GR2> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

Elapsed: 00:00:00.50
rajesh@ORA11GR2>

Cardinality Feedback 11.2.0.1 Vs 11.2.0.2

Rajeshwaran, Jeyabal, April 24, 2012 - 7:58 am UTC

Tom:

Is that cardinality feedback is not working in 11.2.0.2? i tried again but unable to see cardinality feedback happening.
Tom Kyte
April 24, 2012 - 9:04 am UTC

it still happens, that particular example doesn't seem to trigger it anymore. Here is another one:

ops$tkyte%ORA11GR2> alter system flush shared_pool;

System altered.

ops$tkyte%ORA11GR2> set serveroutput off;
ops$tkyte%ORA11GR2> variable txt varchar2(30);
ops$tkyte%ORA11GR2> exec :txt := 'a,b,c';

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> with data as (select /*+ materialize */ * from table(cast(fnc(:txt) as mytab))) select * from data;

COLUMN_VALUE
--------------------
a
b
c

ops$tkyte%ORA11GR2> select * from table( dbms_xplan.display_cursor );

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  4mhrf41j19h2r, child number 0
-------------------------------------
with data as (select /*+ materialize */ * from table(cast(fnc(:txt) as
mytab))) select * from data

Plan hash value: 1294287401

--------------------------------------------------------------------------------
| Id  | Operation                           | Name                       | Rows
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                            |
|   1 |  TEMP TABLE TRANSFORMATION          |                            |
|   2 |   LOAD AS SELECT                    |                            |
|   3 |    COLLECTION ITERATOR PICKLER FETCH| FNC                        |  8168
|   4 |   VIEW                              |                            |  8168
|   5 |    TABLE ACCESS FULL                | SYS_TEMP_0FD9D6622_4BCCFD7 |  8168
--------------------------------------------------------------------------------


18 rows selected.

ops$tkyte%ORA11GR2> connect /
Connected.
ops$tkyte%ORA11GR2> set serveroutput off
ops$tkyte%ORA11GR2> set linesize 1000
ops$tkyte%ORA11GR2> with data as (select /*+ materialize */ * from table(cast(fnc(:txt) as mytab))) select * from data;

COLUMN_VALUE
--------------------
a
b
c

ops$tkyte%ORA11GR2> select * from table( dbms_xplan.display_cursor );

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  4mhrf41j19h2r, child number 1
-------------------------------------
with data as (select /*+ materialize */ * from table(cast(fnc(:txt) as
mytab))) select * from data

Plan hash value: 2499205596

--------------------------------------------------------------------------------
| Id  | Operation                           | Name                       | Rows
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                            |
|   1 |  TEMP TABLE TRANSFORMATION          |                            |
|   2 |   LOAD AS SELECT                    |                            |
|   3 |    COLLECTION ITERATOR PICKLER FETCH| FNC                        |  8168
|   4 |   VIEW                              |                            |     3
|   5 |    TABLE ACCESS FULL                | SYS_TEMP_0FD9D6623_4BCCFD7 |     3
--------------------------------------------------------------------------------

Note
-----
   - cardinality feedback used for this statement


22 rows selected.

Cardinality Feedback 11.2.0.1 Vs 11.2.0.2

Rajeshwaran, Jeyabal, April 24, 2012 - 1:18 pm UTC

This clearly proves that CBO is a Black box.
Tom Kyte
April 25, 2012 - 9:42 am UTC

by design, yes.

Cardinality Feedback 11.2.0.1 Vs 11.2.0.2

Rajeshwaran, Jeyabal, April 25, 2012 - 12:59 am UTC

Tom:

If /*+ materialize */ hint specified, Cardinality feedback works but not in its absence.

create table t as
select * from all_objects;

create index t_ind
on t(object_name) nologging;

begin
 dbms_stats.gather_table_stats
 (ownname=>user,
  tabname=>'T',
  estimate_percent=>100,
  method_opt=>'for all indexed columns size 254');
end;
/

variable txt varchar2(30);
exec :txt := 'a,b,c';


app_ods@DV04> with datas as
  2  ( select /*+ materialize */ * from table ( cast(fnc(:txt) as mytab) ) )
  3  select t.object_id,t.object_name
  4  from datas d, t
  5  where d.column_value = t.object_name
  6  /

no rows selected

Elapsed: 00:00:00.34
app_ods@DV04>
app_ods@DV04> select * from table ( dbms_xplan.display_cursor );

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------
SQL_ID  c6fzgqbqh619y, child number 0
-------------------------------------
with datas as ( select /*+ materialize */ * from table ( cast(fnc(:txt)
as mytab) ) ) select t.object_id,t.object_name from datas d, t where
d.column_value = t.object_name

Plan hash value: 227011802

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                             |       |       |   381 (100)|          |
|   1 |  TEMP TABLE TRANSFORMATION          |                             |       |       |            |          |
|   2 |   LOAD AS SELECT                    |                             |       |       |            |          |
|   3 |    COLLECTION ITERATOR PICKLER FETCH| FNC                         |  8168 | 16336 |    29   (0)| 00:00:01 |
|*  4 |   HASH JOIN                         |                             | 15598 |   746K|   352   (1)| 00:00:05 |
|   5 |    VIEW                             |                             |  8168 | 98016 |     3   (0)| 00:00:01 |
|   6 |     TABLE ACCESS FULL               | SYS_TEMP_0FD9D6CF2_950BF08C |  8168 | 16336 |     3   (0)| 00:00:01 |
|   7 |    TABLE ACCESS FULL                | T                           | 83881 |  3030K|   348   (1)| 00:00:05 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("D"."COLUMN_VALUE"="T"."OBJECT_NAME")


26 rows selected.

Elapsed: 00:00:01.50
app_ods@DV04> with datas as
  2  ( select /*+ materialize */ * from table ( cast(fnc(:txt) as mytab) ) )
  3  select t.object_id,t.object_name
  4  from datas d, t
  5  where d.column_value = t.object_name;

no rows selected

Elapsed: 00:00:00.29
app_ods@DV04>
app_ods@DV04> select * from table ( dbms_xplan.display_cursor );

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------
SQL_ID  c6fzgqbqh619y, child number 1
-------------------------------------
with datas as ( select /*+ materialize */ * from table ( cast(fnc(:txt)
as mytab) ) ) select t.object_id,t.object_name from datas d, t where
d.column_value = t.object_name

Plan hash value: 2959992793

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                             |       |       |    41 (100)|          |
|   1 |  TEMP TABLE TRANSFORMATION          |                             |       |       |            |          |
|   2 |   LOAD AS SELECT                    |                             |       |       |            |          |
|   3 |    COLLECTION ITERATOR PICKLER FETCH| FNC                         |  8168 | 16336 |    29   (0)| 00:00:01 |
|   4 |   NESTED LOOPS                      |                             |       |       |            |          |
|   5 |    NESTED LOOPS                     |                             |     6 |   294 |    12   (0)| 00:00:01 |
|   6 |     VIEW                            |                             |     3 |    36 |     3   (0)| 00:00:01 |
|   7 |      TABLE ACCESS FULL              | SYS_TEMP_0FD9D6CF3_950BF08C |     3 |     6 |     3   (0)| 00:00:01 |
|*  8 |     INDEX RANGE SCAN                | T_IND                       |     2 |       |     2   (0)| 00:00:01 |
|   9 |    TABLE ACCESS BY INDEX ROWID      | T                           |     2 |    74 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   8 - access("D"."COLUMN_VALUE"="T"."OBJECT_NAME")

Note
-----
   - cardinality feedback used for this statement


32 rows selected.

Elapsed: 00:00:01.40


In case of /*+ materialize */ hint missing it went like this.

app_ods@DV04> with datas as
  2  ( select * from table ( cast(fnc(:txt) as mytab) ) )
  3  select t.object_id,t.object_name
  4  from datas d, t
  5  where d.column_value = t.object_name;

no rows selected

Elapsed: 00:00:00.28
app_ods@DV04> select * from table ( dbms_xplan.display_cursor) ;

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
SQL_ID  btrpn0393vdqp, child number 0
-------------------------------------
with datas as ( select * from table ( cast(fnc(:txt) as mytab) ) )
select t.object_id,t.object_name from datas d, t where d.column_value =
t.object_name

Plan hash value: 3921941318

-------------------------------------------------------------------------------------------
| Id  | Operation                          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |      |       |       |   378 (100)|          |
|*  1 |  HASH JOIN                         |      | 16785 |   639K|   378   (1)| 00:00:05 |
|   2 |   COLLECTION ITERATOR PICKLER FETCH| FNC  |  8168 | 16336 |    29   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL                | T    | 83881 |  3030K|   348   (1)| 00:00:05 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T"."OBJECT_NAME"=VALUE(KOKBF$))


22 rows selected.

Elapsed: 00:00:01.31
app_ods@DV04> with datas as
  2  ( select * from table ( cast(fnc(:txt) as mytab) ) )
  3  select t.object_id,t.object_name
  4  from datas d, t
  5  where d.column_value = t.object_name
  6  /

no rows selected

Elapsed: 00:00:00.28
app_ods@DV04> select * from table ( dbms_xplan.display_cursor) ;

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
SQL_ID  btrpn0393vdqp, child number 0
-------------------------------------
with datas as ( select * from table ( cast(fnc(:txt) as mytab) ) )
select t.object_id,t.object_name from datas d, t where d.column_value =
t.object_name

Plan hash value: 3921941318

-------------------------------------------------------------------------------------------
| Id  | Operation                          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |      |       |       |   378 (100)|          |
|*  1 |  HASH JOIN                         |      | 16785 |   639K|   378   (1)| 00:00:05 |
|   2 |   COLLECTION ITERATOR PICKLER FETCH| FNC  |  8168 | 16336 |    29   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL                | T    | 83881 |  3030K|   348   (1)| 00:00:05 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T"."OBJECT_NAME"=VALUE(KOKBF$))


22 rows selected.

Elapsed: 00:00:01.21
app_ods@DV04>

Partitioning dataset to access bigger cache

Bobby, January 14, 2014 - 5:45 pm UTC 

Hi Tom,

It seems that scalar subquery caching is only really useful when there are a relatively small number of distinct values due to the internal hash being 256 in size.

I ran the below experiment to see if I could make use of this feature with a larger set of distinct values which has revealed some interesting behaviour.

By partitioning the subquery call into various branches via a CASE statement, it seems that Oracle can maintain multiple hash tables for the same subquery.

While this method is perhaps not entirely practical, it may have some use.

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE    11.1.0.7.0      Production
TNS for Solaris: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

5 rows selected.

test@local_db> DROP TABLE stage;

Table dropped.

test@local_db>
test@local_db> create table stage as
  2  SELECT T1.*
  3  FROM
  4  (
  5      select rownum rn, OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE
  6      from all_objects
  7      where rownum <=8000
  8  ) T1,
  9  (
 10      select level m
 11      FROM   dual
 12      CONNECT BY level <= 10
 13  )T2;

Table created.

test@local_db>
test@local_db> select count(*) from stage;

  COUNT(*)
----------
     80000

1 row selected.

test@local_db>
test@local_db> SELECT AVG(FREQ), STDDEV(FREQ), COUNT(*) num_distinct
  2  FROM (
  3      SELECT RN, count(*) freq
  4      FROM STAGE
  5      GROUP BY RN
  6  );

 AVG(FREQ) STDDEV(FREQ) NUM_DISTINCT
---------- ------------ ------------
        10            0         8000

1 row selected.

test@local_db>
test@local_db> create or replace function f( x in varchar2 ) return number
  2  as
  3  begin
  4          dbms_application_info.set_client_info(userenv('client_info')+1 );
  5          return length(x);
  6  end;
  7  /

Function created.

test@local_db>
test@local_db> variable cpu number
test@local_db> exec dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

test@local_db> exec :cpu := dbms_utility.get_cpu_time;

PL/SQL procedure successfully completed.

test@local_db>
test@local_db> SELECT MAX(FO||OWNER) FROM
  2  (
  3      select owner, f(RN) FO
  4      from stage
  5  );

MAX(FO||OWNER)
----------------------------------------------------------------------
4SYSTEM


1 row selected.

test@local_db>
test@local_db> select dbms_utility.get_cpu_time-:cpu cpu_hsecs, userenv('client_info') from dual;

 CPU_HSECS USERENV('CLIENT_INFO')
---------- ----------------------------------------------------------------
        81 80000

1 row selected.

test@local_db> --Scalar subquery caching
test@local_db> variable cpu number
test@local_db>
test@local_db> exec dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

test@local_db> exec :cpu := dbms_utility.get_cpu_time;

PL/SQL procedure successfully completed.

test@local_db>
test@local_db> SELECT MAX(FO||OWNER) FROM
  2  (
  3      select owner, (SELECT f(RN) FROM DUAL) FO
  4      from stage
  5  );

MAX(FO||OWNER)
----------------------------------------------------------------------
4SYSTEM

1 row selected.

test@local_db>
test@local_db> select dbms_utility.get_cpu_time-:cpu cpu_hsecs, userenv('client_info') from dual;

 CPU_HSECS USERENV('CLIENT_INFO')
---------- ----------------------------------------------------------------
        87 75392

1 row selected.

--NOTE: Only a 5.8% reduction in function calls


test@local_db>
test@local_db> variable cpu number
test@local_db>
test@local_db> exec dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

test@local_db> exec :cpu := dbms_utility.get_cpu_time;

PL/SQL procedure successfully completed.

test@local_db>
test@local_db> SELECT MAX(FO||OWNER) FROM
  2  (
  3      select owner, CASE MOD(RN,11)
  4                  WHEN 1  THEN (SELECT f(RN) FROM DUAL)
  5                  WHEN 2  THEN (SELECT f(RN) FROM DUAL)
  6                  WHEN 3  THEN (SELECT f(RN) FROM DUAL)
  7                  WHEN 4  THEN (SELECT f(RN) FROM DUAL)
  8                  WHEN 5  THEN (SELECT f(RN) FROM DUAL)
  9                  WHEN 6  THEN (SELECT f(RN) FROM DUAL)
 10                  WHEN 7  THEN (SELECT f(RN) FROM DUAL)
 11                  WHEN 8  THEN (SELECT f(RN) FROM DUAL)
 12                  WHEN 9  THEN (SELECT f(RN) FROM DUAL)
 13                  WHEN 10 THEN (SELECT f(RN) FROM DUAL)
 14                  ELSE         (SELECT f(RN) FROM DUAL)
 15                END FO
 16      from stage
 17  );

MAX(FO||OWNER)
----------------------------------------------------------------------
4SYSTEM

1 row selected.

test@local_db>
test@local_db> select dbms_utility.get_cpu_time-:cpu cpu_hsecs, userenv('client_info') from dual;

 CPU_HSECS USERENV('CLIENT_INFO')
---------- ----------------------------------------------------------------
        67 41642

1 row selected.

--NOTE: An almost 50% reduction in function calls with manual partitioning.

Tom Kyte
January 14, 2014 - 8:20 pm UTC

... It seems that scalar subquery caching is only really useful when there are a
relatively small number of distinct values due to the internal hash being 256
in size. ...

not necessarily true....

ops$tkyte%ORA11GR2> create or replace function f( x in number ) return number
  2  as
  3  begin
  4          dbms_application_info.set_client_info( to_number(sys_context('userenv','client_info'))+1 );
  5          return -x;
  6  end;
  7  /

Function created.

ops$tkyte%ORA11GR2> drop table t;

Table dropped.

ops$tkyte%ORA11GR2> create table t
  2  as
  3  select mod(rownum,1000) id
  4    from dual
  5  connect by level <= 100000
  6  /

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> exec dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> select * from t where f(id) > 0;

no rows selected

ops$tkyte%ORA11GR2> select sys_context('userenv','client_info') from dual;

SYS_CONTEXT('USERENV','CLIENT_INFO')
-------------------------------------------------------------------------------------------------------------------------
100000

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> exec dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> select * from t where (select f(id) from dual) > 0;

no rows selected

ops$tkyte%ORA11GR2> select sys_context('userenv','client_info') from dual;

SYS_CONTEXT('USERENV','CLIENT_INFO')
-------------------------------------------------------------------------------------------------------------------------
21691

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> exec dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> set autotrace on explain
ops$tkyte%ORA11GR2> select * from (select /*+ no_merge */ id from t order by id) where (select f(id) from dual) > 0;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1340550524

-------------------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |   117K|  1485K|       |  7921   (1)| 00:01:36 |
|*  1 |  FILTER              |      |       |       |       |            |          |
|   2 |   VIEW               |      |   117K|  1485K|       |   607   (1)| 00:00:08 |
|   3 |    SORT ORDER BY     |      |   117K|  1485K|  2304K|   607   (1)| 00:00:08 |
|   4 |     TABLE ACCESS FULL| T    |   117K|  1485K|       |    47   (3)| 00:00:01 |
|   5 |   FAST DUAL          |      |     1 |       |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter( (SELECT "F"(:B1) FROM "SYS"."DUAL" "DUAL")>0)

Note
-----
   - dynamic sampling used for this statement (level=2)

ops$tkyte%ORA11GR2> set autotrace off
ops$tkyte%ORA11GR2> select sys_context('userenv','client_info') from dual;

SYS_CONTEXT('USERENV','CLIENT_INFO')
-------------------------------------------------------------------------------------------------------------------------
1000



worst case natural ordering shows 1/5th of the function calls (not too shabby) and best case shows 1% of the function calls. In addition to the 256, the last is always cached as an extra bonus, so depending on the ordering of the data - it can have a material affect as well...


If I make your function a tad more cpu intensive, we can see that taking pains to order the inputs might be useful too:

ops$tkyte%ORA11GR2> create or replace function f( x in varchar2 ) return number
  2  as
  3          l_x number := 0;
  4  begin
  5          dbms_application_info.set_client_info(userenv('client_info')+1 );
  6                  for i in 1 .. length(x)
  7                  loop
  8                          l_x := l_x + ln(i);
  9                  end loop;
 10          return trunc(x);
 11  end;
 12  /

Function created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> variable cpu number
ops$tkyte%ORA11GR2> exec dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> exec :cpu := dbms_utility.get_cpu_time;

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> SELECT MAX(FO||OWNER) FROM
  2  (
  3      select owner, f(RN) FO
  4      from stage
  5  );

MAX(FO||OWNER)
----------------------------------------------------------------------
9SYS

ops$tkyte%ORA11GR2> select dbms_utility.get_cpu_time-:cpu cpu_hsecs, userenv('client_info') from dual;

 CPU_HSECS USERENV('CLIENT_INFO')
---------- ----------------------------------------------------------------
       422 80000

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> exec dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> exec :cpu := dbms_utility.get_cpu_time;

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> SELECT MAX(FO||OWNER) FROM
  2  (
  3      select owner, (SELECT f(RN) FROM DUAL) FO
  4      from stage
  5  );

MAX(FO||OWNER)
----------------------------------------------------------------------
9SYS

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select dbms_utility.get_cpu_time-:cpu cpu_hsecs, userenv('client_info') from dual;

 CPU_HSECS USERENV('CLIENT_INFO')
---------- ----------------------------------------------------------------
       360 61874

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> exec dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> exec :cpu := dbms_utility.get_cpu_time;

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> SELECT MAX(FO||OWNER) FROM
  2  (
  3      select owner, CASE MOD(RN,11)
  4                  WHEN 1  THEN (SELECT f(RN) FROM DUAL)
  5                  WHEN 2  THEN (SELECT f(RN) FROM DUAL)
  6                  WHEN 3  THEN (SELECT f(RN) FROM DUAL)
  7                  WHEN 4  THEN (SELECT f(RN) FROM DUAL)
  8                  WHEN 5  THEN (SELECT f(RN) FROM DUAL)
  9                  WHEN 6  THEN (SELECT f(RN) FROM DUAL)
 10                  WHEN 7  THEN (SELECT f(RN) FROM DUAL)
 11                  WHEN 8  THEN (SELECT f(RN) FROM DUAL)
 12                  WHEN 9  THEN (SELECT f(RN) FROM DUAL)
 13                  WHEN 10 THEN (SELECT f(RN) FROM DUAL)
 14                  ELSE         (SELECT f(RN) FROM DUAL)
 15                END FO
 16      from stage
 17  );

MAX(FO||OWNER)
----------------------------------------------------------------------
9SYS

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select dbms_utility.get_cpu_time-:cpu cpu_hsecs, userenv('client_info') from dual;

 CPU_HSECS USERENV('CLIENT_INFO')
---------- ----------------------------------------------------------------
       131 19700

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> exec dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> exec :cpu := dbms_utility.get_cpu_time;

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> set autotrace on explain
ops$tkyte%ORA11GR2> SELECT MAX(FO||OWNER) FROM
  2  (
  3      select owner, (SELECT f(RN) FROM DUAL) FO, xxx
  4      from (select rownum xxx, owner, rn from (select owner, rn from stage order by rn) )
  5  );

MAX(FO||OWNER)
----------------------------------------------------------------------
9SYS


Execution Plan
----------------------------------------------------------
Plan hash value: 50987422

----------------------------------------------------------------------------------------
| Id  | Operation              | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |       |     1 |    30 |       |   724   (1)| 00:00:09 |
|   1 |  FAST DUAL             |       |     1 |       |       |     2   (0)| 00:00:01 |
|   2 |  SORT AGGREGATE        |       |     1 |    30 |       |            |          |
|   3 |   VIEW                 |       | 70839 |  2075K|       |   724   (1)| 00:00:09 |
|   4 |    COUNT               |       |       |       |       |            |          |
|   5 |     VIEW               |       | 70839 |  2075K|       |   724   (1)| 00:00:09 |
|   6 |      SORT ORDER BY     |       | 70839 |  2075K|  2792K|   724   (1)| 00:00:09 |
|   7 |       TABLE ACCESS FULL| STAGE | 70839 |  2075K|       |   144   (1)| 00:00:02 |
----------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

ops$tkyte%ORA11GR2> set autotrace off
ops$tkyte%ORA11GR2> select dbms_utility.get_cpu_time-:cpu cpu_hsecs, userenv('client_info') from dual;

 CPU_HSECS USERENV('CLIENT_INFO')
---------- ----------------------------------------------------------------
        83 8000

Ordering the input works brilliantly.

Bobby, January 15, 2014 - 4:58 pm UTC

Fantastic idea Tom. Thanks for your time.

A reader, January 16, 2014 - 12:57 pm UTC

Hi Tom,

is the function is deterministic or non-deterministic impact sub-query caching?

Thank you,

Tom Kyte
January 16, 2014 - 6:54 pm UTC

no, sub query caching will work as before - but - it may *appear* to work even better with a deterministic function.

Deterministic functions can be cached for the duration of a call to the database. This caching happens in addition to the subquery caching. If you use a generous array size - it might appear that subquery caching is working even better (but it isn't, it is the deterministic function cache on top of the subquery cache that is doing that).


for example:

ops$tkyte%ORA11GR2> create table t as select * from all_objects;
ops$tkyte%ORA11GR2> exec dbms_stats.gather_table_stats(user,'T');
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create or replace function f( x in varchar2 ) return number
  2  as
  3  begin
  4          dbms_application_info.set_client_info(userenv('client_info')+1 );
  5          return length(x);
  6  end;
  7  /
ops$tkyte%ORA11GR2> exec :cpu := dbms_utility.get_cpu_time; dbms_application_info.set_client_info(0);
ops$tkyte%ORA11GR2> set autotrace traceonly statistics
ops$tkyte%ORA11GR2> select owner, f(owner) from t;

Statistics
----------------------------------------------------------
         17  recursive calls
          0  db block gets
       5876  consistent gets
          0  physical reads
          0  redo size
    1374112  bytes sent via SQL*Net to client
      54056  bytes received via SQL*Net from client
       4878  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      73141  rows processed

ops$tkyte%ORA11GR2> set autotrace off
ops$tkyte%ORA11GR2> select dbms_utility.get_cpu_time-:cpu cpu_hsecs, userenv('client_info') from dual;

 CPU_HSECS USERENV('CLIENT_INFO')
---------- ----------------------------------------------------------------
        78 73141

<b>Non-deterministic function, no scalar subquery caching - it gets called once per row.</b>


ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create or replace function f( x in varchar2 )
  2  return number
  3  DETERMINISTIC
  4  as
  5  begin
  6          dbms_application_info.set_client_info(userenv('client_info')+1 );
  7          return length(x);
  8  end;
  9  /
ops$tkyte%ORA11GR2> 

<b>make it deterministic...</b>

ops$tkyte%ORA11GR2> exec :cpu := dbms_utility.get_cpu_time; dbms_application_info.set_client_info(0);
ops$tkyte%ORA11GR2> set autotrace traceonly statistics
ops$tkyte%ORA11GR2> select owner, f(owner) from t;

Statistics
----------------------------------------------------------
         17  recursive calls
          0  db block gets
       5876  consistent gets
          0  physical reads
          0  redo size
    1374112  bytes sent via SQL*Net to client
      54056  bytes received via SQL*Net from client
       4878  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      73141  rows processed

ops$tkyte%ORA11GR2> set autotrace off
ops$tkyte%ORA11GR2> select dbms_utility.get_cpu_time-:cpu cpu_hsecs, userenv('client_info') from dual;

 CPU_HSECS USERENV('CLIENT_INFO')
---------- ----------------------------------------------------------------
        58 8413

<b>and the number of calls drops dramatically...</b>


ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> exec :cpu := dbms_utility.get_cpu_time; dbms_application_info.set_client_info(0);
ops$tkyte%ORA11GR2> set autotrace traceonly statistics
ops$tkyte%ORA11GR2> select owner, (select f(owner) from dual) from t;

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       5851  consistent gets
          0  physical reads
          0  redo size
    1374128  bytes sent via SQL*Net to client
      54056  bytes received via SQL*Net from client
       4878  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      73141  rows processed

ops$tkyte%ORA11GR2> set autotrace off
ops$tkyte%ORA11GR2> select dbms_utility.get_cpu_time-:cpu cpu_hsecs, userenv('client_info') from dual;

 CPU_HSECS USERENV('CLIENT_INFO')
---------- ----------------------------------------------------------------
        30 58

<b>deterministic + subquery caching drops it even more, but just increasing the array fetch size (sqlplus defaults to 15)</b>


ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> set arraysize 5000
ops$tkyte%ORA11GR2> exec :cpu := dbms_utility.get_cpu_time; dbms_application_info.set_client_info(0);
ops$tkyte%ORA11GR2> set autotrace traceonly statistics
ops$tkyte%ORA11GR2> select owner, (select f(owner) from dual) from t;

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1062  consistent gets
          0  physical reads
          0  redo size
     756685  bytes sent via SQL*Net to client
        574  bytes received via SQL*Net from client
         16  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      73141  rows processed

ops$tkyte%ORA11GR2> set autotrace off
ops$tkyte%ORA11GR2> select dbms_utility.get_cpu_time-:cpu cpu_hsecs, userenv('client_info') from dual;

 CPU_HSECS USERENV('CLIENT_INFO')
---------- ----------------------------------------------------------------
        11 43

<b>can make it go down even further.  Deterministic caches for the duration of the call within a single session, subquery caching for the duration of the statement within a single session, function result cache - across calls, statements and sessions.



this does not mean mark all functions as deterministic!!!! mark only those that are. A function that includes SQL itself is almost certainly NOT deterministic for example!!!


Florin Andrei, October 01, 2014 - 1:48 pm UTC

Hi Tom,
Regarding the above comment, situations where we shouldn't use this scalar query, is there any restriction when using scalar with parallel queries? I've created a really dummy example:

CREATE TABLE tab1
AS
SELECT *
FROM dba_objects;

CREATE TABLE tab2
AS
SELECT *
FROM dba_objects
WHERE 1=0;


CREATE OR REPLACE FUNCTION f_get_value (p_input1 VARCHAR2)
RETURN NUMBER
PARALLEL_ENABLE
IS
BEGIN
RETURN 1;
END f_get_value;
/

ALTER SESSION FORCE PARALLEL DML PARALLEL 15

INSERT INTO tab2
SELECT *
FROM tab1 t1
WHERE (SELECT f_get_value (t1.object_name) from dual) <>1;

seem like the scalar query is done in serial, is there any way we can force it to run in parallel?


------------------------------------------------------------------------------------------------------------
| Id| Operation | Name | Rows |Cost (%CPU)| Time | TQ |IN-OUT|PQ Distrib|
------------------------------------------------------------------------------------------------------------
| 0| INSERT STATEMENT | | 2108K| 4157K (1)| 13:51:33| | | |
| 1| PX COORDINATOR | | | | | | | |
| 2| PX SEND QC (RANDOM) | :TQ20001| 2108K| 805 (1)| 00:00:10| Q2,01| P->S | QC (RAND)|
| 3| LOAD AS SELECT | TAB2 | | | | Q2,01| PCWP | |
| 4| PX RECEIVE | | 2108K| 805 (1)| 00:00:10| Q2,01| PCWP | |
| 5| PX SEND ROUND-ROBIN | :TQ20000| 2108K| 805 (1)| 00:00:10| | S->P | RND-ROBIN|
|* 6| FILTER | | | | | | | |
| 7| PX COORDINATOR | | | | | | | |
| 8| PX SEND QC (RANDOM)| :TQ10000| 2108K| 805 (1)| 00:00:10| Q1,00| P->S | QC (RAND)|
| 9| PX BLOCK ITERATOR | | 2108K| 805 (1)| 00:00:10| Q1,00| PCWC | |
| 10| TABLE ACCESS FULL| TAB1 | 2108K| 805 (1)| 00:00:10| Q1,00| PCWP | |
| 11| FAST DUAL | | 1 | 2 (0)| 00:00:01| | | |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

6 - filter( (SELECT "F_GET_VALUE"(:B1) FROM "SYS"."DUAL" "DUAL")<>1)


Rashmi Ranjan, October 24, 2014 - 8:46 am UTC

Hi Tom,
Thank you very much for such explanotary information on scalar subquery hashing at http://www.oracle.com/technetwork/issue-archive/2011/11-sep/o51asktom-453438.html

I have few questions
What does exactly mean "next to" the has table ? is it another slot appart from 255 slots of hash tabe?
When Hash collision happens before 255 slots are filled then whether that has unique key and its hash value gets stored in the hash table?
what does mean by “partially cached.” ?

Thanks in advance for your answer.
Regards
Rashmi.

Functions in where clause

John, May 05, 2015 - 9:11 pm UTC

Hi Tom,
All the examples of this I've found are where functions are used in the select list. However; does this equally apply when a function is used in the where clause?

Select ...
From tables
Where tab.column = function(tab2.column2)


Etc?

Thanks
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library