Home » SQL & PL/SQL » SQL & PL/SQL » index question
index question [message #189690] Fri, 25 August 2006 11:16 Go to next message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member

I have a weird situation that I cannot explain. We are on Oracle 9i, Rule-Based optimizer. I have a table (EMPLOYEE) with the following indexes:

1.
CREATE UNIQUE INDEX EMPLOYEE_KEY_INDEX ON EMPLOYEE
(EMPLOYEE_PIDM, EMPLOYEE_ID, EMPLOYEE_LAST_NAME, EMPLOYEE_FIRST_NAME, EMPLOYEE_MI,
EMPLOYEE_CHANGE_IND, EMPLOYEE_NTYP_CODE)

2.
CREATE INDEX EMPLOYEE_INDEX_PERS ON EMPLOYEE
(EMPLOYEE_LAST_NAME, EMPLOYEE_FIRST_NAME, EMPLOYEE_MI, EMPLOYEE_ENTITY_IND, EMPLOYEE_CHANGE_IND)

3.
CREATE INDEX EMPLOYEE_INDEX_SEARCH ON EMPLOYEE
(EMPLOYEE_SEARCH_LAST_NAME, EMPLOYEE_SEARCH_FIRST_NAME, EMPLOYEE_SEARCH_MI)

4.
CREATE INDEX EMPLOYEE_INDEX_ID ON EMPLOYEE
(EMPLOYEE_ID, EMPLOYEE_ENTITY_IND, EMPLOYEE_CHANGE_IND)

5.
CREATE INDEX BANINST1.NP_UPPER_INDEX ON EMPLOYEE
(UPPER("EMPLOYEE_ID"))

I force CBO by using ALL_ROWS hint. Since there is like 30 mil records in Employees table, and my select uses LOWER function on EMPLOYEE_ID, I have created this last function-based index. However, explain plan shows that
this index is not used, as Oracle performs 2 NESTED LOOPS via INDEX RANGE SCAN of EMPLOYEE_KEY_INDEX. Any idea why?

The problem is that we use EMPLOYEE_ID as one of the fields that our users can search by in an online search application. It is when the users search by this field that makes everything super slow...

thanx


Re: index question [message #189772 is a reply to message #189690] Sun, 27 August 2006 03:07 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Since you (normally) use RBO, there is a chance that there are no statistics.
Also, how exactly do you search? Is it with LIKE? Do they have to provide the first n characters?

[Updated on: Sun, 27 August 2006 03:08]

Report message to a moderator

Re: index question [message #189810 is a reply to message #189772] Sun, 27 August 2006 23:52 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Could you post the super-slow SQL as well, because there are a few things in your initial post that don't add up.
- You use LOWER in the SQL, but created an index on UPPER Sad
- You sy it uses NESTED LOOPS, but you don't tell us what you are joining to Sad

Ross Leishman
Previous Topic: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
Next Topic: callin oracle stored procedures in Vb
Goto Forum:
  


Current Time: Sat Dec 03 03:53:38 CST 2016

Total time taken to generate the page: 0.09949 seconds