ORA-01450: maximum key length (6398) exceeded

3054700

Dear all,

On creating a string data type index on RDF_VALUE$, I'm getting the SQL Error: ORA-01450: maximum key length (6398) exceeded.

EXECUTE SEM_APIS.ADD_DATATYPE_INDEX('http://www.w3.org/2001/XMLSchema#string')

Error starting at line : 1 in command -EXECUTE SEM_APIS.ADD_DATATYPE_INDEX('http://www.w3.org/2001/XMLSchema#string')Error report -ORA-13199: SQLERRM=ORA-01450: maximum key length (6398) exceeded [index_name=RDF_V$STR_IDX datatype=http://www.w3.org/2001/XMLSchema#string dss=CREATE INDEX RDF_V$STR_IDX         ON MDSYS.RDF_VALUE$(sem_apis.getV$StringVal(value_type,vname_prefix,vname_suffix,literal_type,language_type)) TABLESPACE "RDF_USERS" ]ORA-06512: at "MDSYS.MD", line 1723ORA-06512: at "MDSYS.MDERR", line 17ORA-06512: at "MDSYS.SDO_RDF_INTERNAL", line 19260ORA-06512: at "MDSYS.SDO_RDF", line 5250ORA-06512: at "MDSYS.RDF_APIS", line 2193ORA-06512: at line 113199. 00000 -  "%s"*Cause:    This is an internal error.*Action:   Contact Oracle Support Services.

Any idea what can be going on here?

UPDATE

I think it happens because I changed MAX_STRING_SIZE from STANDARD to EXTENDED on my database. Is there a way to use a string datatype index on RDF_VALUE$ when MAX_STRING_SIZE is EXTENDED?

The documentation says:

By setting MAX_STRING_SIZE = EXTENDED, users are taking an explicit action that could introduce application incompatibility in their database. Applications that do not want to use the expanded data types can be rewritten for compatibility with either setting; for example, these applications could use explicit CASTs to fix the length of VARCHAR2 expressions during CREATE TABLE AS SELECT.Altering MAX_STRING_SIZE will update database objects and possibly invalidate them, as follows: Tables with virtual columns will be updated with new data type metadata for virtual columns of VARCHAR2(4000), 4000-byte NVARCHAR2, or RAW(2000) type.Functional indexes will become unusable if a change to their associated virtual columns causes the index key to exceed index key length limits. Attempts to rebuild such indexes will fail with ORA-01450: maximum key length exceeded. Views will be invalidated if they contain VARCHAR2(4000), 4000-byte NVARCHAR2, or RAW(2000) typed expression columns. Materialized views will be updated with new metadata VARCHAR2(4000), 4000-byte NVARCHAR2, and RAW(2000) typed expression columns

Fred

Message was edited by: Fred

Matperry-Oracle

Hi Fred,

In 12.2, you can use an ' EXTENDED_VC=T ' option in add_datatype_index() as a workaround for that maximum key length issue. However, the xsd:string index is really only used for greater than and less than comparisons.

For searching based on string equality, the best approach is to use the variable directly without a call to STR(). The STR() call is not really necessary if the values you are trying to match are plain literals or xsd:string literals. If you need to cast a URI or numeric literal, etc. to a string, then you need that STR() call.

FILTER (?term = "qwerty")

For a simple FILTER like this, we resolve the constant "qwerty" to a numeric ID at compile time and then match the id directly against the triples table (mdsys.rdf_link$). This avoids a join with the values table (mdsys.rdf_value$) to do lexical value processing for STR().

If your query has an ubound predicate in the triple pattern that contains ?term, then you need an index on rdf_link$ with a leading C column.

{ ?s ?p ?term

  FILTER (?term = "qwerty") }

A CSPGM index would work well for this query. You can create one through sem_apis.add_sem_index(): SEM_APIS Package Subprograms

Note that the index will initially be in an unusable state, and you will need to rebuild it with sem_apis.alter_sem_index_on_model/entailment. When querying afterwards, It is also a good idea to set skip_unusable_indexes to false if you are doing a multi-model query.

alter session set skip_unusable_indexes=false;

In addition, it's best to re-run sem_perf.gather_stats or sem_apis.analyze_model/entailment after creating a new index.

Thanks,
Matt

3054700

Does this make sense?

CREATE INDEX MDSYS.V$STRINGIDX ON MDSYS.RDF_VALUE$(SUBSTRB( SEM_APIS.getV$StringVal(VALUE_TYPE, VNAME_PREFIX, VNAME_SUFFIX, LITERAL_TYPE, LANGUAGE_TYPE), 0, 4000);

alwu-Oracle

Hi,

What is your nls_length_semantics setting? Please run the following as SYS:

SQL> show parameters nls;

Thanks,

Zhe Wu

3054700

Zhe Wu, here is the output:

NAME                                               TYPE        VALUE                                                                                               -------------------------------------------------- ----------- ----------------------------------------------------------------------------------------------------nls_calendar                                       string                                                                                                         nls_comp                                           string      BINARY                                                                                             nls_currency                                       string                                                                                                         nls_date_format                                    string                                                                                                         nls_date_language                                  string      ENGLISH                                                                                             nls_dual_currency                                  string                                                                                                         nls_iso_currency                                   string                                                                                                         nls_language                                       string      ENGLISH                                                                                             nls_length_semantics                               string      BYTE                                                                                               nls_nchar_conv_excp                                string      FALSE                                                                                               nls_numeric_characters                             string                                                                                                         nls_sort                                           string                                                                                                         nls_territory                                      string      BRAZIL                                                                                             nls_time_format                                    string                                                                                                         nls_time_tz_format                                 string                                                                                                         nls_timestamp_format                               string                                                                                                         nls_timestamp_tz_format                            string

Matperry-Oracle

Hi Fred,

Which version of the database are you using? Also, what types of queries are you hoping to speed up with this index?

Thanks,
Matt

3054700

Matt we were using 12.1 but we are now upgrading to 12.2.

We sometimes need to filter an unbound variable to match an exact string literal, i.e.

FILTER (str(?term) = "qwerty")

As we already have a CTXSYS.CONTEXT index on MDSYS.RDF_VALUE$(VNAME_PREFIX), I have found convenient to use it doing something like

FILTER (orardf:textContains(?term, "qwerty") && strlen(?term) = strlen("qwerty"))

It seems to be ok so far, but any other suggestion is welcome!

Thank you!

Fred

Matperry-Oracle

Hi Fred,

In 12.2, you can use an ' EXTENDED_VC=T ' option in add_datatype_index() as a workaround for that maximum key length issue. However, the xsd:string index is really only used for greater than and less than comparisons.

For searching based on string equality, the best approach is to use the variable directly without a call to STR(). The STR() call is not really necessary if the values you are trying to match are plain literals or xsd:string literals. If you need to cast a URI or numeric literal, etc. to a string, then you need that STR() call.

FILTER (?term = "qwerty")

For a simple FILTER like this, we resolve the constant "qwerty" to a numeric ID at compile time and then match the id directly against the triples table (mdsys.rdf_link$). This avoids a join with the values table (mdsys.rdf_value$) to do lexical value processing for STR().

If your query has an ubound predicate in the triple pattern that contains ?term, then you need an index on rdf_link$ with a leading C column.

{ ?s ?p ?term

  FILTER (?term = "qwerty") }

A CSPGM index would work well for this query. You can create one through sem_apis.add_sem_index(): SEM_APIS Package Subprograms

Note that the index will initially be in an unusable state, and you will need to rebuild it with sem_apis.alter_sem_index_on_model/entailment. When querying afterwards, It is also a good idea to set skip_unusable_indexes to false if you are doing a multi-model query.

alter session set skip_unusable_indexes=false;

In addition, it's best to re-run sem_perf.gather_stats or sem_apis.analyze_model/entailment after creating a new index.

Thanks,
Matt

3054700

Thank you for your answer Matt, it is full of insights.

I have the following indexes in usable state: PSCM CPSM SCPM (I don't use named graphs)

Indeed I'm doing queries on a virtual model (all the models in the virtual model have the indexes PSCM CPSM SCPM in usable state). I'm not sure to understand why:

When querying afterwards, It is also a good idea to set skip_unusable_indexes to false if you are doing a multi-model query.

Thank you for you help!

Fred

Matperry-Oracle

Hi Fred,

This one is a bit counter-intuitive. When you do a virtual model query, the SQL optimizer considers it to be a query against the whole rdf_link$ table, so it will only use an index if it is usable in all partitions of rdf_link$ (with the default setting of skip_unusable_indexes= true). When you set skip_unusable_indexes=false, the optimizer will try to use an index even if it is unusable in some partitions.

Thanks,
Matt