Online Redo Log Switch Frequency Map Query set pages 999 lines 400 The query can also highlight abnormal churn (DML activity). Why you might ask? Well it’s important to see how frequent log switches are occurring as Oracle’s rule of thumb is to not switch more then 3 logs per hour (20 minutes of redo) at peak DML activity to prevent excessive checkpoints. Oracle Database 11g Express Edition Release 11.2.0.2.A query I find myself often running is the online redo log switch frequency map query, which queries the v$log_history/gv$log_history (for cluster databases) view and show the historical log switch frequency. If you don’t wan’t to persist this table on your database, you can drop it, as indicated bellow. In this second ordering, since we don’t specify any type of encoding, we will get an alphabetical order.įigure 2 – Ordering the table columns by an additional criteria. Just as an additional example, the query indicated bellow will order the results by our list of values and then by the WorkerName column. Note the result in the bottom, which corresponds to all the records inserted ordered by the list of values given in the decode function.įigure 1 – Running the order by query with decode function on SQL Developer. Note that the table will be ordered by the decoded values and not the original us, thus giving the desired effect.įor our testing data, we should get a result similar to figure 1 upon running the query. So, we will decode the jobs descriptives to numbers, with the order from our list of values. To do so, we will do a query with a select from table Worker, ordered by the list of values indicated before.
The function first argument is the column to which we want to apply the decoding, followed by pairs of original value – decoded value. Oracle SQL allows us to do this very easily and in a compact notation by using the decode function. Our order will be a custom one, according to the following list:Īs we can see, our custom order is not alphabetical, so we will need to translate it to something we can order. Now, we will send a query to get both columns of each record of the database, ordered by the WorkerJob column. If you want this data to be persisted after ending your session, you can send a commit statement.
Insert into Worker values ('Daniel','Nurse') Insert into Worker values ('Jack','Doctor') Insert into Worker values ('Bob','Doctor') Insert into Worker values ('Louisa','Doctor') Insert into Worker values ('Anna','Fisherman') Insert into Worker values ('Terry','Policeman') Insert into Worker values ('John','Fisherman') You should get a “Row inserted” message for each one of them. You can copy them and execute them against your database. The statements needed for our example are shown bellow. Now, we will insert some values in our table. You should receive a “Table created” message. Note that this is a simple example, so we are not going to use any table constraints. The table will be called “ Worker” and we will have two columns: “ WorkerName“, for the name of the person, and “ WorkerJob“, for the job of the person.Ĭheck the statement bellow and run it for the table to be created. This will be a dummy example representing a table with names of people and their jobs. I will be running the queries in Oracle SQL Developer, but it should also work fine for other clients.įirst, we will be creating a simple table with two columns. Then, we will order the table by one if its columns, by a defined list of values. We will start by creating a simple two column table and populate it.
#ORACLE DB DECODE HOW TO#
The objective of this post is to explain how to perform the ordering of a table in Oracle SQL, using a list of values.
0 kommentar(er)
