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


ADVERTISEMENT

SQL & PL/SQL :: Interval Partitioning With Local Index?

Nov 25, 2011

I Have created a interval partitioned table with local index.But when automatic partitions are created based record insertion, whether local indexes will be created for each newly created partition or not? If created, how to check

Below is the code which I tried

CREATE TABLE interval_date
(
date1 date,
days VARCHAR2(50)
)
PARTITION BY RANGE(date1)
INTERVAL (NUMTODSINTERVAL(45,'DAY'))

[code]....

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

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

Server Administration :: Convert Global Index To Local Index

Jun 23, 2011

I have a global index and I want to convert it to local index.Is there a way to recreate local index with out dropping the global index.

I can create a local index first and then drop the global index. But is there a way to create it with out dropping the global index, just convert it.

View 5 Replies View Related

Index On Partitioning Key

Mar 13, 2012

There is a very large fact table that is range partitioned by a column DATE_KEY of type NUMBER(38), such that every hour is a different partition. There is a bitmap index BX$FACT#DATE_KEY on field DATE_KEY, which also is a foreign key referencing DATE_DIM (DATE_KEY). There is a different DATE_KEY for every hour, generated as YYYYMMDDHH24.

When I run

"SELECT * FROM FACT WHERE DATE_KEY >= 2012031207 AND DATE_KEY < 2012031208"

to get all the records for 7 am on March 12th, partition pruning kicks in and sees that only one partition is used. The CBO then decides to do a full scan of the partition. This behavior is correct/desired.

If, however, I run

"SELECT * FROM FACT WHERE DATE_KEY = 2012031207 AND DATE_KEY < 2012031209"

to get all the records for 7 and 8 am, Oracle knows that it will have to scan two partitions. The CBO then decides that using the BX$FACT#DATE_KEY must be a good idea and, instead of doing a full scan of the partitions, does access by local index rowid, which is many times slower.

I think I understand the cause - when more than a single partition is involved, Oracle has to use the global index stats (instead of the local ones, like in the first scenario) and the CBO decides to use it because that the selectivity for the global index is great, when in fact the query will return all the rows for that particular partition (no selectivity).

How to get the CBO to choose a full scan in the second scenario as well? I need to support ad-hoc queries generated by a BI tool, so I cannot add hints to the queries. I also can't get rid of the index on DATE_KEY, because in real life the predicates are on fields of the date dimension, not directly on the key, so I need to join on it.

View 1 Replies View Related

Local Index Scan

Nov 17, 2012

if i have local index, does query which uses this index always have to scan all partitions?

For example, I have hash-partitioned table on column A. There is local index on column B. I am querying:

select * from my_table where A='ABC' and B>123.

I know that records with A='ABC' are in 2nd partition. Does only 2nd partition of local index will be scanned or all of them must be scanned?

View 1 Replies View Related

SQL & PL/SQL :: Use Of Local And Global Partition Index

Aug 17, 2010

What is the use of Local and Global Partition Index?

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

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

Data Distribution On Local Non-prefixed Index?

Jun 20, 2013

how data is distributed on a local non prefixed index.Let's say I have a table calles SALES and it holds data for the last year partitioned by a date column called month (So that's 12 partitions, one for each month).Now i'm creating the following index: CREATE INDEX TESTIDX ON SALES(salesman_id) local; What will happen is that I'll have 12 partitions on the index but to which partitions in the table are they referencing ?

View 1 Replies View Related

Split Partition - Global / Local Index

Aug 23, 2012

I'm trying to split a table partition.

whether the below syntax are correct.

If local index used

ALTER TABLE SNYT.PART_ESTD
SPLIT PARTITION ESTD_M13_S22 AT ('ESTD', 13, '22')
INTO (PARTITION ESTD_M13_S21, PARTITION ESTD_M13_S22)
update indexes;

(per http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1401247200346349807)
=================================================================
If Global index used

ALTER TABLE SNYT.PART_ESTD
SPLIT PARTITION ESTD_M13_S22 AT ('ESTD', 13, '22')
INTO (PARTITION ESTD_M13_S21, PARTITION ESTD_M13_S22)
UPDATE GLOBAL INDEXES;

View 1 Replies View Related

B-Tree Index On Column - Local Or Global

Feb 20, 2013

Version Info: 11.2 on Solaris 10

I have a partitioned table like below. I want to create a B-Tree index on SALES_RGN column which is neither the part of Primary key or the Partitioned key. Should I create this index as local or Global ?

CREATE TABLE sales_dtl
(
txn_id          number (9),
salesman_id     number(5),
salesman_name     varchar2(30),
sales_rgn     varchar2(10), -----------------------------> This column needs to be indexed
sales_amount     number(10),
sales_date     date,
constraint pk_sales_dtl primary key (txn_id)
[code]....

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

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

Rebuild Local Index Partition Is Using Full Table Scan

Apr 23, 2012

I am rebuilding some UNUSABLE local index partitions on Oracle 8.1.7.4.0 (64bit) database . The platform is a HPUX machine.

The DDL of the partition table/indexes:
=========================
CREATE TABLE TESTME
( INST_NO CHAR(3) NOT NULL,
ACCT_NO CHAR(16) NOT NULL,
REC_NO CHAR(9) NOT NULL,
TRAN_TYPE CHAR(2) DEFAULT ' ',
STAT CHAR(2) DEFAULT ' ',
[code]...

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

Real Application Clusters :: Listener Status Showing Local Virtual IP / But Not Local IP

Jan 19, 2013

SNRCTL> stat LISTENER
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.1.0 - Production
Start Date 19-JAN-2013 00:50:10
Uptime 0 days 0 hr. 29 min. 51 sec
Trace Level off
Security ON: Local OS Authentication
[code]....

In every oracle documentation for e.g:11.2 Scan and Node TNS Listener Setup Examples [ID 1070607.1] we found the local listener status showing both local-ip and vip. Why is not showing in our case?

View 8 Replies View Related

SQL & PL/SQL :: Oracle Table Partitioning?

Jun 2, 2010

an existing normal table be converted to a partitioned table without recreating the table or truncating/reloaded data?

View 4 Replies View Related

SQL & PL/SQL :: Vertical Partitioning In Oracle

Mar 19, 2013

Explain me steps to do vertical partitioning in oracle .

View 3 Replies View Related

Server Administration :: Oracle Range Partitioning

Sep 13, 2010

Environment
I have a database 10.2.0.4 running on 64bit Solaris 5.10 SPARC.

Question:

CREATE TABLE SYSTEM.BIG_TABLE1
(
ID NUMBER(10),
LOOKUP_ID NUMBER(10),
DATA VARCHAR2(50 BYTE)
)
[code].......

When I query dba_tab_partitions. I get this result

select table_name, partition_name, high_value, high_value_length from dba_tab_partitions where table_name = 'BIG_TABLE1' ;

table_name partition_name high_value HIGH_VALUE_LENGTH
------------- -------------- ---------- -----------------
BIG_TABLE1 BIG_TABLE_2003 1000000000 10
BIG_TABLE1 BIG_TABLE_2004 5000000000 10
BIG_TABLE1 BIG_TABLE_2005 MAXVALUE 8

3 rows selected.

Now my question is why do I see max_value_lengh=8 when my column value is 10. I don't get any error. But I do like to understand it.

View 1 Replies View Related

Server Administration :: Oracle Partitioning Installation

Jun 20, 2010

I want to install the partitioning option in my oracle database. I have the license. When i run the installer and select the partitioning option, the installation begins from beginning overwriting my old setup files. How can i avoid this and only install partitioning.

View 2 Replies View Related

SQL & PL/SQL :: How To Transfer Data From Non-partitioning Table To Partitioning Table

Apr 4, 2013

I have problem to transfer data in non partitioning table to partitioning table.

I have non partitioning table and i create new table partitioning that have same column and type like in non partitioning. So how can i transfer data from table in non partitioning to table in partitioning?

View 10 Replies View Related

ASM Disk Groups Creation After ASM Instance Creation

Jul 9, 2012

During ASM Disk Groups creation after the ASM instance creation, receive the following error: Disk Group ORAASMGROUP2 already exists. Cannot be created again

The Grid infrastructure was deinstall one time and still the same issue.

View 4 Replies View Related

Backup & Recovery :: Make Copy Of Oracle Database In Local PC

Feb 3, 2011

I want a copy of data from oracle database in Production to my local machine as upgrade is going to be there soon. How can I do that so that I may check my old data later irrespective of upgrade?

View 9 Replies View Related

Open Excel File From Oracle Directory On Local Machine

May 31, 2013

I have an application where I generate a excel using data in a table and write excel file in oracle directory.Now my requirement is to open that file from local machine .How can i achieve it.I am using Apex 4.2,Oracle 10g .

View 1 Replies View Related

SQL & PL/SQL :: Database Creation Manually In Oracle?

Dec 21, 2011

when i connected with oracle user i got error message like "

ERROR:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_APPLICATION_INFO.SET_MODULE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
"

View 1 Replies View Related







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