Oracle table optimization

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.
Any thoughts?
jknj72Asked:
Who is Participating?
 
Alexander Eßer [Alex140181]Connect With a Mentor Software DeveloperCommented:
scary huh!!
yes ;-)

I For dropping that index, I suppose you have to get rid of the PK constraint... Do you rely on this PK index?!
0
 
jknj72Author Commented:
pic is attached
Indexes.jpg
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
How is the table being accessed apart via these 2 indexed fields?!
Using these 2 columns (with up-to-date statistics & histograms) everything should be fine...
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Alexander Eßer [Alex140181]Software DeveloperCommented:
If it helps, I also did an Explain Plan if anyone would like to see the results
please provide as much information as possible ;-)
0
 
jknj72Author Commented:
If it helps, I also did an Explain Plan if anyone would like to see the results
0
 
jknj72Author Commented:
"How is the table being accessed apart via these 2 indexed fields?!
Using these 2 columns (with up-to-date statistics & histograms) everything should be fine... "

Its being used this way throughout the app. I explained the Event key but there are many different parts to this app and it would simply have another key assigned to it and it would be used the same way. The columns are completely generic and he fits certain values into these columns and dependant on which key is dependant on values that are stored.

I have attached a portion of the Explain Plan that I ran that I am trying to optimize
Explain-Plan.jpg
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
could you post the corresponding SQL statement for the explain plan please?!
0
 
jknj72Author Commented:
This is a view I did the Explain Plan on.....

SELECT o.LOOKUP_KEY as EVENTS_KEY, o.LOOKUP_CODE as EVENTS_CODE, o.LOOKUP_VALUE_TEXT as EVENTS_HEADLINE,
o.LOOKUP_SUB_TYPE_CODE as EVENTS_SUB_TYPE, o.LOOKUP_XREF as EVENTS_LINK_ID, o.LOOKUP_TAG as EVENTS_DETAILS,
o.LOOKUP_TAG as EVENTS_SUMMARY, o1.NEWS_SUMMARY, o1.NEWS_HEADLINE, l.LINK_ID as NEWS_LINK_ID, l.LINK_URL as NEWS_LINK_URL,
l.LINK_TYPE as NEWS_LINK_TYPE,
(CASE WHEN (o.LOOKUP_MISC_CODE) IN (SELECT LOOKUP_CODE FROM OMNI_COMMON_LOOKUP WHERE LOOKUP_KEY='PAGE_OWNER_MINIMUM') THEN o.LOOKUP_MISC_CODE ELSE 'CORP' END) as DEPARTMENT,
(SELECT a.LOOKUP_VALUE_TEXT FROM OMNI_COMMON_LOOKUP a INNER JOIN OMNI_COMMON_LOOKUP b on a.Lookup_Code = B.LOOKUP_SUB_TYPE_CODE
WHERE B.LOOKUP_CODE= o.LOOKUP_SUB_TYPE_CODE And a.LOOKUP_KEY = 'ALERT_TYPES_EVENTS')as REALM,
(SELECT LOOKUP_VALUE_TEXT FROM OMNI_COMMON_LOOKUP WHERE Lookup_Code = o.LOOKUP_SUB_TYPE_CODE AND LOOKUP_KEY = 'ALERT_ITEMS_EVENTS') as Classification,
(SELECT LOOKUP_VALUE_TEXT FROM OMNI_COMMON_LOOKUP WHERE Lookup_Misc_Code = o.LOOKUP_SUB_TYPE_CODE AND LOOKUP_KEY = 'CALENDAR_TYPE_CATEGORY') as EVENTS_CATEGORY,
TO_CHAR(o.LOOKUP_EFF_DATE, 'MM/DD/YYYY') EVENTS_START_DATE, TO_CHAR(o.LOOKUP_EXP_DATE, 'MM/DD/YYYY') EVENTS_END_DATE,  
CASE WHEN LOOKUP_VALUE_NUMBER = '1' THEN '1' ELSE '0' END AS EVENTS_VALUE_NUMBER,

FUNC_OMNI_HAS_NEWS(o.LOOKUP_CODE) as HASNEWS,
FUNC_OMNI_NEWS_BANNER(o.LOOKUP_CODE) as HASBANNER, FUNC_OMNI_NEWS_SLIDESHOW(o.LOOKUP_CODE) as HASSLIDESHOW,
FUNC_OMNI_PAGE_TITLE(FUNC_OMNI_PAGE_FROM_LINK(o.LOOKUP_XREF)) as PAGE_TITLE,
FUNC_OMNI_PAGE_FROM_LINK(o.LOOKUP_XREF) as PAGE_ID

FROM OMNI_COMMON_LOOKUP o
LEFT JOIN VW_OMNI_ENT_NEWS_GRID o1 ON o.LOOKUP_CODE = o1.NEWS_CODE
LEFT JOIN OMNI_ENT_LINK l ON o1.LINK_ID = l.LINK_ID
WHERE o.LOOKUP_KEY = 'CALENDAR_EVENTS'
Order by o.LOOKUP_EFF_DATE DESC;
0
 
jknj72Author Commented:
scary huh!! I am going to re-write the CASE WHEN statements and just do a couple of joins but Id really like to index this table properly. I tried to drop the index thats currently on the Key and Code and try and create a clustered index on the Key but Im unable to do this. Not sure if its a permissions thing or not but I get this error
"ORA-02429: cannot drop index used for enforcement of unique/primary key"

Any suggestions of how I can optimize this view and or table would really help me out. Thanks
0
 
jknj72Author Commented:
I just think the row needs to be unique with the Key and Code combo. That was the premise behind what he wanted to do. I can do whatever I need to do to make this perform better so if I have to redo the constraints and indexing thats what I need to do. What are your thoughts?
0
 
DavidConnect With a Mentor Senior Oracle Database AdministratorCommented:
Confirming Alexander,
ALTER TABLE xx DISABLE PRIMARY KEY CONSTRAINT -- exact syntax may vary
/
then drop the index as planned.

As always, provide the RDBMS version going forward, if you please.

Now, the compound key you provided has the three fields -- suggesting if sorted in ascending order, for a given DEPT, and at a given TIME DOWN TO THE SECOND, then order the EMPL.  The entity relationship is for any DEPT, you may have zero to many EMPL.  But your approach is, for any DEPT, there are zero to many TIME, and for any given TIME there are zero to many EMPL.

I respectfully suggest the TIME is misplaced and/or not relevant.

While you're working that one out, I also observe your default block size is only 256k.  So, you're storing approximately ten rows per block.  Disk I/O is deadly expensive, agreed?  So next time, you may be amazed to retrieve a hundred, or even a thousand rows per I/O fetch.....

SQL has a default session environment variable of ARRAYSIZE, having a fetch value of 15.  Bump that puppy up to about 500 and rerun your query.  Wow.

Advanced options to be discussed could include adding PARALLEL streams to your statement; partitioning the table to omit historical records; using the SQL tuning packages built into Oracle.  

For the time being, your relational modeling is hosed and is your priority.
0
 
jknj72Author Commented:
thank you both..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.