| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Indexes for selecting distinct values
Bitmaps indexes have some significant concurrency issues if the cust
table is updated frequently. They can also be impacted if there are
incremental loads against the tables. Don't implement unless you
understand these issues.
Depending on your application requirements, MViews can significantly speed up the query, however I don't see a way to get the query to Incremental/Fast Refresh. You would likely get an ORA-12015: cannot create a fast refresh materialized view from a complex query error ( The union essentially implies that a sort operation goes on before identifying if that is a 'new' row ). You could create a Complete refresh materialized view, however.
An alternative approach to the original query may be to use the With subfactor querying syntax available in 9i to reduce the table access required. This would have the advantage of referencing the source rows once. This could be used in conjunction with a MView as well.
With cust_status_cd as
( select distinct status_1_cd, status_2_cd, status_3_cd from CUST
where region_id = 9 )
select status_1_cd cd from cust_status_cd
union
select status_2_cd from cust_status_cd
union
select status_3_cd from cust_status_cd;
As compared to using the original query with 3 indices on (region_id, status_<n>_cd) the results look pretty promising. Your mileage may vary, and this solution depends on the width of the table, and distribution of data values.
The test case below measures differences in resource usage with the Runstats package via asktom.oracle.com .
Differences look like (with run 2 using the With syntax) :
JT(11)@JTDB9I>exec runStats_pkg.rs_stop();
Run1 ran in 7980 hsecs
Run2 ran in 2680 hsecs
run 1 ran in 297.76% of the time
Name Run1 Run2 Diff . . . LATCH.SQL memory manager worka 1,809 538 -1,271 STAT...redo size 1,816 3,372 1,556 STAT...prefetched blocks 4,110 1,370 -2,740 STAT...session logical reads 4,452 1,541 -2,911 STAT...consistent gets 4,442 1,517 -2,925 STAT...buffer is not pinned co 4,416 1,477 -2,939 STAT...no work - consistent re 4,416 1,477 -2,939 STAT...table scan blocks gotte 4,416 1,477 -2,939 STAT...free buffer requested 4,416 1,475 -2,941 STAT...physical reads 4,416 1,474 -2,942 LATCH.cache buffers lru chain 4,486 1,500 -2,986 STAT...Elapsed Time 7,982 2,684 -5,298 LATCH.cache buffers chains 16,106 6,095 -10,011 STAT...session pga memory max 0 69,656 69,656 STAT...sorts (rows) 153,999 53,380 -100,619 STAT...table scan rows gotten 1,665,405 555,452 -1,109,953 Run1 latches total versus runs -- difference and pct Run1 Run2 Diff Pct 30,066 12,676 -17,390 237.19%
PL/SQL procedure successfully completed.
insert into cust
select rownum, mod(rownum,11), decode(mod(rownum,3),0,'A',1,'B',2,'C'), decode(mod(rownum,5),0,'e',1,'f',2,'g',3,'h',4,'i'), decode(mod(rownum,7),0,'u',1,'v',2,'w',3,'x',4,'y',5,'z',6,'a') from all_objects, (select 'x' from all_objects where rownum <= 15) multiplierwhere rownum <= 1000000; -- actually about 555,000 rows created. commit;
create index cust_reg_cd1 on cust(region_id, status_1_cd); create index cust_reg_cd2 on cust(region_id, status_2_cd); create index cust_reg_cd3 on cust(region_id, status_3_cd);
exec dbms_stats.gather_table_stats('JTOMMANEY','cust', cascade => true);
exec runStats_pkg.rs_start;
select distinct status_1_cd from cust where region_id = 9
union
select distinct status_2_cd from cust where region_id = 9
union
select distinct status_3_cd from cust where region_id = 9;
exec runStats_pkg.rs_middle;
with cust_status_cd as
( select distinct status_1_cd, status_2_cd, status_3_cd from cust
where region_id = 9 )
select status_1_cd cd from cust_status_cd
union
select status_2_cd from cust_status_cd
union
select status_3_cd from cust_status_cd;
exec runStats_pkg.rs_stop();
 |
 |