Ugly View Not Cooperating [message #548216] |
Tue, 20 March 2012 19:19 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
scottwmackey
Messages: 515 Registered: March 2005
|
Senior Member |
|
|
Hi all,
I have an ugly view containing over a dozen tables that we have to run queries against. There are about 10 million rows in each of four of the tables contained in the view. The column that we always drive off, net_device_id, is the primary key of one of the 10 million row tables. When I use a simple IN clause, it yields the desired explain plan. (It's desired because it has proven to be fast).
SELECT * FROM network_device_all nda WHERE 1 = 1 AND net_device_id IN (1)
Plan hash value: 2893850283
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 58 (100)| | | |
| 1 | SORT AGGREGATE | | 1 | 14 | | | | |
|* 2 | TABLE ACCESS BY INDEX ROWID | BILLING_CYCLE | 1 | 14 | 4 (0)| 00:00:01 | | |
|* 3 | INDEX SKIP SCAN | BILLING_CYCLE_UK1 | 24 | | 3 (0)| 00:00:01 | | |
| 4 | SORT AGGREGATE | | 1 | 14 | | | | |
|* 5 | TABLE ACCESS BY INDEX ROWID | BILLING_CYCLE | 1 | 14 | 4 (0)| 00:00:01 | | |
|* 6 | INDEX SKIP SCAN | BILLING_CYCLE_UK1 | 24 | | 3 (0)| 00:00:01 | | |
| 7 | NESTED LOOPS OUTER | | 3 | 134K| 58 (0)| 00:00:01 | | |
| 8 | NESTED LOOPS OUTER | | 3 | 134K| 55 (0)| 00:00:01 | | |
| 9 | NESTED LOOPS OUTER | | 3 | 134K| 52 (0)| 00:00:01 | | |
| 10 | NESTED LOOPS OUTER | | 3 | 134K| 49 (0)| 00:00:01 | | |
| 11 | NESTED LOOPS OUTER | | 2 | 91550 | 47 (0)| 00:00:01 | | |
| 12 | NESTED LOOPS OUTER | | 2 | 91498 | 45 (0)| 00:00:01 | | |
| 13 | NESTED LOOPS OUTER | | 1 | 45731 | 44 (0)| 00:00:01 | | |
| 14 | NESTED LOOPS OUTER | | 1 | 45705 | 43 (0)| 00:00:01 | | |
| 15 | NESTED LOOPS OUTER | | 1 | 45687 | 42 (0)| 00:00:01 | | |
| 16 | NESTED LOOPS OUTER | | 1 | 45661 | 41 (0)| 00:00:01 | | |
| 17 | NESTED LOOPS OUTER | | 1 | 45643 | 40 (0)| 00:00:01 | | |
| 18 | NESTED LOOPS OUTER | | 1 | 45617 | 39 (0)| 00:00:01 | | |
| 19 | NESTED LOOPS OUTER | | 1 | 45591 | 38 (0)| 00:00:01 | | |
| 20 | NESTED LOOPS OUTER | | 1 | 45565 | 37 (0)| 00:00:01 | | |
| 21 | NESTED LOOPS OUTER | | 1 | 45539 | 36 (0)| 00:00:01 | | |
| 22 | NESTED LOOPS OUTER | | 1 | 45521 | 35 (0)| 00:00:01 | | |
| 23 | NESTED LOOPS OUTER | | 1 | 45503 | 34 (0)| 00:00:01 | | |
| 24 | NESTED LOOPS OUTER | | 1 | 45485 | 33 (0)| 00:00:01 | | |
| 25 | NESTED LOOPS OUTER | | 1 | 45467 | 32 (0)| 00:00:01 | | |
| 26 | NESTED LOOPS OUTER | | 1 | 45415 | 31 (0)| 00:00:01 | | |
| 27 | NESTED LOOPS OUTER | | 1 | 45407 | 30 (0)| 00:00:01 | | |
| 28 | NESTED LOOPS OUTER | | 1 | 45399 | 29 (0)| 00:00:01 | | |
| 29 | VIEW | | 1 | 45373 | 28 (0)| 00:00:01 | | |
| 30 | NESTED LOOPS OUTER | | 1 | 1598 | 28 (0)| 00:00:01 | | |
| 31 | NESTED LOOPS OUTER | | 1 | 1572 | 27 (0)| 00:00:01 | | |
| 32 | NESTED LOOPS OUTER | | 1 | 1523 | 25 (0)| 00:00:01 | | |
| 33 | NESTED LOOPS OUTER | | 1 | 1470 | 24 (0)| 00:00:01 | | |
| 34 | NESTED LOOPS OUTER | | 1 | 1449 | 23 (0)| 00:00:01 | | |
| 35 | NESTED LOOPS OUTER | | 1 | 1428 | 22 (0)| 00:00:01 | | |
| 36 | NESTED LOOPS OUTER | | 1 | 1407 | 21 (0)| 00:00:01 | | |
| 37 | NESTED LOOPS OUTER | | 1 | 1386 | 20 (0)| 00:00:01 | | |
| 38 | NESTED LOOPS OUTER | | 1 | 1365 | 19 (0)| 00:00:01 | | |
| 39 | NESTED LOOPS OUTER | | 1 | 1296 | 18 (0)| 00:00:01 | | |
| 40 | NESTED LOOPS OUTER | | 1 | 1227 | 17 (0)| 00:00:01 | | |
| 41 | NESTED LOOPS OUTER | | 1 | 1168 | 16 (0)| 00:00:01 | | |
| 42 | NESTED LOOPS | | 1 | 1090 | 15 (0)| 00:00:01 | | |
| 43 | NESTED LOOPS | | 1 | 737 | 13 (0)| 00:00:01 | | |
| 44 | NESTED LOOPS | | 1 | 683 | 11 (0)| 00:00:01 | | |
| 45 | NESTED LOOPS | | 1 | 670 | 10 (0)| 00:00:01 | | |
| 46 | NESTED LOOPS | | 1 | 641 | 9 (0)| 00:00:01 | | |
| 47 | NESTED LOOPS | | 1 | 612 | 8 (0)| 00:00:01 | | |
| 48 | NESTED LOOPS | | 1 | 591 | 7 (0)| 00:00:01 | | |
| 49 | R NESTED LOOPS OUTE | | 1 | 570 | 6 (0)| 00:00:01 | | |
| 50 | ER NESTED LOOPS OUT | | 1 | 549 | 5 (0)| 00:00:01 | | |
| 51 | NESTED LOOPS | | 1 | 423 | 4 (0)| 00:00:01 | | |
| 52 | Y INDEX ROWID TABLE ACCESS B | UIQ_DB_SWITCH | 1 | 43 | 2 (0)| 00:00:01 | | |
|* 53 | SCAN INDEX UNIQUE | UIQ_DB_SWITCH_UX1 | 1 | | 1 (0)| 00:00:01 | | |
| 54 | Y GLOBAL INDEX ROWID TABLE ACCESS B | DEVICE | 1 | 380 | 2 (0)| 00:00:01 | ROWID | ROWID |
|* 55 | SCAN INDEX UNIQUE | DEVICE_PK | 1 | | 1 (0)| 00:00:01 | | |
| 56 | INDEX ROWID TABLE ACCESS BY | NETWORK_DEVICE_TYPE | 17 | 2142 | 1 (0)| 00:00:01 | | |
|* 57 | CAN INDEX UNIQUE S | NETWORK_DEVICE_TYPE_PK | 1 | | 0 (0)| | | |
| 58 | INDEX ROWID TABLE ACCESS BY | MASTER_TYPE | 2048 | 43008 | 1 (0)| 00:00:01 | | |
|* 59 | AN INDEX UNIQUE SC | MASTER_TYPE_PK | 1 | | 0 (0)| | | |
| 60 | NDEX ROWID TABLE ACCESS BY I | MASTER_TYPE | 2048 | 43008 | 1 (0)| 00:00:01 | | |
|* 61 | N INDEX UNIQUE SCA | MASTER_TYPE_PK | 1 | | 0 (0)| | | |
| 62 | DEX ROWID TABLE ACCESS BY IN | MASTER_TYPE | 2048 | 43008 | 1 (0)| 00:00:01 | | |
|* 63 | INDEX UNIQUE SCAN | MASTER_TYPE_PK | 1 | | 0 (0)| | | |
| 64 | EX ROWID TABLE ACCESS BY IND | MASTER_TYPE | 2048 | 59392 | 1 (0)| 00:00:01 | | |
|* 65 | INDEX UNIQUE SCAN | MASTER_TYPE_PK | 1 | | 0 (0)| | | |
| 66 | X ROWID TABLE ACCESS BY INDE | MASTER_TYPE | 2048 | 59392 | 1 (0)| 00:00:01 | | |
|* 67 | INDEX UNIQUE SCAN | MASTER_TYPE_PK | 1 | | 0 (0)| | | |
| 68 | ROWID TABLE ACCESS BY INDEX | MASTER_TYPE | 2048 | 26624 | 1 (0)| 00:00:01 | | |
|* 69 | INDEX UNIQUE SCAN | MASTER_TYPE_PK | 1 | | 0 (0)| | | |
| 70 | R PARTITION LIST ITERATO | | 1 | 54 | 2 (0)| 00:00:01 | KEY | KEY |
| 71 | INDEX ROWID TABLE ACCESS BY LOCAL | DEVICE_DATA_TIMESTAMPS | 1 | 54 | 2 (0)| 00:00:01 | KEY | KEY |
|* 72 | INDEX UNIQUE SCAN | DEVICE_DATA_TIMESTAMPS_PK | 1 | | 1 (0)| 00:00:01 | KEY | KEY |
| 73 | INDEX ROWID TABLE ACCESS BY GLOBAL | NIC | 9391K| 3161M| 2 (0)| 00:00:01 | ROWID | ROWID |
|* 74 | INDEX UNIQUE SCAN | NIC_PK | 1 | | 1 (0)| 00:00:01 | | |
| 75 | WID TABLE ACCESS BY INDEX RO | UTILOS_SW | 11 | 858 | 1 (0)| 00:00:01 | | |
|* 76 | INDEX UNIQUE SCAN | UTILOS_SW_PK | 1 | | 0 (0)| | | |
| 77 | ID TABLE ACCESS BY INDEX ROW | NIC_TYPE | 9 | 531 | 1 (0)| 00:00:01 | | |
|* 78 | INDEX UNIQUE SCAN | NIC_TYPE_PK | 1 | | 0 (0)| | | |
| 79 | D TABLE ACCESS BY INDEX ROWI | IMU_TYPE | 20000 | 1347K| 1 (0)| 00:00:01 | | |
|* 80 | INDEX UNIQUE SCAN | IMU_TYPE_PK | 1 | | 0 (0)| | | |
| 81 | TABLE ACCESS BY INDEX ROWID | WAN_TYPE | 1 | 69 | 1 (0)| 00:00:01 | | |
|* 82 | INDEX UNIQUE SCAN | WAN_TYPE_PK | 1 | | 0 (0)| | | |
| 83 | TABLE ACCESS BY INDEX ROWID | MASTER_TYPE | 2048 | 43008 | 1 (0)| 00:00:01 | | |
|* 84 | INDEX UNIQUE SCAN | MASTER_TYPE_PK | 1 | | 0 (0)| | | |
| 85 | TABLE ACCESS BY INDEX ROWID | MASTER_TYPE | 2048 | 43008 | 1 (0)| 00:00:01 | | |
|* 86 | INDEX UNIQUE SCAN | MASTER_TYPE_PK | 1 | | 0 (0)| | | |
| 87 | TABLE ACCESS BY INDEX ROWID | MASTER_TYPE | 2048 | 43008 | 1 (0)| 00:00:01 | | |
|* 88 | INDEX UNIQUE SCAN | MASTER_TYPE_PK | 1 | | 0 (0)| | | |
| 89 | TABLE ACCESS BY INDEX ROWID | MASTER_TYPE | 2048 | 43008 | 1 (0)| 00:00:01 | | |
|* 90 | INDEX UNIQUE SCAN | MASTER_TYPE_PK | 1 | | 0 (0)| | | |
| 91 | TABLE ACCESS BY INDEX ROWID | MASTER_TYPE | 2048 | 43008 | 1 (0)| 00:00:01 | | |
|* 92 | INDEX UNIQUE SCAN | MASTER_TYPE_PK | 1 | | 0 (0)| | | |
| 93 | D TABLE ACCESS BY GLOBAL INDEX ROWI | DEVICE | 9391K| 474M| 1 (0)| 00:00:01 | ROWID | ROWID |
|* 94 | INDEX UNIQUE SCAN | DEVICE_PK | 1 | | 1 (0)| 00:00:01 | | |
| 95 | TABLE ACCESS BY GLOBAL INDEX ROWID | DEVICE | 9391K| 438M| 2 (0)| 00:00:01 | ROWID | ROWID |
|* 96 | INDEX UNIQUE SCAN | DEVICE_PK | 1 | | 1 (0)| 00:00:01 | | |
| 97 | TABLE ACCESS BY INDEX ROWID | MASTER_TYPE | 2048 | 53248 | 1 (0)| 00:00:01 | | |
|* 98 | INDEX UNIQUE SCAN | MASTER_TYPE_PK | 1 | | 0 (0)| | | |
| 99 | TABLE ACCESS BY INDEX ROWID | UIQ_METER_PROGRAM_CAL | 1 | 26 | 1 (0)| 00:00:01 | | |
|*100 | INDEX UNIQUE SCAN | UIQ_METER_PROGRAM_CAL_PK | 1 | | 0 (0)| | | |
| 101 | TABLE ACCESS BY INDEX ROWID | UIQ_METER_PROGRAM_TOU | 1 | 8 | 1 (0)| 00:00:01 | | |
|*102 | INDEX UNIQUE SCAN | UIQ_METER_PROGRAM_TOU_PK | 1 | | 0 (0)| | | |
| 103 | TABLE ACCESS BY INDEX ROWID | UIQ_METER_PROGRAM_LP | 1 | 8 | 1 (0)| 00:00:01 | | |
|*104 | INDEX UNIQUE SCAN | UIQ_METER_PROGRAM_LP_PK | 1 | | 0 (0)| | | |
| 105 | TABLE ACCESS BY INDEX ROWID | UIQ_METER_PROGRAM | 1 | 52 | 1 (0)| 00:00:01 | | |
|*106 | INDEX UNIQUE SCAN | UIQ_METER_PROGRAM_PK | 1 | | 0 (0)| | | |
| 107 | TABLE ACCESS BY INDEX ROWID | UIQ_METER_PROGRAM_LP_CHANNEL | 1 | 18 | 1 (0)| 00:00:01 | | |
|*108 | INDEX UNIQUE SCAN | UIQ_METER_PROGRAM_LP_CHN_PK | 1 | | 0 (0)| | | |
| 109 | TABLE ACCESS BY INDEX ROWID | UIQ_METER_PROGRAM_LP_CHANNEL | 1 | 18 | 1 (0)| 00:00:01 | | |
|*110 | INDEX UNIQUE SCAN | UIQ_METER_PROGRAM_LP_CHN_PK | 1 | | 0 (0)| | | |
| 111 | TABLE ACCESS BY INDEX ROWID | UIQ_METER_PROGRAM_TOU_SUM | 1 | 18 | 1 (0)| 00:00:01 | | |
|*112 | INDEX UNIQUE SCAN | UIQ_METER_PROGRAM_TOU_SUM_PK | 1 | | 0 (0)| | | |
| 113 | TABLE ACCESS BY INDEX ROWID | UIQ_METER_PROGRAM_TOU_SUM | 1 | 18 | 1 (0)| 00:00:01 | | |
|*114 | INDEX UNIQUE SCAN | UIQ_METER_PROGRAM_TOU_SUM_PK | 1 | | 0 (0)| | | |
| 115 | TABLE ACCESS BY INDEX ROWID | MASTER_TYPE | 1 | 26 | 1 (0)| 00:00:01 | | |
|*116 | INDEX UNIQUE SCAN | MASTER_TYPE_PK | 1 | | 0 (0)| | | |
| 117 | TABLE ACCESS BY INDEX ROWID | MASTER_TYPE | 1 | 26 | 1 (0)| 00:00:01 | | |
|*118 | INDEX UNIQUE SCAN | MASTER_TYPE_PK | 1 | | 0 (0)| | | |
| 119 | TABLE ACCESS BY INDEX ROWID | MASTER_TYPE | 1 | 26 | 1 (0)| 00:00:01 | | |
|*120 | INDEX UNIQUE SCAN | MASTER_TYPE_PK | 1 | | 0 (0)| | | |
| 121 | TABLE ACCESS BY INDEX ROWID | MASTER_TYPE | 1 | 26 | 1 (0)| 00:00:01 | | |
|*122 | INDEX UNIQUE SCAN | MASTER_TYPE_PK | 1 | | 0 (0)| | | |
| 123 | TABLE ACCESS BY INDEX ROWID | UIQ_METER_PROGRAM_LP_CHANNEL | 1 | 18 | 1 (0)| 00:00:01 | | |
|*124 | INDEX UNIQUE SCAN | UIQ_METER_PROGRAM_LP_CHN_PK | 1 | | 0 (0)| | | |
| 125 | TABLE ACCESS BY INDEX ROWID | MASTER_TYPE | 1 | 26 | 1 (0)| 00:00:01 | | |
|*126 | INDEX UNIQUE SCAN | MASTER_TYPE_PK | 1 | | 0 (0)| | | |
| 127 | TABLE ACCESS BY INDEX ROWID | UIQ_METER_PROGRAM_TOU_SUM | 1 | 18 | 1 (0)| 00:00:01 | | |
|*128 | INDEX UNIQUE SCAN | UIQ_METER_PROGRAM_TOU_SUM_PK | 1 | | 0 (0)| | | |
| 129 | TABLE ACCESS BY INDEX ROWID | MASTER_TYPE | 1 | 26 | 1 (0)| 00:00:01 | | |
|*130 | INDEX UNIQUE SCAN | MASTER_TYPE_PK | 1 | | 0 (0)| | | |
| 131 | TABLE ACCESS BY INDEX ROWID | UIQ_METER_PROGRAM_LP_CHANNEL | 1 | 18 | 1 (0)| 00:00:01 | | |
|*132 | INDEX UNIQUE SCAN | UIQ_METER_PROGRAM_LP_CHN_PK | 1 | | 0 (0)| | | |
| 133 | TABLE ACCESS BY INDEX ROWID | MASTER_TYPE | 1 | 26 | 1 (0)| 00:00:01 | | |
|*134 | INDEX UNIQUE SCAN | MASTER_TYPE_PK | 1 | | 0 (0)| | | |
| 135 | TABLE ACCESS BY INDEX ROWID | UIQ_METER_PROGRAM_TOU_SUM | 1 | 18 | 1 (0)| 00:00:01 | | |
|*136 | INDEX UNIQUE SCAN | UIQ_METER_PROGRAM_TOU_SUM_PK | 1 | | 0 (0)| | | |
| 137 | TABLE ACCESS BY INDEX ROWID | MASTER_TYPE | 1 | 26 | 1 (0)| 00:00:01 | | |
|*138 | INDEX UNIQUE SCAN | MASTER_TYPE_PK | 1 | | 0 (0)| | | |
| 139 | TABLE ACCESS BY INDEX ROWID | BATTERY | 1 | 90 | 1 (0)| 00:00:01 | | |
|*140 | INDEX UNIQUE SCAN | BATTERY_PK | 1 | | 0 (0)| | | |
| 141 | TABLE ACCESS BY INDEX ROWID | MASTER_TYPE | 1 | 13 | 1 (0)| 00:00:01 | | |
|*142 | INDEX UNIQUE SCAN | MASTER_TYPE_PK | 1 | | 0 (0)| | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
If, however, the IN clause is a subquery, the plan sucks. (It sucks because it takes a very long time to run.) The subquery has only one record that matchees
SQL> SELECT net_device_id FROM tmp_device_search tmp WHERE device_search_key = 51854009;
NET_DEVICE_ID
---------------------------------------
9170100 So I would really like it to use the same execution plan. It, instead, generates this ugliness.
SQL_ID anqu9t32u3jg4, child number 0
-------------------------------------
SELECT * FROM network_device_all nda WHERE 1 = 1 AND net_device_id IN
(SELECT net_device_id FROM tmp_device_search tmp WHERE device_search_key = 51854009)
Plan hash value: 1841300360
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 1604K(100)| | | |
| 1 | SORT AGGREGATE | | 1 | 14 | | | | | |
|* 2 | TABLE ACCESS BY INDEX ROWID | BILLING_CYCLE | 1 | 14 | | 4 (0)| 00:00:01 | | |
|* 3 | INDEX SKIP SCAN | BILLING_CYCLE_UK1 | 24 | | | 3 (0)| 00:00:01 | | |
| 4 | SORT AGGREGATE | | 1 | 14 | | | | | |
|* 5 | TABLE ACCESS BY INDEX ROWID | BILLING_CYCLE | 1 | 14 | | 4 (0)| 00:00:01 | | |
|* 6 | INDEX SKIP SCAN | BILLING_CYCLE_UK1 | 24 | | | 3 (0)| 00:00:01 | | |
| 7 | NESTED LOOPS OUTER | | 3 | 134K| | 1604K (1)| 05:20:56 | | |
| 8 | NESTED LOOPS OUTER | | 3 | 134K| | 1604K (1)| 05:20:56 | | |
| 9 | NESTED LOOPS OUTER | | 2 | 91808 | | 1604K (1)| 05:20:56 | | |
| 10 | NESTED LOOPS OUTER | | 2 | 91628 | | 1604K (1)| 05:20:56 | | |
| 11 | NESTED LOOPS OUTER | | 2 | 91576 | | 1604K (1)| 05:20:56 | | |
| 12 | NESTED LOOPS OUTER | | 2 | 91524 | | 1604K (1)| 05:20:56 | | |
| 13 | NESTED LOOPS OUTER | | 2 | 91472 | | 1604K (1)| 05:20:56 | | |
| 14 | NESTED LOOPS OUTER | | 2 | 91420 | | 1604K (1)| 05:20:56 | | |
| 15 | NESTED LOOPS OUTER | | 2 | 91384 | | 1604K (1)| 05:20:55 | | |
| 16 | NESTED LOOPS OUTER | | 2 | 91348 | | 1604K (1)| 05:20:55 | | |
| 17 | NESTED LOOPS OUTER | | 1 | 45656 | | 1604K (1)| 05:20:55 | | |
| 18 | NESTED LOOPS OUTER | | 1 | 45630 | | 1604K (1)| 05:20:55 | | |
| 19 | NESTED LOOPS OUTER | | 1 | 45604 | | 1604K (1)| 05:20:55 | | |
| 20 | NESTED LOOPS OUTER | | 1 | 45578 | | 1604K (1)| 05:20:55 | | |
| 21 | NESTED LOOPS OUTER | | 1 | 45560 | | 1604K (1)| 05:20:55 | | |
| 22 | NESTED LOOPS OUTER | | 1 | 45542 | | 1604K (1)| 05:20:55 | | |
| 23 | NESTED LOOPS OUTER | | 1 | 45524 | | 1604K (1)| 05:20:55 | | |
| 24 | NESTED LOOPS OUTER | | 1 | 45506 | | 1604K (1)| 05:20:55 | | |
| 25 | NESTED LOOPS OUTER | | 1 | 45454 | | 1604K (1)| 05:20:55 | | |
| 26 | NESTED LOOPS OUTER | | 1 | 45446 | | 1604K (1)| 05:20:55 | | |
| 27 | NESTED LOOPS OUTER | | 1 | 45438 | | 1604K (1)| 05:20:55 | | |
| 28 | NESTED LOOPS OUTER | | 1 | 45425 | | 1604K (1)| 05:20:55 | | |
|* 29 | HASH JOIN | | 1 | 45399 | | 1604K (1)| 05:20:55 | | |
| 30 | SORT UNIQUE | | 1 | 26 | | 47 (0)| 00:00:01 | | |
| 31 | PARTITION RANGE ALL | | 1 | 26 | | 47 (0)| 00:00:01 | 1 | 45 |
| 32 | TABLE ACCESS BY LOCAL INDEX ROWID | TMP_DEVICE_SEARCH | 1 | 26 | | 47 (0)| 00:00:01 | 1 | 45 |
|* 33 | INDEX RANGE SCAN | DEVICE_SEARCH_KEY_IDX | 1 | | | 46 (0)| 00:00:01 | 1 | 45 |
| 34 | VIEW | | 9391K| 396G| | 1604K (1)| 05:20:54 | | |
|* 35 | HASH JOIN RIGHT OUTER | | 9391K| 13G| | 1604K (1)| 05:20:54 | | |
| 36 | TABLE ACCESS FULL | MASTER_TYPE | 2048 | 43008 | | 8 (0)| 00:00:01 | | |
|* 37 | HASH JOIN RIGHT OUTER | | 9391K| 13G| | 1604K (1)| 05:20:54 | | |
| 38 | TABLE ACCESS FULL | MASTER_TYPE | 2048 | 43008 | | 8 (0)| 00:00:01 | | |
|* 39 | HASH JOIN RIGHT OUTER | | 9391K| 13G| | 1604K (1)| 05:20:53 | | |
| 40 | TABLE ACCESS FULL | MASTER_TYPE | 2048 | 43008 | | 8 (0)| 00:00:01 | | |
|* 41 | HASH JOIN RIGHT OUTER | | 9391K| 13G| | 1604K (1)| 05:20:52 | | |
| 42 | TABLE ACCESS FULL | MASTER_TYPE | 2048 | 43008 | | 8 (0)| 00:00:01 | | |
|* 43 | HASH JOIN RIGHT OUTER | | 9391K| 13G| | 1604K (1)| 05:20:52 | | |
| 44 | TABLE ACCESS FULL | MASTER_TYPE | 2048 | 43008 | | 8 (0)| 00:00:01 | | |
| 45 | NESTED LOOPS OUTER | | 9391K| 13G| | 1604K (1)| 05:20:51 | | |
|* 46 | HASH JOIN RIGHT OUTER | | 9391K| 12G| | 1604K (1)| 05:20:51 | | |
| 47 | TABLE ACCESS FULL | MASTER_TYPE | 2048 | 53248 | | 8 (0)| 00:00:01 | | |
|* 48 | HASH JOIN | | 9391K| 12G| | 1604K (1)| 05:20:51 | | |
| 49 | TABLE ACCESS FULL | MASTER_TYPE | 2048 | 26624 | | 8 (0)| 00:00:01 | | |
|* 50 | HASH JOIN | | 9391K| 12G| | 1604K (1)| 05:20:50 | | |
| 51 | PART JOIN FILTER CREATE | :BF0000 | 2048 | 59392 | | 8 (0)| 00:00:01 | | |
| 52 | TABLE ACCESS FULL | MASTER_TYPE | 2048 | 59392 | | 8 (0)| 00:00:01 | | |
|* 53 | HASH JOIN | | 9391K| 12G| | 1604K (1)| 05:20:49 | | |
| 54 | TABLE ACCESS FULL | MASTER_TYPE | 2048 | 59392 | | 8 (0)| 00:00:01 | | |
|* 55 | HASH JOIN | | 9391K| 12G| | 1604K (1)| 05:20:49 | | |
| 56 | TABLE ACCESS FULL | MASTER_TYPE | 2048 | 43008 | | 8 (0)| 00:00:01 | | |
|* 57 | HASH JOIN | | 9391K| 11G| | 1603K (1)| 05:20:48 | | |
| 58 | TABLE ACCESS FULL | MASTER_TYPE | 2048 | 43008 | | 8 (0)| 00:00:01 | | |
|* 59 | HASH JOIN RIGHT OUTER | | 9391K| 11G| 546M| 1603K (1)| 05:20:48 | | |
| 60 | PARTITION LIST ALL | | 9391K| 438M| | 109K (1)| 00:21:56 | 1 | 7 |
| 61 | TABLE ACCESS FULL | DEVICE | 9391K| 438M| | 109K (1)| 00:21:56 | 1 | 7 |
| 62 | NESTED LOOPS OUTER | | 9391K| 11G| | 891K (1)| 02:58:21 | | |
|* 63 | HASH JOIN RIGHT OUTER | | 9391K| 10G| | 891K (1)| 02:58:21 | | |
| 64 | TABLE ACCESS FULL | IMU_TYPE | 20000 | 1347K| | 60 (0)| 00:00:01 | | |
|* 65 | R HASH JOIN RIGHT OUTE | | 9391K| 10G| | 891K (1)| 02:58:19 | | |
| 66 | TABLE ACCESS FULL | UTILOS_SW | 11 | 858 | | 8 (0)| 00:00:01 | | |
|* 67 | ER HASH JOIN RIGHT OUT | | 9391K| 9708M| | 891K (1)| 02:58:19 | | |
| 68 | TABLE ACCESS FULL | NIC_TYPE | 9 | 531 | | 8 (0)| 00:00:01 | | |
|* 69 | TER HASH JOIN RIGHT OU | | 9391K| 9179M| | 891K (1)| 02:58:18 | | |
| 70 | TABLE ACCESS FULL | WAN_TYPE | 1 | 69 | | 8 (0)| 00:00:01 | | |
|* 71 | HASH JOIN | | 9391K| 8561M| 3268M| 891K (1)| 02:58:18 | | |
| 72 | LL PARTITION LIST A | | 9391K| 3161M| | 93481 (1)| 00:18:42 | 1 | 7 |
| 73 | LL TABLE ACCESS FU | NIC | 9391K| 3161M| | 93481 (1)| 00:18:42 | 1 | 7 |
|* 74 | OUTER HASH JOIN RIGHT | | 9391K| 5400M| | 362K (1)| 01:12:33 | | |
| 75 | LL TABLE ACCESS FU | NETWORK_DEVICE_TYPE | 17 | 2142 | | 8 (0)| 00:00:01 | | |
|* 76 | HASH JOIN | | 9391K| 4271M| 924M| 362K (1)| 01:12:32 | | |
| 77 | NESTED LOOPS | | 8891K| 822M| | 33022 (1)| 00:06:37 | | |
| 78 | BY INDEX ROWID TABLE ACCESS | UIQ_DB_SWITCH | 1 | 43 | | 2 (0)| 00:00:01 | | |
|* 79 | SCAN INDEX UNIQUE | UIQ_DB_SWITCH_UX1 | 1 | | | 1 (0)| 00:00:01 | | |
| 80 | T ALL PARTITION LIS | | 8891K| 457M| | 33020 (1)| 00:06:37 | 1 | 12 |
| 81 | FULL TABLE ACCESS | DEVICE_DATA_TIMESTAMPS | 8891K| 457M| | 33020 (1)| 00:06:37 | 1 | 12 |
| 82 | JOIN-FILTER PARTITION LIST | | 9391K| 3403M| | 109K (1)| 00:21:56 |:BF0000|:BF0000|
| 83 | FULL TABLE ACCESS | DEVICE | 9391K| 3403M| | 109K (1)| 00:21:56 |:BF0000|:BF0000|
| 84 | L INDEX ROWID TABLE ACCESS BY GLOBA | DEVICE | 1 | 53 | | 1 (0)| 00:00:01 | ROWID | ROWID |
|* 85 | INDEX UNIQUE SCAN | DEVICE_PK | 1 | | | 1 (0)| 00:00:01 | | |
| 86 | TABLE ACCESS BY INDEX ROWID | MASTER_TYPE | 1 | 21 | | 1 (0)| 00:00:01 | | |
|* 87 | INDEX UNIQUE SCAN | MASTER_TYPE_PK | 1 | | | 0 (0)| | | |
| 88 | TABLE ACCESS BY INDEX ROWID | UIQ_METER_PROGRAM_CAL | 1 | 26 | | 1 (0)| 00:00:01 | | |
|* 89 | INDEX UNIQUE SCAN | UIQ_METER_PROGRAM_CAL_PK | 1 | | | 0 (0)| | | |
| 90 | TABLE ACCESS BY INDEX ROWID | MASTER_TYPE | 1 | 13 | | 1 (0)| 00:00:01 | | |
|* 91 | INDEX UNIQUE SCAN | MASTER_TYPE_PK | 1 | | | 0 (0)| | | |
| 92 | TABLE ACCESS BY INDEX ROWID | UIQ_METER_PROGRAM_TOU | 1 | 8 | | 1 (0)| 00:00:01 | | |
|* 93 | INDEX UNIQUE SCAN | UIQ_METER_PROGRAM_TOU_PK | 1 | | | 0 (0)| | | |
| 94 | TABLE ACCESS BY INDEX ROWID | UIQ_METER_PROGRAM_LP | 1 | 8 | | 1 (0)| 00:00:01 | | |
|* 95 | INDEX UNIQUE SCAN | UIQ_METER_PROGRAM_LP_PK | 1 | | | 0 (0)| | | |
| 96 | TABLE ACCESS BY INDEX ROWID | UIQ_METER_PROGRAM | 1 | 52 | | 1 (0)| 00:00:01 | | |
|* 97 | INDEX UNIQUE SCAN | UIQ_METER_PROGRAM_PK | 1 | | | 0 (0)| | | |
| 98 | TABLE ACCESS BY INDEX ROWID | UIQ_METER_PROGRAM_LP_CHANNEL | 1 | 18 | | 1 (0)| 00:00:01 | | |
|* 99 | INDEX UNIQUE SCAN | UIQ_METER_PROGRAM_LP_CHN_PK | 1 | | | 0 (0)| | | |
| 100 | TABLE ACCESS BY INDEX ROWID | UIQ_METER_PROGRAM_LP_CHANNEL | 1 | 18 | | 1 (0)| 00:00:01 | | |
|*101 | INDEX UNIQUE SCAN | UIQ_METER_PROGRAM_LP_CHN_PK | 1 | | | 0 (0)| | | |
| 102 | TABLE ACCESS BY INDEX ROWID | UIQ_METER_PROGRAM_TOU_SUM | 1 | 18 | | 1 (0)| 00:00:01 | | |
|*103 | INDEX UNIQUE SCAN | UIQ_METER_PROGRAM_TOU_SUM_PK | 1 | | | 0 (0)| | | |
| 104 | TABLE ACCESS BY INDEX ROWID | UIQ_METER_PROGRAM_TOU_SUM | 1 | 18 | | 1 (0)| 00:00:01 | | |
|*105 | INDEX UNIQUE SCAN | UIQ_METER_PROGRAM_TOU_SUM_PK | 1 | | | 0 (0)| | | |
| 106 | TABLE ACCESS BY INDEX ROWID | MASTER_TYPE | 1 | 26 | | 1 (0)| 00:00:01 | | |
|*107 | INDEX UNIQUE SCAN | MASTER_TYPE_PK | 1 | | | 0 (0)| | | |
| 108 | TABLE ACCESS BY INDEX ROWID | MASTER_TYPE | 1 | 26 | | 1 (0)| 00:00:01 | | |
|*109 | INDEX UNIQUE SCAN | MASTER_TYPE_PK | 1 | | | 0 (0)| | | |
| 110 | TABLE ACCESS BY INDEX ROWID | MASTER_TYPE | 1 | 26 | | 1 (0)| 00:00:01 | | |
|*111 | INDEX UNIQUE SCAN | MASTER_TYPE_PK | 1 | | | 0 (0)| | | |
| 112 | TABLE ACCESS BY INDEX ROWID | UIQ_METER_PROGRAM_TOU_SUM | 1 | 18 | | 1 (0)| 00:00:01 | | |
|*113 | INDEX UNIQUE SCAN | UIQ_METER_PROGRAM_TOU_SUM_PK | 1 | | | 0 (0)| | | |
| 114 | TABLE ACCESS BY INDEX ROWID | UIQ_METER_PROGRAM_LP_CHANNEL | 1 | 18 | | 1 (0)| 00:00:01 | | |
|*115 | INDEX UNIQUE SCAN | UIQ_METER_PROGRAM_LP_CHN_PK | 1 | | | 0 (0)| | | |
| 116 | TABLE ACCESS BY INDEX ROWID | UIQ_METER_PROGRAM_TOU_SUM | 1 | 18 | | 1 (0)| 00:00:01 | | |
|*117 | INDEX UNIQUE SCAN | UIQ_METER_PROGRAM_TOU_SUM_PK | 1 | | | 0 (0)| | | |
| 118 | TABLE ACCESS BY INDEX ROWID | MASTER_TYPE | 1 | 26 | | 1 (0)| 00:00:01 | | |
|*119 | INDEX UNIQUE SCAN | MASTER_TYPE_PK | 1 | | | 0 (0)| | | |
| 120 | TABLE ACCESS BY INDEX ROWID | MASTER_TYPE | 1 | 26 | | 1 (0)| 00:00:01 | | |
|*121 | INDEX UNIQUE SCAN | MASTER_TYPE_PK | 1 | | | 0 (0)| | | |
| 122 | TABLE ACCESS BY INDEX ROWID | MASTER_TYPE | 1 | 26 | | 1 (0)| 00:00:01 | | |
|*123 | INDEX UNIQUE SCAN | MASTER_TYPE_PK | 1 | | | 0 (0)| | | |
| 124 | TABLE ACCESS BY INDEX ROWID | MASTER_TYPE | 1 | 26 | | 1 (0)| 00:00:01 | | |
|*125 | INDEX UNIQUE SCAN | MASTER_TYPE_PK | 1 | | | 0 (0)| | | |
| 126 | TABLE ACCESS BY INDEX ROWID | BATTERY | 1 | 90 | | 1 (0)| 00:00:01 | | |
|*127 | INDEX UNIQUE SCAN | BATTERY_PK | 1 | | | 0 (0)| | | |
| 128 | TABLE ACCESS BY INDEX ROWID | UIQ_METER_PROGRAM_LP_CHANNEL | 1 | 18 | | 1 (0)| 00:00:01 | | |
|*129 | INDEX UNIQUE SCAN | UIQ_METER_PROGRAM_LP_CHN_PK | 1 | | | 0 (0)| | | |
| 130 | TABLE ACCESS BY INDEX ROWID | MASTER_TYPE | 1 | 26 | | 1 (0)| 00:00:01 | | |
|*131 | INDEX UNIQUE SCAN | MASTER_TYPE_PK | 1 | | | 0 (0)| | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
I understand why it does this. The CBO doesn't know how many rows are in the subquery, so it makes its best guess. I, however, do know how many rows will be in the subquery. It's never more than a couple of thousand and usually not more than a hundred. Either way, the first execution plan will be significantly faster than the second.
How do I convince the CBO that the subquery is not returning enough rows to necessitate full table scans on my 10 million row tables? I tried a CARDINALITY hint, but it didn't do anything. I can't see how gathering stats could do anything because the WHERE clause on the subquery is never the same. I'm at a loss. I fear I am going to have to resort to executing the subquery and creating a dynamic IN clause if it returns less than 1000 rows. Please save me from coding such an abomination.
|
|
|
Re: Ugly View Not Cooperating [message #548219 is a reply to message #548216] |
Tue, 20 March 2012 19:27 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/136107.jpg) |
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
>(SELECT net_device_id FROM tmp_device_search tmp WHERE device_search_key = 51854009)
do current statistics exist for TMP_DEVICE_SEARCH table?
is DEVICE_SEARCH_KEY indexed?
do current statistics exist for DEVICE_SEARCH_KEY?
is TMP_DEVICE_SEARCH a "temp" table that is populated soon before doing more complex SELECT?
[Updated on: Tue, 20 March 2012 19:28] Report message to a moderator
|
|
|
Re: Ugly View Not Cooperating [message #548243 is a reply to message #548219] |
Wed, 21 March 2012 01:16 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
scottwmackey
Messages: 515 Registered: March 2005
|
Senior Member |
|
|
No, we do not have current stats on TMP_DEVICE_SEARCH.
DEVICE_SEARCH_KEY is indexed.
Current statistics do not exist for DEVICE_SEARCH_KEY.
TMP_DEVICE_SEARCH is a "temp" table that is populated soon before doing more complex SELECT.
|
|
|
Re: Ugly View Not Cooperating [message #548260 is a reply to message #548243] |
Wed, 21 March 2012 01:51 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8944 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
How about telling the optimizer to analyze the tmp_device_search tmp table? I usually set the instance parameter OPTIMIZER_DYNAMIC_SAMPLING to 4, I have never known this cause a problem and sometimes it delivers spectacular benefits. Or you could use the DYNAMIC_SAMPLING hint to take it up to 10 for this one statement.
|
|
|
Re: Ugly View Not Cooperating [message #548317 is a reply to message #548260] |
Wed, 21 March 2012 06:46 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Roachcoach
Messages: 1576 Registered: May 2010 Location: UK
|
Senior Member |
|
|
Do either of these do anything useful to the plan?
with subqlst as (SELECT net_device_id FROM tmp_device_search tmp WHERE device_search_key = 51854009)
SELECT /*+ cardinality(subqlst 10) push_subq(subqlst)*/
*
FROM network_device_all nda
join subqlst on nda.net_device_id = subqlst.net_device_id
WHERE 1 = 1
/
with subqlst as (SELECT net_device_id FROM tmp_device_search tmp WHERE device_search_key = 51854009)
SELECT /*+ cardinality(subqlst 10) leading(subqlst)*/
*
FROM network_device_all nda
join subqlst on nda.net_device_id = subqlst.net_device_id
WHERE 1 = 1
/
This kind of thing is very environment specific so usually takes me some trial and error to get just right, I'd start with that though see if the plan changed much.
|
|
|
Re: Ugly View Not Cooperating [message #548479 is a reply to message #548216] |
Thu, 22 March 2012 07:30 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
Can you edit your post to include the predicate section as well ? Also, if you are running on 10g or above can you post the execution plan along with the actual/estimate cardinality output as well please.
Thanks
Raj
|
|
|