6 Replies Latest reply on Feb 19, 2014 12:06 PM by tkandl

    Foreign Keys and performance

    tkandl

      Hi all,

       

      I have a question regarding wether to use foreign keys or not.

       

      In the performing tuning section of the times ten operations guide it is stated:

      The declaration of a foreign key has no performance impact on SELECT queries, ...

       

      My question is, if anybody has any experience with this. Since the foreign key indexes need much memory (in our case much more than the table itself), we would rather avoid them if possible.

       

      Thanks,

      Thomas

        • 1. Re: Foreign Keys and performance
          Chrisjenkins-Oracle

          Well, that statement is not completely correct. it is trying to say that there is no *negative* impact on SELECT queries due to the *presence* of foreign keys compares to the potential negative impact they have, by virtue of begin additional indexes, on INSERT, DELETE and maybe UPDATE operations.

           

          However, many applications query and join tables using the columns that make up the foreign keys. If this is true in your case then dropping those constraints will have a huge negative impact on query performance.

           

          I would be surprised if you can drop all these constraints without seeing a significant (possibly huge) reduction in query performance. But you will need to look at your queries, and their associated plans, to determine this.

           

          Having said all that, I am very surprised to hear that the indexes require more memory than the table. Do you have a huge number of FKs on each table? How are you measuring the memory usage? What  type of indexes are they? What version of TimesTen is this? Can you provide a specific example?

           

          Thanks,

           

          Chris

          1 person found this helpful
          • 2. Re: Foreign Keys and performance
            tkandl

            Hi Chris,

             

            thanks for the clarification.

             

            For the FKs:

            Version: TimesTen Release 11.2.2.5.1

            The table has only 6 FKs.

            I have measured the memory usage by looking at PERM_IN_USE_SIZE of table SYS.MONITOR for the total amount of used memory and with ttComputeTabSizes for the table size. Unfortunately I have not found a way to obtain the index size. Is there one?

            The table size of my small sample table is 23MB. When I apply the FKs the total amount of memory increases by 28MB. The ttsize output is:

            rows = 289611

             

            Total in-line row bytes = 12836238

             

            Compressed columns:

              Column DIMKENN_DIMKENNPK               total   157240    columnar compression   3.15x

              Column DIMFA_DIMFAPK                   total    17720    columnar compression   1.88x

              Column DIMSTF_DIMSTFPK                 total   864328    columnar compression   0.80x

              Column DIMZEIT_DIMZEITPK               total    17720    columnar compression   1.88x

              Column DIMLIEF_DIMLIEFPK               total    17720    columnar compression   1.88x

              Column UNTERSACHBEREICH                total    17720    columnar compression   1.88x

              Column WERT                            total  9593192    columnar compression   3.30x

              Column WERT_DATUM                      total   309064    columnar compression   2.28x

              Column WERT_ZAHL                       total  2751912    columnar compression   0.96x

              Total compressed column bytes = 13746616

              Total table compression       =    2.01x

             

            Indexes:

              Range index STAS.FCTSKW_DIMFA_FK adds 5886954 bytes

              Range index STAS.FCTSKW_DIMKENN_FK adds 5886954 bytes

              Range index STAS.FCTSKW_DIMLIEF_FK adds 5886954 bytes

              Range index STAS.FCTSKW_DIMSTF_FK adds 5886954 bytes

              Range index STAS.FCTSKW_DIMUFA_FK adds 5886954 bytes

              Range index STAS.FCTSKW_DIMZEIT_FK adds 5886954 bytes

              Total index bytes = 35321724

             

            Total = 61904578

             

            When I apply Bitmap-Indexes it gets even worse. The total amount of memory increases by almost 60MB. The ttsize output is:

            Rows = 289611

             

            Total in-line row bytes = 12836238

             

            Compressed columns:

              Column DIMKENN_DIMKENNPK               total   157240    columnar compression   3.15x

              Column DIMFA_DIMFAPK                   total    17720    columnar compression   1.88x

              Column DIMSTF_DIMSTFPK                 total   864328    columnar compression   0.80x

              Column DIMZEIT_DIMZEITPK               total    17720    columnar compression   1.88x

              Column DIMLIEF_DIMLIEFPK               total    17720    columnar compression   1.88x

              Column UNTERSACHBEREICH                total    17720    columnar compression   1.88x

              Column WERT                            total  9593192    columnar compression   3.30x

              Column WERT_DATUM                      total   309064    columnar compression   2.28x

              Column WERT_ZAHL                       total  2751912    columnar compression   0.96x

              Total compressed column bytes = 13746616

              Total table compression       =    2.01x

             

            Indexes:

              Bitmap index STAS.FCTSKW_B1 adds 239066 bytes

              Bitmap index STAS.FCTSKW_B2 adds 156650 bytes

              Bitmap index STAS.FCTSKW_B3 adds 6258538 bytes

              Bitmap index STAS.FCTSKW_B4 adds 46690970 bytes

              Bitmap index STAS.FCTSKW_B5 adds 151370 bytes

              Total index bytes = 53496594

             

            Total = 80079448

            • 3. Re: Foreign Keys and performance
              Chrisjenkins-Oracle

              Can you please:

               

              1.    Post your sys.odbc.ini settings for this database.

               

              2.    Confirm of this is an Exalytics system

               

              3.    If possible, post the TimesTen schema for one of the example tables (including indexes, PKs and FKs)

               

              Thanks,

               

              Chris

              • 4. Re: Foreign Keys and performance
                tkandl

                1.

                Driver=/u01/app/oracle/product/TimesTen/tt1122/lib/libtten.so

                DataStore=/u01/app/oracle/product/TimesTen/tt1122/databases/tt_stas

                LogDir=/u01/app/oracle/product/TimesTen/tt1122/databases/logs

                ConnectionCharacterSet=AL32UTF8

                LogFileSize=1024

                LogBufMB=1024

                LogBufParallelism=16

                Preallocate=0

                PermSize=100000

                TempSize=1000

                MemoryLock=4

                CkptFrequency=30

                CkptLogVolume=0

                CkptRate=20

                PrivateCommands=1

                RecoveryThreads=40

                DatabaseCharacterSet=AL32UTF8

                 

                2. Yes it is an Exalytics system

                 

                3. Do you mean the create statements:

                CREATE TABLE STAS.FCTSKW

                (

                    DIMKENN_DIMKENNPK                TT_BIGINT NOT NULL,

                    DIMFA_DIMFAPK                    TT_SMALLINT NOT NULL,

                    DIMUFA_DIMUFAPK                  TT_TINYINT NOT NULL,

                    DIMSTF_DIMSTFPK                  TT_INTEGER NOT NULL,

                    DIMZEIT_DIMZEITPK                TT_SMALLINT NOT NULL,

                    DIMLIEF_DIMLIEFPK                TT_SMALLINT NOT NULL,

                    UNTERSACHBEREICH                 TT_SMALLINT NOT NULL,

                    WERT                             VARCHAR2(27 CHAR) INLINE,

                    WERT_DATUM                       DATE,

                    WERT_ZAHL                        NUMBER(14,5)

                )

                COMPRESS

                (

                    DIMKENN_DIMKENNPK                BY DICTIONARY MAXVALUES = 65535,

                    DIMFA_DIMFAPK                    BY DICTIONARY MAXVALUES = 255,

                    DIMSTF_DIMSTFPK                  BY DICTIONARY MAXVALUES = 65535,

                    DIMZEIT_DIMZEITPK                BY DICTIONARY MAXVALUES = 255,

                    DIMLIEF_DIMLIEFPK                BY DICTIONARY MAXVALUES = 255,

                    UNTERSACHBEREICH                 BY DICTIONARY MAXVALUES = 255,

                    WERT                             BY DICTIONARY MAXVALUES = 65535,

                    WERT_DATUM                       BY DICTIONARY MAXVALUES = 65535,

                    WERT_ZAHL                        BY DICTIONARY MAXVALUES = 65535

                )

                OPTIMIZED FOR READ;

                 

                FKs:

                ALTER TABLE STAS.FCTSKW ADD CONSTRAINT FCTSKW_DIMUFA_FK FOREIGN KEY ( DIMUFA_DIMUFAPK ) REFERENCES STAS.DIMUFA ( DIMUFAPK );

                ALTER TABLE STAS.FCTSKW ADD CONSTRAINT FCTSKW_DIMFA_FK FOREIGN KEY ( DIMFA_DIMFAPK ) REFERENCES STAS.DIMFA ( DIMFAPK );

                ALTER TABLE STAS.FCTSKW ADD CONSTRAINT FCTSKW_DIMZEIT_FK FOREIGN KEY ( DIMZEIT_DIMZEITPK ) REFERENCES STAS.DIMZEIT ( DIMZEITPK );

                ALTER TABLE STAS.FCTSKW ADD CONSTRAINT FCTSKW_DIMSTF_FK FOREIGN KEY ( DIMSTF_DIMSTFPK ) REFERENCES STAS.DIMSTF ( DIMSTFPK );       

                ALTER TABLE STAS.FCTSKW ADD CONSTRAINT FCTSKW_DIMKENN_FK FOREIGN KEY ( DIMKENN_DIMKENNPK ) REFERENCES STAS.DIMKENN ( DIMKENNPK );       

                ALTER TABLE STAS.FCTSKW ADD CONSTRAINT FCTSKW_DIMLIEF_FK FOREIGN KEY ( DIMLIEF_DIMLIEFPK ) REFERENCES STAS.DIMLIEF ( DIMLIEFPK );

                 

                Bitmap:

                CREATE BITMAP INDEX STAS.FCTSKW_B1 ON STAS.FCTSKW ( DIMFA_DIMFAPK );   

                CREATE BITMAP INDEX STAS.FCTSKW_B2 ON STAS.FCTSKW ( DIMUFA_DIMUFAPK );

                CREATE BITMAP INDEX STAS.FCTSKW_B3 ON STAS.FCTSKW ( DIMKENN_DIMKENNPK );

                CREATE BITMAP INDEX STAS.FCTSKW_B4 ON STAS.FCTSKW ( DIMSTF_DIMSTFPK );

                CREATE BITMAP INDEX STAS.FCTSKW_B5 ON STAS.FCTSKW ( DIMLIEF_DIMLIEFPK );   

                 

                No PKs on this table.

                • 5. Re: Foreign Keys and performance
                  Chrisjenkins-Oracle

                  Sorry for the delay in replying, I was looking into this. These sizings seem accurate. In my tests, the table, with *uncompressed* data, requires around 53,241 KB while the six indexes combined require around 32,527 KB. This is quite reasonable. Obviously if the data is compressed then the data will occupy less space (the degree of compression depends on the actual data so I was not able to exactly simulate your situation) but this has no effect on the indexes since they do not store any data, just pointers.

                   

                  TimesTen in memory indexes are actually very compact compared to indexes in a disk centric database so I think maybe your expectations with regard to index size are perhaps unreasonable, sorry. You can of course omit any indexes (FKs) that are not needed to support queries but if you omit/drop indexes that are needed by queries then of course query performance will suffer (a lot).

                   

                  Please note that we do *not* recommend the use of bitmap indexes for BI/DW type queries in TimesTen. You should use normal range indexes instead; they will perform much better.

                   

                  Regards,

                   

                  Chris

                  1 person found this helpful
                  • 6. Re: Foreign Keys and performance
                    tkandl

                    Thanks again for the clarification.

                    Maybe Oracle should consider an update on the topic in the performance tuning chapter.

                     

                    Regards,

                    Thomas