#StackBounty: #oracle #oracle-12c #unpivot How can I prevent UNPIVOT from being transformed into UNION ALL?

Bounty: 200

I have a somewhat complex Oracle query which is taking about half an hour to complete. If I take the slow part of the query and run it separately it finishes in a few seconds. Here’s is a screenshot of the SQL Monitor report for the isolated query:

ok plan

Here is the same logic when run as part of the full query:

bad plan

The colors correspond to the same tables in both screenshots. For the slow query, Oracle is doing a MERGE JOIN between two tables that do not have an equality condition in the JOIN. As a result of that, about 150 million intermediate rows are unnecessarily processed.

I am able to work around this problem with query hints or rewrites, but I want to understand as much of the root cause as I can so that I can avoid this problem in the future and possibly submit a bug report to Oracle. Every time I get the bad plan the UNPIVOT in the query text is transformed into a UNION ALL in the plan. To further investigate I would like to prevent that query transformation from happening. I have been unable to find a name for this transformation. I also have not been able to find a query hint or underscore parameter that will prevent it. I’m testing on a development server so anything goes.

Is there anything that I can do to prevent the query transformation of the UNPIVOT to UNION ALL? I am on Oracle 12.1.0.2.


Get this bounty!!!

#StackBounty: #oracle #ado.net #connection-string #oracleclient #system.data.oracleclient How to connect with System.Data.OracleClient …

Bounty: 200

With Oracle SQL Developer I can put / -character to Username and leave password empty and I get connected. I have OP$MYWINDOWSUSERNAME user created in database.

EDIT: SQL Developer does not work if I check OS Authentication-checkbox (empties and disables username + pwd). Moreover Preferences->Database->Advanced->Use Oracle Client is unchecked so I guess what SQL Developer does or doesn’t has very little to do with my System.Data.OracleClient.OracleConnection problem.

However when I try to form connection string like this:

string.Format("Data Source={0}; user id=/;password=;Integrated Security=yes", dbName);

I get ORA-01017: invalid username/password: logon denied

with

string.Format("Data Source={0}; user id=/;password=;Integrated Security=no", dbName);

I get ORA-01005.

With

string.Format("Data Source={0};Integrated Security=yes", dbName);

I get ORA-01017: invalid username/password: logon denied

With

string.Format("Data Source={0}; user id=/;", dbName);

I get ORA-01005

With

string.Format("Data Source={0};User Id=/;Integrated Security=yes;", dbName);

I get ORA-01017

Both OracleConnection in my program and Oracle SQL Developer work when I specify Username and password.


Get this bounty!!!

Convert Comma separated String to Rows in Oracle SQL

Many times we need to convert a comma separated list of terms in a single string and convert it rows in SQL query.

for example

 India, USA, Russia, Malaysia, Mexico

Needs to be converted to:

 Country
 India
 USA
 Russia
 Malaysia
 Mexico

The following SQL script can help in this:

just replace the required values with your string and your delimiter.

Apache Commons DbUtils Mini Wrapper

This is a very small DB Connector code in Java as a wrapper class to Apache DBUtils.

The Commons DbUtils library is a small set of classes designed to make working with JDBC easier. JDBC resource cleanup code is mundane, error prone work so these classes abstract out all of the cleanup tasks from your code leaving you with what you really wanted to do with JDBC in the first place: query and update data.

Some of the advantages of using DbUtils are:

  • No possibility for resource leaks. Correct JDBC coding isn’t difficult but it is time-consuming and tedious. This often leads to connection leaks that may be difficult to track down.
  • Cleaner, clearer persistence code. The amount of code needed to persist data in a database is drastically reduced. The remaining code clearly expresses your intention without being cluttered with resource cleanup.
  • Automatically populate Java Bean properties from Result Sets. You don’t need to manually copy column values into bean instances by calling setter methods. Each row of the Result Set can be represented by one fully populated bean instance.

DbUtils is designed to be:

  • Small – you should be able to understand the whole package in a short amount of time.
  • Transparent – DbUtils doesn’t do any magic behind the scenes. You give it a query, it executes it and cleans up for you.
  • Fast – You don’t need to create a million temporary objects to work with DbUtils.

DbUtils is not:

  • An Object/Relational bridge – there are plenty of good O/R tools already. DbUtils is for developers looking to use JDBC without all the mundane pieces.
  • A Data Access Object (DAO) framework – DbUtils can be used to build a DAO framework though.
  • An object oriented abstraction of general database objects like a Table, Column, or Primary Key.
  • A heavyweight framework of any kind – the goal here is to be a straightforward and easy to use JDBC helper library.

Wrapper:

System Design Interview Prep Material

System design is a very broad topic. Even a software engineer with many years of working experience at top IT company may not be an expert on system design. If you want to become an expert, you need to read many books, articles, and solve real large scale system design problems. This repository only teaches you to handle the system design interview with a systematic approach in a short time. You can dive into each topic if you have time. Of course, welcome to add your thoughts!

Table of Contents

System Design Interview Tips:

  • Clarify the constraints and identify the user cases Spend a few minutes questioning the interviewer and agreeing on the scope of the system. Remember to make sure you know all the requirements the interviewer didn’t tell your about in the beginning. User cases indicate the main functions of the system, and constraints list the scale of the system such as requests per second, requests types, data written per second, data read per second.
  • High-level architecture design Sketch the important components and the connections between them, but don’t go into some details. Usually, a scalable system includes web server (load balancer), service (service partition), database (master/slave database cluster plug cache).
  • Component design For each component, you need to write the specific APIs for each component. You may need to finish the detailed OOD design for a particular function. You may also need to design the database schema for the database.

Basic Knowledge about System Design:

Here are some articles about system design related topics.

Of course, if you want to dive into system related topics, here is a good collection of reading list about services-engineering, and a good collection of material about distributed systems.

Company Engineering Blogs:

If you are going to have an onsite with a company, you should read their engineering blog.

Products and Systems:

The following papers/articles/slides can help you to understand the general design idea of different real products and systems.

Hot Questions and Reference:

There are some good references for each question. The references here are slides and articles.
Design a CDN network Reference:

Design a Google document system Reference:

Design a random ID generation system Reference:

Design a key-value database Reference:

Design the Facebook news feed function Reference:

Design the Facebook timeline function Reference:

Design a function to return the top k requests during past time interval Reference:

Design an online multiplayer card game Reference:

Design a graph search function Reference:

Design a picture sharing system Reference:

Design a search engine Reference:

Design a recommendition system Reference:

Design a tinyurl system Reference:

Design a garbage collection system Reference:

Design a scalable web crawling system Reference:

Design the Facebook chat function Reference:

Design a trending topic system Reference:

Design a cache system Reference:

Good Books:

Object Oriented Design:

Tips for OOD Interview

Clarify the scenario, write out user cases Use case is a description of sequences of events that, taken together, lead to a system doing something useful. Who is going to use it and how they are going to use it. The system may be very simple or very complicated. Special system requirements such as multi-threading, read or write oriented.
Define objects Map identity to class: one scenario for one class, each core object in this scenario for one class. Consider the relationships among classes: certain class must have unique instance, one object has many other objects (composition), one object is another object (inheritance). Identify attributes for each class: change noun to variable and action to methods. Use design patterns such that it can be reused in multiple applications.

Useful Websites

Original Source

HackerRank: CodeWhiz.java March 2016: Maximum and Minimum

Problem

The locked code in your editor passes array A (of size N) and index i to the print method, whose try block attempts to print element A[i]; if i is Out-of-Range, an Array Index Out Of Bounds Exception is thrown.

Complete the code in your editor so that it prints the maximum and minimum elements in array A—regardless of whether or not an exception is thrown.

Input Format

The first line contains an integer, N, the number of elements in A.
The second line contains N space-separated integers describing A.
The third line contains an index, i, to be accessed.

Note: Input from stdin handled by the locked code in the editor.

Constraints

  • 1N100
  • 1000Aj1000 where 1jN

Output Format

The try block will print the value accessed at A[i]; if an Exception is thrown, it will be printed by the locked code in your editor.
You must print the respective maximum and minimum values in array A as a single pair of space-separated integers on a new line—regardless of whether an exception is thrown.

Note: Observe that your max/min values may print on either the first or second line, depending on whether or not an Exception was thrown!

Sample Input 0

12
-12 0 1 -899 23 45 96 10 75 23 0 33
100

Sample Output 0

96 -899
java.lang.ArrayIndexOutOfBoundsException

Sample Input 1

10
4 908 -05 445 -208 325 -2 -718 863 400
9   

Sample Output 1

400
908 -718

Explanation

Sample 0:
N=12, i=100, maximum(A)=96, and minimum(A)=899
A‘s indices range from 0 to 11, so attempting to access index 100 throws an Exception. The maximum and minimum values in the array are printed on a new line as a pair of space-separated integers. The program’s control flow then returns to main where the the Exception is caught and printed on a new line.

Sample 1:
N=10, i=9, maximum(A)=908, and minimum(A)=718
A‘s indices range from 0 to 9, so an attempt to access index 9 will be successful and the value at A[9] (i.e.: 400) is printed on a new line. The program’s control flow then proceeds to print the maximum and minimum values in A as a pair of space-separated integers on a new line.

Solution

How to find nth highest integer value from a table?

For a Sample table

name value
a 1
b 3
c 5
d 2
e 0
f 7

If we want to find the nth highest integer value, then the SQL would be:

  1. SELECT MIN(value) FROM (SELECT * FROM table ORDER BY value DESC)
  2. WHERE ROWNUM <=2

Oracle ADF Features

Oracle Application Development Framework (Oracle ADF) is an advanced Rapid Application Development(RAD) Framework for J2EE applications mainly targeted for B2B Applications.

It is a proprietary software maintained by Oracle in 3 flavors:

Features-

  • RAD Framework with most commonly used components of any Front End Application available as inbuilt feature
  • Model-View-Controller (MVC) design pattern and offers an integrated solution that covers all the layers of this architecture with solution to such areas as:
    • ORM and Data Persistence
    • Reusable Task-flow approach,
    • Rich Web user interface elements,
    • Data binding to UI,
    • Security
    • Customization via sandboxes etc
  • Separation of Business Logic implementation details, via metadata and use of this metadata-driven architecture enables developers to focus on the business logic and user experience.
  • Drag-n-Drop features, via JDeveloper IDE, provides the ease to use and customize.
  • ADF Skin Editor: New ADF Skin editor for visually creating and modifying the appearance of ADF Faces applications.(JDeveloper 11G and above)
  • JSF 2.0: JSF 2.0 support in the IDE and for ADF applications. (JDev 11g R2 release)
  • ADF Faces and Data Visualization Tools (DVT) support touch gestures for interactivity on the touch-enabled devices, such as touch support for drag and drop, tooltips, data tips, and context menus.
  • ADF Faces and DVT components are certified on iPad. Additionally, ADF DVT Graph and Gauge components support rendering in HTML5.
  • MDS is metadata service that is used to store metadata information. When you use ADF, it generates lot of internal files about its own components and DB binding details. All these are stored internally by ADF into MDS.
  • ADS (Active Data service)- This is used in ADF to push any data changes automatically from the back end into the portal layer. This is different from Ajax which is used to send request from UI to data service layer.

Reset Sequence in Oracle SQL back to 0

Some times we need to reset the sequence values in the database back to 0.
Here is a small procedure to reset any sequence.

create or replace
procedure reset_sequence(p_seq in varchar2)
is
l_value number;
begin
-- Select the next value of the sequence

execute immediate
'select ' || p_seq ||
'.nextval from dual' INTO l_value;

-- Set a negative increment for the sequence,
-- with value = the current value of the sequence

execute immediate
'alter sequence ' || p_seq ||
' increment by -' || l_value || ' minvalue 0';

-- Select once from the sequence, to
-- take its current value back to 0

execute immediate
'select ' || p_seq ||
'.nextval from dual' INTO l_value;

-- Set the increment back to 1

execute immediate
'alter sequence ' || p_seq ||
' increment by 1 minvalue 0';
end;
/

Whatever sequence you wish to reset, call the procedure as:

begin
    reset_sequence('SEQ_1');
    reset_sequence('SEQ_2');
    reset_sequence('SEQ_3');
    reset_sequence('SEQ_4');
end;
/