GENERAL INFORMATION SECTION ------------------------------------------------------------------------------- Tuning Task Name : SH_SPA Tuning Task Owner : SYS Workload Type : SQL Tuning Set Execution Count : 1 Current Execution : sh_before_change Execution Type : TEST EXECUTE Scope : COMPREHENSIVE Completion Status : COMPLETED Started at : 11/16/2017 09:12:37 Completed at : 11/16/2017 09:13:29 SQL Tuning Set (STS) Name : sh_workload SQL Tuning Set Owner : SYS Number of Statements in the STS : 59 Number of SQLs Analyzed : 59 Number of SQLs in the Report : 59 Number of SQLs with Findings : 13 ------------------------------------------------------------------------------- SUMMARY SECTION ------------------------------------------------------------------------------- SQL Statements Ordered by Elapsed Time ------------------------------------------------------------------------------- Parse Elapsed CPU Buffer Optimizer object ID SQL ID Time (s) Time (s) Time (s) Gets Cost ---------- ------------- -------- -------- -------- -------- --------- 51 cmkygw8kypnpb .000529 .254024 .253961 15965 4851 23 709uggnu40a9a .000568 .251652 .249628 15965 4851 40 9w0c53r5um1xy .000651 .245077 .242629 15965 4851 10 222q8cuf6ugt9 .000488 .240585 .234964 15965 4851 21 5vbwrcz3vw3s0 .000433 .236252 .233714 15965 4851 36 9b723qjgkmy9z .000435 .228769 .224965 15965 4851 5 0bg8rg9srv8rm .000708 .224308 .224965 15965 4851 22 6n5rbcc9c9tvs .000429 .222135 .219466 15965 4849 41 a75y9uq84au4p .000523 .220762 .218216 15965 4851 48 c6axjhw2bv23t .000545 .219398 .219466 15965 4849 60 gpxukn3ft9qgw .000491 .209424 .207468 15965 4851 19 56nj9ptzakm56 .000507 .207727 .207218 15965 4849 32 8wskn4vvvkd93 .000537 .206895 .204719 15965 4841 9 1zm0sn3qcx0hu .000666 .20641 .207568 15965 4849 16 416hz7t3mx918 .000411 .195742 .19477 15965 4851 14 3fgwfcsa2k3bz .000475 .195412 .19457 15965 4851 59 gppx8hpcwsb5g .000573 .194255 .19257 15965 4849 15 3zdr12jv606xs .000566 .191762 .188971 15965 4841 29 8g3y654ptjzrz .000539 .187466 .184972 15965 4849 52 dfdg44tjr89uf .000551 .18526 .182572 15965 4841 33 954q4b1vkp8m4 .000536 .183291 .181372 15965 4849 12 28da8t4nc8uk9 .00082 .174375 .173573 15965 4841 42 a9nt3xdwup082 .000791 .169425 .168174 15965 4841 8 1xmq1jkcnp2mf .000491 .168748 .169974 15965 4851 61 gx7ymx5myrd2t .00063 .167982 .166574 15965 4849 35 9aft7htmzpd1d .001007 .166518 .164975 15965 4849 20 5hppq6bp5wmnm .000711 .163524 .162975 15965 4851 17 4kwqmbh8cs1gp .000592 .162285 .160309 15965 4851 37 9fsjrrksxp4gj .000597 .162116 .160575 15965 4849 38 9h2u2z9y900yk .000519 .159764 .157976 15965 4851 55 fd5j4mmztdpmm .000434 .156774 .154643 15965 4851 3 03ghr9b5m3x43 .000451 .154958 .154726 15965 4851 18 4wazxyxv5cpt0 .000467 .154513 .153643 15965 4841 7 1qr4ps69f36jy .000442 .154435 .155143 15965 4849 46 au7x78c2uju0p .00116 .154018 .154143 15965 4851 57 fx2c47hcyfz9f .000505 .153042 .15181 15965 4841 56 fwsw6473mw961 .000558 .151124 .14931 15965 4849 30 8hytq9ybsh5xu .000504 .150745 .149477 15965 4849 31 8k0bufga1dutt .000484 .150603 .149144 15965 4841 26 7f6kr3z5p8qxa .000787 .149625 .148144 15965 4849 6 1p5p7drt7atxd .000408 .149246 .15031 15965 4851 45 aktx221b6sqc4 .000554 .142861 .142311 15965 4841 13 370m5yqjjcr8z .000548 .14213 .141692 15965 4851 28 83wufms0kwjb0 .000579 .138891 .137836 15965 4851 4 08kysb4nyt58n .000519 .132301 .132122 15965 4851 39 9tkfxkjugywqp .000518 .124307 .122981 11579 4851 ------------------------------------------------------------------------------- DETAILS SECTION ------------------------------------------------------------------------------- SQL Statements Ordered by Elapsed Time ------------------------------------------------------------------------------- Object ID : 51 Schema Name: SH SQL ID : cmkygw8kypnpb SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2013-4' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16 ------------------------------------------------------------------------------- Execution Statistics ------------------------------------------------------------------------------- Parse Time (s): .000529 Elapsed Time (s): .254024 CPU Time (s): .253961 User I/O Time (s): 0 Buffer Gets: 15965 Disk Reads: 0 Direct Writes: 0 I/O Interconnect Bytes: 0 Rows Processed: 16 Fetches: 16 Executions: 1 End of Fetch Count: 1 Optimizer Cost: 4851 Notes ----- 1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers. 2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 3 ejecuciones. ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original ----------- Plan hash value: 3551373025 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 19370 | 1475K| | 4851 (1)| 00:00:59 | |* 1 | VIEW | | 19370 | 1475K| | 4851 (1)| 00:00:59 | |* 2 | WINDOW SORT PUSHED RANK| | 19370 | 964K| 2456K| 4851 (1)| 00:00:59 | | 3 | HASH GROUP BY | | 19370 | 964K| 2456K| 4851 (1)| 00:00:59 | |* 4 | HASH JOIN | | 19370 | 964K| | 4354 (1)| 00:00:53 | |* 5 | HASH JOIN | | 19587 | 592K| | 3157 (1)| 00:00:38 | |* 6 | TABLE ACCESS FULL | TIMES | 92 | 1380 | | 19 (0)| 00:00:01 | | 7 | TABLE ACCESS FULL | SALES | 1472K| 22M| | 3134 (1)| 00:00:38 | | 8 | TABLE ACCESS FULL | CUSTOMERS | 162K| 3175K| | 1197 (1)| 00:00:15 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RANK_WITHIN_QUARTER"<16) 2 - filter(RANK() OVER ( PARTITION BY "TIMES"."CALENDAR_QUARTER_DESC" ORDER BY SUM("AMOUNT_SOLD") DESC )<16) 4 - access("SALES"."CUST_ID"="CUSTOMERS"."CUST_ID") 5 - access("TIMES"."TIME_ID"="SALES"."TIME_ID") 6 - filter("TIMES"."CALENDAR_QUARTER_DESC"='2013-4') ------------------------------------------------------------------------------- Object ID : 23 Schema Name: SH SQL ID : 709uggnu40a9a SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2012-4' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16 ------------------------------------------------------------------------------- Execution Statistics ------------------------------------------------------------------------------- Parse Time (s): .000568 Elapsed Time (s): .251652 CPU Time (s): .249628 User I/O Time (s): 0 Buffer Gets: 15965 Disk Reads: 0 Direct Writes: 0 I/O Interconnect Bytes: 0 Rows Processed: 15 Fetches: 15 Executions: 1 End of Fetch Count: 1 Optimizer Cost: 4851 Notes ----- 1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers. 2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 3 ejecuciones. ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original ----------- Plan hash value: 3551373025 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 19370 | 1475K| | 4851 (1)| 00:00:59 | |* 1 | VIEW | | 19370 | 1475K| | 4851 (1)| 00:00:59 | |* 2 | WINDOW SORT PUSHED RANK| | 19370 | 964K| 2456K| 4851 (1)| 00:00:59 | | 3 | HASH GROUP BY | | 19370 | 964K| 2456K| 4851 (1)| 00:00:59 | |* 4 | HASH JOIN | | 19370 | 964K| | 4354 (1)| 00:00:53 | |* 5 | HASH JOIN | | 19587 | 592K| | 3157 (1)| 00:00:38 | |* 6 | TABLE ACCESS FULL | TIMES | 92 | 1380 | | 19 (0)| 00:00:01 | | 7 | TABLE ACCESS FULL | SALES | 1472K| 22M| | 3134 (1)| 00:00:38 | | 8 | TABLE ACCESS FULL | CUSTOMERS | 162K| 3175K| | 1197 (1)| 00:00:15 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RANK_WITHIN_QUARTER"<16) 2 - filter(RANK() OVER ( PARTITION BY "TIMES"."CALENDAR_QUARTER_DESC" ORDER BY SUM("AMOUNT_SOLD") DESC )<16) 4 - access("SALES"."CUST_ID"="CUSTOMERS"."CUST_ID") 5 - access("TIMES"."TIME_ID"="SALES"."TIME_ID") 6 - filter("TIMES"."CALENDAR_QUARTER_DESC"='2012-4') ------------------------------------------------------------------------------- Object ID : 40 Schema Name: SH SQL ID : 9w0c53r5um1xy SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2011-4' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16 ------------------------------------------------------------------------------- Execution Statistics ------------------------------------------------------------------------------- Parse Time (s): .000651 Elapsed Time (s): .245077 CPU Time (s): .242629 User I/O Time (s): 0 Buffer Gets: 15965 Disk Reads: 0 Direct Writes: 0 I/O Interconnect Bytes: 0 Rows Processed: 15 Fetches: 15 Executions: 1 End of Fetch Count: 1 Optimizer Cost: 4851 Notes ----- 1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers. 2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 3 ejecuciones. ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original ----------- Plan hash value: 3551373025 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 19370 | 1475K| | 4851 (1)| 00:00:59 | |* 1 | VIEW | | 19370 | 1475K| | 4851 (1)| 00:00:59 | |* 2 | WINDOW SORT PUSHED RANK| | 19370 | 964K| 2456K| 4851 (1)| 00:00:59 | | 3 | HASH GROUP BY | | 19370 | 964K| 2456K| 4851 (1)| 00:00:59 | |* 4 | HASH JOIN | | 19370 | 964K| | 4354 (1)| 00:00:53 | |* 5 | HASH JOIN | | 19587 | 592K| | 3157 (1)| 00:00:38 | |* 6 | TABLE ACCESS FULL | TIMES | 92 | 1380 | | 19 (0)| 00:00:01 | | 7 | TABLE ACCESS FULL | SALES | 1472K| 22M| | 3134 (1)| 00:00:38 | | 8 | TABLE ACCESS FULL | CUSTOMERS | 162K| 3175K| | 1197 (1)| 00:00:15 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RANK_WITHIN_QUARTER"<16) 2 - filter(RANK() OVER ( PARTITION BY "TIMES"."CALENDAR_QUARTER_DESC" ORDER BY SUM("AMOUNT_SOLD") DESC )<16) 4 - access("SALES"."CUST_ID"="CUSTOMERS"."CUST_ID") 5 - access("TIMES"."TIME_ID"="SALES"."TIME_ID") 6 - filter("TIMES"."CALENDAR_QUARTER_DESC"='2011-4') ------------------------------------------------------------------------------- Object ID : 10 Schema Name: SH SQL ID : 222q8cuf6ugt9 SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2010-3' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16 ------------------------------------------------------------------------------- Execution Statistics ------------------------------------------------------------------------------- Parse Time (s): .000488 Elapsed Time (s): .240585 CPU Time (s): .234964 User I/O Time (s): 0 Buffer Gets: 15965 Disk Reads: 0 Direct Writes: 0 I/O Interconnect Bytes: 0 Rows Processed: 15 Fetches: 15 Executions: 1 End of Fetch Count: 1 Optimizer Cost: 4851 Notes ----- 1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers. 2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 4 ejecuciones. ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original ----------- Plan hash value: 3551373025 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 19370 | 1475K| | 4851 (1)| 00:00:59 | |* 1 | VIEW | | 19370 | 1475K| | 4851 (1)| 00:00:59 | |* 2 | WINDOW SORT PUSHED RANK| | 19370 | 964K| 2456K| 4851 (1)| 00:00:59 | | 3 | HASH GROUP BY | | 19370 | 964K| 2456K| 4851 (1)| 00:00:59 | |* 4 | HASH JOIN | | 19370 | 964K| | 4354 (1)| 00:00:53 | |* 5 | HASH JOIN | | 19587 | 592K| | 3157 (1)| 00:00:38 | |* 6 | TABLE ACCESS FULL | TIMES | 92 | 1380 | | 19 (0)| 00:00:01 | | 7 | TABLE ACCESS FULL | SALES | 1472K| 22M| | 3134 (1)| 00:00:38 | | 8 | TABLE ACCESS FULL | CUSTOMERS | 162K| 3175K| | 1197 (1)| 00:00:15 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RANK_WITHIN_QUARTER"<16) 2 - filter(RANK() OVER ( PARTITION BY "TIMES"."CALENDAR_QUARTER_DESC" ORDER BY SUM("AMOUNT_SOLD") DESC )<16) 4 - access("SALES"."CUST_ID"="CUSTOMERS"."CUST_ID") 5 - access("TIMES"."TIME_ID"="SALES"."TIME_ID") 6 - filter("TIMES"."CALENDAR_QUARTER_DESC"='2010-3') ------------------------------------------------------------------------------- Object ID : 21 Schema Name: SH SQL ID : 5vbwrcz3vw3s0 SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2013-3' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16 ------------------------------------------------------------------------------- Execution Statistics ------------------------------------------------------------------------------- Parse Time (s): .000433 Elapsed Time (s): .236252 CPU Time (s): .233714 User I/O Time (s): 0 Buffer Gets: 15965 Disk Reads: 0 Direct Writes: 0 I/O Interconnect Bytes: 0 Rows Processed: 15 Fetches: 15 Executions: 1 End of Fetch Count: 1 Optimizer Cost: 4851 Notes ----- 1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers. 2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 4 ejecuciones. ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original ----------- Plan hash value: 3551373025 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 19370 | 1475K| | 4851 (1)| 00:00:59 | |* 1 | VIEW | | 19370 | 1475K| | 4851 (1)| 00:00:59 | |* 2 | WINDOW SORT PUSHED RANK| | 19370 | 964K| 2456K| 4851 (1)| 00:00:59 | | 3 | HASH GROUP BY | | 19370 | 964K| 2456K| 4851 (1)| 00:00:59 | |* 4 | HASH JOIN | | 19370 | 964K| | 4354 (1)| 00:00:53 | |* 5 | HASH JOIN | | 19587 | 592K| | 3157 (1)| 00:00:38 | |* 6 | TABLE ACCESS FULL | TIMES | 92 | 1380 | | 19 (0)| 00:00:01 | | 7 | TABLE ACCESS FULL | SALES | 1472K| 22M| | 3134 (1)| 00:00:38 | | 8 | TABLE ACCESS FULL | CUSTOMERS | 162K| 3175K| | 1197 (1)| 00:00:15 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RANK_WITHIN_QUARTER"<16) 2 - filter(RANK() OVER ( PARTITION BY "TIMES"."CALENDAR_QUARTER_DESC" ORDER BY SUM("AMOUNT_SOLD") DESC )<16) 4 - access("SALES"."CUST_ID"="CUSTOMERS"."CUST_ID") 5 - access("TIMES"."TIME_ID"="SALES"."TIME_ID") 6 - filter("TIMES"."CALENDAR_QUARTER_DESC"='2013-3') ------------------------------------------------------------------------------- Object ID : 36 Schema Name: SH SQL ID : 9b723qjgkmy9z SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2009-4' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16 ------------------------------------------------------------------------------- Execution Statistics ------------------------------------------------------------------------------- Parse Time (s): .000435 Elapsed Time (s): .228769 CPU Time (s): .224965 User I/O Time (s): 0 Buffer Gets: 15965 Disk Reads: 0 Direct Writes: 0 I/O Interconnect Bytes: 0 Rows Processed: 16 Fetches: 16 Executions: 1 End of Fetch Count: 1 Optimizer Cost: 4851 Notes ----- 1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers. 2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 4 ejecuciones. ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original ----------- Plan hash value: 3551373025 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 19370 | 1475K| | 4851 (1)| 00:00:59 | |* 1 | VIEW | | 19370 | 1475K| | 4851 (1)| 00:00:59 | |* 2 | WINDOW SORT PUSHED RANK| | 19370 | 964K| 2456K| 4851 (1)| 00:00:59 | | 3 | HASH GROUP BY | | 19370 | 964K| 2456K| 4851 (1)| 00:00:59 | |* 4 | HASH JOIN | | 19370 | 964K| | 4354 (1)| 00:00:53 | |* 5 | HASH JOIN | | 19587 | 592K| | 3157 (1)| 00:00:38 | |* 6 | TABLE ACCESS FULL | TIMES | 92 | 1380 | | 19 (0)| 00:00:01 | | 7 | TABLE ACCESS FULL | SALES | 1472K| 22M| | 3134 (1)| 00:00:38 | | 8 | TABLE ACCESS FULL | CUSTOMERS | 162K| 3175K| | 1197 (1)| 00:00:15 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RANK_WITHIN_QUARTER"<16) 2 - filter(RANK() OVER ( PARTITION BY "TIMES"."CALENDAR_QUARTER_DESC" ORDER BY SUM("AMOUNT_SOLD") DESC )<16) 4 - access("SALES"."CUST_ID"="CUSTOMERS"."CUST_ID") 5 - access("TIMES"."TIME_ID"="SALES"."TIME_ID") 6 - filter("TIMES"."CALENDAR_QUARTER_DESC"='2009-4') ------------------------------------------------------------------------------- Object ID : 5 Schema Name: SH SQL ID : 0bg8rg9srv8rm SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2012-3' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16 ------------------------------------------------------------------------------- Execution Statistics ------------------------------------------------------------------------------- Parse Time (s): .000708 Elapsed Time (s): .224308 CPU Time (s): .224965 User I/O Time (s): 0 Buffer Gets: 15965 Disk Reads: 0 Direct Writes: 0 I/O Interconnect Bytes: 0 Rows Processed: 15 Fetches: 15 Executions: 1 End of Fetch Count: 1 Optimizer Cost: 4851 Notes ----- 1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers. 2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 4 ejecuciones. ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original ----------- Plan hash value: 3551373025 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 19370 | 1475K| | 4851 (1)| 00:00:59 | |* 1 | VIEW | | 19370 | 1475K| | 4851 (1)| 00:00:59 | |* 2 | WINDOW SORT PUSHED RANK| | 19370 | 964K| 2456K| 4851 (1)| 00:00:59 | | 3 | HASH GROUP BY | | 19370 | 964K| 2456K| 4851 (1)| 00:00:59 | |* 4 | HASH JOIN | | 19370 | 964K| | 4354 (1)| 00:00:53 | |* 5 | HASH JOIN | | 19587 | 592K| | 3157 (1)| 00:00:38 | |* 6 | TABLE ACCESS FULL | TIMES | 92 | 1380 | | 19 (0)| 00:00:01 | | 7 | TABLE ACCESS FULL | SALES | 1472K| 22M| | 3134 (1)| 00:00:38 | | 8 | TABLE ACCESS FULL | CUSTOMERS | 162K| 3175K| | 1197 (1)| 00:00:15 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RANK_WITHIN_QUARTER"<16) 2 - filter(RANK() OVER ( PARTITION BY "TIMES"."CALENDAR_QUARTER_DESC" ORDER BY SUM("AMOUNT_SOLD") DESC )<16) 4 - access("SALES"."CUST_ID"="CUSTOMERS"."CUST_ID") 5 - access("TIMES"."TIME_ID"="SALES"."TIME_ID") 6 - filter("TIMES"."CALENDAR_QUARTER_DESC"='2012-3') ------------------------------------------------------------------------------- Object ID : 22 Schema Name: SH SQL ID : 6n5rbcc9c9tvs SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2012-2' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16 ------------------------------------------------------------------------------- Execution Statistics ------------------------------------------------------------------------------- Parse Time (s): .000429 Elapsed Time (s): .222135 CPU Time (s): .219466 User I/O Time (s): 0 Buffer Gets: 15965 Disk Reads: 0 Direct Writes: 0 I/O Interconnect Bytes: 0 Rows Processed: 15 Fetches: 15 Executions: 1 End of Fetch Count: 1 Optimizer Cost: 4849 Notes ----- 1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers. 2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 4 ejecuciones. ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original ----------- Plan hash value: 3551373025 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 19159 | 1459K| | 4849 (1)| 00:00:59 | |* 1 | VIEW | | 19159 | 1459K| | 4849 (1)| 00:00:59 | |* 2 | WINDOW SORT PUSHED RANK| | 19159 | 954K| 2424K| 4849 (1)| 00:00:59 | | 3 | HASH GROUP BY | | 19159 | 954K| 2424K| 4849 (1)| 00:00:59 | |* 4 | HASH JOIN | | 19159 | 954K| | 4354 (1)| 00:00:53 | |* 5 | HASH JOIN | | 19374 | 586K| | 3157 (1)| 00:00:38 | |* 6 | TABLE ACCESS FULL | TIMES | 91 | 1365 | | 19 (0)| 00:00:01 | | 7 | TABLE ACCESS FULL | SALES | 1472K| 22M| | 3134 (1)| 00:00:38 | | 8 | TABLE ACCESS FULL | CUSTOMERS | 162K| 3175K| | 1197 (1)| 00:00:15 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RANK_WITHIN_QUARTER"<16) 2 - filter(RANK() OVER ( PARTITION BY "TIMES"."CALENDAR_QUARTER_DESC" ORDER BY SUM("AMOUNT_SOLD") DESC )<16) 4 - access("SALES"."CUST_ID"="CUSTOMERS"."CUST_ID") 5 - access("TIMES"."TIME_ID"="SALES"."TIME_ID") 6 - filter("TIMES"."CALENDAR_QUARTER_DESC"='2012-2') ------------------------------------------------------------------------------- Object ID : 41 Schema Name: SH SQL ID : a75y9uq84au4p SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2010-4' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16 ------------------------------------------------------------------------------- Execution Statistics ------------------------------------------------------------------------------- Parse Time (s): .000523 Elapsed Time (s): .220762 CPU Time (s): .218216 User I/O Time (s): 0 Buffer Gets: 15965 Disk Reads: 0 Direct Writes: 0 I/O Interconnect Bytes: 0 Rows Processed: 15 Fetches: 15 Executions: 1 End of Fetch Count: 1 Optimizer Cost: 4851 Notes ----- 1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers. 2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 4 ejecuciones. ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original ----------- Plan hash value: 3551373025 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 19370 | 1475K| | 4851 (1)| 00:00:59 | |* 1 | VIEW | | 19370 | 1475K| | 4851 (1)| 00:00:59 | |* 2 | WINDOW SORT PUSHED RANK| | 19370 | 964K| 2456K| 4851 (1)| 00:00:59 | | 3 | HASH GROUP BY | | 19370 | 964K| 2456K| 4851 (1)| 00:00:59 | |* 4 | HASH JOIN | | 19370 | 964K| | 4354 (1)| 00:00:53 | |* 5 | HASH JOIN | | 19587 | 592K| | 3157 (1)| 00:00:38 | |* 6 | TABLE ACCESS FULL | TIMES | 92 | 1380 | | 19 (0)| 00:00:01 | | 7 | TABLE ACCESS FULL | SALES | 1472K| 22M| | 3134 (1)| 00:00:38 | | 8 | TABLE ACCESS FULL | CUSTOMERS | 162K| 3175K| | 1197 (1)| 00:00:15 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RANK_WITHIN_QUARTER"<16) 2 - filter(RANK() OVER ( PARTITION BY "TIMES"."CALENDAR_QUARTER_DESC" ORDER BY SUM("AMOUNT_SOLD") DESC )<16) 4 - access("SALES"."CUST_ID"="CUSTOMERS"."CUST_ID") 5 - access("TIMES"."TIME_ID"="SALES"."TIME_ID") 6 - filter("TIMES"."CALENDAR_QUARTER_DESC"='2010-4') ------------------------------------------------------------------------------- Object ID : 48 Schema Name: SH SQL ID : c6axjhw2bv23t SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2013-2' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16 ------------------------------------------------------------------------------- Execution Statistics ------------------------------------------------------------------------------- Parse Time (s): .000545 Elapsed Time (s): .219398 CPU Time (s): .219466 User I/O Time (s): 0 Buffer Gets: 15965 Disk Reads: 0 Direct Writes: 0 I/O Interconnect Bytes: 0 Rows Processed: 15 Fetches: 15 Executions: 1 End of Fetch Count: 1 Optimizer Cost: 4849 Notes ----- 1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers. 2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 4 ejecuciones. ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original ----------- Plan hash value: 3551373025 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 19159 | 1459K| | 4849 (1)| 00:00:59 | |* 1 | VIEW | | 19159 | 1459K| | 4849 (1)| 00:00:59 | |* 2 | WINDOW SORT PUSHED RANK| | 19159 | 954K| 2424K| 4849 (1)| 00:00:59 | | 3 | HASH GROUP BY | | 19159 | 954K| 2424K| 4849 (1)| 00:00:59 | |* 4 | HASH JOIN | | 19159 | 954K| | 4354 (1)| 00:00:53 | |* 5 | HASH JOIN | | 19374 | 586K| | 3157 (1)| 00:00:38 | |* 6 | TABLE ACCESS FULL | TIMES | 91 | 1365 | | 19 (0)| 00:00:01 | | 7 | TABLE ACCESS FULL | SALES | 1472K| 22M| | 3134 (1)| 00:00:38 | | 8 | TABLE ACCESS FULL | CUSTOMERS | 162K| 3175K| | 1197 (1)| 00:00:15 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RANK_WITHIN_QUARTER"<16) 2 - filter(RANK() OVER ( PARTITION BY "TIMES"."CALENDAR_QUARTER_DESC" ORDER BY SUM("AMOUNT_SOLD") DESC )<16) 4 - access("SALES"."CUST_ID"="CUSTOMERS"."CUST_ID") 5 - access("TIMES"."TIME_ID"="SALES"."TIME_ID") 6 - filter("TIMES"."CALENDAR_QUARTER_DESC"='2013-2') ------------------------------------------------------------------------------- Object ID : 60 Schema Name: SH SQL ID : gpxukn3ft9qgw SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2011-3' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16 ------------------------------------------------------------------------------- Execution Statistics ------------------------------------------------------------------------------- Parse Time (s): .000491 Elapsed Time (s): .209424 CPU Time (s): .207468 User I/O Time (s): 0 Buffer Gets: 15965 Disk Reads: 0 Direct Writes: 0 I/O Interconnect Bytes: 0 Rows Processed: 15 Fetches: 15 Executions: 1 End of Fetch Count: 1 Optimizer Cost: 4851 Notes ----- 1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers. 2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 4 ejecuciones. ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original ----------- Plan hash value: 3551373025 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 19370 | 1475K| | 4851 (1)| 00:00:59 | |* 1 | VIEW | | 19370 | 1475K| | 4851 (1)| 00:00:59 | |* 2 | WINDOW SORT PUSHED RANK| | 19370 | 964K| 2456K| 4851 (1)| 00:00:59 | | 3 | HASH GROUP BY | | 19370 | 964K| 2456K| 4851 (1)| 00:00:59 | |* 4 | HASH JOIN | | 19370 | 964K| | 4354 (1)| 00:00:53 | |* 5 | HASH JOIN | | 19587 | 592K| | 3157 (1)| 00:00:38 | |* 6 | TABLE ACCESS FULL | TIMES | 92 | 1380 | | 19 (0)| 00:00:01 | | 7 | TABLE ACCESS FULL | SALES | 1472K| 22M| | 3134 (1)| 00:00:38 | | 8 | TABLE ACCESS FULL | CUSTOMERS | 162K| 3175K| | 1197 (1)| 00:00:15 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RANK_WITHIN_QUARTER"<16) 2 - filter(RANK() OVER ( PARTITION BY "TIMES"."CALENDAR_QUARTER_DESC" ORDER BY SUM("AMOUNT_SOLD") DESC )<16) 4 - access("SALES"."CUST_ID"="CUSTOMERS"."CUST_ID") 5 - access("TIMES"."TIME_ID"="SALES"."TIME_ID") 6 - filter("TIMES"."CALENDAR_QUARTER_DESC"='2011-3') ------------------------------------------------------------------------------- Object ID : 19 Schema Name: SH SQL ID : 56nj9ptzakm56 SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2011-2' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16 ------------------------------------------------------------------------------- Execution Statistics ------------------------------------------------------------------------------- Parse Time (s): .000507 Elapsed Time (s): .207727 CPU Time (s): .207218 User I/O Time (s): 0 Buffer Gets: 15965 Disk Reads: 0 Direct Writes: 0 I/O Interconnect Bytes: 0 Rows Processed: 15 Fetches: 15 Executions: 1 End of Fetch Count: 1 Optimizer Cost: 4849 Notes ----- 1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers. 2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 4 ejecuciones. ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original ----------- Plan hash value: 3551373025 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 19159 | 1459K| | 4849 (1)| 00:00:59 | |* 1 | VIEW | | 19159 | 1459K| | 4849 (1)| 00:00:59 | |* 2 | WINDOW SORT PUSHED RANK| | 19159 | 954K| 2424K| 4849 (1)| 00:00:59 | | 3 | HASH GROUP BY | | 19159 | 954K| 2424K| 4849 (1)| 00:00:59 | |* 4 | HASH JOIN | | 19159 | 954K| | 4354 (1)| 00:00:53 | |* 5 | HASH JOIN | | 19374 | 586K| | 3157 (1)| 00:00:38 | |* 6 | TABLE ACCESS FULL | TIMES | 91 | 1365 | | 19 (0)| 00:00:01 | | 7 | TABLE ACCESS FULL | SALES | 1472K| 22M| | 3134 (1)| 00:00:38 | | 8 | TABLE ACCESS FULL | CUSTOMERS | 162K| 3175K| | 1197 (1)| 00:00:15 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RANK_WITHIN_QUARTER"<16) 2 - filter(RANK() OVER ( PARTITION BY "TIMES"."CALENDAR_QUARTER_DESC" ORDER BY SUM("AMOUNT_SOLD") DESC )<16) 4 - access("SALES"."CUST_ID"="CUSTOMERS"."CUST_ID") 5 - access("TIMES"."TIME_ID"="SALES"."TIME_ID") 6 - filter("TIMES"."CALENDAR_QUARTER_DESC"='2011-2') ------------------------------------------------------------------------------- Object ID : 32 Schema Name: SH SQL ID : 8wskn4vvvkd93 SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2013-1' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16 ------------------------------------------------------------------------------- Execution Statistics ------------------------------------------------------------------------------- Parse Time (s): .000537 Elapsed Time (s): .206895 CPU Time (s): .204719 User I/O Time (s): 0 Buffer Gets: 15965 Disk Reads: 0 Direct Writes: 0 I/O Interconnect Bytes: 0 Rows Processed: 15 Fetches: 15 Executions: 1 End of Fetch Count: 1 Optimizer Cost: 4841 Notes ----- 1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers. 2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 4 ejecuciones. ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original ----------- Plan hash value: 3551373025 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 18948 | 1443K| | 4841 (1)| 00:00:59 | |* 1 | VIEW | | 18948 | 1443K| | 4841 (1)| 00:00:59 | |* 2 | WINDOW SORT PUSHED RANK| | 18948 | 943K| 2408K| 4841 (1)| 00:00:59 | | 3 | HASH GROUP BY | | 18948 | 943K| 2408K| 4841 (1)| 00:00:59 | |* 4 | HASH JOIN | | 18948 | 943K| | 4354 (1)| 00:00:53 | |* 5 | HASH JOIN | | 19161 | 580K| | 3157 (1)| 00:00:38 | |* 6 | TABLE ACCESS FULL | TIMES | 90 | 1350 | | 19 (0)| 00:00:01 | | 7 | TABLE ACCESS FULL | SALES | 1472K| 22M| | 3134 (1)| 00:00:38 | | 8 | TABLE ACCESS FULL | CUSTOMERS | 162K| 3175K| | 1197 (1)| 00:00:15 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RANK_WITHIN_QUARTER"<16) 2 - filter(RANK() OVER ( PARTITION BY "TIMES"."CALENDAR_QUARTER_DESC" ORDER BY SUM("AMOUNT_SOLD") DESC )<16) 4 - access("SALES"."CUST_ID"="CUSTOMERS"."CUST_ID") 5 - access("TIMES"."TIME_ID"="SALES"."TIME_ID") 6 - filter("TIMES"."CALENDAR_QUARTER_DESC"='2013-1') ------------------------------------------------------------------------------- Object ID : 9 Schema Name: SH SQL ID : 1zm0sn3qcx0hu SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2009-2' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16 ------------------------------------------------------------------------------- Execution Statistics ------------------------------------------------------------------------------- Parse Time (s): .000666 Elapsed Time (s): .20641 CPU Time (s): .207568 User I/O Time (s): 0 Buffer Gets: 15965 Disk Reads: 0 Direct Writes: 0 I/O Interconnect Bytes: 0 Rows Processed: 17 Fetches: 17 Executions: 1 End of Fetch Count: 1 Optimizer Cost: 4849 Notes ----- 1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers. 2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 5 ejecuciones. ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original ----------- Plan hash value: 3551373025 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 19159 | 1459K| | 4849 (1)| 00:00:59 | |* 1 | VIEW | | 19159 | 1459K| | 4849 (1)| 00:00:59 | |* 2 | WINDOW SORT PUSHED RANK| | 19159 | 954K| 2424K| 4849 (1)| 00:00:59 | | 3 | HASH GROUP BY | | 19159 | 954K| 2424K| 4849 (1)| 00:00:59 | |* 4 | HASH JOIN | | 19159 | 954K| | 4354 (1)| 00:00:53 | |* 5 | HASH JOIN | | 19374 | 586K| | 3157 (1)| 00:00:38 | |* 6 | TABLE ACCESS FULL | TIMES | 91 | 1365 | | 19 (0)| 00:00:01 | | 7 | TABLE ACCESS FULL | SALES | 1472K| 22M| | 3134 (1)| 00:00:38 | | 8 | TABLE ACCESS FULL | CUSTOMERS | 162K| 3175K| | 1197 (1)| 00:00:15 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RANK_WITHIN_QUARTER"<16) 2 - filter(RANK() OVER ( PARTITION BY "TIMES"."CALENDAR_QUARTER_DESC" ORDER BY SUM("AMOUNT_SOLD") DESC )<16) 4 - access("SALES"."CUST_ID"="CUSTOMERS"."CUST_ID") 5 - access("TIMES"."TIME_ID"="SALES"."TIME_ID") 6 - filter("TIMES"."CALENDAR_QUARTER_DESC"='2009-2') ------------------------------------------------------------------------------- Object ID : 16 Schema Name: SH SQL ID : 416hz7t3mx918 SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2009-3' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16 ------------------------------------------------------------------------------- Execution Statistics ------------------------------------------------------------------------------- Parse Time (s): .000411 Elapsed Time (s): .195742 CPU Time (s): .19477 User I/O Time (s): 0 Buffer Gets: 15965 Disk Reads: 0 Direct Writes: 0 I/O Interconnect Bytes: 0 Rows Processed: 15 Fetches: 15 Executions: 1 End of Fetch Count: 1 Optimizer Cost: 4851 Notes ----- 1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers. 2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 5 ejecuciones. ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original ----------- Plan hash value: 3551373025 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 19370 | 1475K| | 4851 (1)| 00:00:59 | |* 1 | VIEW | | 19370 | 1475K| | 4851 (1)| 00:00:59 | |* 2 | WINDOW SORT PUSHED RANK| | 19370 | 964K| 2456K| 4851 (1)| 00:00:59 | | 3 | HASH GROUP BY | | 19370 | 964K| 2456K| 4851 (1)| 00:00:59 | |* 4 | HASH JOIN | | 19370 | 964K| | 4354 (1)| 00:00:53 | |* 5 | HASH JOIN | | 19587 | 592K| | 3157 (1)| 00:00:38 | |* 6 | TABLE ACCESS FULL | TIMES | 92 | 1380 | | 19 (0)| 00:00:01 | | 7 | TABLE ACCESS FULL | SALES | 1472K| 22M| | 3134 (1)| 00:00:38 | | 8 | TABLE ACCESS FULL | CUSTOMERS | 162K| 3175K| | 1197 (1)| 00:00:15 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RANK_WITHIN_QUARTER"<16) 2 - filter(RANK() OVER ( PARTITION BY "TIMES"."CALENDAR_QUARTER_DESC" ORDER BY SUM("AMOUNT_SOLD") DESC )<16) 4 - access("SALES"."CUST_ID"="CUSTOMERS"."CUST_ID") 5 - access("TIMES"."TIME_ID"="SALES"."TIME_ID") 6 - filter("TIMES"."CALENDAR_QUARTER_DESC"='2009-3') ------------------------------------------------------------------------------- Object ID : 14 Schema Name: SH SQL ID : 3fgwfcsa2k3bz SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2008-4' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16 ------------------------------------------------------------------------------- Execution Statistics ------------------------------------------------------------------------------- Parse Time (s): .000475 Elapsed Time (s): .195412 CPU Time (s): .19457 User I/O Time (s): 0 Buffer Gets: 15965 Disk Reads: 0 Direct Writes: 0 I/O Interconnect Bytes: 0 Rows Processed: 15 Fetches: 15 Executions: 1 End of Fetch Count: 1 Optimizer Cost: 4851 Notes ----- 1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers. 2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 5 ejecuciones. ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original ----------- Plan hash value: 3551373025 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 19370 | 1475K| | 4851 (1)| 00:00:59 | |* 1 | VIEW | | 19370 | 1475K| | 4851 (1)| 00:00:59 | |* 2 | WINDOW SORT PUSHED RANK| | 19370 | 964K| 2456K| 4851 (1)| 00:00:59 | | 3 | HASH GROUP BY | | 19370 | 964K| 2456K| 4851 (1)| 00:00:59 | |* 4 | HASH JOIN | | 19370 | 964K| | 4354 (1)| 00:00:53 | |* 5 | HASH JOIN | | 19587 | 592K| | 3157 (1)| 00:00:38 | |* 6 | TABLE ACCESS FULL | TIMES | 92 | 1380 | | 19 (0)| 00:00:01 | | 7 | TABLE ACCESS FULL | SALES | 1472K| 22M| | 3134 (1)| 00:00:38 | | 8 | TABLE ACCESS FULL | CUSTOMERS | 162K| 3175K| | 1197 (1)| 00:00:15 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RANK_WITHIN_QUARTER"<16) 2 - filter(RANK() OVER ( PARTITION BY "TIMES"."CALENDAR_QUARTER_DESC" ORDER BY SUM("AMOUNT_SOLD") DESC )<16) 4 - access("SALES"."CUST_ID"="CUSTOMERS"."CUST_ID") 5 - access("TIMES"."TIME_ID"="SALES"."TIME_ID") 6 - filter("TIMES"."CALENDAR_QUARTER_DESC"='2008-4') ------------------------------------------------------------------------------- Object ID : 59 Schema Name: SH SQL ID : gppx8hpcwsb5g SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2012-1' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16 ------------------------------------------------------------------------------- Execution Statistics ------------------------------------------------------------------------------- Parse Time (s): .000573 Elapsed Time (s): .194255 CPU Time (s): .19257 User I/O Time (s): 0 Buffer Gets: 15965 Disk Reads: 0 Direct Writes: 0 I/O Interconnect Bytes: 0 Rows Processed: 15 Fetches: 15 Executions: 1 End of Fetch Count: 1 Optimizer Cost: 4849 Notes ----- 1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers. 2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 5 ejecuciones. ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original ----------- Plan hash value: 3551373025 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 19159 | 1459K| | 4849 (1)| 00:00:59 | |* 1 | VIEW | | 19159 | 1459K| | 4849 (1)| 00:00:59 | |* 2 | WINDOW SORT PUSHED RANK| | 19159 | 954K| 2424K| 4849 (1)| 00:00:59 | | 3 | HASH GROUP BY | | 19159 | 954K| 2424K| 4849 (1)| 00:00:59 | |* 4 | HASH JOIN | | 19159 | 954K| | 4354 (1)| 00:00:53 | |* 5 | HASH JOIN | | 19374 | 586K| | 3157 (1)| 00:00:38 | |* 6 | TABLE ACCESS FULL | TIMES | 91 | 1365 | | 19 (0)| 00:00:01 | | 7 | TABLE ACCESS FULL | SALES | 1472K| 22M| | 3134 (1)| 00:00:38 | | 8 | TABLE ACCESS FULL | CUSTOMERS | 162K| 3175K| | 1197 (1)| 00:00:15 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RANK_WITHIN_QUARTER"<16) 2 - filter(RANK() OVER ( PARTITION BY "TIMES"."CALENDAR_QUARTER_DESC" ORDER BY SUM("AMOUNT_SOLD") DESC )<16) 4 - access("SALES"."CUST_ID"="CUSTOMERS"."CUST_ID") 5 - access("TIMES"."TIME_ID"="SALES"."TIME_ID") 6 - filter("TIMES"."CALENDAR_QUARTER_DESC"='2012-1') ------------------------------------------------------------------------------- Object ID : 15 Schema Name: SH SQL ID : 3zdr12jv606xs SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2011-1' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16 ------------------------------------------------------------------------------- Execution Statistics ------------------------------------------------------------------------------- Parse Time (s): .000566 Elapsed Time (s): .191762 CPU Time (s): .188971 User I/O Time (s): 0 Buffer Gets: 15965 Disk Reads: 0 Direct Writes: 0 I/O Interconnect Bytes: 0 Rows Processed: 15 Fetches: 15 Executions: 1 End of Fetch Count: 1 Optimizer Cost: 4841 Notes ----- 1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers. 2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 5 ejecuciones. ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original ----------- Plan hash value: 3551373025 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 18948 | 1443K| | 4841 (1)| 00:00:59 | |* 1 | VIEW | | 18948 | 1443K| | 4841 (1)| 00:00:59 | |* 2 | WINDOW SORT PUSHED RANK| | 18948 | 943K| 2408K| 4841 (1)| 00:00:59 | | 3 | HASH GROUP BY | | 18948 | 943K| 2408K| 4841 (1)| 00:00:59 | |* 4 | HASH JOIN | | 18948 | 943K| | 4354 (1)| 00:00:53 | |* 5 | HASH JOIN | | 19161 | 580K| | 3157 (1)| 00:00:38 | |* 6 | TABLE ACCESS FULL | TIMES | 90 | 1350 | | 19 (0)| 00:00:01 | | 7 | TABLE ACCESS FULL | SALES | 1472K| 22M| | 3134 (1)| 00:00:38 | | 8 | TABLE ACCESS FULL | CUSTOMERS | 162K| 3175K| | 1197 (1)| 00:00:15 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RANK_WITHIN_QUARTER"<16) 2 - filter(RANK() OVER ( PARTITION BY "TIMES"."CALENDAR_QUARTER_DESC" ORDER BY SUM("AMOUNT_SOLD") DESC )<16) 4 - access("SALES"."CUST_ID"="CUSTOMERS"."CUST_ID") 5 - access("TIMES"."TIME_ID"="SALES"."TIME_ID") 6 - filter("TIMES"."CALENDAR_QUARTER_DESC"='2011-1') ------------------------------------------------------------------------------- Object ID : 29 Schema Name: SH SQL ID : 8g3y654ptjzrz SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2010-2' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16 ------------------------------------------------------------------------------- Execution Statistics ------------------------------------------------------------------------------- Parse Time (s): .000539 Elapsed Time (s): .187466 CPU Time (s): .184972 User I/O Time (s): 0 Buffer Gets: 15965 Disk Reads: 0 Direct Writes: 0 I/O Interconnect Bytes: 0 Rows Processed: 15 Fetches: 15 Executions: 1 End of Fetch Count: 1 Optimizer Cost: 4849 Notes ----- 1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers. 2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 5 ejecuciones. ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original ----------- Plan hash value: 3551373025 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 19159 | 1459K| | 4849 (1)| 00:00:59 | |* 1 | VIEW | | 19159 | 1459K| | 4849 (1)| 00:00:59 | |* 2 | WINDOW SORT PUSHED RANK| | 19159 | 954K| 2424K| 4849 (1)| 00:00:59 | | 3 | HASH GROUP BY | | 19159 | 954K| 2424K| 4849 (1)| 00:00:59 | |* 4 | HASH JOIN | | 19159 | 954K| | 4354 (1)| 00:00:53 | |* 5 | HASH JOIN | | 19374 | 586K| | 3157 (1)| 00:00:38 | |* 6 | TABLE ACCESS FULL | TIMES | 91 | 1365 | | 19 (0)| 00:00:01 | | 7 | TABLE ACCESS FULL | SALES | 1472K| 22M| | 3134 (1)| 00:00:38 | | 8 | TABLE ACCESS FULL | CUSTOMERS | 162K| 3175K| | 1197 (1)| 00:00:15 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RANK_WITHIN_QUARTER"<16) 2 - filter(RANK() OVER ( PARTITION BY "TIMES"."CALENDAR_QUARTER_DESC" ORDER BY SUM("AMOUNT_SOLD") DESC )<16) 4 - access("SALES"."CUST_ID"="CUSTOMERS"."CUST_ID") 5 - access("TIMES"."TIME_ID"="SALES"."TIME_ID") 6 - filter("TIMES"."CALENDAR_QUARTER_DESC"='2010-2') ------------------------------------------------------------------------------- Object ID : 52 Schema Name: SH SQL ID : dfdg44tjr89uf SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2010-1' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16 ------------------------------------------------------------------------------- Execution Statistics ------------------------------------------------------------------------------- Parse Time (s): .000551 Elapsed Time (s): .18526 CPU Time (s): .182572 User I/O Time (s): 0 Buffer Gets: 15965 Disk Reads: 0 Direct Writes: 0 I/O Interconnect Bytes: 0 Rows Processed: 15 Fetches: 15 Executions: 1 End of Fetch Count: 1 Optimizer Cost: 4841 Notes ----- 1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers. 2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 5 ejecuciones. ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original ----------- Plan hash value: 3551373025 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 18948 | 1443K| | 4841 (1)| 00:00:59 | |* 1 | VIEW | | 18948 | 1443K| | 4841 (1)| 00:00:59 | |* 2 | WINDOW SORT PUSHED RANK| | 18948 | 943K| 2408K| 4841 (1)| 00:00:59 | | 3 | HASH GROUP BY | | 18948 | 943K| 2408K| 4841 (1)| 00:00:59 | |* 4 | HASH JOIN | | 18948 | 943K| | 4354 (1)| 00:00:53 | |* 5 | HASH JOIN | | 19161 | 580K| | 3157 (1)| 00:00:38 | |* 6 | TABLE ACCESS FULL | TIMES | 90 | 1350 | | 19 (0)| 00:00:01 | | 7 | TABLE ACCESS FULL | SALES | 1472K| 22M| | 3134 (1)| 00:00:38 | | 8 | TABLE ACCESS FULL | CUSTOMERS | 162K| 3175K| | 1197 (1)| 00:00:15 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RANK_WITHIN_QUARTER"<16) 2 - filter(RANK() OVER ( PARTITION BY "TIMES"."CALENDAR_QUARTER_DESC" ORDER BY SUM("AMOUNT_SOLD") DESC )<16) 4 - access("SALES"."CUST_ID"="CUSTOMERS"."CUST_ID") 5 - access("TIMES"."TIME_ID"="SALES"."TIME_ID") 6 - filter("TIMES"."CALENDAR_QUARTER_DESC"='2010-1') ------------------------------------------------------------------------------- Object ID : 33 Schema Name: SH SQL ID : 954q4b1vkp8m4 SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2008-2' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16 ------------------------------------------------------------------------------- Execution Statistics ------------------------------------------------------------------------------- Parse Time (s): .000536 Elapsed Time (s): .183291 CPU Time (s): .181372 User I/O Time (s): 0 Buffer Gets: 15965 Disk Reads: 0 Direct Writes: 0 I/O Interconnect Bytes: 0 Rows Processed: 16 Fetches: 16 Executions: 1 End of Fetch Count: 1 Optimizer Cost: 4849 Notes ----- 1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers. 2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 5 ejecuciones. ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original ----------- Plan hash value: 3551373025 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 19159 | 1459K| | 4849 (1)| 00:00:59 | |* 1 | VIEW | | 19159 | 1459K| | 4849 (1)| 00:00:59 | |* 2 | WINDOW SORT PUSHED RANK| | 19159 | 954K| 2424K| 4849 (1)| 00:00:59 | | 3 | HASH GROUP BY | | 19159 | 954K| 2424K| 4849 (1)| 00:00:59 | |* 4 | HASH JOIN | | 19159 | 954K| | 4354 (1)| 00:00:53 | |* 5 | HASH JOIN | | 19374 | 586K| | 3157 (1)| 00:00:38 | |* 6 | TABLE ACCESS FULL | TIMES | 91 | 1365 | | 19 (0)| 00:00:01 | | 7 | TABLE ACCESS FULL | SALES | 1472K| 22M| | 3134 (1)| 00:00:38 | | 8 | TABLE ACCESS FULL | CUSTOMERS | 162K| 3175K| | 1197 (1)| 00:00:15 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RANK_WITHIN_QUARTER"<16) 2 - filter(RANK() OVER ( PARTITION BY "TIMES"."CALENDAR_QUARTER_DESC" ORDER BY SUM("AMOUNT_SOLD") DESC )<16) 4 - access("SALES"."CUST_ID"="CUSTOMERS"."CUST_ID") 5 - access("TIMES"."TIME_ID"="SALES"."TIME_ID") 6 - filter("TIMES"."CALENDAR_QUARTER_DESC"='2008-2') ------------------------------------------------------------------------------- Object ID : 12 Schema Name: SH SQL ID : 28da8t4nc8uk9 SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2009-1' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16 ------------------------------------------------------------------------------- Execution Statistics ------------------------------------------------------------------------------- Parse Time (s): .00082 Elapsed Time (s): .174375 CPU Time (s): .173573 User I/O Time (s): 0 Buffer Gets: 15965 Disk Reads: 0 Direct Writes: 0 I/O Interconnect Bytes: 0 Rows Processed: 16 Fetches: 16 Executions: 1 End of Fetch Count: 1 Optimizer Cost: 4841 Notes ----- 1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers. 2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 5 ejecuciones. ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original ----------- Plan hash value: 3551373025 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 18948 | 1443K| | 4841 (1)| 00:00:59 | |* 1 | VIEW | | 18948 | 1443K| | 4841 (1)| 00:00:59 | |* 2 | WINDOW SORT PUSHED RANK| | 18948 | 943K| 2408K| 4841 (1)| 00:00:59 | | 3 | HASH GROUP BY | | 18948 | 943K| 2408K| 4841 (1)| 00:00:59 | |* 4 | HASH JOIN | | 18948 | 943K| | 4354 (1)| 00:00:53 | |* 5 | HASH JOIN | | 19161 | 580K| | 3157 (1)| 00:00:38 | |* 6 | TABLE ACCESS FULL | TIMES | 90 | 1350 | | 19 (0)| 00:00:01 | | 7 | TABLE ACCESS FULL | SALES | 1472K| 22M| | 3134 (1)| 00:00:38 | | 8 | TABLE ACCESS FULL | CUSTOMERS | 162K| 3175K| | 1197 (1)| 00:00:15 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RANK_WITHIN_QUARTER"<16) 2 - filter(RANK() OVER ( PARTITION BY "TIMES"."CALENDAR_QUARTER_DESC" ORDER BY SUM("AMOUNT_SOLD") DESC )<16) 4 - access("SALES"."CUST_ID"="CUSTOMERS"."CUST_ID") 5 - access("TIMES"."TIME_ID"="SALES"."TIME_ID") 6 - filter("TIMES"."CALENDAR_QUARTER_DESC"='2009-1') ------------------------------------------------------------------------------- Object ID : 42 Schema Name: SH SQL ID : a9nt3xdwup082 SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2007-1' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16 ------------------------------------------------------------------------------- Execution Statistics ------------------------------------------------------------------------------- Parse Time (s): .000791 Elapsed Time (s): .169425 CPU Time (s): .168174 User I/O Time (s): 0 Buffer Gets: 15965 Disk Reads: 0 Direct Writes: 0 I/O Interconnect Bytes: 0 Rows Processed: 16 Fetches: 16 Executions: 1 End of Fetch Count: 1 Optimizer Cost: 4841 Notes ----- 1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers. 2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 5 ejecuciones. ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original ----------- Plan hash value: 3551373025 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 18948 | 1443K| | 4841 (1)| 00:00:59 | |* 1 | VIEW | | 18948 | 1443K| | 4841 (1)| 00:00:59 | |* 2 | WINDOW SORT PUSHED RANK| | 18948 | 943K| 2408K| 4841 (1)| 00:00:59 | | 3 | HASH GROUP BY | | 18948 | 943K| 2408K| 4841 (1)| 00:00:59 | |* 4 | HASH JOIN | | 18948 | 943K| | 4354 (1)| 00:00:53 | |* 5 | HASH JOIN | | 19161 | 580K| | 3157 (1)| 00:00:38 | |* 6 | TABLE ACCESS FULL | TIMES | 90 | 1350 | | 19 (0)| 00:00:01 | | 7 | TABLE ACCESS FULL | SALES | 1472K| 22M| | 3134 (1)| 00:00:38 | | 8 | TABLE ACCESS FULL | CUSTOMERS | 162K| 3175K| | 1197 (1)| 00:00:15 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RANK_WITHIN_QUARTER"<16) 2 - filter(RANK() OVER ( PARTITION BY "TIMES"."CALENDAR_QUARTER_DESC" ORDER BY SUM("AMOUNT_SOLD") DESC )<16) 4 - access("SALES"."CUST_ID"="CUSTOMERS"."CUST_ID") 5 - access("TIMES"."TIME_ID"="SALES"."TIME_ID") 6 - filter("TIMES"."CALENDAR_QUARTER_DESC"='2007-1') ------------------------------------------------------------------------------- Object ID : 8 Schema Name: SH SQL ID : 1xmq1jkcnp2mf SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2006-4' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16 ------------------------------------------------------------------------------- Execution Statistics ------------------------------------------------------------------------------- Parse Time (s): .000491 Elapsed Time (s): .168748 CPU Time (s): .169974 User I/O Time (s): 0 Buffer Gets: 15965 Disk Reads: 0 Direct Writes: 0 I/O Interconnect Bytes: 0 Rows Processed: 16 Fetches: 16 Executions: 1 End of Fetch Count: 1 Optimizer Cost: 4851 Notes ----- 1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers. 2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 5 ejecuciones. ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original ----------- Plan hash value: 3551373025 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 19370 | 1475K| | 4851 (1)| 00:00:59 | |* 1 | VIEW | | 19370 | 1475K| | 4851 (1)| 00:00:59 | |* 2 | WINDOW SORT PUSHED RANK| | 19370 | 964K| 2456K| 4851 (1)| 00:00:59 | | 3 | HASH GROUP BY | | 19370 | 964K| 2456K| 4851 (1)| 00:00:59 | |* 4 | HASH JOIN | | 19370 | 964K| | 4354 (1)| 00:00:53 | |* 5 | HASH JOIN | | 19587 | 592K| | 3157 (1)| 00:00:38 | |* 6 | TABLE ACCESS FULL | TIMES | 92 | 1380 | | 19 (0)| 00:00:01 | | 7 | TABLE ACCESS FULL | SALES | 1472K| 22M| | 3134 (1)| 00:00:38 | | 8 | TABLE ACCESS FULL | CUSTOMERS | 162K| 3175K| | 1197 (1)| 00:00:15 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RANK_WITHIN_QUARTER"<16) 2 - filter(RANK() OVER ( PARTITION BY "TIMES"."CALENDAR_QUARTER_DESC" ORDER BY SUM("AMOUNT_SOLD") DESC )<16) 4 - access("SALES"."CUST_ID"="CUSTOMERS"."CUST_ID") 5 - access("TIMES"."TIME_ID"="SALES"."TIME_ID") 6 - filter("TIMES"."CALENDAR_QUARTER_DESC"='2006-4') ------------------------------------------------------------------------------- Object ID : 61 Schema Name: SH SQL ID : gx7ymx5myrd2t SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2008-1' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16 ------------------------------------------------------------------------------- Execution Statistics ------------------------------------------------------------------------------- Parse Time (s): .00063 Elapsed Time (s): .167982 CPU Time (s): .166574 User I/O Time (s): 0 Buffer Gets: 15965 Disk Reads: 0 Direct Writes: 0 I/O Interconnect Bytes: 0 Rows Processed: 15 Fetches: 15 Executions: 1 End of Fetch Count: 1 Optimizer Cost: 4849 Notes ----- 1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers. 2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 5 ejecuciones. ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original ----------- Plan hash value: 3551373025 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 19159 | 1459K| | 4849 (1)| 00:00:59 | |* 1 | VIEW | | 19159 | 1459K| | 4849 (1)| 00:00:59 | |* 2 | WINDOW SORT PUSHED RANK| | 19159 | 954K| 2424K| 4849 (1)| 00:00:59 | | 3 | HASH GROUP BY | | 19159 | 954K| 2424K| 4849 (1)| 00:00:59 | |* 4 | HASH JOIN | | 19159 | 954K| | 4354 (1)| 00:00:53 | |* 5 | HASH JOIN | | 19374 | 586K| | 3157 (1)| 00:00:38 | |* 6 | TABLE ACCESS FULL | TIMES | 91 | 1365 | | 19 (0)| 00:00:01 | | 7 | TABLE ACCESS FULL | SALES | 1472K| 22M| | 3134 (1)| 00:00:38 | | 8 | TABLE ACCESS FULL | CUSTOMERS | 162K| 3175K| | 1197 (1)| 00:00:15 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RANK_WITHIN_QUARTER"<16) 2 - filter(RANK() OVER ( PARTITION BY "TIMES"."CALENDAR_QUARTER_DESC" ORDER BY SUM("AMOUNT_SOLD") DESC )<16) 4 - access("SALES"."CUST_ID"="CUSTOMERS"."CUST_ID") 5 - access("TIMES"."TIME_ID"="SALES"."TIME_ID") 6 - filter("TIMES"."CALENDAR_QUARTER_DESC"='2008-1') ------------------------------------------------------------------------------- Object ID : 35 Schema Name: SH SQL ID : 9aft7htmzpd1d SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2004-2' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16 ------------------------------------------------------------------------------- Execution Statistics ------------------------------------------------------------------------------- Parse Time (s): .001007 Elapsed Time (s): .166518 CPU Time (s): .164975 User I/O Time (s): 0 Buffer Gets: 15965 Disk Reads: 0 Direct Writes: 0 I/O Interconnect Bytes: 0 Rows Processed: 16 Fetches: 16 Executions: 1 End of Fetch Count: 1 Optimizer Cost: 4849 Notes ----- 1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers. 2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 6 ejecuciones. ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original ----------- Plan hash value: 3551373025 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 19159 | 1459K| | 4849 (1)| 00:00:59 | |* 1 | VIEW | | 19159 | 1459K| | 4849 (1)| 00:00:59 | |* 2 | WINDOW SORT PUSHED RANK| | 19159 | 954K| 2424K| 4849 (1)| 00:00:59 | | 3 | HASH GROUP BY | | 19159 | 954K| 2424K| 4849 (1)| 00:00:59 | |* 4 | HASH JOIN | | 19159 | 954K| | 4354 (1)| 00:00:53 | |* 5 | HASH JOIN | | 19374 | 586K| | 3157 (1)| 00:00:38 | |* 6 | TABLE ACCESS FULL | TIMES | 91 | 1365 | | 19 (0)| 00:00:01 | | 7 | TABLE ACCESS FULL | SALES | 1472K| 22M| | 3134 (1)| 00:00:38 | | 8 | TABLE ACCESS FULL | CUSTOMERS | 162K| 3175K| | 1197 (1)| 00:00:15 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RANK_WITHIN_QUARTER"<16) 2 - filter(RANK() OVER ( PARTITION BY "TIMES"."CALENDAR_QUARTER_DESC" ORDER BY SUM("AMOUNT_SOLD") DESC )<16) 4 - access("SALES"."CUST_ID"="CUSTOMERS"."CUST_ID") 5 - access("TIMES"."TIME_ID"="SALES"."TIME_ID") 6 - filter("TIMES"."CALENDAR_QUARTER_DESC"='2004-2') ------------------------------------------------------------------------------- Object ID : 20 Schema Name: SH SQL ID : 5hppq6bp5wmnm SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2006-3' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16 ------------------------------------------------------------------------------- Execution Statistics ------------------------------------------------------------------------------- Parse Time (s): .000711 Elapsed Time (s): .163524 CPU Time (s): .162975 User I/O Time (s): 0 Buffer Gets: 15965 Disk Reads: 0 Direct Writes: 0 I/O Interconnect Bytes: 0 Rows Processed: 15 Fetches: 15 Executions: 1 End of Fetch Count: 1 Optimizer Cost: 4851 Notes ----- 1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers. 2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 6 ejecuciones. ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original ----------- Plan hash value: 3551373025 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 19370 | 1475K| | 4851 (1)| 00:00:59 | |* 1 | VIEW | | 19370 | 1475K| | 4851 (1)| 00:00:59 | |* 2 | WINDOW SORT PUSHED RANK| | 19370 | 964K| 2456K| 4851 (1)| 00:00:59 | | 3 | HASH GROUP BY | | 19370 | 964K| 2456K| 4851 (1)| 00:00:59 | |* 4 | HASH JOIN | | 19370 | 964K| | 4354 (1)| 00:00:53 | |* 5 | HASH JOIN | | 19587 | 592K| | 3157 (1)| 00:00:38 | |* 6 | TABLE ACCESS FULL | TIMES | 92 | 1380 | | 19 (0)| 00:00:01 | | 7 | TABLE ACCESS FULL | SALES | 1472K| 22M| | 3134 (1)| 00:00:38 | | 8 | TABLE ACCESS FULL | CUSTOMERS | 162K| 3175K| | 1197 (1)| 00:00:15 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RANK_WITHIN_QUARTER"<16) 2 - filter(RANK() OVER ( PARTITION BY "TIMES"."CALENDAR_QUARTER_DESC" ORDER BY SUM("AMOUNT_SOLD") DESC )<16) 4 - access("SALES"."CUST_ID"="CUSTOMERS"."CUST_ID") 5 - access("TIMES"."TIME_ID"="SALES"."TIME_ID") 6 - filter("TIMES"."CALENDAR_QUARTER_DESC"='2006-3') ------------------------------------------------------------------------------- Object ID : 17 Schema Name: SH SQL ID : 4kwqmbh8cs1gp SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '1998-3' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16 ------------------------------------------------------------------------------- Execution Statistics ------------------------------------------------------------------------------- Parse Time (s): .000592 Elapsed Time (s): .162285 CPU Time (s): .160309 User I/O Time (s): 0 Buffer Gets: 15965 Disk Reads: 0 Direct Writes: 0 I/O Interconnect Bytes: 0 Rows Processed: 16 Fetches: 16 Executions: 1 End of Fetch Count: 1 Optimizer Cost: 4851 Notes ----- 1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers. 2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 6 ejecuciones. ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original ----------- Plan hash value: 3551373025 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 19370 | 1475K| | 4851 (1)| 00:00:59 | |* 1 | VIEW | | 19370 | 1475K| | 4851 (1)| 00:00:59 | |* 2 | WINDOW SORT PUSHED RANK| | 19370 | 964K| 2456K| 4851 (1)| 00:00:59 | | 3 | HASH GROUP BY | | 19370 | 964K| 2456K| 4851 (1)| 00:00:59 | |* 4 | HASH JOIN | | 19370 | 964K| | 4354 (1)| 00:00:53 | |* 5 | HASH JOIN | | 19587 | 592K| | 3157 (1)| 00:00:38 | |* 6 | TABLE ACCESS FULL | TIMES | 92 | 1380 | | 19 (0)| 00:00:01 | | 7 | TABLE ACCESS FULL | SALES | 1472K| 22M| | 3134 (1)| 00:00:38 | | 8 | TABLE ACCESS FULL | CUSTOMERS | 162K| 3175K| | 1197 (1)| 00:00:15 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RANK_WITHIN_QUARTER"<16) 2 - filter(RANK() OVER ( PARTITION BY "TIMES"."CALENDAR_QUARTER_DESC" ORDER BY SUM("AMOUNT_SOLD") DESC )<16) 4 - access("SALES"."CUST_ID"="CUSTOMERS"."CUST_ID") 5 - access("TIMES"."TIME_ID"="SALES"."TIME_ID") 6 - filter("TIMES"."CALENDAR_QUARTER_DESC"='1998-3') ------------------------------------------------------------------------------- Object ID : 37 Schema Name: SH SQL ID : 9fsjrrksxp4gj SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2000-1' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16 ------------------------------------------------------------------------------- Execution Statistics ------------------------------------------------------------------------------- Parse Time (s): .000597 Elapsed Time (s): .162116 CPU Time (s): .160575 User I/O Time (s): 0 Buffer Gets: 15965 Disk Reads: 0 Direct Writes: 0 I/O Interconnect Bytes: 0 Rows Processed: 15 Fetches: 15 Executions: 1 End of Fetch Count: 1 Optimizer Cost: 4849 Notes ----- 1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers. 2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 5 ejecuciones. ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original ----------- Plan hash value: 3551373025 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 19159 | 1459K| | 4849 (1)| 00:00:59 | |* 1 | VIEW | | 19159 | 1459K| | 4849 (1)| 00:00:59 | |* 2 | WINDOW SORT PUSHED RANK| | 19159 | 954K| 2424K| 4849 (1)| 00:00:59 | | 3 | HASH GROUP BY | | 19159 | 954K| 2424K| 4849 (1)| 00:00:59 | |* 4 | HASH JOIN | | 19159 | 954K| | 4354 (1)| 00:00:53 | |* 5 | HASH JOIN | | 19374 | 586K| | 3157 (1)| 00:00:38 | |* 6 | TABLE ACCESS FULL | TIMES | 91 | 1365 | | 19 (0)| 00:00:01 | | 7 | TABLE ACCESS FULL | SALES | 1472K| 22M| | 3134 (1)| 00:00:38 | | 8 | TABLE ACCESS FULL | CUSTOMERS | 162K| 3175K| | 1197 (1)| 00:00:15 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RANK_WITHIN_QUARTER"<16) 2 - filter(RANK() OVER ( PARTITION BY "TIMES"."CALENDAR_QUARTER_DESC" ORDER BY SUM("AMOUNT_SOLD") DESC )<16) 4 - access("SALES"."CUST_ID"="CUSTOMERS"."CUST_ID") 5 - access("TIMES"."TIME_ID"="SALES"."TIME_ID") 6 - filter("TIMES"."CALENDAR_QUARTER_DESC"='2000-1') ------------------------------------------------------------------------------- Object ID : 38 Schema Name: SH SQL ID : 9h2u2z9y900yk SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '1999-3' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16 ------------------------------------------------------------------------------- Execution Statistics ------------------------------------------------------------------------------- Parse Time (s): .000519 Elapsed Time (s): .159764 CPU Time (s): .157976 User I/O Time (s): 0 Buffer Gets: 15965 Disk Reads: 0 Direct Writes: 0 I/O Interconnect Bytes: 0 Rows Processed: 15 Fetches: 15 Executions: 1 End of Fetch Count: 1 Optimizer Cost: 4851 Notes ----- 1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers. 2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 6 ejecuciones. ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original ----------- Plan hash value: 3551373025 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 19370 | 1475K| | 4851 (1)| 00:00:59 | |* 1 | VIEW | | 19370 | 1475K| | 4851 (1)| 00:00:59 | |* 2 | WINDOW SORT PUSHED RANK| | 19370 | 964K| 2456K| 4851 (1)| 00:00:59 | | 3 | HASH GROUP BY | | 19370 | 964K| 2456K| 4851 (1)| 00:00:59 | |* 4 | HASH JOIN | | 19370 | 964K| | 4354 (1)| 00:00:53 | |* 5 | HASH JOIN | | 19587 | 592K| | 3157 (1)| 00:00:38 | |* 6 | TABLE ACCESS FULL | TIMES | 92 | 1380 | | 19 (0)| 00:00:01 | | 7 | TABLE ACCESS FULL | SALES | 1472K| 22M| | 3134 (1)| 00:00:38 | | 8 | TABLE ACCESS FULL | CUSTOMERS | 162K| 3175K| | 1197 (1)| 00:00:15 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RANK_WITHIN_QUARTER"<16) 2 - filter(RANK() OVER ( PARTITION BY "TIMES"."CALENDAR_QUARTER_DESC" ORDER BY SUM("AMOUNT_SOLD") DESC )<16) 4 - access("SALES"."CUST_ID"="CUSTOMERS"."CUST_ID") 5 - access("TIMES"."TIME_ID"="SALES"."TIME_ID") 6 - filter("TIMES"."CALENDAR_QUARTER_DESC"='1999-3') ------------------------------------------------------------------------------- Object ID : 55 Schema Name: SH SQL ID : fd5j4mmztdpmm SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2003-3' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16 ------------------------------------------------------------------------------- Execution Statistics ------------------------------------------------------------------------------- Parse Time (s): .000434 Elapsed Time (s): .156774 CPU Time (s): .154643 User I/O Time (s): 0 Buffer Gets: 15965 Disk Reads: 0 Direct Writes: 0 I/O Interconnect Bytes: 0 Rows Processed: 15 Fetches: 15 Executions: 1 End of Fetch Count: 1 Optimizer Cost: 4851 Notes ----- 1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers. 2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 6 ejecuciones. ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original ----------- Plan hash value: 3551373025 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 19370 | 1475K| | 4851 (1)| 00:00:59 | |* 1 | VIEW | | 19370 | 1475K| | 4851 (1)| 00:00:59 | |* 2 | WINDOW SORT PUSHED RANK| | 19370 | 964K| 2456K| 4851 (1)| 00:00:59 | | 3 | HASH GROUP BY | | 19370 | 964K| 2456K| 4851 (1)| 00:00:59 | |* 4 | HASH JOIN | | 19370 | 964K| | 4354 (1)| 00:00:53 | |* 5 | HASH JOIN | | 19587 | 592K| | 3157 (1)| 00:00:38 | |* 6 | TABLE ACCESS FULL | TIMES | 92 | 1380 | | 19 (0)| 00:00:01 | | 7 | TABLE ACCESS FULL | SALES | 1472K| 22M| | 3134 (1)| 00:00:38 | | 8 | TABLE ACCESS FULL | CUSTOMERS | 162K| 3175K| | 1197 (1)| 00:00:15 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RANK_WITHIN_QUARTER"<16) 2 - filter(RANK() OVER ( PARTITION BY "TIMES"."CALENDAR_QUARTER_DESC" ORDER BY SUM("AMOUNT_SOLD") DESC )<16) 4 - access("SALES"."CUST_ID"="CUSTOMERS"."CUST_ID") 5 - access("TIMES"."TIME_ID"="SALES"."TIME_ID") 6 - filter("TIMES"."CALENDAR_QUARTER_DESC"='2003-3') ------------------------------------------------------------------------------- Object ID : 3 Schema Name: SH SQL ID : 03ghr9b5m3x43 SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2003-4' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16 ------------------------------------------------------------------------------- Execution Statistics ------------------------------------------------------------------------------- Parse Time (s): .000451 Elapsed Time (s): .154958 CPU Time (s): .154726 User I/O Time (s): 0 Buffer Gets: 15965 Disk Reads: 0 Direct Writes: 0 I/O Interconnect Bytes: 0 Rows Processed: 15 Fetches: 15 Executions: 1 End of Fetch Count: 1 Optimizer Cost: 4851 Notes ----- 1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers. 2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 4 ejecuciones. ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original ----------- Plan hash value: 3551373025 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 19370 | 1475K| | 4851 (1)| 00:00:59 | |* 1 | VIEW | | 19370 | 1475K| | 4851 (1)| 00:00:59 | |* 2 | WINDOW SORT PUSHED RANK| | 19370 | 964K| 2456K| 4851 (1)| 00:00:59 | | 3 | HASH GROUP BY | | 19370 | 964K| 2456K| 4851 (1)| 00:00:59 | |* 4 | HASH JOIN | | 19370 | 964K| | 4354 (1)| 00:00:53 | |* 5 | HASH JOIN | | 19587 | 592K| | 3157 (1)| 00:00:38 | |* 6 | TABLE ACCESS FULL | TIMES | 92 | 1380 | | 19 (0)| 00:00:01 | | 7 | TABLE ACCESS FULL | SALES | 1472K| 22M| | 3134 (1)| 00:00:38 | | 8 | TABLE ACCESS FULL | CUSTOMERS | 162K| 3175K| | 1197 (1)| 00:00:15 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RANK_WITHIN_QUARTER"<16) 2 - filter(RANK() OVER ( PARTITION BY "TIMES"."CALENDAR_QUARTER_DESC" ORDER BY SUM("AMOUNT_SOLD") DESC )<16) 4 - access("SALES"."CUST_ID"="CUSTOMERS"."CUST_ID") 5 - access("TIMES"."TIME_ID"="SALES"."TIME_ID") 6 - filter("TIMES"."CALENDAR_QUARTER_DESC"='2003-4') ------------------------------------------------------------------------------- Object ID : 18 Schema Name: SH SQL ID : 4wazxyxv5cpt0 SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2005-1' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16 ------------------------------------------------------------------------------- Execution Statistics ------------------------------------------------------------------------------- Parse Time (s): .000467 Elapsed Time (s): .154513 CPU Time (s): .153643 User I/O Time (s): 0 Buffer Gets: 15965 Disk Reads: 0 Direct Writes: 0 I/O Interconnect Bytes: 0 Rows Processed: 15 Fetches: 15 Executions: 1 End of Fetch Count: 1 Optimizer Cost: 4841 Notes ----- 1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers. 2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 6 ejecuciones. ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original ----------- Plan hash value: 3551373025 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 18948 | 1443K| | 4841 (1)| 00:00:59 | |* 1 | VIEW | | 18948 | 1443K| | 4841 (1)| 00:00:59 | |* 2 | WINDOW SORT PUSHED RANK| | 18948 | 943K| 2408K| 4841 (1)| 00:00:59 | | 3 | HASH GROUP BY | | 18948 | 943K| 2408K| 4841 (1)| 00:00:59 | |* 4 | HASH JOIN | | 18948 | 943K| | 4354 (1)| 00:00:53 | |* 5 | HASH JOIN | | 19161 | 580K| | 3157 (1)| 00:00:38 | |* 6 | TABLE ACCESS FULL | TIMES | 90 | 1350 | | 19 (0)| 00:00:01 | | 7 | TABLE ACCESS FULL | SALES | 1472K| 22M| | 3134 (1)| 00:00:38 | | 8 | TABLE ACCESS FULL | CUSTOMERS | 162K| 3175K| | 1197 (1)| 00:00:15 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RANK_WITHIN_QUARTER"<16) 2 - filter(RANK() OVER ( PARTITION BY "TIMES"."CALENDAR_QUARTER_DESC" ORDER BY SUM("AMOUNT_SOLD") DESC )<16) 4 - access("SALES"."CUST_ID"="CUSTOMERS"."CUST_ID") 5 - access("TIMES"."TIME_ID"="SALES"."TIME_ID") 6 - filter("TIMES"."CALENDAR_QUARTER_DESC"='2005-1') ------------------------------------------------------------------------------- Object ID : 7 Schema Name: SH SQL ID : 1qr4ps69f36jy SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2006-2' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16 ------------------------------------------------------------------------------- Execution Statistics ------------------------------------------------------------------------------- Parse Time (s): .000442 Elapsed Time (s): .154435 CPU Time (s): .155143 User I/O Time (s): 0 Buffer Gets: 15965 Disk Reads: 0 Direct Writes: 0 I/O Interconnect Bytes: 0 Rows Processed: 15 Fetches: 15 Executions: 1 End of Fetch Count: 1 Optimizer Cost: 4849 Notes ----- 1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers. 2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 6 ejecuciones. ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original ----------- Plan hash value: 3551373025 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 19159 | 1459K| | 4849 (1)| 00:00:59 | |* 1 | VIEW | | 19159 | 1459K| | 4849 (1)| 00:00:59 | |* 2 | WINDOW SORT PUSHED RANK| | 19159 | 954K| 2424K| 4849 (1)| 00:00:59 | | 3 | HASH GROUP BY | | 19159 | 954K| 2424K| 4849 (1)| 00:00:59 | |* 4 | HASH JOIN | | 19159 | 954K| | 4354 (1)| 00:00:53 | |* 5 | HASH JOIN | | 19374 | 586K| | 3157 (1)| 00:00:38 | |* 6 | TABLE ACCESS FULL | TIMES | 91 | 1365 | | 19 (0)| 00:00:01 | | 7 | TABLE ACCESS FULL | SALES | 1472K| 22M| | 3134 (1)| 00:00:38 | | 8 | TABLE ACCESS FULL | CUSTOMERS | 162K| 3175K| | 1197 (1)| 00:00:15 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RANK_WITHIN_QUARTER"<16) 2 - filter(RANK() OVER ( PARTITION BY "TIMES"."CALENDAR_QUARTER_DESC" ORDER BY SUM("AMOUNT_SOLD") DESC )<16) 4 - access("SALES"."CUST_ID"="CUSTOMERS"."CUST_ID") 5 - access("TIMES"."TIME_ID"="SALES"."TIME_ID") 6 - filter("TIMES"."CALENDAR_QUARTER_DESC"='2006-2') ------------------------------------------------------------------------------- Object ID : 46 Schema Name: SH SQL ID : au7x78c2uju0p SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2005-3' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16 ------------------------------------------------------------------------------- Execution Statistics ------------------------------------------------------------------------------- Parse Time (s): .00116 Elapsed Time (s): .154018 CPU Time (s): .154143 User I/O Time (s): 0 Buffer Gets: 15965 Disk Reads: 0 Direct Writes: 0 I/O Interconnect Bytes: 0 Rows Processed: 15 Fetches: 15 Executions: 1 End of Fetch Count: 1 Optimizer Cost: 4851 Notes ----- 1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers. 2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 6 ejecuciones. ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original ----------- Plan hash value: 3551373025 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 19370 | 1475K| | 4851 (1)| 00:00:59 | |* 1 | VIEW | | 19370 | 1475K| | 4851 (1)| 00:00:59 | |* 2 | WINDOW SORT PUSHED RANK| | 19370 | 964K| 2456K| 4851 (1)| 00:00:59 | | 3 | HASH GROUP BY | | 19370 | 964K| 2456K| 4851 (1)| 00:00:59 | |* 4 | HASH JOIN | | 19370 | 964K| | 4354 (1)| 00:00:53 | |* 5 | HASH JOIN | | 19587 | 592K| | 3157 (1)| 00:00:38 | |* 6 | TABLE ACCESS FULL | TIMES | 92 | 1380 | | 19 (0)| 00:00:01 | | 7 | TABLE ACCESS FULL | SALES | 1472K| 22M| | 3134 (1)| 00:00:38 | | 8 | TABLE ACCESS FULL | CUSTOMERS | 162K| 3175K| | 1197 (1)| 00:00:15 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RANK_WITHIN_QUARTER"<16) 2 - filter(RANK() OVER ( PARTITION BY "TIMES"."CALENDAR_QUARTER_DESC" ORDER BY SUM("AMOUNT_SOLD") DESC )<16) 4 - access("SALES"."CUST_ID"="CUSTOMERS"."CUST_ID") 5 - access("TIMES"."TIME_ID"="SALES"."TIME_ID") 6 - filter("TIMES"."CALENDAR_QUARTER_DESC"='2005-3') ------------------------------------------------------------------------------- Object ID : 57 Schema Name: SH SQL ID : fx2c47hcyfz9f SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2006-1' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16 ------------------------------------------------------------------------------- Execution Statistics ------------------------------------------------------------------------------- Parse Time (s): .000505 Elapsed Time (s): .153042 CPU Time (s): .15181 User I/O Time (s): 0 Buffer Gets: 15965 Disk Reads: 0 Direct Writes: 0 I/O Interconnect Bytes: 0 Rows Processed: 15 Fetches: 15 Executions: 1 End of Fetch Count: 1 Optimizer Cost: 4841 Notes ----- 1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers. 2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 6 ejecuciones. ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original ----------- Plan hash value: 3551373025 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 18948 | 1443K| | 4841 (1)| 00:00:59 | |* 1 | VIEW | | 18948 | 1443K| | 4841 (1)| 00:00:59 | |* 2 | WINDOW SORT PUSHED RANK| | 18948 | 943K| 2408K| 4841 (1)| 00:00:59 | | 3 | HASH GROUP BY | | 18948 | 943K| 2408K| 4841 (1)| 00:00:59 | |* 4 | HASH JOIN | | 18948 | 943K| | 4354 (1)| 00:00:53 | |* 5 | HASH JOIN | | 19161 | 580K| | 3157 (1)| 00:00:38 | |* 6 | TABLE ACCESS FULL | TIMES | 90 | 1350 | | 19 (0)| 00:00:01 | | 7 | TABLE ACCESS FULL | SALES | 1472K| 22M| | 3134 (1)| 00:00:38 | | 8 | TABLE ACCESS FULL | CUSTOMERS | 162K| 3175K| | 1197 (1)| 00:00:15 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RANK_WITHIN_QUARTER"<16) 2 - filter(RANK() OVER ( PARTITION BY "TIMES"."CALENDAR_QUARTER_DESC" ORDER BY SUM("AMOUNT_SOLD") DESC )<16) 4 - access("SALES"."CUST_ID"="CUSTOMERS"."CUST_ID") 5 - access("TIMES"."TIME_ID"="SALES"."TIME_ID") 6 - filter("TIMES"."CALENDAR_QUARTER_DESC"='2006-1') ------------------------------------------------------------------------------- Object ID : 56 Schema Name: SH SQL ID : fwsw6473mw961 SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2004-1' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16 ------------------------------------------------------------------------------- Execution Statistics ------------------------------------------------------------------------------- Parse Time (s): .000558 Elapsed Time (s): .151124 CPU Time (s): .14931 User I/O Time (s): 0 Buffer Gets: 15965 Disk Reads: 0 Direct Writes: 0 I/O Interconnect Bytes: 0 Rows Processed: 17 Fetches: 17 Executions: 1 End of Fetch Count: 1 Optimizer Cost: 4849 Notes ----- 1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers. 2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 6 ejecuciones. ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original ----------- Plan hash value: 3551373025 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 19159 | 1459K| | 4849 (1)| 00:00:59 | |* 1 | VIEW | | 19159 | 1459K| | 4849 (1)| 00:00:59 | |* 2 | WINDOW SORT PUSHED RANK| | 19159 | 954K| 2424K| 4849 (1)| 00:00:59 | | 3 | HASH GROUP BY | | 19159 | 954K| 2424K| 4849 (1)| 00:00:59 | |* 4 | HASH JOIN | | 19159 | 954K| | 4354 (1)| 00:00:53 | |* 5 | HASH JOIN | | 19374 | 586K| | 3157 (1)| 00:00:38 | |* 6 | TABLE ACCESS FULL | TIMES | 91 | 1365 | | 19 (0)| 00:00:01 | | 7 | TABLE ACCESS FULL | SALES | 1472K| 22M| | 3134 (1)| 00:00:38 | | 8 | TABLE ACCESS FULL | CUSTOMERS | 162K| 3175K| | 1197 (1)| 00:00:15 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RANK_WITHIN_QUARTER"<16) 2 - filter(RANK() OVER ( PARTITION BY "TIMES"."CALENDAR_QUARTER_DESC" ORDER BY SUM("AMOUNT_SOLD") DESC )<16) 4 - access("SALES"."CUST_ID"="CUSTOMERS"."CUST_ID") 5 - access("TIMES"."TIME_ID"="SALES"."TIME_ID") 6 - filter("TIMES"."CALENDAR_QUARTER_DESC"='2004-1') ------------------------------------------------------------------------------- Object ID : 30 Schema Name: SH SQL ID : 8hytq9ybsh5xu SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2000-2' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16 ------------------------------------------------------------------------------- Execution Statistics ------------------------------------------------------------------------------- Parse Time (s): .000504 Elapsed Time (s): .150745 CPU Time (s): .149477 User I/O Time (s): 0 Buffer Gets: 15965 Disk Reads: 0 Direct Writes: 0 I/O Interconnect Bytes: 0 Rows Processed: 16 Fetches: 16 Executions: 1 End of Fetch Count: 1 Optimizer Cost: 4849 Notes ----- 1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers. 2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 6 ejecuciones. ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original ----------- Plan hash value: 3551373025 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 19159 | 1459K| | 4849 (1)| 00:00:59 | |* 1 | VIEW | | 19159 | 1459K| | 4849 (1)| 00:00:59 | |* 2 | WINDOW SORT PUSHED RANK| | 19159 | 954K| 2424K| 4849 (1)| 00:00:59 | | 3 | HASH GROUP BY | | 19159 | 954K| 2424K| 4849 (1)| 00:00:59 | |* 4 | HASH JOIN | | 19159 | 954K| | 4354 (1)| 00:00:53 | |* 5 | HASH JOIN | | 19374 | 586K| | 3157 (1)| 00:00:38 | |* 6 | TABLE ACCESS FULL | TIMES | 91 | 1365 | | 19 (0)| 00:00:01 | | 7 | TABLE ACCESS FULL | SALES | 1472K| 22M| | 3134 (1)| 00:00:38 | | 8 | TABLE ACCESS FULL | CUSTOMERS | 162K| 3175K| | 1197 (1)| 00:00:15 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RANK_WITHIN_QUARTER"<16) 2 - filter(RANK() OVER ( PARTITION BY "TIMES"."CALENDAR_QUARTER_DESC" ORDER BY SUM("AMOUNT_SOLD") DESC )<16) 4 - access("SALES"."CUST_ID"="CUSTOMERS"."CUST_ID") 5 - access("TIMES"."TIME_ID"="SALES"."TIME_ID") 6 - filter("TIMES"."CALENDAR_QUARTER_DESC"='2000-2') ------------------------------------------------------------------------------- Object ID : 31 Schema Name: SH SQL ID : 8k0bufga1dutt SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2002-1' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16 ------------------------------------------------------------------------------- Execution Statistics ------------------------------------------------------------------------------- Parse Time (s): .000484 Elapsed Time (s): .150603 CPU Time (s): .149144 User I/O Time (s): 0 Buffer Gets: 15965 Disk Reads: 0 Direct Writes: 0 I/O Interconnect Bytes: 0 Rows Processed: 16 Fetches: 16 Executions: 1 End of Fetch Count: 1 Optimizer Cost: 4841 Notes ----- 1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers. 2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 6 ejecuciones. ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original ----------- Plan hash value: 3551373025 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 18948 | 1443K| | 4841 (1)| 00:00:59 | |* 1 | VIEW | | 18948 | 1443K| | 4841 (1)| 00:00:59 | |* 2 | WINDOW SORT PUSHED RANK| | 18948 | 943K| 2408K| 4841 (1)| 00:00:59 | | 3 | HASH GROUP BY | | 18948 | 943K| 2408K| 4841 (1)| 00:00:59 | |* 4 | HASH JOIN | | 18948 | 943K| | 4354 (1)| 00:00:53 | |* 5 | HASH JOIN | | 19161 | 580K| | 3157 (1)| 00:00:38 | |* 6 | TABLE ACCESS FULL | TIMES | 90 | 1350 | | 19 (0)| 00:00:01 | | 7 | TABLE ACCESS FULL | SALES | 1472K| 22M| | 3134 (1)| 00:00:38 | | 8 | TABLE ACCESS FULL | CUSTOMERS | 162K| 3175K| | 1197 (1)| 00:00:15 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RANK_WITHIN_QUARTER"<16) 2 - filter(RANK() OVER ( PARTITION BY "TIMES"."CALENDAR_QUARTER_DESC" ORDER BY SUM("AMOUNT_SOLD") DESC )<16) 4 - access("SALES"."CUST_ID"="CUSTOMERS"."CUST_ID") 5 - access("TIMES"."TIME_ID"="SALES"."TIME_ID") 6 - filter("TIMES"."CALENDAR_QUARTER_DESC"='2002-1') ------------------------------------------------------------------------------- Object ID : 26 Schema Name: SH SQL ID : 7f6kr3z5p8qxa SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2002-2' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16 ------------------------------------------------------------------------------- Execution Statistics ------------------------------------------------------------------------------- Parse Time (s): .000787 Elapsed Time (s): .149625 CPU Time (s): .148144 User I/O Time (s): 0 Buffer Gets: 15965 Disk Reads: 0 Direct Writes: 0 I/O Interconnect Bytes: 0 Rows Processed: 16 Fetches: 16 Executions: 1 End of Fetch Count: 1 Optimizer Cost: 4849 Notes ----- 1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers. 2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 6 ejecuciones. ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original ----------- Plan hash value: 3551373025 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 19159 | 1459K| | 4849 (1)| 00:00:59 | |* 1 | VIEW | | 19159 | 1459K| | 4849 (1)| 00:00:59 | |* 2 | WINDOW SORT PUSHED RANK| | 19159 | 954K| 2424K| 4849 (1)| 00:00:59 | | 3 | HASH GROUP BY | | 19159 | 954K| 2424K| 4849 (1)| 00:00:59 | |* 4 | HASH JOIN | | 19159 | 954K| | 4354 (1)| 00:00:53 | |* 5 | HASH JOIN | | 19374 | 586K| | 3157 (1)| 00:00:38 | |* 6 | TABLE ACCESS FULL | TIMES | 91 | 1365 | | 19 (0)| 00:00:01 | | 7 | TABLE ACCESS FULL | SALES | 1472K| 22M| | 3134 (1)| 00:00:38 | | 8 | TABLE ACCESS FULL | CUSTOMERS | 162K| 3175K| | 1197 (1)| 00:00:15 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RANK_WITHIN_QUARTER"<16) 2 - filter(RANK() OVER ( PARTITION BY "TIMES"."CALENDAR_QUARTER_DESC" ORDER BY SUM("AMOUNT_SOLD") DESC )<16) 4 - access("SALES"."CUST_ID"="CUSTOMERS"."CUST_ID") 5 - access("TIMES"."TIME_ID"="SALES"."TIME_ID") 6 - filter("TIMES"."CALENDAR_QUARTER_DESC"='2002-2') ------------------------------------------------------------------------------- Object ID : 6 Schema Name: SH SQL ID : 1p5p7drt7atxd SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2001-4' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16 ------------------------------------------------------------------------------- Execution Statistics ------------------------------------------------------------------------------- Parse Time (s): .000408 Elapsed Time (s): .149246 CPU Time (s): .15031 User I/O Time (s): 0 Buffer Gets: 15965 Disk Reads: 0 Direct Writes: 0 I/O Interconnect Bytes: 0 Rows Processed: 15 Fetches: 15 Executions: 1 End of Fetch Count: 1 Optimizer Cost: 4851 Notes ----- 1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers. 2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 6 ejecuciones. ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original ----------- Plan hash value: 3551373025 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 19370 | 1475K| | 4851 (1)| 00:00:59 | |* 1 | VIEW | | 19370 | 1475K| | 4851 (1)| 00:00:59 | |* 2 | WINDOW SORT PUSHED RANK| | 19370 | 964K| 2456K| 4851 (1)| 00:00:59 | | 3 | HASH GROUP BY | | 19370 | 964K| 2456K| 4851 (1)| 00:00:59 | |* 4 | HASH JOIN | | 19370 | 964K| | 4354 (1)| 00:00:53 | |* 5 | HASH JOIN | | 19587 | 592K| | 3157 (1)| 00:00:38 | |* 6 | TABLE ACCESS FULL | TIMES | 92 | 1380 | | 19 (0)| 00:00:01 | | 7 | TABLE ACCESS FULL | SALES | 1472K| 22M| | 3134 (1)| 00:00:38 | | 8 | TABLE ACCESS FULL | CUSTOMERS | 162K| 3175K| | 1197 (1)| 00:00:15 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RANK_WITHIN_QUARTER"<16) 2 - filter(RANK() OVER ( PARTITION BY "TIMES"."CALENDAR_QUARTER_DESC" ORDER BY SUM("AMOUNT_SOLD") DESC )<16) 4 - access("SALES"."CUST_ID"="CUSTOMERS"."CUST_ID") 5 - access("TIMES"."TIME_ID"="SALES"."TIME_ID") 6 - filter("TIMES"."CALENDAR_QUARTER_DESC"='2001-4') ------------------------------------------------------------------------------- Object ID : 45 Schema Name: SH SQL ID : aktx221b6sqc4 SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '1997-1' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16 ------------------------------------------------------------------------------- Execution Statistics ------------------------------------------------------------------------------- Parse Time (s): .000554 Elapsed Time (s): .142861 CPU Time (s): .142311 User I/O Time (s): 0 Buffer Gets: 15965 Disk Reads: 0 Direct Writes: 0 I/O Interconnect Bytes: 0 Rows Processed: 16 Fetches: 16 Executions: 1 End of Fetch Count: 1 Optimizer Cost: 4841 Notes ----- 1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers. 2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 6 ejecuciones. ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original ----------- Plan hash value: 3551373025 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 18948 | 1443K| | 4841 (1)| 00:00:59 | |* 1 | VIEW | | 18948 | 1443K| | 4841 (1)| 00:00:59 | |* 2 | WINDOW SORT PUSHED RANK| | 18948 | 943K| 2408K| 4841 (1)| 00:00:59 | | 3 | HASH GROUP BY | | 18948 | 943K| 2408K| 4841 (1)| 00:00:59 | |* 4 | HASH JOIN | | 18948 | 943K| | 4354 (1)| 00:00:53 | |* 5 | HASH JOIN | | 19161 | 580K| | 3157 (1)| 00:00:38 | |* 6 | TABLE ACCESS FULL | TIMES | 90 | 1350 | | 19 (0)| 00:00:01 | | 7 | TABLE ACCESS FULL | SALES | 1472K| 22M| | 3134 (1)| 00:00:38 | | 8 | TABLE ACCESS FULL | CUSTOMERS | 162K| 3175K| | 1197 (1)| 00:00:15 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RANK_WITHIN_QUARTER"<16) 2 - filter(RANK() OVER ( PARTITION BY "TIMES"."CALENDAR_QUARTER_DESC" ORDER BY SUM("AMOUNT_SOLD") DESC )<16) 4 - access("SALES"."CUST_ID"="CUSTOMERS"."CUST_ID") 5 - access("TIMES"."TIME_ID"="SALES"."TIME_ID") 6 - filter("TIMES"."CALENDAR_QUARTER_DESC"='1997-1') ------------------------------------------------------------------------------- Object ID : 13 Schema Name: SH SQL ID : 370m5yqjjcr8z SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '1997-4' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16 ------------------------------------------------------------------------------- Execution Statistics ------------------------------------------------------------------------------- Parse Time (s): .000548 Elapsed Time (s): .14213 CPU Time (s): .141692 User I/O Time (s): 0 Buffer Gets: 15965 Disk Reads: 0 Direct Writes: 0 I/O Interconnect Bytes: 0 Rows Processed: 17 Fetches: 17 Executions: 1 End of Fetch Count: 1 Optimizer Cost: 4851 Notes ----- 1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers. 2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 7 ejecuciones. ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original ----------- Plan hash value: 3551373025 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 19370 | 1475K| | 4851 (1)| 00:00:59 | |* 1 | VIEW | | 19370 | 1475K| | 4851 (1)| 00:00:59 | |* 2 | WINDOW SORT PUSHED RANK| | 19370 | 964K| 2456K| 4851 (1)| 00:00:59 | | 3 | HASH GROUP BY | | 19370 | 964K| 2456K| 4851 (1)| 00:00:59 | |* 4 | HASH JOIN | | 19370 | 964K| | 4354 (1)| 00:00:53 | |* 5 | HASH JOIN | | 19587 | 592K| | 3157 (1)| 00:00:38 | |* 6 | TABLE ACCESS FULL | TIMES | 92 | 1380 | | 19 (0)| 00:00:01 | | 7 | TABLE ACCESS FULL | SALES | 1472K| 22M| | 3134 (1)| 00:00:38 | | 8 | TABLE ACCESS FULL | CUSTOMERS | 162K| 3175K| | 1197 (1)| 00:00:15 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RANK_WITHIN_QUARTER"<16) 2 - filter(RANK() OVER ( PARTITION BY "TIMES"."CALENDAR_QUARTER_DESC" ORDER BY SUM("AMOUNT_SOLD") DESC )<16) 4 - access("SALES"."CUST_ID"="CUSTOMERS"."CUST_ID") 5 - access("TIMES"."TIME_ID"="SALES"."TIME_ID") 6 - filter("TIMES"."CALENDAR_QUARTER_DESC"='1997-4') ------------------------------------------------------------------------------- Object ID : 28 Schema Name: SH SQL ID : 83wufms0kwjb0 SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '1995-3' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16 ------------------------------------------------------------------------------- Execution Statistics ------------------------------------------------------------------------------- Parse Time (s): .000579 Elapsed Time (s): .138891 CPU Time (s): .137836 User I/O Time (s): 0 Buffer Gets: 15965 Disk Reads: 0 Direct Writes: 0 I/O Interconnect Bytes: 0 Rows Processed: 15 Fetches: 15 Executions: 1 End of Fetch Count: 1 Optimizer Cost: 4851 Notes ----- 1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers. 2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 7 ejecuciones. ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original ----------- Plan hash value: 3551373025 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 19370 | 1475K| | 4851 (1)| 00:00:59 | |* 1 | VIEW | | 19370 | 1475K| | 4851 (1)| 00:00:59 | |* 2 | WINDOW SORT PUSHED RANK| | 19370 | 964K| 2456K| 4851 (1)| 00:00:59 | | 3 | HASH GROUP BY | | 19370 | 964K| 2456K| 4851 (1)| 00:00:59 | |* 4 | HASH JOIN | | 19370 | 964K| | 4354 (1)| 00:00:53 | |* 5 | HASH JOIN | | 19587 | 592K| | 3157 (1)| 00:00:38 | |* 6 | TABLE ACCESS FULL | TIMES | 92 | 1380 | | 19 (0)| 00:00:01 | | 7 | TABLE ACCESS FULL | SALES | 1472K| 22M| | 3134 (1)| 00:00:38 | | 8 | TABLE ACCESS FULL | CUSTOMERS | 162K| 3175K| | 1197 (1)| 00:00:15 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RANK_WITHIN_QUARTER"<16) 2 - filter(RANK() OVER ( PARTITION BY "TIMES"."CALENDAR_QUARTER_DESC" ORDER BY SUM("AMOUNT_SOLD") DESC )<16) 4 - access("SALES"."CUST_ID"="CUSTOMERS"."CUST_ID") 5 - access("TIMES"."TIME_ID"="SALES"."TIME_ID") 6 - filter("TIMES"."CALENDAR_QUARTER_DESC"='1995-3') ------------------------------------------------------------------------------- Object ID : 4 Schema Name: SH SQL ID : 08kysb4nyt58n SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '1995-4' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16 ------------------------------------------------------------------------------- Execution Statistics ------------------------------------------------------------------------------- Parse Time (s): .000519 Elapsed Time (s): .132301 CPU Time (s): .132122 User I/O Time (s): 0 Buffer Gets: 15965 Disk Reads: 0 Direct Writes: 0 I/O Interconnect Bytes: 0 Rows Processed: 15 Fetches: 15 Executions: 1 End of Fetch Count: 1 Optimizer Cost: 4851 Notes ----- 1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers. 2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 7 ejecuciones. ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original ----------- Plan hash value: 3551373025 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 19370 | 1475K| | 4851 (1)| 00:00:59 | |* 1 | VIEW | | 19370 | 1475K| | 4851 (1)| 00:00:59 | |* 2 | WINDOW SORT PUSHED RANK| | 19370 | 964K| 2456K| 4851 (1)| 00:00:59 | | 3 | HASH GROUP BY | | 19370 | 964K| 2456K| 4851 (1)| 00:00:59 | |* 4 | HASH JOIN | | 19370 | 964K| | 4354 (1)| 00:00:53 | |* 5 | HASH JOIN | | 19587 | 592K| | 3157 (1)| 00:00:38 | |* 6 | TABLE ACCESS FULL | TIMES | 92 | 1380 | | 19 (0)| 00:00:01 | | 7 | TABLE ACCESS FULL | SALES | 1472K| 22M| | 3134 (1)| 00:00:38 | | 8 | TABLE ACCESS FULL | CUSTOMERS | 162K| 3175K| | 1197 (1)| 00:00:15 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RANK_WITHIN_QUARTER"<16) 2 - filter(RANK() OVER ( PARTITION BY "TIMES"."CALENDAR_QUARTER_DESC" ORDER BY SUM("AMOUNT_SOLD") DESC )<16) 4 - access("SALES"."CUST_ID"="CUSTOMERS"."CUST_ID") 5 - access("TIMES"."TIME_ID"="SALES"."TIME_ID") 6 - filter("TIMES"."CALENDAR_QUARTER_DESC"='1995-4') ------------------------------------------------------------------------------- Object ID : 39 Schema Name: SH SQL ID : 9tkfxkjugywqp SQL Text : SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers, times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2014-4' AND times.time_id = sales.time_id GROUP BY customers.cust_id, customers.cust_first_name, customers.cust_last_name, customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16 ------------------------------------------------------------------------------- Execution Statistics ------------------------------------------------------------------------------- Parse Time (s): .000518 Elapsed Time (s): .124307 CPU Time (s): .122981 User I/O Time (s): 0 Buffer Gets: 11579 Disk Reads: 0 Direct Writes: 0 I/O Interconnect Bytes: 0 Rows Processed: 0 Fetches: 0 Executions: 1 End of Fetch Count: 1 Optimizer Cost: 4851 Notes ----- 1. La sentencia se ha ejecutado en primer lugar para calentar la caché de buffers. 2. La media de las estadísticas mostradas se ha realizado sobre las siguientes 8 ejecuciones. ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original ----------- Plan hash value: 3551373025 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 19264 | 1467K| | 4851 (1)| 00:00:59 | |* 1 | VIEW | | 19264 | 1467K| | 4851 (1)| 00:00:59 | |* 2 | WINDOW SORT PUSHED RANK| | 19264 | 959K| 2440K| 4851 (1)| 00:00:59 | | 3 | HASH GROUP BY | | 19264 | 959K| 2440K| 4851 (1)| 00:00:59 | |* 4 | HASH JOIN | | 19264 | 959K| | 4354 (1)| 00:00:53 | |* 5 | HASH JOIN | | 19480 | 589K| | 3157 (1)| 00:00:38 | |* 6 | TABLE ACCESS FULL | TIMES | 92 | 1380 | | 19 (0)| 00:00:01 | | 7 | TABLE ACCESS FULL | SALES | 1472K| 22M| | 3134 (1)| 00:00:38 | | 8 | TABLE ACCESS FULL | CUSTOMERS | 162K| 3175K| | 1197 (1)| 00:00:15 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RANK_WITHIN_QUARTER"<16) 2 - filter(RANK() OVER ( PARTITION BY "TIMES"."CALENDAR_QUARTER_DESC" ORDER BY SUM("AMOUNT_SOLD") DESC )<16) 4 - access("SALES"."CUST_ID"="CUSTOMERS"."CUST_ID") 5 - access("TIMES"."TIME_ID"="SALES"."TIME_ID") 6 - filter("TIMES"."CALENDAR_QUARTER_DESC"='2014-4') ------------------------------------------------------------------------------- Object ID : 11 Schema Name: SH SQL ID : 23bdv7p1xf88s SQL Text : BEGIN dbms_application_info.set_module('SalesMovingAverage', null); END; ------------------------------------------------------------------------------- ADDITIONAL INFORMATION SECTION ------------------------------------------------------------------------------- - Tipo de sentencia SQL no soportado. ------------------------------------------------------------------------------- Object ID : 24 Schema Name: SH SQL ID : 772ank4p8pcha SQL Text : BEGIN dbms_application_info.set_module('SalesRollupByWeek', null); END; ------------------------------------------------------------------------------- ADDITIONAL INFORMATION SECTION ------------------------------------------------------------------------------- - Tipo de sentencia SQL no soportado. ------------------------------------------------------------------------------- Object ID : 25 Schema Name: SH SQL ID : 7dkpwxm9qu436 SQL Text : BEGIN dbms_application_info.set_module('ProductMonthlySalesRollup Cube', null); END; ------------------------------------------------------------------------------- ADDITIONAL INFORMATION SECTION ------------------------------------------------------------------------------- - Tipo de sentencia SQL no soportado. ------------------------------------------------------------------------------- Object ID : 27 Schema Name: SH SQL ID : 7p3m5a493ys3v SQL Text : BEGIN dbms_application_info.set_module('SalesRollupByMonth', null); END; ------------------------------------------------------------------------------- ADDITIONAL INFORMATION SECTION ------------------------------------------------------------------------------- - Tipo de sentencia SQL no soportado. ------------------------------------------------------------------------------- Object ID : 34 Schema Name: SH SQL ID : 96uvdds9xf676 SQL Text : BEGIN dbms_application_info.set_module('SalesCubeByWeek', null); END; ------------------------------------------------------------------------------- ADDITIONAL INFORMATION SECTION ------------------------------------------------------------------------------- - Tipo de sentencia SQL no soportado. ------------------------------------------------------------------------------- Object ID : 43 Schema Name: SH SQL ID : ad5f7gub3vkmb SQL Text : BEGIN dbms_application_info.set_module('WeekToWeekComparison', null); END; ------------------------------------------------------------------------------- ADDITIONAL INFORMATION SECTION ------------------------------------------------------------------------------- - Tipo de sentencia SQL no soportado. ------------------------------------------------------------------------------- Object ID : 44 Schema Name: SH SQL ID : ahagwf5568kh6 SQL Text : BEGIN dbms_application_info.set_module('SalesCubeByMonth', null); END; ------------------------------------------------------------------------------- ADDITIONAL INFORMATION SECTION ------------------------------------------------------------------------------- - Tipo de sentencia SQL no soportado. ------------------------------------------------------------------------------- Object ID : 47 Schema Name: SH SQL ID : bph8vd0ct58yz SQL Text : BEGIN dbms_application_info.set_module('TopSalesWithinWeek', null); END; ------------------------------------------------------------------------------- ADDITIONAL INFORMATION SECTION ------------------------------------------------------------------------------- - Tipo de sentencia SQL no soportado. ------------------------------------------------------------------------------- Object ID : 49 Schema Name: SH SQL ID : c860hqput21a4 SQL Text : BEGIN dbms_application_info.set_module('TopSalesWithinQuarter', null); END; ------------------------------------------------------------------------------- ADDITIONAL INFORMATION SECTION ------------------------------------------------------------------------------- - Tipo de sentencia SQL no soportado. ------------------------------------------------------------------------------- Object ID : 50 Schema Name: SH SQL ID : ccn2d3x50s9sq SQL Text : BEGIN dbms_application_info.set_module('ProductSalesRollupCube', null); END; ------------------------------------------------------------------------------- ADDITIONAL INFORMATION SECTION ------------------------------------------------------------------------------- - Tipo de sentencia SQL no soportado. ------------------------------------------------------------------------------- Object ID : 53 Schema Name: SH SQL ID : djqf09v8rzk0f SQL Text : BEGIN dbms_application_info.set_module('PeriodToPeriodComparison' , null); END; ------------------------------------------------------------------------------- ADDITIONAL INFORMATION SECTION ------------------------------------------------------------------------------- - Tipo de sentencia SQL no soportado. ------------------------------------------------------------------------------- Object ID : 54 Schema Name: SH SQL ID : dxqdy7uch79qn SQL Text : BEGIN dbms_application_info.set_module('SalesByQuarterCountry', null); END; ------------------------------------------------------------------------------- ADDITIONAL INFORMATION SECTION ------------------------------------------------------------------------------- - Tipo de sentencia SQL no soportado. ------------------------------------------------------------------------------- Object ID : 58 Schema Name: SH SQL ID : fy8v6kg0spv9f SQL Text : BEGIN dbms_application_info.set_module('SalesByWeekCountry', null); END; ------------------------------------------------------------------------------- ADDITIONAL INFORMATION SECTION ------------------------------------------------------------------------------- - Tipo de sentencia SQL no soportado. -------------------------------------------------------------------------------