Home » RDBMS Server » Performance Tuning » Tunig of query
Tunig of query [message #219833] Fri, 16 February 2007 04:46 Go to next message
Messages: 86
Registered: September 2006
Location: UK

I have two tables with a common column. Both tables holds millions of rows and having UNIQUE index on common columns in both tables.

Both the column is having a type of varchar2 and width is 15 with a different name.

Both table holds the same number of records as an example 9484287.

I need to pull out records from one of the tables and to populate in another tables if it is having similar records in both tables on the basis of common key column.

I have written the statement using join as well as sub-query to perform this job.
But, It takes a lot of time even to display from the tables by joining two tables and using sub-query.

Can you suggest an alternate or best way to display and pull out data using any other mechanism or proper tuning query or script.

As a first step, I wanted to display and identify the data from both of the tables on the basis of common column.

select a.c1,b.ticketid from t16 a, j16 b where a.c1=b.ticketid


select b.ticeketid from j16 b where b.ticketid in (select a.c1 from t16 a where a.c1=b.ticketid)

I will extract all the records from the b tables and populate into c tables
after executing and optimizing performance of this query.

I will subsequently remove the common records from b to achieve my desired goal.

Your help would be highly appreciated.
Re: Tunig of query [message #220104 is a reply to message #219833] Sun, 18 February 2007 20:22 Go to previous message
Messages: 460
Registered: July 2005
Location: Stockton, California - US...
Senior Member
Try logminer

rem -----------------------------------------------------------------------
rem Filename: logmgr.sql
rem Purpose: Log Miner: extract undo statements from online and archived
rem redo log files based on selection criteria.
rem Date: 21-Sep-2000
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

-- Create a dictionary file
-- (init.ora parameter utl_file_dir must be set)
exec dbms_logmnr_d.build('mydictfile', '/tmp');

-- Register log files, can be from a different db
-- (NEWFILE=start new list/ ADDFILE=add next file)
exec dbms_logmnr.add_logfile(
LogFileName =>
Options => dbms_logmnr.NEW);
exec dbms_logmnr.add_logfile(
LogFileName =>
Options => dbms_logmnr.ADDFILE);

-- Start the logminer session
exec dbms_logmnr.start_logmnr(DictFileName => '/tmp/mydictfile');

-- Query v_$logmnr_contents view to extract required info
select timestamp, sql_undo
from sys.v_$logmnr_contents
where seg_name = 'EMPLOYEES';

-- Stop the logminer session
exec dbms_logmnr.end_logmnr;

Previous Topic: straight sql vs procedural ASAP
Next Topic: Data Buffer Cache and Dirty Blocks
Goto Forum:

Current Time: Mon Aug 21 14:55:55 CDT 2017

Total time taken to generate the page: 0.01672 seconds