PL/SQL :: Index Ignored While Using Update Statement?

Nov 26, 2012

I am having trouble figuring out why an update statment is ignoring the primary kiy index when performing an update through the application. The index IS used when the update is run from sql*plus or other sql tools.

The statement is very simple:
update ITEM_MASTER set COST = :1 where SMARTPART_NUM = :2;
ITEM_MASTER has unique, primary key index on SMARTPART_NUM

When I use OEM and other tools, I can see the index is not used in the exlain plan, and the query has a high CPU cost due to the full table scan. The table is analyzed, lately using 100%. Table rowcount is 229768

SELECT column_name, num_distinct, num_buckets, histogram, trunc(last_analyzed) ANALYZED FROM USER_TAB_COL_STATISTICS
WHERE table_name = 'ITEM_MASTER' AND column_name = 'SMARTPART_NUM';

COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM ANALYZED
------------------------------ ------------ ----------- --------------- ---------
SMARTPART_NUM 198417 254 HEIGHT BALANCED 25-NOV-12

The database is Oracle Database 10g Release 10.2.0.4.0 - 64bit Production (on Windows, standard edition)

Here are parameters related to optimizer:
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 10.2.0.4
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean TRUE

We've tried rebuilding the indexes then re-analyzing, dropping stats and locking at zero, using various sample sizes; none have any impact.

We wondered if the bind variables are causing this, though in testing outside the application, we tried pl/sql and sql batches to mimic the passing of values into the binds, and all still used the index.

Other than putting a hint into SQL in the application (we'd have to ask for a customization), how can I "fix" the database to use the index? In other words, how can I make the database see that the cost of a FTS is much higher than using the index, whenever it sees these updates (either from sql or the application)? This is the simplest example, but we have 4-5 SQL updates on this and other tables that are ignoring the indexes and using full table scans, so we'd like to fix it for all.

View 10 Replies


ADVERTISEMENT

Update Statement Versus Cursor Based Update

Sep 7, 2010

I have to update 20 and 60 million records of a table. The update statement are

1> 20 million recs
update mycustomer set update_time=add_months(sysdate,240) where seq_num = 1;

commit;

2> 60 million recs
update mycustomer set update_time=sysdate-seq_num where seq_num <> 1;

commit;

Q1> Is there any way to improve performance
Q2> Will parallel dml improve performance
Q2> Would a pl/sql cursor make any difference in speed.

View 1 Replies View Related

SQL & PL/SQL :: Update Statement With Join Only Update Matching Rows

Aug 17, 2010

This is my query:

UPDATE t_tt_hours a
SET a.sak_request = (
SELECT b.sak_request
FROM t_requests b, co c

[Code]...

The problem I am having is that it is updating all rows even when it is pulling back a null value for b.sak_request. I've tried adding b.sak_request is not null to the select statement like this:

UPDATE t_tt_hours a
SET a.sak_request = (
SELECT b.sak_request
FROM t_requests b, co c
WHERE b.nam_eds_tracking_id = c.id_dir_track_eds

[Code]...

but it doesn't seem to make a difference. The reason I need to do this is that the difference between where it matches with a valid (non-null) value is 396 rows vs. 12,484 rows which is too time consuming to run on my page.

View 9 Replies View Related

PL/SQL :: Update Multiple Columns With Single Update Statement

May 30, 2013

i am reading the columns value from different table but i want to update it with single update statement. such as how to update multiple columns (50 columns) of table with single update statement .. is there any sql statement available i know it how to do with pl/sql.

View 5 Replies View Related

SQL & PL/SQL :: How To Update Multiple Rows With Different Values Using Update Statement

Mar 21, 2011

I have one doubt about update command in sql. How to update the multiple rows with different values using update statment.

Eg:-

SQL> set linesize 500;
SQL> set pagesize 500;
SQL> select * from emp;
SQL> select empno,ename,sal from emp;
SQL> select empno,ename,sal from emp;

EMPNO ENAME SAL
---------- ---------- ----------
7839 KING 5000
7698 BLAKE 2850
7782 CLARK 2450
7566 JONES 2975
7654 MARTIN 1250

[Code]....

The above table contains 14 records. Now i would like to update the salary column with different values like

EMPNO SAL
===========
7839 18000
7698 20000
7782 5000
...
...
...
7934 25000

How to update above values with single update query.

View 11 Replies View Related

Performance Tuning :: Index With IN Statement

Jan 18, 2012

I have the following problem. When I used in the IN-Statement fixed values e.q. 197321,197322,197323 ..., the index i_tab2_index works fine (index range scan).

But when I used in the IN-Statement an Sub-Select, the index i_tab2_index doesn't work (fast full scan)!My scale indices and used Selects:

CREATE INDEX i_tab1_index ON tab1 ( datum, flag_inst );
CREATE INDEX i_tab2_index ON tab2 ( tab2Idx, kontro );
SELECT count(epidx) as rowAnz
FROM tab2
WHERE tab2Idx IN ( SELECT tab1IDX FROM tab1
WHERE datum BETWEEN '20120117' AND '20120117'
AND flag_inst = '1' )
AND kontro = '9876521'
[code]...

View 12 Replies View Related

Performance Tuning :: How To Use Index On (ON In MERGE Statement)

Apr 6, 2011

mbr has 60,000 rows and member has 60,000 rows approx. two tables have indexes on ssn, and citi_no on them.

PK of mbr : mbr_id
PK of member : mbr_id

other columns are not PK, and have no index on it.

I'm wondering why the statment doesn't use index while ssn and citi_no have index.

MERGE INTO mbr t
USING (SELECT mbr_id,citi_no
FROM member) a
ON (t.ssn = a.citi_no)
WHEN MATCHED THEN
UPDATE SET t.asis_mbr_id = a.mbr_id
where t.ssn not in(select ssn from mbr group by ssn having count(*) > 1)

View 19 Replies View Related

SQL & PL/SQL :: Update Each Row - Unique Index

Jun 23, 2011

I've got a table which has no unique identifier for a row. I have a field for an ID value but somhow there are duplicate values in this field.

Now I want to create an unique index over this field but therefore I have to remove the duplicate values.

So what I need is an update statement, maybe in a for each loop, which updates the ID field in each row starting with 1 and increasing the value.

In Informix there is a rowid value which is automatically set by Informix. But I think there´s no correspondent function/value in Oracle....

View 17 Replies View Related

TX - Index Contention On Update

Sep 3, 2012

I have a UPDATE statement which sets values on non-indexes columns and do not use indexed-column in where clause (so every column used in statement are not indexes). Lastly, I had some problem with database and saw in ASH report in TOP SQLs that update with events enq: TX - index contention and buffer busy waits. What does it mean? I read a lot of information about index contention, but always related to INSERT and DELETE. What has UPDATE to do with this contention?

View 3 Replies View Related

Select Statement Gives Different Results Using Bitmap Or Normal Index

Jan 10, 2012

we have a strange symptom in a database Oracle 11.2.0.2 EE. Following question comes from our application developers.

The following SQL statement:

SELECT
v.reporting_month,
sum(v.f_s) "REV_S",
sum(v.f_f) "REV_F",
sum(v.f_c) "REV_C",
[code].......

gives different result when we exchange the index ksr_valid_until_i on table kreditkarten_sets_rs. For some reasons we changed the index from bitmap to normal and are getting different results. Switching back gives us the same results as before. When we avoid the usage of this index in the statement than we are getting the same results as when we are using the normal index.

View 4 Replies View Related

PL/SQL :: How To Display Pseudo Static Index Using Select Statement

Jul 20, 2012

I have to display a 24 hours time as an index. (00:00 - 23:00) That is easy, if the data exists.

But, if the data is not regularly exists on specific hour (the time/hours is not regular, i.e: 00:00, 01:00, 05:00, 08:00, 10:00, 11:00, 23:00), then it is OK for a TABLE, but not for a CHART.

Here is the story:

I wrote an SQL statement that can "choose" what to display respectively to the the available time. The result will be displayed on the chart: Days or Time is used for X-Axis, and the respective Value on the Y-Axis.

Example:

+ if the data consists of "days" (i.e: the last 5 days from now, 16/07/2012, 17/07/2012, 18/07/2012, 19/07/2012, 20/07/2012), then the chart will show those dates on the X-Axis.

+ if the data consists of "hours" (i.e: 1 single day, from 00:00 hour - 23:00), then the chart shows hours on the X-Axis.

If the 'hours' are not regular, means: there is no data on specific hours, the query result is also not regular, means: the X-Axis-value 'jumps' irregularly.

Question:

Is it possible to query our own static value?

for example:

select 'hello' from dual --> result: "hello", 1 column, 1 row

But how to display this in multiple rows, i.e.:

Time
*****
0
1
2
3
4
5
...
...
23

Note: the X-Axis on the chart template can be only customized for 1 single mode, example: "Days", but of course it is not going to show up properly if the query result are in "Hours". It means: I have to find the workaround on the SQL Query.

DB: ORA 11

View 9 Replies View Related

SQL & PL/SQL :: Update Statement (lag)

Aug 10, 2011

I have a table zTest with several products:

create table zTEST
( PRODUCT number,
ID number,
Flag number,
FLAG_L1 number );

[Code]...

The field FLAG_L1 is the field FLAG with a Lag 1 (order by ID, partition by PRODUCT). I want to write the field FLAG_L1 in an update statement, how can I do this?

View 7 Replies View Related

UPDATE Statement Tuning

Dec 6, 2011

I have a below UPDATE STATEMENT and it is taking more than 1hr to complete. tune this to complete as quick as possible.

UPDATE A_TABLE A
SET FLG= 1
WHERE EXISTS (SELECT ID FROM B_TABLE B where A.ID=B.ID);

Count of the table A_TABLE --- 16,331,441
Count of the table B_TABLE --- 1,271,903

We have index on B.ID and A.ID

View 3 Replies View Related

SQL & PL/SQL :: Can A Procedure Be Used In Update Statement

Aug 2, 2011

PROCEDURE split_name (
full_name IN VARCHAR2,
name1 OUT VARCHAR2,
name2 OUT VARCHAR2,
name3 OUT VARCHAR2,
name4 OUT VARCHAR2,
name5 OUT VARCHAR2
)

the above is the procedure declaration. i want to use this function in updating the records in a table. this procedure is working fine in forms and it is splitting the names perfectly and i dont know how to use this in update statement as it is not returning anything like functions do.

View 4 Replies View Related

SQL & PL/SQL :: Get Old Value Of Column In Update Statement

Sep 17, 2007

While updating a table, is it possible to get the old value of a column. I need to get it inside a PLSQL block and not in a trigger.for example :-

DECLARE
l_amountNUMBER(5) := 0;
BEGIN
UPDATECustTransactions
SETamount = 150
WHEREcust_id = 5412
RETURNING amount INTO l_amount;
DBMS_OUTPUT.PUT_LINE ( 'l_amount= ' || l_amount );
END;

By using the RETURNING clause i am getting the new updated value. I need the old value for some processing. Do i have to explicitly query it before the update stmt? Pls reply.

View 6 Replies View Related

PL/SQL :: Row Locking During Update Statement?

Aug 9, 2013

Using Oracle 11gr2. If I call the following update statement from session A, session A will lock the row until a commit/rollback statement is issued from session A. If session B calls the same update statement and same row, session B will have to wait until the lock is released in session A. However, the application developers are speaking in terms of threads. Could it be possible that the update statement is called within the same session by multiple requests? If yes, could the case statement be evaluated without the row being locked which could lead to false results? Trying to keep this post brief. tableA has columnA (primary key, number) and columnB (number) 

{update tableAset columnB = case when columnB = 3 then   4  when columnB = 4 then   5   else  columnB    endwhere columnA = 6;}

Could 2 requests (almost at the exact same time) in the same session evaluate columnB as 3. The desired result would be the first request sets the column to 4 and the second request sets the column to 5.

View 4 Replies View Related

SQL & PL/SQL :: Update Statement By Joining 2 Tables

Mar 24, 2012

I have a table called test_renames that has two columns(new_name and old_name) and one row with the following values

NEW_NAME OLD_NAME
DRUG10 DRUG1

I have another table called SH0 that has 10,000 rows and the column of interest is called ITEM1. ITEM 1 contains the following distinct drug names:

DRUG1,DRUG2,DRUG3,DRUG4,DRUG5

Each DRUG has multiple rows associated with it.

I need to only update rows for DRUG1 and set the name from DRUG1 to DRUG10 for all rows where ITEM1 is DRUG1.

However I need to write this update statement by joining with the values in the test_renames table that contains the NEW_NAME and the OLD_NAME.

DRUG1 has 2,000 rows associated with it. The following SQL statement gives me 2,000 rows

(SELECT NVL(CHG.NEW_NAME, SH.ITEM1)
FROM test_renames CHG,sh0 sh
WHERE UPPER(SH.ITEM1) = UPPER(CHG.OLD_NAME)
AND UPPER(SH.ITEM1) IN (SELECT UPPER(OLD_NAME) from test_renames))

However when I run the following update statement, it appears that the SQL query updates all the 10,000 rows in the table instead of just touching the 2,000 rows for DRUG1. It does change all the rows for DRUG1 to DRUG10, but all the other ITEM1 values become null. What I need from the update statement is only chnage the rows for DRUG1 to DRUG10 and leave the other rows that I associated with DRUG2, DRUG3 etc. Here is my update statement.

UPDATE SH0 SH
SET ITEM1 = (SELECT NVL(CHG.NEW_NAME, SH.ITEM1)
FROM test_renames CHG
WHERE UPPER(SH.ITEM1) = UPPER(CHG.OLD_NAME)
AND UPPER(SH.ITEM1) IN (SELECT UPPER(OLD_NAME) from test_renames))

This update statement touches all 10,000 rows. I am looking to update only the relevant 2,000 rows for DRUG1.

View 2 Replies View Related

Forms :: Update Statement On Button?

Apr 16, 2010

I try to apply update statement on the button for the specific records, the command does executes if in the end i write execute_query it always asks for do u wanna save the records, yes or no. I want to remove that message. Also if i write commit it does remove that message but everytime i press the button it updates the old record fine but also adds one more entry for the same record.

View 5 Replies View Related

SQL & PL/SQL :: Update Statement For OLTP Table

May 14, 2013

I have a table TEMP_TEST_1 to be loaded with multiple rows which contains the available items for a store with their sequence numbers. I also have another table TEMP_INV_1 which holds a column for inventory count of the items, and this column is to be updated after loading the first table TEMP_TEST_1.

The table TEMP_TEST_1 is updated through all possible channels in OLTP. So while updating the inventory, which is the best way. My update should either add the total number of inserts per item into the TEMP_TEST_1 table's INVENTORY_CNT column i.e. existing value + count of new rows OR a full update by taking the complete row count (Grouped by item id) and update the INVENTORY_CNT column

--------------------------------------------------------------------------------
-- Initial setup SQL's, TEMP_TEST_1 is updates on OLTP through multiple channels
--------------------------------------------------------------------------------
CREATE TABLE TEMP_TEST_1 ( ITEMID NUMBER ( 4 ),
INVENTORY_CNT NUMBER ( 2 ) );
CREATE TABLE TEMP_INV_1 ( ITEMID NUMBER ( 4 ),
ITEMSEQ NUMBER ( 5 ) );
INSERT INTO
[code].......

--------------------------------------------------------------------------------
-- Inserts for new items in the store
--------------------------------------------------------------------------------
INSERT INTO
TEMP_INV_1
VALUES
( 1,
11 );
INSERT INTO
TEMP_INV_1
[code].......

View 4 Replies View Related

SQL & PL/SQL :: Update Statement Using Multiple Tables

Aug 26, 2010

I am issuing an update statement in which I am using multiple tables it is giving me an error " set keyword missing"

update E_CONT_DETAIL_NUMB_VALUE ecdnv, y_obj_category yoc, t_contact tc
set ecdnv.ContTPRecCount = 1000
where tc.default_category_id = (select primary_key from y_ojb_category where tree_position = 'CONT')
and ecdnv.detail_field_id=tc.default_category_id;

update E_CONT_DETAIL_NUMB_VALUE ecdnv, y_obj_category yoc, t_contact tc
*
ERROR at line 1:
ORA-00971: missing SET keyword

View 4 Replies View Related

SQL & PL/SQL :: Update Statement With NEW LINE Character?

Feb 14, 2005

My Table "demo" has a column "Combined_attributes". I want to update this column with some value having a new line character in it.

View 3 Replies View Related

SQL & PL/SQL :: How To Use DECODE For A Column In Update Statement

Apr 9, 2010

I have to conditionally update a set of columns in a table. If the column status_code_stage_3 IS NULL THEN I have to update the column status_code_stage_2. The below query is giving error:-

Test Table create scripts
CREATE TABLE test11( date_stage_3 date, reason_code_stage_3 varchar2(20),
reason_code_stage_2 varchar2(20), opportunity_date date )
INSERT INTO test11 values(sysdate,'reason1',NULL,sysdate)
INSERT INTO test11 values(sysdate,NULL,'reason2',sysdate)

[code]....

how to work out the update statement to use the conditional statement for columns.

View 2 Replies View Related

SQL & PL/SQL :: Substr And Instr In Update Statement

Nov 28, 2010

I have a table1 with columns serial no and name as below

serial no name
1 john paul
2 john victor

and i have another table1 with columns serialno,firstname and second name as below

serial no firstname secondname
1
2

I want to update my table2 with the name from table1 and divide and insert in first name and second name as below

serialno firstname secondname
1 john paul
2 john victor

i use the query as below

update table2 set
firstname = (select substr(name, 1, instr(name, ' ', 1) - 1)
from table1
)

but the above query not worked.

View 11 Replies View Related

SQL & PL/SQL :: After Update Statement Sql%NotFound Is Being Validated?

Jun 29, 2012

After an Update statement, Sql%NotFound is being validated. Sql%NotFound validates to true even though a record matching the update condition is found. Because of this, the procedure execution Exits. If the Sql%NotFound is commented, then the Record gets updated. which is validating Sql%NotFound condition to True.

View 11 Replies View Related

PL/SQL :: Merge Statement - Update Only When There Is Difference?

Feb 7, 2013

I have two tables have almost the same columns, how can I use merge statement to update the target table only when there is difference between source and target table. Is there any easier way not compare each column one by one? I am using Oracle 11.2

Here is the MERGE statement:

Merge into tb_trgt trgt using tb_src src
on (src.id = trgt.id)
when not matched then insert (trgt.id, trgt.nm, trgt.addr) values (src.id, src.nm, src.nm)
when matched then update set trgt.nm = src.nm, trgt.addr = src.addr
where trgt.nm <> src.nm or trgt.addr <> src.addr
;

Is there any easier way to specify the where clause in the NOT MATCHED? I don't want compare each column of the tables. Since I may have many columns in the tables.

View 3 Replies View Related

PL/SQL :: Update Statement In Join Query

Nov 16, 2012

I've seen this example numerous places, and tried to implement it, but I keep getting an "invalid identifier" error message, despite the fact that I've got the table and column specifically identified.For instance, my query reads like:

UPDATE tbl1
SET tbl1.EMPID =
(SELECT  tbl2.EMPIDA  FROM tbl2 
WHERE LOWER(tbl1.EMAILCOL) =  LOWER(tbl2.EMAILCOL2)
)
WHERE tbl2.EMPIDA IN ('Z1O435','S8M4722','M0D5156')
AND EXISTS
(SELECT tbl2.EMPIDA
FROM tbl2
WHERE  tbl1.EMAILCOL= tbl2.EMAILCOL2 );

But I'll keep getting flagged at the tbl2.EMPIDA column reference. I have not tried this in SQL Plus, just in TOAD, but it seems to repeatedly fail.I have had to dump records to standalone Access tables and link back to perform the updates.

View 12 Replies View Related

Update Statement Not Working - Invalid Identifier?

Apr 15, 2009

I am having trouble getting the following update statement to work.

update Team
set Names.Team_Name = "Not Yet Assigned"
WHERE
Team.ROE_PROCESS = 'G' AND

[code]...

The error message I am receiving is:
ORA-00904: "Department"."UI_WKLY_EARNS": invalid identifier

View 4 Replies View Related

Update Statement - Query Hanging Or Processing

Oct 6, 2010

I am running one update statement which is running almost one hour still no response.

I would like to know either query is processing or hanging(suppose to finish the update within few minutes).

Is there any way or sql to find either the statement(my update query) is running or hanging.

View 4 Replies View Related

Forms :: How To Insert And Update With Single Statement

Mar 5, 2012

I would like to insert a value if that value is not existing in the table (example for a column which contains date only new dates should be inserted and if the date already exists in the column then it needs to get updated )

example of scenario...

date s1 s2 s3

in the above if the date is new..it should get inserted with the appropriate slot no.(s1,s2,s3) if the date already exists it needs to update the no.in slot no.

View 8 Replies View Related

SQL & PL/SQL :: Using Optional Parameters To Fill Update Statement?

Jul 17, 2011

I need to use funtion optional parameters to construct an update statement in the funtion body. I have two scenarios, either both parameters are not null or the 2nd is null. Do I have to use

IF
(param2 is null)
THEN
udpate using param1 only
ELSE
update using both param1 and param2
END IF;
or is there a shortcut to do this?

View 15 Replies View Related







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