Home » RDBMS Server » Performance Tuning » create table statement with union
| create table statement with union [message #469563] |
Thu, 05 August 2010 03:11  |
prashant_ora
Messages: 196
Registered: July 2010
|
Senior Member |
|
|
Hi this statement is taking 1hr , can we reduce the timing?
CREATE TABLE DGT_ITEMEFFORTDATA (ENTERPRISEID, OWNERTYPE, OWNERID, SUPEROWNERTYPE, SUPEROWNERID,
ITEMTYPE, ITEMID, STAGEID, USERID, DATEIDENTIFIED,
DATECLOSED, ACTIVITYCODEID, PHASEID, RELEASEID, MONTHID,
QUARTERID, INITIALEFFORT, BASELINEDEFFORT,
ACTUALEFFORT, ITEMSTATUS, ALLOCATIONSTATUS, STAGESTATUS,
OCCURANCETYPE, DSLPROJECTTYPE, METRICCALCRUNID,
PLANNEDEFFORT,REMAININGEFFORT, ACTUAL_START_DATE, ACTUAL_END_DATE,
BASELINED_START_DATE, BASELINED_END_DATE,
CURBASE_START_DATE, CURBASE_END_DATE, PACKAGETYPE, PACKAGEID,
PRACTICEID) NOLOGGING
AS
SELECT ENTERPRISEID, OWNERTYPE, OWNERID, SUPEROWNERTYPE, SUPEROWNERID,
ITEMTYPE, ITEMID, STAGEID, USERID, DATEIDENTIFIED,
DATECLOSED, ACTIVITYCODEID, PHASEID, RELEASEID,
MONTHID, QUARTERID, INITIALEFFORT, BASELINEDEFFORT,
ACTUALEFFORT, ITEMSTATUS, ALLOCATIONSTATUS,
STAGESTATUS, OCCURANCETYPE, DSLPROJECTTYPE, METRICCALCRUNID,
PLANNEDEFFORT,REMAININGEFFORT, ACTUAL_START_DATE, ACTUAL_END_DATE,
BASELINED_START_DATE,
BASELINED_END_DATE,
CURBASE_START_DATE, CURBASE_END_DATE, PACKAGETYPE,
PACKAGEID, PRACTICEID
FROM DGT_ITEMEFFORTDATA_DAILY
UNION ALL
SELECT ENTERPRISEID, OWNERTYPE, OWNERID, SUPEROWNERTYPE,
SUPEROWNERID, ITEMTYPE, ITEMID, STAGEID, USERID, DATEIDENTIFIED,
DATECLOSED, ACTIVITYCODEID, PHASEID, RELEASEID, MONTHID,
QUARTERID, INITIALEFFORT, BASELINEDEFFORT,
ACTUALEFFORT, ITEMSTATUS, ALLOCATIONSTATUS, STAGESTATUS,
OCCURANCETYPE, DSLPROJECTTYPE, METRICCALCRUNID,
PLANNEDEFFORT,REMAININGEFFORT, ACTUAL_START_DATE, ACTUAL_END_DATE,
BASELINED_START_DATE, BASELINED_END_DATE,
CURBASE_START_DATE, CURBASE_END_DATE, PACKAGETYPE,
PACKAGEID, PRACTICEID FROM DGT_ITEMEFFORTDATA_TEMP
This is the explain plan of the above query
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%C
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11M| 4137M| 46149 (
| 1 | UNION-ALL | | | |
| 2 | TABLE ACCESS FULL| DGT_ITEMEFFORTDATA_DAILY | 3455K| 428M| 14575
| 3 | TABLE ACCESS FULL| DGT_ITEMEFFORTDATA_TEMP | 7656K| 3709M| 31574
--------------------------------------------------------------------------------
Note
-----
- 'PLAN_TABLE' is old version
This is the index details
1 DGT_ITEMEFFORTDATA_DAILY HCLT_IDX_DGT_IFD ITEMID 4
2 DGT_ITEMEFFORTDATA_DAILY HCLT_IDX_DGT_IFD ITEMTYPE 3
3 DGT_ITEMEFFORTDATA_DAILY HCLT_IDX_DGT_IFD OWNERID 2
4 DGT_ITEMEFFORTDATA_DAILY HCLT_IDX_DGT_IFD OWNERTYPE 1
There is no index on DGT_ITEMEFFORTDATA_TEMP table
Total no of records in DGT_ITEMEFFORTDATA_dialy:11176852
and DGT_ITEMEFFORTDATA_DAILY:3456786
and DGT_ITEMEFFORTDATA_TEMP :7720066
[Updated on: Thu, 05 August 2010 08:17] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: create table statement with union [message #469609 is a reply to message #469578] |
Thu, 05 August 2010 05:29  |
prashant_ora
Messages: 196
Registered: July 2010
|
Senior Member |
|
|
I got something like this
CREATE TABLE TEMP
PARALLEL 4 NOLOGGING
AS
SELECT /*+ PARALLEL (emp, 4) PARALLEL(detail,4) */
emp.emp_id emp_id
,deatil.house_id house_id
,detail.first_name first_name
,detail.last_name last_name
FROM
employee emp
,emp_details detail
WHERE
emp.emp_id = detail.emp_id;
bur one thing i am not able to understand what would be the degree in my case , like in above example it took 4.
This is my server configuration:
2 Intel Itanium 2 9100 series processors (1.67 GHz, 18 MB)
666 MT/s bus, CPU version A1
4 logical processors (2 per socket)
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: create table statement with union [message #469638 is a reply to message #469634] |
Thu, 05 August 2010 06:20 |
 |
Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Yes I want at least information on disks: number, number of controlers, number of disk per controlers, structure (RAID or not), repartition of the tablespace files on the disks...
But the best way is for you ask your sysadmin what the performances of your disks.
If you can't have this information, the last step is to test, test, test, you could test many different values in the time this topic lasted.
Regards
Michel
|
|
|
|
|
|
|
|
| Re: create table statement with union [message #469655 is a reply to message #469647] |
Thu, 05 August 2010 06:52 |
prashant_ora
Messages: 196
Registered: July 2010
|
Senior Member |
|
|
I got one thing to get the degree value..
Exec dbms_stats.gather_table_stats('my_user','my_tab', degree=>dbms_stats.auto_degree);
Now tell me whether it would be ok or not and if yes which column says me the value after gather the stats.
|
|
|
|
|
|
|
|
|
|
| Re: create table statement with union [message #469988 is a reply to message #469685] |
Fri, 06 August 2010 12:52 |
prashant_ora
Messages: 196
Registered: July 2010
|
Senior Member |
|
|
|
I tried with parallel option startted with degreee value 2 and incremented by 1 reached upto 10, but i did not get any time benifit, the normal and parallel option both are takig same time. So please advice me considering the query,whether i can optimise it without parallel option.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: create table statement with union [message #470005 is a reply to message #470000] |
Fri, 06 August 2010 13:50 |
|
Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Maybe you are a fresher but this does imply you can't think.
1/ Do you think if there was a way to rewrite it I spent several days to find other ways without FIRST speaking about it?
2/ Do you think that there can be something simpler/smaller/more optimize to write a query that is the following one?
select * from t1 union all select * from t2
Regards
Michel
|
|
|
|
Goto Forum:
Current Time: Sun May 05 12:47:21 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
)