Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 632
  • Last Modified:

PL/SQL: Alter index after Update in a procedure

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
kathysmith
Asked:
kathysmith
  • 8
  • 6
1 Solution
 
kathysmithAuthor Commented:
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
 
Aaron ShiloCommented:
use

execute immediate 'ALTER INDEX MYSCHEMA.IDX_USER_NAME REBUILD' ;
0
 
slightwv (䄆 Netminder) Commented:
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
kathysmithAuthor Commented:
@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
 
kathysmithAuthor Commented:
@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
 
slightwv (䄆 Netminder) Commented:
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
 
kathysmithAuthor Commented:
@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
 
slightwv (䄆 Netminder) Commented:
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
 
kathysmithAuthor Commented:
@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
 
slightwv (䄆 Netminder) Commented:
'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
 
kathysmithAuthor Commented:
@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
 
slightwv (䄆 Netminder) Commented:
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
 
kathysmithAuthor Commented:
@kathysmith: yes, the procedure that returns data from the two columns make use of CONTAINS.
0
 
kathysmithAuthor Commented:
@slightwv: the procedure that returns data from the two columns are simple, just SELECTs containing CONTAINS.
0
 
slightwv (䄆 Netminder) Commented:
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 8
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now