1

I'm beginning to work with Oracle and I've learn that in Oracle 9 statistics aren't collected for an index unless you use COMPUTE STATISTICS, but you can't use that option when defining a primary key, so I would want to know if primary keys can use statistics in some way.

If not, is there a noticeable difference in performance? Are there any workarounds?

Thanks in advance.

Alberto Martinez
  • 2,620
  • 4
  • 25
  • 28

1 Answers1

1

First of all, you can define your index before you define the primary key constraint, hence you'll have full control over the index (you can add COMPUTE STATISTICS as well) see this http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/indexes003.htm#i1006566

Second, unless you have a good reason for it, you should use the Automatic Statistics Gathering http://download.oracle.com/docs/cd/B12037_01/server.101/b10752/stats.htm#40674

bpgergo
  • 15,669
  • 5
  • 44
  • 68
  • The problem is that we need to use Oracle 9i per client specifications, and it doesn't seem to have the Automatic Statistics Gathering. I'll try the first suggestion of creating first the index and then associate it with the primary key. – Alberto Martinez Aug 30 '11 at 08:33
  • 1
    I found that you can add the COMPUTE STATISTICS option to a existing primary key using: "ALTER INDEX key_index_name REBUILD COMPUTE STATISTICS;" – Alberto Martinez Sep 12 '11 at 17:45