Solved

SQL Optimzer for oracle

Posted on 2011-09-08
21
306 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 74

Accepted Solution

by:
sdstuber earned 400 total points
ID: 36505189
what is your query?
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 100 total points
ID: 36505678
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
ID: 36505927
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
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.

 
LVL 1

Expert Comment

by:sivaprakasam
ID: 36507240
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 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 100 total points
ID: 36507257
Please post the explain plan.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36507508
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 74

Expert Comment

by:sdstuber
ID: 36507511
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
ID: 36510611
Attached is the plan, exported to csv
export.csv
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36510635
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 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36510637
>>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
 

Author Comment

by:dojjol
ID: 36512393
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 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36512411
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
ID: 36512419
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
ID: 36512458
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 74

Expert Comment

by:sdstuber
ID: 36512496
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
ID: 36512519
no problem...

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

Expert Comment

by:sdstuber
ID: 36512599
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
ID: 36512860
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 74

Expert Comment

by:sdstuber
ID: 36512922
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 74

Expert Comment

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

Author Closing Comment

by:dojjol
ID: 36567085
thank you sdstuber, it was really helpful
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

829 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