Solved

need help on oracle indexes

Posted on 2014-09-30
9
333 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 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
PL/SQL Two changes 7 42
Creating a are-you-sure condition prior to shutdown: what do you think of... 14 43
Excess Redo 3 32
how to tune the query 17 61
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
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…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

730 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