Performance Tuning :: Split Large Table To Small Pieces?

Mar 28, 2011

I have several large tables in the live system! Those table are store historical information.

current situation:

Now, A table record was 129 million rows.

Every month added 4.5M records to this table.

This table data size 17GB and index size 28GB.

I have only 30 GB available free space on disk!

How to split this table to small pieces (partition table by month)?

What is the best approach?

I would like to do partitioning on this table month by month.

View 12 Replies


ADVERTISEMENT

Performance Tuning :: Create Small Functional Indexes For Special Cases In Very Large Tables

Apr 5, 2012

Create small functional indexes for special cases in very large tables.

When there is a column having one values in 99% records and another values that have to be search for, it is possible to create an index using null value. Index will be small and the rebuild fast.

Example

create index vh_tst_decode_ind_if1 on vh_tst_decode_ind
(decode(S,'I','I',null),style)

It is possible to do index more selective when the key is updated and there are many records to create more levels in b-tree.

create index vh_tst_decode_ind_if3 on vh_tst_decode_ind
(decode(S,'I','I',null),
decode(S,'I',style,null)
)

To access the record can by like:

SQL> select --+ index(vh_tst_decode_ind_if3)
2 style ,count(*)
3 from vh_tst_decode_ind
4 where
5 decode(S,'I','I',null)='I'
6 group by style
7 ;

[code]....

View 2 Replies View Related

Performance Tuning :: Managing Large Table?

Aug 26, 2011

I am working with an online application with the database in Oracle 10G. We have a table with 10 million rows and this table is subjected to grow in future also. Moreover we cannot archive some of these rows as these records are required for referencing.

We have all necessary indexes on the table but querying this table takes a lot of time especially when it is joined with other tables. some methods with which I can manage this table in a better way so that queries joining this table would execute faster..

SELECT
TAB1.C6,
TAB1.C8,
TAB1.C10,
TAB3.C4,

[code]....

View 7 Replies View Related

Performance Tuning :: How Expensive (speed) Is Unique Versus Primary Key In Large Table

Aug 15, 2011

I have two design alternatives and need to understand how expensive (speed) is one of them against the other for a medium size table (100K-200K records):

create table xyz
(
f1 number not null,
f2 varchar2(20) not null,
f3 number not null,
f4 varchar2(50),

[code]....

the idea is to optimize the design by using a PK instead of the 3 keys and there is a debate that searching a unique index field(2nd scenario) is of the same speed than searching a PK field (1st scenario).

View 5 Replies View Related

SQL & PL/SQL :: Performance Tuning In Large Inserts

Jan 19, 2011

I want to load 10 millions records from staging table to master table.One logic must be take during the load, the logic is rows already present in master table means, we need to update corresponding rows in master table otherwise rows insert in target table.

I have been using bulk collect and forall method to load data. it shows better performance compare then cursor row by row process. As per oracle doucmentation, we cannot use SELECT statements inside FORALL condition so we could not use logic inside the forall condition.

View 2 Replies View Related

Performance Tuning :: Split Data Separated By Comma / Then Create Collection With Data Mapped From Other Columns

Sep 25, 2013

DB Used : Oracle 10g.

A table X : NUM, INST are column names

NUM ----- INST

1234 ----- 23,22,21,78
2235 ----- 20,7,2,1
1298 ----- 23,22,21,65,98
9087 ----- 20,7,2,1

-- Based upon requirement :

1) Split values from "INST" Column : suppose 23
2) Find all values from "NUM" column for above splitted value i.e 23 ,

Eg:

For Inst : 23 ,
It's corresponding "NUM" values are : 1234,1298

3) Save these values into

A table Y : INST, NUM are column names.

INST NUM
23 1234,1298

1) I have a thousand records in Table X , and for all of those records i need to split and save data into Table Y.Hence, I need to do this task with best possible performance.

2) After this whenever a new data comes in Table X, above 'split & save' operation should automatically be called and append corresponding data wherever possible..

View 4 Replies View Related

SQL & PL/SQL :: Split String Function For Large Number Of Records?

Aug 6, 2010

I have created a function that is used for splitting a comma separated string & give the output in tabular form.here is the function

Here I have used CLOB as my input string will be huge(greater than max limit of varchar2)

CREATE OR REPLACE TYPE SPLIT_TBL_CLOB AS TABLE OF CLOB;
CREATE OR REPLACE FUNCTION CSVTOSTRING_CLOB
(
P_LIST CLOB,
P_DEL VARCHAR2 := ','
) RETURN SPLIT_TBL_CLOB PIPELINED

[code]....

But here I am facing 2 problems.

1. The function is not accepting a large string & I am getting the error

ORA-01704: string literal too long

2. The function is going for an infinite loop.

View 10 Replies View Related

Performance Tuning :: Merge Statement Tuning For 100M Records In Table?

Oct 31, 2011

I have two tables with 113M records in DWH_BILL_DET & 103M in prd_rerate_chg_que and Im running following merge query, which is running for 13 hrs to update records, which is quiet longer time.

SQL> explain plan for MERGE /*+ parallel (rq, 16) */
INTO DWH_BILL_DET rq
USING (SELECT rated_que_rowid,
detail_rerate_flag_code,
rerate_sel_key,

[code].....

View 39 Replies View Related

Performance Tuning :: Update Columns Of One Table Using Another Table

Feb 6, 2011

I am trying to update columns of Table A with the columns of Table B. Both these tables have 60,000 rows each. I tried this operation using following 2 queries:

Query 1

Update TableA A
set
(A.col1,A.col2,A.col3)=(select B.col1,B.col2,B.col3
from TableB
where A.CODE=B.CODE)

Query 2
Update TableA A
set
(A.col1,A.col2,A.col3)=(select B.col1,B.col2,B.col3
from TableB
where A.CODE=B.CODE)
where exists
A.code = (select B.code
from TableB B
where A.code=B.code)

When i execute these two above queries, it keeps executing indefinitely.

View 4 Replies View Related

Performance Tuning :: Query Doing FTS On A Big Table

Jun 24, 2011

Below query is taking a long time...

select gam.SOL_ID,COUNT(gam.FORACID) from gam,smt where
gam.ACID=smt.ACID and gam.ACID NOT IN(select ACID from imt) and
gam.SCHM_TYPE in('SBA','CCA','CAA','ODA') and GAM.ACCT_CLS_FLG='N' and
gam.SOL_ID IN(select SOL_ID from IMT) group by gam.SOL_ID
/

attached is the explain plan.

in which index on IMT table is not used. And the query is doing a FTS on IMT table. What needs to be done to avoid FTS on IMT table.

View 10 Replies View Related

Performance Tuning :: Partition Of Table

Jun 28, 2010

What are the factors that decide on which column we should partition the table and which partition method we should chose.

View 2 Replies View Related

Performance Tuning :: Delete No Of Records From A Table

Aug 3, 2010

I am using one script to delete the records from a table, its taking 1hr to delete.

declare
cursor c1 is select ownerid,ownertype from nightly_metric_projects
;
v1 c1%rowtype;
open c1;
loop
fetch c1 into v1;
exit when c1%notfound;
DELETE FROM DGT_ITEMEFFORTDATA WHERE OWNERTYPE = c1.OWNERTYPE
AND OWNERID = c1.OWNERID;
end loop;
close c1;
commit;

nightly_metric_projects--1200 records
DGT_ITEMEFFORTDATA--13200000

View 14 Replies View Related

Performance Tuning :: Table Partitioned With Primary Key

Aug 4, 2010

I have normal tables with hugh Data and would like to increase the performace by following means:

1) Add a new column in each table. Say this column Name is IS_LIVE. This new column have only two value 1 ( LIVE ) OR 0 ( NOT LIVE ).
2) Change the normal tables to Partitioned table. There would be only two partitioned in all the table. The partitioned key column would be IS_LIVE and both partitioend recrods would be in two different tablespace.
3) Added a POLICY function to these partitioned table to Always add a Query Predicate of '1' to all queuries.

I am interested to know that what kind of Indexes ( Global Or local ) would be suitable for these kind of Design.Is there any use of having Local index on IS_LIVE.Please note that Primary Key doesnot have this new column in it.

View 10 Replies View Related

Performance Tuning :: What Does Analyzing A Table Do For Indexes

Sep 26, 2012

what analyzing a table does to existing indexes? Do I need to rebuild the indexes after dbms_stats.gather_table_stats command ?

View 4 Replies View Related

Performance Tuning :: Table Occupying Max Size

May 9, 2011

Objective : To find solution to archieve data from 2 big tables which is occupying maximum size in the data base. With current data (From Jan 2005 to Sept 2011) it has records as mentioned below:

transaction - 41687927
trnansaction_dtl - 83945934

We need to load data and run monthly batches from October 2011 to current month which will increase this space.

1. Issue is there will not be having so much space.

2. Maintenance of such table is diffcult now.Also there is huge impact on performance. Can we think of partitioning the table base on date aswe query 1st table based on certain date range?

3. Most of reports use this table and creating performances issues

View 30 Replies View Related

Performance Tuning :: Big Table Data Maintenance

Jun 17, 2011

We have few tables in our production database which are havoc in size and will increase in size in future too so as part of the corrective measures , we have jotted down the below 3 methods to manage the size of those tables :-

1> Partitioning the table and take the export of identified partitions and after that, truncate those partition.
2> Creating history tables and remove not so current data from the original table to history table.

View 3 Replies View Related

Performance Tuning :: Remove Table Fragmentation

Dec 29, 2011

I have tried below steps for removing the table fregmentation but for some table i am not getting good result here.

1. It will collect the data which are having more than 100MB fragmentation.

select owner,table_name,blocks,num_rows,avg_row_len,round(((blocks*8/1024)),2)||'MB' "TOTAL_SIZE", round((num_rows*avg_row_len
/1024/1024),2)||'Mb' "ACTUAL_SIZE", round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) ||'MB' "FRAGMENTED_SPACE" from
dba_tables where owner in('a','b','c','d') and round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2)
> 100 order by 8 desc;

2. then move the object(table) to the same tablespace.

alter table abc move;
alter table bcd move;
alter table efg move;

3. also rebuild the dependent objects.

alter index abc_PK rebuild online;

4. Then analyze the table which are having more than 100MB of fragmentation.

exec dbms_stats.gather_table_stats('a','abc');
exec dbms_stats.gather_table_stats('b','bcd');
exec dbms_stats.gather_table_stats('c','cdf');

after that when check the table fragmentation, i am getting the same result, which i have collected from the 1st query.

View 7 Replies View Related

Performance Tuning :: Stale Statistics For Table?

Apr 27, 2012

From the below query i found that there are some stale stats for 3 tables.

=================================
select table_name, stale_stats, last_analyzed
from dba_tab_statistics
where owner= 'SYSADM' and stale_stats='YES'
order by last_analyzed desc

I collect stats for those above 3 tables with dbms_stats.gather_table_stats().But no luck.After collection of stats immediately I ran the above query.But still it is showing there are stale stats for 3 tables.

how can I change "STALE-STATS" status, so that optimizer can use the updated stats eficiently.

View 3 Replies View Related

Performance Tuning :: DML Slow When Table Have Many Rows

Sep 4, 2011

If a table(have a primary key) is empty(after truncate),the sql of dml(insert,update) is very quickly,but if the table have many rows about 10,000,000 rows, the dml is very slowly,why?

View 6 Replies View Related

Performance Tuning :: Stats On A Partitioned Table?

Aug 8, 2013

Is it possible for the DBMS_STATS "LIST STALE" command to show a stale partition but NOT have its table show as stale?

I had a scenario where the table itself AND 1 partition showed as stale. I ran a fnd_stats gather table stats just on that 1 partition. Once it was completed it showed the partition to no longer be stale. it also showed that the table was no longer stale. so I guess I do not need to run stats on the whole table as well?

so if this is the case, when would I need to run stats on the full partitioned table if running it on the partitions themselves removes the staleness of the table?

View 3 Replies View Related

Performance Tuning :: Initial Extent For Table?

Mar 19, 2012

1.2 million chained rows, 1.7 million blocks, etc. Initial extent for this table is 64k and next 1 mb. I would try to calculate this out better for efficiency and performance. This will not be efficient as it stands. calculate the size.

View 14 Replies View Related

Performance Tuning :: Table Rebuild Should Be Done After Removing Most Of Its Data?

Oct 23, 2012

We are on Oracle 10.2.0.4 on Solaris 10. There is a table in my production db that has 872944 number of rows. Most of its data is now unnecessary, we need to retain, based on a date column in the table just last one month's data and delete rest of the data. So after that the table will have just 3000 rows.

However as the table was huge earlier(872k rows prior to delete) , does the delete of data release its oracle blocks and does the size of the table reduce? If not, will it rebuild the table online (online redefinition) so that the query that does a full scan on this table goes faster?

I checked using an example table that just delete of data does not remove the oracle blocks - they remain in the user_tables for that table and cost of full table scan remains same. We have a query that does the full table scan so I am thinking that after this delete I should do an online table re-definition , is that the right decision?

View 4 Replies View Related

Performance Tuning :: Insert Into Table Slows Down Query

Sep 23, 2010

When i run a script that does a select from a single table (table has 33521868 records)the query is executed in about .094 seconds. I use the exact same query to insert into a temporary table and the query takes 10 minutes and more.

What should I be doing to speed up this process. Also tried using hints and it does not speed up the insert.

View 3 Replies View Related

Performance Tuning :: Lot Of Time Being Taken To Delete Data From A Table

Apr 27, 2012

I have a table which contains 8,21,177 amount of data totally.Now I am trying to delete around 4,84,000 of data from this table by using just one filter i.e. my query is something like below

DELETE /*+ parallel(resource,4) */ FROM resource where created_by = 'MIGN'

This is going to delete 4,84,000 rows of data . But my current issue is this is taking lots of time to delete the data . To be precise , its almost taking 25 hours to delete this data..The created_by column is indexed .

Execution Plan
----------------------------------------------------------
Plan hash value: 2389236532

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------

| 0 | DELETE STATEMENT | | 499 | 20459 | 39 (0)| 00:00:
01 |

| 1 | DELETE | RESOURCE | | | |
[code]....

View 26 Replies View Related

Performance Tuning :: Insert Data Fast On Table

Oct 15, 2013

I am inserting data using a procedure for 2012 and 2013 year which is using partitioned tables includes crore of data in a partition taking lot of time or taking months. Is there any other way by which I can insert data fast from our query.

View 14 Replies View Related

Performance Tuning :: How To Check Table Has Changed 10% In Database

Dec 13, 2011

By default the DBMS_STATS package runs once every 24 hours to collect statistics for database objects and Oracle collects new statistics when enough of the data (about 10%) has changed.

My question here is how to check the table has changed 10% in database?

View 23 Replies View Related

Performance Tuning :: Difference Between Move And Shrink Table?

Jul 22, 2011

What is different between move table and shrink table?

View 2 Replies View Related

Performance Tuning :: Alternate Query Instead Of Querying Table Twice?

Nov 11, 2012

The Item data for individual cycles is as below.

Item_tbl
ItemRundate StddateStatus
P103-Nov-1203-Nov-12A
P104-Nov-1204-Nov-12D
P2 04-Nov-1203-Nov-12A

The requirement is I have to get the details of all data of previous Active cycle(status A) when the Item became disabled(status = D) for Input date.

In above case,since for Item P1 and on cycle date 04-Nov-12,status is D,I have to consider the previous active cycle which is 03-Nov-12. Based on above std date,the data is queried from another table to get all the Items. Item P2 should not be considered in above case.

Below is the code which I have written which considers the rundate as Input parameter.

-- To get the Items disabled for Input date
with Itemdisabled as
(
select item,stddate maxcycledate
from Item_tbl
where rundate = stddate

[code]....

In above case,I'm querying the Item_tbl twice once for getting the disabled Items and once for getting the Previous cycle which is active.

Is there any way to query above only once and get the required results using Lag/Lead functions etc.

View 5 Replies View Related

Performance Tuning :: How To Find Top Query Running On A Table

Feb 19, 2012

How do i find a particular SQL or a set of SQL's which are excuted against a table (user identified table) that is either a very frequently executed query against that table or high impact SQL against that table? I am currently looking through the AWR reports to go through all the queries but i was wondering if there are any dictionary views where we can find this info from?

View 2 Replies View Related

Performance Tuning :: Frequent Changed Table Stats

Aug 6, 2012

Our UNDO space remains at a high level 85 to 95 percent. We keep adding database files and it doesn't seem to go down significantly. When we do a backup of the system where we shut the database down, it does go down some but then within a week or so it is back up again.

View 11 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved