Fastest Way To Move Table With Indexes?

Jan 18, 2011

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?

View 4 Replies


ADVERTISEMENT

Server Administration :: Move Indexes (around 300) From Data To Index Tablespace

May 18, 2011

I need to move my indexes (around 300) from data tablespace to index tablespace. What's the best way to do it?

View 9 Replies View Related

SQL & PL/SQL :: Fastest Method For One To One Update

May 9, 2013

We have a requirement to update one column as given in the below mentioned pl/sql block.The challenge is to update the a large volume of data every day.Generally this kind of one to one update will takes long time (45 min approxmately) We need a solution that will be able to update 10 million records in few minutes.

begin
for i in (select t3.event_id, t1.header_id from
table_1 t1, table_2 t2, table_3 t3
where t1.header_id=t2.header_id and t2.entity_id = t3.entity_id)
loop
update table_1 set event_id = i.event_id
where header_id =i.header_id;
end loop;
commit;
end;

View 5 Replies View Related

Convert Indexes Of Non-partitioned Table To Hash Partitioned Indexes?

Sep 10, 2012

RDBMS - 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?

View 7 Replies View Related

Server Administration :: Move Partitioned Table Between Table Spaces Of Different Block Size?

Apr 4, 2011

I was about to move some tables from one table space to another but it seems it is not possible to move partitioned tables between table spaces of different block sizes.

So far the only option I have is to export and then import back the data.

know if there is any way to move a partitioned table between table spaces of different block size?

View 14 Replies View Related

SQL & PL/SQL :: How To Move Table To Customer DB

Apr 23, 2012

We want to provide data dump from 3 tables Like A,B,C.

In C we have more than 3 million records.

What is the best way to move this data from our DB to customer DB.

Customer is running on MySql

View 12 Replies View Related

PL/SQL :: Multiple Indexes Using Same Columns On One Table?

Oct 4, 2012

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.

View 4 Replies View Related

Text :: Add Multi Indexes On One Table?

Jul 22, 2012

I been doing a little testing with indexing the contents of URLs (database v11.2.x.x).

ctx_ddl.drop_preference('TEST_URL');
ctx_ddl.create_preference('TEST_URL','URL_DATASTORE');
ctx_ddl.set_attribute('TEST_URL','Timeout','3600');

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?

View 0 Replies View Related

SQL & PL/SQL :: Oracle - How To Move Table To Another Schema

Feb 6, 2008

How can I move a table to another schema in Oracle?”

View 33 Replies View Related

SQL & PL/SQL :: Move Partition Of One Fact Table To Another?

Aug 30, 2011

I have two fact table and both are partitioned on same key and have same columns. Is it possible to move the partition of one fact table to another.

View 2 Replies View Related

SQL & PL/SQL :: Move Data From One Partition To Other In A Table

Jul 22, 2010

I want to move data from one partition to another partition in a same table.

Ex: Table A has Partitions P1 and P2. I want to move data from P1 to P2.

Is this is possible?

View 3 Replies View Related

Performance Tuning :: What Does Analyzing A Table Do For Indexes

Sep 26, 2012

what analyzing a table does to existing indexes? Do I need to rebuild the indexes after dbms_stats.gather_table_stats command ?

View 4 Replies View Related

SQL & PL/SQL :: UNION ALL - View With Similar Indexes Each Table?

Jul 5, 2012

I have this sql (generated by discoverer plus), and work fine:

SELECT COUNT(o10475761.SUC_ID)
FROM GAR_DW.ARTICULOS o10475528,
GAR_DW.EMPRESAS o10475602,
GAR_DW.L_DIA o10475639,

[code]...

We need change the table stock_por_sucursal for a view like this, with similar indexes each table

select * from stock_por_sucursal_old
union all
select * from stock_por_sucursal_new

and the result is not good:

SQL Statement from editor:

SELECT COUNT(o10475761.SUC_ID)
FROM GAR_DW.ARTICULOS o10475528,
GAR_DW.EMPRESAS o10475602,
GAR_DW.L_DIA o10475639,
GAR_DW.V_STOCK_POR_SUCURSAL o10475761,

[code]...

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,

[code]...

View 2 Replies View Related

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;

[code]....

This contains the 30 schema names.

SELECT owner_name FROM global_bu_mapping;

View 4 Replies View Related

Infrastructure DBA To Move Table With Size 126GB?

Jul 16, 2010

I have requested the Infrastructure DBA to move a table with size 126GB(as shown in the stats/size tab in TOAD) from one tablespace to another.This is free the huge space in the first tablespace which i wanted to use for creating another table.

But when the table is moved to another tablespace, surprisingly for me, i saw that the sizeof the table has come down to 8 GB from 126 GB.Point to be noted is that everyday there are physical deletes happening on the table.

View 6 Replies View Related

SQL & PL/SQL :: Move Data From Varray Column To A Table

Mar 28, 2013

I have to move the data from a Varray column to a table.

Lets say I have created a Varray as described below,

create or replace TYPE "BT_TYPE" AS OBJECT (
R_ID NUMBER,
P_EVENT VARCHAR2(100))
/

create or replace TYPE "BT_VR" AS varray(20) of BT_TYPE
/

And I have a used this Varray as the column datatype in table

Create table BT_MASTER(
BT_ID_SEQNUMBER(5),
BT_DETAILBT_VR);

And this table contains data.

I want to move the data in the VARRAY column BT_DETAIL to another table. I have create a staging table BT_STG which contains a surrogate key column and the columns from the VARRAY. I am creating this staging table at run time.

Create Table BT_STG(
BT_STG_ID NUMBER(5),
R_ID NUMBER(5),
P_EVENT VARCHAR2(100)
);

One way to create this staging table is to query the data dictionary views to get the VARRAY object's columns, datatyeps and create it.

Is there any other simpler way by which I could create a table similar to a VARRAY/Object?

Something similar to,

create table test as select * from BT_VR

View 4 Replies View Related

SQL & PL/SQL :: How To Copy Constraints (primary / Foreign Key / Indexes) To A Table

Mar 11, 2011

I have created table as below

create table emp_temp as select * from emp;

the table is created, but the constraints are not copied. Is there any way to copy all the constraints.

View 3 Replies View Related

Performance Tuning :: Difference Between Move And Shrink Table?

Jul 22, 2011

What is different between move table and shrink table?

View 2 Replies View Related

How To Move Offline Table Partition In Oracle 11gR2

Sep 17, 2012

OS=Windows 2003
DB = 11gR2

We got a request to take offline for few unused partitions and move it to a another drive (lease used).Please set the following partitions offline in PreProd:

MONTH_20110901
MONTH_20111001
MONTH_20111101
MONTH_20111201
MONTH_20120101
MONTH_20120201

Please move the offline partitions to the E disk.How can we do this ?

View 9 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 :: Dynamically Filling Data In Table And Rebuilding Indexes Gathering Stats

Jan 26, 2012

I have created a table like below-

PROMPT CREATE TABLE tst_fetch_vendor_data
CREATE TABLE tst_fetch_vendor_data (
vendor_data_seq_no NUMBER NOT NULL,
study_seq_no NUMBER NOT NULL,
vendor_record_seq_no NUMBER NOT NULL,
control_column_seq_no NUMBER NOT NULL,
resolved_value VARCHAR2(4000) NULL,
original_value VARCHAR2(4000) NULL,
transaction_user VARCHAR2(30) NOT NULL,
[code]....

Its just a temporary table, in which data comes and goes. I am using this in middle of a process.I am using it in a process like below--

--EXECUTE IMMEDIATE 'TRUNCATE TABLE TST_FETCH_VENDOR_DATA DROP STORAGE';

insert /*+ append */ into tst_fetch_vendor_data
(select * from vendor_data vd
where vd.control_column_seq_no in
(select control_column_seq_no from temp_control_column));
dbms_stats.gather_table_stats('EPDSYSREP','TST_FETCH_VENDOR_DATA',ESTIMATE_PERCENT=>100,
METHOD_OPT=>'for all indexed columns size auto',CASCADE=>True);

code to use that table..This table can contain data from 0 to 108000000 records.Now my questions are-

1. How much should I select sampling size (currently its 100%)Can I use dbms_stats.auto_sample_size, what will be the effect?

2. dbms_stats is good approach or should I use dynamic sampling.

3. what about the approach using CTAS instead of inserting data through insert.

4. What about pl/sql table with index or with clause query.

5. Do I need to rebuild index after inserting data into table.

View 3 Replies View Related

Forms :: When User Change / Delete Any Record / Row In Forms Data Automatically Move To Other Table

Dec 25, 2011

when a user change or delete any record or row in forms data automatically move to other table because i want to compare old and new record.

View 8 Replies View Related

Similar Table Structure Having Similar Indexes?

Sep 24, 2010

I do have problem with a table which have same structure of other table with indexes created being the same. Both tables have partions & subpartitions. Let us consider two tables table1 & table2. The problem i face is the index for table1 not being used when its being joined with other respective tables in a query. Whereas for table2 its working perfectly and cost is also less. I have used the explain plan and compared and query timing also takes more time. what might be causing it?

View 2 Replies View Related

Indexes In Oracle

May 9, 2011

this is just a generic question. If a table has multiple Indexes on it say a table of 20 columns where 8 columns have indexes on it (each columns out of the 8 has an index on it and out of which three columns contain unique indexes on it).

If i run a query on this table where only two columns are specified in the where clause are the remaining 6 indexes on the table scanned during query execution.

View 1 Replies View Related

Find Ddl For Any Indexes?

Oct 9, 2012

on 11g R2 on Win 2008

How to find ddl for any indexes built on MY_VW ?

View 6 Replies View Related

Update Global Indexes?

Aug 15, 2011

If I have owner, table_name is there a query I can issue that will tell me if I have to add the "update global indexes" clause when dropping a partition from a table?

View 1 Replies View Related

SQL & PL/SQL :: Indexes On Materialized View

Mar 16, 2011

Why we can create indexes only on materialized view and not on normal views?

View 8 Replies View Related

SQL & PL/SQL :: Locking And Foreign Key Indexes

Sep 1, 2013

Say we have an employee(id_emp) table with a primary key on id_emp. We have also some history tables emp_stuff with columns say (id_emp, dat_event, some_stuff) with primary key id_emp, dat_event.

This means that we have a unique index on (id_emp,dat_event). We also have a foreign key id_emp that references employee(id_emp). When we update id_emp on employee, we still have a lock on emp_stuff. According to this (end of the page) :

Quote:So, in short, with releases prior to Oracle Database 11g Release 1, you will want an index on the foreign key of the child table if you do any of the following:

Update the parent table primary key
Delete from the parent table
Merge into the parent table

So is id_emp in emp_stuff considered as indexed (through the unique index of the primary key) or do we have to add an explicit index
like this CREATE INDEX emp_stuff ON emp_stuff(id_emp) to avoide child table locks?

View 33 Replies View Related

SQL & PL/SQL :: Using Function-based Indexes In SE?

Mar 29, 2012

I have noticed a very questionable phrase on an article updated in 2011: "Oracle SE may allow you to create a function-based index, but you must pay for an EE license to use FBI's." [URL] Is this true? I have tested a FBI on my SE and works just fine.

Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
SQL> create table t ( col varchar2(10));
Table created.
SQL> create index t_idx on t(upper(col));
Index created.
SQL> insert into t values('a');

[code]....

View 11 Replies View Related

SQL & PL/SQL :: Indexes For Query Performance?

Mar 16, 2010

Lot of my tables are just for join purpose , a table with just with two columns both FK , I did not add any composite PK to this table, lot my queries uses this table , because it does not have composite PK will the query be slow ? what I have to do to increase performance querying with such tables ?

View 5 Replies View Related







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