Hi Tom,
I have a view, which has date_p,date_p_c. Now I have a ssas cube where it has many partitions based on date.I need to fetch the data based on latest date.
Monthly partition brings data from beginning of year to prior month.
Daily partition brings data from beginning of month to prior day.
hourly partition brings data fof that particular hour.
now monthhly partitions's query is like -
SELECT * FROM loc_view
WHERE greatest(DATE,DATE_P_C) between TRUNC(SYSDATE, 'MONTH') and (TRUNC(SYSDATE, 'DD'))- INTERVAL '1' SECOND;
So when I use above query, the run time/processing time of cube is increasing.
Is there any other alternative for greatest function.I already tried OR but it is also not helping.
Thank you
The greatest function means your query won't be able to use partition pruning. Or regular indexes on date or date_p_c. So you'll always scan every partition. And, as you've noticed, the query take longer and longer:
create table t (
c1 int,
c2 int
) partition by range ( c1 )
interval ( 1 ) (
partition p0 values less than ( 2 )
);
create index i1 on t ( c1 );
create index i2 on t ( c2 );
insert into t
with rws as (
select level x , level + 1
from dual
connect by level <= 10
)
select * from rws;
commit;
select * from t
where greatest ( c1, c2 ) = 2;
C1 C2
1 2
select *
from table(dbms_xplan.display_cursor(null, null, 'BASIC LAST +PARTITION'));
PLAN_TABLE_OUTPUT
EXPLAINED SQL STATEMENT:
------------------------
select * from t where greatest ( c1, c2 ) = 2
Plan hash value: 4094519774
----------------------------------------------------
| Id | Operation | Name | Pstart| Pstop |
----------------------------------------------------
| 0 | SELECT STATEMENT | | | |
| 1 | PARTITION RANGE ALL| | 1 |1048575|
| 2 | TABLE ACCESS FULL | T | 1 |1048575|
----------------------------------------------------One way around this is to create a function-based index on greatest ( c1, c2 ). This allows the optimizer to use this index:
create index i on t ( greatest ( c1, c2 ) );
select * from t
where greatest ( c1, c2 ) = 2;
C1 C2
1 2
select *
from table(dbms_xplan.display_cursor(null, null, 'BASIC LAST +PARTITION'));
PLAN_TABLE_OUTPUT
EXPLAINED SQL STATEMENT:
------------------------
select * from t where greatest ( c1, c2 ) = 2
Plan hash value: 4075433511
---------------------------------------------------------------------------
| Id | Operation | Name | Pstart| Pstop |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| T | ROWID | ROWID |
| 2 | INDEX RANGE SCAN | I | | |
---------------------------------------------------------------------------