URGENT- query performance 1

[rohanem]

Hi all,
I have a query somethink like this..
select col1,col2,col3,col4,col5
from my_table
where my_date between date1 and date2
and my_time between time1 and time2
and (col8='xxxx' or
col9='xxxx' or
col10='xxxx' or
col11='xxxx')
order by col4,col8

my question is,I want the query to return the result faster.The table volume is about 2 million.right now it takes about 20 seconds to the result.Will it help to create indexes on columns,if so where?I have tried combinations of creating indexes on different columns but it has not helped much.

Thanks
Rohan

 

[Turkbear]

Have you analyzed the table recently?

What does the explain plan show?

 

[rohanem]

I haven analyzed the table but I have analyzed the indexes.
The expalin plan is not taking any of the indexes I have created(on my_date)

 

[karluk]

My guess that your best chance to get this query to use an index would be to create a composite index on all the columns in your where clause. That's a pretty ugly looking index, but it may be justified if it significantly improves performance on a frequently used query.

 

[rohanem]

thanks karluk..but I thought of that before and tried it..no avail :-(

 

[rcurva]

It would be easier for us to know your problem better if you could post the indexe here and the execution plan of the sql.

But in a nutshell, could you also try this query:

select col1,col2,col3,col4,col5
from my_table
where my_date between date1 and date2
and my_time between time1 and time2
and col8='xxxx'
UNION ALL
select col1,col2,col3,col4,col5
from my_table
where my_date between date1 and date2
and my_time between time1 and time2
and col9='xxxx'
UNION ALL
select col1,col2,col3,col4,col5
from my_table
where my_date between date1 and date2
and my_time between time1 and time2
and col10='xxxx'
UNION ALL
select col1,col2,col3,col4,col5
from my_table
where my_date between date1 and date2
and my_time between time1 and time2
and col9='xxxx'
order by 4,5;

I expect a composite index for (my_date,my_time,col9) here.
Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[rohanem]

Yeah I do have a composite index on my_date,my_time and col9
when Itried running the above query u mentioned,it said "Invalid column name"

 

[sem]

You may create composite index on (my_date, my_time) and enforce it by hint. Indexing col8-col9 will increase index size so the performance may even degrade.

 

[rohanem]

I have tried enforcing it by hint.When I generate the explain plan,it does not show the index at all

 

[sem]

Your hint is probably incorrect (in this case it's ignored). Do you use aliases in your query? In this case you must use them in hint also. The syntax is: /*+ index(alias index)*/

 

[rohanem]

when you say alais,you arre referring to column alaises right?What if I have an index on more than one columns?

 

[fmorel]

As you have a "time" column, I suppose that your dates are truncated (at 00:00 H).
So your dates columns must be low cardinality.

I would create 2 bitmap indexes on dates:

create bitmap index I_date1 on my_table(date1) compute stastics;
create bitmap index I_date2 on my_table(date2) compute stastics;

Oracle is able to merge bitmap indexes;

 

[sem]

Rohanem:
No, I mean TABLE alias.

Fmorel:
Creating bitmap index on date field is not a good idea: what will the size of this index be next year?

Date is an ideal candidate for B-tree index, as its selectivity will grow continuously.

 

[fmorel]

I use index bitmap on dates, and it works pretty well!

Cardinality is very low (max 365 values a year...)

What is important in a bitmap index is to have a very low ratio column cardinality/nb of rows

I have bitmap indexes on columns that have up to 15000 values (but 3 millions rows in the table), and it makes no problem. These indexes are always much thinner than standard b-tree indexes.

And I think dates are not enought selective for a b-tree, except if you use them as timestamps (I mean with hour, min, secs ...)


The problem with bitmap indexes is when there are a lot of updates on the table.

 

[sem]

Fmorel:

The declared rule of thumb to use index is 95/5, so after 20 days B-tree index becomes usefull.

In most cases the date is treated as "measurable" value, so you need not only equations, but also interval comparisons (see the query above). Bitmap index does not provide such functionality, or you should list ALL the values in interval (between date1 and date2 with B-Tree VS in (date1, date1+1,..,date2) with Bitmap).

Bitmap index uses aprox. bit-per-value entries and is quite efficient for columns with low cardinality, not depending on their types. You may list the names of states or short names or abbreviations - the size of index will be about the same. Though if the number of distinct values grows, the index also grows "in both directions".

That's why I do not recommend to use bitmap index for DATE column.

 

[fmorel]

Sem,

I did not understand your sentence "The declared rule of thumb to use index is 95/5, so after 20 days B-tree index becomes usefull." could you explain ? What do you mean by (95/5) ?

As I personnaly use a lot of bitmap indexes in my DWH, I wanted to verify what I said on bitmap indexes:
Please test the following!

-- Create a table (n,dt1,dt2) with test values
create table testbitmaps
as (select rownum as n, trunc(sysdate,'D')-5+mod(rownum,30) as dt1, -- values for 1 month
trunc(sysdate,'D')+mod(rownum,1095) as dt2 --values for 3 years
from all_tab_columns);

-- create 2 bitmaps
create bitmap index idt1 on testbitmaps(dt1);
create bitmap index idt2 on testbitmaps(dt2);

-- analyze all
analyze table testbitmaps compute statistics;

-- how many values
select count,count(distinct dt1), count(distinct dt2) from testbitmaps;

-- I got
-- COUNT(N) COUNT(DISTINCTDT1) COUNT(DISTINCTDT2)
------------ ------------------ ------------------
-- 18344 30 1095

-- then do some tests:
set autotrace traceonly exp


Then tests:

*** Between ***

SQL> select * from testbitmaps where sysdate between dt1 and dt2;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=7331 Bytes=13
1958)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TESTBITMAPS' (Cost=5 Car
d=7331 Bytes=131958)

2 1 BITMAP CONVERSION (TO ROWIDS)
3 2 BITMAP INDEX (RANGE SCAN) OF 'IDT1'


*** Operations ****
SQL> select * from testbitmaps where sysdate+25>dt1-3
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=9 Card=918 Bytes=165
24)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TESTBITMAPS' (Cost=9 Car
d=918 Bytes=16524)

2 1 BITMAP CONVERSION (TO ROWIDS)
3 2 BITMAP INDEX (FULL SCAN) OF 'IDT1'

**** count(*) ****
SQL> select count(*) from testbitmaps where sysdate between dt1 and dt2;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=14)
1 0 SORT (AGGREGATE)
2 1 BITMAP CONVERSION (COUNT)
3 2 BITMAP AND
4 3 BITMAP MERGE
5 4 BITMAP INDEX (RANGE SCAN) OF 'IDT1'
6 3 BITMAP MERGE
7 6 BITMAP INDEX (RANGE SCAN) OF 'IDT2'

*** equal ***
SQL> select * from testbitmaps where trunc(sysdate)=dt2;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=17 Bytes=306)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TESTBITMAPS' (Cost=1 Car
d=17 Bytes=306)

2 1 BITMAP CONVERSION (TO ROWIDS)
3 2 BITMAP INDEX (SINGLE VALUE) OF 'IDT2'

SQL> select * from testbitmaps where trunc(sysdate)=dt2 or trunc(sysdate)=dt1;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=628 Bytes=113
04)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TESTBITMAPS' (Cost=4 Car
d=628 Bytes=11304)

2 1 BITMAP CONVERSION (TO ROWIDS)
3 2 BITMAP OR
4 3 BITMAP INDEX (SINGLE VALUE) OF 'IDT2'
5 3 BITMAP INDEX (SINGLE VALUE) OF 'IDT1'

 

[sem]

95/5 means that using index is supposed to be usefull when query returns less than 5% of all rows.

What version of Oracle do you use? Is it 9+? I obtained other results:

SQL> select count,count(distinct dt1), count(distinct dt2) from testbitmaps;

COUNT(N) COUNT(DISTINCTDT1) COUNT(DISTINCTDT2)
---------- ------------------ ------------------
63447 30 1095

SQL> select * from testbitmaps where sysdate between dt1 and dt2;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=86 Card=23379 Bytes=
420822)

1 0 TABLE ACCESS (FULL) OF 'TESTBITMAPS' (Cost=86 Card=23379 B
ytes=420822)




SQL>
SQL> select * from testbitmaps where sysdate between dt1 and dt2;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=86 Card=23379 Bytes=
420822)

1 0 TABLE ACCESS (FULL) OF 'TESTBITMAPS' (Cost=86 Card=23379 B
ytes=420822)


Other plans are the same as yours, so I see that bitmap indexes may also be range-scanned, thank you.

But look at the possible future:

SQL> create table testbitmaps
2 as (select rownum as n, trunc(sysdate,'D')-5+mod(rownum,30) as dt1,
3 trunc(sysdate,'D')+rownum as dt2
4 from all_tab_columns)
5 /

Table created.

SQL> create INDEX idt2 on testbitmaps(dt2) STORAGE(INITIAL 1m NEXT 20K);

Index created.

SQL> Select sum(bytes) from user_segments where segmenT_name='IDT2';

SUM(BYTES)
----------
1441792

SQL> DROP INDEX idt2;

Index dropped.

SQL> create bitmap INDEX idt2 on testbitmaps(dt2) STORAGE(INITIAL 1m NEXT 20K);

Index created.

SQL> Select sum(bytes) from user_segments where segmenT_name='IDT2';

SUM(BYTES)
----------
2621440

Bitmap index is not a panacea, though I should revise my attitude.

 

[fmorel]

I run a 8.1.7.3 version

Maybe the differences in our explain plans come from our init.ora settings.

I have
sort_area_size = 2097152
optimizer_index_caching = 50
optimizer_index_cost_adj = 10

(the two lasts may change explain plans). I promise I did not falsified explain plans!

Can you test the first queries again after a
alter session set optimizer_index_caching = 50 optimizer_index_cost_adj = 10;


For your second test (size of indexes), using trunc(sysdate,'D')+rownum as dt2, you created 63447 distinct values for date dt2.
That means that there are more than 177 years of history in your table.
With this testcase, there are as many distinct values of dt2 as number of rows. In this case, of course a bitmap would be a nonsense !

But if you have at least 1000 rows for 1 distinct date, I think a bitmap is a good idea, and I will be thinner than a b-tree!

 

[sem]

Yes, it was due to optimizer_index_cost_adj.

 

[rohanem]

Thanks a loooot everyone!The bitmap dit it!!