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 ?
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?
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.
I created a mv for one of the partitioned tables but on viewing the mv capabilities it still shows PCT is set to 'N'.
create materialized view MV_summary_SEC refresh fast start with sysdate nEXT SYSDATE + 1/24 enable query rewrite as [code]....
If i remove the sub query and create the mview like this,then PCT is enabled.
create materialized view MV_summary_SEC refresh fast start with sysdate nEXT SYSDATE + 1/24 enable query rewrite as select PERIOD , SUM(SUM_WEB_HITS) from summary ,date_table where PERIOD >= DATE_TABLE.CUR_DATE group by PERIOD
Is it simply because oracle doesn't support PCT if the definition contains subqueries ?
Users use front end (called ESS Console) and when they try to open one of those tables they wait very long (really bad performance). Sometimes the GUI even hanging without displaying results.
Does Partitioned Tables feature works for better performance?
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:
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.
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.
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 ?
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?
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.
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);
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.
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
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?
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.
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.
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.
