How do i Optimize this Query

[gkinu]

I have 2 tables with a parent-child relationship. Parent table's primary key field is Entry_no and is of type VarChar(50). This parent table has about 50 fields. The child has 3 fields, Entry_No (varchar(50), Requestor char(3) and id (integer).

I need the following query

QUERY 1. select p.entry_no, p.reg_date from tparent p, tchild c where p.entry_no<>c.entry_no and c.requestor='KPA' order by p.reg_date, p.entry_no;

The above query takes a very long time (more than 30 min)

If i remove the ORDER BY clause, it runs very fast (milliseconds). The problem is that the query must be ordered for it to be of any help to me.

I have tried other options like

QUERY 2. select p.entry_no, p.reg_date from tparent p where p.entry_no not in (select entry_no from tchild where c.requestor='KPA') order by p.reg_date, p.entry_no;

This also takes forever. In fact this one hangs even when i remove the ORDER BY clause.

The interesting thing is that if i use = instead of <> in query 1 and 'in' in place of 'not in' in query 2, the query runs in milliseconds range whether or not the 'ORDER BY' clause is present.

How do i customize this query to run fast?

MORE INFORMATION:
- Oracle 10G
- Parent table is indexed on entry_no and sreg_date. Has approx 350,000 records.
- Child is indexed on entry_no and requestor. Has approx 500,000 records.

[amitpatel66]

I have 2 tables with a parent-child relationship. Parent table's primary key field is Entry_no and is of type VarChar(50). This parent table has about 50 fields. The child has 3 fields, Entry_No (varchar(50), Requestor char(3) and id (integer).

I need the following query

QUERY 1. select p.entry_no, p.reg_date from tparent p, tchild c where p.entry_no<>c.entry_no and c.requestor='KPA' order by p.reg_date, p.entry_no;

The above query takes a very long time (more than 30 min)

If i remove the ORDER BY clause, it runs very fast (milliseconds). The problem is that the query must be ordered for it to be of any help to me.

I have tried other options like

QUERY 2. select p.entry_no, p.reg_date from tparent p where p.entry_no not in (select entry_no from tchild where c.requestor='KPA') order by p.reg_date, p.entry_no;

This also takes forever. In fact this one hangs even when i remove the ORDER BY clause.

The interesting thing is that if i use = instead of <> in query 1 and 'in' in place of 'not in' in query 2, the query runs in milliseconds range whether or not the 'ORDER BY' clause is present.

How do i customize this query to run fast?

MORE INFORMATION:
- Oracle 10G
- Parent table is indexed on entry_no and sreg_date. Has approx 350,000 records.
- Child is indexed on entry_no and requestor. Has approx 500,000 records.

Try This:

 
select p.entry_no, p.reg_date from tparent p where EXISTS (SELECT 'X' FROM tchild WHERE entry_no <> p.entry_no and requestor='KPA') order by p.reg_date, p.entry_no