Solved

SQL Optimzer for oracle

Posted on 2011-09-08
21
305 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
ID: 36505189
what is your query?
0
 
LVL 76

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 76

Assisted Solution

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

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 73

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 73

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 76

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 76

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 73

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 73

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 73

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 73

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

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.

Question has a verified solution.

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

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to take different types of Oracle backups using RMAN.

813 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

11 Experts available now in Live!

Get 1:1 Help Now