Solved

compute statistics using index Tablespace

Posted on 2004-08-18
6
1,368 Views
Last Modified: 2008-01-09
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
0
Comment
Question by:bolicat
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 18

Expert Comment

by:JR2003
ID: 11829868
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
 
LVL 23

Expert Comment

by:seazodiac
ID: 11829902
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
 
LVL 1

Accepted Solution

by:
JanMah earned 250 total points
ID: 11830207
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Expert Comment

by:JanMah
ID: 11830213
The QSA_INDEX to be changed to tsdix, sorry

JanMah
0
 
LVL 48

Expert Comment

by:schwertner
ID: 11830518
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
 
LVL 35

Expert Comment

by:johnsone
ID: 11831073
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

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question