How Efficient Are Partitioned Indexes

Feb 22, 2013

Using Oracle 11.2.0.3

We are evaluating partition strategies with view to achieving performance gains in reporting in particular. How efficient are partitioned indexes in this regard e.g.

just partitioned indexes on an un partitioned table.

One large fact table with durrogate keys on which have bitmpa indexes which link to unique key in associated dimensions. Considering partitioning the bitmap index which links to the largest dimension and similarly partition the dimension key on largest dimension.

View 5 Replies


ADVERTISEMENT

Convert Indexes Of Non-partitioned Table To Hash Partitioned Indexes?

Sep 10, 2012

RDBMS - 11.1.0.7, I it possible to convert indexes of a non-partitioned table to hash partitioned indexes by retaining table as non-partitioned?

If yes, is this what it is Creating a Hash-Partitioned Global Index - can be created for partitioned and non-partitioned tables?

View 7 Replies View Related

SQL & PL/SQL :: Procedure To Rebuild Partitioned Indexes

Oct 3, 2012

I have a task to create a procedure that disables (particular) partitioned indexes and REBUILD after ETL load.

Developers wants 3 parameters for the procedure:

1) TABLE_OWNER2) TABLE_NAME3) TRANSACTION_DATE = 13-JUN-12

And here is what I have done. I have created a procedure that takes 3 input parameters, please see the attached script:

1) TABLE_OWNER 2) TABLE_NAME 3) PARTITION_NAME - requires to query the particular partition to get the partition name

a situation where they will input dates as the partition was on a DATE column, now my challenge is how to incorporate this into the procedure to accept DATE as an input which will require one to query the particular table to get dates.I thought of using (HIGH_VALUE - 1) to get the dates from ALL_IND_PARTITIONS.

View 2 Replies View Related

Local Partitioned Indexes (adding Additional)?

Jan 11, 2013

I'm trying to find a way to ADD new partitions to local indexes and at the same time specify their tablespaces without having to DROP and RECREATE.

Here´s an example table based on yearly partitioning:

CREATE TABLE "TABL_ANOM"
(
ANOM_TS TIMESTAMP(6) NOT NULL
, ANOM_TIPO NUMBER(2, 0) NOT NULL
, ANOM_NIVEL NUMBER(2, 0) NOT NULL
, ANOM_ID NUMBER(10, 0) NOT NULL

[code]...

Here´s an index def for the table:

CREATE INDEX "TABL_ANOM_INDEX1" ON "TABL_ANOM" ("ANOM_NIVEL") LOCAL
(PARTITION DGSCOPSX_2011 TABLESPACE DGSCOPSX_2011
,PARTITION DGSCOPSX_2012 TABLESPACE DGSCOPSX_2012
)

OK. Now I want to add partitions for 2013 so for the table I use:

ALTER TABLE TABL_ANOM ADD
PARTITION DGSCOPS_2013 VALUES LESS THAN (TO_DATE('2014-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) TABLESPACE DGSCOPS_2013;

and this works fine for the table but I can't find a similar command to simply add additional partitions to the indexes. I know that I can drop and recreate the indexes with the additional partition defs but on some of my tables, I'm dealing with hundreds of millions of rows and I think it would take way too long to drop and recreate all indexes on all partitions.

Also related is the PRIMARY KEY index partitions. Is there a way to add partitions (specifying the tablespaces) without having to DROP and re-ADD the CONSTRAINT with the additional partition for 2013?

View 3 Replies View Related

Performance Tuning :: Only LOCAL Bitmap Indexes Are Permitted On Partitioned Tables

Feb 4, 2005

16:28:32 SQL> create bitmap index bp_idx_ag_id on transactions(type);

create bitmap index bp_idx_ag_id on transactions(type)
*
ERROR at line 1:ORA-25122: Only LOCAL bitmap indexes are permitted on partitioned tables

how to create bitmap index on partitiioned tables

View 3 Replies View Related

Performance Tuning :: Efficient Way To Alter Column For TDE?

Jul 4, 2013

I am using 11.2.0.3.0 version of oracle.

Now we are supposed to apply column level TDE to some of our table in database. Now it will be a 'ALTER' on the columns. it involves 4 big tables out of which 3 tables having size ~30GB(one is partitioned table) and another one ~800GB(Not partitioned) Now the concern is, what will be the efficient/safest way to apply TDE on columns, below are the two options with us. (NOTE - We do have downtime window during DB maintenance but looking at the size of the table, i suspect it might take lot.)

1. Directly apply 'ALTER' on the columns. (Note- i was testing on my local, it took 3hrs for a 30GB table to ALTER the column to TDE)

2. Use Table Redefinition for Altering the column. (Creating interim table with column as TDE and then Redefining whole table).

View 5 Replies View Related

Efficient Way To Connect SQL Server From Oracle Which Is Running On HP-UNIX

Mar 8, 2011

I am trying to Push the data from Oracle which is running in HP-UNIX to SQL Server. But don't know the efficient way to connect the SQL server from oracle which is running on HP-UNIX.I have heard about oracle Heterogeneous Connectivity but don't know exactly how to implement it in Unix environment.

View 1 Replies View Related

SQL & PL/SQL :: Efficient And Fast Method Required For Audit Trailing Tables In A Schema?

Nov 17, 2011

efficient & fast method for Audit trailing tables in a Schema for any insert /update /deletes for a table. I do not want to use db triggers because of performance issues.

presently we have as system which does audit trail as below:

1. If a user changes a column value for a table ( update/insert/delete) then we call a db package and pass the parameters like table name,col name,user, operation (ins/update/delete),old value, new value,date modified,user modified etc

2. the called package will insert a record in the audit trail table with the parameters passed

3. The audit trail table is used for report generation .

View 1 Replies View Related

Partition Non-partitioned Table To Partitioned Table Date Wise?

Nov 1, 2012

what is the best way to partitioned a non-partitioned table to partitioned table date wise? I have data from last four year?

View 4 Replies View Related

Index In Partitioned Tables

Jan 2, 2013

I would like to ask about indexes in partitioned tables.I have indexes on a partitioned table, it is partitioned by range method i.e based on Creation date time.All select queries sent to the table use the Creation date time. I have an index on Creation date time.Here is an example:

SELECT col1, col2, col3
FROM table1 where
date_time BETWEEN TO_DATE ('20120117 10:00:00','YYYYMMDD HH24:MI:SS')
AND TO_DATE ('20120117 13:00:00','YYYYMMDD HH24:MI:SS')
AND frmt_name = 'XXXX'
AND sender = 'YYYYY'
AND nature = 'ZZZZ'
AND type LIKE '548'
ORDER BY date_time
WHERE ROWNUM <= 5000
[code]....

do I have to add DATE_TIME to all indexes (IX_NAME_FORMAT_TYPE,IX_CCY) or not?

View 6 Replies View Related

SQL & PL/SQL :: Partitioned Table Information

Jan 8, 2013

How to know weather table is partitioned or not ? Is there any data dictionary view to know the information about the Partitioned type,keys on table ?

View 4 Replies View Related

SQL & PL/SQL :: Insert Into Partitioned Table

Mar 14, 2013

I have a table like this:

CREATE TABLE CARGA_P_RECARGA_NEW1
(
TELE_NUM VARCHAR2(10) NOT NULL,
FECHA DATE NOT NULL,

[Code].....

Then I tried to insert some rows in that table, every insert statement is like this:

INSERT INTO CARGA_P_RECARGA_NEW1
VALUES
('3134769595','20/01/2013 07:22:50','1107','CONFB_20121121_20121122175002 60000000000000000090.TXT',0,16,'8327--7991284',1);

Every insert I executed had the month 01 because I expected to query results only from partittion p_0113 but nevermind how query I execute, the result is always the same. I mean if I excute this statement:

SELECT * FROM CARGA_P_RECARGA_NEW1 P_0113;

I get the same result when I execute any other like this:

SELECT * FROM CARGA_P_RECARGA_NEW1 P_0213

What is the error here ?

View 4 Replies View Related

DBMS_REDEFINITION For Partitioned Tables?

Jul 22, 2012

how DBMS_REDEFINITION online reorg works with partitioned tables. I have a table with 6 partitions and I want to reorrg whole table...

View 43 Replies View Related

PL/SQL :: Counts On A Partitioned Table?

Apr 26, 2013

We have a partitioned transaction table in our Datawarehouse environment which has the following partition strategy

SCHEME=DATE-HASH
GRAIN=DAILY
SUBGRAIN=NONE
HASH=8
FROM=31/12/2011

We recently had to delete data from the table. This was a simple delete statement with a where clause and without taking into consideration any partition/subpartition clauses. Post committing the delete we have a count mismatch problem with two queries in particular

select count(0) count_without_parallel FROM TRANSACTION_TABLE t;

--THIS RETRIEVES *15774811* ROWS

select /* parallel(t,default) */count(0) count_with_parallel FROM TRANSACTION_TABLE t+

--THIS RETRIEVES *15777617* ROWS WHICH IS THE ACTUAL EXPECTED COUNT.

I also ran the following just to summarize

select (select count_with_parallel from (
select /* parallel(t,default) */count(0) count_with_parallel FROM TRANSACTION_TABLE t))+
-
+(select count_without_parallel from (+
select count(0) count_without_parallel FROM TRANSACTION_TABLE t)) as false_difference
from dual;

The difference in *2806* rows as expected.To re-affirm my counts I ran

select /*+ parallel(t,default) */
'count_on_t',count(*) from TRANSACTION_TABLE t
group by 'count_on_t'
order by 1;

--THIS RETRIEVES *15777617* ROWS

Removing the parallel hint reverts back to the lesser count. Not sure what is wrong but something prevents the query from parsing the whole table and/or partitions and subpartitions.

View 0 Replies View Related

Partitioned Table Based On Field

Nov 27, 2012

I have partitioned the table based on field.But when I am selecting by Partition or by the field I am getting Explain plan as Table Access full.I am pasting the sql and Explain Plan here. The table has two partition by BOOKING_DT_WID. One less than 20100801 and other less than 99991231.

CODESELECT * FROM WC_BOOKING_SALESREP_F WHERE BOOKING_DT_WID >= 20100801;
SELECT * FROM WC_BOOKING_SALESREP_F  PARTITION(SALESREP_LESS1_99991231);
Here is the Explain Plan for the same.
CODESELECT STATEMENT  ALL_ROWSCost: 1,501  Bytes: 293,923,641  Cardinality: 809,707                  
    4 PX COORDINATOR              
[code]....

How do I know if the sql is doing partition prune.

View 1 Replies View Related

Partitioned Tables And Respective Indices

Aug 23, 2010

understand on the below regarding partitioning table and indices.

1. How to know if each partition have LOCAL index or Global index?

I had the above question duw to the beloe reason :

I have a table with 130 partitions and 7 indices. When checked the DBA_IND_PARTITIONS it lists down only 1 index name (which distinct) for all the 130 partitions.

Does it mean it is a Global index? If Global index, is it partitioned? How to get those information?

2. Assuming, if i have LOCAL indices for every partitions, from where i shall nail down the name of each local index and how would i get the detail of referencing Global index?

3. By which type a table was partitioned? LIST,HASH,RANGE ?

View 2 Replies View Related

Analyzing Partitioned Table On Oracle

Sep 6, 2010

A question regarding analyzing behavior of partition table on Oracle -

Is analyzing different sub partitions within a partition is same as analyzing that partition and what about vice-versa?

Attached File(s)

untitled.JPG ( 9.2K )
Number of downloads: 3

View 2 Replies View Related

Does Update On A Partitioned Column Cause Fragmentation

Apr 11, 2008

Does "Update on a Partitioned Column" cause fragmentation ?

See example below :

Suppose I have a table as below that has approx 3 million rows and growing :

1) Table ABC :
file_id number
status_flag varchar2(1)
file_Content clob
date_created date

2) This table is list partitioned on Column "status_flag".

Column "status_flag" can take 3 values "A" or "B" or "C"

3) A file_id can transition from 1 "Status_flag" to another. [ A--> B , C--> B, B-->C, A --> C, etc ]

This means its possible to UPDATE the "status_flag " - the partitioned column.

Question :
-------------
1) Would the movement of rows from 1 partition to another cause fragmentation ?
2) Or Instead, should this be achieved by maintaining 3 tables - 1 each for every "status_Flag" as Table_A, Table_B, Table_C

This would mean that, if file_id 1, changes "status_flag" from 'A' to 'B' :
-- The corresponding row from Table_A will be created in Table_B
--- The same will be deleted from Table_A

This would still cause fragmentation ... with the overhead of Inserting a CLOB column from 1 table to another.

3) How to determine how much percent of the table is fragmented ?

View 5 Replies View Related

Partitioned Table Number Of Tablespaces

Jan 27, 2011

I created table that is range partitioned:

CODEPARTITION t1p1 VALUES LESS THAN (TO_DATE('2011-11-01', 'YYYY-MM-DD'))
PARTITION t1p2 VALUES LESS THAN (TO_DATE('2011-11-02', 'YYYY-MM-DD'))
....
PARTITION t1p4 VALUES LESS THAN (MAXVALUE)

Every year partitions will be added for next 12 month. The table partition will be dropped every month (I have to have data from last six month so in July I could drop partition t1p1, in August - t1p2....). How many tablespaces should I create for this table and how place partitions in them to have data for last six month and use minimum space on disk?

I was thinking about one tablespace for whole table because space of each dropped partition will be reused, what do you think about that?

View 2 Replies View Related

SQL & PL/SQL :: Create Partitioned Table With Tablespace

May 17, 2012

I got just confused while looking at the below two create table statements:

CREATE TABLE Test (
TestID integer not null,
Name varchar2(20) not null )
PARTITION BY LIST (TestID)
(
PARTITION testPart1 VALUES (1) TABLESPACE tbspc1,
PARTITION testPart2 VALUES (2) TABLESPACE tbspc2@RemoteServer);

and

CREATE TABLE Test (
TestID integer not null,
Name varchar2(20) not null )
tablespace tbspc1
PARTITION BY LIST (TestID)
(
PARTITION testPart1 VALUES (1) TABLESPACE tbspc1,
PARTITION testPart2 VALUES (2) TABLESPACE tbspc2@RemoteServer);

View 11 Replies View Related

How To Archive Data On Partitioned Table

Jul 7, 2011

I have a partitioned table that is streamed to another database. I need to archive data on that table. That is I need to add a partition and remove a partition.

If I make those changes to the source table, will it stream over to the destination table?

If not, can I ...

pause streaming make changes to source table make same changes to destination table sreenable streaming. I know making data changes to the destination table can screw up streams but not sure if that holds for ddl.

View 1 Replies View Related

SQL & PL/SQL :: How To Change Pctfree Of Partitioned Index

Oct 3, 2011

I have a partitioned index with a pctfree of 10 .I want to change pctfree to 20.How to change?

I tried this

alter index fnsonli.IG01PK rebuild partition SYS_P245 pctfree 20 ;
alter index fnsonli.IG01PK rebuild partition SYS_P245 pctfree 20
*
ERROR at line 1:ORA-14185: incorrect physical attribute specified for this index partition

View 8 Replies View Related

SQL & PL/SQL :: Compress Already Existing - Partitioned Index

Jul 5, 2012

Today I found myself in a situation where I needed to compress an already existing, partitioned index. First I issued an alter table to rebuild and compress the whole index:

SQL> ALTER INDEX MY_INDEX REBUILD COMPRESS;

ERROR at line 1:ORA-14086: a partitioned index may not be rebuilt as a wholeSo next I tried to rebuild compress one of the partitions:

SQL> ALTER INDEX MY_INDEX REBUILD PARTITION PART1 COMPRESS;

ERROR at line 1:ORA-28659: COMPRESS must be specified at object level first

Turns out that there is no way you can do a rebuild compress on partitioned indexes. I did not realize that until I stumbled across document 312843.1 on Metalink. It says the only way to compress a partitioned index is to drop it and recreate it (through 11.2).

My question is do we have any new feature in Oracle 11gR2 to rebuild partition indexes?

View 2 Replies View Related

Partitioned Table - What Extent Size To Be Set

Sep 28, 2012

I have a partitioned table (one partition per month). Every month there are added about 1GB data. What extent size should I set? 1GB will be ok?

What if data will be greater than 1GB, adding new 1GB extent takes probably a lot of time and clients may see delays while they're inserting in this time? (it's OLTP system)

When new extent is allocated? Exact in time of lacking space in existing extent or before? Partitions are dropped after one year so free space isn't a problem.

View 6 Replies View Related

SQL & PL/SQL :: To Add A Partition Into A Ranged Partitioned Table

Mar 15, 2010

I want to add a partition into a ranged partioned table but I receive error:

ALTER TABLE t_log
ADD
PARTITION p897_fft
VALUES
LESS THAN (898);

ORA-14074: partition bound must collate higher than that of the last partition

i check the high value like this:

SELECT partition_name, high_value
FROM dba_tab_partitions
WHERE table_name = 'T_LOG'
AND table_owner = 'PLAT'
[code]........

now, what to do? i dont want to remove Partition P899_MCDW. How to Split?

View 5 Replies View Related

Use Oracle 11g As Log Database Using Partitioned Tables

Sep 25, 2012

we are looking to use oracle 11g as a log database using partitioned tables.

- The tables will have only a 3-5 columns of ~varchar(50) size
- We are looking at a volumn of ~33million rows (inserts) per day.

1) Will partitioned tables be able to handle this type of volume?
2) if yes, will a Composite Partitioning (using last modified datetime as range) then subparition with range be the best choice?

if this type of volume is too high for 11g, what are some of the alternative products we can use.

View 5 Replies View Related

Index Creation In Partitioned Table

Jul 20, 2012

Facts:
Oracle 10.1.0.5
AIX 5.3

My table, HMTX have 10 partitions each of one have 6 millions of rows (average). We have 7 partitioned LOCAL indexes in that table.
Every month we load data into a new partition (6 million of rows aprox) and drop the oldest partition in table HMTX.

In order to do that we have a script that contain the next statements:

drop of all indexes
drop index n1;
drop index n...;
drop index n7;

[Code]...

create indexes again with tha same storage and degree parameters
CREATE INDEX hmtx_TST_N1 ON hmtx (campo1, campo2, campo3 .... campo8)
TABLESPACE xxxx
PCTFREE 0
INITRANS 2

[Code]....

My problem is in rhe index creation section, despite use parallel with degree 8 and nologging the index was created in :

Elapsed: 02:43:50.85.

In past months that index was created in :
Elapsed: 01:43:36.94
Elapsed: 04:48:31.24
Elapsed: 00:57:16.28

there are another way in order speed the index creation ?? o another way to disable ths index ??

View 4 Replies View Related

PL/SQL :: Assign Partitioned Index To Primary Key

Jan 25, 2013

create table mypart(a number, b number, c number, p_key number) PARTITION BY RANGE (p_key)
( PARTITION p0 VALUES LESS THAN (18),
PARTITION p1 VALUES LESS THAN (29),
PARTITION p3 VALUES LESS THAN (MAXVALUE)
)  ENABLE ROW MOVEMENT;

create index idx_mypart on mypart(p_key,a,b)

I want to create primary key on this table that will use the local partitioned index idx_mypart

can I do that ?

alter table mypart add constraint pk_mypart primary key using index (idx_mypart)

above syntax gives error

basically the primary key should make use of the local partitioned index.

View 4 Replies View Related

How To Rebuild Local Partitioned Index

Jan 29, 2013

2day i was dropping few unwanted index from the data base, By mistake i removed the local partitioned index , So i want to recreate that index.i create the index, will the partitioned index updates when we add partitioned to the tables.

View 2 Replies View Related

Compress Existing Table Which Is Not Partitioned

Aug 1, 2013

Im having table which is of 45M rows table [Not partitioned], Now I want to compress the old data other than last 3Months data, I should not go for partition compress. Rarely some select queries will be fired on that Old data. Now how can I compress that table without affecting the Indexes , Dependencies proc, pkgs, Functions.   

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionPL/SQL Release 11.2.0.3.0 - Production"CORE 11.2.0.3.0 Production"

View 3 Replies View Related







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