1 / 14

Oracle Features -Partition Tables -External Tables

Oracle Features -Partition Tables -External Tables . By G.Gopi 25 December 2010 Saturday. Partition Tables. What? Decompose table into smaller and more manageable pieces. Why? To access very large Tables. How?

hertz
Download Presentation

Oracle Features -Partition Tables -External Tables

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Oracle Features -Partition Tables -External Tables By G.Gopi 25 December 2010 Saturday

  2. Partition Tables • What? Decompose table into smaller and more manageable pieces. • Why? To access very large Tables. • How? SQL queries and DML statements do not need to be modified.

  3. Advantages • Partitioning enables data management operations such data loads, index creation and rebuilding, and backup/recovery at the partition level, rather than on the entire table. This results in significantly reduced times for these operations. • import / export can be done at the " Partition Level". • Faster access of data. • Partitions work independent of the other partitions. • Very easy to use.

  4. Types • RANGE Partitioning • Based on the " Range of Column" values. • Each partition is defined by a " Partition Bound" (non inclusive). Ex: • CREATE TABLE COMPANY (EMPID NUMBER(10) NOT NULL,EMPNAME       VARCHAR2(200) NOT NULL,JOINING_DATE    DATE NOT NULL)PARTITION BY RANGE (JOINING_DATE) (PARTITION yr0 VALUES LESS THAN (TO_DATE('01-JAN-2007','DD-MON-YYYY'))TABLESPACE part1,PARTITION yr7 VALUES LESS THAN (TO_DATE('01-JAN-2008','DD-MON-YYYY'))TABLESPACE part2,PARTITION yr8 VALUES LESS THAN (TO_DATE('01-JAN-2009','DD-MON-YYYY'))TABLESPACE part3,PARTITION yr9 VALUES LESS THAN (MAXVALUE) TABLESPACE part4);

  5. Hash partitioning • Performance and manageability reasons. • Rows are mapped into partitions based on a hash value of the partitioning key. • EX: CREATE TABLE products      (partno NUMBER,       description VARCHAR2 (60))   PARTITION BY HASH (partno) PARTITIONS 4 STORE IN (tab1, tab2, tab3, tab4);

  6. List Partitioning • You can specify a list of discrete values (explicit control over rows). • Unordered and unrelated sets of data to be grouped. • Ex: Create table customers ( custcode number(5),                   Name varchar2(20),               Address varchar2(10,2),                   City varchar2(20),                  Bal number(10,2))      Partition by list (city), Partition north_India values (‘DELHI’,’CHANDIGARH’), Partition east_India values (‘KOLKOTA’,’PATNA’), Partition south_India values (‘HYDERABAD’,’BANGALORE’, ’CHENNAI’), Partition west_India values (‘BOMBAY’,’GOA’);

  7. Composite Partitioning • Using partitions and sub partitions. • Uses Range and Hash. • EX: CREATE TABLE orders( ord NUMBER, orderdate DATE, prod NUMBER, quantity NUMBER) PARTITION BY RANGE(orderdate) SUBPARTITION BY HASH(prod) SUBPARTITIONS 4 STORE IN(ts1, ts2, ts3, ts4) ( PARTITION q1 VALUES LESS THAN (TO_DATE('01-APR-2009', 'DD-MON-YYYY')), PARTITION q2 VALUES LESS THAN (TO_DATE('01-JUL-2009', 'DD-MON-YYYY')), PARTITION q3 VALUES LESS THAN (TO_DATE('01-OCT-2009', 'DD-MON-YYYY')), PARTITION q4 VALUES LESS THAN (MAXVALUE) );

  8. How to Alter • alter table sales add partition p6 values less than (1996); • alter table customers add partition central_India values (‘NELLORE’,’ANDHRA’); • alter table sales drop partition p5; • alter table sales truncate partition p5;

  9. External Tables

  10. External Tables • The external tables feature is a complement to existing SQL*Loader functionality. • External tables allow Oracle to query data that is stored outside the database in flat files. • No DML can be performed on external tables but they can be used for query, join and sort operations. Views can be created against external tables.

  11. Creating External Table • Create a directory and grant access to it. Ex: create directory load_src as '/usr/apps/datafiles'; GRANT READ ON DIRECTORY load_src TO user1; • Put the external table's data file in the data directory. • Ex: employee.csv. 0001,gopi,ggopi82@gmail.com 0002,Kumar,kumar@hotmail.com 0003,ganesh,ganesh@yahoo.com 0004,havish,sarma@buddy.com

  12. Creating External Table • Create table script Create table employee ( id varchar2(20), name varchar2(100), email varchar2(100) ) organization external ( default directory load_src access parameters ( record delimited by newline fields terminated by ‘,’ ) location (‘employee.csv’) );

  13. Note • The CREATE TABLE statement will succeed even if the external data file you specify doesn't actually exist • Oracle used the ORACLE_LOADER driver to process the file • The log, bad, discard files will be written to the default directory with default file names.

  14. Any Q?

More Related