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


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

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

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

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

When Do Global And Local Indexes Becoming UNUSABLE?

Nov 29, 2012

RDMS Version : 11.2.0.2
Platfomrm : AIX 6.1

For partitioned tables (RANGE, LIST types ) what are circumstances when a Global or a Local Index become UNUSABLE ? I was told that in some circumstances where the Indexes become UNUSABLE , not even a SELECT query against the table will work. Is this true ? For partitioned tables with Global Index, the global index will be listed in DBA_INDEXES . Right ?

View 5 Replies View Related

Global And Local Inventory - Patchset?

Apr 21, 2013

1)What is the use of inventory?

2) Global inventory is found by checking the oraInst.loc file. How to find the location of Local inventory?

3)Why is every patchset(11.2.0.2) now a full release?

View 4 Replies View Related

Performance Tuning :: How To Find Local And Global Indexes On Particular Table

Mar 19, 2012

getting how many local and global indexes on particular oracle table

View 2 Replies View Related

SQL & PL/SQL :: Bitmap Or B-Tree Index?

Apr 9, 2010

I was executing following query and it was taking about 20 sec's to execute before i decided to create B-Tree(Normal) index on column DeliVery.

Select
DeliVery,Code,Sum(Units),Sum(Loads),Count(units),
Count(Loads)
Where

[code]...

After creating B-Tree index on Column Delivery the execution time has been reduced to one second ,thats what i wanted. But If i create Bitmap index on the same column then execution time is not getting reduced and is still same around 20 sec.I think theoratically "Delivery" column is the right candidate for Bitmap index? whether should i create bitmap index or stick with B-Tree index?

View 14 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 :: What Should Be Order Of Columns While Creating B-tree Non-unique Index

Jul 19, 2012

What should be the order of columns while creating b-tree non-unique index.

Low cardinality first
or
Hight cardinality first

View 15 Replies View Related

SQL & PL/SQL :: Index On Global Temp Tables

Jun 16, 2010

I am trying to use Global temporary tables, and index on this table to get my results faster. I can see even if I run any query on this table, it does full table scan and not Index scan..

create global temporary table abc_tab on commit preserve rows
as select a,b,c from xyz;

create index lmn on abc_tab(a,b,c)

View 10 Replies View Related

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

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

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

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

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

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

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

PL/SQL :: Bitmap Index On Number Column X Char Column?

Jul 23, 2013

is there some performance/access difference between a bitmap index on a number column and char(1) column? Both columns are not null with a default value.My application has a querie like this:  

select ass.column20,  ass.column30from table_a pucinner join table_b asson ass.column1 = puc.column1where pc.column_char = 'S'and ass.column_char02 = 'P'    

If I create a bitmap index on column "column_char", the access plan is not changed. But changing the column datatype to number(1) and obviously the values, the index is accessed and the cost decreases.This table has 4.000.000 rows. Oracle 11.2.0.2SO

View 7 Replies View Related

Index On Null Column

Oct 31, 2012

I have a table with column A which contains very few null values. I need to select these rows. I am considering two options:

a) create function based index on NVL(A, 0) and use this in where clause NVL(A, 0)=0 (column doesn't have values 0)
b) create function based index on NVL2(A, 0, NULL) and and use this in where clause NVL2(A, 0, NULL) = 0

First idea was option A. But I realized in option B the index will be much smaller, because most of values of column A isn't NULL so NVL2 will return NULL and index will not have as much leafs as in NVL. It is good idea to use NVL2? Is there any against to use option B instead of A?

View 1 Replies View Related

SQL & PL/SQL :: How To Add Column In Unique Index

Jan 17, 2011

I have created one unique index on one column of my table. Now i would like to add one more column in the same index without dropping the index.

SQL > CREATE TABLE DEBUG_TABLE
2 (
3 SLNO NUMBER,
4 MESSAGE VARCHAR2(4000 BYTE),
5 CREATED_DATE DATE DEFAULT SYSDATE,
6 CREATED_TIME TIMESTAMP(6) DEFAULT SYSDATE
7 );

Table created.

SQL > CREATE UNIQUE INDEX index_debug1 ON debug_table (SLNO);

Index created.

SQL > ALTER INDEX index_debug1 ADD COLUMN MESSAGE;
ALTER INDEX index_debug1 ADD COLUMN MESSAGE
*
ERROR at line 1:
ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option

SQL >

View 6 Replies View Related

PL/SQL :: Make Index On Column?

Oct 5, 2013

If my query is under execution and I want to make an index on a column which is very much needed by my query. Will a simple index solve the purpose or is there any extra keyword required ?

View 2 Replies View Related

PL/SQL :: Index On Date Column

Aug 2, 2012

Is the index suggested on the date column.

Here is the nature of the date column in my case:

1. The table populates with 1000's of records every day with date being always incremental (current date).

2. The search criteria from the weba application (ADF) is based on the date, user gives the range.

3. From ADF I am referring to it as sql.Timestamp when building the query.

Does Index suggested on the date column here and if so what type of index ?

View 4 Replies View Related

Creating Index On Hashed Column

Sep 23, 2010

I currently have a 5 column index on a table with over 2 billion records (paritioned on created_date (weekly) that is not very effective.I am contemplating replacing this 5 key index and creating a new single column index made up by hashing of all the 5 five columns.

Is this a wise stratgey? How can I implement this so it is most effective and I dont shoot myself in the foot?

View 4 Replies View Related

Multi-column Index - Low Cardinality?

Oct 13, 2011

I have a table with, for example, three columns: A, B,C.

I execute on this table only one select: CODESELECT * FROM TABLE WHERE A = :1 AND B=:2

Column A has a lot of distinct values (numbers), but B can have only two values: 'Y' or 'N' (cardinality about 50%/50%). It is worth to create index on two columns: A, B? Does query using index on A column will be much slower than using index on A, B?

View 4 Replies View Related

Creating A Multi Column Index

Jan 24, 2012

I have in my database (OLTP-System) a table with about 6000000 records and a zise of about 2GB.

the way to create multi_column indexes on the table?

What are the rule to define the best-position of a column in an index?

index_1(col_1,Col_2,col_3) and not [ (col_1,Col_3,col_2) or (col_2,Col_3,col_1) or (col_2,Col_1,col_3) or (col_3,Col_2,col_1) or (col_3,Col_1,col_2) ] ?

View 4 Replies View Related

SQL & PL/SQL :: ORA-01408 Index Already Exist On The Column

Apr 15, 2010

I am creating an index in program and then drop the index at the end of the program. Some times due to some problem if the index could not be dropped and the user again runs the program then we get the error

ORA-01408 Index already exist on the column.

how I can get away with this error or how I can check whether the index with the same columns exits prior to creating an index.

Is there any command like

Create or replace index.....

View 6 Replies View Related

Can Index Be Created On Virtual Column?

May 17, 2011

what my issue is with this virtual column

CREATE TABLE C0HARPA.VCOL_TAB
(
col1 VARCHAR2(30 BYTE),
v_col1 VARCHAR2(6) GENERATED ALWAYS AS (SUBSTR(col1,1,6)) VIRTUAL
)
TABLESPACE TOOLS

[code]...

Lastly, can an index be created on a virtual column? I am thinking no since it calculated?

View 3 Replies View Related







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