Solved

need help on oracle indexes

Posted on 2014-09-30
9
335 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 49

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 49

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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

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 77

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 74

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 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

717 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