Query optimization using HINT

In this paper I would like to share the experience with an attribute Hint, we were forced to use to optimize queries. After the implementation of the dashboard with one unnamed client duration of all queries conducted at the required times (1-3 seconds).Approximately 2 months after deployment to production operation there was a sudden drop in performance of queries with a few tens of minutes. This was because the facts table monthly "reproduced" by about 10 million records. optimization, we decided to solve the deployment of metadata attributes hint at the physical table. After deployment, the performance of queries returned to the original desired time intervals (for some queries, the duration of the query even lower than after the start of production operation). To set a HINT everything works as it should: for example. syntax for using a particular index is: INDEX (TableName, název_indexu) Hint is given in tables in the physical layer of the metadata repository: But we had to deal with demand, which for a given query can specify different filters (conditions) on several key attributes - at least one of them.On any given main attribute was used in a database index on which we wanted to set the hint. The syntax used is more than hint at a time (again for the index) this (separated by a space): INDEX (TableName, název_indexu) INDEX (TableName, název_indexu) INDEX (TableName, název_indexu) Unfortunately the version used in the OBI EE (10.1.3.3.0), this can not write decipher. The solution is simple ("vypocené") for 2 and another hint to not use the name of a physical table, but the alias under which is listed in the resulting physical SQL. Fortunately, the alias invariant (apparently generated from the identifier under which the table is stored in the metadata). Example: INDEX (F_ATM, IDX_ATM_CARD) INDEX (T1391, IDX_ATM_TERM) INDEX (T1391, IDX_ATM_POSTDAT_AUDIT) Miroslav Petr (Consultant Adastra).