Home » SQL & PL/SQL » SQL & PL/SQL » Tuning a query (Oracle 10g)
Tuning a query [message #412138] Tue, 07 July 2009 23:31 Go to next message
Messages: 13
Registered: May 2009
Junior Member
I have a query that is comparing two tables having near about same name and type of columns. For example: Assume there are two tables A (No. of columns 52) and B (No.of cols 48 ). B has same name and types of colums as the A has.
Table A contains 400000 records and Table B contains 100.
I have to check that the table B's records are there in Table A or not.
So in 'WHERE' clause I am checking all column values of A with all column values of B. Note: Table A has a primary key whereas table B dosen't. Tha data in the table B is getting populated through the feed files. It comes without any primary key.

But the query is taking 10 minutes for execution and in EXPLAIN plan optimizer is going for FULL table scan.

My question is should I impose the indexes on all the columns of table A ?
Re: Tuning a query [message #412140 is a reply to message #412138] Tue, 07 July 2009 23:37 Go to previous messageGo to next message
Messages: 25570
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.


What gets returned are the differences.
Re: Tuning a query [message #412152 is a reply to message #412140] Wed, 08 July 2009 00:46 Go to previous message
Messages: 13
Registered: May 2009
Junior Member
Thanks for your reply.
But I have solved the problem imposing the composite index on the main columns.
Now query is taking less than 0 seconds.
Previous Topic: Compilation error PLS-00410: duplicate fields in RECORD,TABLE or argument
Next Topic: Need Opinion on the Better Approach
Goto Forum:

Current Time: Sat Aug 19 02:32:11 CDT 2017

Total time taken to generate the page: 0.04867 seconds