Oracle sql query rewrite

Question

I have to query data in the database, have the following table

COLUMNS:  Type, Location, name, etc.

DATA:
 1. Stores in NJ, name = xyz
 2. Restaurants in NY
 3. Hotels in US
 4. Stores in PA
 5. Restaurants in VA
 6. Hotels in MD
 7. Stores in NJ, name = abc
 8. etc.

I need a query, to fetch the data from 1, 2, 3

Right now, I have the following query. This runs faster. But is there any other query I can use without UNION

select type, location from table1 
where type='stores' and location='NJ' and name='XYZ'
  UNION
select type, location from table1 
where type='restaurants' and location='NY'
  UNION
select type, location from table1 
where type='hotels' and location='US'

Don't use union in a situation like this. It will try to do a distinct, which means that you're unnecessarily de-duplicating. — Ben
– Ben, Commented Jun 6, 2012 at 16:08

Tony Andrews · Accepted Answer · 2012-06-06 16:07:24Z

2

You can use OR:

select type, location from table1 
where (type='stores' and location='NJ' and name='XYZ')
or (type='restaurants' and location='NY')
or (type='hotels' and location='US');

answered Jun 6, 2012 at 16:07

Tony Andrews

133k23 gold badges230 silver badges266 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user12121 Over a year ago

thanks Andrew for your answer. I tried this, it is taking considerable time compared to my previous query. My table contains huge amounts of data.

Tony Andrews Over a year ago

Do your tables have accurate (enough) statistics?

aF. · Accepted Answer · 2012-06-06 16:16:11Z

1

UNION ALL is faster then using OR and prolly is what you really need:

select type, location from table1 
where type='stores' and location='NJ' and name='XYZ'
  UNION ALL
select type, location from table1 
where type='restaurants' and location='NY'
  UNION ALL
select type, location from table1 
where type='hotels' and location='US'

If you really want the OR:

select type, location from table1 
where (type='stores' and location='NJ' and name='XYZ')
or (type='restaurants' and location='NY')
or (type='hotels' and location='US');

To make your query faster, create indexes on type, location and name columns!

edited Jun 6, 2012 at 16:16

answered Jun 6, 2012 at 16:10

aF.

67k45 gold badges141 silver badges201 bronze badges

5 Comments

user12121 Over a year ago

ya, tried OR, but my query is very slow now. Is there anything I can use such as scalar subquery etc. If so, what will the syntax be for that.

aF. Over a year ago

To make it faster create indexes on type, location and name columns!

user12121 Over a year ago

Thanks aF. I created index as suggested by you. Still no luck.

Bob Jarvis - Слава Україні Over a year ago

@user12121 - it would help to know what indexes you have on TABLE1, how up-to-date the statistics are, how many rows you have in the table, how many rows exist for each of the conditions you're querying, the rewritten query you're using, and the plan Oracle generates for the rewritten query.

user12121 Over a year ago

Thanks everyone for your help. I looked at the query plan and I saw where the problem is coming from.

Collectives™ on Stack Overflow

Oracle sql query rewrite

2 Answers 2

2 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related