Thanks for the question, Richard.
Asked: October 24, 2016 - 6:10 pm UTC
Last updated: October 25, 2016 - 1:17 am UTC
Version: 12c
Viewed 1000+ times
You Asked
Hi.
I'm trying to find a way to optimize this situation below.
Example table definition:
create table rw_test
(A varchar2(10),
B varchar2(10),
C varchar2(10),
D varchar2(10),
E varchar2(10),
F number(10),
entry_date date);
Because the front end wants to see the first 4 fields together as 1 field, queries are routinely concatenating the 4 fields:
select A||'-'||B||'-'||C||'-'||D combofield
from rw_test
where F > 100
and to_char(entry_date, 'yyyy-mm-dd') <= '2016-05-31' ;
I "think" the queries will always be using those 4 fields in that order. I tried creating a single index with all 4 columns in the order they would be in the concatenation: A, B, C, D. but that didn't make a difference. I don't think the cardinality of unique values with these 4 fields combined will be efficient for a bitmap index.
Modifying the table structure is not an option. "Average" size of this table at various locations would be approx 40 million rows.
Thanks in advance for any suggestions!
R.
and Connor said...
If you are *selecting* those concatenated columns, then an index is unlikely to of much benefit. The primary benefit of an index is to improve selectivity of data (ie, in the WHERE clause).
In your example above, indexing options would be explored by
a) examining potential indexes on F
b) not using expressions on date columns, ie
and entry_date <= date '2016-05-31'
and looking at potential indexes on entry_date.
In terms of simpler coding, you might consider virtual columns or a view to expose the concatenated field.
https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_3001.htm#BABIDDJJ