Changing AG Field Type from FILTER to INDEX

[yousuf_gani]

Hi,
Require your help to do following
- We want to Change the Exisitng APPL Group FIELD Type (already loaded volumes of data) from FILTER to INDEX.
What steps we need to Follow without deleting the APPL Group and FOLDER pointing to this APPL Group. Please help with the details

• CMOD version 9.5.0.1
  Our DB is Oracle not DB2
  CMOD Server installed in the Linux Platform

Your guidance and support is  required. Please do the needful
 
Thanks
Mohd Yousuf gani

[Alessandro Perucchi]

From my colleague which is near me, he would say "RTFM".

But I will give one more hint :-) Update your Application Group where you want to change this setting.
Change from Filter to Index.
Press OK.
Read the message. and press ok to do the indexation NOW, or cancel to do it later.

You might do that operation during a moment, that has no much activities in the system.

[yousuf_gani]

Thanks a lot Alessandro

I tried Googling and Reading the IBM Knowledge Center, Could not able to find any Specific Command to Update the FIELD type from FILTER to INDEX unless doing via ADMINCONSOLE.
So Only came to Our userforum

But as always u helped me. I am trying this option from the ADMIN CONSOLE, Hope my old Column data will get INDEXED properly.

Thanks a lot.
Another Question I see in your old Post related to INDEXING(http://www.odusergroup.org/forums/index.php?topic=797.msg2381#msg2381).


"1)Choose index keys that have high selectivity. The selectivity of an index is the percentage of rows in a table having the same value for the inexed key. An index's selectivity is optimal if few rows have the same value.
2) Indexing low selectivity columns can be helpful if the data distribution is skewed so that one or two values occur much less often than other values."

I am bit Confused with the "High-Selectivity" and "Few Rows have Same Value" contradict statement  referenced in the 1st point . Can you just help me to clarify with my example
eg In a 100,000 records Table
DATE field Index    -> DISTINCT DATE       = > 500    [then for each INDEXed records average search records selected ~ 20000 (100000/500)
Transaction ID Index -> DISTINCT TRAN_ID    = > 9000   [then for each INDEXed records average search records selected ~ 12 (100000/9000)]

From the Both the FILED which is HIGH-SLECTIVITY INDEX? and WHICH INDEX field is better then Other, Can you please help (i know its more of DB related, But APPL GROUP defintion require to properly select the FIELD which can be made as INDEX key for faster retrieval).

Require your Expertise help

Thanks
Mohd Yousuf gani

[Alessandro Perucchi]

I'm happy I could help you :-)

You have the admin client to change the field from "FILTER" <-> "INDEX", but you could also use a XML file for that.

Concerning your second question... I've little experience with Indexing in DB. I think the best way would to ask a DBA to help you on that topic, because the INDEX in CMOD and INDEX in a DB is exactly the same concept.
What he will tell you, is exactly what you can implement in CMOD.

That said, what would be also good, is to check what are the queries that your users are doing. And you can log that in the System Log, and the setting is done via the LOG tab in the Application Group.
My advice would be to do it for a small period (maybe few hours) because it takes a lot of resources.
And with the output, you will see what are the fields that the users mainly use, and then with your DBA, you can try to find the optimal index configuration.
I would advise also not to have too much indexes (I mean, not an index for each field). I might help, but you will explode the size of your database!!! :-)
Sometimes it is better to have few but well placed indexes, than to have it everywhere!

The tip to check the how the users are doing there query, will also give you the reaction time. Meaning, that if you add/remove indexes, then you will be able to reactivate the query logs, and check if the answer time is the same, better or worse. And from there try something else, and recheck, etc...

I know I haven't answered you... and I'm sorry.
Maybe some more knowledgeable people in this forum can answer you and your specific question.

[ewirtz]

Hi,

I think the correct way is:
- determine which searches shall be supported in an efficient way
- For these searches you need indexes. Often combined indexes are the best choice.

to avoid inefficient searches you have to define which combination of search fields are allowed. As I have heard this is possible with CMOD 9.5. If you have searches much longer than 1 second (see message 226) you have a candidate for optimizing.

Regards

Egon