Oracle table optimization
Posted on 2014-03-31
Im working with a table, that is the center of this application built by a co-worker. The first column is Lookup_Key and second is Lookup_Code. The values in Lookup_Key are strings that represent the kind of record being saved. For instance, we have a portion of the app called Events and if you save an event the key he saves is 'CALENDAR_EVENTS'. The Lookup_Code is typically the users Dept || DateTime || Username. Example(CORP_2013_09_19_16_23_35_CISSRXP).
Now, I am tasked with trying speed up the execution of a query that takes a long time to run. The way the indexes are set up right now, to me it looks like its just a Constraint but its also a PK and the 2 columns are used as an index. I guess he put the 2 columns together because the Key column isnt unique on its own? Im trying to figure out the best way to index this table in order to get the best performance out of it? I attached a picture of the Index tab from TOAD.