4 Replies Latest reply: Jan 7, 2010 11:17 PM by NestaFeng RSS

    unique index

    NestaFeng
      TimesTen 11.2.1.4.0

      There is one cache table:

      Table ORACLE.GUY:
      Columns:
      *ID                              NUMBER NOT NULL
      NAME VARCHAR2 (50) INLINE NOT NULL
      CITY VARCHAR2 (50) INLINE
      Aging: LRU on

      1 table found.

      Add unique index
      create unique index name_index on oracle.guy(name);

      Execute sql query
      select * from oracle.guy where name = 'nesta'

      Error Message
      2609: Incompatible types found in expression
      Execution time (SQLPrepare) = 0.000559 seconds.
      The command failed.

      After I drop the unique index, it works well. And then I create a non-unique index, and it works too.

      Regards,
      Nesta
        • 1. Re: unique index
          Slaw-Oracle
          Hi nesta,

          I cannot reproduce this using a regular table in TimesTen 11.2.1.4.0, is it a cache table specific issue?

          Please can you do a desc on the cache group, and also on the oracle.guy table in your Oracle database, and post the results here.

          Simon
          • 2. Re: unique index
            NestaFeng
            Hi slaw,

            Yes, agree. I can not either.

            It seems that "desc" can't be used on cache group.

            Cache groups information with "cachegroups"

            Cache Group CACHEUSER.GLOBALCACHE:

            Cache Group Type: Asynchronous Writethrough global (Dynamic)
            Autorefresh: No
            Aging: LRU on

            Root Table: ORACLE.GUY
            Table Type: Propagate

            1 cache group found.

            Oracle table information:
            Name Null? Type
            ----------------------------------------- -------- ----------------------------
            ID NOT NULL NUMBER
            NAME NOT NULL VARCHAR2(50)
            CITY VARCHAR2(50)

            Regards,
            Nesta
            • 3. Re: unique index
              Jspalmer-Oracle
              Nesta - according to your definitions you have a primary key on column "ID"

              Table ORACLE.GUY:
              Columns:
              *ID NUMBER NOT NULL
              NAME VARCHAR2 (50) INLINE NOT NULL
              CITY VARCHAR2 (50) INLINE
              Aging: LRU on

              1 table found.

              You add a unique index to a non-primary key column:

              Add unique index
              create unique index name_index on oracle.guy(name);

              Execute sql query
              select * from oracle.guy where name = 'nesta'

              This is not going to work on a Global Dynamic AWT cache group. I agree the 2609 error is rather misleading but you can only do a dynamic load with an equality expression on the primary key column. See "Types of SQL statements for which dynamic load is available" in the Cache user's Guide, chapter 5 Cache Group Operations. Even if you have a non-unique index on a non-primary key column, the dynamic load still won't load the cache instance (row) from Oracle or another TT node.

              I hope I haven't overlooked something here. Please let me know if I have.
              • 4. Re: unique index
                NestaFeng
                Hi jspalmer,

                Yes, nothing missed.
                Thanks very much.

                Regards,
                Nesta