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  |
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   |
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   |
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 #441995 is a reply to message #441988] |
Thu, 04 February 2010 05:34   |
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  |
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
|
|
|
Goto Forum:
Current Time: Sun Feb 16 00:37:12 CST 2025
|