getting in a little over my head
Date: Thu, 8 Sep 2011 21:53:00 +0000
Message-ID: <D18D6513433DF04394041EA42B53E91C57884719_at_ICATEXCH2.ICAT.com>
SQL gurus,
I have a developer who came to me and asked me to make a certain sql statement 'as fast as possible'. Said statement was running against a view that was sort of nasty with sub-queries and a union thrown in. Cutting to the chase, I created a MV that looks like this:
SSS3_at_idm1> desc accountnotemview
Name Null? Type ----------------------------------------- -------- ---------------------------- CREATED_USERNAME VARCHAR2(100) CREATED_TIME TIMESTAMP(6) OBJID VARCHAR2(50) LINKTYPE VARCHAR2(100) TASKORDER NUMBER STATUS CHAR(7) MESSAGE VARCHAR2(4000)
Not a lot of data....36,474 rows.
SSS3_at_idm1> select count(*) from accountnotemview;
COUNT(*)
36474
SQL statement is (ultimately, the accountID will be a bind variable):
SELECT rownum AS pk, 128847 AS accountId, NULL AS logid, 0 AS deleted, CREATED_TIME AS CREATEDATE, CREATED_USERNAME AS USERNAME, 'GENERAL' AS CATEGORY, MESSAGE AS note
FROM accountnotemview acv
WHERE (linktype = 'Quote'
AND objid IN
(SELECT objectid FROM quote WHERE accountid = 128847 ))
OR (linktype = 'Policy'
AND objid IN
(SELECT objectid FROM pol_policy WHERE accountid = 128847 AND logid = 1 ))
/
Explain plan is:
SSS3_at_idm1> @/opt/app/oracle/product/10.2.0/rdbms/admin/utlxpls.sql
PLAN_TABLE_OUTPUT
Plan hash value: 3127273909
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 41 | 87986 | 170 (1)| 00:00:03 | | 1 | COUNT | | | | | | |* 2 | FILTER | | | | | | | 3 | MAT_VIEW ACCESS FULL | ACCOUNTNOTEMVIEW | 40790 | 83M| 170 (1)| 00:00:03 | |* 4 | TABLE ACCESS BY INDEX ROWID | QUOTE | 1 | 15 | 4 (0)| 00:00:01 | |* 5 | INDEX RANGE SCAN | QUOTE_BY_OBJECTID | 1 | | 3 (0)| 00:00:01 | |* 6 | TABLE ACCESS BY INDEX ROWID| POL_POLICY | 1 | 16 | 4 (0)| 00:00:01 | |* 7 | INDEX RANGE SCAN | POL_POLICY_BY_OBJECTID | 3 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
I figured it might be a good idea to get rid of the full access of the MV so I created an index: SSS3_at_idm1> create index joe1 on accountnotemview (linktype);
Index created.
New explain is:
SSS3_at_idm1> @/opt/app/oracle/product/10.2.0/rdbms/admin/utlxpls.sql
PLAN_TABLE_OUTPUT
Plan hash value: 4264724585
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1246 | 2611K| 106 (0)| 00:00:02 | | 1 | COUNT | | | | | | | 2 | CONCATENATION | | | | | | |* 3 | FILTER | | | | | | | 4 | MAT_VIEW ACCESS BY INDEX ROWID| ACCOUNTNOTEMVIEW | 623 | 1305K| 49 (0)| 00:00:01 | |* 5 | INDEX RANGE SCAN | JOE1 | 249 | | 44 (0)| 00:00:01 | |* 6 | TABLE ACCESS BY INDEX ROWID | POL_POLICY | 1 | 16 | 4 (0)| 00:00:01 | |* 7 | INDEX RANGE SCAN | POL_POLICY_BY_OBJECTID | 3 | | 1 (0)| 00:00:01 | |* 8 | FILTER | | | | | | | 9 | MAT_VIEW ACCESS BY INDEX ROWID| ACCOUNTNOTEMVIEW | 623 | 1305K| 49 (0)| 00:00:01 | |* 10 | INDEX RANGE SCAN | JOE1 | 249 | | 44 (0)| 00:00:01 | |* 11 | TABLE ACCESS BY INDEX ROWID | POL_POLICY | 1 | 16 | 4 (0)| 00:00:01 | |* 12 | INDEX RANGE SCAN | POL_POLICY_BY_OBJECTID | 3 | | 1 (0)| 00:00:01 | |* 13 | TABLE ACCESS BY INDEX ROWID | QUOTE | 1 | 15 | 4 (0)| 00:00:01 | |* 14 | INDEX RANGE SCAN | QUOTE_BY_OBJECTID | 1 | | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
Here is where I get lost. The original clock time against the original view was about 1.5 seconds. The clock time against the materialized view is about 0.5 seconds, with or without the index. These numbers were garnered simply by 'set timing on' in sqlplus. I can see that it is accessing fewer rows but it is also accessing the MV twice by index (presumably once for each value of linktype).
My question is this: does the 2nd explain plan look any "better" than the first? Since the timings are so close I am wondering if it's just a wash because the amount of data is relatively small.
Any/all comments welcome.
Thanks,
-joe
Confidentiality Note: This message contains information that may be confidential and/or privileged. If you are not the intended recipient, you should not use, copy, disclose, distribute or take any action based on this message. If you have received this message in error, please advise the sender immediately by reply email and delete this message. Although ICAT Managers, LLC, Underwriters at Lloyd's, Syndicate 4242, scans e-mail and attachments for viruses, it does not guarantee that either are virus-free and accepts no liability for any damage sustained as a result of viruses. Thank you.
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Sep 08 2011 - 16:53:00 CDT