Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

PL/SQL: Alter index after Update in a procedure

Posted on 2011-03-04
15
Medium Priority
?
628 Views
Last Modified: 2012-05-11
Hi,

I am newbie in the PL/SQL and Toad world, and need your help.
My question: how to alter an index after an update? This Update is executed from a procedure.
0
Comment
Question by:kathysmith
[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
  • 8
  • 6
15 Comments
 

Author Comment

by:kathysmith
ID: 35034898
I am newbie in the PL/SQL and Toad world, and need your help.

SCENARIO:
- I'm using "Toad for Oracle" (but this is maybe less important)
- we have MyTable table with the fields: user_name (Varchar2) and id (Numeric)
- we have created the index IDX_USER_NAME on the field user_name. The command for creating this index was:

CREATE INDEX MYSCHEMA.IDX_USER_NAME ON MYSCHEMA.MYTABLE
(USER_NAME)
INDEXTYPE IS CTXSYS.CONTEXT
NOPARALLEL;

- we have two procedures, one updates MyTable, and the other one gets data:

PROCEDURE Update_MyTable( param_userName IN VARCHAR2, userId IN NUMBER )
IS
BEGIN
    UPDATE MyTable
    SET
        user_name = param_userName
    WHERE
        id = userId;
    COMMIT;
                                     
END Update_MyTable;

PROCEDURE Get_Users( param_userName IN VARCHAR2, SOME_CURSOR OUT T_CURSOR)
IS
BEGIN
    OPEN SOME_CURSOR FOR
   
    SELECT * FROM MyTable WHERE user_name = param_userName
END Get_Users

- each of Update_MyTabel and Get_Users procedures works correctly

PROBLEM: If I first execute Update_MyTable to change a record, and then I execute Get_Users, Get_Users doesn't return the record that has been changed. If I execute an "SELECT * FROM MyTable", then I get the correct results.

WHAT I've tried:
- I've recreated the index, and executed again Get_Users procedure. Now I get the correct result. This means that the index must be recreated after UPDATE is executed, or?
- after COMMIT, in Update_MyTable procedure, I've added the following:
      ALTER INDEX MYSCHEMA.IDX_USER_NAME REBUILD;
- but this line gives the following error in Toad:

ERROR line 1414, col 11, ending_line 1414, ending_col 15, Found 'INDEX', Expecting: ;   -or-   :=   -or-   .   -or-   (   -or-   @   -or-   ROW

QUESTION: how can I solve this?
0
 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 35035630
use

execute immediate 'ALTER INDEX MYSCHEMA.IDX_USER_NAME REBUILD' ;
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35035891
No need to a full reubild.

A CTXSYS.CONTEXT index is a special Oracle Text index.  Unless you specify 'SYNC ON COMMIT', new rows need to be manually synced.  This is done with the CTX_DDL.SYNC_INDEX call.

http://download.oracle.com/docs/cd/E11882_01/text.112/e16593/cddlpkg.htm#CCREF0652

Special note:
Oracle Text indexes become fragmented and inefficient if you have a lot of DML.  You will want to call OPTIMIZE_INDEX from time to time to ensure decent performance.

How often will be driven by your database.  I run OPTIMIZE_INDEX as a scheduled database job so I don't have to worry about it.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:kathysmith
ID: 35036197
@ashilo: thanx for the answer, but it doesn't help. As I wrote in my comment, ALTER INDEX MYSCHEMA.IDX_USER_NAME REBUILD leads to ERROR. I need something that refreshes the index automatically after that COMMIT in Update_MyTable procedure.
0
 

Author Comment

by:kathysmith
ID: 35036266
@slightwv: this seems to be what I need.
MyTable is not so often updated. But the contain of the table is shown in a DataGrid (ASP.NET), and if the user makes a change, then this change must be shown immediately in DataGrid. Therefore I need that the index is refreshed immediately. I don't know how to use SYNC ON COMMIT, but I'll check the link.
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 35036315
The syntax for it goes like:

CREATE INDEX MYSCHEMA.IDX_USER_NAME ON MYSCHEMA.MYTABLE
(USER_NAME)
INDEXTYPE IS CTXSYS.CONTEXT
parameters('sync(on commit)')
NOPARALLEL;



I guess I also need to ask why you are creating a Text index on a single user_name columns.  What types of queries are you using that requires the complexity of a Text index?
0
 

Author Comment

by:kathysmith
ID: 35038298
@ashilo: I apologize: the command you indicate does not lead to error. I forgot the ' characters. Sorry for that. So now I have the following after COMMIT:

EXECUTE IMMEDIATE 'ALTER INDEX MYSCHEMA.IDX_USER_NAME REBUILD' ;

But when I execute the package where the Update_MyTable is, Toad for Oracle stops responding. I "killed" Toad from Windows Task Manager. I started Toad again, and tried again to execute the package. And again Toad  stops responding.

I'd like to remove the line with EXECUTE IMMEDIATE... , and to try the solution from @slightwv. But first I have to get Toad respond.


0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35038325
Why are you still experimenting with the ALTER INDEX syntax?  It's the wrong thing to do here.

A full rebuild of a large text index can take a LONG LONG time.  I've had them take DAYS on slower hardware.

That is why Toad appears to hang.


I would also like you to answer the question above:
I guess I also need to ask why you are creating a Text index on a single user_name columns.  What types of queries are you using that requires the complexity of a Text index?
0
 

Author Comment

by:kathysmith
ID: 35038399
@slightwv: my scenario description is simplified.

MyTable has, among others, two Varchar2 columns, let's call them productDescr and productDetails. I have another procedure that returns data from these two columns, based on parameters.
Our DB admin created two indexes on productDescr and productDetails columns.

I have a package that contains procedures for insert, update, delete and getting data.
I'd like to recreate the indexes as you indicate, by using SYNC(ON COMMIT). This means that I don't need to use EXECUTE IMMEDIATE... in my insert, update and delete procedures.

But, first of all, I have to make Toad responsive....
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35038465
'sync on commit' will cause Oracle to pull apart the data and index it.  The downside is the actual 'commit' will take longer.

So yes, you will not need execute immediate.

As I mentioned before, you will need some process to optimize the index over time but not on every insert/update or delete.

>>let's call them productDescr and productDetails.

That really doesn't answer what I was trying to get.  What types of queries are you executing?  Text indexes require a CONTAINS clause.

Text indexes are highly specialized and are for a specific purpose.  If you do not need them, why put up with all the overhead and maintenance that goes along with them?

0
 

Author Comment

by:kathysmith
ID: 35038508
@slightwv

MyTable has about 45000 rows, and productDescr and productDetails columns don't contain very much text. Data in this table is pretty static, that is it will be seldom changed. When I recreated an index with Toad, it only took about 3 minutes. So this is an very easy scenario.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35038532
Why are you not answering my questions?

What types of queries are you executing?  I'm looking to see if you really need a Text index.
0
 

Author Comment

by:kathysmith
ID: 35038550
@kathysmith: yes, the procedure that returns data from the two columns make use of CONTAINS.
0
 

Author Comment

by:kathysmith
ID: 35038638
@slightwv: the procedure that returns data from the two columns are simple, just SELECTs containing CONTAINS.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35038882
OK.  I really would have liked to get more detail.

For 45,000 rows, I have a feeling you do not need the Text index but as long as you are happy:  Glad to help.

Don't forget to have your DBAs call OPTIMIZE_INDEX once in a while.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

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…
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…
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 shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

704 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