This forum is currently locked. You can't register or post questions at this time. (read more)

Indexing on nullable date column

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

SwapnilShimpi
Member
Posts: 4
Joined: Tue Jun 10, 2014 12:01 pm

Indexing on nullable date column

Postby SwapnilShimpi » Tue Jun 10, 2014 12:46 pm

Hi Team,
I have table PS_HEADER containing two date colums:
created_dt and sale_dt.
Sale_dt can have null values.

In where clause of select queries when we have conditions like :
where trunc(sale_dt)= trunc(input_parameter);
or
then queries runs much longer for minutes.

I converted null and created function based index as -
CREATE INDEX PS_H_IDX1 ON PS_HEADER (NVL(TRUNC(SALE_DT),TO_DATE(' 1899-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')));
and replaced where cluase as
where NVL(trunc(sale_dt),,TO_DATE(' 1899-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))= trunc(input_parameter);

Then query performance was much good as index range scan is used.

But now few queirs contains clause like -
where NVL(trunc(sale_dt),trunc(created_dt))= trunc(input_parameter);
or
where NVL(trunc(sale_dt),trunc(created_dt))<= trunc(input_parameter);

Creating one more functional index is not useful.

Can you pelase suggest better approach.!!

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Indexing on nullable date column

Postby Tim... » Tue Jun 10, 2014 2:14 pm

Hi.

The real solutions is not to trunc...

Code: Select all

AND sale_dt BETWEEN trunc(input_parameter) AND trunc(input_parameter)+(1-1/60/24)


Now the query can use a regular index.

Code: Select all

test@db11g> select trunc(sysdate), trunc(sysdate)+(1-1/60/24) from dual;

TRUNC(SYSDATE)       TRUNC(SYSDATE)+(1-1/
-------------------- --------------------
10-JUN-2014 00:00:00 10-JUN-2014 23:59:00

1 row selected.

test@db11g>


Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

SwapnilShimpi
Member
Posts: 4
Joined: Tue Jun 10, 2014 12:01 pm

Re: Indexing on nullable date column

Postby SwapnilShimpi » Wed Jun 11, 2014 9:59 am

Thanks it helped me a lot .
How to tune queries which have below condition:
sale_dt <= (input_parameter) or
sale_dt < (input_parameter)

Max time input_parameter is sysdate.. so it needs to scan high range of data and it prefers to do full table scan only.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Indexing on nullable date column

Postby Tim... » Wed Jun 11, 2014 10:22 am

Hi.

Code: Select all

sale_dt <= (input_parameter) or sale_dt < (input_parameter)


Is the same as,

Code: Select all

sale_dt <= (input_parameter)


You don't need the "OR". Just remove the second part of the expression.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

SwapnilShimpi
Member
Posts: 4
Joined: Tue Jun 10, 2014 12:01 pm

Re: Indexing on nullable date column

Postby SwapnilShimpi » Wed Jun 11, 2014 10:29 am

Hi Sorry for creating confusion .
I wanted to say that query either contains sale_dt <= (input_parameter) condition or
sale_dt < (input_parameter) condition.

In either case ,since there is no lower bound for index scan, optimizer selects full table scan.
This problem is with all range operators if we do not have defined proper condition to limit the scan (Correct me If my understanding is wrong)

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Indexing on nullable date column

Postby Tim... » Wed Jun 11, 2014 10:48 am

Hi.

Oh. OK. :)

If it is an indexed column, it could still choose to do an index range scan if that were the preferred solution.

I guess you need to ask yourself, what percentage of the data are you likely to return using this predicate? If the predicate is very selective, then the index range scan is preferable. If the predicate will not eliminate many rows, then the full table scan is preferable...

Here is an example. Create and populate a test table.

Code: Select all

DROP TABLE t1 PURGE;

CREATE TABLE t1 (
  id NUMBER,
  description VARCHAR2(50),
  valid_date DATE
);

INSERT INTO t1
SELECT level,
       'Description for ' || level,
       sysdate+level
FROM dual
CONNECT BY level <= 10000;
COMMIT;

EXEC DBMS_STATS.gather_table_stats('test','t1');


Do a query that pulls back very few rows. Notice, not index yet.

Code: Select all

SET AUTOTRACE ON

SELECT description
FROM   t1
WHERE  valid_date < sysdate+3;

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     2 |    58 |    17   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     2 |    58 |    17   (0)| 00:00:01 |
--------------------------------------------------------------------------


Now add an index and do the same thing.

Code: Select all

CREATE INDEX t1_date_idx ON t1(valid_date);
EXEC DBMS_STATS.gather_table_stats('test','t1');

SET AUTOTRACE ON

SELECT description
FROM   t1
WHERE  valid_date < sysdate+3;

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     2 |    58 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1          |     2 |    58 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_DATE_IDX |     2 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------


So we used the index rather than full scanning the table. Cool!

Now we will use the same type of statement, but use a date criteria that will pull back most of the rows. Using the index now would be a disaster.

Code: Select all

SET AUTOTRACE ON

SELECT description
FROM   t1
WHERE  valid_date < sysdate+9000;

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  9000 |   254K|    17   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |  9000 |   254K|    17   (0)| 00:00:01 |
--------------------------------------------------------------------------


Great. We FTS the table, which is exactly what we want... :)

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

SwapnilShimpi
Member
Posts: 4
Joined: Tue Jun 10, 2014 12:01 pm

Re: Indexing on nullable date column

Postby SwapnilShimpi » Thu Jun 12, 2014 7:08 am

Great. Nicely explained. I have solved 3-4 issues on same line.
Can you please help me further.
I am trying to tune following query. It shows 00:00:31 as per execution plan. Actually query runs longer than 10mins .
In our project majority of the performance issues are due to date clause in where clause as reports are fetched for past data which is of almost 2 years.

I am in learning phase and not yet good in interpreting understanding Explain plan.

Thanks for your help once again.


Code: Select all

 explain plan for     SELECT NVL(MIN (PH.CREATED_DT),SYSDATE+1)
      --INTO V_SYSTEM_MIN_DATE
      FROM pos.POS_SALES_HEADER PH,
        pos.POS_SALES_DETAILS P,
        pos.ITEM_MASTER I,
        pos.V_ITEM_GROUP V,
        SAI.BUILDING B,
        SAI.COUNTER C,
        SAI.LOCATION L
      WHERE PH.SALES_ID  =P.SALES_ID
      AND P.ITEM_ID      =I.ITEM_ID
      AND I.ITEM_GROUP_ID=V.ITEM_GROUP_ID
      AND PH.COUNTER_ID  =C.COUNTERID
      AND C.BUILDINGID   = B.BUILDINGID
      AND B.LOCATIONID   =L.LOCATIONID
      AND L.LOCATIONID   =843
      AND V.MODULE_NAME  ='CANTEEN'
      AND nvl(PH.SALE_IND,'GN')='GN'
      AND PH.SALES_STATUS=1
      AND nvl(ph.glsynced,'N')   ='N'
      --AND ph.created_dt  < v_shiftstart ;
      AND (ph.created_dt) <  trunc(sysdate)+1 ;


Code: Select all

      select * from table(dbms_xplan.display);


-------------------------------------------------------------------------------


Plan hash value: 1192371418
 
----------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                     |     1 |    78 |  2555   (1)| 00:00:31 |
|   1 |  SORT AGGREGATE                    |                     |     1 |    78 |            |          |
|*  2 |   HASH JOIN                        |                     |    28 |  2184 |  2555   (1)| 00:00:31 |
|   3 |    MERGE JOIN CARTESIAN            |                     |    11 |   814 |  2550   (1)| 00:00:31 |
|   4 |     NESTED LOOPS                   |                     |       |       |            |          |
|   5 |      NESTED LOOPS                  |                     |    21 |  1386 |  2503   (1)| 00:00:31 |
|   6 |       NESTED LOOPS                 |                     |   644 | 27692 |  1214   (1)| 00:00:15 |
|*  7 |        HASH JOIN                   |                     |     1 |    33 |    14   (8)| 00:00:01 |
|*  8 |         TABLE ACCESS BY INDEX ROWID| LOOKUPDATA          |     1 |    24 |     4   (0)| 00:00:01 |
|*  9 |          INDEX RANGE SCAN          | IDX_LOOKUPCODE_NAME |    22 |       |     1   (0)| 00:00:01 |
|  10 |         TABLE ACCESS FULL          | ITEM_MASTER         |  1063 |  9567 |     9   (0)| 00:00:01 |
|  11 |        TABLE ACCESS BY INDEX ROWID | POS_SALES_DETAILS   |  6796 | 67960 |  1200   (1)| 00:00:15 |
|* 12 |         INDEX RANGE SCAN           | IDX_ITEM_ID         | 16645 |       |    47   (0)| 00:00:01 |
|* 13 |       INDEX UNIQUE SCAN            | SYS_C0066140        |     1 |       |     1   (0)| 00:00:01 |
|* 14 |      TABLE ACCESS BY INDEX ROWID   | POS_SALES_HEADER    |     1 |    23 |     2   (0)| 00:00:01 |
|  15 |     BUFFER SORT                    |                     |     1 |     8 |  2548   (1)| 00:00:31 |
|* 16 |      TABLE ACCESS FULL             | BUILDING            |     1 |     8 |     2   (0)| 00:00:01 |
|  17 |    TABLE ACCESS FULL               | COUNTER             |   164 |   656 |     4   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("C"."BUILDINGID"="B"."BUILDINGID")
   7 - access("I"."ITEM_GROUP_ID"="VID")
   8 - filter("V2"='CANTEEN')
   9 - access("NAME"='POSITEMGROUP')
  12 - access("P"."ITEM_ID"="I"."ITEM_ID")
  13 - access("PH"."SALES_ID"="P"."SALES_ID")
  14 - filter(NVL("PH"."GLSYNCED",'N')='N' AND NVL("PH"."SALE_IND",'GN')='GN' AND
              "PH"."SALES_STATUS"=1 AND "PH"."CREATED_DT"<TRUNC(SYSDATE@!)+1)
  16 - filter("B"."LOCATIONID"=843)

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Indexing on nullable date column

Postby Tim... » Thu Jun 19, 2014 7:57 am

Hi.

Sorry about the delay in getting back to you. This one got kind-of lost in the mix. :(

The way to read an explain plan is explained here:

http://www.oracle-base.com/articles/9i/ ... tion-plans

The trick to this is to check each operation and see if the expected cardinality (rows) looks sensible. If you can actually run the statement, the easiest way to do this is to use GATHER_PLAN_STATISTICS.

http://www.oracle-base.com/articles/9i/ ... stics_hint

This way you see the expected and actual cardinalities of all operations in the plan. If the expected and actuals differ greatly, the optimizer is probably going to make a bad decision. This type of discrepancy could be because of old stats, or stats that are not representative of the data.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 4 guests

cron