Solved

need help on oracle indexes

Posted on 2014-09-30
9
326 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 48

Assisted Solution

by:PortletPaul
PortletPaul earned 100 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 48

Assisted Solution

by:PortletPaul
PortletPaul earned 100 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
 

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 50 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 73

Assisted Solution

by:sdstuber
sdstuber earned 350 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 73

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

706 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now