Hi All,
i have designed the below query for retrieval of records which is flowing latest based on the grouped columns. If i execute the query it is taking so long time for execution even if index and indexes are balanced. this table contains 10 year data. kindly help me to sort out this issue.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> SELECT COUNT(1) FROM T_PBAMLGLOBUSSPRING;
COUNT(1)
----------
103964144
SQL> EXPLAIN PLAN FOR
2 SELECT *
3 FROM (SELECT T.*,
4 RANK() OVER(PARTITION BY T.BRANCHID, T.CIF, T.SYSTEMID, T.CONTRACTNUMBER ORDER BY T.DATADATE DESC) RNK
5 FROM T_PBAMLGLOBUSSPRING T
6 WHERE T.DATADATE >= '20160101'
AND DATADATE <= '20161231'
7 8 AND T.BRANCHID != 'GE'
9 AND T.SYSTEMID IN ('AC', 'SC', 'MM'))
10 WHERE RNK = 1;
Explained.
SQL> SET PAGESIZE 1500;
SQL> SET LINESIZE 1500;
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 30M| 12G| | 3361K (1)|
|* 1 | VIEW | | 30M| 12G| | 3361K (1)|
|* 2 | WINDOW SORT PUSHED RANK| | 30M| 12G| 15G| 3361K (1)|
|* 3 | TABLE ACCESS FULL | T_PBAMLGLOBUSSPRING | 30M| 12G| | 575K (2)|
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RNK"=1)
2 - filter(RANK() OVER ( PARTITION BY "T"."BRANCHID","T"."CIF","T"."SYSTEMID","T".
"CONTRACTNUMBER" ORDER BY INTERNAL_FUNCTION("DATADATE") DESC )<=1)
3 - filter(("T"."SYSTEMID"='AC' OR "T"."SYSTEMID"='MM' OR "T"."SYSTEMID"='SC')
AND "T"."DATADATE">='20160101' AND "DATADATE"<='20161231' AND "T"."BRANCHID"<>'GE')
Note
-----
- 'PLAN_TABLE' is old version
22 rows selected.
SQL> ^C