Solved

SQL Optimzer for oracle

Posted on 2011-09-08
21
303 Views
Last Modified: 2012-05-12
Hello All,

I have this complex query which takes lot of time, I need to optimize it, need optimizer tools.

Any help is helpful.

Thank you.
0
Comment
Question by:dojjol
  • 8
  • 8
  • 4
  • +1
21 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 400 total points
Comment Utility
what is your query?
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 100 total points
Comment Utility
Best to start out is the execution plan:

Explain plan for
Select ...

Then to see it:
Select * from table(dbms_xplan.display);
0
 

Author Comment

by:dojjol
Comment Utility
select count(*) from (
             SELECT /*+ materialize */ aid FROM allelem ae WHERE ae.elemid = 102 AND LOWER(entryval) LIKE LOWER('go%')
              INTERSECT
             SELECT /*+ materialize */ aid FROM allelem ae WHERE ae.elemid = 147 AND LOWER(entryval) LIKE LOWER('USA')
              INTERSECT
             SELECT /*+ materialize */ aid FROM allelem ae WHERE ae.elemid = 146 AND LOWER(entryval) LIKE LOWER('ohio')
              UNION
             SELECT /*+ materialize */ aid FROM allelem ae WHERE ae.elemid = 150 AND LOWER(entryval) LIKE LOWER('to%')
              UNION
             SELECT /*+ materialize */ aid FROM allelem ae WHERE ae.elemid = 170 AND LOWER(entryval) LIKE LOWER('monday'))
0
 
LVL 1

Expert Comment

by:sivaprakasam
Comment Utility
one lower function is enough and also why r u using intersect  ...if u Just need the count try the below query ...
select count(*) from (
             SELECT  aid FROM sample1 ae WHERE ae.elemid = 102 AND LOWER(entryval) LIKE 'go%'
              union all
            SELECT  aid FROM sample1 ae WHERE ae.elemid = 147 AND LOWER(entryval) LIKE 'usa'
              union all
             SELECT  aid FROM sample1 ae WHERE ae.elemid = 146 AND LOWER(entryval) LIKE 'ohio'
              UNION all
             SELECT  aid FROM sample1 ae WHERE ae.elemid = 150 AND LOWER(entryval) LIKE 'to'
              UNION all
             SELECT  aid FROM sample1 ae WHERE ae.elemid = 170 AND LOWER(entryval) LIKE 'monday')

if u r not expecting this pls give me more details...  or some other expert can answer ...
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 100 total points
Comment Utility
Please post the explain plan.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
since your conditions are guaranteed to be mutually exclusive you can simply OR them together
that way you only access the table once

also, LIKE clauses without wildcards are simply equalities
try this...

SELECT COUNT(*)
  FROM allelem
 WHERE (elemid = 102 AND LOWER(entryval) LIKE LOWER('go%'))
    OR  (elemid = 147 AND LOWER(entryval) = 'USA')
    OR  (elemid = 146 AND LOWER(entryval) = 'ohio')
    OR  (elemid = 150 AND LOWER(entryval) LIKE LOWER('to%'))
    OR  (elemid = 170 AND LOWER(entryval) = 'monday');
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
if the optimizer isn't smart enough to pick out the elemid's for use with indexes then give it a little help

SELECT COUNT(*)
  FROM allelem
 WHERE  elemid in (102,147,146,150,170)
   AND ( (elemid = 102 AND LOWER(entryval) LIKE LOWER('go%'))
    OR  (elemid = 147 AND LOWER(entryval) = 'usa')
    OR  (elemid = 146 AND LOWER(entryval) = 'ohio')
    OR  (elemid = 150 AND LOWER(entryval) LIKE LOWER('to%'))
    OR  (elemid = 170 AND LOWER(entryval) = 'monday')
          )
0
 

Author Comment

by:dojjol
Comment Utility
Attached is the plan, exported to csv
export.csv
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
what is that?

please post output of dbms_xplan as shown above  (no csv,  just plain text)


also,  did you try http:#36507511  if so, what were the results.

and, for the plan you post include the exact query you explained
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>Attached is the plan, exported to csv

That looks like the plan for the original.

I would look at the SQL provided by sdstuber.  It should run a lot better.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:dojjol
Comment Utility
Thanks , new query runs awesome. I think I m gonna use this.

But , all the inner queries are not mutullay exclusive, they return common aid (s),
so in case of OR, it works fine, but how to get the count when we have common aid - I can use distinct.

But if you see my original post, I am also using intersect, just to get common set of aid required , if user selects "AND" operation in my application.

with the new query, how to put one more check to get common aid list , when user selects "AND" in place of "OR"

-D
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
How does the app work?

If you let them just select/group a lot of terms how were you planning on handling:

'a' AND 'b' OR 'c' AND 'd' OR 'e'

You need to build the proper 'grouping' using either approach.
0
 

Author Comment

by:dojjol
Comment Utility
My Bad

by aid(s) ,  I mean the id in the table.

SELECT COUNT(aid)
  FROM allelem
 WHERE  elemid in (102,147,146,150,170)
   AND ( (elemid = 102 AND LOWER(entryval) LIKE LOWER('go%'))
    OR  (elemid = 147 AND LOWER(entryval) = 'usa')
    OR  (elemid = 146 AND LOWER(entryval) = 'ohio')
    OR  (elemid = 150 AND LOWER(entryval) LIKE LOWER('to%'))
    OR  (elemid = 170 AND LOWER(entryval) = 'monday')
          )
0
 

Author Comment

by:dojjol
Comment Utility
the app is built on terms depending on user selection like

we have three terms

A = operation = 102
B  = country  = 147
C = state = 146 etc,

so user can either choose 1 "OR" and 1 "AND"  or may or both "ORs" or both "ANDs" and allelem is a denormalized table with primary key as aid and elemid.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
oops, sorry, I completely missed the INTERSECTs in the original query, I read them all as UNIONs hence my OR version.

sorry about that
0
 

Author Comment

by:dojjol
Comment Utility
no problem...

do you think we can still optimize the problem to get the count
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
sure, it gets kind of wierd though


SELECT COUNT(DISTINCT aid)
  FROM (SELECT elemid,
               aid,
               COUNT(DISTINCT CASE WHEN elemid IN (102, 147, 146) THEN elemid END)
                   OVER (PARTITION BY aid)
                   and_cnt
          FROM allelem
         WHERE elemid IN (102, 147, 146, 150, 170)
               AND (   (elemid = 102 AND LOWER(entryval) LIKE LOWER('go%'))
                    OR (elemid = 147 AND LOWER(entryval) = 'usa')
                    OR (elemid = 146 AND LOWER(entryval) = 'ohio')
                    OR (elemid = 150 AND LOWER(entryval) LIKE LOWER('to%'))
                    OR (elemid = 170 AND LOWER(entryval) = 'monday')))
 WHERE elemid NOT IN (102, 147, 146) OR and_cnt = 3


(102,147,146) is your list of INTERSECT/AND criteria

3 is number elements in that list

note, the inner OR list doesn't change,  the analytic and final outer-where do the real work


I have not tested this.   If it doesn't work for you,
 you can provide some sample data and I'll work out the kinks
0
 

Author Comment

by:dojjol
Comment Utility
thank stuber, but it is slow too.

My idea for the query is basically to get the aids from allelem tables for the searched condition and as well as count, although to get list aid i created a query (which is also not fast) but saves me to the fact that for each page i only need to to display 50 records, so i select 50 records from each conditon from all elem.

but to display the count or page numbers I need to get the exact count.

Sample data:

User wants to see the details of operations has been done in USA or state ohio  with operation states with go%

User can select country USA and state ohio in "OR" assuming ohio exists in two countries.

so condtion becomes

operation has elemid = 102 and entryval like go%
country has elemid = 147 and entryval like USA
state has elemid = 146 and entryval like 'ohio'.

Please let me know, if this is useful.


Operation
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
are you saying my last query is wrong and slow? or just slow?


by sample data,  I mean  actual data from your table, or at least representative

rows/columns something I can use to build test cases against.

also, what indexes do you have on your table?
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
also,  what are the explain plans (dbms_xplan.display)  for the original vs the latest ?
0
 

Author Closing Comment

by:dojjol
Comment Utility
thank you sdstuber, it was really helpful
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
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 information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

744 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

9 Experts available now in Live!

Get 1:1 Help Now