Dynamic creation of indexes

[ramyapriyacsit]

Hi

Could you tell me is it possible to create indices dynamically ?
if yes please tell me how can we create.

Thank you.

 

[TomBascom]

If you are referring to database tables then the answer is "no".

If you are referring to temp-tables then the answer is "maybe". If you are dynamically creating a TT then you could, of course, dynamically create indexes to go with it.

You might also, perhaps, be thinking of adding an index to the db online -- which is possible in recent releases but slightly different from doing so dynamically...

 

[palthe]

If you mean dynamically creating indexes on a dynamic temp-table which is based on a db table, you can of course run a query on the _index table of the db.

With the procedure below you can extract the primary unique index of the table in the db. Adding the index (in the cprimary) on the temp-table should do the trick.

DEFINE VARIABLE ctable AS CHARACTER NO-UNDO.
DEFINE VARIABLE hbuffer AS HANDLE NO-UNDO.
DEFINE VARIABLE hqueryindex AS HANDLE NO-UNDO.
DEFINE VARIABLE hbuffile AS HANDLE NO-UNDO.
DEFINE VARIABLE hbufindex AS HANDLE NO-UNDO.
DEFINE VARIABLE icount AS INTEGER NO-UNDO.
DEFINE VARIABLE cindex AS CHARACTER NO-UNDO.
DEFINE VARIABLE cprimary AS CHARACTER NO-UNDO.
DEFINE VARIABLE icount2 AS INTEGER NO-UNDO.
ctable = "<tablename>".
CREATE BUFFER hbuffer FOR TABLE ctable.
CREATE QUERY hqueryindex.
CREATE BUFFER hbuffile FOR TABLE "_file".
CREATE BUFFER hbufindex FOR TABLE "_index".
hqueryindex:SET-BUFFERS(hbuffile,hbufindex).
hqueryindex:QUERY-PREPARE(SUBSTITUTE("for each _file where _file-name = &1, each _index of _file",QUOTER(ctable))).
hqueryindex:QUERY-OPEN.
hqueryindex:GET-FIRST(NO-LOCK).
DO WHILE NOT hqueryindex:QUERY-OFF-END: 
    icount = icount + 1.
    IF cindex = "" THEN cindex = hbuffer:INDEX-INFORMATION(icount).
    ELSE cindex = cindex + "|" + hbuffer:INDEX-INFORMATION(icount). 
    hqueryindex:GET-NEXT(NO-LOCK).
END.
hqueryindex:QUERY-CLOSE.
DELETE OBJECT hqueryindex.
DO icount = 1 TO NUM-ENTRIES(cindex,"|"):
    IF ENTRY(2,ENTRY(icount,cindex,"|")) = "1" AND ENTRY(3,ENTRY(icount,cindex,"|")) = "1" THEN 
    DO icount2 = 1 TO NUM-ENTRIES(ENTRY(icount,cindex,"|"),","):
        IF icount2 > 4 AND LOOKUP(ENTRY(icount2,ENTRY(icount,cindex,"|"),","),"0,1") = 0 THEN
        DO: 
            IF cprimary = "" THEN cprimary = ENTRY(icount2,ENTRY(icount,cindex,"|"),",").
            ELSE cprimary = cprimary + "," + ENTRY(icount2,ENTRY(icount,cindex,"|"),","). 
        END.
    END.
END.

Edit:
by the way, the cprimary is an ","-delimited variable with the fieldnames of the primary unique index of the <tablename>. I created this procedure to create a proposal on cascade delete actions.
Or an interface for advanced querying (eg: users with no comprehension at all of querying can "click" an inner join query together this way ).

But for the use of dynamic indexing it's also very useful, I guess.