Solved

compute statistics using index Tablespace

Posted on 2004-08-18
6
1,336 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
6 Comments
 
LVL 18

Expert Comment

by:JR2003
Comment Utility
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
Comment Utility
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
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 1

Expert Comment

by:JanMah
Comment Utility
The QSA_INDEX to be changed to tsdix, sorry

JanMah
0
 
LVL 47

Expert Comment

by:schwertner
Comment Utility
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 34

Expert Comment

by:johnsone
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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 take different types of Oracle backups using RMAN.

763 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now