Search code examples
sqloracle-databaseoracle11gora-00904

index on oracle 11g question

i have table (MEN) with 900,000 records.

in this table i have field IP and Tdate.

when i run query:

select * from MEN where IP = '1.1.1.1' and Tdate = TO_DATE('07/04/2010', 'DD/MM/YYYY')

it takes long time until i get Result.

i try to make index like this:

create index
    my_in
on
   MEN (IP,Tdate );

but how to run the query to get fast Result?

i try this:

select My_in from MEN where IP = '1.1.1.1' and Tdate = TO_DATE('07/04/2010', 'DD/MM/YYYY')

and get error: ORA-00904

Solution

  • You do not use the index name (My_in) in the select. The database itself will figure out to use the index. So you should just do the same select as in your first example.