Solved

Oracle table optimization

Posted on 2014-03-31
12
618 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

691 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