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
anakin
Messages: 13
Registered: May 2009
Junior Member
Hi,
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
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
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.

SELECT FROM B
MINUS
SELECT FROM A

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
anakin
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: Find procedure name
Next Topic: Is there any alternative for LIST partition to capture the DEFAULT values? (merged)
Goto Forum:
  


Current Time: Fri Dec 09 04:23:56 CST 2016

Total time taken to generate the page: 0.06841 seconds