Simple Oracle query hangs depending on WHERE clause

Question

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)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |    12 |  4416 |  2376K  (1)| 07:55:19 |       |       |
|   1 |  PARTITION RANGE ALL|          |    12 |  4416 |  2376K  (1)| 07:55:19 |     1 |    14 |
|*  2 |   TABLE ACCESS FULL | [Employee|    12 |  4416 |  2376K  (1)| 07:55:19 |     1 |    14 |
------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$1 / Employees@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("EmployeeID"='3R5MMN-0F9345L9IK8A349A043')

Column Projection Information (identified by operation id):
-----------------------------------------------------------
-- The list of all the columns follows.

My guess is that this is just a really awefully designed table and not designed to have gotten so big, or for someone to actually use a lot :) For practical purposes, I will move on to more recent incarnations of this table which will have indexing and hopefully that will fix it.

Answer 1

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