| Tunig of query [message #219833] |
Fri, 16 February 2007 04:46  |
jaydba
Messages: 86
Registered: September 2006
Location: UK
|
Member |
|
|
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
Alternatively,
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  |
nmacdannald
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 =>
'/app/oracle/arch/oradba/log_1_0000000027.oradba',
Options => dbms_logmnr.NEW);
exec dbms_logmnr.add_logfile(
LogFileName =>
'/app/oracle/arch/oradba/log_1_0000000028.oradba',
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;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
