{Oracle}… {Oracle Off}

oracleoracle-11g

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!

Best Answer

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.

Related Question