Databases
StackOverflow2013
Postsdbo
POOracle query optimization using dynamic dates
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POOracle query optimization using dynamic dates
    primarykey
    data
    text
    <p>I have a view where if I select like this:</p> <pre><code>select * from view where date = '17-sep-10' </code></pre> <p>it returns in seconds.</p> <p>If I use a dynamic date:</p> <pre><code>select * from view where date = to_date((select current_business_date from v_business_day), 'mm/dd/yyyy') </code></pre> <p>it returns in 20 mins.</p> <p>Why would hard coding improve performance so much? Also, the select in the to_date is not the issue (I think). It returns almost instanteously when running it.</p> <p>Here is the desc for the view:</p> <pre><code>JAID_OWNER@algoja1p&gt; desc v_positions_rm_base Name Null? Type ----------------------------------------- -------- ---------------------------- RUN_KEY NOT NULL NUMBER(10) POSITION_KEY NOT NULL NUMBER(10) POSITIONS VARCHAR2(50) INSTRUMENT_ID VARCHAR2(151) BUSINESS_DATE NOT NULL DATE PROCESSING_DATE DATE PROCESSING_STATUS_KEY NOT NULL NUMBER(10) </code></pre> <p>Here is the explain plan for when I hardcode the date. I changed the query to do a to_date on a hardcoded value so it's consistent..</p> <pre><code>explain plan for select * from v_positions_rm_base where business_date = to_date('09/17/2010', 'mm/dd/yyyy') ----------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 518 | 1003K| 1679 (2)| 00:00:21 | |* 1 | FILTER | | | | | | |* 2 | FILTER | | | | | | |* 3 | HASH JOIN RIGHT OUTER | | 10360 | 19M| 1651 (2)| 00:00:20 | | 4 | INDEX FAST FULL SCAN | RM_SRC_ACCT_UDX | 601 | 7212 | 2 (0)| 00:00:01 | | 5 | VIEW | | 10360 | 19M| 1648 (2)| 00:00:20 | |* 6 | HASH JOIN RIGHT OUTER | | 10360 | 20M| 1648 (2)| 00:00:20 | | 7 | VIEW | V_RM_FUTURES_CODE_TRANS_FLAT | 1 | 96 | 9 (12)| 00:00:01 | | 8 | HASH GROUP BY | | 1 | 51 | 9 (12)| 00:00:01 | | 9 | TABLE ACCESS BY INDEX ROWID | CODE_TRANSLATION | 3 | 153 | 8 (0)| 00:00:01 | |* 10 | INDEX RANGE SCAN | CODE_TRANSLATION_IDX3 | 3 | | 7 (0)| 00:00:01 | |* 11 | HASH JOIN RIGHT OUTER | | 10360 | 19M| 1638 (2)| 00:00:20 | | 12 | VIEW | V_RM_FUTURES_CODE_TRANS_FLAT | 1 | 96 | 9 (12)| 00:00:01 | | 13 | HASH GROUP BY | | 1 | 51 | 9 (12)| 00:00:01 | | 14 | TABLE ACCESS BY INDEX ROWID | CODE_TRANSLATION | 3 | 153 | 8 (0)| 00:00:01 | |* 15 | INDEX RANGE SCAN | CODE_TRANSLATION_IDX3 | 3 | | 7 (0)| 00:00:01 | |* 16 | HASH JOIN RIGHT OUTER | | 10360 | 18M| 1629 (2)| 00:00:20 | | 17 | VIEW | V_RM_FUTURES_CODE_TRANS_FLAT | 404 | 42420 | 12 (9)| 00:00:01 | | 18 | HASH GROUP BY | | 404 | 20604 | 12 (9)| 00:00:01 | | 19 | TABLE ACCESS BY INDEX ROWID | CODE_TRANSLATION | 404 | 20604 | 11 (0)| 00:00:01 | |* 20 | INDEX RANGE SCAN | CODE_TRANS_TYPE_IDX | 404 | | 3 (0)| 00:00:01 | |* 21 | HASH JOIN RIGHT OUTER | | 10360 | 17M| 1616 (2)| 00:00:20 | | 22 | VIEW | V_RM_FUTURES_CODE_TRANS_FLAT | 404 | 42420 | 12 (9)| 00:00:01 | | 23 | HASH GROUP BY | | 404 | 20604 | 12 (9)| 00:00:01 | | 24 | TABLE ACCESS BY INDEX ROWID | CODE_TRANSLATION | 404 | 20604 | 11 (0)| 00:00:01 | |* 25 | INDEX RANGE SCAN | CODE_TRANS_TYPE_IDX | 404 | | 3 (0)| 00:00:01 | |* 26 | HASH JOIN RIGHT OUTER | | 10360 | 16M| 1603 (2)| 00:00:20 | | 27 | TABLE ACCESS FULL | SOURCE_SYSTEM | 68 | 748 | 3 (0)| 00:00:01 | |* 28 | HASH JOIN RIGHT OUTER | | 10360 | 16M| 1599 (2)| 00:00:20 | | 29 | TABLE ACCESS BY INDEX ROWID | CODE_TRANSLATION | 1 | 51 | 2 (0)| 00:00:01 | |* 30 | INDEX RANGE SCAN | CODE_TRANS_TYPE_IDX | 1 | | 1 (0)| 00:00:01 | | 31 | VIEW | | 10360 | 16M| 1597 (2)| 00:00:20 | |* 32 | HASH JOIN RIGHT OUTER | | 10360 | 7527K| 1597 (2)| 00:00:20 | | 33 | TABLE ACCESS BY INDEX ROWID | PARAMETER_CONTROL | 1 | 38 | 2 (0)| 00:00:01 | |* 34 | INDEX RANGE SCAN | PARAMETER_CONTROL_IDX3 | 1 | | 1 (0)| 00:00:01 | |* 35 | HASH JOIN RIGHT OUTER | | 10360 | 7142K| 1594 (2)| 00:00:20 | | 36 | VIEW | V_ENTITY_FLAT | 1742 | 282K| 35 (9)| 00:00:01 | |* 37 | HASH JOIN RIGHT OUTER | | 1742 | 229K| 35 (9)| 00:00:01 | | 38 | INDEX FAST FULL SCAN | ENTITY_IDX_5 | 1742 | 40066 | 6 (0)| 00:00:01 | |* 39 | HASH JOIN RIGHT OUTER | | 1742 | 190K| 28 (8)| 00:00:01 | | 40 | INDEX FAST FULL SCAN | ENTITY_IDX_5 | 1742 | 47034 | 6 (0)| 00:00:01 | |* 41 | HASH JOIN RIGHT OUTER | | 1742 | 144K| 21 (5)| 00:00:01 | | 42 | INDEX FAST FULL SCAN | ENTITY_IDX_5 | 1742 | 47034 | 6 (0)| 00:00:01 | |* 43 | HASH JOIN RIGHT OUTER | | 1742 | 98K| 15 (7)| 00:00:01 | | 44 | INDEX FAST FULL SCAN | ENTITY_IDX_5 | 1742 | 47034 | 6 (0)| 00:00:01 | | 45 | TABLE ACCESS FULL | ENTITY | 1742 | 54002 | 8 (0)| 00:00:01 | |* 46 | HASH JOIN RIGHT OUTER | | 2432 | 1282K| 1559 (1)| 00:00:19 | | 47 | TABLE ACCESS FULL | INSTRUMENT_ID_TYPE | 8 | 88 | 3 (0)| 00:00:01 | |* 48 | HASH JOIN RIGHT OUTER | | 2432 | 1256K| 1555 (1)| 00:00:19 | | 49 | TABLE ACCESS BY INDEX ROWID | PROCESSING_STATUS | 1 | 76 | 1 (0)| 00:00:01 | |* 50 | INDEX UNIQUE SCAN | PROCESSING_STATUS_PK | 1 | | 0 (0)| 00:00:01 | |* 51 | HASH JOIN RIGHT OUTER | | 2432 | 1075K| 1554 (1)| 00:00:19 | |* 52 | TABLE ACCESS BY INDEX ROWID | CODE_TRANSLATION | 1 | 65 | 2 (0)| 00:00:01 | |* 53 | INDEX RANGE SCAN | CODE_TRANS_TYPE_IDX | 1 | | 1 (0)| 00:00:01 | |* 54 | HASH JOIN RIGHT OUTER | | 2432 | 921K| 1551 (1)| 00:00:19 | |* 55 | TABLE ACCESS BY INDEX ROWID| CODE_TRANSLATION | 1 | 65 | 2 (0)| 00:00:01 | |* 56 | INDEX RANGE SCAN | CODE_TRANS_TYPE_IDX | 1 | | 1 (0)| 00:00:01 | |* 57 | TABLE ACCESS BY INDEX ROWID| POSITIONS | 2432 | 767K| 1549 (1)| 00:00:19 | |* 58 | INDEX RANGE SCAN | POSITIONS_IDX_01 | 5676 | | 737 (2)| 00:00:09 | |* 59 | INDEX RANGE SCAN | PARAMETER_CONTROL_IDX3 | 1 | 27 | 2 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------------------------------- </code></pre> <p>and here is the explain plan for the other view:</p> <pre><code>explain plan for select * from v_positions_rm_base where business_date = to_date((select current_business_date from v_business_day), 'mm/dd/yyyy'); ------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 171K| 324M| 366K (3)| 01:13:16 | |* 1 | FILTER | | | | | | |* 2 | FILTER | | | | | | |* 3 | HASH JOIN RIGHT OUTER | | 3436K| 6485M| 366K (3)| 01:13:16 | | 4 | INDEX FAST FULL SCAN | RM_SRC_ACCT_UDX | 601 | 7212 | 2 (0)| 00:00:01 | |* 5 | VIEW | | 3436K| 6446M| 366K (3)| 01:13:15 | |* 6 | HASH JOIN RIGHT OUTER | | 3436K| 6806M| 366K (3)| 01:13:15 | | 7 | VIEW | V_RM_FUTURES_CODE_TRANS_FLAT | 1 | 96 | 9 (12)| 00:00:01 | | 8 | HASH GROUP BY | | 1 | 51 | 9 (12)| 00:00:01 | | 9 | TABLE ACCESS BY INDEX ROWID | CODE_TRANSLATION | 3 | 153 | 8 (0)| 00:00:01 | |* 10 | INDEX RANGE SCAN | CODE_TRANSLATION_IDX3 | 3 | | 7 (0)| 00:00:01 | |* 11 | HASH JOIN RIGHT OUTER | | 3436K| 6492M| 366K (3)| 01:13:15 | | 12 | VIEW | V_RM_FUTURES_CODE_TRANS_FLAT | 1 | 96 | 9 (12)| 00:00:01 | | 13 | HASH GROUP BY | | 1 | 51 | 9 (12)| 00:00:01 | | 14 | TABLE ACCESS BY INDEX ROWID | CODE_TRANSLATION | 3 | 153 | 8 (0)| 00:00:01 | |* 15 | INDEX RANGE SCAN | CODE_TRANSLATION_IDX3 | 3 | | 7 (0)| 00:00:01 | |* 16 | HASH JOIN RIGHT OUTER | | 3436K| 6177M| 366K (3)| 01:13:14 | | 17 | VIEW | V_RM_FUTURES_CODE_TRANS_FLAT | 404 | 42420 | 12 (9)| 00:00:01 | | 18 | HASH GROUP BY | | 404 | 20604 | 12 (9)| 00:00:01 | | 19 | TABLE ACCESS BY INDEX ROWID | CODE_TRANSLATION | 404 | 20604 | 11 (0)| 00:00:01 | |* 20 | INDEX RANGE SCAN | CODE_TRANS_TYPE_IDX | 404 | | 3 (0)| 00:00:01 | |* 21 | HASH JOIN RIGHT OUTER | | 3436K| 5833M| 366K (3)| 01:13:13 | | 22 | VIEW | V_RM_FUTURES_CODE_TRANS_FLAT | 404 | 42420 | 12 (9)| 00:00:01 | | 23 | HASH GROUP BY | | 404 | 20604 | 12 (9)| 00:00:01 | | 24 | TABLE ACCESS BY INDEX ROWID | CODE_TRANSLATION | 404 | 20604 | 11 (0)| 00:00:01 | |* 25 | INDEX RANGE SCAN | CODE_TRANS_TYPE_IDX | 404 | | 3 (0)| 00:00:01 | |* 26 | HASH JOIN RIGHT OUTER | | 3436K| 5489M| 366K (3)| 01:13:13 | | 27 | TABLE ACCESS FULL | SOURCE_SYSTEM | 68 | 748 | 3 (0)| 00:00:01 | |* 28 | HASH JOIN RIGHT OUTER | | 3436K| 5453M| 365K (3)| 01:13:12 | | 29 | TABLE ACCESS BY INDEX ROWID | CODE_TRANSLATION | 1 | 51 | 2 (0)| 00:00:01 | |* 30 | INDEX RANGE SCAN | CODE_TRANS_TYPE_IDX | 1 | | 1 (0)| 00:00:01 | | 31 | VIEW | | 3436K| 5286M| 365K (3)| 01:13:11 | |* 32 | HASH JOIN RIGHT OUTER | | 3436K| 2438M| 365K (3)| 01:13:11 | | 33 | TABLE ACCESS BY INDEX ROWID | PARAMETER_CONTROL | 1 | 38 | 2 (0)| 00:00:01 | |* 34 | INDEX RANGE SCAN | PARAMETER_CONTROL_IDX3 | 1 | | 1 (0)| 00:00:01 | | 35 | MERGE JOIN OUTER | | 3436K| 2313M| 365K (3)| 01:13:11 | | 36 | MERGE JOIN OUTER | | 3436K| 2064M| 365K (3)| 01:13:11 | | 37 | SORT JOIN | | 806K| 357M| 365K (3)| 01:13:10 | | 38 | MERGE JOIN OUTER | | 806K| 357M| 365K (3)| 01:13:10 | | 39 | SORT JOIN | | 806K| 348M| 365K (3)| 01:13:10 | |* 40 | HASH JOIN RIGHT OUTER | | 806K| 348M| 365K (3)| 01:13:10 | |* 41 | TABLE ACCESS BY INDEX ROWID | CODE_TRANSLATION | 1 | 65 | 2 (0)| 00:00:01 | |* 42 | INDEX RANGE SCAN | CODE_TRANS_TYPE_IDX | 1 | | 1 (0)| 00:00:01 | |* 43 | HASH JOIN RIGHT OUTER | | 806K| 298M| 365K (3)| 01:13:10 | |* 44 | TABLE ACCESS BY INDEX ROWID| CODE_TRANSLATION | 1 | 65 | 2 (0)| 00:00:01 | |* 45 | INDEX RANGE SCAN | CODE_TRANS_TYPE_IDX | 1 | | 1 (0)| 00:00:01 | |* 46 | TABLE ACCESS FULL | POSITIONS | 806K| 248M| 365K (3)| 01:13:09 | |* 47 | SORT JOIN | | 8 | 88 | 4 (25)| 00:00:01 | | 48 | TABLE ACCESS FULL | INSTRUMENT_ID_TYPE | 8 | 88 | 3 (0)| 00:00:01 | |* 49 | SORT JOIN | | 1742 | 282K| 36 (12)| 00:00:01 | | 50 | VIEW | V_ENTITY_FLAT | 1742 | 282K| 35 (9)| 00:00:01 | |* 51 | HASH JOIN RIGHT OUTER | | 1742 | 229K| 35 (9)| 00:00:01 | | 52 | INDEX FAST FULL SCAN | ENTITY_IDX_5 | 1742 | 40066 | 6 (0)| 00:00:01 | |* 53 | HASH JOIN RIGHT OUTER | | 1742 | 190K| 28 (8)| 00:00:01 | | 54 | INDEX FAST FULL SCAN | ENTITY_IDX_5 | 1742 | 47034 | 6 (0)| 00:00:01 | |* 55 | HASH JOIN RIGHT OUTER | | 1742 | 144K| 21 (5)| 00:00:01 | | 56 | INDEX FAST FULL SCAN | ENTITY_IDX_5 | 1742 | 47034 | 6 (0)| 00:00:01 | |* 57 | HASH JOIN RIGHT OUTER | | 1742 | 98K| 15 (7)| 00:00:01 | | 58 | INDEX FAST FULL SCAN | ENTITY_IDX_5 | 1742 | 47034 | 6 (0)| 00:00:01 | | 59 | TABLE ACCESS FULL | ENTITY | 1742 | 54002 | 8 (0)| 00:00:01 | |* 60 | SORT JOIN | | 1 | 76 | 2 (50)| 00:00:01 | | 61 | TABLE ACCESS BY INDEX ROWID | PROCESSING_STATUS | 1 | 76 | 1 (0)| 00:00:01 | |* 62 | INDEX UNIQUE SCAN | PROCESSING_STATUS_PK | 1 | | 0 (0)| 00:00:01 | | 63 | MERGE JOIN CARTESIAN | | 1 | 119 | 4 (0)| 00:00:01 | | 64 | MERGE JOIN CARTESIAN | | 1 | 81 | 3 (0)| 00:00:01 | | 65 | MERGE JOIN CARTESIAN | | 1 | 54 | 2 (0)| 00:00:01 | |* 66 | INDEX RANGE SCAN | PARAMETER_CONTROL_IDX3 | 1 | 27 | 1 (0)| 00:00:01 | | 67 | BUFFER SORT | | 1 | 27 | 1 (0)| 00:00:01 | |* 68 | INDEX RANGE SCAN | PARAMETER_CONTROL_IDX3 | 1 | 27 | 1 (0)| 00:00:01 | | 69 | BUFFER SORT | | 1 | 27 | 2 (0)| 00:00:01 | |* 70 | INDEX RANGE SCAN | PARAMETER_CONTROL_IDX3 | 1 | 27 | 1 (0)| 00:00:01 | | 71 | BUFFER SORT | | 1 | 38 | 3 (0)| 00:00:01 | |* 72 | TABLE ACCESS BY INDEX ROWID | PARAMETER_CONTROL | 1 | 38 | 1 (0)| 00:00:01 | |* 73 | INDEX RANGE SCAN | PARAMETER_CONTROL_IDX1 | 2 | | 0 (0)| 00:00:01 | |* 74 | INDEX RANGE SCAN | PARAMETER_CONTROL_IDX3 | 1 | 27 | 2 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------------------------------- </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USDan
    1. USDan
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COTake a look at the explain plan to see what exactly is going on. Some questions: The subquery looks like it just pulls all values from that table so how many values are actually being returned there? Are there duplicate dates being returned as well?
      singulars
      1. POOracle query optimization using dynamic dates
      1. USMichael Cheng
    2. COthe subquery returns exactly one record. Right now, "select current_business_date from v_business_day" returns '09/17/2010'
      singulars
      1. POOracle query optimization using dynamic dates
      1. USDan
    3. COAnd how many records does Oracle THINK it's going to pull?
      singulars
      1. POOracle query optimization using dynamic dates
      1. USStephanie Page
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload