Solved

Oracle table optimization

Posted on 2014-03-31
12
603 Views
Last Modified: 2014-04-07
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?
0
Comment
Question by:jknj72
  • 7
  • 4
12 Comments
 

Author Comment

by:jknj72
ID: 39967432
pic is attached
Indexes.jpg
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39967456
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
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39967458
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
 

Author Comment

by:jknj72
ID: 39967448
If it helps, I also did an Explain Plan if anyone would like to see the results
0
 

Author Comment

by:jknj72
ID: 39967521
"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
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39967543
could you post the corresponding SQL statement for the explain plan please?!
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:jknj72
ID: 39967586
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
 

Author Comment

by:jknj72
ID: 39969033
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
 
LVL 13

Accepted Solution

by:
Alexander Eßer [Alex140181] earned 250 total points
ID: 39969044
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
 

Author Comment

by:jknj72
ID: 39969381
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
 
LVL 23

Assisted Solution

by:David
David earned 250 total points
ID: 39977973
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
 

Author Closing Comment

by:jknj72
ID: 39983008
thank you both..
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article describes some very basic things about SQL Server filegroups.
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now