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
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
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.
I need the following query
Code:
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;
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
Code:
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;
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.
Comment