Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
Is that column indexed? Otherwise you'll get a full table scan, which can take a while if the table is big. –  Mat Aug 31 '12 at 3:34
    
Let me look into this. I think it must be doing a full table scan... it is taking forever and hanging... how do I check indexing normally? I am using SQLTools as my basic connection and query tool... –  fa1c0n3r Aug 31 '12 at 3:48
1  
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 Aug 31 '12 at 3:57
2  
Please show us the execution plan for the second query –  a_horse_with_no_name Aug 31 '12 at 6:48

1 Answer 1

up vote 1 down vote accepted

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

share|improve this answer
    
I would say that is a good assumption. We experience something similar in DB2. A good index, or lack thereof, can make a ton of difference. –  Chris Aldrich Aug 31 '12 at 12:40
    
thanks seems to make sense... –  fa1c0n3r Aug 31 '12 at 23:52

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.