Solved

Create index problems

Posted on 2004-10-13
19
1,369 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
ID: 12297916
What is the datatype of STARTTIME?

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

Expert Comment

by:catchmeifuwant
ID: 12297922
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
ID: 12297941
>>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
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 11

Expert Comment

by:cjjclifford
ID: 12297963
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
ID: 12298220
eventview is a table called eventview.
0
 

Author Comment

by:wuyue15
ID: 12298330
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
ID: 12298354
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
ID: 12298361

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
ID: 12298371
What data explorere application are you using?
0
 

Author Comment

by:wuyue15
ID: 12298418

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
ID: 12298437
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
ID: 12298453
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
ID: 12298515
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
ID: 12298626
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
ID: 12298630

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
ID: 12298679
glad I could help.
0
 
LVL 11

Expert Comment

by:cjjclifford
ID: 12298714
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
ID: 12298938
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
ID: 12299255
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.

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…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

809 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