Oracle Tips and Tricks — David Fitzjarrell

November 17, 2016

“That STILL Ain’t Right”

Filed under: General — dfitzjarrell @ 07:45

"If you want sense, you'll have to make it yourself."
- Norton Juster, The Phantom Tollbooth

In a recent post to an Oracle forum a query was presented and a tuning request was made. It appears that the query was taking 20 hours to complete. Through further interrogation and responses it was discovered that the dates were being stored in a VARCHAR2 column and implicit date conversion was being used. To show how much of a problem this can cause the following example was created; notice the results returned and the execution plans generated for each run of the query, once with the table defined in the manner the original poster described and one with the date column using the DATE datatype. We begin:


SQL>
SQL> create table datetst(
  2  myid    number,
  3  res_value	varchar2(3),
  4  mydt    varchar2(20));

Table created.

SQL>
SQL> create index datetest_idx on datetst(res_value);

Index created.

SQL>
SQL> begin
  2  	     for i in 1..10000 loop
  3  		     if mod(i,23) = 0 then
  4  		     insert into datetst
  5  		     values(i, 'PUR', to_char(sysdate+i, 'MM/DD/RRRR'));
  6  		     else
  7  		     insert into datetst
  8  		     values(i, 'BID', to_char(sysdate+i, 'MM/DD/RRRR'));
  9  		     end if;
 10  	     end loop;
 11
 12  	     commit;
 13  end;
 14  /

PL/SQL procedure successfully completed.

SQL>

Let’s now run a query using conditions similar to the posted query and see what Oracle returns:


SQL>
SQL>
SQL> select *
  2  from datetst
  3  where mydt <= sysdate +230;
where mydt <= sysdate +230
      *
ERROR at line 3:
ORA-01843: not a valid month


SQL>

As expected the implicit date conversion failed; modifying the query to explicitly convert the strings to dates produces ‘interesting’ results:


SQL>
SQL> select *
  2  from datetst
  3  where res_value = 'PUR'
  4  and mydt <= to_char(sysdate +230, 'MM/DD/RRRR');

      MYID RES MYDT
---------- --- --------------------
       920 PUR 05/26/2019
       943 PUR 06/18/2019
      1150 PUR 01/11/2020
      1173 PUR 02/03/2020
      1196 PUR 02/26/2020
      1219 PUR 03/20/2020
      1242 PUR 04/12/2020
      1265 PUR 05/05/2020
      1288 PUR 05/28/2020
      1311 PUR 06/20/2020
        46 PUR 01/02/2017
        69 PUR 01/25/2017
        92 PUR 02/17/2017
       115 PUR 03/12/2017
       138 PUR 04/04/2017
       161 PUR 04/27/2017
       184 PUR 05/20/2017
       207 PUR 06/12/2017
       230 PUR 07/05/2017
       414 PUR 01/05/2018
       437 PUR 01/28/2018
       460 PUR 02/20/2018
       483 PUR 03/15/2018
       506 PUR 04/07/2018
       529 PUR 04/30/2018
       552 PUR 05/23/2018
       575 PUR 06/15/2018
       782 PUR 01/08/2019
       805 PUR 01/31/2019
       828 PUR 02/23/2019
       851 PUR 03/18/2019
       874 PUR 04/10/2019
       897 PUR 05/03/2019
      2622 PUR 01/22/2024
      2645 PUR 02/14/2024
      2668 PUR 03/08/2024
      2691 PUR 03/31/2024
      2714 PUR 04/23/2024
      3335 PUR 01/04/2026
      3358 PUR 01/27/2026
      3381 PUR 02/19/2026
      3404 PUR 03/14/2026
      3427 PUR 04/06/2026
      3450 PUR 04/29/2026
      3473 PUR 05/22/2026
      3496 PUR 06/14/2026
      3703 PUR 01/07/2027
      3726 PUR 01/30/2027
      3749 PUR 02/22/2027
      3772 PUR 03/17/2027
      3795 PUR 04/09/2027
      3818 PUR 05/02/2027
      3841 PUR 05/25/2027
      3864 PUR 06/17/2027
      2737 PUR 05/16/2024
      2760 PUR 06/08/2024
      2783 PUR 07/01/2024
      2967 PUR 01/01/2025
      2990 PUR 01/24/2025
      3013 PUR 02/16/2025
      3036 PUR 03/11/2025
      3059 PUR 04/03/2025
      3082 PUR 04/26/2025
      3105 PUR 05/19/2025
      3128 PUR 06/11/2025
      3151 PUR 07/04/2025
      1518 PUR 01/13/2021
      1541 PUR 02/05/2021
      1564 PUR 02/28/2021
      1587 PUR 03/23/2021
      1610 PUR 04/15/2021
      1633 PUR 05/08/2021
      1656 PUR 05/31/2021
      1679 PUR 06/23/2021
      1886 PUR 01/16/2022
      1909 PUR 02/08/2022
      1932 PUR 03/03/2022

      MYID RES MYDT
---------- --- --------------------
      1955 PUR 03/26/2022
      1978 PUR 04/18/2022
      2001 PUR 05/11/2022
      2024 PUR 06/03/2022
      2047 PUR 06/26/2022
      2254 PUR 01/19/2023
      2277 PUR 02/11/2023
      2300 PUR 03/06/2023
      2323 PUR 03/29/2023
      2346 PUR 04/21/2023
      2369 PUR 05/14/2023
      2392 PUR 06/06/2023
      2415 PUR 06/29/2023
      5543 PUR 01/21/2032
      5566 PUR 02/13/2032
      5589 PUR 03/07/2032
      5612 PUR 03/30/2032
      5635 PUR 04/22/2032
      5658 PUR 05/15/2032
      5681 PUR 06/07/2032
      5704 PUR 06/30/2032
      5911 PUR 01/23/2033
      5934 PUR 02/15/2033
      5957 PUR 03/10/2033
      5980 PUR 04/02/2033
      6003 PUR 04/25/2033
      6026 PUR 05/18/2033
      4071 PUR 01/10/2028
      4094 PUR 02/02/2028
      4117 PUR 02/25/2028
      4140 PUR 03/19/2028
      4163 PUR 04/11/2028
      4186 PUR 05/04/2028
      4209 PUR 05/27/2028
      4232 PUR 06/19/2028
      4439 PUR 01/12/2029
      4462 PUR 02/04/2029
      4485 PUR 02/27/2029
      4508 PUR 03/22/2029
      4531 PUR 04/14/2029
      4554 PUR 05/07/2029
      4577 PUR 05/30/2029
      4600 PUR 06/22/2029
      4807 PUR 01/15/2030
      4830 PUR 02/07/2030
      4853 PUR 03/02/2030
      4876 PUR 03/25/2030
      4899 PUR 04/17/2030
      4922 PUR 05/10/2030
      4945 PUR 06/02/2030
      4968 PUR 06/25/2030
      5175 PUR 01/18/2031
      5198 PUR 02/10/2031
      5221 PUR 03/05/2031
      5244 PUR 03/28/2031
      5267 PUR 04/20/2031
      5290 PUR 05/13/2031
      5313 PUR 06/05/2031
      5336 PUR 06/28/2031
      6992 PUR 01/09/2036
      7015 PUR 02/01/2036
      7038 PUR 02/24/2036
      7061 PUR 03/18/2036
      7084 PUR 04/10/2036
      7107 PUR 05/03/2036
      7130 PUR 05/26/2036
      7153 PUR 06/18/2036
      7866 PUR 06/01/2038
      7889 PUR 06/24/2038
      8096 PUR 01/17/2039
      8119 PUR 02/09/2039
      8142 PUR 03/04/2039
      8165 PUR 03/27/2039
      8188 PUR 04/19/2039
      8211 PUR 05/12/2039
      8234 PUR 06/04/2039
      8257 PUR 06/27/2039

      MYID RES MYDT
---------- --- --------------------
      7360 PUR 01/11/2037
      7383 PUR 02/03/2037
      7406 PUR 02/26/2037
      7429 PUR 03/21/2037
      7452 PUR 04/13/2037
      7475 PUR 05/06/2037
      7498 PUR 05/29/2037
      7521 PUR 06/21/2037
      7728 PUR 01/14/2038
      7751 PUR 02/06/2038
      7774 PUR 03/01/2038
      7797 PUR 03/24/2038
      7820 PUR 04/16/2038
      7843 PUR 05/09/2038
      6049 PUR 06/10/2033
      6072 PUR 07/03/2033
      6256 PUR 01/03/2034
      6279 PUR 01/26/2034
      6302 PUR 02/18/2034
      6325 PUR 03/13/2034
      6348 PUR 04/05/2034
      6371 PUR 04/28/2034
      6394 PUR 05/21/2034
      6417 PUR 06/13/2034
      6624 PUR 01/06/2035
      6647 PUR 01/29/2035
      6670 PUR 02/21/2035
      6693 PUR 03/16/2035
      6716 PUR 04/08/2035
      6739 PUR 05/01/2035
      6762 PUR 05/24/2035
      6785 PUR 06/16/2035
      9982 PUR 03/17/2044
      8464 PUR 01/20/2040
      8487 PUR 02/12/2040
      8510 PUR 03/06/2040
      8533 PUR 03/29/2040
      8556 PUR 04/21/2040
      8579 PUR 05/14/2040
      8602 PUR 06/06/2040
      8625 PUR 06/29/2040
      8832 PUR 01/22/2041
      8855 PUR 02/14/2041
      8878 PUR 03/09/2041
      8901 PUR 04/01/2041
      8924 PUR 04/24/2041
      8947 PUR 05/17/2041
      8970 PUR 06/09/2041
      8993 PUR 07/02/2041
      9177 PUR 01/02/2042
      9200 PUR 01/25/2042
      9223 PUR 02/17/2042
      9246 PUR 03/12/2042
      9269 PUR 04/04/2042
      9292 PUR 04/27/2042
      9315 PUR 05/20/2042
      9338 PUR 06/12/2042
      9545 PUR 01/05/2043
      9568 PUR 01/28/2043
      9591 PUR 02/20/2043
      9614 PUR 03/15/2043
      9637 PUR 04/07/2043
      9660 PUR 04/30/2043
      9683 PUR 05/23/2043
      9706 PUR 06/15/2043
      9913 PUR 01/08/2044
      9936 PUR 01/31/2044
      9959 PUR 02/23/2044

222 rows selected.

SQL>

The query should have returned no more than 10 rows that met the criteria, and it returned 222. Looking at the plan we see:


SQL>
SQL> select * From table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  6qatrtphp5wjt, child number 0
-------------------------------------
select * from datetst where res_value = 'PUR' and mydt <=
to_char(sysdate +230, 'MM/DD/RRRR')

Plan hash value: 3255216368

----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |       |       |     2 (100)|          |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| DATETST      |   222 |  6216 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | DATETEST_IDX |   434 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("MYDT"<=TO_CHAR(SYSDATE@!+230,'MM/DD/RRRR'))
   2 - access("RES_VALUE"='PUR')

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


25 rows selected.

SQL>

Oracle found 222 rows that ‘matched’ the conditions, illustrating a problem of using an incorrect datatype; Oracle can’t know these are dates and compares them as ASCII strings creating a result set that is much larger than it should be. We drop the table and start over:


SQL>
SQL> drop table datetst purge;

Table dropped.

SQL>
SQL> create table datetst(
  2  myid    number,
  3  res_value varchar2(3),
  4  mydt    date);

Table created.

SQL>
SQL> create index datetest_idx on datetst(res_value);

Index created.

SQL>
SQL> begin
  2  	     for i in 1..10000 loop
  3  		     if mod(i,23) = 0 then
  4  		     insert into datetst
  5  		     values(i, 'PUR', sysdate+i);
  6  		     else
  7  		     insert into datetst
  8  		     values(i, 'BID', sysdate+i);
  9  		     end if;
 10  	     end loop;
 11
 12  	     commit;
 13  end;
 14  /

PL/SQL procedure successfully completed.

SQL>

We now run the original query (that didn’t have explicit date conversion, since we no longer need it) and examine the results:


SQL>
SQL> select *
  2  from datetst
  3  where res_value = 'PUR'
  4  and mydt <= sysdate +230;

      MYID RES MYDT
---------- --- ---------
        23 PUR 10-DEC-16
        46 PUR 02-JAN-17
        69 PUR 25-JAN-17
        92 PUR 17-FEB-17
       115 PUR 12-MAR-17
       138 PUR 04-APR-17
       161 PUR 27-APR-17
       184 PUR 20-MAY-17
       207 PUR 12-JUN-17
       230 PUR 05-JUL-17

10 rows selected.

SQL>
SQL> select * From table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  0m2c2mv7zhx49, child number 0
-------------------------------------
select * from datetst where res_value = 'PUR' and mydt <= sysdate +230

Plan hash value: 3255216368

----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |       |       |     2 (100)|          |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| DATETST      |    10 |   250 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | DATETEST_IDX |   434 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("MYDT"<=SYSDATE@!+230)
   2 - access("RES_VALUE"='PUR')

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


24 rows selected.

SQL>

Oracle now found the 10 rows we sought using the conditions we specified because the date data was correctly stored as a DATE datatype. Using VARCHAR2 made the result set 2,220 percent larger, and that was for a 10000 row table. Let’s re-run the example with 1,000,000 rows and see what numbers Oracle produces:


SQL>
SQL> create table datetst(
  2  myid    number,
  3  res_value	varchar2(3),
  4  mydt    varchar2(20));

Table created.

SQL>
SQL> create index datetest_idx on datetst(res_value);

Index created.

SQL>
SQL> begin
  2  	     for i in 1..1000000 loop
  3  		     if mod(i,23) = 0 then
  4  		     insert into datetst
  5  		     values(i, 'PUR', to_char(sysdate+i, 'MM/DD/RRRR'));
  6  		     else
  7  		     insert into datetst
  8  		     values(i, 'BID', to_char(sysdate+i, 'MM/DD/RRRR'));
  9  		     end if;
 10  	     end loop;
 11
 12  	     commit;
 13  end;
 14  /

PL/SQL procedure successfully completed.

SQL>
SQL> select *
  2  from datetst
  3  where mydt <= sysdate +230;
where mydt <= sysdate +230
      *
ERROR at line 3:
ORA-01843: not a valid month


SQL>
SQL> select *
  2  from datetst
  3  where res_value = 'PUR'
  4  and mydt <= to_char(sysdate +230, 'MM/DD/RRRR');

      MYID RES MYDT
---------- --- --------------------
       920 PUR 05/26/2019
       943 PUR 06/18/2019
      1150 PUR 01/11/2020
      1173 PUR 02/03/2020
      1196 PUR 02/26/2020
      1219 PUR 03/20/2020
      1242 PUR 04/12/2020
      1265 PUR 05/05/2020
      1288 PUR 05/28/2020
      1311 PUR 06/20/2020
        46 PUR 01/02/2017
        69 PUR 01/25/2017
        92 PUR 02/17/2017
       115 PUR 03/12/2017
       138 PUR 04/04/2017
       161 PUR 04/27/2017
       184 PUR 05/20/2017
       207 PUR 06/12/2017
       230 PUR 07/05/2017
       414 PUR 01/05/2018
       437 PUR 01/28/2018
       460 PUR 02/20/2018
       483 PUR 03/15/2018
       506 PUR 04/07/2018
       529 PUR 04/30/2018
       552 PUR 05/23/2018
       575 PUR 06/15/2018
       782 PUR 01/08/2019
       805 PUR 01/31/2019
       828 PUR 02/23/2019
       851 PUR 03/18/2019
       874 PUR 04/10/2019
       897 PUR 05/03/2019
      2622 PUR 01/22/2024
      2645 PUR 02/14/2024
      2668 PUR 03/08/2024
      2691 PUR 03/31/2024
      2714 PUR 04/23/2024
      3335 PUR 01/04/2026
      3358 PUR 01/27/2026
      3381 PUR 02/19/2026
      3404 PUR 03/14/2026
      3427 PUR 04/06/2026
      3450 PUR 04/29/2026
      3473 PUR 05/22/2026
      3496 PUR 06/14/2026
      3703 PUR 01/07/2027
      3726 PUR 01/30/2027
      3749 PUR 02/22/2027
      3772 PUR 03/17/2027
      3795 PUR 04/09/2027
      3818 PUR 05/02/2027
      3841 PUR 05/25/2027
      3864 PUR 06/17/2027
      2737 PUR 05/16/2024
      2760 PUR 06/08/2024
      2783 PUR 07/01/2024
      2967 PUR 01/01/2025
      2990 PUR 01/24/2025
      3013 PUR 02/16/2025
      3036 PUR 03/11/2025
      3059 PUR 04/03/2025
      3082 PUR 04/26/2025
      3105 PUR 05/19/2025
      3128 PUR 06/11/2025
      3151 PUR 07/04/2025
      1518 PUR 01/13/2021
      1541 PUR 02/05/2021
      1564 PUR 02/28/2021
      1587 PUR 03/23/2021
      1610 PUR 04/15/2021
      1633 PUR 05/08/2021
      1656 PUR 05/31/2021
      1679 PUR 06/23/2021
      1886 PUR 01/16/2022
      1909 PUR 02/08/2022
      1932 PUR 03/03/2022
...
    986309 PUR 04/21/4717
    986332 PUR 05/14/4717
    986355 PUR 06/06/4717
    986378 PUR 06/29/4717
    990955 PUR 01/09/4730
    990978 PUR 02/01/4730
    991001 PUR 02/24/4730
    991024 PUR 03/19/4730
    995394 PUR 03/06/4742
    995417 PUR 03/29/4742
    995440 PUR 04/21/4742
    995463 PUR 05/14/4742
    995486 PUR 06/06/4742
    995509 PUR 06/29/4742
    986585 PUR 01/22/4718
    986608 PUR 02/14/4718
    986631 PUR 03/09/4718
    986654 PUR 04/01/4718
    986677 PUR 04/24/4718
    991047 PUR 04/11/4730
    991070 PUR 05/04/4730
    991093 PUR 05/27/4730
    991116 PUR 06/19/4730
    991323 PUR 01/12/4731
    995716 PUR 01/22/4743
    995739 PUR 02/14/4743
    995762 PUR 03/09/4743
    995785 PUR 04/01/4743
    995808 PUR 04/24/4743
    995831 PUR 05/17/4743
    995854 PUR 06/09/4743
    995877 PUR 07/02/4743

22054 rows selected.

SQL>
SQL> select * From table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  6qatrtphp5wjt, child number 0
-------------------------------------
select * from datetst where res_value = 'PUR' and mydt <=
to_char(sysdate +230, 'MM/DD/RRRR')

Plan hash value: 3255216368

----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |       |       |   158 (100)|          |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| DATETST      | 25757 |   704K|   158   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | DATETEST_IDX | 49112 |       |   158   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("MYDT"<=TO_CHAR(SYSDATE@!+230,'MM/DD/RRRR'))
   2 - access("RES_VALUE"='PUR')

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


25 rows selected.

SQL>
SQL> drop table datetst purge;

Table dropped.

SQL>

SQL> create table datetst(
  2  myid    number,
  3  res_value varchar2(3),
  4  mydt    date);

Table created.

SQL>
SQL> create index datetest_idx on datetst(res_value);

Index created.

SQL>
SQL> begin
  2  	     for i in 1..1000000 loop
  3  		     if mod(i,23) = 0 then
  4  		     insert into datetst
  5  		     values(i, 'PUR', sysdate+i);
  6  		     else
  7  		     insert into datetst
  8  		     values(i, 'BID', sysdate+i);
  9  		     end if;
 10  	     end loop;
 11
 12  	     commit;
 13  end;
 14  /

PL/SQL procedure successfully completed.

SQL>
SQL> select *
  2  from datetst
  3  where res_value = 'PUR'
  4  and mydt <= sysdate +230;

      MYID RES MYDT
---------- --- ---------
        23 PUR 10-DEC-16
        46 PUR 02-JAN-17
        69 PUR 25-JAN-17
        92 PUR 17-FEB-17
       115 PUR 12-MAR-17
       138 PUR 04-APR-17
       161 PUR 27-APR-17
       184 PUR 20-MAY-17
       207 PUR 12-JUN-17
       230 PUR 05-JUL-17

10 rows selected.

SQL>
SQL> select * From table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  0m2c2mv7zhx49, child number 0
-------------------------------------
select * from datetst where res_value = 'PUR' and mydt <= sysdate +230

Plan hash value: 3255216368

----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |       |       |   148 (100)|          |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| DATETST      |    35 |   875 |   148   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | DATETEST_IDX | 46482 |       |   148   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("MYDT"<=SYSDATE@!+230)
   2 - access("RES_VALUE"='PUR')

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


24 rows selected.

SQL>

With 1,000,000 rows of data Oracle inflated the original 10-row result set to 22,054 rows, a whopping 220,440 percent increase. As the data volumes increase this result set will increase to even larger numbers, resulting in extremely long query times and vast numbers of incorrect results, something I doubt the original poster had counted on.

Using the correct datatype is essential in ensuring Oracle can do its job and do it properly, returning result sets that are reliable. Storing data in formst that doesn’t reflect the actual data type can be disastrous, as illustrated here. It pays when writing or purchasing applications that the proper datatype is in force for the columns being used.

It only makes sense.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: