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  |
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  |
 |
Barbara Boehmer
Messages: 9097
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 #412308 is a reply to message #412228] |
Wed, 08 July 2009 11:04 |
|
Barbara Boehmer
Messages: 9097
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.
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Jun 28 00:47:45 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
