1. Materialized View on Prebuilt Table : 247 pageviews
2. OPEN RESETLOGS without really doing a Recovery : 216 pageviews
3. AUTOEXTEND ON Next Size : 198 pageviews
.
.
.
I am an Oracle Database Specialist in Singapore.
Please note that this site uses cookies.
SQL> -- Query for Total Sales for PROD_ID=5
SQL> select sum(l.quantity), sum(l.quantity*l.price)
2 from sales_lines l, sales s
3 where
4 -- join code is used to validate that the sale_id is legitimate
5 l.sale_id = s.sale_id
6 and l.prod_id=5
7 and l.sale_id between 25000 and 35000
8 /
SUM(L.QUANTITY) SUM(L.QUANTITY*L.PRICE)
--------------- -----------------------
2441786 48892118.9
Execution Plan
----------------------------------------------------------
Plan hash value: 146457679
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 139 (0)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 39 | | |
| 2 | NESTED LOOPS | | 984 | 38376 | 139 (0)| 00:00:02 |
|* 3 | TABLE ACCESS BY INDEX ROWID| SALES_LINES | 984 | 33456 | 139 (0)| 00:00:02 |
|* 4 | INDEX RANGE SCAN | SALES_LINES_UK | 10000 | | 24 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | SALES_UK | 1 | 5 | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("L"."PROD_ID"=5)
4 - access("L"."SALE_ID">=25000 AND "L"."SALE_ID"<=35000)
5 - access("L"."SALE_ID"="S"."SALE_ID")
filter("S"."SALE_ID"<=35000 AND "S"."SALE_ID">=25000)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
281 consistent gets
0 physical reads
0 redo size
523 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> REM REM ######################################
SQL> REM Now add the Constraint Definitions !
SQL> alter table SALES add constraint SALES_PK primary key (sale_id);
Table altered.
SQL> alter table SALES_LINES add constraint SALES_LINES_FK foreign key (sale_id) references SALES (sale_id);
Table altered.
SQL> REM REM ######################################
SQL> select sum(l.quantity), sum(l.quantity*l.price)
2 from sales_lines l, sales s
3 where
4 -- join code is used to validate that the sale_id is legitimate
5 l.sale_id = s.sale_id
6 and l.prod_id=5
7 and l.sale_id between 25000 and 35000
8 /
SUM(L.QUANTITY) SUM(L.QUANTITY*L.PRICE)
--------------- -----------------------
2441786 48892118.9
Execution Plan
----------------------------------------------------------
Plan hash value: 2517766180
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 34 | 139 (0)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 34 | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| SALES_LINES | 984 | 33456 | 139 (0)| 00:00:02 |
|* 3 | INDEX RANGE SCAN | SALES_LINES_UK | 10000 | | 24 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("L"."PROD_ID"=5)
3 - access("L"."SALE_ID">=25000 AND "L"."SALE_ID"<=35000)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
139 consistent gets
0 physical reads
0 redo size
523 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed