Home » SQL & PL/SQL » SQL & PL/SQL » Function based index creation.
Function based index creation. [message #290101] Thu, 27 December 2007 07:07 Go to next message
psychotron108
Messages: 2
Registered: December 2007
Junior Member
hi,

I am try to optimize execution of a dynamic query generated by a java code.
In that Instr func is being used on a field called catalog_product_desc as:
instr(translate(catalog_product_desc,'|.#%&,.?-',' '),'piece1[j]',1,1)

piece1[j] is a variable here and provide by the javacode.

Is it possible to create a function based index on it??

If not can somebody suggest any other optimization technique......
Re: Function based index creation. [message #290111 is a reply to message #290101] Thu, 27 December 2007 08:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

Is it possible to create a function based index on it?

No.

Regards
Michel
Re: Function based index creation. [message #290146 is a reply to message #290111] Thu, 27 December 2007 13:08 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
You should the check whole query where problem is may be the section you posted its not causing the problem.
You should post the query with execution plan ,tkprof, check hte statistics all things related to the performance and as well indexes on columns.
Re: Function based index creation. [message #290147 is a reply to message #290101] Thu, 27 December 2007 13:10 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
However, if I interpret your need correctly, you might wish to take a look at Oracle TEXT features. For example, a simple text index might be just what you need (maybe). Here is an example:

create table test1
(
 test1_id number not null
,test1_string varchar2(4000)
)
/

alter table test1
   add constraint pk_test1 primary key (test1_id)
/

create index test1_string_index on test1(test1_string) indextype is ctxsys.CTXCAT
/

insert into test1 values (1,'abc def ghi')
/
insert into test1 values (2,'abcdefghi')
/

commit
/


This creates a simple CTXCAT type index on a simple text field. Please notice that I have assumed the text string consists of a set of "words" seperated by "white space" which in this case is spaces. This seems to fit with your "translate" example.

Now we can execute a query like this one:

select *
from test1
where CATSEARCH(test1_string,'def',null) > 0
/


from which we get these results:

SQL> select *
  2  from test1
  3  where CATSEARCH(test1_string,'def',null) > 0
  4  /

  TEST1_ID TEST1_STRING
---------- -------------------------------------------------
         1 abc def ghi

SQL> 


As you can see, we returned one of our two rows. We found the word def inside our text column, but the emphasis is on "word". This is not a simple "instr" operation. This looks like what you want, yes/no? If you are infact searching for words, then your example has a bug in it does it not? If you are looking for a true instr search, then this will not help you.

Here is a place to start some additional reading. You should do lots of reading as you have plenty of options with Oracle TEXT features. However, pay attention to the limitations of each type of text index you can create, as they are not all EQUAL in what they can do and how they work.

http://www.peacetech.com/flipper/oracle9i/901_doc/text.901/a90121/csql3.htm#CCREF0103

In this example, I specifically chose the CTXCAT index type because this is transactional in nature and thus this type of index is maintained along with any insert/updates/deletes that you do to the underlying data. A CONTEXT type index which is another kind of Oracle TEXT index, is not automatically maintained and thus requires "synchronizing" and means that changes to data are not always immediately seen. This type of index if used to index documents like WORD, EXCEL, HTML, and XML even.

Do some reading before you jump into Oracle TEXT.

Good luck, Kevin
icon14.gif  Re: Function based index creation. [message #291504 is a reply to message #290147] Fri, 04 January 2008 08:17 Go to previous messageGo to next message
psychotron108
Messages: 2
Registered: December 2007
Junior Member
Thanks a TON Kevin!!! The ctxsys index really worked. The Search has become 20 times faster.

The SQL
select * from product where catsearch(catalog_product_desc,'ASDFG',null)
> 0
took 0.109 seconds. It used the CTXCAT index
While
select * from c_product where instr(catalog_product_desc like,'ASDFG',1,1) <> 0
took 19.968 seconds

The Best part is, catsearch is so flexible with usage operations like AND and OR for strings....

All i needed was to install this package 'Oracle text' which was not thr earlier.
But then it does take up a lot of space.....
Anyway doesnt really matter though with kind performance improvement it give....

Re: Function based index creation. [message #291522 is a reply to message #291504] Fri, 04 January 2008 12:45 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
how about 200 times faster?
Re: Function based index creation. [message #291526 is a reply to message #290101] Fri, 04 January 2008 13:47 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
hehehe, bet you check spelling during powerpoint presentations too eh Frank? 200x faster woohoo!

Congratulations PSYCHO. I am pleased that you were able to do the research needed to get this to work for you. It is nice to just give someone a direction and see them make it work. The fact that you had to install Oracle Text before you could use it tells us you had to at least do some reading to make it happen. Your comment "it does take up a lot of space" brings up a very important point we can now both emphasise to others reading this thread, which is...

That whenever we use an advanced feature of Oracle we have to always keep in mind that each advanced feature was created by Oracle with a particular purpose in mind. One way to interpret this is to say that many advanced features are NICHE features. They fill a special void. In the end this means advanced features often come with unspoken LIMITATIONS, RESTRICTIONS, or REQUIREMENTS that we must accept and work around if we want to use the feature. Here are some general expressions of what this means:

This feature does not work with that feature.
I need to have extra disk/memory/network bandwidth to use this feature well.
I restrict database alterations when using this feature.
There are inherent limits I can't go beyond due to bugs or "stupidity" of the feature.


Examples of these are:

Merge statement does not work on views with instead of triggers.
Context Indexes need extra space, and some need manual synchs.
Oracle Total Recall won't let me drop a column unless I drop all the history for the table.
Oracle Workspace Manager has a limit on the number of foreign keys it can deal with.


It is a great idea to explore advanced features. It makes little sense to write your own code to duplcate a feature that the Oracle database already provides. However, we must research and experiment with any advanced feature or Oracle before we accept it as a good use of product, particularly if we are planning its use in a production environment.

To butcher Dirty Harry, "a man has got to know his feature's limitations".

good job Psycho, see you around, Kevin
Re: Function based index creation. [message #291533 is a reply to message #290111] Fri, 04 January 2008 14:09 Go to previous messageGo to next message
Clauddvon
Messages: 23
Registered: July 2006
Junior Member
Michel Cadot wrote on Thu, 27 December 2007 09:37

Quote:

Is it possible to create a function based index on it?

No.

Regards
Michel



I read the boards frequently as I still consider myself a 'Newbie'...the information contained within these forums has helped me grow in my development knowledge tremendeously.

Having said that, I take issue with a 'Senior Member' of a board being so rude and not at all helpful. The information that Kevin provided helped the person in need and I'm sure you had the knowledge, Michel, you just chose to focus on the first part of the question that psychotron108 asked, and ignore the second sentence where he asked, "If not can somebody suggest any other optimization technique......"

Over 10k posts and many of them are just non-sense posts. Senior Members 'should' be there to assist...not to just disregard people's requests/questions. I sometimes get the feeling that you're more concerned with your 'Post count' than you are with actually helping people. I realize this is the 'Newbie' board...and this may bore you, so how about just not making a post at all as opposed to posting an unhelpful (but ONE MORE POST TO THE POST TICKER!!!) response such as "No." I know I'm not over 10k posts...and part of the reason is that I don't know the answers. If I did, you bet I would be providing HELPFUL information to the other Newbies out there who really need help and are doing everything they can to test their issues themselves. (He was even within policy...which you love posting to people...regarding the structuring of his post!!)

There are other boards and if I get banned from here, oh well...there are others I can go too where moderator's are actually respectful and not seated on some imaginary throne somewhere.

Sorry to be rude and disrespectful, but I see so many posts from you like this, I just felt I had to say something.

Bye everyone...I'm sure I'll be banned now ( hope not).

I do actually get alot out of these forums, I wish everyone (especially moderator's) provided 'useful' information.

Take care and happy new year.

[Updated on: Fri, 04 January 2008 14:31]

Report message to a moderator

Re: Function based index creation. [message #291539 is a reply to message #291533] Fri, 04 January 2008 14:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

Senior Members 'should' be there to assist...not to just disregard people's requests/questions.

I post what I want to post, I spend several hours per day to post and help, I do it for free, I owe any answer to anyone and I don't care of your feelings.

Regards
Michel

[Updated on: Fri, 04 January 2008 14:48]

Report message to a moderator

Re: Function based index creation. [message #291541 is a reply to message #291539] Fri, 04 January 2008 14:52 Go to previous messageGo to next message
Clauddvon
Messages: 23
Registered: July 2006
Junior Member
Okay, atleast now I understand. I actually misunderstood your intent for being on these boards, but I'm sorry I presumed to know. You're here for the Biggest Poster of the Year Award! Congrats!

My feelings aren't the question, btw...they're not 'hurt'...I just think if you have nothing 'good and useful' to say, you should just move on to the next message without posting at all. I know...who cares what I think! You're right.

Regards,
Re: Function based index creation. [message #291545 is a reply to message #291541] Fri, 04 January 2008 15:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Of course you didn't read all my posts but now you know how I post.
My first concern is to make people understand how they may work for the best and the first point is how to clearly explain (to themselves before to us) their problem.
Learning how to express a problem is the most important thing BEFORE learning how to solve it.
Then I don't explain, I prefer put people in the mind to solve themselves. Then they know they don't need me nor anyone else and can do it by themselves.
It is a hard and thankless way to teach. Many don't understand, many say this guy is just a asshole but who want to follow can take much more profit than just reading a solution (which much easier to post) even if it is immediately useful.
You know the proverb "give a man a fish and he will eat one day, learn him how to fish and he will eat all his life" and I add "learn him to learn by himself and he will do everything".

Regards
Michel
Re: Function based index creation. [message #291546 is a reply to message #290101] Fri, 04 January 2008 15:35 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Michel and I sometimes approach questions for newbies differently. I am not defending him, he don't need me for that cause he can do a fine job himself. He is more critical that i to the issue of offering up advanced techniques to newbies. He is sensitive to and pointed out to me on one occassion the fact that newbies don't often do the research they need to do before jumping into a "big thing" no matter how many times you say "do the reasearch"..."do the reasearch"..."do the reasearch" or how many CAPS YOU USE TO SAY IT; and the result is often disaster because of it.

I respect this position as it is a valid one. I just think differently, and I am happy to given newbies as much rope as they want to hang themselves. When I do so, I try to provide links to start their research efforts, and give enough direction from my experience so that they avoid obvious pitfalls, but I believe at least a little in trial by fire in IM.

Lastly, I am not a moderator, so I don't have the heavier responsbilities the Michel has, which leaves me open to post less threads and give more detail in some cases.

I would be bad if Michel stopped being a moderator and stopped posting. Hell, I have laughed my butt off several times at his remarks and would miss that (and those or Ross and Anacedent and ...).

PeaceKeeper Kevin
Re: Function based index creation. [message #291548 is a reply to message #291545] Fri, 04 January 2008 15:44 Go to previous messageGo to next message
Clauddvon
Messages: 23
Registered: July 2006
Junior Member
Michel,

I have read MANY good posts from you and I know your style - I am also aware that you are here to help...no one in their right mine (I hope) would hang out in here for hours for any other reason than to help others. I have gained valuable information from many posts that you have made in the past and I appreciate all the help you've given over the years

What I'm referring too, using your illustration...

"give a man a fish and he will eat one day, learn him how to fish and he will eat all his life" and I add "learn him to learn by himself and he will do everything."

...is that, in your response of 'No'...you neither provided a man with a fish, nor did you teach him to fish for himself. That response provided nothing useful...neither 'food' now, nor 'food' later (mentally speaking).

Thanks for all the hard work that you and others put forth to help us noobs...we really do benefit from it, even if sometimes you feel like we're unappreciative.

Regards,
Re: Function based index creation. [message #291552 is a reply to message #291548] Fri, 04 January 2008 16:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

...is that, in your response of 'No'...you neither provided a man with a fish, nor did you teach him to fish for himself. That response provided nothing useful...neither 'food' now, nor 'food' later (mentally speaking).

Of course there is a message, it is there to induce a shock, to put OP in the mind to react and say "BUT" and start to open his mind.
Maybe it is not understood, maybe nothing trigger (as for you but this is not your question), too bad but there are other answerers like Kevin.
http://en.wikipedia.org/wiki/Koan

Regards
Michel
Re: Function based index creation. [message #291553 is a reply to message #290101] Fri, 04 January 2008 16:19 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
ooh, now that's deep

grass-hopper sees your wisdom Master
Previous Topic: ref cursors / without dynamic sql
Next Topic: Trim the spaces between the words in oracle
Goto Forum:
  


Current Time: Sat Dec 03 08:14:30 CST 2016

Total time taken to generate the page: 0.14558 seconds