need help on oracle indexes

Hi ,

I came across an blog , which says the below lines

One obscure trick for indexing queries with a leading wildcard character (like '%SON') is to create a REVERSE index and them programmatically reverse the SQL like clause to read "like 'NOS%'", effectively indexing on the other side of the text, clumsy, yet effective.  

I have an table , comments  . I need to perform %<>% operation on  two columns title and comments . Here title is varchar2(4000) and comments is clob.

Can any one help how i can implement the above solution , to speed up performance. I have been asked not to use oracle text indexes.

Comments :
comment_id number
title varchar2(4000)
comments clob
organization1_id number
organization2_id number
organization3_id number.

Select *
from comments
where (
title like '%sam%'
or subject like '%sam%')
sam_2012Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
that trick will not help you with

title like '%sam%'
or subject like '%sam%'

to start:
title like 'sam%' or title like '%sam' would NOT find this: 'this is my article about sam the pieman'


---------------------- indexes on large strings ---------- finding by wildcards --------------------------------
 ---- seems I've been here before ---------------------------------------------------------- funny feeling -------------- Mmmmm

I'm getting that deja-vu sensation ... oh yes ... "Oracle Text indexes"

http://www.experts-exchange.com/Database/Oracle/Q_28519899.html
0
sam_2012Author Commented:
hi portpaul,

thats for my previous question . But we have decided not to use oracle text indexes. Hence , we need an alternative approach to do it . When going thru , i found that blog which stated it. Any help is really appreciated.
0
PortletPaulfreelancerCommented:
my iunderstanding is that blog idea really will not help you

it is only useful (if at all) for queries that would look at the end of a string, that is not what you are asking for.

It will not assist when using double ended wildcards through big text fields. (that's what text indexes are for)

If you have rules out Oracle text indexes; then you should also rule out double ended wildcard searches over the clob and the very long title.

Sorry if this is not what you want to hear, but it is my honest opinion.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

sam_2012Author Commented:
Iam fine with the answer for the blog .  we are fine with using the % in the begining and not both the ends.
0
slightwv (䄆 Netminder) Commented:
You need the right tool for the job.  You 'can' drive a nail with a screwdriver but a hammer makes the job much easier.

You might have been asked to not use a Text index but that is the right tool for double ended wildcards when a full table scan won't meet your requirements.
0
sdstuberCommented:
slightwv and portletpaul are correct - you seem to be going out of your way to NOT achieve what you really want.

To illustrate just how cumbersome what you've asked is...
This is how you would implement indexes on reversed columns.
It's not too bad for a varchar2, but for a clob it's kind of awful.


CREATE TABLE comments
(
    comment_id       NUMBER,
    title            VARCHAR2(4000),
    comments         CLOB,
    organization1_id NUMBER,
    organization2_id NUMBER,
    organization3_id NUMBER
);

CREATE INDEX ind_rev_title
    ON comments(reverse(title));

CREATE INDEX ind_rev_comments
    ON comments(
        reverse(DBMS_LOB.SUBSTR(comments, 4000, GREATEST(DBMS_LOB.getlength(comments) - 4000, 1)))
    );

INSERT INTO comments
     VALUES (
                1,
                'test-title-sam',
                'test-comments-sam',
                1,
                2,
                3
            );

SELECT *
  FROM comments
 WHERE reverse(title) LIKE reverse('%sam');

SELECT *
  FROM comments
 WHERE reverse(DBMS_LOB.SUBSTR(comments, 4000, GREATEST(DBMS_LOB.getlength(comments) - 4000, 1))) LIKE
           reverse('%sam');

Open in new window


And do note you can only search the last 4000 characters of your comments.
Which is fine if you're really only interested in end-searches.
My guess is that's not really true.  It seems more like it's a desperate attempt to get "something" to work, even if it's not really a viable solution to the entire problem.

And one last note.  The REVERSE function isn't documented.  So, if you really want to pursue this option and be safe doing it, you should write your own.
0
sam_2012Author Commented:
Hi All,

Thanks for your inputs. I will persuade my team to implement oracle text indexes.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sdstuberCommented:
>>> Would be more happy ,  if I can get the logic for the reverse function.

FUNCTION reverse(p_string IN VARCHAR2)
        RETURN VARCHAR2
    IS        
        v_result VARCHAR2(32767);
    BEGIN
        FOR i IN 1 .. LENGTH(p_string)
        LOOP
            v_result := SUBSTR(p_string, i, 1) || v_result;
        END LOOP;

        RETURN v_result;
    END reverse;
0
sam_2012Author Commented:
Would be more happy ,  if I can get the logic for the reverse function.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.