compute statistics using index Tablespace

Creating this index using oracle 9i

create unique index FLOG_PREFIX_DATE_inx on FAX_LOG
 (SUBSTR(FLAG_CONFIRM,5,10))
   compute statistics using index Tablespace tsdix;

And getting the following error:

ERROR at line 3:
ORA-02158: invalid CREATE INDEX option
bolicatAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
JanMahConnect With a Mentor Commented:
JR2003:
You can use Compute Statistics while Creating Index

Use the following:
create unique index FLOG_PREFIX_DATE_inx on FAX_LOG
 (SUBSTR(FLAG_CONFIRM,5,10))
   compute statistics;

If you want to create the index on tablespace tsdix then

create unique index FLOG_PREFIX_DATE_inx on FAX_LOG
 (SUBSTR(FLAG_CONFIRM,5,10)) tablespace QSA_INDEX
   compute statistics;


JanMah
0
 
JR2003Commented:
create unique index FLOG_PREFIX_DATE_inx on FAX_LOG
 (SUBSTR(FLAG_CONFIRM,5,10))
Tablespace tsdix;

I've never seen compute statistic put there before!
0
 
seazodiacCommented:
using index syntax does not belong here either.
It's used to create index when you create constraints like Primary key or Unique key.

JR's suggestion should work.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
JanMahCommented:
The QSA_INDEX to be changed to tsdix, sorry

JanMah
0
 
schwertnerCommented:
To create the tablespace use JRs sugestion.
 

To create statistics on index analyze the table as follow:
   
 ANALYZE TABLE fax_log COMPUTE STATISTICS;

or

exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => NULL, TABNAME => ' fax_log', CASCADE => TRUE);
0
 
johnsoneSenior Oracle DBACommented:
I agree with schwerner's suggestion.

Analyze the table after the index has been created.  This is because the optimizer will not understand the correct cost until the table has been re-analyzed.  Not sure if an enhancement has come through to address this, but that has definitely been the case.
0
All Courses

From novice to tech pro — start learning today.