Oracle Magazine, September/October 2017
DBA PERFORMANCE MULTICOLUMN LIST PARTITIONING Now consider another list partition case Suppose the bank wants a different strategy for partitioning the ACCOUNTS table on not one but two columns STATE_ CODE which contains the two letter abbreviation for US states and ACC_ TYPE which shows the type of the account such as savings checking money market and so on Because possible values in both columns are discrete list partitioning is the only choice But how can you use both columns as partition keys Remember you have to use both columns as partition keys not use one column for a partition and the other for a subpartition The partition and subpartition combination would make a composite partitioned table not just a partitioned table and eliminate the possibility ORACLE MAGAZINE SEPTEMBER OCTOBER 2017 85 of composite partitioning later In Oracle Database 12c Release 2 you can create a list partitioned table with two or more columns as partition keys Listing 2 shows the SQL statements to create the table Note the two columns in the PARTITION BY clause Code Listing 2 List partitioning with two columns create table accounts acc_ no number acc_ type varchar2 1 state_ code varchar2 2 acc_ balance number partition by list state_ code acc_ type partition ct_ s values CT S
You must have JavaScript enabled to view digital editions.
