Simple Oracle query hangs depending on WHERE clause

Not used to using Oracle, but we have a large database where a non-unique query like

SELECT * FROM employees where department = 'HR'

is working, results list up no problem.

But when I do

SELECT * FROM employees where employeeID = '3HVtxO-F3004728F87EF61E'

the oracle database query is hanging (I am expecting one record to come back, and I am certain it exists because I copy pasted the employeeID from another query).

For the columns, only difference between the two is:

department is VARCHAR2(25)  NULL
employeeID is VARCHAR2(50)  NULL

Also department would have many matches while employee ID would most likely have 1 (I can't see that it is particularly defined to be unique in the table definition).

Other table characteristics:

• not actually an employee table, it has work related data so I renamed it here...

• contains more than a million rows and a few dozen columns

• a bit of an old database, not sure who designed it at work ages ago, could have database integrity / indexing issues?

• a regular select * from employees with no where clause would not work either because it freezes up around the half a million rows found mark.

Any idea why this could be happening? Should I design my query better? How would you recommend diagnosing some issue related to problems with the database itself? Trying to get a hang of this oracle database but a bit awkward to get used to since a simple query which should return one item is hanging. Thank you.

Update 1:
Responding to comments, there is no indexing for this column. Fortunately this is an outdated table which will not be in use at all, with a new version being made which will have indexed columns, so I think this was one issue.

Regarding the plan for the second query, it looks like:

```
Plan hash value: 123724717

------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Tim

following the advice of a_horse_with_no_name,


Please show us the execution plan for the second query –
a_horse_with_no_name 4 hours ago

I have posted the relevenat parts of the execution plan... however I can't make heads or tails of what is wrong with it from that....

I followed Mat's advice and guess that lack of indexing is the issue. Because there is no indexing, it must be as Matt says, that this simple query is doing a full table scan, which could take longer than the few minutes I have been waiting for the query to complete...

Useful link about the concept of indexing: http://www.orafaq.com/node/1403


Is that column indexed? Otherwise you'll get a full table scan, which
can take a while if the table is big. – Mat 7 hours ago


select index_name, column_name from dba_ind_columns where index_owner
= 'THE_SCHEMA' and table_name = 'YOUR_TAB' order by index_name will get you all the indexed columns. Look at the explain plan to see what
it is doing (look into set autotrace or use a GUI that has that
feature, like sqldeveloper) – Mat 7 hours ago

Thanks for your help guys. Let me know if I might be wrong in that assumption, or if you might have additional insight a_horse_with_no_name....

StackExchange Database Administrators Q#23482, answer score: 2