Create index problems

I want to create an index on a event start time. But I have tried all possible combinations I thought of:
create index idinidin
on EVENTVIEW (STARTTIME);
I got "ORA-00911: invalid character" but not indication which character that is.

create index idinidin
on EVENTVIEW (STARTTIME);
/
I got "ORA-00911: invalid character" but not indication which character that is. I also tried using the "\" and it failed too.

create index idinidin
on EVENTVIEW (STARTTIME)
SELECT STARTTIME FROM EVENTVIEW

I got "ORA-02158: invalid CREATE INDEX option" but I have no way of knowing what SQL was sent to my db to check if the DDL was wrongly formed.

I tried
create index idinidin
on EVENTVIEW (STARTTIME)
I got "ORA-01702: a view is not appropriate"

But if I just use
SELECT STARTTIME FROM EVENTVIEW
No problem at all.
Headache!!!

help??

Thanks in advance!

wuyue15Asked:
Who is Participating?
 
cjjcliffordConnect With a Mentor Commented:
being able to select from the object doesn't rule out the possibility it is a view - do my "select count(*)" above, if it returns a zero count its probably a view....
0
 
bvanderveenCommented:
What is the datatype of STARTTIME?

Could you DESCRIBE the EVENTVIEW table and post the results?
0
 
catchmeifuwantCommented:
Is eventview a View?You cannot create an index on a view...instead create it on the base table
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
bvanderveenCommented:
>>create index idinidin
>>on EVENTVIEW (STARTTIME);

Are you using SQL Plus?  If so, try leaving out the semicolons, and just using the "/" to execute.

>>create index idinidin
>>on EVENTVIEW (STARTTIME)
>>SELECT STARTTIME FROM EVENTVIEW
This isn't accepted because it is two separate SQL statements.  Need to execute the first, then the second.  That's why you have the invalid option error.
0
 
cjjcliffordCommented:
Hi,

you cannot create an index on a view - a view is basically a pre-prepared select statement...

  1* create view test_view as select 1 a, 2 b from user_tables
SQL> /

View created.

SQL> select count(*) from test_view;

  COUNT(*)
----------
        75

SQL> create index test_index on test_view( a );
create index test_index on test_view( a )
                           *
ERROR at line 1:
ORA-01702: a view is not appropriate here

SQL> drop view test_view;

View dropped.

=======

You create an index on a table:

SQL> create table test_table as select 1 a, 2 b from user_tables;

Table created.

SQL> select count(*) from test_table;

  COUNT(*)
----------
        75

SQL> create index test_index on test_table( a );

Index created.

SQL> drop table test_table;

Table dropped.

0
 
wuyue15Author Commented:
eventview is a table called eventview.
0
 
wuyue15Author Commented:
So bascially I did try to create an index in an table not a view. So still do not know what is the problem.

I did this query in a ODBC data explorer application, which might be a little bit of different to ordinary SQL system.

But it should support all the SQL statement.

The problem is  if I create a newtalbe based on the exsiting table then the index of the new table can be created succeesfully.
if I try to create an index directly  on the exsiting table (oracle), the problems happened.
0
 
cjjcliffordCommented:
I'd consider renaming it then (otherwise it could be confusing...)

Are you sure it is a table, as one of the errors you listed (fourth listing) indicates it is indeed a view. Check this by executing the following select:

SELECT count(*) FROM user_tables WHERE table_name = 'EVENTVIEW';

Note the uppercase in the table_name comparision...

There seems to be nothing wrong with either of the first 2 listings - the index name is a bit odd though, again I'd choose a name that makes sense for later management...
0
 
schwertnerCommented:

May be EVENTVIEW  or STARTTIME are reserved words in Oracle.
Also check if you do this user who owns the table.
0
 
Bigfam5Commented:
What data explorere application are you using?
0
 
wuyue15Author Commented:

Matrikon ProcessNet data explorer. you should not know this special and stupid software, which I must use!!!

One question, do you think you can query data from a view? Is it possible use SELECT ... FROM a view?
0
 
oratimCommented:
you only need to create the index one time, not everytime you want to select from the table.

CREATE INDEX idinidin on EVENTVIEW(STARTTIME);


THEN WHEN YOU WANT TO SELECT FROM THE TABLE,

TO GET ALLDATA WHERE STARTTIME IS BETWEEN 1 HOUR AGO AND NOW:

SELECT * FROM EVENTVIEW WHERE STARTTIME > SYSDATE - 1/24
0
 
bvanderveenCommented:
First, I would use SQL*Plus to create your index.

Your ODBC application should be able to talk with Oracle.  Very few of them use "/", though - that's a SQL*Plus thing.  Most use the semicolon.

You need to see if there is already an index on this table.  Is there?  

Try renaming the index.  I don't think the tablename is a problem, but you may be accessing a view instead of the table.  Try this:

select * from all_objects
where object_name = 'EVENTVIEW'

If eventview is in a different schema, you may be looking at a view created for other schemas.  You can also try this:

create index idinidin
on schema_name.EVENTVIEW (STARTTIME) --note use of schema name

This could also be a permissions issue if this is in a different schema.

But, the invalid character error indicates a data problem to me - could you post a describe of the table?
0
 
wuyue15Author Commented:
Cjjclifford,
You are right!!!
I did what you suggested. It is a view! but the people who gave me the work told me that is a table!!!

Thanks a lot!!!
0
 
Bigfam5Commented:

The error  ORA-00911 can refer to the semi-colon just as it does (in odd cases) in SqlPlus
EX.
XXXX> l
  1* create index idx_test2 on test1 (ddate)
XXXX> edi
Wrote file afiedt.buf

  1* create index idx_test2 on test1 (ddate);
XXXX> /
create index idx_test2 on test1 (ddate);
                                       *
ERROR at line 1:
ORA-00911: invalid character



Have you tried just executing the statement without the semicolon
0
 
cjjcliffordCommented:
glad I could help.
0
 
cjjcliffordCommented:
btw, if you have a look at the underlying SELECT of the view, it should be relatively straightforward to figure out which table to create the index, if indeed it is necessary...

This will return you the SELECT of the view:

select text from user_views where view_name = 'EVENTVIEW'

And from that figuring out the table with the STARTTIME column should be straightforward.
0
 
wuyue15Author Commented:
Cjjclifford,
I did select text from user_views where view_name = 'EVENTVIEW'
but the system retrun:
TEXT
MEMO
Are there any way I can know which table the STARTTIME column come from?
0
 
cjjcliffordCommented:
How did you run the select?
I would have expected a "SELECT..." statement though.... e.g. using SQLPlus:

SQL> create view test_view as select 1 a from dual;

View created.

SQL> select text from user_views where view_name = 'TEST_VIEW';

TEXT
--------------------------------------------------------------------------------
select 1 a from dual

SQL> drop view test_view;

View dropped.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.