Query in Indexes...
Jojo Morales asked for a safe way to disable and re-enable indexes from 4GL. Arthur suggested optimizer directives such as Avoid_index (with PREPAREd statements in 4GL) and Watawinona agreed for 7.3. Rudy Fernandes said disabling is easy but enabling needs an exclusive lock, so do both in one transaction after LOCK TABLE ... EXCLUSIVE with SET LOCK MODE TO WAIT.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hi Gurus, I have a query, is there a safe and tested approach where can i disable the indexes and enable it again. Im planning to put the approach in the 4GL's. Regards, Jojo
Hi Jojo From version 7.?0 ids/4gl you can use "Optimizer Directives" for this. See SQL Guide Page "Segments 4 - 141" Or 4367.pdf page 884" Select {+ Avoid_full} tblnm.fldnm From tblnm where bla bla ... or "" {+ Avoid_index} "" In 4GL you will have to Prepare the statement from a cmd string otherwise the compiler will filter the {comment} out your SQL command. Switch on explain and you will see the results. bibi Arthur_apw In article <8asdba$822$1@news.xmission.com>, Jojo Morales <jmorales@nettaxi.com> wrote: > > Hi Gurus, > > I have a query, is there a safe and tested approach where can i > disable the indexes and enable it again. Im planning to put the approach > > in the 4GL's. > > Regards, > > Jojo > > Sent via Deja.com http://www.deja.com/ Before you buy.
>Subject: Query in Indexes... >From: Jojo Morales jmorales@nettaxi.com >Date: 17.03.00 05:32 W. Europe Standard Time >Message-id: <8asdba$822$1@news.xmission.com> > > >Hi Gurus, > > I have a query, is there a safe and tested approach where can i >disable the indexes and enable it again. Im planning to put the approach > >in the 4GL's. > > >Regards, > >Jojo > > Try adding optimizer directives to the query - provided you have a 7.3 engine. Nona
There usually isn't a major problem disabling indexes. Enabling them can be
difficult if queries against the table are left unhindered, because they
now will run much slower than normal preventing the enable sql from getting
the exclusive lock on that table that it needs.
The obvious solution is to carry out the disabling and enabling in a
transaction after locking the table in exclusive mode. Something like this
:
whenever error stop;
set lock mode to wait 30;begin work;
lock table <whatever> in exclusive mode;
set constraints, indexes for <whatever> disabled;
set constraints, indexes for <whatever> enabled;
commit work;
Think about your lock mode setting (wait 30 or wait 10 or wait infinite) a
bit, in the context of your environment. Also consider the time taken for
the operation by your large tables. Maybe, parallelizing the operation -
multiple tables at a time - can help : doing related tables together (after
all, if one table is locked out, the other related ones may be useless to
client queries).
Rudy
Jojo Morales wrote:
> Hi Gurus,
>
> I have a query, is there a safe and tested approach where can i
> disable the indexes and enable it again. Im planning to put the approach
>
> in the 4GL's.
>
> Regards,
>
> Jojo