SQL & PL/SQL :: Effect Of Truncate On Associated Indexes Of Tables?
Jul 21, 2012
I know that truncate is a ddl operation that removes all the data from the table and set the HWM to very beginning. I am curious to know, is anyhow indexes will be affected on the issuance of truncate command or we need to rebuild the indexes after issuing truncate.
Also is there any way to know that how many rows/blocks a select statement is scanning because of the effect of HWM even the table has 0 rows.
Are there redo generate during truncate operation even my primary database is in FORCE LOGGGING mode? How this will effect on physical standby database.
I have two tablespace a and a_idx , a is comtaining all tables data and it fixed size is 19000MB and b containing all the index associated with these tables and its size also 19000MB.
but this is am testing database, and we need some space on my unix machine for this i truncate all tables from a tablespace a and resize the tablespace a with size 1000M with no error.
but as per my understanding after truncating all tables from tablespace a its also release space from tablespace b but its not hapening.
so my questin is how can i reize tablespace B using alter database datafile 'full path of dbf file' resize 1000M.
i have many user tables in my database and i want to truncate selected tables and how i can overcome this scenario.
i can truncate using truncate table <table_name> but they are 250 tables to truncate, so,i can't write truncate command for every table. if i want to truncate first i have to truncate parent table or child table and how i can find parent table and child table for given tables to truncatein my database.
We have a procedure, which do truncate to some of the tables. Most of the time it finished in short of spam of time. But from last few days, it is taking much longer time.
i am using oracle 10g. is there a way to find out the over fragmented tables/indexes ,so that reorganizing or rebuilding them will improve sql performajnce?
So our situation is pretty simple. We have 3 tables.
A, B and C
the model is A->>B->>C
Currently A, B and C are range partitioned on a key created_date however it's typical that only C is every qualfied with created date. There is a foreign key from B -> A and C -> Bhave many queries where the data is identified by state that is indexed currently non partitioned on columns in A ... there are also indexes on the foreign keys that get from C -> B -> A. Again these are non partitioned indexes at this time.
It is typical that we qualifier A on either account or user or both. There are indexes (non partitioned on these) We have a problem with now because many of the queries use leading wildcards ie. account like '%ACCOUNT' etc. This often results in large full table scans. Our solution has been to remove the leading wildcard.
We are wondering how we can benefit from partitioning and or sub partitioning table A. since it's partitioned on created_date but rarely qualified by that. We are also wondering where and how we can benefit from either global partitioned index or local partitioned indexes on tables A. We suspect that the index on the foreign key from C to B could be a local partitioned index.
I have one control table as below.I want to rebuild all indexes for the tables in control table.
The control table is having the following data.
SEQ_IDTABLENAME SCHEMA_NAME 1GEDIS_ORDER_FORM_STATES ALL 2GEDIS_NOTES ALL 3GEDIS_CARD_TYPE_AUDIT APRT 4FAX_HEADER OMS
In the control_table schema_name "ALL" means this is for 30 schemas(The table is existed in 30 schemas).Except for schema_name "ALL" ,the table is existed in the particular schema(The table is existed in the only one schema).
I tried the following code it is executing for all 30 schemas(ALL).But it is not executing for specific schemas.
CREATE OR REPLACE PROCEDURE Rebuilding_index IS l_sql VARCHAR2(4000); CURSOR cur_tab_schema IS SELECT tablename,schema_name FROM control_table3;
I have tried a lot by alternate solutions like rearranging the order of tables in join and moving where conditions before but no success...Its a bottleneck and I could not have indexes on these tables in production...I want to change the approach in subquery
SELECT g.COLUMN1, g.COLUMN2, e.COLUMN3, g.COLUMN4, MIN(e.dat1) KEEP ( DENSE_RANK FIRST ORDER BY date2 Desc) * -1, min(to_char(date3,'dd-mm-yyyy')) [code]....
I have created 3 tables and some indexes, but these objects do not show up in dba_segments view. Is this a normal behaviour? Previously, with dictionary managed tablespace, I can specify the minimum extent to create, when the table/index is created. But I'm not sure how the locally managed tablespace work.
I'm using Oracle 11g R2 (11.2.0.1.0) for Microsoft Windows (x64), running on Windows 7.For the purpose of reproducing this issue, I have created the tablespaces as follow:
CREATE TABLESPACE CUST_DATA DATAFILE 'd:appasusoradataorcl11gr2CUST_DATA01.DBF' SIZE 512K AUTOEXTEND ON NEXT 256K MAXSIZE 2000K EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K SEGMENT SPACE MANAGEMENT AUTO;
I have noticed that Oracle text related objects, particularily the $I tables are some of the largest objects in our database. I have been actively pursuing utilizing Oracle advanced compression in our databases for OLTP table compression and LOB object compression. I have been unable to find any documentation or notes on if it is advisable to implement either table OLTP or LOB compression for Oracle text objects.
Create small functional indexes for special cases in very large tables.
When there is a column having one values in 99% records and another values that have to be search for, it is possible to create an index using null value. Index will be small and the rebuild fast.
Example
create index vh_tst_decode_ind_if1 on vh_tst_decode_ind (decode(S,'I','I',null),style)
It is possible to do index more selective when the key is updated and there are many records to create more levels in b-tree.
create index vh_tst_decode_ind_if3 on vh_tst_decode_ind (decode(S,'I','I',null), decode(S,'I',style,null) )
To access the record can by like:
SQL> select --+ index(vh_tst_decode_ind_if3) 2 style ,count(*) 3 from vh_tst_decode_ind 4 where 5 decode(S,'I','I',null)='I' 6 group by style 7 ;
aix 6.111.2.0.3 I have an expdp dump from prod to be imported to our test database.I have imported it using impdp, but to my surprise the tables were imported but lots of indexes were not created? even If I have used TRANSFORM=SEGMENT_ATTRIBUTES:N just to use the default USERS tablespace. How do I import the indexes separately, skipping the tables and other objects?
we are in the migration of oracle apps 11.5.9 [version] , while uploading AR open invoices we used auto accounting rule for revenue account , but we did this changes its not taking an effect , same revenue a/c we uploaded is uploaded , but the auto accounting is not taking an effect ,
for a work around we migrated the data ! but we would be the root cause and possibilities why it hasn't worked ?
But this auto accounting rule worked in CRP & UAT as well ...!!!
SQL> SELECT COUNT(*) FROM v$parameter WHERE value IS NOT NULL; COUNT(*) ---------- 246 SQL> SELECT COUNT(*) FROM v$spparameter WHERE value IS NOT NULL; COUNT(*) ---------- 20
v$parameter displays information about the initialization parameters that are currently in effect for the session,but v$spparameter displays information about the contents of the spfile.I want to know why the count of parameters whose values are not null in v$parameter is greater.where are they from? My initialization parameter file is spfile.
i have one question that why oracle not set high water mark when we delete data from a table and commit it, on the other hand it set in case of truncate.both of these statement release physical structure(in case of delete after commiting)
I have an Oracle Package with a procedure in which
package with a procedure in which there is a truncate partition, ALTER TABLE table_name TRUNCATE partition_name DROP STORAGE and the all is run with an EXECUTE IMMEDIATE 'alter table ...' .
The point is that the procedure in the package is started from another DB via DB Link (schema USER1) and doesn't work because of lack of privileges.
Instead, if the same procedure is started as a procedure, standalone, not in the package but from the same user (USER1) it works perfectly.
Don't understand why and which privileges must give to the user to run the procedure from inside the package.
I am using oracle11g. I want to truncate subpartition on specific partion.
I have partition on statewise. Each state partion has 7 day sub partition.
For intance,
Partion TX Sub partition MON, TUE, WED, THU, FRI, SAT, SUN
Partion CA Sub partition MON, TUE, WED, THU, FRI, SAT, SUN
Partion IA Sub partition MON, TUE, WED, THU, FRI, SAT, SUN
Now i want to perform following tasks.
1. Need to truncate TUE sub partiion on TX partition. 2. Need to truncate WED sub partiion on CA partition. 3. Need to truncate SUN sub partiion on IA partition.
How do we do this?
The below statment truncate all TUE partition on all the partitions.
ALTER TABLE TRX_TABLE TRUNCATE SUBPARTITION TUE;
How do i tuncate specfic sub partition on specific partition?
The Scenario is that we have Master and detail table (With Foreign key enabled), we want to TRUNCATE Master table.
1) Is there any option which can Truncate the table without disabling the constraints for child tables...we want to Truncate the table forcefully.. 2) What will be best method to truncate a Table having Master detail relation (Foreign key enabled) and we need to truncate the table without disabling the constraint ( if there are records in child table) 3) What will be best method to truncate a Table having Master detail relation (Foreign key enabled) and we need to truncate the table without disabling the constraint ( if there are NO records in child table
I have found one interview question no.7 @ [URL] According to that it can be rollbackQuote:The truncate command is a DDL operation and just moves the high water mark and produces few rollback data.
But, I read That is not true in the case of oracle but it is true in the case of SQL Server.