Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Muhammad Riaz.

Asked: November 16, 2022 - 6:06 am UTC

Last updated: November 28, 2022 - 1:22 pm UTC

Version: 12.1.0.2

Viewed 10K+ times! This question is

You Asked

Dear Team,

I have a logging table with the following structure:

SQL> desc t
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 LOG_ID                                                         NUMBER
 TRACEID                                                        VARCHAR2(250 CHAR)
 TYPE                                                           VARCHAR2(250 CHAR)
 NODE                                                           VARCHAR2(250 CHAR)
 URL                                                            VARCHAR2(4000 CHAR)
 TOKEN                                                          VARCHAR2(4000 CHAR)
 METHOD                                                         VARCHAR2(250 CHAR)
 TIMESTAMPREQ                                                   VARCHAR2(100 CHAR)
 BODY                                                           CLOB
 RESPONSE                                                       CLOB
 TIMESTAMPRES                                                   VARCHAR2(100 CHAR)
 REC_DATE                                                       DATE


In addition to other indexes, there is one index on columns (Trunc(Rec_Date), Type, Node, Method). I try to get min or max of trunc(rec_date) but surprisingly it doesn't uses the index min/max optimization. Below is the command and trace file output:

SELECT min(trunc(rec_date)) 
FROM t 
where trunc(rec_date) is not null


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.09          0          1          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2     94.40     567.28     256176     507776          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4     94.43     567.38     256176     507777          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 242  
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT AGGREGATE (cr=507776 pr=256176 pw=0 time=0 us starts=567286694)
   4176565    4176565    4176565   TABLE ACCESS FULL T (cr=507776 pr=256176 pw=0 time=86468 us starts=709057596 cost=86468 size=34748199 card=3860911)


SELECT min(trunc(rec_date)) 
FROM t
where rec_date is not null


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      5.84      17.41          5     259917          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      5.84      17.41          5     259918          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 242  
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT AGGREGATE (cr=259917 pr=5 pw=0 time=0 us starts=17415568)
   4184139    4184139    4184139   TABLE ACCESS FULL T (cr=259917 pr=5 pw=0 time=86242 us starts=106246737 cost=86242 size=34748199 card=3860911)


Can you please help me understanding why the query is not using min/max optimization and how can we fix it?

Regards,

and Chris said...

I'm not sure why either!

The decision to use a min/max scan is still cost-based. So either:

- Something's preventing the MIN/MAX option
- The optimizer thinks a full scan is cheaper (it has a lower cost)

To help us diagnose, please share:

The DDL for the table and index (create table & create index)

The plans for the query when hinted to use a full table scan and the index:

select /*+ full ( t ) */min(trunc(rec_date)) 
from t 
where trunc(rec_date) is not null;

select * 
from   table(dbms_xplan.display_cursor( format => 'TYPICAL' ));

select /*+ index ( t ( rec_date ) ) */min(trunc(rec_date)) 
from t 
where trunc(rec_date) is not null;

select * 
from   table(dbms_xplan.display_cursor( format => 'TYPICAL' ));

Rating

  (7 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

More details

Muhammad Riaz, November 17, 2022 - 5:11 am UTC

Hi Chris,

Thanks for the feedback. Please see bloew the simplified version of DDL of table and index and TKPROF output for the commands:

CREATE TABLE T 
(LOG_ID NUMBER, TRACEID VARCHAR2(250), TYPE VARCHAR2(250), NODE VARCHAR2(250), URL VARCHAR2(4000), TOKEN VARCHAR2(4000), METHOD VARCHAR2(250), TIMESTAMPREQ VARCHAR2(100), 
BODY CLOB, RESPONSE CLOB, TIMESTAMPRES VARCHAR2(100), REC_DATE DATE) 
TABLESPACE TBS1 LOGGING NOCOMPRESS LOB (BODY) STORE AS SECUREFILE ( TABLESPACE TBS1 RETENTION AUTO KEEP_DUPLICATES NOCOMPRESS STORAGE ( INITIAL 104K BUFFER_POOL DEFAULT) NOCACHE LOGGING ) 
LOB (RESPONSE) STORE AS SECUREFILE ( TABLESPACE TBS1 RETENTION AUTO KEEP_DUPLICATES NOCOMPRESS STORAGE ( INITIAL 104K BUFFER_POOL DEFAULT) NOCACHE LOGGING ) ENABLE ROW MOVEMENT
/


create index t_idx1 on t(trunc(rec_date), type, node, method) tablespace TBS1
/


select /*+ full ( t ) */min(trunc(rec_date))
from t
where trunc(rec_date) is not null

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      5.26       5.43          0     284463          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      5.26       5.43          0     284464          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 242  
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT AGGREGATE (cr=284463 pr=0 pw=0 time=5430957 us)
   4560699    4560699    4560699   TABLE ACCESS FULL T (cr=284463 pr=0 pw=0 time=96276223 us cost=94642 size=38478096 card=4275344)

********************************************************************************

select /*+ index ( t idx1 ) */min(trunc(rec_date))
from t
where trunc(rec_date) is not null

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      5.18       5.25          0     284459          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      5.18       5.26          0     284460          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 242  
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT AGGREGATE (cr=284459 pr=0 pw=0 time=5259466 us)
   4560770    4560770    4560770   TABLE ACCESS FULL T (cr=284459 pr=0 pw=0 time=92032403 us cost=94642 size=38478096 card=4275344)


Regards,

Correction

Muhammad Riaz, November 17, 2022 - 8:03 am UTC

Apologies for the typo in above comment; it's all result of trying to rename actual table/index, so for the command. Both commands, even the one with valid index hint, still go for FTS.
Chris Saxon
November 17, 2022 - 1:44 pm UTC

Thanks, I'm unable to reproduce this though (see below).

Both commands, even the one with valid index hint, still go for FTS

The optimizer always obeys hints if it's possible to do so.

So this suggests there's some problem with the index itself. Is it disabled? Invalid? Invisible? Has someone fiddled with hidden parameters? It seems something is preventing index usage.

create table t (
  log_id       number,
  traceid      varchar2 (250),
  type         varchar2 (250),
  node         varchar2 (250),
  url          varchar2 (4000),
  token        varchar2 (4000),
  method       varchar2 (250),
  timestampreq varchar2 (100),
  body         clob,
  response     clob,
  timestampres varchar2 (100),
  rec_date     date
) logging nocompress
  lob (response) store as securefile 
  enable row movement
/

create index t_idx1 on t (
  trunc (rec_date), type, node, method
)
/

set serveroutput off
select min(trunc(rec_date))
from   t
where  trunc(rec_date) is not null;

select * 
from   table(dbms_xplan.display_cursor());

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |       |       |     1 (100)|          |
|   1 |  SORT AGGREGATE             |        |     1 |     9 |            |          |
|   2 |   FIRST ROW                 |        |     1 |     9 |     1   (0)| 00:00:01 |
|*  3 |    INDEX FULL SCAN (MIN/MAX)| T_IDX1 |     1 |     9 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

More details

Muhammad Riaz, November 18, 2022 - 5:15 am UTC

Thanks for the feedback. The index is visible/valid. In fact, we I try to do range scan on index by using trunc(rec_date) between <Val1> and <Val2>, it does so as expected. It's only min/max optimization that is not happening:

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
_addm_auto_enable                    boolean                          FALSE
_ash_disk_write_enable               boolean                          FALSE
_ash_enable                          boolean                          FALSE
_ash_size                            big integer                      300M
_awr_restrict_mode                   boolean                          TRUE
_disable_file_resize_logging         boolean                          TRUE
_kghdsidx_count                      integer                          2
_kgl_large_heap_warning_threshold    integer                          83886080
_optimizer_cost_based_transformation string                           OFF
_report_capture_cycle_time           integer                          0
_rollback_segment_count              integer                          100
_swrf_mmon_flush                     boolean                          FALSE
_swrf_mmon_metrics                   boolean                          FALSE


SQL> explain plan for
  2  select * FROM t where trunc(rec_date) between to_date(:1) and to_date(:2);

Explained.

SQL> select * from table( dbms_xplan.display(format=>'+note'))
  2  /


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 945958992

----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                                | 14851 |   122M|  3020   (1)| 00:00:22 |
|*  1 |  FILTER                      |                                |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T                | 14851 |   122M|  3020   (1)| 00:00:
|*  3 |    INDEX RANGE SCAN          | T_IDX1         | 26732 |       |   167   (1)| 00:00
----------------------------------------------------------------------------------------------------

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

   1 - filter(TO_DATE(:2)>=TO_DATE(:1))
   3 - access(TRUNC(INTERNAL_FUNCTION("REC_DATE"))>=TO_DATE(:1) AND
              TRUNC(INTERNAL_FUNCTION("REC_DATE"))<=TO_DATE(:2))

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

21 rows selected.

Connor McDonald
November 21, 2022 - 12:08 pm UTC

You're definitely sure its a DATE column.

TRUNC(INTERNAL_FUNCTION("REC_DATE"))

suggests a data type conversion is going on

More details

Muhammad Riaz, November 21, 2022 - 12:44 pm UTC

I've also noticed that but I am unable to find the reason behined that :(

The column is of DATE data type for sure. I also ran the simple test case and result is consistent:

SQL> create table t (
  2    log_id       number,
  3    traceid      varchar2 (250),
  4    type         varchar2 (250),
  5    node         varchar2 (250),
  6    url          varchar2 (4000),
  7    token        varchar2 (4000),
  8    method       varchar2 (250),
  9    timestampreq varchar2 (100),
 10    body         clob,
 11    response     clob,
 12    timestampres varchar2 (100),
 13    rec_date     date
 14  ) logging nocompress
 15    lob (response) store as securefile
 16    enable row movement
 17  /

Table created.

SQL> 
SQL> create index t_idx1 on t (
  2    trunc (rec_date), type, node, method
  3  )
  4  /

Index created.

SQL> 
SQL> set serveroutput off
SQL> select min(trunc(rec_date))
  2  from   t
  3  where  trunc(rec_date) is not null;

MIN(TRUNC
---------


SQL> 
SQL> select *
  2  from   table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  31b2as31rrbtv, child number 0
-------------------------------------
select min(trunc(rec_date)) from   t where  trunc(rec_date) is not null

Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     9 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |     1 |     9 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter(TRUNC(INTERNAL_FUNCTION("REC_DATE")) IS NOT NULL)

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


23 rows selected.


It's really confusing.
Chris Saxon
November 21, 2022 - 2:22 pm UTC

I don't know what the issue is here.

I see you're on 12.1.0.2 - I've not got an instance for this version handy to double-check. Support for this release is over (unless you've got a custom contract).

So if this is a problem for you, I suggest creating a plain index on REC_DATE and changing the query to:

select trunc(min(rec_date))
from   t
where  rec_date is not null;


But really you should be looking to upgrade to 19c soon.

More details

Muhammad Riaz, November 22, 2022 - 6:25 am UTC

The plan to upgrade to 19c is already in piepline.

There is something very very strange, though. Even when I create index on rec_date with TRUNC function, it still doesn't use the index:
SQL> ed
Wrote file afiedt.buf

  1  create index t_idx1 on t (
  2    rec_date, type, node, method
  3* )
SQL> /

Index created.

SQL> ed
Wrote file afiedt.buf

  1   explain plan for
  2   select min(rec_date)
  3   from   t
  4*  where  rec_date is not null
SQL> /

Explained.

SQL> @explain1

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     9 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     9 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |     1 |     9 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("REC_DATE" IS NOT NULL)

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


Chris Saxon
November 28, 2022 - 1:22 pm UTC

What happens if you create an index ONLY on REC_DATE? Can you get the min/max optimization to work for any queries on any table?

If the answer to the second question is no, there's something about your environment that prevents this. I don't know what that might be.

Could the function-based index be part of the reason?

Patrick, November 30, 2022 - 5:18 pm UTC

One question - why is the table indexed on TRUNC(rec_date) instead of rec_date?

I wonder if the problem is the nesting of the TRUNC function in the MIN and MAX function calls. What happens if you change the statement to 

SELECT TRUNC(MIN(rec_date)) FROM t WHERE rec_date IS NOT NULL

?


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.