Optimizing Between clause operation.

Database
Enthusiast

Optimizing Between clause operation.

All,

I have a table T1 which has about 200 M rows and it is a type 2 table (so contains Start_Date and End_Date for every row).

I want to optimize the below condition "<Given Date> Between Start_Date and End_Date"  what options are there ?

1. Partition on Start_Date ? this helps if the condition is like "Start_Date between <Given_Date_1> and <Given_Date_2>". This is different from what i need.

2. An Index Start_Date and an Index on End_Date ?

3. Composite index on Start_Date and End_Date ?

It would be very helpful if you can share your ideas and experiences.

Thanks in advance.

8 REPLIES
Supporter

Re: Optimizing Between clause operation.

Which DB release you are on?

Enthusiast

Re: Optimizing Between clause operation.

I am on 13.10.

Supporter

Re: Optimizing Between clause operation.

Did you tried to combine the start_date and end_date in a period data type and define a temporal table with valid time?

But I don't have performance figures on this.

You can also try to define two NUSI as value ordered NUSI on Start_date and End_date - if combination is selective you might get fast responces.

Enthusiast

Re: Optimizing Between clause operation.

.. interesting point. Can you help me understand the period data type which can replace the start_date and end_date ? any example/blog/url will help.

I am trying NUSI, but they are not used when i join to another table.  If i do a simple select on this table with between clause, then the NUSI gets utilized.

Thank you.

Supporter

Re: Optimizing Between clause operation.

Check the documentation - in the SQL Reference there is a Temporary Table Support.

NUSI - did you check the value ordered NUSI?

Selectivity - assume the 200 mio rows. How many rows would you expect to select if you specify one Given_Date?

Enthusiast

Re: Optimizing Between clause operation.

Selectivity is 50% (sounds high to me).

Here is what i did on 2 tables:

Created individual indexes on start_date and end_date.

Created combined index on start_date and end_date.

Created partition on start_date and tested then created individual index and tested. Then created combined index on start_date and end_date and tested.

Result:

The indexes get used when i the tables are queries individually (even with 50% selectivity). but when joined, the index does not get used

Supporter

Re: Optimizing Between clause operation.

hm, 50% is not a high selctivity for a 200m row table and an NUSI. In fact I wonder why the index is used in a single table query - do you only count(*) in this querys.

without knowing the details (DDL, businessquestion/Query ) it is difficult to help any further.

Enthusiast

Re: Optimizing Between clause operation.

I looked into the Period data type.

Created a table as below :

CREATE multiSET TABLE t12 (a INTEGER,b PERIOD (date) FORMAT 'YYYY-MM-DD' NULL)

PRIMARY INDEX(a)

PARTITION BY CAST((BEGIN(b)) AS INTEGER);

when i am trying to insert the data using below statement, i get an error saying "INSERT Failed 5728: Partitioaning violation for table t12".

insert into t12 values(1, Period ( DATE '2011-01-26', DATE '2011-10-21'))

 I don't get the insert error when i don't partition the period column. but i need the partitioning as i cannot index a Period data type column.

Do you know how to resolve the insert error ?