var sql="";
for(var i=1;i<args.length;i++){
sql = sql + " " + args[i];
}
ctx.write("\n\n SQL Statement: \n"+ sql + "\n\n");
var ret = util.executeReturnListofList(sql,null);
for (var i = 0; i < ret.size(); i++) {
if (i ==0) { ctx.write('> SKIP HEADER ROW\n');continue; }
else { ctx.write('>ROW \n') };
for( var ii=0;ii<ret[i].size();ii++) {
ctx.write("\t" + ret[0][ii] + " : " + ret[i][ii] + "\n");
}
}
ctx.write('\n\n');
SQL> CREATE TABLE orders (
2 order_id NUMBER(12),
3 order_date DATE,
4 order_mode VARCHAR2(8),
5 customer_id NUMBER(6),
6 order_status NUMBER(2),
7 order_total NUMBER(8,2),
8 sales_rep_id NUMBER(6),
9 promotion_id NUMBER(6),
10 CONSTRAINT order_pk PRIMARY KEY ( order_id )
11 )
12 INDEXING OFF
13 PARTITION BY RANGE ( order_date ) (
14 PARTITION P2004 VALUES LESS THAN (TO_DATE('01-JAN-2005','DD-MON-YYYY')) INDEXING OFF,
15 PARTITION P2005 VALUES LESS THAN (TO_DATE('01-JAN-2006','DD-MON-YYYY')) INDEXING OFF,
16 PARTITION P2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','DD-MON-YYYY')) INDEXING OFF,
17 PARTITION P2007 VALUES LESS THAN (TO_DATE('01-JAN-2008','DD-MON-YYYY')) INDEXING OFF,
18 PARTITION P2008 VALUES LESS THAN (TO_DATE('01-JAN-2009','DD-MON-YYYY')) INDEXING ON,
19 PARTITION P2009 VALUES LESS THAN (TO_DATE('01-JAN-2010','DD-MON-YYYY')) INDEXING ON
20 )
21 /
Table ORDERS created.
SQL> select order_date from oe.orders sample(10);
ORDER_DATE
-------------------------------
14-SEP-06 06.03.04.763452000 AM
17-NOV-06 01.22.11.262552000 AM
12-MAR-07 08.53.54.562432000 PM
29-MAR-07 03.41.20.945676000 PM
07-JUN-07 05.18.08.883310000 AM
16-AUG-07 02.34.12.234359000 PM
10-NOV-07 04.49.25.526321000 AM
27-FEB-08 03.41.45.109654000 AM
26-JUN-08 09.19.43.190089000 PM
9 rows selected.
SQL> insert into orders select * from oe.orders;
105 rows inserted.
SQL> commit;
Commit complete.
SQL> create index order_gi1 on orders (sales_rep_id) global indexing partial;
Index ORDER_GI1 created.
SQL> create index order_li1 on orders (customer_id) local indexing partial;
Index ORDER_LI1 created.
SQL> set sqlformat ansiconsole
SQL> select partition_name,indexing from user_tab_partitions where table_name='ORDERS';
PARTITION_NAME INDEXING
P2004 OFF
P2005 OFF
P2006 OFF
P2007 OFF
P2008 ON
P2009 ON
6 rows selected.
SQL> select index_name,partition_name,status from user_ind_partitions;
INDEX_NAME PARTITION_NAME STATUS
ORDER_LI1 P2004 UNUSABLE
ORDER_LI1 P2005 UNUSABLE
ORDER_LI1 P2006 UNUSABLE
ORDER_LI1 P2007 UNUSABLE
ORDER_LI1 P2008 USABLE
ORDER_LI1 P2009 USABLE
6 rows selected.
SQL> select index_name,indexing from user_indexes;
INDEX_NAME INDEXING
ORDER_LI1 PARTIAL
ORDER_PK FULL
ORDER_GI1 PARTIAL
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
Plan hash value: 670661013
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 93 | 821 (1)| 00:00:01 | | |
| 1 | VIEW | VW_TE_2 | 2 | 186 | 821 (1)| 00:00:01 | | |
| 2 | UNION-ALL | | | | | | | |
|* 3 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| ORDERS | 1 | 93 | 1 (0)| 00:00:01 | ROWID | ROWID |
|* 4 | INDEX RANGE SCAN | ORDER_GI1 | 1 | | 1 (0)| 00:00:01 | | |
| 5 | PARTITION RANGE ITERATOR | | 1 | 93 | 820 (1)| 00:00:01 | 1 | 4 |
|* 6 | TABLE ACCESS FULL | ORDERS | 1 | 93 | 820 (1)| 00:00:01 | 1 | 4 |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("ORDERS"."ORDER_DATE">=TO_DATE(' 2008-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"ORDERS"."ORDER_DATE"<TO_DATE(' 2010-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
4 - access("SALES_REP_ID"=154)
6 - filter("SALES_REP_ID"=154)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
25 rows selected.
SQL> explain plan for select * from orders where customer_id=104;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
Plan hash value: 4090115495
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 93 | 963 (1)| 00:00:01 | | |
| 1 | VIEW | VW_TE_2 | 17 | 1581 | 963 (1)| 00:00:01 | | |
| 2 | UNION-ALL | | | | | | | |
| 3 | PARTITION RANGE ITERATOR | | 16 | 1488 | 143 (0)| 00:00:01 | 5 | 6 |
| 4 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| ORDERS | 16 | 1488 | 143 (0)| 00:00:01 | 5 | 6 |
|* 5 | INDEX RANGE SCAN | ORDER_LI1 | 16 | | 1 (0)| 00:00:01 | 5 | 6 |
| 6 | PARTITION RANGE ITERATOR | | 1 | 93 | 820 (1)| 00:00:01 | 1 | 4 |
|* 7 | TABLE ACCESS FULL | ORDERS | 1 | 93 | 820 (1)| 00:00:01 | 1 | 4 |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("CUSTOMER_ID"=104)
7 - filter("CUSTOMER_ID"=104)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
24 rows selected.
