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 More

Problem:You have a performance issue with a queryhaving JOIN conditions on multiple tables, and you wish to override the join type by placing the appropriate hint in the query.Solution:There are three possible types of joins: nested loops, hash, and sort merge.Nested Loops Join Hint:  To invoke a nested loops join, use the USE_NL hintExampleEXPLAIN PLAN FOR SELECT /*+ use_nl(students, departments)  */  first_name, department_name   FROM students e, departments d  WHERE e.department_id = d.department_id;   SELECT * FROM table(DBMS_XPLAN.DISPLAY (FORMAT=>'BASIC'));Output---------------------------------------------------------- | Id  | Operation                    | Name             ... Read More

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 More

Problem:You wanted to generate a data model from data dictionary tables in OracleSolution:The Oracle data dictionary is a collection of tables and related views that enable us to view the structure of the Oracle database. By querying these tables and views, we can obtain information about every object and every user of the database.IntroductionThe data dictionary is packaged with a series of views owned by the SYS user. These views, known as static data dictionary views, present information contained in tables that are updated when Oracle processes a Data Definition Language (DDL) statement.There is a second set of views known ... Read More

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 More

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 More

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 More

You want to use Oracle aggregate function XMLAGG for string aggregation.?Solution:You would like to include calculations based on preceding and following rows in the result set.Oracle supports the LAG and LEAD analytical functions to provide access to multiple rows in a table, utilizing preceding or following logic and you won’t need to resort to joining the source data to itself. To demonstrate the usage we will use the students data.The LAG function can be used to see which student/s joining followed another, and also to calculate the elapsed time between joining.SQL: Identify the student joining informationExampleSELECT first_name,       ... Read More

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 More

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 More