Solved

Oracle table optimization

Posted on 2014-03-31
12
617 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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
 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Read about achieving the basic levels of HRIS security in the workplace.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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 set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

734 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