Home » RDBMS Server » Performance Tuning » RAW datatype with NUMBER
RAW datatype with NUMBER [message #109156] Tue, 22 February 2005 10:48 Go to next message
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 #109161 is a reply to message #109156] Tue, 22 February 2005 11:26 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Please ignore the previous message.

CBO decides to use the index depending on skewness of data.
May be CBO thinks it is better NOT to use the index.
Full table scan are not always as bad as it looks
Re: RAW datatype with NUMBER [message #109213 is a reply to message #109161] Tue, 22 February 2005 22:42 Go to previous messageGo to next message
Mack Sundar
Messages: 20
Registered: July 2002
Junior Member
The data in the "name" field is names of people which has uniform spread. Not sure why there would be a skew in that field.
Re: RAW datatype with NUMBER [message #109317 is a reply to message #109213] Wed, 23 February 2005 15:06 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
By Any chance, is there a Primary Key on those raw columns?
If so then you need to use HEXTORAW.
Try using an index hint ( though it is not a very good approach).

[Updated on: Wed, 23 February 2005 15:12]

Report message to a moderator

Re: RAW datatype with NUMBER [message #109342 is a reply to message #109317] Wed, 23 February 2005 22:55 Go to previous messageGo to next message
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 Go to previous message
Mahesh Rajendran
Messages: 10707
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...


Previous Topic: materialized view
Next Topic: Help: chained rows
Goto Forum:
  


Current Time: Wed Feb 21 02:37:50 CST 2024