?
Solved

need help on oracle indexes

Posted on 2014-09-30
9
Medium Priority
?
340 Views
Last Modified: 2014-10-06
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%')
0
Comment
Question by:sam_2012
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 400 total points
ID: 40351664
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
 

Author Comment

by:sam_2012
ID: 40351816
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
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 400 total points
ID: 40351824
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:sam_2012
ID: 40351845
Iam fine with the answer for the blog .  we are fine with using the % in the begining and not both the ends.
0
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 200 total points
ID: 40351983
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
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 1400 total points
ID: 40352076
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
 

Accepted Solution

by:
sam_2012 earned 0 total points
ID: 40355277
Hi All,

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

Expert Comment

by:sdstuber
ID: 40355292
>>> 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
 

Author Closing Comment

by:sam_2012
ID: 40363218
Would be more happy ,  if I can get the logic for the reverse function.
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question