primary key generation failure

Hello,

We have a serious problem with our performance in Xe.
What we found is that the primary indices for all tables in our tablespace seems to be not correct.

The primary key is named 'F3411_0' and the constraints is named 'F3411_PK' when we generate a table/index.

So our database is not using the primary key and this slow down our system. We now have a lot of FULL tablescans.

greetings,

guenter

Xe, Oracle 9.0.1.4, SP22_H1, Update 6, HPUX11.11

I believe that _PK is always supposed to be the primary key. Did someone do some kind of Oracle re-indexing that did not work as expected.

hello,

no, we are creating our indices with OMW.

greetings

Guenter,

the name of the key is immaterial as long as it exists. The database decides what path/plan it will use based on a number of factors. OneWorld does not make the decision which index to use.

My guess is that you've never (or very infrequently) analyzed the database schemas to generate statistics:
"execute dbms_utility.analyze_schema('PRODDTA','COMPUTE');"

if no stats exist for the tables then, yes, you will have many full table scans.

I would not use OMW to rebuild your indices since it tends to put them in the wrong tablespace (example: in PRODDTAT instead of PRODDTAI).

Hope this helps,

If OMW is putting indexes in the wrong tablespace it's only because the OneWorld environment is set up incorrectly. You define what the name of the tablespace is when you initially create your environment....

sd

Well ... this is digging up an old thread ...

At any rate way back on XE and pre SP-20 service packs there WAS a problem with OMW and R98403 using the Oracle default tablespace for the schema owner when indices were recreated. A couple of the SARs related to this were 4535248 and 3370331. Since the original poster stated he was on XE I thought it worth mentioning.

Larry

Thanks for the post. I think we still have this problem. Everytime I generate a new table or index our DBA wants to know about it. We are still on XE SP18.1_E1. I will check out the sars. I hope the fix is not in an SP.

Patty.

Hi Patty,

my faulty memory tells me the problem was in a common BSFN but that it was distributed via a service pack . . .

Good luck!