tag:blogger.com,1999:blog-2257062243512934022.post7174821376812979390..comments2023-10-31T18:51:23.836+05:30Comments on Sachin Arora's Blog: Index range Scan Vs Nested loop in IN-LIST ?Sachinhttp://www.blogger.com/profile/04551992629821526364noreply@blogger.comBlogger21125tag:blogger.com,1999:blog-2257062243512934022.post-8519636026225932292010-05-10T14:12:45.721+05:302010-05-10T14:12:45.721+05:30thanksthanksmsh gameshttp://mshgroup.blogspot.com/noreply@blogger.comtag:blogger.com,1999:blog-2257062243512934022.post-6264246520607721022009-09-07T21:21:24.847+05:302009-09-07T21:21:24.847+05:30For me, using the 9i method, takes 4 seconds, whil...For me, using the 9i method, takes 4 seconds, while your method(using binds) takes 1 second.<br /><br />So, its 4 times slower. I have to check how to reduce it further.<br /><br />If I could reach anywhere near, I will post here.Rajesh Subramaniamhttps://www.blogger.com/profile/11297002277169324841noreply@blogger.comtag:blogger.com,1999:blog-2257062243512934022.post-20645482679407234612009-09-07T09:08:28.379+05:302009-09-07T09:08:28.379+05:30Hi Sachin,
Try the function based approach to ins...Hi Sachin,<br /><br />Try the function based approach to insert the list into GTT(this will avoid the number of context switches) and then use the GTT in the join.<br /><br />BTW, Did you try the 9i method mentioned in Tom Kyte's blog. Could you please share the results of that. Mean while I will also try to try that approach with your test case.Rajesh Subramaniamhttps://www.blogger.com/profile/11297002277169324841noreply@blogger.comtag:blogger.com,1999:blog-2257062243512934022.post-59684408151454412792009-07-25T04:32:29.503+05:302009-07-25T04:32:29.503+05:30sachin, r u the guy from t-474, arora hostel, balj...sachin, r u the guy from t-474, arora hostel, baljit nagar, delhi?neohttps://www.blogger.com/profile/17253239815778029886noreply@blogger.comtag:blogger.com,1999:blog-2257062243512934022.post-88167330103235082112009-07-02T15:22:30.267+05:302009-07-02T15:22:30.267+05:30(tab)HI Narender,
Sorry for late update.
My conc...(tab)HI Narender,<br /><br />Sorry for late update.<br /><br />My concern is : In case of GTT - we will have to insert the values into a tempirary table. This will be a context switch b/w client process and db server and only after the data (that needs to be there in IN clause) is present in server server cache (Global temporary table), we can use that.<br /><br />insert into (GTT) values ('[value]');<br /><br />insert into (GTT) values ('[value]');<br /><br />insert into (GTT) values ('[value]');<br /><br />insert into (GTT) values ('[value]');<br /><br />insert into (GTT) values ('[value]');<br />..<br />..<br />..<br /><br />select * from (tab) where (col) in (select (col) from (GTT));<br /><br />The additional cost of INSERT can be heavy.<br /><br />Do you have a better way to optimize that.<br /><br />Regards,<br />SachinSachinhttps://www.blogger.com/profile/04551992629821526364noreply@blogger.comtag:blogger.com,1999:blog-2257062243512934022.post-55806916488799541662009-02-14T23:20:00.000+05:302009-02-14T23:20:00.000+05:30SachinI haven't had a chance to experiment further...Sachin<BR/><BR/>I haven't had a chance to experiment further, but <A HREF="http://www.freelists.org/post/oracle-l/Pass-multiple-numeric-values-to-a-function" REL="nofollow">here's a thread on the Oracle-L list</A> that gives another way of breaking a list into a join using sys_connect_by_path - this time without requiring a user function call and so the context-change that Narendra is worried about. See in particular Stephane Faroult's contribution (2nd in the trail).<BR/><BR/>Regards NigelNigelhttps://www.blogger.com/profile/10826219818302312878noreply@blogger.comtag:blogger.com,1999:blog-2257062243512934022.post-20838468064832026532009-02-12T15:00:00.000+05:302009-02-12T15:00:00.000+05:30Sachin,Agreed. But I am still not sure why you thi...Sachin,<BR/><BR/>Agreed. But I am still not sure why you think nested loop is the cause of high CPU usage, which is resulting in poor response time. In your example, it is evident that context-switch is causing excessive CPU usage and affecting poorperformance. If your goal is to achieve better response time without (much) compromising on latches, CPU etc., shouldn't you be looking at addressing the real issue ?Narendrahttps://www.blogger.com/profile/14645699853364658640noreply@blogger.comtag:blogger.com,1999:blog-2257062243512934022.post-72976239731543941882009-02-11T23:49:00.000+05:302009-02-11T23:49:00.000+05:30@Narendra - this is what i'm searching -- is there...@Narendra - this is what i'm searching -- is there a way to use "INLIST ITERATOR" as a substitue to nested (or for that matter any kind of join) loop. <BR/>I just gave a few min try to user push_subq to force that but I could not avoid joins<BR/><BR/>Hoping we can find something like that. <BR/><BR/>-- SachinSachinhttps://www.blogger.com/profile/04551992629821526364noreply@blogger.comtag:blogger.com,1999:blog-2257062243512934022.post-41747289803200420342009-02-11T17:15:00.000+05:302009-02-11T17:15:00.000+05:30Sachin,I am not sure I understood you there. You a...Sachin,<BR/><BR/>I am not sure I understood you there. You are saying <I>I was trying to somehow force INDEX RANGE scan (without nested loops) using GTT - but was not able to do so</I>. But Oracle will <B>have to</B> use some joining mechanism when more than one "tables" are involved. How can you hope of getting rid of nested loop with GTT ?<BR/>Your first query<BR/><I><BR/>SELECT COUNT(*)<BR/>FROM<BR/>TEST WHERE R IN (1,2,3,4,5,6,7,8,9,10 )<BR/></I><BR/>uses only one table (TEST) and hence Oracle does not need to use any of the JOIN mechanisms.<BR/><BR/>Your second query<BR/><I><BR/>SELECT COUNT(*)<BR/>FROM<BR/>TEST WHERE R IN ( SELECT /*+ cardinality(10) */ * FROM THE ( SELECT CAST(<BR/><BR/>CONVERT2TABLE( '1,2,3,4,5,6,7,8,9,10' ) AS NUMBERTABLETYPE ) FROM DUAL ) )<BR/></I><BR/>is using 2 "tables" (TEST and resultset generated from function) and hence oracle is using NESTED LOOP joining mechanism (but it is also using INDEX RANGE scan to access TEST table).<BR/><BR/>This will be true even for GTT. If you use a query of the form<BR/><I><BR/>SELECT COUNT(*)<BR/>FROM<BR/>TEST WHERE R IN ( SELECT col from GTT )<BR/></I><BR/>Oracle will still treat it as query with 2 tables and use a joining mechanism.<BR/>Am I missing something obvious here ?Narendrahttps://www.blogger.com/profile/14645699853364658640noreply@blogger.comtag:blogger.com,1999:blog-2257062243512934022.post-70648723297271418042009-02-11T16:52:00.000+05:302009-02-11T16:52:00.000+05:30Hi Narendra,That is precisely my argument is: i fe...Hi Narendra,<BR/><BR/>That is precisely my argument is: i feel whenever we use NESTED Loop -- we spend more cpu time Vs doing a range scan .. probably because of going back to root block and traversing to leaf "n" number of times rather than getting to leaf and walk horizontally (INDEX range scan). I was trying to somehow force INDEX RANGE scan (without nested loops) using GTT - but was not able to do so.<BR/><BR/>If you can give it a try and share your experiment - it would be great - i will also try the same.<BR/><BR/>--SachinSachinhttps://www.blogger.com/profile/04551992629821526364noreply@blogger.comtag:blogger.com,1999:blog-2257062243512934022.post-52787028966105648522009-02-11T16:45:00.000+05:302009-02-11T16:45:00.000+05:30Sachin,Personally, I am not quite comfortable with...Sachin,<BR/><BR/>Personally, I am not quite comfortable with having an IN clause with more than 20-30 values in it. If the values in IN list can go upto 1000, I would actually take a global temporary table approach (where I add all the values in a GTT and then the SQL becomes SELECT ... FROM tbl WHERE r in (SELECT col FROM GTT)). I guess that should also take care of manually defining buckets and shared SQL.<BR/>Will be happy to know if you have faced any issues with GTT approach.Narendrahttps://www.blogger.com/profile/14645699853364658640noreply@blogger.comtag:blogger.com,1999:blog-2257062243512934022.post-16264696400241755472009-02-11T16:37:00.000+05:302009-02-11T16:37:00.000+05:30I agree about cursor sharing; I had to do it on on...I agree about cursor sharing; I had to do it on one system written without bind variables, and it helped a lot - but it's <B>much</B> better to code the app right in the first place whenever possible.<BR/><BR/>I'll try to do the tests bucket vs function. <BR/><BR/>Regards NigelNigelhttps://www.blogger.com/profile/10826219818302312878noreply@blogger.comtag:blogger.com,1999:blog-2257062243512934022.post-70241267405885896212009-02-11T16:25:00.000+05:302009-02-11T16:25:00.000+05:30Thanks Narendra and Nigel for your replies ..@nare...Thanks Narendra and Nigel for your replies ..<BR/><BR/>@narendra - Although i havent read much on context switching to comment butu logically agree with your context switching argument.<BR/>But - we need to take this argument to a conclusion that in which case it could be worse than bucket model which i suggested in my original post.<BR/><BR/>@nigel - thx for your updates - cursor_sharing is critical parameter to change in any application -- so first look, i'm against it. however trying to choose one of 2 (function based,bucket) based approach.<BR/>If you get a chance - please share your views.. thx!<BR/><BR/>-- SachinSachinhttps://www.blogger.com/profile/04551992629821526364noreply@blogger.comtag:blogger.com,1999:blog-2257062243512934022.post-14009788062473888232009-02-11T16:05:00.000+05:302009-02-11T16:05:00.000+05:30Narendra asked the wrong question. As Sachin expla...Narendra asked the wrong question. As Sachin explained in the original post, the real app uses bind variables - with a cunning trick of IN list buckets to support lists of 16, 32, 64, up to 1000 elements.<BR/><BR/>So I guess we should be asking - for a set of varying sized IN lists does the function based approach work better than<BR/>1) hard coded IN lists (no binds)<BR/>2) soft coded IN lists (buckets)<BR/>3) function based solution<BR/><BR/>(1) obviously causes lots of hard parses (or use SHARE_CURSORS=FORCE - which is like Oracle doing the buckets for you).<BR/><BR/>If I get the chance I will try to test all three. The test is only valid if both the list values and the length of the list vary.<BR/><BR/>Anyway, it's an interesting discussion!<BR/><BR/>Regards NigelNigelhttps://www.blogger.com/profile/10826219818302312878noreply@blogger.comtag:blogger.com,1999:blog-2257062243512934022.post-25471683798174559122009-02-11T15:57:00.000+05:302009-02-11T15:57:00.000+05:30Sachin,You are correct. However, what made you thi...Sachin,<BR/><BR/>You are correct. However, what made you think that function model causes extra hard parses ? In the row-source operation you posted, I could see that the statement is getting parsed only once for 10000 executions. It appears that using function approach is indeed consuming more CPU time. This seems to be because the function is being called once per execution. Now your test case is executing the SELECT 10000 times, which means there is a context-switch (calling PL/SQL function from SQL) taking place 10000 times. The context-switch does cause excessive CPU usage. When you use "IN (1,2,3,4,5,6,7,8,9,10)" approach, there is no PL/SQL involved and hence no context-switching. You can verify this with creating even a simple function and executing it 10000 or more times from within SQL versus a SQL (or PL/SQL) only approach. I can not access TKProf on my database server and hence can not post results for my reason.<BR/><B>Is extra hard parses good anough reason to switch to high CPU model?</B> Now this seems to be a question that deserves the famous Tom Kyte answer "It depends".Narendrahttps://www.blogger.com/profile/14645699853364658640noreply@blogger.comtag:blogger.com,1999:blog-2257062243512934022.post-73369715470521875682009-02-11T11:07:00.000+05:302009-02-11T11:07:00.000+05:30Narendra,Thanks for checking this ..However I chec...Narendra,<BR/><BR/>Thanks for checking this ..<BR/><BR/>However I checked the way cardinality hint can be provided and found both ways (your and mine) should work with same results:<BR/><BR/>See here:<BR/><BR/><BR/>SQL> select * from v$version<BR/> 2 /<BR/><BR/>BANNER<BR/>----------------------------------------------------------------<BR/>Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi<BR/>PL/SQL Release 10.2.0.2.0 - Production<BR/>CORE 10.2.0.2.0 Production<BR/>TNS for Linux: Version 10.2.0.2.0 - Production<BR/>NLSRTL Version 10.2.0.2.0 - Production<BR/><BR/>SQL><BR/> 1 SELECT COUNT(*)<BR/> 2 FROM<BR/> 3 TEST WHERE R IN ( SELECT /*+ cardinality(10) */ * FROM THE ( SELECT CAST(<BR/> 4* NUM2TBL( '1,2,3,4,5,6,7,8,9,10' ) AS NUM2TBLTYPE ) FROM DUAL ) )<BR/>SYSTEM: irfc01s3> /<BR/><BR/> COUNT(*)<BR/>----------<BR/> 10<BR/><BR/>Elapsed: 00:00:00.10<BR/><BR/>Execution Plan<BR/>----------------------------------------------------------<BR/> 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=31 Card=1 Bytes=17<BR/> )<BR/><BR/> 1 0 SORT (AGGREGATE)<BR/> 2 1 NESTED LOOPS (Cost=31 Card=1 Bytes=17)<BR/> 3 2 VIEW OF 'VW_NSO_1' (VIEW) (Cost=29 Card=10 Bytes=130) -- See the cardinality here -- it is 10, which is what i want.<BR/> 4 3 HASH (UNIQUE)<BR/> 5 4 COLLECTION ITERATOR (PICKLER FETCH) OF 'NUM2TBL' (<BR/> PROCEDURE)<BR/><BR/> 6 5 FAST DUAL (Cost=2 Card=1)<BR/> 7 2 INDEX (RANGE SCAN) OF 'TEST_UX' (INDEX) (Cost=1 Card=1<BR/> Bytes=4)<BR/><BR/><BR/><BR/><BR/><BR/>Statistics<BR/>----------------------------------------------------------<BR/> 0 recursive calls<BR/> 0 db block gets<BR/> 12 consistent gets<BR/> 0 physical reads<BR/> 0 redo size<BR/> 336 bytes sent via SQL*Net to client<BR/> 427 bytes received via SQL*Net from client<BR/> 2 SQL*Net roundtrips to/from client<BR/> 0 sorts (memory)<BR/> 0 sorts (disk)<BR/> 1 rows processed<BR/><BR/><BR/>The difference b/w your and my run is probably the SQL itself. You are using:<BR/>SELECT COUNT(*)<BR/>FROM<BR/> TEST WHERE R IN ( SELECT COUNT(*) FROM TEST WHERE R IN ( SELECT /*+<BR/> CARDINALITY(t 10) */ * FROM TABLE(CAST(NUM2TBL('1,2,3,4,5,6,7,8,9,10') AS<BR/> NUM2TBLTYPE)) T) )<BR/><BR/>and I'm using :<BR/>SELECT COUNT(*)<BR/>FROM<BR/> TEST WHERE R IN ( SELECT /*+ cardinality(10) */ * FROM THE ( SELECT CAST(<BR/> NUM2TBL( '1,2,3,4,5,6,7,8,9,10' ) AS NUM2TBLTYPE ) FROM DUAL ) )<BR/><BR/><BR/>Apart from cardinality hint which has different syntax (but not making any change) -- you are using table function and i'm using DUAL table -- which seems to be making the difference.<BR/><BR/>The new SQL posted by Narendra has following plan and cpu utilization:<BR/><BR/>SELECT COUNT(*)<BR/>FROM<BR/> TEST WHERE R IN ( SELECT COUNT(*) FROM TEST WHERE R IN ( SELECT /*+<BR/> CARDINALITY(t 10) */ * FROM TABLE(CAST(NUM2TBL('1,2,3,4,5,6,7,8,9,10') AS<BR/> NUM2TBLTYPE)) T) )<BR/><BR/><BR/>call count cpu elapsed disk query current rows<BR/>------- ------ -------- ---------- ---------- ---------- ---------- ----------<BR/>Parse 1 0.00 0.00 0 24 0 0<BR/>Execute 10000 4.41 4.45 0 120000 0 0<BR/>Fetch 10000 0.33 0.32 0 20000 0 10000<BR/>------- ------ -------- ---------- ---------- ---------- ---------- ----------<BR/>total 20001 4.75 4.79 0 140024 0 10000<BR/><BR/><BR/>In this case also, i see cpu utlization almost double when compared to simple IN-List.<BR/><BR/>Is extra hard parses good anough reason to switch to high CPU model?Sachinhttps://www.blogger.com/profile/04551992629821526364noreply@blogger.comtag:blogger.com,1999:blog-2257062243512934022.post-59487991129001106862009-02-10T19:55:00.000+05:302009-02-10T19:55:00.000+05:30Sachin,I tested your code, with slight modificatio...Sachin,<BR/><BR/>I tested your code, with slight modification. I changed the way CARDINALITY hint is used. When tested on 10g (I don't have 9i to verify), I got the same plan as yours with the way you mentioned hint. With my way, plan changed. So that might be the reason. Following is my test:<BR/><B><BR/>SQL> select * from v$version ;<BR/><BR/>BANNER <BR/>---------------------------------------------------------------- <BR/>Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi <BR/>PL/SQL Release 10.2.0.4.0 - Production <BR/>CORE 10.2.0.4.0 Production <BR/>TNS for Solaris: Version 10.2.0.4.0 - Production <BR/>NLSRTL Version 10.2.0.4.0 - Production <BR/><BR/>SQL> set timing on<BR/>SQL> declare<BR/> 2 a number ;<BR/> 3 begin<BR/> 4 for i in 1..10000<BR/> 5 loop<BR/> 6 select count(*) into a from test_tbl where r in (1,2,3,4,5,6,7,8,9,10) ;<BR/> 7 end loop;<BR/> 8 end ;<BR/> 9 /<BR/><BR/>PL/SQL procedure successfully completed.<BR/><BR/>Elapsed: 00:00:01.01<BR/>SQL> declare<BR/> 2 a number ;<BR/> 3 begin<BR/> 4 for i in 1..10000<BR/> 5 loop<BR/> 6 select count(*) into a from test_tbl where r in ( select /*+ CARDINALITY(t 10) */ *<BR/> 7 from TABLE(cast(num2tbl('1,2,3,4,5,6,7,8,9,10') as numtbltype)) t) ;<BR/> 8 end loop ;<BR/> 9 end ;<BR/> 10 /<BR/><BR/>PL/SQL procedure successfully completed.<BR/><BR/>Elapsed: 00:00:02.05<BR/>SQL> set autotrace on<BR/>SQL> select count(*) from test_tbl where r in (1,2,3,4,5,6,7,8,9,10) ;<BR/><BR/> COUNT(*) <BR/>---------- <BR/> 10 <BR/><BR/>Elapsed: 00:00:00.00<BR/><BR/>Execution Plan<BR/>---------------------------------------------------------- <BR/> 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=10 Card=1 Bytes=4) <BR/> 1 0 SORT (AGGREGATE) <BR/> 2 1 INLIST ITERATOR <BR/> 3 2 INDEX (RANGE SCAN) OF 'TEST_TBL_IDX' (INDEX) (Cost=10 <BR/> Card=10 Bytes=40) <BR/> <BR/><BR/><BR/><BR/><BR/>Statistics<BR/>---------------------------------------------------------- <BR/> 1 recursive calls <BR/> 0 db block gets <BR/> 20 consistent gets <BR/> 0 physical reads <BR/> 0 redo size <BR/> 203 bytes sent via SQL*Net to client <BR/> 276 bytes received via SQL*Net from client <BR/> 2 SQL*Net roundtrips to/from client <BR/> 0 sorts (memory) <BR/> 0 sorts (disk) <BR/> 1 rows processed <BR/><BR/>SQL> select count(*) from test_tbl where r in (1,2,3,4,5,6,7,8,9,10) ;<BR/><BR/> COUNT(*) <BR/>---------- <BR/> 10 <BR/><BR/>Elapsed: 00:00:00.00<BR/><BR/>Execution Plan<BR/>---------------------------------------------------------- <BR/> 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=10 Card=1 Bytes=4) <BR/> 1 0 SORT (AGGREGATE) <BR/> 2 1 INLIST ITERATOR <BR/> 3 2 INDEX (RANGE SCAN) OF 'TEST_TBL_IDX' (INDEX) (Cost=10 <BR/> Card=10 Bytes=40) <BR/> <BR/><BR/><BR/><BR/><BR/>Statistics<BR/>---------------------------------------------------------- <BR/> 0 recursive calls <BR/> 0 db block gets <BR/> 20 consistent gets <BR/> 0 physical reads <BR/> 0 redo size <BR/> 217 bytes sent via SQL*Net to client <BR/> 276 bytes received via SQL*Net from client <BR/> 2 SQL*Net roundtrips to/from client <BR/> 0 sorts (memory) <BR/> 0 sorts (disk) <BR/> 1 rows processed <BR/><BR/>SQL> select count(*) from test_tbl where r in ( select /*+ CARDINALITY(t 10) */ *<BR/> 2 from TABLE(cast(num2tbl('1,2,3,4,5,6,7,8,9,10') as numtbltype)) t) ;<BR/><BR/> COUNT(*) <BR/>---------- <BR/> 10 <BR/><BR/>Elapsed: 00:00:00.00<BR/><BR/>Execution Plan<BR/>---------------------------------------------------------- <BR/> 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=30 Card=1 Bytes=6) <BR/> 1 0 SORT (AGGREGATE) <BR/> 2 1 NESTED LOOPS (Cost=30 Card=10 Bytes=60) <BR/> 3 2 SORT (UNIQUE) <BR/> 4 3 COLLECTION ITERATOR (PICKLER FETCH) OF 'NUM2TBL' (PR <BR/> OCEDURE) <BR/> <BR/> 5 2 INDEX (RANGE SCAN) OF 'TEST_TBL_IDX' (INDEX) (Cost=1 C <BR/> ard=1 Bytes=4) <BR/> <BR/><BR/><BR/><BR/><BR/>Statistics<BR/>---------------------------------------------------------- <BR/> 12 recursive calls <BR/> 0 db block gets <BR/> 39 consistent gets <BR/> 0 physical reads <BR/> 0 redo size <BR/> 217 bytes sent via SQL*Net to client <BR/> 276 bytes received via SQL*Net from client <BR/> 2 SQL*Net roundtrips to/from client <BR/> 1 sorts (memory) <BR/> 0 sorts (disk) <BR/> 1 rows processed <BR/><BR/>SQL> select count(*) from test_tbl where r in ( select /*+ CARDINALITY(t 10) */ *<BR/> 2 from TABLE(cast(num2tbl('1,2,3,4,5,6,7,8,9,10') as numtbltype)) t) ;<BR/><BR/> COUNT(*) <BR/>---------- <BR/> 10 <BR/><BR/>Elapsed: 00:00:00.00<BR/><BR/>Execution Plan<BR/>---------------------------------------------------------- <BR/> 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=30 Card=1 Bytes=6) <BR/> 1 0 SORT (AGGREGATE) <BR/> 2 1 NESTED LOOPS (Cost=30 Card=10 Bytes=60) <BR/> 3 2 SORT (UNIQUE) <BR/> 4 3 COLLECTION ITERATOR (PICKLER FETCH) OF 'NUM2TBL' (PR <BR/> OCEDURE) <BR/> <BR/> 5 2 INDEX (RANGE SCAN) OF 'TEST_TBL_IDX' (INDEX) (Cost=1 C <BR/> ard=1 Bytes=4) <BR/> <BR/><BR/><BR/><BR/><BR/>Statistics<BR/>---------------------------------------------------------- <BR/> 0 recursive calls <BR/> 0 db block gets <BR/> 12 consistent gets <BR/> 0 physical reads <BR/> 0 redo size <BR/> 217 bytes sent via SQL*Net to client <BR/> 276 bytes received via SQL*Net from client <BR/> 2 SQL*Net roundtrips to/from client <BR/> 1 sorts (memory) <BR/> 0 sorts (disk) <BR/> 1 rows processed <BR/><BR/>SQL> set autotrace off<BR/>SQL> spool off<BR/></B>Narendrahttps://www.blogger.com/profile/14645699853364658640noreply@blogger.comtag:blogger.com,1999:blog-2257062243512934022.post-4971140754016846572009-02-10T00:30:00.000+05:302009-02-10T00:30:00.000+05:30Narendra,i I did make us of cardinality hint in my...Narendra,<BR/><BR/>i I did make us of cardinality hint in my original post- Still i see this issue happening!<BR/><BR/>-- SachinSachinhttps://www.blogger.com/profile/04551992629821526364noreply@blogger.comtag:blogger.com,1999:blog-2257062243512934022.post-73048659204891175552009-02-09T21:01:00.000+05:302009-02-09T21:01:00.000+05:30Sachin,Is your code expected to be for 8i ?I could...Sachin,<BR/><BR/>Is your code expected to be for 8i ?<BR/>I could not run it as it is on 8i database. The CONNECT BY trick to generate extra rows works from 9i onwards (not sure about 11g though). So I used a little trick that I had learned from Frank Zou. So that worked. However, if I am not mistaken, CARDINALITY hint is available from 9i onwards and not available in 8i.<BR/>Now, assuming this is run on 9i, I found this <A> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:3779680732446#15740265481549 </A>.<BR/>I do not have 9i database to test it but it seems it can explain what you are seeing.Narendrahttps://www.blogger.com/profile/14645699853364658640noreply@blogger.comtag:blogger.com,1999:blog-2257062243512934022.post-63941758213641504522009-02-09T15:02:00.000+05:302009-02-09T15:02:00.000+05:30Hi Nigel,Thanks for your reply! I did what you sai...Hi Nigel,<BR/><BR/>Thanks for your reply! <BR/><BR/>I did what you said - but it didnt seems to work as we thought ..<BR/><BR/>it took more than 20 secs again to run the sql 10000 times as against 1.5 secs with normal "IN" clause"<BR/><BR/>Here is the tkprof results of the NumberType:<BR/><BR/>SELECT COUNT(*)<BR/>FROM<BR/> TEST WHERE R IN ( SELECT /*+ cardinality(10) */ * FROM THE ( SELECT CAST(<BR/><BR/> CONVERT2TABLE( '1,2,3,4,5,6,7,8,9,10' ) AS NUMBERTABLETYPE ) FROM DUAL ) )<BR/><BR/><BR/><BR/><BR/><BR/>call count cpu elapsed disk query current rows<BR/><BR/>------- ------ -------- ---------- ---------- ---------- ---------- ----------<BR/><BR/>Parse 1 0.01 0.01 0 25 0 0<BR/><BR/>Execute 10000 4.80 4.78 0 0 0 0<BR/><BR/>Fetch 10000 13.42 13.09 0 120000 0 10000<BR/><BR/>------- ------ -------- ---------- ---------- ---------- ---------- ----------<BR/><BR/>total 20001 18.24 17.89 0 120025 0 10000Sachinhttps://www.blogger.com/profile/04551992629821526364noreply@blogger.comtag:blogger.com,1999:blog-2257062243512934022.post-72548673172852847382009-02-09T14:19:00.000+05:302009-02-09T14:19:00.000+05:30SachinI notice you used Convert2Table ... AS Strin...Sachin<BR/><BR/>I notice you used Convert2Table ... AS StringTableType. This means all your list values then need to be implicitly converted to number. <BR/><BR/>In that circumstance, a hash join seems like a pretty good plan (much better than nested FTS).<BR/><BR/>Change the function to return a NumberTableType, and see what difference it makes!<BR/><BR/>Regards NigelNigelhttps://www.blogger.com/profile/10826219818302312878noreply@blogger.com