I want to do composite index to some table of oracle data base to make the application more faster. Please suggest apart from INV_INVESTMENT table what is the other table we need to do ?

[PurnaHCL]

I want to do composite index to some table  of oracle data base to make the application more faster. Please suggest apart from INV_INVESTMENT table what is the other table we need to do ?

[Jerin Sebastian]

Hi, Are you trying to enhance the performance of portfolio management items? The best way would be to do a SQL trace of the page you are trying to enhance the performance and that will give you the details of all the underlying tables and the execution time it took. Thanks, Jerin

[PurnaHCL]

Hi Jerin,

 

Thanks for your quick response.

We did the index for all table of Clarity in last month. But because of some issue our DBA team is asking to do composite indexing to INV_Investment table.

Please advise some more table like CMN_SEC_USERS, we can go for composite index and able to get response faster from db.

 

Thanks,

Purna Chandra Patra

[Suman Pramanik]

Hi Purna,

 

I would want to understand on what analysis you came to conclusion of building composite index , Did you run the oracle enterprise manager and it suggested that. Please remember you can create index but you need to disable them during upgrades

 

Regards

Suman Pramanik

[PurnaHCL]

Dear Suman,

 

Thanks for your information and suggestion.

 

DBA people did the analysis and based one the query analysis report the cost (% CPU) is coming down from 1095 to 880 .

We already tested the same thing in dev environment and wants to do some more crucial table randomly  so that it will be make some faster response.

Thanks,

Purna Chandra Patra

[Suman Pramanik]

There are multiple ways to reduce the cost you can try doing the de fragmentation and see if it works or adding index also doesn't harm provided you take it off during upgrade.

[Nick Darlington]

Adding indexes would be unsupported, there are impacts that go beyond just upgrades to consider such as causing Clarity transactions to fail and resulting in corrupted (incomplete) data.  If those failures occur whilst making a change to an object that uses the same table in Studio for example, it can cause a complete outage that might be unrecoverable.

 

If there is evidence that the performance of a table/query must be enhanced by the addition of an index, this should be pursued through a Support issue where (if true) it could ultimately be logged as a defect and fixed in a future version (with or without the proposed index, as fixes may take other paths to a solution).

 

Officially, those are the responses we can give.

 

Considering alternatives to performance improvements like defragmentation, ensuring stats are uptodate, those various things your DBA should be (and quite possibly is) already performing, would be the kinds of actions that would be acceptable.  Adding or changing indexes though would be treated the same as trying to change our application's code.