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
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 RelatedI 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?
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?
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 ??
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.
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 RelatedI 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.
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.
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?
I have a table Employee which gets updated and Inserted very frequently. Wanted to increase the PCTFREE value for this table
select table_name,pct_free,chain_cnt from user_tables
where table_name='EMPLOYEE'
EMPLOYEE NULL 0
is the output. I altered the pctfree like below
ALTER TABLE EMPLOYEE PCTFREE 30
and I can see Table altered but the value is not reflecting in above command again. Where else I should alter the value and cross check that value is changed.
What Influence PCTFREE on Shrink?.
View 1 Replies View RelatedHow to compress sub partition on exadata, using 'for query high' and pctfree 10 options? I used this statement, but I get only ORA-14160:this physical attribute may not be specified for a table subpartition.
alter table table_name move subpartition subpartition_name PCTFREE 10 compress for query high;
tell the syntax for setting pctfree and pct used for a specific table.
View 4 Replies View RelatedRDBMS - 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?
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 RelatedI am on 11.2.0.3 Enterprise Edition. We are using the new feature "Composite Domain Index" for a Domain index on a very large table (>250.000.000 rows). It really works with mixed queries. We added two number columns using FILTER BY.We have lots of DML on this table. Therefore, we are executing synchronize and optimize once the week. The synch behaves pretty normal. But "optimize_index" takes a very very long time to complete. I have switsched on 'logging' for the optimize process. The $I table takes some time but is finished normally. But the optimization of the $S table (that is the table created for the CDI feature) is running over 12 hours now - and far from being finished. From the logfile, I can see that it optimizes 1000 rows every 20 minutes. Here is the output of the logfile:
Oracle Text, 11.2.0.3.0
14:33:05 06/26/12 begin logging
14:33:05 06/26/12 event
14:33:05 06/26/12 process $N for optimize: SEQDEV.GEN_GES_DESCRIPTION_CTX_I
14:33:16 06/26/12
14:33:16 06/26/12
[code]....
I haven't found a recommendation from Oracle not to use "optimize_index" for Domain Indexes with CDI. But in my case, it would be much faster just to drop and recreate the Domain Index in question.
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.
I am facing the error "ORA-01502: index or partition of such index is in unusable state " while loading the text data using
sql loader with direct path (direct = Y ,rows = 10000) option. Table consists an composite non unique index. If I query the dba indexes for the effected index it shows the index status as VALID. There was no maintaince done on the effected table or index. I have tried loading the same data using conventional path but didn't found any issues for the same.
I have a query which had a join:
a.c1=b.c1 and a.c2=@var
where @var is user supplied input at runtime...We had a index on a.c2 . The CBO would use this index to generate an opitimised query plan.We found some records from table "b" were dropping due to inner join. So we made a change in join. It'd be like
a.c1(+)=b.c1 and nvl(a.c2,@var)=@var
This query is no longer using the index, instead its doing a full table scan causing the query to slowdown.I have tried creating index on nvl(a.c2,'31-dec-9999')
But the CBO won't use it.Anyway to create index on this col so that full table scan can be avoided?
How to force an index if the table not using the index?
View 10 Replies View RelatedWe have occurrences of enq : TX - index contentions in the database. Using the SQL ID, we have identified the INSERT statement and the table which they are trying to insert.
This table has almost 25 different indexes, some of which are unique as well.I am wondering how to identify the actual index causing issue, out of these 25 indexes.
Is there any way to pin point to the name of index which is causing the lock?My plan is, once the index is identified, I would like to check the extents and inittrans and other attributes of this index to fix.
Can we create non-cluster index on a clustered index?
View 5 Replies View RelatedHow 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 RelatedI 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 ?
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.
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 RelatedWe 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.
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.
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 ?