1

I inherited an old script, that should, as part of a pl/sql routine, create a temporary index for me. I tried it on Oracle 11.2.0.4.0, but it does not accept the options.

CREATE INDEX x ON y(gid) {IN IndexTableSpace} {ORACLE}COMPUTE STATISTICS{ORACLE OFF};

However I am not familiar with the options listed in {}. Can somebody enlighten me as to what these are? And would the following be a updated equivalent?

create index x on y(gid) tablespace IDX_TS;

Any help is appreciated!

  • 3
    That's not valid SQL or PL/SQL syntax. I would assume that your script is going through some sort of pre-processor that is looking for the text in the brackets and replacing it with valid syntax. – Justin Cave Jan 19 '15 at 10:37
  • I checked the script and I could not find anything that does what you are describing. It creates the index as shown, then uses it in a routine and drops it again. Therefore I assume these {} are just performance optimizations... – Hoax Jan 19 '15 at 10:52
  • They are not "just performance optimizations". – Colin 't Hart Jan 19 '15 at 10:58
2

I guess these {...} get replaced somewhere in the code with something that makes sense.

This is indeed an old script because compute statistics is not quite state of the art anymore.

Go ahead and create the index with the sql you just posted:

create index x on y(gid) tablespace IDX_TS;

And then analyze the table with dbms_stats:

begin
  DBMS_STATS.GATHER_TABLE_STATS(
       ownname          => '<owner of the table>'
      ,tabname          => 'Y'
      ,estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE
      ,method_opt       => 'FOR ALL COLUMN SIZE AUTO'
      ,granularity      => 'AUTO'
      ,cascade          => TRUE
    );
end;
/

EDIT: "cascade => TRUE" will analyze indexes of the table as well.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.