Solved

Create index problems

Posted on 2004-10-13
19
1,307 Views
Last Modified: 2008-03-06
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!

0
Comment
Question by:wuyue15
  • 6
  • 5
  • 3
  • +4
19 Comments
 
LVL 7

Expert Comment

by:bvanderveen
Comment Utility
What is the datatype of STARTTIME?

Could you DESCRIBE the EVENTVIEW table and post the results?
0
 
LVL 12

Expert Comment

by:catchmeifuwant
Comment Utility
Is eventview a View?You cannot create an index on a view...instead create it on the base table
0
 
LVL 7

Expert Comment

by:bvanderveen
Comment Utility
>>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
 
LVL 11

Expert Comment

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

Author Comment

by:wuyue15
Comment Utility
eventview is a table called eventview.
0
 

Author Comment

by:wuyue15
Comment Utility
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
 
LVL 11

Expert Comment

by:cjjclifford
Comment Utility
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
 
LVL 47

Expert Comment

by:schwertner
Comment Utility

May be EVENTVIEW  or STARTTIME are reserved words in Oracle.
Also check if you do this user who owns the table.
0
 
LVL 7

Expert Comment

by:Bigfam5
Comment Utility
What data explorere application are you using?
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.

 

Author Comment

by:wuyue15
Comment Utility

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
 
LVL 3

Expert Comment

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

Expert Comment

by:bvanderveen
Comment Utility
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
 
LVL 11

Accepted Solution

by:
cjjclifford earned 400 total points
Comment Utility
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
 

Author Comment

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

Expert Comment

by:Bigfam5
Comment Utility

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
 
LVL 11

Expert Comment

by:cjjclifford
Comment Utility
glad I could help.
0
 
LVL 11

Expert Comment

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

Author Comment

by:wuyue15
Comment Utility
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
 
LVL 11

Expert Comment

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

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

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

772 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

10 Experts available now in Live!

Get 1:1 Help Now