SQL Optimzer for oracle

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'))

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 ...

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');

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')
          )

Attached is the plan, exported to csv
export.csv

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

>>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.

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

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.

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')
          )

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.

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

sorry about that

no problem...

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

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

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

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?

also,  what are the explain plans (dbms_xplan.display)  for the original vs the latest ?

thank you sdstuber, it was really helpful