Solved

PL/SQL: Alter index after Update in a procedure

Posted on 2011-03-04
15
611 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
  • 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 76

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
 

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 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 76

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 76

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 76

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 76

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

758 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

16 Experts available now in Live!

Get 1:1 Help Now