Home » SQL & PL/SQL » SQL & PL/SQL » SQL Query and OR Operation (Oracle 10g)
SQL Query and OR Operation [message #441980] Thu, 04 February 2010 03:53 Go to next message
arijit77
Messages: 6
Registered: February 2010
Location: Gurgaon
Junior Member
Hi Friends,

I have extended a query from Level A to Level B but performance has drastically fall down due to this OR clauses.

Level A:
--This query is taking 15 mins to execute

Select <List of Columns> from [Table A],[Table B],[Table C]
Where <different join conditions>
AND [Table A].Col1 in ('XXX','YYY','ZZZ')
AND [Table B].Col5 = 'USA'

Level B:
--This query is taking 2 Hours to execute

Select <List of Columns> from
[Table A] M,[Table A] N,[Table A] O,
[Table B] P,[Table B] Q,[Table B] R,
[Table C]
Where <different join conditions>
AND
(
(
M.Col1 in ('XXX','YYY','ZZZ')
AND P.Col5 = 'USA'
)
OR
(
N.Col1 in ('XXX','YYY','ZZZ')
AND Q.Col5 = 'USA'
)
OR
(
O.Col1 in ('XXX','YYY','ZZZ')
AND R.Col5 = 'USA'
)
)

Indexes are created on [Table A].Col1 and [Table B].Col5.
Please provide some suggestion on this issue.

Thanks in advance.

Regards
Arijit
Re: SQL Query and OR Operation [message #441984 is a reply to message #441980] Thu, 04 February 2010 04:03 Go to previous messageGo to next message
tejasvi_ss
Messages: 22
Registered: February 2010
Location: Bangalore, India
Junior Member
Hi,


In your question does column containing values XXX,YYY,ZZZ exists only in one table or all tables because your first query tells it exists only in one table but second query tells its in all tables.

Better you use ANSI std Joins than Theta joins where you can reduce time.
Follow as below if it exists in one table.
SELECT col1,... 
FROM table1 
JOIN table2 ON <condition>
JOIN table2 ON <condition>
...
WHERE table1.col1 in ('XXX','YYY','ZZZ')


else if it exists in all table then provide WHERE condition with OR

[Updated on: Thu, 04 February 2010 04:21]

Report message to a moderator

Re: SQL Query and OR Operation [message #441987 is a reply to message #441984] Thu, 04 February 2010 04:17 Go to previous messageGo to next message
arijit77
Messages: 6
Registered: February 2010
Location: Gurgaon
Junior Member
Hi,

Thanks for your reply. I am creating hierarchical level through these self joins. If I remove the data before joining then I will elimanate the necessary records also. Let me explain in more details.

Select P.Col5,Q.Col5,R.Col5 from
[Table A] M,[Table A] N,[Table A] O,
[Table B] P,[Table B] Q,[Table B] R,
[Table C]
Where <different join conditions>
AND
(
(
P.Col5 = 'USA'
)
OR
(
Q.Col5 = 'USA'
)
OR
(
R.Col5 = 'USA'
)
)

From the above query, I amy get records like this.

P.Col5 ~ Q.Col5 ~ P.Col5
USA ~ UK ~ Brazil
INDIA ~ USA ~ Korea
Hongkong ~ Singapore ~ USA
India ~ Russia ~ Germany --This record should be eliminated only.

But if I put the elimination first then join the tables. I will loose data. I think, it is making sense.

Thanks
Arijit
Re: SQL Query and OR Operation [message #441988 is a reply to message #441987] Thu, 04 February 2010 04:19 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel
Re: SQL Query and OR Operation [message #441995 is a reply to message #441988] Thu, 04 February 2010 05:34 Go to previous messageGo to next message
arijit77
Messages: 6
Registered: February 2010
Location: Gurgaon
Junior Member
Hi,

Thanks for your reply. I am creating hierarchical level through these self joins. If I remove the data before joining then I will elimanate the necessary records also. Let me explain in more details.
Select P.Col5,Q.Col5,R.Col5 from 
[Table A] M,[Table A] N,[Table A] O,
[Table B] P,[Table B] Q,[Table B] R,
[Table C]
Where <different join conditions>
AND 
(
(
P.Col5 = 'USA'
)
OR
(
Q.Col5 = 'USA'
)
OR
(
R.Col5 = 'USA'
)
)

From the above query, I amy get records like this.
P.Col5 ~ Q.Col5 ~ P.Col5
USA ~ UK ~ Brazil
INDIA ~ USA ~ Korea
Hongkong ~ Singapore ~ USA
India ~ Russia ~ Germany --This record should be eliminated only.

But if I put the elimination first then join the tables. I will loose data. I think, it is making sense.

Thanks
Arijit
Re: SQL Query and OR Operation [message #442003 is a reply to message #441995] Thu, 04 February 2010 06:00 Go to previous message
tejasvi_ss
Messages: 22
Registered: February 2010
Location: Bangalore, India
Junior Member
HI,

Try these structure of query, it will execute faster than previous.
SELECT col1,... 
FROM table1 
JOIN table2 ON <condition> AND table2.col1 in ('XXX','YYY','ZZZ')
JOIN table3 ON <condition> AND table3.col1 in ('XXX','YYY','ZZZ')
...


Remark : Eliminated WHERE clause from previous query. Hope it works for you

[Updated on: Thu, 04 February 2010 06:00]

Report message to a moderator

Previous Topic: Need to retrieve object elements
Next Topic: strange ora-00942 in a procedure.
Goto Forum:
  


Current Time: Wed Sep 28 04:04:59 CDT 2016

Total time taken to generate the page: 0.24640 seconds