SQL & PL/SQL :: Rebuild All Indexes For The Tables In Control Table?
Mar 6, 2013
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;
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 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.
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 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.
--this for txn details CREATE TABLE txn_det( txnid NUMBER PRIMARY KEY, amount NUMBER, status varchar2(50), cust_id NUMBER); ----this for customer details CREATE TABLE cust_det( cust_id NUMBER PRIMARY KEY, cust_name VARCHAR2(50), cust_acc number(15));
--data to insert for customer table INSERT INTO cust_det VALUES(101,'Miller','12345');
INSERT INTO cust_det VALUES(201,'Scott','45678'); ----data to insert for txn table INSERT INTO txn_det VALUES('tx0045',123.00,'success',101);
INSERT INTO txn_det VALUES('tx0046',4512.50,'success',101);
insert into txn_det values('tx0049',78.12,'success',101);
INSERT INTO txn_det VALUES('tx0055',123.12,'success',201);
Now THE problem IS cust_det TABLE's cust_id coulmn may contain duplicate.So I thought OF adding THE txn_id COLUMN TO THE cust_det table but I know that encourgaes redundancy.
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 ;
We are on Oracle 10.2.0.4 on Solaris 10. There is a table in my production db that has 872944 number of rows. Most of its data is now unnecessary, we need to retain, based on a date column in the table just last one month's data and delete rest of the data. So after that the table will have just 3000 rows.
However as the table was huge earlier(872k rows prior to delete) , does the delete of data release its oracle blocks and does the size of the table reduce? If not, will it rebuild the table online (online redefinition) so that the query that does a full scan on this table goes faster?
I checked using an example table that just delete of data does not remove the oracle blocks - they remain in the user_tables for that table and cost of full table scan remains same. We have a query that does the full table scan so I am thinking that after this delete I should do an online table re-definition , is that the right decision?
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]...
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?
I want drop some old partitions from big table but this will not increase free space on disk. So I want to move table with indexes to anothers tablespaces. What is the fastest way to do that? ALTER TABLE ... MOVE TABLESPACE ...? CTAS ? Or something else?
I have a table like MyTab(a int, b int), and I am required to create a primary key index and a non-unique index on this table using columns (a,b) in a specific table space.
The back end database is Oracle 10g.
Here's what I have tried so far, needless to say, unsuccessfully.
Alter Table MyTab Add Constraint c_1 primary key (a, b) Using Index (Create index mytab_idx on MyTab(a, b)) Using index tablespace results_index
So my question are:
1. is this is possible? if so, what is the correct syntax. 2. assuming it is possible, using this sort of construct before? it appears to be conflicting and inconsistent to me.
create index datastores_text on test_url_search ( doc ) indextype is ctxsys.context parameters ( 'Datastore TEST_URL' );
SELECT SCORE(1),DOC from TEST_URL_SEARCH WHERE CONTAINS(DOC, 'London', 1) > 0
This works. i like to more
e.g. multi words search e.g. ' London Games' where if London or games are present it return a URL with a score,
or
Search in the Chinese.
The only way I can see to do this is create different indexes as the attributes for 'URL_DATASTORE' are limited. If I can what does the 'Contains' statement look like?
finally add a HINT /*+ gather_plan_statistics push_pred(TABLE) */ and the result was not very good in this case, but improved the resolution of the view, the rest got worse
SQL Statement from editor: SELECT /*+ gather_plan_statistics push_pred(o10475761) */ COUNT(o10475761.SUC_ID) FROM GAR_DW.ARTICULOS o10475528, GAR_DW.EMPRESAS o10475602, GAR_DW.L_DIA o10475639,
