?
Solved

Create index problems

Posted on 2004-10-13
19
Medium Priority
?
1,566 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
[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
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 48

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 1600 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Via a live example, show how to take different types of Oracle backups using RMAN.
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.
Suggested Courses

752 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