RAW datatype with NUMBER [message #109156] |
Tue, 22 February 2005 10:48  |
Mack Sundar
Messages: 20 Registered: July 2002
|
Junior Member |
|
|
I have a table called CUSTOMER which has the following fields:
OBJ_ID: RAW(16)
OBSOLETE_FLAG NUMBER
INDIVIDUAL_FK RAW(16)
I also have another table called INDIVIDUAL with the following field:
OBJ_ID: RAW(16)
NAME: VARCHAR2(100)
I am executing the following query:
select customer.obj_id
from CUSTOMER c LEFT OUTER JOIN individual i ON
((c.individual_fk = i.obj_id) AND (c.obsolete_flag = 0))
WHERE (upper(i.name) = 'TOM')
ORDER BY c.obj_id ASC
I have the following indexes:
customer_obj_id ON customer.obj_id
customer_obsolete ON customer.obsolete_flag
customer_obj_obs ON customer(obj_id, obsolete_flag)
customer_ind_fk ON customer(individual_fk)
individual_obj_id ON individual(obj_id)
individual_name_upper ON individual(upper(name))
All tables and indexes have been analyzed. However, Oracle doesn't pick up the INDIVIDUAL_NAME_UPPER index at all for some reason. Instead, it does a FULL TABLE SCAN on the CUSTOMER table and a UNIQUE SCAN on INDIVIDUAL table taking about 15secs for this query to return.
I have also noticed that if I remove the (c.obsolete_flag = 0) condition in the LEFT OUTER JOIN, the query runs in about 10ms.
Would somebody be able to send me some ideas on how to get Oracle to use the index? Is there something about RAW datatypes OR function-based indexes that causes this?
I am using Oracle 9.2.0.1.
Thanks,
Mahesh
|
|
|
|
|
|
Re: RAW datatype with NUMBER [message #109342 is a reply to message #109317] |
Wed, 23 February 2005 22:55   |
Mack Sundar
Messages: 20 Registered: July 2002
|
Junior Member |
|
|
Yes, both the OBJ_ID fields are PKs and the INDIVIDUAL_FK is an FK. I assume that I would need to say the following in the query:
((hextoraw(c.individual_fk) = hextoraw(i.obj_id)) AND (c.obsolete_flag = 0))
Question is if there is any other way to resolve this issue because both INDIVIDUAL_ID and OBJ_ID are not HEX values, but RAW fields. It would be good to know that because then we would need to stop using RAW fields as PKs in our application.
We use RAW datatype for PKs throughout the application and the rest of the queries perform very well.
|
|
|
Re: RAW datatype with NUMBER [message #109346 is a reply to message #109342] |
Thu, 24 February 2005 00:16  |
 |
Mahesh Rajendran
Messages: 10708 Registered: March 2002 Location: oracleDocoVille
|
Senior Member Account Moderator |
|
|
A PK on raw column is not very recomended.
I beleive.
column_name=hextoraw(somecharacter)
is the only way to make use of index.
Else
oracle will do an implicit conversion
rawtohex(column_name)= somecharacter
which will disable indexes.
I will set a test case...
|
|
|