This discussion is archived
2 Replies Latest reply: Nov 12, 2013 9:57 AM by RonSexton RSS

Unused Indexes in PS VCHR ACCTG LINE...remove them?

RonSexton Newbie
Currently Being Moderated

There are a number of SQL Server table indexes delivered with PS_VCHR_ACCTG_LINE table that appear to have no seeks, scans or lookups and a couple of indexes that have only a few like 10. These indexes take up a lot of space. In total they are half of the disk space used for this table. Seems like it would be good to remove these and re-use this space. Maybe with some other better indexes even.
Specifically:

No seeks, scans, lookups:

PSCVCHR_ACCTG_LINE
PSEVCHR_ACCTG_LINE
PSGVCHR_ACCTG_LINE
PSJVCHR_ACCTG_LINE

Only a few:
PSHVCHR_ACCTG_LINE
PSKVCHR_ACCTG_LINE

Anyone else looked at or done this? Why are they here? Could it be they were supposed to be removed when others were added?

PeopleSoft Financials 9.1 , Tools 8.52 .

Ron

  • 1. Re: Unused Indexes in PS VCHR ACCTG LINE...remove them?
    BobLilly Expert
    Currently Being Moderated

    You could remove them, but it would be considered a customization. Some of the indexes have comments associated with them that should give you some idea what they're for. Depending on the functionality your users currently use, you can always expect that you will have unused indexes on many tables. Disk is cheap, though, so customizing to save space probably isn't worth it.

     

    You can view the comments in app designer, or use SQL--something like

     

    select indexid, idxcomments from psindexdefn where recname='VCHR_ACCTG_LINE' order by indexid;

     

    If you do want to remove an index, you should set the Platform flag to none (Not Active) in app designer (Tools->Data Administration->Indexes then highlight the index you want to remove and click on Edit Index). If you then use Build->Current Definition and select Create Indexes (make sure the Build Settings say Create Indexes only if modified), I think it will generate the DROP INDEX statements for you.

     

    If you just drop the index in Oracle you will get DDDAUDIT errors. If you completely remove the index definition in app designer, it probably won't be identified as a customization in an upgrade and the indexes will reappear if you aren't careful.

     

    Regards,

    Bob

  • 2. Re: Unused Indexes in PS VCHR ACCTG LINE...remove them?
    RonSexton Newbie
    Currently Being Moderated

    Thanks for the information. These are taking up a lot of space and can affect the cache and memory usage on SQL Server performance wise. Your valuable insight has given me good food for thought on how I might or might not handle this.

     

    Thanks again.

     

    Ron

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points