PL/SQL :: Index On A Staging Table

Jul 10, 2012

I have 2 similar tables. Same columns. Data inserted to second table from first for a condition and first table is truncated. Then data moved from second to first and second table truncated. Second table is to store data for a small period of time. First table has indexes. Do I need any index on second table?

The query on second table is always insert into second select * from first. Similarly insert into first select * from second.

View 3 Replies


ADVERTISEMENT

SQL & PL/SQL :: Pull In Last Loaded Data From Staging Table To Target Table?

Mar 9, 2011

I have a staging table and a target table. How do I pull in last loaded data from staging table to target table?

View 4 Replies View Related

SQL & PL/SQL :: Exchange Partitions Between Actual Table From It's Corresponding Staging Table

Nov 26, 2012

We have a table with interval partition. This table is accessed very frequently. We are suppose to exchange partitions between this actual table from it's corresponding staging table.

In order to keep the newly created partitions empty, is there a way to restrict other applications from using it before we push data from staging table to it's actual table.

View 4 Replies View Related

SQL & PL/SQL :: How To Have Procedure Populate Staging Table Primary Key

Jun 6, 2012

I've a staging table STG_TABLEA which has a primary key discount_seq_no.

I am creating a pl/sql procedure to populate a primary key (discount_seq_no) with a database sequence. The intent is to keep this populated with next value incrementing by 1.

I am using Oracle 11.2.0.2 version.

I've put together the below code, not sure on next steps...

BEGIN
UPDATE STG_TABLEA
SET A.DISCOUNT_SEQ_NO = "INSERT A SEQUENCE HERE AND KEEP INCREMENTING the seq value by 1
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
RAISE;
END;

View 5 Replies View Related

Server Utilities :: SQL Loader Loads All Fields With Double Quotes Into Staging Table

Aug 25, 2010

I am having a similar problem like above ONLY in UNIX box where my datafile is delimited by "|". The last field is ITM_CMNT declared as VARCHAR2(60) in Oracle. When I have exactly 60bytes in the last field it rejects the record saying actual 61 and max allowed is 60. If i reduce it to < 60bytes then it is stored as a value enclosed with double quotes. The enclosing double quote is on the next line.

"PROC,RAM,FLPY,HD,ACT MTX CLR DSP,D/PCMCIA,TRKBAL,LIT ION BA"

Expected: the one below is exactly 60bytes.

PROC,RAM,FLPY,HD,ACT MTX CLR DSP,D/PCMCIA,TRKBAL,LIT ION BAT
LOAD DATA
INFILE *
INTO TABLE TMPTLI_LAWSON_ITM_MST
TRUNCATE
FIELDS TERMINATED BY "|"
(ITM_NO, HAZ_MAT_CD, ITM_SHRT_DS, ITM_SON "TRIM(:ITM_SON)", ADDED_DT DATE "YYYY-MM-DD",
AVL_CD , ITM_CST_AMT, ITM_SLL_AMT,
EXCHG_PRC_AMT , ITM_UOM "TRIM(:ITM_UOM)",
PCK_QTY INTEGER EXTERNAL, SPC_HNDL_CD "TRIM(:SPC_HNDL_CD)", EFF_DT DATE "YYYY-MM-DD",
ITM_CMNT "TRIM(:ITM_CMNT)")

View 4 Replies View Related

PL/SQL :: Index Range Scan And Table Access By Index Rowid Versus Table Access Full

Oct 5, 2013

Let's consider such table that all rows fit into single block:

SQL> create table test as select rownum id, '$'||rownum name from dual connect by level <= 530;
Table created.
SQL> create index i_test on test(id);
Index created.
SQL>
SQL> begin

[code].....

why does approach with full scan take longer even if table occupies only one data block? PS. 11gR2

View 8 Replies View Related

Performance Tuning :: Local Index Versus Global Index On Partitioned Table

Jun 28, 2011

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 Related

Performance Tuning :: Force Index If Table Not Using Index?

Aug 9, 2013

How to force an index if the table not using the index?

View 10 Replies View Related

Performance In Inserting Data From Staging

Feb 13, 2013

I am facing problem while inserting data into Core Table from staging (both Core Table and Staging are on same database but different schema)

I am using below command:

INSERT /*+ APPEND PARALLEL("CORE TABLE", DEFAULT, DEFAULT) */ INTO
SELECT DATA from Staging

CORE TABLE is quit big contains millions of record partition on date and having old stats of 2007. Data fetching from staging is very fast appx 1 million record in 2 mins. we are inserting one day data daily into CORE TABLE from staging and its taking 3 Hrs.

View 2 Replies View Related

Replication Of Production Data To Staging

Aug 30, 2011

We have Development, Staging/UAT ( installed on XX.XX.XX.10 ) and Production ( installed on XX.XX.XX.20) Environment respectively. I have queries regarding getting the data from Production environment into Staging environment. The overall PROD database size is around 250 GB.

STAGING DATABASE DETAILS

SID : STG_DB
Staging Schema Name :schema_UAT
Replication Schema Name :schema_PrdReplica ( This is the schema where the production data gets loaded daily)

PROD DATABASE DETAILS

SID : PROD_DB
Prod Schema Name : schema_PROD

What is happening now:
----------------------------
There is a script (Stored Proc) written on staging ( STG_DB.schema_PrdReplica ) environment which executes daily in NIGHT and does replication. Currently we use DBMS_DATAPUMP to get the ENTIRE data/Meta Data (Everything) from Production to Staging. It is ta king significantly more time. It takes approx 8 Hours to replicate the everything from PROD_DB.schema_PROD to STG_DB.schema_PrdReplica

What I am expecting :
-----------------------------
I want to reduce the replication time.

I have heard about Level 0 (Full BackUp ) and Level 1 ( Incremental Cumulative ) Backups in RMAN. I am planning to take PROD_DB.schema_PROD Full Backup (Level 0) on Sunday and will restore that on STG_DB.schema_PrdReplica immediately. And on weekdays ( Mon - Fri ) I will take Level 1 ( Incremental Cumulative ) and will restore that on STG_DB.schema_PrdReplica

I am assuming by doing so, the overall replication time will be reduced. How can I implement this with script assuming that two different servers are on different machines.

View 1 Replies View Related

Windows :: OUI-10133 / Invalid Staging Area

Oct 5, 2005

I have a prob installing Oracle 10g on Window 98 SE :

OUI-10133:Invalid staging area.

There are no top level components for Windows 98 available for installation in this staging area.

View 8 Replies View Related

Real Application Clusters :: How To Export Database Statistics From Production To Staging

Jun 7, 2013

i am using 11.2.0.3.0 version db on Linux x86 64-bit...how to export the database statistics from Production to staging .

View 9 Replies View Related

Server Administration :: Reorganize A Table And Index After The Deletion Of Records From Table?

Feb 7, 2012

We deleted millions of records from a table.

1.Is it necessary to reorganize a table and index after the deletion of records from table ? Because i see some change in table size after table and index reorganization.

2.Will re org table and index improve the database performance ?

View 7 Replies View Related

SQL & PL/SQL :: Full Table Scan On Index Table

Feb 3, 2012

when i am Executing the following statement

SELECT DISTINCT EXPOSURE_REF FROM KBNAS.VW_EXPOSUREDETS_FOR_CCYREVAL
WHERE EXPOSURE_CURRENCY='THB' AND BASE_TXN_CCY='USD' AND BRANCH_CODE='7000'
AND (REVAL_STATUS='O') AND CONV_RATE<>'62' AND (EXPOSURE_AMOUNT<>0)
UNION
SELECT DISTINCT ED.EXPOSURE_REF FROM KBNAS.EXPOSURE_DETAILS ED,
[code].....

I have attached DDL for table EXPOSURE_DETAIL(PARTITION),LEDGERCARD,LEDGERCARDDETAILS, DDL for INDEX on those tables and DDL for Views..

Issue: we have created the Indexes but when we check the explain plain .. full table scan is going on..I have attached the explain plan ..

View 11 Replies View Related

PL/SQL :: Load Data From Index By Table To Table

Jun 27, 2013

We need to load data from index by table to table.Below code is working fine. 

declare
query varchar2(200);
Type l_emp is TABLE OF emp%rowtype INDEX BY Binary_Integer;
rec_1 l_emp;
begin

[Code]....

But data from source table and target table is dynamic.Ex:In above code, emp(source) and target table is emp_b are static. But for our scenario is depends on the source table , target would change as below.If source is emp then target is emp_bIf source is emp1 then target is emp_b1 ............ 

create or replace procedure p(source in varchar2, target in varchar2)
as
query varchar2(200);
source varchar2(200);
Type l_emp is TABLE OF emp%rowtype INDEX BY Binary_Integer;
rec_1 l_emp;

[Code]....

Its throwing. How to implement this scenario .

View 2 Replies View Related

SQL & PL/SQL :: Use Of Table Index?

Feb 10, 2012

Am pasting the sample code here, which i got from some site..

DECLARE
TYPE population_type IS TABLE OF NUMBER INDEX BY VARCHAR2(64);
country_population population_type;
continent_population population_type;
howmany NUMBER;

[code]...

Here we are fetching indexes (like Antartica/Australia) from these two statements continent_population.FIRST or continent_population.LAST. If there are three or more indexes in the same table, how to fetch all of them?

I have tried using this, but doesnt work because loop variables are by default integers:

for i in continent_population.FIRST .. continent_population.LAST loop
dbms_output.put_line('i:'||i);
end loop;

View 2 Replies View Related

How To Partition And Index The Table

Oct 24, 2011

how to partition and index my table for a special problem.

The table:

CREATE TABLE TEST (
ID NUMBER PRIMARY KEY,
U_VALUE NUMBER NOT NULL, -- Ranges from 0 - 30.000.000
O_VALUE NUMBER NOT NULL, -- Ranges from U_VALUE - 30.000.000
CREATE_TS TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
UPDATE_TS TIMESTAMP NOT NULL,
ITEM_TYPE NUMBER NOT NULL --<< Only 4 different values >>
);

As you can see, U_VALUE is ALWAYS lower than O_VALUE.I need to have the CREATE_TS in either main- or subpartition do drop the partitions after some time so i don,t have to use DELETE statements. The table has 360 millions rows.

The application has only 8 query which will always use a WHERE clause like this:

SELECT * FROM TEST
WHERE U_VALUE <= :1 AND O_VALUE => :2 AND ITEM_TYPE = :3

1. Is there any good technique how to create a good index for the queries (application will execute 120 queries per second)?

2. how to partition this table?

View 2 Replies View Related

SQL & PL/SQL :: How To Know Index Is Created On Table Or Not

Jun 30, 2011

how to know weather procedure is working or not in a database ..

how to know index is created on table or not.

View 2 Replies View Related

SQL & PL/SQL :: NULL Index Table Key Value

Sep 26, 2010

We are getting the below error on a piece of code that chas been functioning fine for over 4 years..

ORA-06502: PL/SQL: numeric or value error: NULL index table key value
ORA-06512: at "pd.pf33", line 148

The line it's pointing at is merely a FOR loop that opens a cursor (select query) and loops through the items.

FOR rec IN csr_cash
LOOP
END LOOP

This piece of code has been functioning fine for years.. and never got this kind of error on a for loop. Could it be something internal to Oracle/underlying memory issues?

View 8 Replies View Related

SQL & PL/SQL :: Two Index Exist On Same Table

Apr 19, 2011

Can there be an impact on performance if below two index exists on the same table:

t_idx(col1,col2, nvl(col7,col6));
t_idx1(col1,col2,nvl(col6,col7));

View 5 Replies View Related

Text :: Index For Domain Index With Composite Domain Index (CDI) Very Slow

Jun 27, 2012

I 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.

View 5 Replies View Related

What SQL Command To Find Index Of A Table

May 14, 2007

1) how to find a primary key of a table in oracle, for example if I want to drop a index in table , how do I find what is the primary key in a table so that I can issue that command.

P/s: I don't have OEM installed so I must use SQL command

Example of dropping the index
ALTER TABLE table_name
drop CONSTRAINT constraint_name;

For example:

ALTER TABLE supplier
drop CONSTRAINT supplier_pk;

View 2 Replies View Related

Create Reverse Key Index On Table

Feb 25, 2013

difference between the ways we create Reverse Key Index on the table.The two ways are as below:

1) CREATE INDEX <Index_Name> ON <Table_Name>(<Column_Name) REVERSE ;
2) CREATE INDEX <Index_Name> ON<Table_Name>(REVERSE(<Column_Name>)) ;

Which one is the appropraite one.
Are both going to act in the same way.

I created index in both ways one by one on the table.But when I fire the select statement against the same table, The explain plan doesn't show any cost and it shows the full table scan in both ways.The select statement used is as below...I want to compare the column with string RBO(i.e('RBO%') at the start.

SELECT *
FROM <Table_Name>
WHERE REVERSE (<Column_Name>) LIKE '%OBR' ;

The select statement is giving me the correct result. Only the explain plan is showing Full Table Scan.can we use this reverse with IN operator also in the same way by reversing the values.

View 3 Replies View Related

SQL & PL/SQL :: Bulk Insert In Index Table

Jul 8, 2013

why bulk insert is not possible in a table which has index?

View 9 Replies View Related

SQL & PL/SQL :: Create A Table With Constraint And Index

Apr 7, 2010

I use Oracle 10.0.2.0.1.If I create a table with constraint key; after that I create an unique index key, I got an error. Does it mean when I create a table with constraint the unique index are automatically created and I could not create index key as I did as below?

create table test_const(ename varchar2(50) not null,
key_num number not null,
descr varchar2(100),
constraint constraint_test_const unique (ename, key_num));
create unique index test_const_idx on test_const
(
"ENAME","KEY_NUM"
)
tablespace tmp_data;

Error report: SQL Error: ORA-01408: such column list already indexed 01408. 00000 - "such column list already indexed" added [pre] tags by Sriram

View 3 Replies View Related

SQL & PL/SQL :: Create Table As Select With Index

Nov 10, 2010

Is it a possible to create table using clause below together with index ?

create table the_table
as
select col1, col2 from table2

I got procedure which create a table in the schema B. The procedure is called from schema A. But when I write into procedure query for create index
then I got a error:

ORA-01031: insufficient privileges when
...executing

Therefore I think about to create table together with index.

begin
B.proc.cre_table;
end;

View 6 Replies View Related

SQL & PL/SQL :: Use Of Index For Searching Data In Table

Mar 5, 2010

i want to know the use of index for searching data in table...tell answer to my query with table example....

View 2 Replies View Related

SQL & PL/SQL :: Index Of Table Type Object

Apr 4, 2012

I have a table type object loaded (tto) with data and am using it as :

SELECT tto.B, tto.C
FROM TABLE(tto)
WHERE tto.A = <some value>;

This is working fine. BUT, can I also select the index of each element too along with other fields of each element ?

View 16 Replies View Related

How To Disable Index In Partition Table

Aug 11, 2010

I am trying to disable the index i am getting the error like

ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option

the table have partition. how to disable the index in partition table.

View 1 Replies View Related

SQL & PL/SQL :: Drop Index Which Has Constraint On Table?

May 18, 2011

How to drop an index which has a constraint on the table?

while droping the index on the table wheather i should remove the

constraint or disable the constraint or without droping the

constraint

View 1 Replies View Related







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