8 Replies Latest reply on May 30, 2017 2:30 PM by Matperry-Oracle

    ORA-01450: maximum key length (6398) exceeded

    3054700
    3054700 Apr 20, 2017 11:42 AM

      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 1723
ORA-06512: at "MDSYS.MDERR", line 17
ORA-06512: at "MDSYS.SDO_RDF_INTERNAL", line 19260
ORA-06512: at "MDSYS.SDO_RDF", line 5250
ORA-06512: at "MDSYS.RDF_APIS", line 2193
ORA-06512: at line 1
13199. 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

        • 1. Re: ORA-01450: maximum key length (6398) exceeded
          3054700
          3054700 Apr 20, 2017 4:32 PM (in response to 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);
          • 2. Re: ORA-01450: maximum key length (6398) exceeded
            alwu-Oracle
            alwu-Oracle May 1, 2017 3:47 PM (in response to 3054700)

            Hi,

             

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

             

            SQL> show parameters nls;

             

            Thanks,

             

            Zhe Wu

            • 3. Re: ORA-01450: maximum key length (6398) exceeded
              3054700
              3054700 May 2, 2017 4:59 PM (in response to alwu-Oracle)

              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
              • 4. Re: ORA-01450: maximum key length (6398) exceeded
                Matperry-Oracle
                Matperry-Oracle May 4, 2017 5:24 PM (in response to 3054700)

                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

                • 5. Re: ORA-01450: maximum key length (6398) exceeded
                  3054700
                  3054700 May 26, 2017 11:40 AM (in response to Matperry-Oracle)

                  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

                  • 6. Re: ORA-01450: maximum key length (6398) exceeded
                    Matperry-Oracle
                    Matperry-Oracle May 26, 2017 5:15 PM (in response to 3054700)

                    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

                    1 person found this helpful
                    • 7. Re: ORA-01450: maximum key length (6398) exceeded
                      3054700
                      3054700 May 29, 2017 6:40 PM (in response to Matperry-Oracle)

                      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

                      • 8. Re: ORA-01450: maximum key length (6398) exceeded
                        Matperry-Oracle
                        Matperry-Oracle May 30, 2017 2:30 PM (in response to 3054700)

                        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

                        1 person found this helpful