tag:blogger.com,1999:blog-518481768015386858.post90806686442227590..comments2023-08-06T04:28:52.092-04:00Comments on Karen Morton: Analytics to the rescue - followupKarenhttp://www.blogger.com/profile/03309823327597536648noreply@blogger.comBlogger10125tag:blogger.com,1999:blog-518481768015386858.post-48983597590381631452008-08-08T11:31:00.000-04:002008-08-08T11:31:00.000-04:00Robert - You asked about using a join with an inli...Robert - <BR/><BR/>You asked about using a join with an inline view? I didn't test it as the results of the analytic were so great. It might certainly be another option. But, if the formulation of the inline view query required the main table to need to be accessed more than once, then my belief is that it wouldn't produce better results than the analytic.<BR/><BR/>Thanks.Karenhttps://www.blogger.com/profile/03309823327597536648noreply@blogger.comtag:blogger.com,1999:blog-518481768015386858.post-32538670240833910832008-08-08T03:35:00.000-04:002008-08-08T03:35:00.000-04:00Sorry to repeat myself, I hadn't seen your update ...Sorry to repeat myself, I hadn't seen your update when posting the comment to v1.0: what about using a join with an inline view?<BR/><BR/>Cheersroberthttps://www.blogger.com/profile/01724179181550310220noreply@blogger.comtag:blogger.com,1999:blog-518481768015386858.post-67125987490693850212008-08-04T17:24:00.000-04:002008-08-04T17:24:00.000-04:00Peter,Good eye! Yes, the original query should (a...Peter,<BR/><BR/>Good eye! Yes, the original query should (and does) have the two additional predicates. Apparently, when I copied the SQL, I missed the last two lines of the statement and didn't realize it. But, if you look at the plan output in the Predicate Information section, you'll see that in steps 4 & 5, those two predicates show up.<BR/><BR/>So, when I did the test, all the required predicates were there. I carelessly missed the last two lines when I copied the SQL into the blog post. <BR/><BR/>Sorry about that...and good catch!Karenhttps://www.blogger.com/profile/03309823327597536648noreply@blogger.comtag:blogger.com,1999:blog-518481768015386858.post-40045839288994719482008-08-04T17:11:00.000-04:002008-08-04T17:11:00.000-04:00Hi Karen,Are the 2 queries that you're comparing f...Hi Karen,<BR/><BR/>Are the 2 queries that you're comparing functionally equivalent? It seems the SQL that does not use the analytics should have 2 extra predicates in the outer query?<BR/><BR/>select utrsotp_desc <BR/>from utrsotp, ucbsvco <BR/>where ucbsvco_sotp_code = utrsotp_code <BR/>and ucbsvco_dispatch_date = <BR/> (select max(ucbsvco_dispatch_date) <BR/> from ucbsvco <BR/> where ucbsvco_cust_code = :b1 <BR/> and ucbsvco_prem_code = :b2 <BR/> and ucbsvco_stus_code = 'C' <BR/> and ucbsvco_dispatch_ind is not null)<BR/>and ucbsvco_cust_code = :b1 -- do you not need this predicate?<BR/>and ucbsvco_prem_code = :b2 -- do you not need this predicate?<BR/>and ucbsvco_stus_code = 'C' <BR/>and ucbsvco_dispatch_ind is not null;peterhttps://www.blogger.com/profile/05523160511269627118noreply@blogger.comtag:blogger.com,1999:blog-518481768015386858.post-68847424520834728212008-07-28T14:57:00.000-04:002008-07-28T14:57:00.000-04:00Oracle,Given your test data, the optimal performin...Oracle,<BR/><BR/>Given your test data, the optimal performing plan is the one derived by using the analytic. It goes back to the main point of this post, in that it's almost always better to access an object as few times as possible. With your NOT EXISTS query, the table "t" must have two access operations performed against it whereas the analytic syntax allows the optimizer to choose a plan which only requires one pass over the data.<BR/><BR/>Using your same test, simply increase the number of rows you add to your table and watch how much better the analytic version looks as the data volume grows. Your test showed consistent gets of 11 for the NOT EXISTS version and 4 for the analytic version. But, if you add a bunch more rows to the table, you'll see what looks pretty good in favor of the analytic look even better. A quick test I did to put about 500 records in the table, showed that the NOT EXISTS version used 578 consistent gets versus 7 for the analytic version. Try it yourself and see!Karenhttps://www.blogger.com/profile/03309823327597536648noreply@blogger.comtag:blogger.com,1999:blog-518481768015386858.post-86368275945302987712008-07-26T03:52:00.000-04:002008-07-26T03:52:00.000-04:00Hi,I am unable to decide whether to go with analyt...Hi,<BR/><BR/>I am unable to decide whether to go with analytic functions or not. Your help is appreciated.<BR/><BR/>drop table t purge;<BR/>create table t (dt date, sno number, amt number);<BR/>alter table t add constraint t_pk primary key (dt, sno);<BR/>insert into t select trunc(sysdate)-1, level, round(dbms_random.value(1, 1000)) from dual connect by level <=10;<BR/>insert into t select trunc(sysdate), 10+level, round(dbms_random.value(1, 1000)) from dual connect by level <= 3;<BR/>insert into t select trunc(sysdate), level, round(dbms_random.value(1, 1000)) from dual connect by level <= 3;<BR/>commit;<BR/>select * from t;<BR/><BR/>select * <BR/> from t t1<BR/> Where dt = trunc(sysdate) - 1<BR/> AND NOT EXISTS (select 1 <BR/> from t t2<BR/> where t2.dt = trunc(sysdate)<BR/> and t1.sno = t2.sno);<BR/><BR/>DT SNO AMT<BR/>--------- ---------- ----------<BR/>25-JUL-08 4 965<BR/>25-JUL-08 5 944<BR/>25-JUL-08 6 20<BR/>25-JUL-08 7 44<BR/>25-JUL-08 8 458<BR/>25-JUL-08 9 848<BR/>25-JUL-08 10 245<BR/><BR/>7 rows selected.<BR/><BR/><BR/>Execution Plan<BR/>----------------------------------------------------------<BR/>Plan hash value: 862936642<BR/><BR/>-------------------------------------------------------------------------------------<BR/>| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<BR/>-------------------------------------------------------------------------------------<BR/>| 0 | SELECT STATEMENT | | 4 | 100 | 2 (0)| 00:00:01 |<BR/>| 1 | NESTED LOOPS ANTI | | 4 | 100 | 2 (0)| 00:00:01 |<BR/>| 2 | TABLE ACCESS BY INDEX ROWID| T | 10 | 140 | 2 (0)| 00:00:01 |<BR/>|* 3 | INDEX RANGE SCAN | T_PK | 10 | | 1 (0)| 00:00:01 |<BR/>|* 4 | INDEX UNIQUE SCAN | T_PK | 3 | 33 | 0 (0)| 00:00:01 |<BR/>-------------------------------------------------------------------------------------<BR/><BR/>Predicate Information (identified by operation id):<BR/>---------------------------------------------------<BR/><BR/> 3 - access("DT"=TRUNC(SYSDATE@!)-1)<BR/> 4 - access("T2"."DT"=TRUNC(SYSDATE@!) AND "T1"."SNO"="T2"."SNO")<BR/><BR/><BR/>Statistics<BR/>----------------------------------------------------------<BR/> 0 recursive calls<BR/> 0 db block gets<BR/> 11 consistent gets<BR/> 0 physical reads<BR/> 0 redo size<BR/> 672 bytes sent via SQL*Net to client<BR/> 399 bytes received via SQL*Net from client<BR/> 2 SQL*Net roundtrips to/from client<BR/> 0 sorts (memory)<BR/> 0 sorts (disk)<BR/> 7 rows processed<BR/><BR/>SQL><BR/><BR/><BR/>With analytic function:<BR/><BR/>select dt, sno, amt from (<BR/>select dt, sno, amt,<BR/> lead(dt) over (partition by sno order by dt) next_dt,<BR/> lead(sno) over (partition by dt order by sno) next_sno<BR/> from t t1<BR/> Where dt in ( trunc(sysdate), trunc(sysdate) - 1))<BR/> Where dt = trunc(sysdate) - 1<BR/> and next_dt is null;<BR/><BR/><BR/>DT SNO AMT<BR/>--------- ---------- ----------<BR/>25-JUL-08 4 965<BR/>25-JUL-08 5 944<BR/>25-JUL-08 6 20<BR/>25-JUL-08 7 44<BR/>25-JUL-08 8 458<BR/>25-JUL-08 9 848<BR/>25-JUL-08 10 245<BR/><BR/>7 rows selected.<BR/><BR/><BR/>Execution Plan<BR/>----------------------------------------------------------<BR/>Plan hash value: 2843002491<BR/><BR/>---------------------------------------------------------------------------------------<BR/>| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<BR/>---------------------------------------------------------------------------------------<BR/>| 0 | SELECT STATEMENT | | 16 | 704 | 3 (34)| 00:00:01 |<BR/>|* 1 | VIEW | | 16 | 704 | 3 (34)| 00:00:01 |<BR/>| 2 | WINDOW SORT | | 16 | 224 | 3 (34)| 00:00:01 |<BR/>| 3 | INLIST ITERATOR | | | | | |<BR/>| 4 | TABLE ACCESS BY INDEX ROWID| T | 16 | 224 | 2 (0)| 00:00:01 |<BR/>|* 5 | INDEX RANGE SCAN | T_PK | 16 | | 1 (0)| 00:00:01 |<BR/>---------------------------------------------------------------------------------------<BR/><BR/>Predicate Information (identified by operation id):<BR/>---------------------------------------------------<BR/><BR/> 1 - filter("DT"=TRUNC(SYSDATE@!)-1 AND "NEXT_DT" IS NULL)<BR/> 5 - access("DT"=TRUNC(SYSDATE@!) OR "DT"=TRUNC(SYSDATE@!)-1)<BR/><BR/><BR/>Statistics<BR/>----------------------------------------------------------<BR/> 0 recursive calls<BR/> 0 db block gets<BR/> 4 consistent gets<BR/> 0 physical reads<BR/> 0 redo size<BR/> 624 bytes sent via SQL*Net to client<BR/> 399 bytes received via SQL*Net from client<BR/> 2 SQL*Net roundtrips to/from client<BR/> 1 sorts (memory)<BR/> 0 sorts (disk)<BR/> 7 rows processed<BR/><BR/><BR/>I am extremely sorry as I do not know how to format code on blogspot.<BR/><BR/>Thanks for taking my question.<BR/><BR/>RegardsAnonymoushttps://www.blogger.com/profile/15495662504665830930noreply@blogger.comtag:blogger.com,1999:blog-518481768015386858.post-5013361578864716842008-07-21T18:03:00.000-04:002008-07-21T18:03:00.000-04:00Karen,Thanks for the info.I have searched on askto...Karen,<BR/><BR/>Thanks for the info.<BR/>I have searched on asktom and Jonathan Lewis' blog for scalar subquery caching and the given examples where all about subqueries that where linked with the parent query or using functions.<BR/><BR/>In the query in the orginal post however the subquery was independent of the parent query, meaning it is sure to return the same value for each of the rows from the parent query.<BR/><BR/>When I did a quick test with the dbms_xplan.display_cursor function, the stats indicated that the subquery was only executing the subquery just once in my example.<BR/><BR/>But this could be either the result of the caching or because oracle knows that the subquery result will always be the same.<BR/><BR/>sys@GUNNAR> select count(*)<BR/> 2 from T1<BR/> 3 where veld2 =<BR/> 4 ( select max(veld1)<BR/> 5 from T2<BR/> 6 );<BR/><BR/> COUNT(*)<BR/>----------<BR/> 1000<BR/><BR/>sys@GUNNAR> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));<BR/><BR/>PLAN_TABLE_OUTPUT<BR/>------------------------------------------------------------------------------------------------------------------------<BR/>SQL_ID gqf8p77hszd3a, child number 0<BR/>-------------------------------------<BR/>select count(*) from T1 where veld2 = ( select max(veld1) from T2<BR/> )<BR/><BR/>Plan hash value: 2872123968<BR/><BR/>---------------------------------------------------------------------------------------<BR/>| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |<BR/>---------------------------------------------------------------------------------------<BR/>| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.03 | 26 |<BR/>|* 2 | TABLE ACCESS FULL | T1 | 1 | 1000 | 1000 |00:00:00.03 | 26 |<BR/>| 3 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 3 |<BR/>| 4 | TABLE ACCESS FULL| T2 | 1 | 10 | 10 |00:00:00.01 | 3 |<BR/>---------------------------------------------------------------------------------------<BR/><BR/>Predicate Information (identified by operation id):<BR/>---------------------------------------------------<BR/><BR/> 2 - filter("VELD2"=)<BR/><BR/><BR/>21 rows selected.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-518481768015386858.post-24546287567988513672008-07-21T12:56:00.000-04:002008-07-21T12:56:00.000-04:00Freek,The number of times a subquery is executed d...Freek,<BR/><BR/>The number of times a subquery is executed depends on several things. When you have a subquery in the WHERE clause (as in this example), it can be executed for each row that must pass through the test that subquery filter is performing. <BR/><BR/>I want to state that I'm not absolutely sure that this particular query would get cached. Normally, Oracle will provide a caching mechanism for scalar subqueries and I'm not absolutely positive that this subquery would qualify as such after giving it a bit more thought. But, if Oracle was able to use a cache for this, you are correct that there would be a bit of overhead to check the cache, but that (as you can imagine) would be much less resource intensive than executing the SQL every time.<BR/><BR/>Both Tom Kyte and Jonathan Lewis have written about scalar subquery caching and I'd suggest searching their sites for "scalar subquery caching" to get some great examples of how to verify caching occurs. <BR/><BR/>As far as values disappearing from the cache, the answer is "yes"...this can happen. If you have more distinct values in the cache than it can hold (limits of the cache size vary based on Oracle version), then the oldest values in the cache will be replaced with newer values.<BR/><BR/>Again, I'm now questioning whether or not the original SQL in this post would have used caching but if it did that would be "good" and if it didn't do it, then the behavior of the query would be to execute the subquery for each row that must pass the WHERE clause successfully.Karenhttps://www.blogger.com/profile/03309823327597536648noreply@blogger.comtag:blogger.com,1999:blog-518481768015386858.post-71890442093226203142008-07-19T04:16:00.000-04:002008-07-19T04:16:00.000-04:00Thanks for the update.I always thought that when y...Thanks for the update.<BR/><BR/>I always thought that when you have a subquery that is not linked to the parent query, oracle would always execute the subquery only once.<BR/><BR/>But from your post I understand that oracle would cache the result and recheck the input values. So even if the query itself is not executed there would be an overhead because of the input value checking.<BR/>Is my understanding correct?<BR/>Is there a way to know if the result caching occurs?<BR/>Could it happen that a result "dissappears" from the cache?<BR/><BR/>regards,<BR/><BR/>FreekAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-518481768015386858.post-55732902287011346892008-07-18T10:31:00.000-04:002008-07-18T10:31:00.000-04:00Excellent set of posts. The additional informatio...Excellent set of posts. The additional information with the STATs and your explanations is much appreciated. I will be sure to test the analytic approach the next time I have a similar problem.Craig Martinhttps://www.blogger.com/profile/01210939485432835552noreply@blogger.com