cancel
Showing results for 
Search instead for 
Did you mean: 

Secondary index missing for ODS ??

Former Member
0 Kudos

Hi All

I have a scenario where the secondary indices for ODS object are missing from DB02 on PROD box (oracle DB)

I know that we have a note SAP NOTE: 157918 and the reports

SAP_INFOCUBE_INDEXES_REPAI

SAP_UPDATE_DBDIFF

but the above are related to infocubes?

Since I am in a PROD box not sure to run these reports?

What would be the best practise

Can I use SE14 - to adjust the indexes again I am on PROD system

Thanks & Regards

Naveen

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

You don't say whether the indices were originally created thru the Admin Workbench on Dev and transported or just created in Prod, or perhaps even at just the DB level.

I think I would agree with Edan - and create them in Dev and transport as the preferred approach.

Former Member
0 Kudos

Hi Pizzaman and Edan

The secondary indices are created directly in PROD on the active table of ODS

and the index is not avilable in the database

Can i directly correct in SE14?

Any implications

Regards

Naveen

Former Member
0 Kudos

As AHP and others mention,you should be fine addressing the problem with SE14.

Did you recently make any changes / reload the ODS that could have dropped the index?

One advantage of creating the ODS index thru the BW Admin workbench and transporting is that it keeps your systems consistent, documenting the creation of the index. Really depends perhaps in your environment - if you have access to create directly in Prod or not. Who knows, maybe that level of access is repsonsible for the indices going missing inthe first place.......

Former Member
0 Kudos

Thanks for the reply

yeah the data is loaded every day around 150,000 rec and we have two indices on the ODS . one is working fine.

How can i create from BW Admin workbench. Do you mean creating in the indexes tab of ODS defination?

Thanks

Naveen

Former Member
0 Kudos

Yes - and then just transport the ODS. When it activates, on the target system, an index creation job will start.

If you are adding an index to a very large ODS, you might want to arrange to have the transport sent off hours to control when the create index job runs.

Another option is that the index could be created first at the DB level, using parallel options etc. The system is smart enough then to recognize that the index already exists when the ODS activation occurs and it simply updates the SAP DDIC, but doesn't need to create the index then.

Former Member
0 Kudos

Hi Pizzaman

Thanks a lot for the info

it almost solves my problem

One more issue the ODS on PROD contains more than 2 billion records.

So any idea on how much time does index activation job runs?

and is it necessary not to load the daily loads, when the ODS object is transported to PROD box?

Do I need to delete the indexes that are already existing on PROD if I transport the indices that are created using Admin workbench?

Best Regards

Naveen

Former Member
0 Kudos

Activation of an index on a 2 billion row ODS....

3hrs, 47 min, 12 secs

Just kidding. Really a funciton of a couple of things:

- Your DB server

- Complexity of the index - how many columns, how unqiue the values - all affect the density of the index.

- What else might be running

We have a 6 CPU box and couple of days ago built a two column index on 150 million rows and it ran 20 minutes, but as I said - there are a lot of variables.

While the Oracle DB could handle loading data and building an index concurrently, it would slow things down. Not sure if BW would/wouldn't place a lock on any of the tables - so I would pass on that.

You do NOT have to drop the other indices - the activation will figure out they already exist, so they'll be OK.

I don't know how many CPUs your server has or if the ODS is partitioned, but I would definitely look at creating the index at the Oracle level using parallel processing, then ODS activation wil recognize the index is already in place and not need to build it.

Former Member
0 Kudos

Thanks a lot Pizzaman

So I will drop the index (the index that is not active) which is not active on DB level and create indices (the missing ones) via admin workbench.

and transport to PROD.

one last question

How can patrition the ODS, Is it avilable as part of Admin Workbench OR DB level?

How to look at creating the index at the Oracle level using parallel processing ?

Thanks once again

I wanted to award more points but all options are not visible

Regards

Naveen

Former Member
0 Kudos

Not sure what your role/system privledges are at your shop are - ODS partitioning and creating an index at the DB level would be a DBA function.

Partitioning of the an ODS can NOT be done thru the BW Admin Workbench up thru 3.5 (I am not sure about 2004s - something makes me think I read that it would be able to, but maybe that was still a future item). It would be done at the DB level. An ODS with 2 billion rows would certainly be something I would want to partition.

As for parallel index creation - here's a link to Don Burleson's web site.

http://www.dba-oracle.com/oracle_tips_index_speed.htm

0 Kudos

hi,

my 2 cents ...

as mentioned by Pizzaman, it could be a DBA function.

when encounter missing index here, i NEVER have to create with AWB and transport to the production, the DBA will take care of it and i think i can sure they didn't touch the ods in development since no change request

i would prefer to not do re-transport ods changes if not for new fields have been added, especially for ods with high volume data, since the transport itself may take hours duration and system resource problem tend to happen.

hope this helps.

Former Member
0 Kudos

Hi Pizzaman & AHP

Good to know all the aspects...

Any how we added new fields to ODS in DEV so do you suggest creating it in ODS def and treansport OR directly fix on PROD with my DBA

Regards

Naveen

0 Kudos

hi Naveen,

check if the index actually exist already in dev. if not then create and

since you are going to transport the ods again with the new infoobjects added, it should bring the index also, so just go with transport.

hope this helps.

Former Member
0 Kudos

If by "added new fields" you mean you added the index tehn just transport it, but if you mean you added other fields to the ODS, then I would agree with AHP, and correct in Prod and then transport the ODS when you are ready to move them to Prod.

Just to clarify - when AHP says they NEVER have to create the index on Dev and transport to resolve a missing index:

If th eindex has already been defined to SAP and is missing, then that is something to resolve on Prod with the DBA.

As far as creating new secondary ODS indices, I think this might depend on shop preferences or standards. Our shop standard is to try to keep our Dev, QA, and Prod environments as consistent as possible, so that is why I create them on Dev and transport thru. This also has an advantage perhaps of a better documented creation/reason for the index. On the flip side, in most cases the secondary indices serve little purpose in our Dev and QA systems because we have only a small amount of data in those systems.

As AHP mentions - if you transport the ODS, and you had made other changes to the ODS besides the secondary index, you could end up making some unintended changes to Prod.

Former Member
0 Kudos

Pizzaman

I mean we added new infoobjects to the ODS as well secondary indexes from Admin workbench and trasported to QA sucessfully.

Now we want to trasport it to PROD, since the index on PROD is missing Just wondering weather the index that i transport from DEV will overwrite in PROD or I should delete the missing index on PROD and trasport it?

Any how thanks to you all for nice inputs

Regards

Naveen

Answers (3)

Answers (3)

Former Member
0 Kudos

Hi

U can use se14 to fix the missing indexs

Former Member
0 Kudos

Hi

Are these indeces created in the BW application?

if yes, then just rcreate a transport of the ODS and transport it thru the landscape, D->Q->P.

Reg's

Edan

0 Kudos

dear Naveen,

yes, you can use SE14 to fix the missing indices.

hope this helps.