Development

Ancestor Worship

Greg Pavlik - Sat, 2015-08-08 08:25
Some profound lessons in how to be human that we can learn from our Confucian friends


Fascinating Lives

Greg Pavlik - Fri, 2015-08-07 18:38
There is something, I think, admirable in a quiet life: care for family, constructive participation in community, hard work. But there are times and places (perhaps all times, but not all places?) where simply attending to the simple things of life becomes a kind of impossibility: whether for psychological or moral reasons. I was reflecting on two persons recently who have struck me by not only their intellectual genius but also by the sheer force by which they pushed against the norm, one for reasons of psychology and one for reasons of morality.

Yukio Mishima: narcissist, political fanatic, suicide. And one of Japan's greatest novelists. I recently completed the Sea of Fertility tetralogy, which traces the life of Shigekuni Honda from youth to retirement as a wealthy attorney, centered around what Honda believes are the successive reincarnations of his friend Kiyoaki Matsugae: as a young rightist, a Thai princess and an orphan. The most powerful of the four novels, in my opinion is the second: Runaway Horses. The book seems to rebuke the militant nationalism of Japanese reactionaries, though ironically enough Mishima himself ends his own life under the banner of a similar ideology. Mishima's fascinating portrait of an inherent dark side of youth - a taming of a deep inhumanism - so to speak, comes through almost all the novels, but most strongly in the last. This echoes a theme he developed in The Sailor Who Fell From Grace with the Sea, though I can think of few works that more strongly explore this theme than the Lord of the Flies. In any case, Mishima is masterful in exploring aberrant developmental psychology - even as he, himself, seems to have been stricken with his own disordered personality.

Maria Skobtsova: atheist, symbolist poet, Bolshevik revolutionary - and a renegade nun arrested for helping Jews in Paris by the Gestapo, she allegedly died by taking the place of a Jewish woman being sent to death. Jim Forrest provides a useful overview of her life - unlikely most lives of a Christian saints, this is no hagiography: it is a straightforward story of life. At the same time, we see a life transformed by a dawning realization that self-denial is a path to transformation -

"The way to God lies through love of people. At the Last Judgment I shall not be asked whether I was successful in my ascetic exercises, nor how many bows and prostrations I made. Instead I shall be asked did I feed the hungry, clothe the naked, visit the sick and the prisoners. That is all I shall be asked. About every poor, hungry and imprisoned person the Savior says ‘I': ‘I was hungry and thirsty, I was sick and in prison.’ To think that he puts an equal sign between himself and anyone in need. . . . I always knew it, but now it has somehow penetrated to my sinews. It fills me with awe."

And despite a life dedicated to service, she remained an acute intellectual, a characteristic of so many Russian emigres in Paris. This too reflected her view that redemption and suffering where intertwined - my favorite piece On the Imitation of the Mother of God
- draws this out beautifully.


Oracle ADD_MONTHS Function with Examples

Complete IT Professional - Thu, 2015-08-06 06:00

Oracle ADD_MONTHS Function with ExamplesIn this article, I’ll explain how the very useful ADD_MONTHS function works and use some examples.

Purpose of the Oracle ADD_MONTHS Function

The purpose of the Oracle ADD_MONTHS function is to add a specified number of months to a date, and return another month.

 

Syntax

The syntax of the ADD_MONTHS function is:

ADD_MONTHS ( input_date, number_months )

The function returns a DATE value.

 

Parameters

The parameters of the ADD_MONTHS function are:

  • input_date (mandatory): This is the starting date, or the date that you will add a number of months to.
  • number_months (mandatory): This is an integer value that represents the number of months to add to input_date.

Some things to note about this function:

  • The input_date can be any DATETIME value, or a value that can be converted to DATE (such as a VARCHAR2 formatted as a date).
  • The number_months can be any integer value, or a value that can be converted to an integer (such as a VARCHAR2 formatted as a whole number).
  • The return type is always DATE, even if the data type supplied for input_date is not a DATE.

 

What If The Number Of Days Are Different?

This is a valid question. What would happen if you try to add to a month using ADD_MONTHS, with the original date being at the end of the month, and the resulting month doesn’t have that day?

Well, the function would return the last day of the resulting month.

For example, adding 3 months to January 31 will return April 30 (because April only has 30 days).

 

Is There An Oracle ADD_DAYS Function Like ADD_MONTHS?

No, there is no ADD_DAYS function in Oracle.

However, you can easily add or subtract a day from a date by just adding the number to it.

For example, to add 3 days to today’s date:

SELECT SYSDATE + 1 FROM dual;

To subtract 7 days from today’s date:

SELECT SYSDATE - 7 FROM dual;

There doesn’t need to be an extra function to do this.

 

Is There An Oracle ADD_YEARS Function Like ADD_MONTHS?

No, there is also no ADD_YEARS function in Oracle.

The good news is you can use ADD_MONTHS with a number_months parameter of 12, to add a single year. You can use multiples of 12 to add more years.

Also, you can use negative numbers to subtract years (such as -12).

See the Examples section below for more details.

 

Can Oracle ADD_MONTHS Subtract Months?

Yes, you can subtract months using the ADD_MONTHS function by simply using a negative number as the number_months parameter.

See the Examples section below for more details.

 

How Do You Get The First Day Of The Month Using Oracle ADD_MONTHS?

There are several ways to get the first day of the month, and one is using ADD_MONTHS.

SELECT TRUNC(ADD_MONTHS(LAST_DAY(SYSDATE)+1,-1) FROM dual;

You can also use the TRUNC function by itself:

SELECT TRUNC(SYSDATE,'MM') FROM dual;

 

Examples of the Oracle ADD_MONTHS Function

Here are some examples of the ADD_MONTHS function. I find that examples are the best way for me to learn about code, even with the explanation above.

Example 1

This example shows how to add one month to the current date.

SELECT SYSDATE,
ADD_MONTHS(SYSDATE, 1) as ADD_MONTH_RESULT
FROM dual;

Result:

SYSDATEADD_MONTH_RESULT06/AUG/201506/SEP/2015

The ADD_MONTH_RESULT shows that one month has been added to the date.

 

Example 2

This example shows how to add six months to the current date.

SELECT SYSDATE,
ADD_MONTHS(SYSDATE, 6) as ADD_MONTH_RESULT
FROM dual;

Result:

SYSDATEADD_MONTH_RESULT06/AUG/201506/FEB/2016

The result is six months added to the current date, which results in Feb 6, 2016.

 

Example 3

This example shows how to add a larger number of months.

SELECT SYSDATE,
ADD_MONTHS(SYSDATE, 41) as ADD_MONTH_RESULT
FROM dual;

Result

SYSDATEADD_MONTH_RESULT06/AUG/201506/JAN/2019

The result is in January 2019, which is 41 months into the future.

 

Example 4

This example shows how to subtract months by using a negative number

SELECT SYSDATE,
ADD_MONTHS(SYSDATE, -6) as ADD_MONTH_RESULT
FROM dual;

Result

SYSDATEADD_MONTH_RESULT06/AUG/201506/FEB/2015

The result is in February 2015, which is 6 months before the SYSDATE.

 

Example 5

This example shows the use of a number inside quotes, which is actually a CHAR but should get treated like a numeric value.

SELECT SYSDATE,
ADD_MONTHS(SYSDATE, '5') as ADD_MONTH_RESULT
FROM dual;

Result:

SYSDATEADD_MONTH_RESULT06/AUG/201506/JAN/2016

The result is a date in January 2016, which is five months into the future.

 

Example 6

This example shows a text value inside the number_months parameter.

SELECT SYSDATE,
ADD_MONTHS(SYSDATE, 'Six') as ADD_MONTH_RESULT
FROM dual;

Result:

ORA-01722: invalid number
01722. 00000 -  "invalid number"

This is because I used the value of “Six”, which isn’t actually a number.

 

Example 7

This example uses a specific date as the input date, instead of SYSDATE.

SELECT TO_DATE('08-JUL-2015') as INPUT_DATE,
ADD_MONTHS(TO_DATE('08-JUL-2015'), 1) as ADD_MONTH_RESULT
FROM dual;

Result:

SYSDATEADD_MONTH_RESULT08/JUL/201508/AUG/2015

As you can see, a single month is added to the specified date.

 

Example 8

This example uses a decimal number as the number of months to add.

SELECT SYSDATE,
ADD_MONTHS(SYSDATE, 2.3) as ADD_MONTH_RESULT_LOW,
ADD_MONTHS(SYSDATE, 2.9) as ADD_MONTH_RESULT_HIGH
FROM dual;

Result:

SYSDATEADD_MONTH_RESULT_LOWADD_MONTH_RESULT_HIGH06/AUG/201506/OCT/201506/OCT/2015

In both cases, the value is the same. This is because the decimal value has been rounded down to 2, even if the 2.9 would normally be rounded up to 3.

 

Example 9

This example shows how you can add a year to a date.

SELECT SYSDATE,
ADD_MONTHS(SYSDATE, 12) as ADD_MONTH_RESULT
FROM dual;

Result:

SYSDATEADD_MONTH_RESULT06/AUG/201506/AUG/2016

The result is a date one year into the future.

 

Example 10

This example shows how to find the first day of the month using the ADD_MONTHS function.

SELECT SYSDATE,
TRUNC(ADD_MONTHS(LAST_DAY(SYSDATE)+1,-1)) as FIRST_DAY
FROM dual;

Result:

SYSDATEADD_MONTH_RESULT06/AUG/201501/AUG/2016

As you can see, the result is the first day of the month that was provided.

 

Similar Functions

Some functions which are similar to the ADD_MONTHS function are:

  • TRUNC – Truncates a datetime into a date format.
  • LAST_DAY – Returns the date of the last day of the specified month.

You can find a fill list of Oracle SQL functions here.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

Categories: Development

How To Use the SQL Developer Format SQL Options

Complete IT Professional - Wed, 2015-08-05 06:00

How To Use the SQL Developer Format SQL OptionsIn this article, I’ll explain what formatting SQL is, and how to find the SQL Developer Format SQL options.

What Is SQL Formatting?

SQL formatting is the process of neatly arranging your code to make it more readable.

When you write SQL, the format of the code is not relevant to the database. By this, I mean that the SQL statement will execute if the syntax is correct and the formatting is terrible.

Formatting the SQL code will clean up the line breaks, indentation, capitalisation, and overall look of your SQL.

You can do this manually, or automatically, in SQL Developer. We’ll discuss them in this article.

 

Why Should I Format SQL Code?

Have you ever had a look at someone else’s code, and tried to work out what it’s doing, but couldn’t because the code was all over the place? Similar statements weren’t grouped, capitalisation wasn’t used, the code was not indented consistently, and it was just a mess to understand?

Well, this is precisely the reason you should format your SQL code.

It makes it easier for you to read in the future, for when you need to make changes.

It makes it easier for you right now, as you’re working on the code.

It also makes it easier for others to read.

It helps with debugging as well, because if the code is well formatted, it’s easier to see where any issues are, and work on them.

So, there are a few reasons you should format your SQL code. How do you do it in SQL Developer?

 

How To Change The SQL Developer Format SQL Options

The formatting options inside SQL Developer are some of my favourite options in the program. Without this feature, my SQL writing would be a lot worse.

To find these options, first go to Tools > Preferences.

autocommit 01 crop

The Preferences window is then shown.

autocommit 02

Click on the + next to the Database item to expand the Database section.

Click on the SQL Formatter item.

sql format 01

Now, there are many options here for formatting SQL. The SQL Formatter entry allows you to automatically format the SQL, with the “Autoformat Visible SQL and PL/SQL”. This is turned off by default.

The main area where the format SQL options are is under the SQL Formatter entry. So, click on the + icon next to SQL Formatter.

Two items will appear: Oracle Formatting, and Other Vendors. This “Other Vendors” option includes Access, DB2, SQL Server, and Sybase.

For the moment, we’ll just look at Oracle Formatting.

Click on Oracle Formatting. You’ll get this screen.

sql format 02

Now, this shows two main sections. The small section at the top is where you choose your profile (which we’ll come back to in a minute). You can also Import and Export profiles, which is a fantastic feature.

The larger section below is the Preview. This is how the SQL code will look, based on your profile. It’s read-only, so you can’t change the settings here.

To actually change the formatting settings, you’ll need to select a profile and click Edit.

Or, to create a new profile, select one and click Save As. You’ll be prompted to enter a name, which will then appear in your profile list.

After you click Edit, you’ll be shown this window.

sql format 03

On the left, you’ll have a list of settings, in a similar tree structure as the previous window. On the right is your preview panel.

The window may be a little small, so you might want to make it wider. Especially the Preview pane if you want to see more of what the code will look like.

There are a LOT of options to change on this window, such as:

  • Input and Output – change options for importing or outputting SQL
  • Alignment – align different types of characters
  • Indentation – set the number of spaces to indent and indenting different keywords
  • Line Breaks – what points to insert line breaks
  • CASE Linebreaks – what points during the CASE statement to insert line breaks
  • White Space – how to deal with spaces around different characters or keywords
  • Other – comments and case changing

I won’t go into detail about what each of these options include in this article, but feel free to change them and see how they impact your code. You can see the Preview on the right. Also, you can save and exit out of the windows and see how it will directly impact any code you’re working on.

How do you format your SQL code in SQL Developer?

You can press CTRL+F7 to format the SQL code in your current Code Editor window to update the formatting of the code based on any format changes you have made.

So, that’s how you can access and change the SQL Developer format SQL options.

If you want to see a full list of the Oracle SQL functions, along with tips and examples, view my Oracle SQL Functions page.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

Categories: Development

Expert Oracle Application Express 2nd Edition

Denes Kubicek - Wed, 2015-08-05 03:00
Expert Oracle Application Express 2nd Edition seems to be available at Apress. Buy this book and support the charity project in memory of Carl Backstrom and Scott Spadafore (previous members of the APEX Team). Please note that all funds will be donated to the families of these two great men.

Categories: Development

4 Things To Do If a Your Teammate Is Slowing The Team Down

Complete IT Professional - Mon, 2015-08-03 06:00

4 Things To Do If a Your Teammate Is Slowing The Team DownDo you have a team member that seems to be slowing the team down and not “pulling their weight” to get things done? Learn what you can do about it in this article.

This is a common problem when working in teams.

There are a few of you working on the one project, trying to get it all done before the deadline. You’ve all got work allocated to you, you’re discussing how to solve the problems you’re having, and trying to get your code done and ready for testing.

But, there’s one person in the team that seems to be quite a bit slower than the rest. They are not getting as much done, and as a result, they’re slowing the team down.

What can you do in this situation?

There are a few things you can do, actually.

 

Ask Them If They Need Help

The first thing that I recommend you do is speak to them and ask if they need help.

A simple question like “Hey, is there anything I can help you with?” is a good way to start.

If they are struggling with getting their work done because there is too much of it, you can help them by taking some of the work from them. Perhaps there’s a couple of user stories they haven’t gotten to, or some defects that are on their list of things to fix.

Taking things from their to-do list after asking them can help the team work better.

If they aren’t struggling with a long list of things to do, and they’re just having issues with writing the code or understanding an issue, you might be able to help them there. Sometimes, as developers, we can spend a long time looking at a single issue or an area of code, and it takes up so much time we don’t even know where the day went. This can cause unexpected delays in the team, as they might be waiting for us to do other things.

If your team member is doing this, maybe you can step in and help them look at it. If they are a more junior team member, maybe they don’t understand the code as well as others, or they aren’t as experienced or efficient with their code.

Or, they may be struggling to solve a defect, which can happen regardless of their experience. A second set of eyes can really help.

 

Ask Them If They Think They Can Meet The Deadline

Another thing you can ask your team member is if they can meet the deadline.

“Hey, this code needs to be dropped into the testing environment on Thursday, do you think you can meet that date?”

It’s good to ask this because you force the other team member to think about the deadline and give a response. They can give a variety of answers here.

If they are unsure that they can meet the deadline, or they say they can’t, you can offer to help them by going back to the first point above, and asking them if they need help.

 

Ask Your Manager For Some Of Their Work

Let’s say you’ve tried the first two steps and spoken to them about their work. It’s been going on for a while and things haven’t improved.

At this point, I would suggest speaking to your manager.

Ask your manager if you can take some of their work, or if some of the tasks they were expected to do can be moved to you.

When you do, try to frame it in a way that you’re able to take on the work, or that you have extra capacity to do it, rather than the other team member being inefficient.

This isn’t meant to deceive anyone, but I’m suggesting doing it this way initially so you can get some work to do and help the team.

 

Mention Your Concerns To Your Manager

Another thing you can do is to mention your concerns to your manager.

Talk with your manager and say that you have concerns over the speed of your team member or the fact they may be slowing the team down.

Try not to do this as though you want them to be fired. Instead, try to do it as though you want to help the team out and you’re concerned with the team.

Your manager may have a few suggestions on what can be done.

You can also come up with some suggestions as well. You should have spoken to the team member already, using the steps above, to see what can be done. You can offer to take some of their work, or your manager may want to reorganise the work in the team to make it more efficient.

The point of this is to make your manager aware of your concerns and to get the project running better. In the long run, your team member should be improving their skills, so hopefully it’s only a short term problem and doesn’t delay your team for a long time.

 

Career Action Tip: If you have a team member that is slowing the team down, ask them if there’s anything you can do to help them.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

Categories: Development

Planet Hard Drive? Muddled nonsense from Scientific American

FeuerThoughts - Thu, 2015-07-23 08:59
In the August 2015 issue of Scientific American, I came across an article titled "Planet Hard Drive", a "thought experiment" arguing that we can think of Earth as a kind of "hard drive" and "although Earth has an enormous capacity to store information, order is still rare....but the growth of order on Earth also stems from the production of cultural information."

The article is behind a paywall, so I cannot reproduce it here, but if you are a subscriber, here you go.

I find it generally hard to read SciAm these days, as well as many other scientific sources, because of the pervasive species-ism (humans unique, more important than all others) found sadly among scientists.

But this article was, I thought, a real disappointment, coming from SciAm. I sent this letter to the author:

Professor Hidalgo, 

I read your SciAm article with the above title, and I found it scientifically sloppy and offensively tone deaf, given the state of our planet today (specifically the threat of climate change and human-cause extinctions and species degradation). 

You might not read past that initial paragraph but if you do:

Scientifically Sloppy

I am all for interesting “thought experiments”, but it should have a reasonable amount of logical consistency. I think your experiment fails in this regard. 

Specifically, you talk about the growth of order on earth from production of cultural information.

This implies a clear net positive change in order due to our intensely “ordered” products. 

Yet previously, you recognized that there is order (lots of it) in living things. 

And that's where I see a very deep (specie-ist-driven) fallacy: to create our products humans destroy a vast amount of living things and therefore wipe out corresponding enormous amounts of order. 

Vast parts of the rainforest, extinction of entire species, degradation of the ocean, etc., etc., etc. - do you really think that if you even attempted to conceptualize the volume of order sacrificed to build iPhones, you could come out with a net positive growth in order?

I suppose it might be remotely possible - but you don’t even address this trade-off, making your argument incomplete and sloppy. I am very surprised that SciAm did not insist on a more rigorous treatment.

Sdaly, you seem to blithely accept that destruction of life on our planet in order to manifest our culture-as-thought as products. 

Which that brings me to…

Offensively Tone Deaf

Your insistence to see the entire world through a human filter and impose human paradigms onto the rest of the natural world is shocking, giving the growing awareness (especially among the most rational of us, like many scientists).
“A tree, for example, is a computer”
“Objects of this kind [manufactured products] are particularly special.”
“Biological cells are finite computers”
“People are also limited, and we transcend our finite compuational capacities by forming social and professional networks.”
“Special” “Transcend”

You use words that impute relentlessly positive values to human activity. 

Yet if you do not place humans “above” all others, you could at least say (my changes in bold):

“People are also limited, and we augment our finite compuational capacities by forming social and professional networks. A necessary consequence of this agumentation is the destruction of the computational capacities of billions of other living creatures.

At the very end of your muddled thought experiment, you finally hint at a bigger picture:
“The resulting hyperconnected society will present our species with some of the most challenging ethical problems in human history.”
Ah, ethics! Finally! Professor Hidalgo will now point out the grave price paid by our planet and co-inhabitants for human's desire for comfort and convenience, but....

No, no. For you, like way too many other humans, all that matters is the human species.
“We could lose aspects of our humanity that some of us consider essential: for example, we might cheat death.”
Now that would be a real ethical disaster (cheating death) - precisely because it mean accelerated devastation of our planet and non-humans.

But that doesn’t seem to even register in your thinking.
Categories: Development

New APEX Blog

Denes Kubicek - Wed, 2015-07-22 03:33
This is a new blog on APEX. I expect a lots of good stuff there in the future. The last example describes how to modify the Page Designer and move the panes, adjust the layout and simplify the development of an application since accessing element attributes is much easier that way. I think this is worth of looking at and maybe the APEX team considers this as an improvement for the future release.



Categories: Development

A Lot To Listen To

FeuerThoughts - Mon, 2015-07-20 07:40
A Lot To Listen To

Sometimes, if you're lucky,
there is nothing to hear
but the sound of the wind
blowing through trees.

Now you could say:
"That's not much to listen to."
Or you could listen...

Listen
to the rustling, hissing, whispering, sometimes angry sound
of thousands 
of almost silent brushings of leaf against leaf,
of feather-light taps of twig striking twig,
any single act nothing to hear at all
but when the tree is big enough
and the leaves are numerous enough
and the branches reach out 
thinner and thinner
poking out toward the sun
carrying leaves to their destiny,

then you might be able to hear
the sound of the wind
blowing through trees.

It's a lot to listen to,
if you can hear it.




Copyright 2015 Steven Feuerstein
Categories: Development

First patch set (5.0.1) released for APEX 5.0

Dimitri Gielis - Thu, 2015-07-16 16:22
I know some people waiting till the first patch set that comes available after a major Oracle APEX release... today you no longer have to wait, APEX 5.0.1 is now available.

In the patch set notes you can read what changed.

If you're still on APEX 4.x you can go immediately to APEX 5.0.1, you only need to download the latest version on OTN.

If you're already on APEX 5.0, you can download the patch set from support.oracle.com, search for patch number 21364820. Applying the patch took less than 5 minutes in my environment.



This patch set updates the Universal Theme too, so don't forget to update your images folder. When you login in APEX after the patch, it will check if you have the correct images folder, if not it will give you an alert. Although I updated the images directory I still got that alert due to browser caching. Refresh your page and it should be ok.

Note that it's important to be on APEX 5.0.1 when you use APEX Office Print - currently available in beta for a select audience, public release end of this month (July 2015). Behind the scenes we use the APEX_JSON and APEX_WEB_SERVICE packages which got an update in APEX 5.0.1.

And finally, there's a nice new D3 chart available in APEX 5.0.1 called "D3 Collapsible Treemap Chart"


Happy upgrading...

Update 18-JUL-2015: there're still a couple of known issues, please read those too and install some patchset exception when necessary.
Categories: Development

Oracle APEX 5.0.1 now available

Patrick Wolf - Thu, 2015-07-16 06:48
Oracle Application Express 5.0.1 is now released and available for download. If you wish to download the full release of Oracle Application Express 5.0.1, you can get it from the Downloads page on OTN. If you have Oracle APEX 5.0.0 … Continue reading
Categories: Development

Oracle LTRIM Function with Examples

Complete IT Professional - Thu, 2015-07-16 06:00

Oracle LTRIM FunctionThe LTRIM function removes characters from the left side of a string. Learn more about it and see some examples in this article.

Purpose of the Oracle LTRIM Function

The purpose of the LTRIM function is to remove a specified character from the left side of a string. The L in LTRIM stands for “Left”, and is the opposite of the RTRIM or “Right” Trim function.

 

Syntax

The syntax of the Oracle LTRIM functions is:

LTRIM( input_string, [trim_string] )

 

Parameters

The parameters of the LTRIM function are:

  • input_string (mandatory): This is the string to remove characters from the left-hand side of.
  • trim_string (optional): This is the string to be removed from the input_string. If it is not specified, a space is used, and all spaces are removed from the left of the input_string.

Some points to note about LTRIM:

  • If the trim_string is a literal value, you need to include it inside single quotes. For example, to remove an underscore, you need to specify it as ‘_’
  • Both input_string and trim_string can be of data type CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
  • The returned value is a VARCHAR2 data type if the input types are CHAR, VARCHAR2, NCHAR or NVARCHAR2, and the returned value is a LOB data type if the input types are LOB or CLOB.
  • The trim_string can be more than one character
  • The function removes each individual value inside trim_string, not the string as a whole. See the Examples section below for more information.

 

Can You Use Oracle LTRIM To Remove Leading Zeroes?

Yes, you can. It’s one of the more common uses for the function that I’ve seen.

This can be done as:

LTRIM(value, ‘0’)

See the Examples section below for more information.

 

Can You Use Oracle LTRIM with RTRIM?

Yes, you can, and it works in the same way as just using the TRIM function.

You’ll need to use one inside the other, and it doesn’t really matter which one is used first.

So, you can use either LTRIM(RTRIM(value)) or RTRIM(LTRIM(value)).

See the Examples section below for more information.

 

Are There Other Ways for Oracle to Trim Strings?

Yes, there are a few ways you can trim strings in Oracle:

  • Use LTRIM or RTRIM
  • Use TRIM
  • Use REPLACE
  • Use SUBSTR if you need more advanced trimming features
  • Use regular expressions

 

Examples of the LTRIM Function

Here are some examples of the Oracle LTRIM function. I find that examples are the best way for me to learn about code, even with the explanation above.

Example 1

This example demonstrates a simple LTRIM with no trim value specified.

SELECT LTRIM('    Complete IT Professional')
AS LTRIM_EXAMPLE FROM DUAL;

Result:

LTRIM_EXAMPLEComplete IT Professional

The extra spaces are removed from the original value.

 

Example 2

This example uses a specific value to trim.

SELECT LTRIM('___Complete IT Professional', '_')
AS LTRIM_EXAMPLE FROM DUAL;

Result:

LTRIM_EXAMPLEComplete IT Professional

The underscores are removed from the original value.

Example 3

This example uses LTRIM with several characters as the string to trim.

SELECT LTRIM('; ; ; ; ; Complete IT Professional', ' ; ')
AS LTRIM_EXAMPLE FROM DUAL;

Result:

LTRIM_EXAMPLEComplete IT Professional

Both the spaces and semicolons are removed from the original value.

 

Example 4

This example uses LTRIM  on data in a table, instead of providing a value.

SELECT country, LTRIM(country, 'U')
AS LTRIM_EXAMPLE FROM customers;

Result:

COUNTRYLTRIM_EXAMPLEUSASAUSASACanadaCanadaUKKUSASA(null)(null)FranceFrance(null)(null)

The capital U is removed from several values.

 

Example 5

This example uses LTRIM on data in a table with several characters in the trim parameter.

SELECT full_address, LTRIM(full_address, '1')
AS LTRIM_EXAMPLE FROM customers;

Result:

FULL_ADDRESSLTRIM_EXAMPLE10 Long Road0 Long Road50 Market Street50 Market Street201 Flinders Lane201 Flinders Lane8 Smith Street8 Smith Street14 Wellington Road4 Wellington Road80 Victoria Street80 Victoria Street5 Johnson St5 Johnson St155 Long Road55 Long Road

The “1” characters are removed from several address values.

 

Example 6

This example uses LTRIM  with 0 as the parameter.

SELECT LTRIM('000Complete IT Professional', 0)
AS LTRIM_EXAMPLE FROM DUAL;

Result:

LTRIM_EXAMPLEComplete IT Professional

The zeroes are removed from the original value.

 

Example 7

This example uses both LTRIM and RTRIM in the one expression.

SELECT LTRIM(RTRIM('___Complete IT Professional__', '_'), '_')
AS RTRIM_EXAMPLE FROM DUAL;

Result:

LTRIM_EXAMPLEComplete IT Professional

The underscores are removed from both sides of the original value.

 

Example 8

This example uses Unicode characers as the trim parameter.

SELECT LTRIM('ééComplete IT Professional', 'é')
AS LTRIM_EXAMPLE FROM DUAL;

Result:

LTRIM_EXAMPLEComplete IT Professional

The accented “e” character is removed from the original value.

Similar Functions

Some functions which are similar to the LTRIM function are:

  • RTRIM – Trims characters from the right of the string. The opposite of the LTRIM function.
  • TRIM – Trims characters from both the left and right side of the string. A combination of LTRIM and RTRIM.
  • SUBSTR – Extracts one value from a larger value. Not really a TRIM function but does something similar.
  • REPLACE – Replaces occurrences of one text value with another.

You can find a full list of Oracle functions here.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

Image courtesy of digitalart / FreeDigitalPhotos.net

Categories: Development

APEX 5 - Opening and Closing Modal Window

Denes Kubicek - Wed, 2015-07-15 05:56
This example is showing how to open a Modal Page from any element in your application. It is easy to get it working using some standards like a button or a link in a report. However, it is not 100% clear how to get it working with some other elements which don't have the redirect functionality built in (item, region title, custom links, etc.). This example is also showing how to get the success message displayed on the parent page after closing of the Modal Page.

Categories: Development

How To Keep Calm at Work

Complete IT Professional - Mon, 2015-07-13 06:00

How To Keep Calm at WorkAs part of your job in the IT industry, there will no doubt be times that you will get annoyed and stressed. Learn how to keep calm at work with some tips in this article.

Take a Deep Breath

When I’m not feeling calm at work, the first thing I do is stop and take a big, deep breath. It makes me stop what I’m doing for a few seconds and focus just on the breath that I’m taking.

It always makes me feel better, especially when I’m annoyed about something.

So, next time you’re feeling stressed, annoyed, or irritated about something at work, try this.

  • Stop what you’re dong.
  • Take one big deep breath in.
  • Breathe it all out.
  • Resume what you were doing.

That whole process only takes about ten seconds. But, it allows you to re-focus on what you’re doing and can have a calming effect on you.

 

Remember the Bigger Picture

Another tip that I can suggest to keep calm at work is to remember the bigger picture of why you’re there.

If you’re getting annoyed at someone else or having a disagreement with them over the way a certain feature should be developed for your project, for example, then one thing you can do is to try think of the bigger picture.

What’s the overall plan for what you’re doing? What are you both trying to achieve?

If you have different ideas on how something should be done, for example, you could be disagreeing over how it’s being done. Think about why you’re both there, on the project.

Are you trying to add a new feature to make the customers’ lives easier? Are you trying to improve an internal system? Make a new system from scratch?

If you can see what you’re both trying to do at a high level, and see things from the other person’s point of view, it can help you keep calm at work.

 

Get Away From Your Desk

Another tip I can suggest is to get away form your desk if you want to calm down.

You can try go for a walk outside. Get out of the office and get some fresh air. Walk around the block, down to th ecar park, to the coffee shop – anything to get outside and get some fresh air.

The exercise, the fresh air, and taking a break from your work all contribute to helping you keep calm at work. It can also give you more energy.

If you can’t walk outside for whatever reason, if it’s pouring with rain for example, then take a walk to the ktichen or somewhere else inside.

You can go get a coffee or a glass of water or something. This can help you feel better at work and also gets you away from the desk.

In any case, when you return to what you were doing, you should feel a little calmer and a little better.

 

Vent To A Friend

We can do all of the breathing and walking outside that we want, but sometimes, it just isn’t enough.

I mean, most of the time it works for me, but there’s been a few times over the years where I’ve felt really annoyed.

If you feel the same way, then speak to a friend of yours about it and tell them the story.

You can speak to your partner, a friend outside work, a family member, or someone in your team, depending on the issue.

Sometimes it can really help if you just get it off your shoulders and just express the way you’re feeling to someone.

It’s not something I suggest doing all of the time, but it can help sometimes if you really just can’t focus on your work and whatever the issue is has really thrown you off your game.

 

Readers: What methods do you use to keep calm at work after something goes wrong? Do you have any other tips to share about staying calm? Share them in the comments section below.

Career Action Tip: Next time you’re feeling annoyed about something at work, remember to take a deep breath and consider going for a short walk outside.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

Image courtesy of stockimages / FreeDigitalPhotos.net

Categories: Development

Hot Deployment in JDeveloper 12c - Don't Stop/Start Your App

Shay Shmeltzer - Fri, 2015-07-10 16:45

Old habits are hard to get rid off, and I still see long time users of JDeveloper (and also many new users) who are stopping/starting their application on the embedded WebLogic each time that they make a change or addition to their code.

Well you should stop it! (I mean stop stopping the application).

For a while now, JDeveloper has support for hot deployment that means that when you do most of the changes to your code you just need to do save-all followed by a rebuild of your viewController project - and that's it.

You can then go to your browser and reload your page - and the changes will be reflected there.  This will not only save you the time it takes to undeploy and redeploy your app, it will also reduce the amount of memory you use since frequent redeployment of the app on the embedded WebLogic leads to bigger memory consumption.

In the demo below I use JDeveloper 12.1.3 to show you that I can just do the save->rebuild and pick up:

  • Changes in the JSF file
  • Changes to the JSF configuration file adfc-config.xml
  • New classes that are added to both the model and viewController projects
  • Changed to the ADF configuration files (pagedefs, data binding, data controls)

So for most cases, you should be covered with this hot-deployment capability.

There are some cases that will require a redeploy of the application (for example if you add a new skin css file, or if you change some runtime configuration of your app in web.xml) but for most cases you don't need to continue with the stop/start habit.

Categories: Development

How To Turn On or Off SQL Developer Autocommit Settings

Complete IT Professional - Fri, 2015-07-10 06:00

SQL Developer AutocommitIn this article, I’ll explain what the SQL Developer Autocommit feature is, where to find it, and how to turn it on or off.

What Is SQL Developer Autocommit?

When you write an SQL statement in SQL Developer, you perform an operation on that data. If you’re just reading the data, this is pretty straightforward.

Where it acts differently is when you change data in the database. This is usually done with the DELETE, INSERT, or UPDATE statements.

If you run an SQL statement that does any of these functions, then the data will be updated, but it won’t be committed.

If you’re not sure what committed means (when we talk about databases), it just means that the data is not permanently saved. It allows you to see the changes in your session, and then you can make a conscious decision to update the changes permanently (committing) or undoing the changes (rolling back).

Now, this is the default behaviour. There are two separate steps to this – running the statement to change the data, and then a second command to commit the changes to the database.

SQL Developer allows you to change this default behaviour, so that the statement is run and the changes are saved in a single step – hence the term “auto commit”.

 

How Do I Change The Autocommit Setting in SQL Developer?

SQL Developer is pretty flexible when it comes to what you can change, and autocommit is no exception.

To find the autocommit setting, go to the Tools > Preferences.

SQL Developer Tools and Preferences

The Preferences window will appear.

SQL Developer Preferences

Click on the + icon next to Database to expand it.

SQL Developer Preferences

Then, click on Advanced.

SQL Developer Preferences Advanced

Here, you’ll see the option for Autocommit.

SQL Developer Preferences Autocommit

Click the checkbox to turn it on.

Here’s what the setting does:

ValueImpactCheckedAutocommit is on. SQL statements that change data will have their changes automatically committed to the database.UncheckedDefault. Autocommit is off. SQL statements that change data will not have their changes automatically committed to the database. A separate COMMIT action is needed.
What Are The Disadvantages of Autocommit?

Sure, turning on autocommit may save you time and confusion if you expect changes to be saved but they aren’t.

But there are some disadvantages.

You won’t be able to undo any of your changes by issuing a ROLLBACK statement, as you’d normally be able to do. If you run a DELETE or UPDATE statement, your changes are made permanently, and it’s much harder to get them back

It’s not impossible, though. If you have set up save points or database backups then you can restore from a backup, but it is a hassle.

Generally, I would prefer to have this autocommit option turned off in SQL Developer. The only exception would be if you’re running Oracle Express on your own computer and want to save some time with your SQL.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

Categories: Development

Oracle RTRIM Function with Examples

Complete IT Professional - Thu, 2015-07-09 06:00

Oracle RTRIM FunctionThe Oracle RTRIM function is one of the most commonly used string manipulation functions in SQL. Learn more about it and see some examples in this article.

Purpose of the Oracle RTRIM Function

The Oracle RTRIM function removes all characters that you specify from the right side of a string.

This is most often used when you want to remove spaces from the right side of the string. However, it can be used for other characters as well, depending on the data.

 

Syntax

The syntax for the Oracle RTRIM function is:

RTRIM ( input_string, [trim_character])

 

Parameters

The parameters of the RTRIM function are:

  • input_string (mandatory): This is the string that will have the characters trimmed from it.
  • trim_string (optional): This is the value to trim or remove from the input_string. If it is omitted, a space character is used for trimming.

The R in RTRIM stands for Right, because it removes the characters from the right of the string.

Some other points to remember:

  • If the trim_string is a literal value, you need to include it inside single quotes. For example, to remove an underscore, you need to specify it as ‘_’
  • Both parameters can be any of the types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
  • The string returned is a VARCHAR2 data type if the input_string was a character, and LOB if it was a LOB data type.
  • The trim_string can be more than one character
  • The function removes each individual value inside trim_string, not the string as a whole. See the Examples section below for more information.

 

Can You Use Oracle RTRIM with Special Characters?

Yes, you can. You can use exclamation points and many kinds of special characters as input.

You can also use unicode characters, such as å, as the function supports NVARCHAR2 types.

See the Examples section for more information.

 

Can You Use Oracle RTRIM with LTRIM?

Yes, you can. It works similar to the TRIM function.

You’ll need to enclose one within the other, and it doesn’t matter which one is outside and which is inside.

So, LTRIM(RTRIM(value)) works the same as RTRIM(LTRIM(value)).

See the Examples section for more information.

 

Can You Use Oracle RTRIM with CHAR or LOB Types?

Yes, the Oracle RTRIM can handle CHAR and LOB types. It can handle CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB data types.

 

Examples of the RTRIM Function

Here are some examples of the RTRIM function. I find that examples are the best way for me to learn about code, even with the explanation above.

 

Example 1

This example demonstrates a simple RTRIM with no trim value specified.

SELECT RTRIM('Complete IT Professional   ')
AS RTRIM_EXAMPLE FROM DUAL;

Result:

RTRIM_EXAMPLEComplete IT Professional

The extra spaces are removed from the original value.

 

Example 2

This example uses a specific value to trim.

SELECT RTRIM('Complete IT Professional___', '_')
AS RTRIM_EXAMPLE FROM DUAL;

Result:

RTRIM_EXAMPLEComplete IT Professional

The underscores are removed from the original value.

 

Example 3

This example uses RTRIM with several characters as the string to trim.

SELECT RTRIM('Complete IT Professional; ; ; ; ; ', ' ; ')
AS RTRIM_EXAMPLE FROM DUAL;

Result:

RTRIM_EXAMPLEComplete IT Professional

Both the spaces and semicolons are removed from the original value.

 

Example 4

This example uses RTRIM on data in a table, instead of providing a value.

SELECT country, RTRIM(country, 'A')
AS RTRIM_EXAMPLE FROM customers;

Result:

COUNTRYRTRIM_EXAMPLEUSAUSUSAUSCanadaCanadaUKUKUSAUS(null)(null)FranceFrance(null)(null)

The capital A is removed from the “USA” values. It remains on the end of “Canada” because it is a lower-case “a”.

 

Example 5

This example uses RTRIM on data in a table with several characters in the trim parameter.

SELECT full_address, RTRIM(full_address, 'et')
AS RTRIM_EXAMPLE FROM customers;
Result:
FULL_ADDRESSRTRIM_EXAMPLE10 Long Road10 Long Road50 Market Street50 Market Str201 Flinders Lane201 Flinders Lan8 Smith Street8 Smith Str14 Wellington Road14 Wellington Road80 Victoria Street80 Victoria Str5 Johnson St5 Johnson S155 Long Road155 Long Road
The letters “e” and “t” are removed from the right of the street names. Some values have been changed to “Str” because all occurrences of these letters are removed, not just those that end in “et”.
Example 6

This example uses RTRIM with 0 as the parameter.

SELECT RTRIM('Complete IT Professional00', 0)
AS RTRIM_EXAMPLE FROM DUAL;

Result:

RTRIM_EXAMPLEComplete IT Professional

The zeroes are removed from the original value.

 

Example 7

This example uses both LTRIM and RTRIM in the one expression.

SELECT LTRIM(RTRIM('___Complete IT Professional__', '_'), '_')
AS RTRIM_EXAMPLE FROM DUAL;

Result:

RTRIM_EXAMPLEComplete IT Professional

The underscores are removed from both sides of the original value.

 

Example 8

This example uses Unicode characers as the trim parameter.

SELECT RTRIM('Complete IT Professionalé', 'é')
AS RTRIM_EXAMPLE FROM DUAL;

Result:

RTRIM_EXAMPLEComplete IT Professional

The accented “e” character is removed from the original value.

 

Similar Functions

Some functions which are similar to the RTRIM function are:

  • TRIM – This function trims characters from both the left and the right of the specified value.
  • LTRIM – This function trims characters from the left of the specified value only.
  • SUBSTR – Extracts one value from a larger value. Not really a TRIM function but does something similar.
  • REPLACE – Replaces occurrences of one text value with another.

You can find a full list of Oracle functions here.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

Image courtesy of digitalart / FreeDigitalPhotos.net

Categories: Development

12c: New SQL PLAN OPERATIONS and HINTS

XTended Oracle SQL - Wed, 2015-07-08 08:27

This post is just a compilation of the links to other people’s articles and short descriptions about new SQL PLAN OPERATIONS and HINTS with a couple little additions from me.

th.c_operation_name { min-width:100px; max-width:100px; } th.c_description { min-width:200px } .c_links { min-width:150px; max-width:220px; } .c_links ul { margin: 0 0 5px 0 !important; -webkit-padding-start: 5px; } .c_links ul li { margin-left: 0px; -webkit-padding-start: 0px; } td.c_operation_name { font-size:12px;} td.c_description { font-size:12px;} td.c_links { font-size:10px;} .c_body td { vertical-align: text-top; } div.hints_wrapper { border-style: solid; border-width: 1px; padding: 2px; overflow: scroll !important; } div.hints_content { width: 1175px; min-width:1175px; padding: 2px; }


OPERATION_NAME Description Links JSONTABLE EVALUATION JSON_TABLE execution XMLTABLE EVALUATION This is new name for “COLLECTION ITERATOR PICKLER FETCH [XQSEQUENCEFROMXMLTYPE]”. XPATH EVALUATION still exists. MATCH RECOGNIZE New feature “PATTERN MATCHING” STATISTICS COLLECTOR Optimizer statistics collector OPTIMIZER STATISTICS GATHERING Automatic Optimizer statistics gathering during the following types of bulk loads:

  • CREATE TABLE … AS SELECT
  • INSERT INTO … SELECT into an empty table using a direct-path insert
CUBE JOIN Joining Cubes to Tables and Views EXPRESSION EVALUATION Each parallel slave executes scalar correllated subqueries from SELECT-list. parallel “FILTER” Each parallel slave executes own FILTER operation

Example
SQL> explain plan for
  2  select--+ parallel
  3      owner,object_name
  4  from xt_test l
  5  where exists(select/*+ no_unnest */ 0 from dual where dummy=object_name);

Explained.

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
Plan hash value: 2189761709

-------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name      | Rows  | Bytes | Cost (%CPU)|   TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |           |     2 |    62 |   177K  (1)|       |      |            |
|   1 |  PX COORDINATOR          |           |       |       |            |       |      |            |
|   2 |   PX SEND QC (RANDOM)    | :TQ10000  | 91060 |  2756K|   113   (0)| Q1,00 | P->S | QC (RAND)  |
|*  3 |    FILTER                |           |       |       |            | Q1,00 | PCWC |            |
|   4 |     PX BLOCK ITERATOR    |           | 91060 |  2756K|   113   (0)| Q1,00 | PCWC |            |
|   5 |      INDEX FAST FULL SCAN| IX_TEST_1 | 91060 |  2756K|   113   (0)| Q1,00 | PCWP |            |
|*  6 |     TABLE ACCESS FULL    | DUAL      |     1 |     2 |     2   (0)|       |      |            |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "SYS"."DUAL" "DUAL" WHERE "DUMMY"=:B1))
   6 - filter("DUMMY"=:B1)
                

[collapse]
PX SELECTOR Execution of the serial plan parts in the one of the parallel slaves PX SEND 1 SLAVE Execution of the serial plan parts in the one of the parallel slaves(single DFO tree) PX TASK Parallel access to fixed tables(x$) by each node in RAC HYBRID HASH DISTRIBUTION Adaptive parallel data distribution that does not decide the final data distribution(HASH, BROADCAST or SKEW) method until execution time. PQ_DISTRIBUTE_WINDOW In addition to “PX SEND” HASH-distribution for WINDOW functions, “PX SEND RANGE” was added
Example
-- TESTPART - list-partitiioned table:
-------------------------------------------------------------------------------------------------
| Operation               | Name     | Rows  | Cost | Pstart| Pstop |   TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------
| SELECT STATEMENT        |          | 74384 |   102|       |       |       |      |            |
|  PX COORDINATOR         |          |       |      |       |       |       |      |            |
|   PX SEND QC (RANDOM)   | :TQ10001 | 74384 |   102|       |       | Q1,01 | P->S | QC (RAND)  |
|    WINDOW SORT          |          | 74384 |   102|       |       | Q1,01 | PCWP |            |
|     PX RECEIVE          |          | 74384 |   100|       |       | Q1,01 | PCWP |            |
|      PX SEND RANGE      | :TQ10000 | 74384 |   100|       |       | Q1,00 | P->P | RANGE      |
|       PX BLOCK ITERATOR |          | 74384 |   100|     1 |     3 | Q1,00 | PCWC |            |
|        TABLE ACCESS FULL| TESTPART | 74384 |   100|     1 |     3 | Q1,00 | PCWP |            |
-------------------------------------------------------------------------------------------------
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      PQ_DISTRIBUTE_WINDOW(@"SEL$1" 3)
      FULL(@"SEL$1" “TESTPART"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('12.1.0.2')
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

[collapse]

Hint PQ_DISTRIBUTE_WINDOW(@Query_block N), where N=1 for hash, N=2 for range, N=3 for list VECTOR
KEY VECTOR Inmemory aggregation RECURSIVE ITERATION Unknown WINDOW CONSOLIDATOR WINDOW CONSOLIDATOR BUFFER for parallel execution of analyrical WINDOW aggregation functions

Example
SQL> explain plan for select/*+ parallel(t 4) PQ_DISTRIBUTE_WINDOW(2) */ count(*) over(partition by owner) cnt,owner from xt_test t;
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
Plan hash value: 3410952625
---------------------------------------------------------------------------------------------------
| Id | Operation                    |Name    |Rows |Cost |Pstart|Pstop|   TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT             |        |91060|  124|      |     |       |      |            |
|  1 |  PX COORDINATOR              |        |     |     |      |     |       |      |            |
|  2 |   PX SEND QC (RANDOM)        |:TQ10001|91060|  124|      |     | Q1,01 | P->S | QC (RAND)  |
|  3 |    WINDOW CONSOLIDATOR BUFFER|        |91060|  124|      |     | Q1,01 | PCWP |            |
|  4 |     PX RECEIVE               |        |91060|  124|      |     | Q1,01 | PCWP |            |
|  5 |      PX SEND HASH            |:TQ10000|91060|  124|      |     | Q1,00 | P->P | HASH       |
|  6 |       WINDOW SORT            |        |91060|  124|      |     | Q1,00 | PCWP |            |
|  7 |        PX BLOCK ITERATOR     |        |91060|  122|    1 |    4| Q1,00 | PCWC |            |
|  8 |         TABLE ACCESS FULL    |XT_TEST |91060|  122|    1 |    4| Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------

Note
-----
   - Degree of Parallelism is 4 because of table property
                

[collapse]
DETECT END Unknown DM EXP MAX AGGR Unknown DM EXP MAX PAR Unknown FAULT-TOLERANCE BUFFER The fault-tolerance for parallel statement.
Patent #US8572051: Making parallel execution of structured query language statements fault-tolerant
  • PX_FAULT_TOLERANCE / NO_PX_FAULT_TOLERANCE hints


See also:

  1. Randolf Geist “12c New Optimizer Features”
  2. Randolf Geist “Parallel Execution 12c New Features Overview”


HINTS:

sup {color: red} table.HINTS{ font-size:12px; } .HINTS td {vertical-align: text-top;}

PATH HINT_CLASS HINT_NAME VERSION VERSION_OUTLINE ALL WITH_PLSQL WITH_PLSQL 12.1.0.1 ALL -> ANSI_REARCH ANSI_REARCH 1 ANSI_REARCH
NO_ANSI_REARCH 12.1.0.2 12.1.0.2 ALL -> EXECUTION BATCH_TABLE_ACCESS_BY_ROWID 2 BATCH_TABLE_ACCESS_BY_ROWID
NO_BATCH_TABLE_ACCESS_BY_ROWID 12.1.0.1 12.1.0.1 INMEMORY INMEMORY
NO_INMEMORY 12.1.0.2 12.1.0.2 INMEMORY_PRUNING INMEMORY_PRUNING
NO_INMEMORY_PRUNING 12.1.0.2 12.1.0.2 ALL -> COMPILATION -> ZONEMAP ZONEMAP ZONEMAP
NO_ZONEMAP 12.1.0.1 12.1.0.1 ALL -> COMPILATION -> DATA_SECURITY_REWRITE DATA_SECURITY_REWRITE_LIMIT DATA_SECURITY_REWRITE_LIMIT

NO_DATA_SECURITY_REWRITE 12.1.0.1 12.1.0.1 ALL -> COMPILATION -> CBO CLUSTER_BY_ROWID CLUSTER_BY_ROWID
CLUSTER_BY_ROWID 12.1.0.1(11.2.0.4) 12.1.0.1 ALL -> COMPILATION -> CBO -> ACCESS_PATH -> BITMAP_TREE BITMAP_AND BITMAP_AND 12.1.0.1 12.1.0.1 ALL -> COMPILATION -> CBO -> ADAPTIVE_PLAN ADAPTIVE_PLAN ADAPTIVE_PLAN
NO_ADAPTIVE_PLAN 12.1.0.2 12.1.0.2 ALL -> COMPILATION -> CBO -> AUTO_REOPT AUTO_REOPTIMIZE 2 AUTO_REOPTIMIZE
NO_AUTO_REOPTIMIZE 12.1.0.1 ALL -> COMPILATION -> CBO -> JOIN_METHOD ANTIJOIN CUBE_AJ 12.1.0.1 12.1.0.1 SEMIJOIN CUBE_SJ 12.1.0.1 12.1.0.1 ALL -> COMPILATION -> CBO -> JOIN_METHOD -> USE_CUBE JOIN USE_CUBE

NO_USE_CUBE 12.1.0.1 12.1.0.1 ALL -> COMPILATION -> CBO -> PARTIAL_JOIN PARTIAL_JOIN PARTIAL_JOIN
NO_PARTIAL_JOIN 12.1.0.1 12.1.0.1 ALL -> COMPILATION -> CBO -> PARTITION USE_HIDDEN_PARTITIONS USE_HIDDEN_PARTITIONS 12.1.0.1 ALL -> COMPILATION -> CBO -> PQ PARTIAL_ROLLUP_PUSHDOWN PARTIAL_ROLLUP_PUSHDOWN

NO_PARTIAL_ROLLUP_PUSHDOWN 12.1.0.1 12.1.0.1 PQ_CONCURRENT_UNION PQ_CONCURRENT_UNION

NO_PQ_CONCURRENT_UNION 12.1.0.1 12.1.0.1 PQ_DISTRIBUTE_WINDOW PQ_DISTRIBUTE_WINDOW 12.1.0.1 12.1.0.1 PQ_FILTER PQ_FILTER 12.1.0.1 12.1.0.1 PQ_SKEW PQ_SKEW

NO_PQ_SKEW 12.1.0.1 12.1.0.1 PX_FAULT_TOLERANCE PX_FAULT_TOLERANCE
NO_PX_FAULT_TOLERANCE 12.1.0.1 12.1.0.1 ALL -> COMPILATION -> CBO -> PQ -> PQ_REPLICATE PQ_REPLICATE PQ_REPLICATE
NO_PQ_REPLICATE 12.1.0.1 12.1.0.1 ALL -> COMPILATION -> CBO -> STATS -> DBMS_STATS GATHER_OPTIMIZER_STATISTICS GATHER_OPTIMIZER_STATISTICS

NO_GATHER_OPTIMIZER_STATISTICS 12.1.0.1 ALL -> COMPILATION -> TRANSFORMATION ELIM_GROUPBY ? ELIM_GROUPBY

NO_ELIM_GROUPBY ALL -> COMPILATION -> CBO -> CBQT -> VECTOR_AGG
and

ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> VECTOR_AGG USE_VECTOR_AGGREGATION USE_VECTOR_AGGREGATION
NO_USE_VECTOR_AGGREGATION 12.1.0.2 12.1.0.2 VECTOR_TRANSFORM VECTOR_TRANSFORM

NO_VECTOR_TRANSFORM 12.1.0.2 12.1.0.2 VECTOR_TRANSFORM_DIMS VECTOR_TRANSFORM_DIMS

NO_VECTOR_TRANSFORM_DIMS 12.1.0.2 12.1.0.2 VECTOR_TRANSFORM_FACT VECTOR_TRANSFORM_FACT

NO_VECTOR_TRANSFORM_FACT 12.1.0.2 12.1.0.2

ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> DECORRELATE DECORRELATE DECORRELATE

NO_DECORRELATE 12.1.0.1 12.1.0.1

See also:
Fuyuncat(Wei Huang) – “Oracle 12c new SQL Hints”

Categories: Development

Pages

Subscribe to Oracle FAQ aggregator - Development