tag:blogger.com,1999:blog-22727713.post117489715602544108..comments2023-09-18T12:29:07.382+03:00Comments on Gints Plivna blog: Gints Plivnahttp://www.blogger.com/profile/17716037729002528529noreply@blogger.comBlogger7125tag:blogger.com,1999:blog-22727713.post-59813421596034643352008-12-27T08:03:00.000+02:002008-12-27T08:03:00.000+02:00Hi,When you do:-select ename from emp where name l...Hi,<BR/><BR/>When you do:-<BR/><BR/>select ename from emp where name like '%NIL';<BR/><BR/>Oracle will do full table scan as it will not know what can all comes under the string "%NIL%. But if you want to index than the only option is to use DOMAIN index.<BR/><BR/>Once you will create domain indexes you can use:-<BR/><BR/>select ename from emp where contains (ename,'%NIL%') >0<BR/><BR/>Regards,<BR/>Sunil BholaAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-22727713.post-71018929258457391432007-11-27T01:18:00.000+02:002007-11-27T01:18:00.000+02:00For those that need much faster indexing technolog...For those that need much faster indexing technology and are willing to do some C/C++ program, there is a much more efficient bitmap index in <A HREF="http://sdm.lbl.gov/fastbit/" REL="nofollow">FastBit</A>. For example, in <A HREF="http://www.cs.umn.edu/~kewu/abs/48975.html" REL="nofollow">a 2001 report</A>, it was shown that the compressed bitmap index used in FastBit perform on the average 12 times faster than the BBC compressed bitmap index (which is what is used in ORACLE).Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-22727713.post-77603227038330523762007-06-11T13:45:00.000+03:002007-06-11T13:45:00.000+03:00Yea with multi-byte chars unfortunately there are ...Yea with multi-byte chars unfortunately there are problems for both approaches, for example (tēja is latvian for tee):<BR/>SQL> select<BR/> 2 utl_raw.cast_to_varchar2(<BR/> 3 utl_raw.reverse(<BR/> 4 utl_raw.cast_to_raw('tēja')))<BR/> 5 from dual<BR/> 6 /<BR/><BR/>UTL_RAW.CAST_TO_VARCHAR2(UTL_RAW.REVERSE(UTL_RAW.CAST_TO_RAW('TĒJA')))<BR/>-----------------------------------------------------------------------<BR/>aj??<BR/><BR/>SQL> select reverse('tēja') from dual;<BR/><BR/>REVER<BR/>-----<BR/>aj??<BR/><BR/>SQL>Gints Plivnahttps://www.blogger.com/profile/17716037729002528529noreply@blogger.comtag:blogger.com,1999:blog-22727713.post-66161071102128985672007-06-11T12:46:00.000+03:002007-06-11T12:46:00.000+03:00I also regret that reverse key index do not suppor...I also regret that reverse key index do not support range... it would make sense!<BR/><BR/><I><BR/>create table t as select object_name from all_objects;<BR/>create index i on t(object_name) reverse compute statistics;<BR/><BR/>select * from t where object_name like '%EMP';<BR/><BR/>Execution Plan<BR/>---------------------------<BR/>| Id | Operation |<BR/>---------------------------<BR/>| 0 | SELECT STATEMENT |<BR/>|* 1 | TABLE ACCESS FULL|<BR/>---------------------------<BR/></I>Laurent Schneiderhttps://www.blogger.com/profile/13833048234373264017noreply@blogger.comtag:blogger.com,1999:blog-22727713.post-9102974112941856442007-06-11T12:31:00.000+03:002007-06-11T12:31:00.000+03:00which is not exactly the same if you have multibyt...which is not exactly the same if you have multibytes charactersLaurent Schneiderhttps://www.blogger.com/profile/13833048234373264017noreply@blogger.comtag:blogger.com,1999:blog-22727713.post-43090169248494989582007-06-11T12:29:00.000+03:002007-06-11T12:29:00.000+03:00note RESERVE(str) is not a supported function. You...note RESERVE(str) is not a supported function. You should prefer<BR/><BR/><I><BR/>utl_raw.cast_to_varchar2(<BR/>utl_raw.reverse(<BR/>utl_raw.cast_to_raw(str)))<BR/></I>Laurent Schneiderhttps://www.blogger.com/profile/13833048234373264017noreply@blogger.comtag:blogger.com,1999:blog-22727713.post-24369134524109893352007-06-09T20:51:00.000+03:002007-06-09T20:51:00.000+03:00This comment has been removed by the author.Laurent Schneiderhttps://www.blogger.com/profile/13833048234373264017noreply@blogger.com