Today we”ll present to you another optimization method – partitioning, that is done before the real writing of the SQL queries. This is mostly done with a script that is launched at the beginning of the development, by the DBA or by the programmer.
Using partition
The partitioning of a table supposes physical division of a table, as it is stored on the disk, following some criteria. For example, if a table has 1 million records and one column named EFFDT, but for all record from the table we have only two different dates, into the whole table, then the table could be divided into two partitions by the two dates. When we write a query, if we know what is the date for the results, we can put one supplementary condition in the WHERE clause. In consequence, ORACLE will seek the results only into one partition, as a single, different table. For the queries that use the partitioned tables, we have to try each time to use the partitions into the WHERE clause. If the predicates (WHERE clause) do not use the partitioning advantage, this will force ORACLE (in 90% of situations) do a complete operation of the PARTITION RANGE ALL called – which means that all the partitions of the table will be covered, even if this is not necessary. In this situation, ORACLE will take much more time to read from a partitioned table than from unpartitioned one, for the same volumetric.
Practically, we lose the partitioning advantage if we get through the all partitions. For example, see the anterior article, where we wanted to find a MAX:
SELECT FROM (SELECT T.* , MAX(EFFDT) OVER (PARTITION BY ID) MAXDT FROM TABLE) WHERE EFFDT = MAXDT
The partitioning method is very strong and useful, making possible a very important decrease of the execution time, even for the tables with a very big number of records.
No Comments