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


ADVERTISEMENT

Performance Tuning :: Local Index Versus Global Index On Partitioned Table

Jun 28, 2011

I have a huge table (about 60 gb) partition over range. The index on this table is global index created on 4 columns together. I have a query which is running very slowly. The explain plan is showing the use of this global index.Explain plan is not showing pstart and pend because the index is global.

View 6 Replies View Related

How Does Local Index Work In Partitioned Table

Nov 28, 2012

I have table with 4 partition by range partition. I am loading the table in bulk mode to latest partition. Before I load , I dropped the index and after Load I will be creating index. So when I am dropping index, it is dropping index from all the partitions and when creating the index, I am creating the index for all partitions. When I am creating index using local, it is telling you have to create local index for all partitions at the same time. because of that I have to drop and recreate all indexes again. Again I have to gather stats for whole table .

I was thinking we can build index for one partition and index should remain as is for old partitions If this is not the case, how do I plan my load for a partitioned table using bulk mode to latest partition.

View 4 Replies View Related

Performance Tuning :: Parallel Index With Non-partitioned No-parallel Table?

Apr 30, 2012

If we have not set parallel degree for a table then we can ( try to ) force parallel execution on a table using a parallel hint Does this 'parallelism' works on the index search in the query as well?

In which situations non-parallel non-partitioned table but parallel index (degree>2) will facilitate a query?

View 5 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 :: 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

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

Bad Query Performance With Global Non-Partitioned Index?

Aug 17, 2010

I have a partioned (by row_create_date) table, lets called it TABLE_X, which has about 300 million records. This table has 7 columns including the primary key and a non-unique, locally partitioned column called trace_id; 99% of queries access this table via this column.

Lately, querying TABLE_X via the trace_id has been very very bad. Queries run for over 1 hr in some cases. So we decided to change the index for trace_id to a global index. Now queries against TABLE_X return in seconds. So far so good.

However, when the query has to join TABLE_X to another table, the query sometimes runs for over 1 hours; back to the same old problem. Here is an illustration;

SELECT COUNT(1) FROM TABLE_X WHERE TRACE_ID = 'XXXXX';
-- returns in seconds
SELECT COUNT(1)
FROM TABLE_X,
TABLE_Y
WHERE TABLE_X.TRACE_ID = 'XXXXX'
AND TABLE_X.TRACE_D = TABLE_Y.TRACE_ID;

-- runs for over 1 hr, even when TABLE_Y.TRACE_ID is a primary key.

View 3 Replies View Related

Index Creation Estimates

May 13, 2013

I have a quiet large table (around 140 GB) and i want to create an index on it. I was wondering, before i fire my my create index statement, i know in advance how long will it take to complete? I know after the create index is executed, we can monitor and gets some estimates from longops view. but is there any way we estimate before hand?

Also my database is in archive log mode and what would be the fastest way to create an online index?

View 5 Replies View Related

Data Pump Index Creation

Aug 7, 2011

I am running Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production on RHEL5. I am busy with data pump import, from the log I can see that my import is busy with the constraints.

I am using parameter EXCLUDE=INDEX during the import and I created the index DDL's.

Now I want to manually create indexes while the import is busy.

Will this be advisable to do or what would be the impact?

View 1 Replies View Related

Performance Tuning :: Index Creation?

Aug 9, 2010

is there any way to reduce the index creation time.

in my case one index creation took 5 minute and there are 5 indexes , so it took 25 minutes.

View 7 Replies View Related

SQL & PL/SQL :: Local Index Creation In Oracle Partitioning?

Jul 6, 2010

I have a table which if approc 5GB in size.I am trying to create a partition table from it. I created the new table,but when i am creating local indexes, i am getting an error when i create a unique key. I ll post the creation scripts:

CREATE TABLE A_PT_ORDREG
(
BUS_UNIT VARCHAR2(5) NOT NULL,
ORDER_NO VARCHAR2(15) NOT NULL,
PT_CODE VARCHAR2(15) NOT NULL,
REG_NO VARCHAR2(15) NOT NULL,
AS_APPLICATION VARCHAR2(5) NOT NULL,

[code].....

THE INDEX:

CREATE UNIQUE INDEX PK_A_PT_ORDREG ON A_PT_ORDREG (BUS_UNIT, ORDER_NO, SEQ_NO) local;

I am getting the following error:

ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index

View 4 Replies View Related

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

Performance Tuning :: Index Creation On Text Column?

Oct 10, 2013

We have a table called address and having the address fields and city ,state etc. The table will store huge amount of data .We need to query on the table. I would like to know how can we fasten the query and improve the performance of the query by creating index on these columns...Query is given below . note that the nullable columns can have data

SELECT *
FROM address
WHERE address1 = 'a'

[Code]....

View 9 Replies View Related

Performance Tuning :: How To Reduce Index Creation Time

Aug 17, 2010

Is there any way to reduce the index creation time. I have one table which has 7700000 records and every day this table get truncate and we create with create table as select statement and then create the 4 indexes and each index took 5 minutes so in totality it took 20 minutes in index creation.

View 2 Replies View Related

Performance Tuning :: Oracle 8i / Win NT - Creation Of Index On Columns

Mar 30, 2006

we have oracle 8i/win nt. (Prod Server running 24 X7)

On doing a explain plan on long running queries. i realized a couple of columns (that were used in the WHERE condition, were not having any indexes.)

More importantly, here we do not have a Test environment.

I would like to know if i can go ahead and create indexes for those 2 columns by issuing

CREATE INDEX index_name on table_name(Column_name);

on our PDB.

Would this be of any impact on our PDB? Do i check anything more prior to building these indexes. or Do have i create them at a down time of PDB.

View 4 Replies View Related

Server Administration :: Move Partitioned Table Between Table Spaces Of Different Block Size?

Apr 4, 2011

I was about to move some tables from one table space to another but it seems it is not possible to move partitioned tables between table spaces of different block sizes.

So far the only option I have is to export and then import back the data.

know if there is any way to move a partitioned table between table spaces of different block size?

View 14 Replies View Related

PL/SQL :: Index Range Scan And Table Access By Index Rowid Versus Table Access Full

Oct 5, 2013

Let's consider such table that all rows fit into single block:

SQL> create table test as select rownum id, '$'||rownum name from dual connect by level <= 530;
Table created.
SQL> create index i_test on test(id);
Index created.
SQL>
SQL> begin

[code].....

why does approach with full scan take longer even if table occupies only one data block? PS. 11gR2

View 8 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

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

Performance Tuning :: Force Index If Table Not Using Index?

Aug 9, 2013

How to force an index if the table not using the index?

View 10 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

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

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







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