Or versus UNION operator (Oracle)

building reports desktopintelligence
1

Hi All,

I have a query which has or condition between three different columns in the same table. When I try running this query it results in a Full Table scan and never runs.

When I split this query into 3 separate queries and Unioned them, it worked very fast.

But I am not sure if the Union query retruns the same results that would have been returned by OR query.

Any help or insight into it?

Best Regards,
Mohan

cpmohanraj :australia: (BOB member since 2002-09-23)

2

UNION will be faster but OR really shudn’t be doing a FULL TABLE SCAN… Do you have Index on those 3 Columns… Did you see the Plan of the UNION Query??? Both should return same results as long as you don’t end up having Duplicate Rows which UNION will not take and show only one record in which case you need to do UNION ALL…

Sridharan :india: (BOB member since 2002-11-08)

3

Because a UNION usually requires a sort (to eliminate duplicate rows) it might be even slower assuming that your OR conditions can use an index (which does not seem to be the case in your scenario). UNION ALL does not require any sorts.

Andreas :de: (BOB member since 2002-06-20)

4

In our case the Union uses an index where as OR condition goes for a FULL table scan… But what is surprising is, the same query (with Or condition) was working few weeks back… :cry:

Regards,
Mohan :roll_eyes:

cpmohanraj :australia: (BOB member since 2002-09-23)

5

Again, ask your DBA to analyze why the query (with the OR conditions) is running all of a sudden way slower, maybe an index was dropped or statistics not updated.

Andreas :de: (BOB member since 2002-06-20)

6

Thanks Andreas… I will check with my DBA today and keep this post updated if I find a solution.

Regards,
Mohan

cpmohanraj :australia: (BOB member since 2002-09-23)