In one of my previous post (http://mfzahirdba.blogspot.com/2014/12/partitioning-made-easy.html) , I have mentioned about few of the partioning enhancements in Oracle 12c.
In Oracle 12c , creation of partial indexes have made easy to maintain and administer. Partial indexes helps us in saving space , index maintenance operations such as rebuilding / gathering statistics , so on.
One way is to do that is , to define the partitioned table as indexing OFF / ON at the table level and then define indivdual partions to set indexing OFF / ON. The second step is to define the partioned index as PARTIAL.
Here is the example .
For my business case , only the courses that were offered for the last three years are actively queried on ... In this case , I am setting these three partitions ( course_part_2013 , course_part_2014 , course_part_2015) to have index on the dataset.
SQL> Create table course
2 ( course_year int ,
3 course_term varchar2(1) ,
4 course_index varchar2(6) ,
5 course_code varchar2(12)
6 )
7 indexing off
8 PARTITION by range ( course_year)
9 (
10 partition course_part_2010 values less than ( 2011) indexing off,
11 partition course_part_2011 values less than ( 2012) indexing off,
12 partition course_part_2012 values less than ( 2013) indexing off,
13 partition course_part_2013 values less than ( 2014) indexing on,
14 partition course_part_2014 values less than ( 2015) indexing on,
15 partition course_part_2015 values less than ( 2016) indexing on
16 );
Table created.
Now , I am creating global PARTIAL index as below .
SQL> Create index idx_course on course(course_index) global indexing partial ;
Index created.
SQL> Select
2 table_name , partition_name , indexing
3 from user_tab_partitions
4 where table_name ='COURSE' ;
TABLE_NAME PARTITION_NAME INDE
-------------------- ------------------------------ ----
COURSE COURSE_PART_2010 OFF
COURSE COURSE_PART_2011 OFF
COURSE COURSE_PART_2012 OFF
COURSE COURSE_PART_2013 ON
COURSE COURSE_PART_2014 ON
COURSE COURSE_PART_2015 ON
6 rows selected.
SQL> Select
2 table_name , index_name , indexing
3 from user_indexes
4 where table_name ='COURSE' ;
TABLE_NAME INDEX_NAME INDEXIN
-------------------- -------------------- -------
COURSE IDX_COURSE PARTIAL
This is one of the good ones in Oracle 12c.
In the past , I have had 2 posts ( http://mfzahirdba.blogspot.com/2013/08/fetch-x-rows-in-oracle-12c-new-feature.html and http://mfzahirdba.blogspot.com/2013/07/oracle-12c-installed-new-fetch-feature.html) on Pagination feature in oracle 12c ( Fetch ... ) .
Semantically , FIRST and NEXT means the same in this SQL construct . Sounds bit odd , a'int it ?
Here is an example.
SQL> select empno,ename,hiredate from scott.emp order by hiredate;
EMPNO ENAME HIREDATE
---------- ---------- ---------
7369 SMITH 17-DEC-80
7499 ALLEN 20-FEB-81
7521 WARD 22-FEB-81
7566 JONES 02-APR-81
7698 BLAKE 01-MAY-81
7782 CLARK 09-JUN-81
7844 TURNER 08-SEP-81
7654 MARTIN 28-SEP-81
7839 KING 17-NOV-81
7900 JAMES 03-DEC-81
7902 FORD 03-DEC-81
7934 MILLER 23-JAN-82
7788 SCOTT 19-APR-87
7876 ADAMS 23-MAY-87
14 rows selected.
SQL> select empno , ename , hiredate from scott.emp order by hiredate fetch first 5 rows only;
EMPNO ENAME HIREDATE
---------- ---------- ---------
7369 SMITH 17-DEC-80
7499 ALLEN 20-FEB-81
7521 WARD 22-FEB-81
7566 JONES 02-APR-81
7698 BLAKE 01-MAY-81
SQL> select empno , ename , hiredate from scott.emp order by hiredate fetch next 5 rows only;
EMPNO ENAME HIREDATE
---------- ---------- ---------
7369 SMITH 17-DEC-80
7499 ALLEN 20-FEB-81
7521 WARD 22-FEB-81
7566 JONES 02-APR-81
7698 BLAKE 01-MAY-81
If you want to display the next n records , you will need to use OFFSET clause .
SQL> select empno , ename , hiredate from scott.emp
2 order by hiredate
3 offset 5 rows
4 fetch first 5 rows only ;
EMPNO ENAME HIREDATE
---------- ---------- ---------
7782 CLARK 09-JUN-81
7844 TURNER 08-SEP-81
7654 MARTIN 28-SEP-81
7839 KING 17-NOV-81
7900 JAMES 03-DEC-81
SQL> select empno , ename , hiredate from scott.emp
2 order by hiredate
3 offset 5 rows
4 fetch next 5 rows only ;
EMPNO ENAME HIREDATE
---------- ---------- ---------
7782 CLARK 09-JUN-81
7844 TURNER 08-SEP-81
7654 MARTIN 28-SEP-81
7839 KING 17-NOV-81
7900 JAMES 03-DEC-81
Update:
The same semantic issue is with SQL Server as well.
Examples below .
The only ( major) difference is that FETCH clause has to be used in conjunction with OFFSET clause , as can be seen from the last example below.
1> Select table_name from information_Schema.tables
2> order by table_type ;
3> GO
table_name
----------
spt_fallback_db
spt_fallback_dev
spt_fallback_usg
SpServerDiagnosticsResult
MSreplication_options
spt_monitor
spt_values
(7 rows affected)
1> Select table_name from information_Schema.tables
2> order by table_type
3> offset 2 rows
4> fetch first 3 rows only;
5> GO
table_name
----------
spt_fallback_usg
spt_fallback_dev
spt_fallback_db
(3 rows affected)
1> Select table_name from information_Schema.tables
2> order by table_type
3> offset 2 rows
4> fetch next 3 rows only;
5> GO
table_name
----------
spt_fallback_usg
spt_fallback_dev
spt_fallback_db
(3 rows affected)
1>
2>
3>
4>
5> Select table_name from information_Schema.tables
6> order by table_type
7> fetch first 3 rows only;
8> GO
Msg 153, Level 15, State 2, Server DDCVSQL02, Line 7
Invalid usage of the option first in the FETCH statement.
Comments Welcome.
