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.
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.
.. 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.
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?
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.
The indexes get used when i the tables are queries individually (even with 50% selectivity). but when joined, the index does not get used
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.
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)
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 ?