Article Categories
- All Categories
-
Data Structure
-
Networking
-
RDBMS
-
Operating System
-
Java
-
MS Excel
-
iOS
-
HTML
-
CSS
-
Android
-
Python
-
C Programming
-
C++
-
C#
-
MongoDB
-
MySQL
-
Javascript
-
PHP
-
Economics & Finance
Software & Coding Articles
Page 57 of 83
How to gather extended query execution stats in Oracle?
Problem:You want to gather extended explain plan statistics for a specific query.SolutionWe can use the GATHER_PLAN_STATISTICS hint. This hint when placed within a query at runtime, will generate extended runtime statistics. It basically have two steps.Execute the query with the gather_plan_statistics hint.Use dbms_xplan.display_cursor to display the results.ExampleSELECT /*+ gather_plan_statistics */ city, round(avg(fees)) avg_fees, min(fees) min_fees, max(fees) max_fees FROM students e, departments d, locations l WHERE e.department_id = d.department_id AND l.location_id = d.location_id GROUP BY city;OutputSouthlake 5760 4200 9000 Seattle 10701 6900 ...
Read MoreHow to change the JOIN order in Oracle?
Problem:You have a performance issue with a queryhaving JOIN conditions on multiple tables, and the Oracle optimizer is not choosing the join order you wanted.Solution:Oracle has two hints the ORDERED hint, and the LEADING hint that can be used to influence the join order used within a query.ORDERED HintYou are running a SQL to join two tables, student and departments, as you want to get the department names for each student. By placing an ORDERED hint into the query, you can see how the hint alters the execution access path.ExampleEXPLAIN PLAN FOR SELECT first_name, department_name FROM students e, departments ...
Read MoreHow to generate data with Union ALL and Insert ALL in Oracle?
Problem:You wanted to know the difference between Union ALL and Insert ALL to generate small amounts of data.Solution:I have been recently generating small amounts of data for testing some functionality and came across a couple of options in Oracle. Union All and Insert All are two options commonly used for generating small amounts of data in Oracle.The most common set operators in Oracle are UNION and UNION ALL. These operators are used to combine sets of data, even if there are no relationships between these sets.UNION creates a distinct set, whereas UNION ALL allows for duplicates. Removing duplicates can have ...
Read MoreHow to detect if a given year is a Leap year in Oracle?
Problem:You need to determine if any given year is a leap year.Solution:There are numerous ways of solving this problem.These include calculations to determine whether February 29 exists in the year, or whether March 1 is the 61st or 62nd day of the year.ExampleSELECT to_number(to_char(sysdate, 'YYYY')) Year, CASE WHEN MOD(to_number(to_char(sysdate, 'YYYY')), 400) = 0 then 'Yes' WHEN MOD(to_number(to_char(sysdate, 'YYYY')), 100) = 0 then 'No' WHEN MOD(to_number(to_char(sysdate, 'YYYY')), 4) = 0 then 'Yes' ELSE 'No' END AS "Leap Year?" FROM dual;Output2020 YesObviously 2020 is a year to forget and it ...
Read MoreHow to view storage configuration of Oracle database?
Problem:You want to find out some indroductory information about the database.Solution:Every Oracle programmer/ DBA has at one point or another during their career inherited a database someone else have already set up.You needs to find out some introductory information about the database to learn more about it.Idenitfy the host details and databsae versionExampleSELECT instance_name, host_name, version, startup_time FROM v$instanceOutputINSTANCE_NAME HOST_NAME VERSION STARTUP_TIME TESTDB ip-000-00-0-000 19.0.0.0.0 12/AUG/20Next, We will determine the ...
Read MoreHow to use Oracle aggregate function XMLAGG ?
You want to use Oracle aggregate function XMLAGG for string aggregation.?Solution:ExampleSELECT class_id, rtrim(xmlagg(xmlelement(x, first_name || ' ' || last_name , ', ') ORDER BY first_name).extract('//text()').getstringval(), ', ') AS "names" FROM students GROUP BY class_id;OutputCL_MATH ANDERSON DANIEL, MARTINEZ CHRISTOPHER, TAYLOR PAUL, THOMAS MARK, WILSON THOMAS, CL_PHY RODRIGUEZ JOSEPH, INS_CHAIRMAN SMITH JAMES, INS_STAFF BROWN MICHAEL, DAVIS RICHARD, GARCIA CHARLES, JONES WILLIAM, MILLER DAVID, INS_VP JOHNSON JOHN, WILLIAMS ROBERT, In the above statement xmlelement will create an XML element called x (name it what ...
Read MoreHow to perform string aggregation/concatenation in Oracle?
Problem Statement:You want to perform string concatenation as a comma delimited text in oracle.Solution:Oracle has few methods to perform string aggregation. The most common usuage you popularly find on internet is to convert multiple rows to a single row with a delimiter.Starting Oracle version 11.2, a new built-in function is added called listagg which performs string aggregation. The listagg function uses two parameters the first is the string column or expression we want to aggregate and the second which is an optional parameter is the delimiter to put between the strings in the aggregated result. within group is mandatory and ...
Read MoreHow to assign ranks to the query results in Oracle?
Problem Statement:You want to assign a number/rank representing their positions in the result.Solution:Oracle provides the RANK analytic function to generate a ranking number for rows in a result set. To demonstrate we will rank students by fees, from highest paid down. The following SELECT statement uses the rank function to assign these values.ExampleSELECT student_id, first_name, last_name, fees, RANK() OVER (ORDER BY fees DESC) AS rnk FROM students;Outputstudent_idfirst_namelast_namefeesrnk100SMITHJAMES240001101JOHNSONJOHN170002102WILLIAMSROBERT170002108RODRIGUEZJOSEPH120084103BROWNMICHAEL90005109WILSONTHOMAS90005110MARTINEZCHRISTOPHER82007112TAYLORPAUL78008111ANDERSONDANIEL77009113THOMASMARK690010104JONESWILLIAM600011105MILLERDAVID480012106DAVISRICHARD480012107GARCIACHARLES420014RANK behaves similar to any other analytic function, operating in a second pass over the result set once non analytic processing is ...
Read MoreHow to cache query results in Oracle?
Problem Statement:You want to improve the performance of frequently used queries.Solution:We have to use Oracle’s result cache to store the query results of frequently used SQL, so they can be retrieved quickly for future use when the same query has been executed.The result cache is new additon to Oracle 11g, which will allow us store results from often-used queries in memory for quick and easy retrieval.ExampleSELECT /*+ result_cache */ e.class_id, min_fees, max_fees FROM students e ,jobs j WHERE e.class_id = j.class_id GROUP BY e.class_id, min_fees, max_fees;To demonstrate how it is used, we will check ...
Read MoreHow to optimize the INSERT statement using direct-path insert technique in Oracle?
Problem Statement:You are performing a INSERT statement, and it is performing slower than needed. You want to optimize the INSERT statement.Solution:By using the APPEND or APPEND_VALUES hint with INSERT statement, we can significantly speed up the process of performing an insert operation on the database. Here is an example of the performance savings using the APPEND hint.SQL without OptimizationINSERT INTO students SELECT * FROM students_bkp;Output-- Output 22141998 rows created. Elapsed: 00:03:11.21 ------------------------------------------------- | Id | Operation | Name | ------------------------------------------------- | 0 | INSERT STATEMENT ...
Read More