| Please tune this query [message #474050] |
Thu, 02 September 2010 06:57  |
shabir46
Messages: 41
Registered: November 2009
|
Member |
|
|
Hi I have 2 tables as shown below and data is like this.
Position
COD IND
AAA N
BBB N
CCC N
DDD Y
Distance
orig dest
AAA BBB
BBB CCC
AAA CCC
I need to create the records like this
start end
DDD AAA
DDD BBB
DDD CCC
The query which i am using now for this is
select p.code AS start,
P1.CODE AS end
from position p, position p1
where
P.CODE != P1.CODE
AND (P.ind = 'Y' or P1.IND = 'Y')
AND not exists
(select 1
from distance d
where (d.orig = p.code or d.dest = p.code)
and (d.orig = p1.code or d.dest = p1.code))
But its doing a full table scan and table is having crore record
Please someone please help in tuning this query?
Thanks and regards,
Shabir Kaithayil
|
|
|
|
| Re: Please tune this query [message #474051 is a reply to message #474050] |
Thu, 02 September 2010 07:03  |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
"full scans are not always evil, indexes are not always good"
Need more information as to the actual problem.
edit: In fact, just more information. You say its doing an FTS, but dont include any index data?
Therefore I assume its doing FTS as there are no indexes, therefore no alternative.
[Updated on: Thu, 02 September 2010 07:04] Report message to a moderator |
|
|
|
| Re: Please tune this query [message #474052 is a reply to message #474050] |
Thu, 02 September 2010 07:08 |
shabir46
Messages: 41
Registered: November 2009
|
Member |
|
|
Sorry.. this is the query.
select p.code AS start,
P1.CODE AS end
from position p, position p1
where
P.CODE != P1.CODE
AND (P.ind = 'Y' or P1.IND = 'Y')
and p.ind = 'N'
AND p2.ind = 'N'
AND not exists
(select 1
from distance d
where (d.orig = p.code or d.dest = p.code)
and (d.orig = p1.code or d.dest = p1.code))
Selexts are on index columns. but still doing a FTS
|
|
|
|
|
|
|
|
| Re: Please tune this query [message #474055 is a reply to message #474054] |
Thu, 02 September 2010 07:19 |
shabir46
Messages: 41
Registered: November 2009
|
Member |
|
|
select p.code AS start,
P1.CODE AS end
from position p, position p1
where
P.CODE != P1.CODE
AND (P.ind = 'Y' or P1.IND = 'Y')
and p.ind = 'N'
AND p1.ind = 'N'
AND not exists
(select 1
from distance d
where (d.orig = p.code or d.dest = p.code)
and (d.orig = p1.code or d.dest = p1.code))
|
|
|
|
|
|
| Re: Please tune this query [message #474057 is a reply to message #474054] |
Thu, 02 September 2010 07:26 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
cookiemonster wrote on Thu, 02 September 2010 13:161) Please read the orafaq forum guide and follow it in future - especially the bit about formatting your posts.
2) Please read the following and supply the requested information.
We're going to need:
The correct query - that last one you put won't compile - there's no p2 in your from clause.
The explain plan.
Details of the indexes.
You appear to have done one of things I asked, now how about the rest?
|
|
|
|
| Re: Please tune this query [message #474058 is a reply to message #474056] |
Thu, 02 September 2010 07:27 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Roachcoach wrote on Thu, 02 September 2010 13:25AND (P.ind = 'Y' or P1.IND = 'Y')
and p.ind = 'N'
AND p1.ind = 'N'
Do I need coffee or are those predicates always going to return no rows?
No coffee needed. I can't see any way that can work either.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
