Skip to Main Content
Forums

LiveLabs & Workshops

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

cannot change the ‘TreatAs’ field to an ‘Attribute’ in Oracle Analytics Desktop

User_3ARL4Aug 5 2021

I'm following Visualizing Your Data on Autonomous Database Workshop.
I'm stuck here Lab 4: Visualize Your Data => STEP 3: Create a Connection to Your Autonomous Database from Oracle Analytics Desktop => Click the CALENDAR_YEAR column name under Data Elements, and change the ‘TreatAs’ field to an ‘Attribute’. Repeat for the field, CUST_YEAR_OF_BIRTH.
The ‘TreatAs’ field has no response at my PC.
cannot_change_treatas_to_attribute.png2021-08-05_16-34-10.7z (623.58 KB)

Comments

Jonathan Lewis

You could use hashing (e.g. dbms_crypto.hash()) to create a hash value for the lob column - either as a function-based index or as a virtual column that you index, then change your code to have two predicate involving your input bind variable, something like:

where
        dbms_crypto.hash(blob column) = dbms_crypt.hash({your value})
and     dbms_Lob.compare(blob column, {your value} = 0

realistically you probably won't have any hash collisions, technically you might which is why you would use the indexed hash value as the first check then the actual value.
You may have to mess around a bit to work out the best way of setting this up (especially in 11.2). And you'll probably need to use substrb() on the hash value before you can index it.
See https://docs.oracle.com/cd/E18283_01/appdev.112/e16760/d_crypto.htm
I don't think there are any licence implications of using dbms_crypto, but you'd need to check that.
Gathering stats on the table will become expensive if you do this (see https://jonathanlewis.wordpress.com/2014/03/02/auto-sample-size )

Regards
Jonathan Lewis

Sebino

Virtual columns do not seem to be null-friendly, or am I mistaken ?
Because this blobColumn may be null (and quite often is), which make index creation fail with:

ORA-01405: fetched column value is NULL

Well - actually I must find a way to sneak around this error that is returned by the call to DBMS_CRYPTO.HASH (nothing to do with virtual column)...
Sebino

Jonathan Lewis
Answer

If dbms_crypto.hash doesn't accept a NULL input than use a CASE expression to generate the index, something like the following (1 is the value for the symbolic constant HASH_MD4):

create index t1_b1 on t1(case when b1 is not null then substrb(dbms_crypto.hash(b1,1),1,16) end)

The same messy expression will have to be used in your first predicate.
Or:

alter table t1 add hash_md4 generated always as (
        case when b1 is not null then substrb(dbms_crypto.hash(b1,1),1,16) end
) virtual
;

create index t1_b1 on t1(hash_md4);

Regards
Jonathan Lewis

Marked as Answer by Sebino · Jul 5 2021
1 - 3

Post Details

Added on Aug 5 2021
#livelabs-discussions
0 comments
233 views