10g INDEX_RS ? Syntax problem maybe?
Date: Wed, 5 Dec 2012 09:57:23 -0600
Message-ID: <CAP79kiQAiiRd6ZtgTFzKJBXF5AWr5XH86jD+ecx59AB8Feumtw_at_mail.gmail.com>
(Testing the GMAIL mobile interface to see if the formatting comes out right - if it doesn't my apologies!)
Ok guys, I have this SQL INSERT query I've been working on. This large table is slated to be partitioned but while that goes through the code testing/migration phase, I need to get this nightly batch query performance time down.
What I've done is created a composite index on this really wide table to have all the required columns in it to get an INDEX FAST FULL SCAN.
Now, with this step, the query time reduced from 6 minutes to 2 minutes on a cold start (minus any blocks cached at the filesystem layer).
From here, I'd like to get an INDEX_RS instead of a FAST FULL SCAN to only pull back the ID rows I want instead of examining all 24 M rows in the index.
I can accomplish this by modifying a SQL profile from q'[USE_HASH(_at_"SEL$5DA710D3" "MACD"@"SEL$1")]' to q'[USE_NL(_at_"SEL$5DA710D3" "MACD"@"SEL$1")]'.
Using the USE_NL instruction the INSERT time comes down from 2 minutes to 7 seconds.
I'm attempting to change that back to USE_HASH but get an INDEX_RS instead of an INDEX FAST FULL SCAN, but my hint is not working.
Here is my HINT:
INDEX_RS(MACD, MON_ACCOUNT_CHG_DET_IDX01)
(note #1 I've tried it with and without the comma - makes no difference)
(note #2 The MACD table has 0 filters applied to it - grrrr - I work
with what I'm given and trying to get the dev team to think about how
to access more specific data when hitting this object)
| Id | Operation | Name | Starts |
E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers | Reads | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN | | 1 |
1278K| 97M| 35780 (1)| 00:07:10 | | | 54158
|00:01:48.26 | 161K| 160K|
|* 2 | HASH JOIN SEMI | | 1 |
28051 | 767K| 527 (2)| 00:00:07 | | | 2899
|00:00:00.06 | 95 | 92 |
| 3 | PARTITION LIST SINGLE| | 1 |
28086 | 383K| 458 (1)| 00:00:06 | KEY | KEY | 4484
|00:00:00.01 | 19 | 18 |
|* 4 | INDEX FAST FULL SCAN| MON_ACCOUNT_IDX01 | 1 |
28086 | 383K| 458 (1)| 00:00:06 | KEY | KEY | 4484
|00:00:00.01 | 19 | 18 |
| 5 | PARTITION LIST SINGLE| | 1 |
82126 | 1122K| 68 (2)| 00:00:01 | KEY | KEY | 8696
|00:00:00.02 | 76 | 74 |
|* 6 | TABLE ACCESS FULL | WQ_ORG_ACCOUNT | 1 |
82126 | 1122K| 68 (2)| 00:00:01 | KEY | KEY | 8696
|00:00:00.02 | 76 | 74 |
| 7 | INDEX FAST FULL SCAN | MON_ACCOUNT_CHG_DET_IDX01 | 1 |
24M| 1214M| 35163 (1)| 00:07:02 | | | 24M|00:01:13.58 | 161K| 160K| ------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Here's the OUTLINE
Query Block Name / Object Alias (identified by operation id):
1 - SEL$5DA710D3 4 - SEL$5DA710D3 / MA_at_SEL$1 6 - SEL$5DA710D3 / WQOA_at_SEL$2 7 - SEL$5DA710D3 / MACD_at_SEL$1
Outline Data
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
OPT_PARAM('_optim_peek_user_binds' 'false')
ALL_ROWS
OUTLINE_LEAF(_at_"SEL$5DA710D3")
UNNEST(_at_"SEL$2")
OUTLINE_LEAF(_at_"INS$1")
OUTLINE(_at_"SEL$1")
OUTLINE(_at_"SEL$2")
OUTLINE(_at_"INS$1")
FULL(_at_"INS$1" "WQ_MON_ACCOUNT_CHARGE_DETAIL"@"INS$1")
INDEX_FFS(_at_"SEL$5DA710D3" "MA"@"SEL$1" ("MON_ACCOUNT"."ID"
"MON_ACCOUNT"."ORG_ID_PROVIDER"))
FULL(_at_"SEL$5DA710D3" "WQOA"@"SEL$2")
INDEX_FFS(_at_"SEL$5DA710D3" "MACD"@"SEL$1"
("MON_ACCOUNT_CHARGE_DETAIL"."MON_ACCOUNT_ID"
"MON_ACCOUNT_CHARGE_DETAIL"."ID"
"MON_ACCOUNT_CHARGE_DETAIL"."CHARGE_MASTER_ID" "MON_ACCOUNT_CHARGE_DETAIL"."HCPCS_CODE" "MON_ACCOUNT_CHARGE_DETAIL"."HCPCS_MODIFIER_1" "MON_ACCOUNT_CHARGE_DETAIL"."HCPCS_MODIFIER_2" "MON_ACCOUNT_CHARGE_DETAIL"."HCPCS_MODIFIER_3" "MON_ACCOUNT_CHARGE_DETAIL"."HCPCS_MODIFIER_4" "MON_ACCOUNT_CHARGE_DETAIL"."HCPCS_MODIFIER_5" "MON_ACCOUNT_CHARGE_DETAIL"."HCPCS_MODIFIER_6" "MON_ACCOUNT_CHARGE_DETAIL"."REVENUE_CODE"
"MON_ACCOUNT_CHARGE_DETAIL"."HIPPS_CODE"))
LEADING(_at_"SEL$5DA710D3" "MA"@"SEL$1" "WQOA"@"SEL$2" "MACD"@"SEL$1")
USE_HASH(_at_"SEL$5DA710D3" "WQOA"@"SEL$2")
USE_HASH(_at_"SEL$5DA710D3" "MACD"@"SEL$1")
END_OUTLINE_DATA
*/
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Dec 05 2012 - 16:57:23 CET