Re: Oracle using indexes on RAW PK columns

On 23 Aug 2002 13:39:03 -0500, allenh_at_starbase.neosoft.com (Allen) wrote:

>
>Oracle 9.2 linux standard edition, optimizer_mode=first_rows, full schema
>analysis done every night.
>
>For some reason (before I was hired) the developers started using RAW(24)
>columns as primary keys. These PKs are indexed of course. But explain
>plays says its doing full table scans. However when I add the hint /*+
>index(tname,pk_indx) */ it starts using the index. Is there anyway to get
>oracle to use this index without using hints, or should I tell the
>developers its time to switch to something more appropriate like integer
>PKs..
>
>thx.. Allen

You are hitting my worst nightmare.
I am administering a third party app where all PK columns are RAW(16)

RAW is a datatype native to PL/SQL and OCI *only* Hence outside PL/SQL and OCI, you'll need <PK COLUMN > = HEXTORAW(<string expression or variable>) *EVERYWHERE* This is also the reason why EXPLAIN PLAN (NOT the optimizer) fails to report the actual path being used. You can verify that in v$session_longops, you won't see any full table scans, provided they use a RAW bind variable or HEXTORAW('<string literal>')

In my case your hint won't work, as the app has been developed, guess what, using *RBO*, and using any other hint than RULE automatically switches the optimizer to CBO.

In our situation , any adhoc select will end-up in a full table scan as it will be impossible to explain to end-users they have to use = HEXTORAW('<string>')

Your developers are on the way to hell, and your firm is about to get sued. (Which is the probable outcome in my situation, as our client is getting completely stuck with the 3rd party app) I am afraid I can't disclose the name of the application, nor the customer.

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Fri Aug 23 2002 - 17:05:38 CDT