Home » RDBMS Server » Performance Tuning » Selection/Updation from a table based on condition (Oracle 10g)
Selection/Updation from a table based on condition [message #404849] Sun, 24 May 2009 23:39 Go to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi,
I have to update the column PAYFLG based on the condition that the testcode 'FBS' and 'LIP' comes under 'S12' only.
So objective is to check the transactions which have the TESTCODE of S12,
FBS and/or LIP, with matching ME Code, Exam Date, , Appl. No. and Source as 'LA'
thus retaining the transaction which has S12 as TestCode and cancelling
the other two across the transactions.

The logic is to update the PAYFLG to 4 (which means Cancelled) when for a particular MECODE and APPLNO
the TESTCODE IS 'S12' as well as 'FBS' or 'LIP'.

So If a transaction has TESTCODE as 'S12' then if the same transaction has 'FBS' or 'LIP' too,
then those transaction PAYFLG need to be updated to 4. In case they have no 'S12' then they will remain as they are.

I have given the create table statement with some inserts.
CREATE TABLE ME_TEST_DETAILS
(
APPLNO VARCHAR2(15 BYTE) NOT NULL,
TESTCODE VARCHAR2(3 BYTE) NOT NULL,
MECODE VARCHAR2(8 BYTE) NOT NULL,
DATEOFEXAM DATE NOT NULL,
PAYFLG NUMBER(2) NOT NULL,
SOURCE VARCHAR2(10 BYTE)
)

INSERT INTO ME_TEST_DETAILS (APPLNO, TESTCODE, MECODE, DATEOFEXAM, PAYFLG, SOURCE) VALUES (
'60665588', 'RUA', '00000201', TO_Date( '03/10/2005 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 7, 'LA');

INSERT INTO ME_TEST_DETAILS (APPLNO, TESTCODE, MECODE, DATEOFEXAM, PAYFLG, SOURCE) VALUES (
'60665588', 'S12', '00000201', TO_Date( '03/10/2005 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 7, 'LA');

INSERT INTO ME_TEST_DETAILS (APPLNO, TESTCODE, MECODE, DATEOFEXAM, PAYFLG, SOURCE) VALUES (
'60665588', 'LIP', '00000201', TO_Date( '03/10/2005 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 7, 'LA');

INSERT INTO ME_TEST_DETAILS (APPLNO, TESTCODE, MECODE, DATEOFEXAM, PAYFLG, SOURCE) VALUES (
'60665588', 'FBS', '00000201', TO_Date( '03/10/2005 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 7, 'LA');

INSERT INTO ME_TEST_DETAILS (APPLNO, TESTCODE, MECODE, DATEOFEXAM, PAYFLG, SOURCE) VALUES (
'90010062', 'FBS', '00000201', TO_Date( '03/10/2005 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 7, 'LA');

INSERT INTO ME_TEST_DETAILS (APPLNO, TESTCODE, MECODE, DATEOFEXAM, PAYFLG, SOURCE) VALUES (
'90010062', 'RUA', '00000201', TO_Date( '03/10/2005 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 7, 'LA');

INSERT INTO ME_TEST_DETAILS (APPLNO, TESTCODE, MECODE, DATEOFEXAM, PAYFLG, SOURCE) VALUES (
'90010062', 'S12', '00000201', TO_Date( '03/10/2005 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 7, 'LA');

INSERT INTO ME_TEST_DETAILS (APPLNO, TESTCODE, MECODE, DATEOFEXAM, PAYFLG, SOURCE) VALUES (
'40034021', 'FBS', '00000201', TO_Date( '03/10/2005 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 7, 'LA');

If you see the data has LIP and FBS in appl_no = '60665588'. So PAYFLG needs to be updated to 4 for 'LIP' and 'FBS'.
Also for applno '90010062', the PAYFLAG needs to be updated to 4 for testcode = 'FBS'

But for applno '40034021', the PAYFLG won't be updated as it doesn't have any 'S12'.

I have written this Update statement, but not sure if its the best method to do it as there is a cartesian join which might make it slow.

update me_test_details set payflg = 4 where rowid in( select rid from( 
select b.rowid rid, b.mecode, b.testcode, b.dateofexam, b.applno, b.payflg, b.source 
  from me_test_details a, me_test_details b
 WHERE UPPER (a.SOURCE) = 'LA'
                   and a.mecode= b.mecode
                   and a.applno = b.applno
                   and a.testcode = 'S12'
                   and b.testcode in ('FBS', 'LIP')))
 


Please help me in this,

Mahi

[Updated on: Mon, 25 May 2009 00:19]

Report message to a moderator

Re: Selection/Updation from a table based on condition [message #410401 is a reply to message #404849] Fri, 26 June 2009 15:55 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
You can at least eliminate one level of subquery by removing your outer wrapper and just selecting the rowid from the inner query. You might also compare to usage of exists. Please see the demonstration below that shows that using exists runs a little faster with a simpler plan with fewer stops. This assumes that you have an appropriate index and current statistics.

SCOTT@orcl_11g> -- starting data:
SCOTT@orcl_11g> SELECT * FROM me_test_details
  2  /

APPLNO          TES MECODE   DATEOFEXA     PAYFLG SOURCE
--------------- --- -------- --------- ---------- ----------
60665588        RUA 00000201 10-MAR-05          7 LA
60665588        S12 00000201 10-MAR-05          7 LA
60665588        LIP 00000201 10-MAR-05          7 LA
60665588        FBS 00000201 10-MAR-05          7 LA
90010062        FBS 00000201 10-MAR-05          7 LA
90010062        RUA 00000201 10-MAR-05          7 LA
90010062        S12 00000201 10-MAR-05          7 LA
40034021        FBS 00000201 10-MAR-05          7 LA

8 rows selected.

SCOTT@orcl_11g> -- additional data for testing:
SCOTT@orcl_11g> INSERT INTO me_test_details
  2  SELECT SUBSTR (object_name, 1, 15),
  3  	    SUBSTR (object_name, 1, 3),
  4  	    status,
  5  	    created,
  6  	    7,
  7  	    status
  8  FROM   all_objects
  9  /

68692 rows created.

SCOTT@orcl_11g> -- index and statistics:
SCOTT@orcl_11g> CREATE INDEX test_idx ON me_test_details (mecode, applno, source, testcode)
  2  /

Index created.

SCOTT@orcl_11g> EXEC DBMS_STATS.GATHER_TABLE_STATS (USER, 'ME_TEST_DETAILS')

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> SET TIMING ON
SCOTT@orcl_11g> -- original update:
SCOTT@orcl_11g> SET AUTOTRACE ON EXPLAIN
SCOTT@orcl_11g> update me_test_details
  2  set    payflg = 4
  3  where  rowid in
  4  	      (select rid from
  5  		(select b.rowid rid, b.mecode, b.testcode, b.dateofexam, b.applno, b.payflg, b.source
  6  		 from	me_test_details a, me_test_details b
  7  		 WHERE	UPPER (a.SOURCE) = 'LA'
  8  		 and	a.mecode= b.mecode
  9  		 and	a.applno = b.applno
 10  		 and	a.testcode = 'S12'
 11  		 and	b.testcode in ('FBS', 'LIP')))
 12  /

3 rows updated.

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
Plan hash value: 3594163741

------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT             |                 |     1 |    27 |   117   (2)| 00:00:02 |
|   1 |  UPDATE                      | ME_TEST_DETAILS |       |       |            |          |
|   2 |   NESTED LOOPS               |                 |     1 |    27 |   117   (2)| 00:00:02 |
|   3 |    VIEW                      | VW_NSO_1        |     1 |    12 |   115   (1)| 00:00:02 |
|   4 |     SORT UNIQUE              |                 |     1 |    73 |            |          |
|   5 |      NESTED LOOPS            |                 |     1 |    73 |   115   (1)| 00:00:02 |
|*  6 |       INDEX FAST FULL SCAN   | TEST_IDX        |     1 |    34 |   113   (1)| 00:00:02 |
|*  7 |       INDEX RANGE SCAN       | TEST_IDX        |     1 |    39 |     2   (0)| 00:00:01 |
|   8 |    TABLE ACCESS BY USER ROWID| ME_TEST_DETAILS |     1 |    15 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - filter("A"."TESTCODE"='S12' AND UPPER("A"."SOURCE")='LA')
   7 - access("A"."MECODE"="B"."MECODE" AND "A"."APPLNO"="B"."APPLNO")
       filter("B"."TESTCODE"='FBS' OR "B"."TESTCODE"='LIP')

SCOTT@orcl_11g> SET AUTOTRACE OFF
SCOTT@orcl_11g> SELECT * FROM me_test_details WHERE payflg = 4
  2  /

APPLNO          TES MECODE   DATEOFEXA     PAYFLG SOURCE
--------------- --- -------- --------- ---------- ----------
60665588        LIP 00000201 10-MAR-05          4 LA
60665588        FBS 00000201 10-MAR-05          4 LA
90010062        FBS 00000201 10-MAR-05          4 LA

Elapsed: 00:00:00.02
SCOTT@orcl_11g> ROLLBACK
  2  /

Rollback complete.

Elapsed: 00:00:00.01
SCOTT@orcl_11g> -- without unnecessary subquery:
SCOTT@orcl_11g> SET AUTOTRACE ON EXPLAIN
SCOTT@orcl_11g> update me_test_details
  2  set    payflg = 4
  3  where  rowid in
  4  	      (select b.rowid
  5  	       from   me_test_details a, me_test_details b
  6  	       WHERE  UPPER (a.SOURCE) = 'LA'
  7  	       and    a.mecode= b.mecode
  8  	       and    a.applno = b.applno
  9  	       and    a.testcode = 'S12'
 10  	       and    b.testcode in ('FBS', 'LIP'))
 11  /

3 rows updated.

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
Plan hash value: 3594163741

------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT             |                 |     1 |    27 |   117   (2)| 00:00:02 |
|   1 |  UPDATE                      | ME_TEST_DETAILS |       |       |            |          |
|   2 |   NESTED LOOPS               |                 |     1 |    27 |   117   (2)| 00:00:02 |
|   3 |    VIEW                      | VW_NSO_1        |     1 |    12 |   115   (1)| 00:00:02 |
|   4 |     SORT UNIQUE              |                 |     1 |    73 |            |          |
|   5 |      NESTED LOOPS            |                 |     1 |    73 |   115   (1)| 00:00:02 |
|*  6 |       INDEX FAST FULL SCAN   | TEST_IDX        |     1 |    34 |   113   (1)| 00:00:02 |
|*  7 |       INDEX RANGE SCAN       | TEST_IDX        |     1 |    39 |     2   (0)| 00:00:01 |
|   8 |    TABLE ACCESS BY USER ROWID| ME_TEST_DETAILS |     1 |    15 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - filter("A"."TESTCODE"='S12' AND UPPER("A"."SOURCE")='LA')
   7 - access("A"."MECODE"="B"."MECODE" AND "A"."APPLNO"="B"."APPLNO")
       filter("B"."TESTCODE"='FBS' OR "B"."TESTCODE"='LIP')

SCOTT@orcl_11g> SET AUTOTRACE OFF
SCOTT@orcl_11g> SELECT * FROM me_test_details WHERE payflg = 4
  2  /

APPLNO          TES MECODE   DATEOFEXA     PAYFLG SOURCE
--------------- --- -------- --------- ---------- ----------
60665588        LIP 00000201 10-MAR-05          4 LA
60665588        FBS 00000201 10-MAR-05          4 LA
90010062        FBS 00000201 10-MAR-05          4 LA

Elapsed: 00:00:00.02
SCOTT@orcl_11g> ROLLBACK
  2  /

Rollback complete.

Elapsed: 00:00:00.01
SCOTT@orcl_11g> -- using exists:
SCOTT@orcl_11g> SET AUTOTRACE ON EXPLAIN
SCOTT@orcl_11g> update me_test_details b
  2  set    b.payflg = 4
  3  where  b.testcode in ('FBS', 'LIP')
  4  and    exists
  5  	      (select *
  6  	       from   me_test_details a
  7  	       where  upper (a.source) = 'LA'
  8  	       and    a.mecode	       = b.mecode
  9  	       and    a.applno	       = b.applno
 10  	       and    a.testcode       = 'S12')
 11  /

3 rows updated.

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 1064918459

-------------------------------------------------------------------------------------------
| Id  | Operation               | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT        |                 |     1 |    64 |   117   (2)| 00:00:02 |
|   1 |  UPDATE                 | ME_TEST_DETAILS |       |       |            |          |
|   2 |   NESTED LOOPS          |                 |     1 |    64 |   117   (2)| 00:00:02 |
|   3 |    SORT UNIQUE          |                 |     1 |    34 |   113   (1)| 00:00:02 |
|*  4 |     INDEX FAST FULL SCAN| TEST_IDX        |     1 |    34 |   113   (1)| 00:00:02 |
|*  5 |    INDEX RANGE SCAN     | TEST_IDX        |     1 |    30 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("A"."TESTCODE"='S12' AND UPPER("A"."SOURCE")='LA')
   5 - access("A"."MECODE"="B"."MECODE" AND "A"."APPLNO"="B"."APPLNO")
       filter("B"."TESTCODE"='FBS' OR "B"."TESTCODE"='LIP')

SCOTT@orcl_11g> SET AUTOTRACE OFF
SCOTT@orcl_11g> SELECT * FROM me_test_details WHERE payflg = 4
  2  /

APPLNO          TES MECODE   DATEOFEXA     PAYFLG SOURCE
--------------- --- -------- --------- ---------- ----------
60665588        LIP 00000201 10-MAR-05          4 LA
60665588        FBS 00000201 10-MAR-05          4 LA
90010062        FBS 00000201 10-MAR-05          4 LA

Elapsed: 00:00:00.02
SCOTT@orcl_11g> 





Re: Selection/Updation from a table based on condition [message #412228 is a reply to message #410401] Wed, 08 July 2009 05:14 Go to previous messageGo to next message
cherry
Messages: 56
Registered: December 2007
Member
Hi Barbara

As always, great explanation!

Could you clarify why you created the index on the columns in this order? Or was it just random?

CREATE INDEX test_idx ON me_test_details ([B]mecode, applno, source, testcode[/B])


Thanks Smile

[Updated on: Wed, 08 July 2009 05:15]

Report message to a moderator

Re: Selection/Updation from a table based on condition [message #412308 is a reply to message #412228] Wed, 08 July 2009 11:04 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
cherry wrote on Wed, 08 July 2009 03:14

Could you clarify why you created the index on the columns in this order? Or was it just random?



I believe the columns used in joining (mecode and appno) need to be first and which of them is first probably does not matter. I added the columns used in the filter conditions in case they could also be used, but it looks like they weren't, but that might be because of the data distribution. It used to be that any columns used in an index had to be the leading edge of the index, but there seem to be exceptions to everything nowadays. It would probably be best to experiment with different indexes and realistic queries on your system and see what works best. You can provide different indexes with the columns in different orders, update your statistics, run your query, and see which index it uses, then drop the indexes that aren't used. There are others more adept at tuning, like Ross L., who may have more to add.
Re: Selection/Updation from a table based on condition [message #416785 is a reply to message #412308] Tue, 04 August 2009 05:28 Go to previous message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Thanks a lot Barbara for such a detailed explanation. I learned a lot from it.

Regards,
Mahi
Previous Topic: regarding statspack....
Next Topic: FTS - Full Table Scan - (merged 3)
Goto Forum:
  


Current Time: Wed Dec 07 20:25:21 CST 2016

Total time taken to generate the page: 0.11397 seconds