From time to time someone comes up with the question about whether or not the order of tables in the from clause of a SQL statement should make a difference to execution plans and performance. Broadly speaking the answer is no, although there are a couple of boundary cases were a difference can appear unexpectedly.
When considering join permutations the optimizer has a few algorithms for picking an initial join order and then deciding how to permute from that order, and one of the criteria with the very lowest priority (i.e. when all other factors are equal) is dictated by the order the tables appear in the from clause so if you have enough tables in the from clause it’s possible for the subset of join orders considered to change if you change the from clause in a way that causes the initial join order to change.
It’s been over 11 years since I wrote the article I’ve linked to in the previous paragraph and in that time no-one has yet approached me with other examples of a plan changing due to a change in the from clause order (though, with all the transformations now available to the optimizer, I wouldn’t be surprised if a few cases have appeared occasionally, so feel free to let me know if you think you’ve got an interesting example that I can experiment on).
A little while ago, though, while testing a feature enhancement in 12.2, I finally came across a case where a real difference appeared. Here’s the query I was using – I’ll give you the SQL to reproduce the tables at the end of the article:
select count(c.small_vc_c) from grandparent g, parent p, child c where c.small_num_c between 200 and 215 and p.id = c.id_p and p.id_g = c.id_g and g.id = p.id_g ;
As you will see later on the three tables grandparent, parent, child have the obvious primary keys and referential integrity constraints. This means that grandparent has a single-column primary key, parent has a two-column primary key, and child has a three-column primary key. The query joins the three tables along their primary keys and then selects data only from the child table, so it’s a good candidate for join elimination.
In earlier versions of Oracle join elimination could take place only if the primary key you joined to was a single column key, so 12.1 and earlier would be able to eliminate just the grandparent from this three-table join; but in 12.2 multi-column primary keys also allow join elimination to take place, so we might hope that the plan we get from this query would eliminate both the grandparent and parent tables. Here’s the plan (pulled from memory after execution):
SQL_ID 8hdybjpn2884b, child number 0 ------------------------------------- select count(c.small_vc_c) from grandparent g, parent p, child c where c.small_num_c between 200 and 215 and p.id = c.id_p and p.id_g = c.id_g and g.id = p.id_g Plan hash value: 4120004759 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 26 (100)| | | 1 | SORT AGGREGATE | | 1 | 23 | | | | 2 | NESTED LOOPS | | 85 | 1955 | 26 (4)| 00:00:01 | |* 3 | TABLE ACCESS FULL| CHILD | 85 | 1615 | 26 (4)| 00:00:01 | |* 4 | INDEX UNIQUE SCAN| G_PK | 1 | 4 | 0 (0)| | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter(("C"."SMALL_NUM_C"<=215 AND "C"."SMALL_NUM_C">=200)) 4 - access("G"."ID"="C"."ID_G")
It didn’t work quite as expected. The optimizer has managed to eliminate table parent – so that looks like “single column primary key” join elimination has worked, but “multi-column” join elimination hasn’t appeared. On the other hand, I’ve not followed my usual rules for writing SQL so let’s try again. If I follow the pattern I usually follow, my from clause would have been in the order child -> parent -> grandparent – listing the tables in the order I expect to visit them. Here’s the plan – again pulled from memory – after making this visual change the SQL:
SQL_ID 1uuq5vf4bq0gt, child number 0 ------------------------------------- select count(c.small_vc_c) from child c, parent p, grandparent g where c.small_num_c between 200 and 215 and p.id = c.id_p and p.id_g = c.id_g and g.id = p.id_g Plan hash value: 1546491375 ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 26 (100)| | | 1 | SORT AGGREGATE | | 1 | 15 | | | |* 2 | TABLE ACCESS FULL| CHILD | 85 | 1275 | 26 (4)| 00:00:01 | ---------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(("C"."SMALL_NUM_C"<=215 AND "C"."SMALL_NUM_C">=200))
So join elimination based on multi-column primary keys does work – but you might have to get a bit lucky in the order you list the tables in the from clause.Footnote.
If you’re wondering whether or not switching from Oracle syntax to ANSI syntax would make a difference, it does: with ANSI syntax both grandparent and parent are eliminated if the SQL lists the tables in the order grandparent -> parent -> child (i.e. the order which doesn’t work properly for Oracle syntax) and only the parent is eliminated for the order child -> parent -> grandparent. In other words, both syntax options have a point of failure but they fail the opposite way around.Code:
rem rem Script: join_elimination_12c2.sql rem Author: Jonathan Lewis rem -- Environment details eliminated define m_pad=100 /* IDs will be 1 to 1000 */ create table grandparent as select rownum id, trunc((rownum-1)/5) small_num_g, rpad(rownum,10) small_vc_g, rpad(rownum,&m_pad) padding_g from all_objects where rownum <= 1000 ; /* Each GP has two (scattered) children here Parent IDs are 1 to 2,000 */ create table parent as select 1+mod(rownum,1000) id_g, rownum id, trunc((rownum-1)/5) small_num_p, rpad(rownum,10) small_vc_p, rpad(rownum,&m_pad) padding_p from all_objects where rownum <= 2000 ; /* Simple trick to get 5 (clustered) children per parent Child IDs are 1 to 12,000 */ create table child as select id_g, id id_p, rownum id, trunc((rownum-1)/5) small_num_c, rpad(rownum,10) small_vc_c, rpad(rownum,&m_pad) padding_c from parent p, ( select /*+ no_merge */ rownum from parent p where rownum <= 5 ) d ;
create unique index g_pk on grandparent(id); create unique index p_pk on parent (id_g, id) compress 1; create unique index c_pk on child (id_g, id_p, id) compress 2; alter table grandparent add constraint g_pk primary key (id); alter table parent add constraint p_pk primary key (id_g, id); alter table child add constraint c_pk primary key (id_g, id_p, id); alter table parent add constraint p_fk_g foreign key (id_g) references grandparent; alter table child add constraint c_fk_p foreign key (id_g, id_p) references parent; rem rem Don't need to collect stats because it's 12c rem prompt =============================================================== prompt Join all three tables with the FROM clause ordered gp -> p -> c prompt The final plan is GP->C, The optimizer eliminated P before prompt considering GP prompt =============================================================== select count(c.small_vc_c) from grandparent g, parent p, child c where c.small_num_c between 200 and 215 and p.id = c.id_p and p.id_g = c.id_g and g.id = p.id_g ; select * from table(dbms_xplan.display_cursor(null,null,'outline')); prompt =============================================================== prompt Join all three tables with the FROM clause ordered c -> p -> gp prompt The final plan is a tablescan of C only. The optimizer managed prompt to eliminate GP first and P second prompt =============================================================== select count(c.small_vc_c) from child c, parent p, grandparent g where c.small_num_c between 200 and 215 and p.id = c.id_p and p.id_g = c.id_g and g.id = p.id_g ; select * from table(dbms_xplan.display_cursor(null,null,'outline')); prompt ================================================== prompt Convert to ANSI standard in the order gp -> p -> c prompt and both gp and p eliminated. prompt ================================================== select count(c.small_vc_c) from grandparent g join parent p on p.id_g = g.id join child c on c.id_p = p.id and c.id_g = p.id_g where c.small_num_c between 200 and 215 ; select * from table(dbms_xplan.display_cursor(null,null,'outline')); prompt =================================================== prompt Convert to ANSI standard in the order c -> p -> gp prompt and only p is eliminated. prompt =================================================== select count(c.small_vc_c) from child c join parent p on p.id = c.id_p and p.id_g = c.id_g join grandparent g on g.id = p.id_g where c.small_num_c between 200 and 215 ; select * from table(dbms_xplan.display_cursor(null,null,'outline'));
It’s possible, of course, that with different system stats, or I/O calibration, or extent sizes, or segment space management, or block sizes, sundry other parameter details that you won’t be able to reproduce the results without messing about a little bit, but I don’t think I’ve done anything special in the setup that would make a real difference.
Redwood Shores, Calif.—Jan 10, 2017
Today Oracle announced that Sur La Table has deployed Oracle Retail Xstore Point-of Service and has selected Oracle Commerce to deliver a converged commerce experience across all channels. Catering to the growing number of consumers visiting its commerce site and more than 130 stores nationwide, Sur La Table is creating a cohesive shopping experience, personalizing offers and streamlining order fulfillment. A popular destination for exclusive and premium-quality goods for the kitchen and table, Sur La Table is a place where cooks find a surprising selection of culinary tools and foods from around the world, with helpful, knowledgeable employees.
“We are empowering our associates in the stores to improve the customer experience with the Oracle Retail Xstore Point-of-Service solution. We selected Oracle Commerce to deliver a consistent brand experience across channels,” said Tracy Cioffi, Senior Vice President of Marketing, Sur La Table.
Sur La Table continues to transform its IT infrastructure by using Oracle to compliment or replace customized legacy applications that hindered growth and were costly to maintain. Before transitioning to Oracle, Sur La Table evaluated the savings against ongoing cost of maintaining its legacy systems.
“Oracle is providing the retail industry’s leading, most comprehensive enterprise solutions to retailers of all sizes,” said Ray Carlin, Senior Vice President and General Manager, Oracle Retail. “We are pleased to partner with Sur La Table will continue to deliver innovation backed by world class technology.”Oracle Retail at NRF 2017
Oracle Retail will be showcasing the full suite of Oracle Retail solutions and cloud services at the National Retail Federation Big Show Jan. 15-17, 2017, in New York City at the Jacob K. Javitz Convention Center. Oracle Retail will be located at booth #2521. For more information check out: www.oracle.com/nrf2017
+415 595 1584
Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit www.oracle.com.Trademarks
Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.Safe Harbor
The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle's products remains at the sole discretion of Oracle Corporation.
- +415 595 1584
- Using Oracle Big Data Lite Virtual Machine with Ravello trial (OPN Innovation and Modernisation Center (EMEA))
via OPN Innovation and Modernisation Center (EMEA) http://ift.tt/1AAiVSD
Web-based content in Oracle E-Business Suite 12 runs on the Oracle Application Framework (OAF or "OA Framework") user interface libraries and infrastructure. Since the release of Oracle E-Business Suite 12.2 in 2013, we have released several cumulative updates to Oracle Application Framework to fix performance, security, and stability issues.
These updates are provided in cumulative Release Update Packs, and cumulative Bundle Patches that can be applied on top of the Release Update Pack. "Cumulative" means that the latest RUP or Bundle Patch contains everything released earlier.
The latest OAF update for EBS 12.2.5 is now available:
- Oracle Application Framework (FWK) Release 12.2.5+ Bundle 9 (Patch 25124879:R12.FWK.C)
Where is this update documented?
Instructions for installing this OAF Release Update Pack are here:
- Oracle E-Business Suite Release 12.2 Upgrade Considerations for OA Framework-based Applications (Note 1927975.1)
Who should apply this patch?
All EBS 12.2.5 users should apply this patch. Future OAF patches for EBS 12.2.5 will require this patch as a prerequisite.
What's new in this update?
This bundle patch is cumulative: it includes all fixes released in previous EBS 12.2.5 bundle patches.
This latest bundle patch also includes new fixes for several critical issues:
- Partial Page Rendering event queuing fails in Internet Explorer 11 and Chrome browsers
- Inability to reset the value of a read-only text input bean mapped to a list of values
- Columns selected while creating a view are not displayed after running a query against the view
- An error message is displayed when trying to import a personalization
- Which JDeveloper Version Works With Your EBS Release?
- Oracle E-Business Suite 12.2.5 Now Available
- New User Interface Features in Release 12.2.4
- New OA Framework 12.2.5 Update 4 Now Available
- New OA Framework 12.2.5 Update 5 Now Available
- New OA Framework 12.2.5 Update 6 Now Available
- New OA Framework 12.2.5 Update 7 Now Available
- New OA Framework 12.2.5 Update 8 Now Available
I’m trying to write this blog post with classeur.io.
Just like Martin I’m also searching for alternative ways to write blog posts. I don’t want to completely migrate my blog to a new platform, so I’m searching for a way to write in Markdown and deploy to Blogger.
Since our development of APEX Office Print (AOP) we also use Markdown for our documentation as it makes including code samples easier, it can be version controlled and overall it’s pleasant to write in. Whenever we deploy a new version, we publish the markdown as HTML. For example you see the result of our documentation here. SSjj… we like Markdown so much that we are even looking into supporting Markdown to write your template in AOP, next to Word, Excel and Powerpoint, but more on that in the February timeframe. :)
So I’m giving classeur.io a try, it’s just another Markdown editor, but it can publish directly to Blogger. It allows me to include code samples like this:
l := 'hello world';
Or if I want to reference somebody I can use a quote:
Oracle Application Express (APEX) changed my life. – Dimitri Gielis
And a list … for example the top 3 reasons I’m looking at a different way to blog:
- Faster to write a post
- Reuse my writings in different ways
- Easier to share code
So this post is really to try the different options of classeur.io which should show up in Blogger after I hit the publish button.
Here’s an image which I plan to use in my next post:
If you see this and the post looks ok, my test went well :)
I will be attending the USA and APAC Oracle Utilities 2017 Edge Customer Conference Product Forum this year, conducting a number of technical sessions. This year, to make the sessions more relevant, the content of the sessions has been tweaked to cover a number of aspects for the subject are chosen. The sessions are a combination of information, future plans, best practices and tips/techniques for getting the most out of your products. The information is based upon feedback from implementations across the world as well as information on how Oracle itself is implementing the products in the cloud.
The sessions this year are as follows:
Topic TECH-001 Data Management Strategies - Using ILM and CMA to manage your data. This session will not be conducted at the APAC conference.
TECH-002 Integration Techniques - Using the various techniques available to build an integration solution including Web Services, REST and the Oracle Integration Cloud Adapter.
TECH-003 Extending your implementation - Various techniques for extending your product on site and in the cloud.
TECH-004 Testing your implementation - Outlining testing accelerators with Oracle Utilities Advanced Testing Pack (co-presented with a customer).
TECH-005 Utilities in the Cloud - An architectural overview of the Oracle Utilities offerings in the cloud to understand the capabilities and learn about how to apply the same architectures to your onsite or cloud implementations.
TECH-006 Securing your implementation - Understanding the security aspects of the products as well as options for extending the security capabilities.
TECH-007 General Question and Answer session - A panel session where you can ask product experts questions about implementation issues and directions.
If you are attending the forum, feel free to attend and catch up with me at the sessions or the various other avenues during the conference.
Check it out Jet Builder GITHUB
But a recent mishap accident with my father has made me realize that there are far more difficult and significant things in life than I had yet to confront and I was not prepared to handle it alone.
Diwali the festival of lights had just gone by and we were all in a cheerful mood. My brother and his family were back home in India and the grand parents were having a good time with grand kids. My dad has just returned back to India after a pleasant three month stay with me here in USA.
Then came the scary Halloween trick for me. On a fateful day of Oct 30th 2016, just after Diwali in Bhilai, India my dad slipped and fell and the back of his neck hit the swing base. It was not a major accident by external measures, no external bleeding or wound, but it was severe enough to compress the nerves to a point were he could not move or stand up. He was lying on the floor, lifeless and could not move his both legs and one hand.
He was fortunately able to make an audible alarm and was picked up , lifted and transported to hospital. He was immediately admitted to Bhilai Sec-9 hospital in Chhattisgarh, India. There it was diagnosed that he already had a case of OPLL () and this fall and immobility are symptoms of a final breakdown.
OPLL is a calcification of the soft tissues that connect the spinal bones which results in a narrowing of the spinal canal and compression of the cervical spinal cord.
The cause of OPLL is unclear and I could not get a certain answer from anyone, but was told that people of Asian heritage especially Japanese have a higher likelihood of developing OPLL. Also more men seem to be affected than women.
OPLL SymptomsMost patients with OPLL are asymptomatic with no symptoms at all, but others may experience mild pain and numbness in the arms and/or legs to complete numbness in the extremities. The symptoms are similar to those of cervical cord compression.
Treatment of OPLLThis depends on the stage of OPLL and in my case , my dad is at stage 4 and that is the severest stage. Conservative treatment like activity modification and exercises may help relieve the pain caused by OPLL but I was told that open spine surgery is required.
If pressure is not relieved on the spinal cord then future damage and loss of neurological function is very much possible. At sec 1 Bhilai hospital, my brother was told that this may affect respiratory functions as well. So they advised immediate surgery and there is a major risk when you open up the spine.
Since then in last 2 months we have gone through many things and are working on getting this thing taken care of. It is a big help that my brother is back home in India and can execute on many on site things. Simple things like opening zipper or buttons that we take granted for in life start to seem like mammoth tasks. What you can do in person on site, is a significant thing in such circumstances.
I would like to thanks and credit the following websites for some of my research. Indian Government portal surprisingly had a good amount of research papers at NCBI.
We have undergone surgery since then and in future articles I will share more details, but if anyone is in similar situation, I will be glad to share detailed notes. I was helped by many known and unknown people so my heartfelt thanks goes out to them and I plan to pay it forward.
[I was chatting with ODP.NET product manager, Alex Keh, and he let me know about some known issues that developers currently face when using managed ODP.NET with Transport Layer Security/Secure Sockets Layer (TLS/SSL) wallets. I asked him for more details and he wrote up the step by step walkthrough you see below. If you have questions about the steps provided below, please post to the ODP.NET OTN forum for assistance. -CS]
Setting up managed ODP.NET Transport Layer Security/Secure Sockets Layer (TLS/SSL) wallets correctly can be tricky for those that don't do it on a regular basis. Microsoft Windows now restricts wallets from using the MD5 algorithm. Oracle wallets may have been generated with this algorithm as that was the default option in Oracle Public Key Infrastructure (orapki) utility 12.1 and earlier.
When you setup TLS/SSL and encounter an "ORA-0052: Failure during SSL handshake" error combined with a 0x80004005 error code and first inner exception "A SSPI-call failed" and second inner exception "A token sent to the function is invalid", then it is very likely that Microsoft Security Support Provider Interface (SSPI) rejected your Oracle Wallet, such as when MD5 is used. This is a failure on the handshake. You can resolve this error by using the SHA-2 algorithm instead. We'll describe how shortly.
If the second inner exception instead indicates "The credentials supplied to the package were not recognized", it is possible the user certificate was generated without a certificate authority (CA). You can resolve this error by using orapki to generate a CA/root certificate and then regenerating your user wallet/certificate to point to this new CA/root certificate. More info on how to do this is below.
Orapki refers to orapki.exe. This utility is part of full Oracle client (admininstrator) installations. It is not included with Oracle Instant Client. The utility is only needed to setup up the wallet; it is not necessary to deploy it with the wallet.
The steps below will regenerate your Oracle Wallet using orapki. Any orapki version can be used to generate the wallet with these instructions.
1. Create root wallet, for example, a CA wallet.
orapki wallet create -wallet ./root -pwd yourpassword
2. Add a self-signed certificate (CA certificate) to the root wallet.
orapki wallet add -wallet ./root -dn 'CN=yourroot' -keysize 1024 -self_signed -validity 3650 -pwd yourpassword -sign_alg sha512
3. Export the self-signed certificate from the wallet.
orapki wallet export -wallet ./root -dn 'CN=yourroot' -cert ./root/b64certificate.txt -pwd yourpassword
4. Create a user wallet, for example, a customer wallet.
orapki wallet create -wallet ./user -pwd yourpassword -auto_login
5. Add a certificate request.
orapki wallet add -wallet ./user -dn 'CN=clientshostname' -keysize 1024 -pwd yourpassword -sign_alg sha512
6. Export the certificate request.
orapki wallet export -wallet ./user -dn 'CN=clientshostname' -request ./user/creq.txt -pwd yourpassword
7. Create a certificate issued by a CA.
orapki cert create -wallet ./root -request ./user/creq.txt -cert ./user/cert.txt -validity 3650 -pwd yourpassword -sign_alg sha512
8. Add a trusted certificate (CA certificate) to the wallet. This example assumes the same CA for both the client and server wallets.
orapki wallet add -wallet ./user -trusted_cert -cert ./root/b64certificate.txt -pwd yourpassword
9. Add a user certificate.
orapki wallet add -wallet ./user -user_cert -cert ./user/cert.txt -pwd yourpassword -sign_alg sha512
10. Display contents of user wallet.
orapki wallet display -wallet ./user -pwd yourpassword
11. Create a server wallet.
orapki wallet create -wallet ./server -pwd yourpassword -auto_login
12. Add a server certificate request.
orapki wallet add -wallet ./server -dn 'CN=servershostname' -keysize 1024 -pwd yourpassword -sign_alg sha512
13. Export the certificate request.
orapki wallet export -wallet ./server -dn 'CN=servershostname' -request ./server/creq.txt -pwd yourpassword
14. Create a server certificate issued by a CA.
orapki cert create -wallet ./root -request ./server/creq.txt -cert ./server/cert.txt -validity 3650 -pwd yourpassword -sign_alg sha512
15. Add a trusted certificate (CA certificate) to the server wallet. This example assumes the same CA for both the client and server wallets.
orapki wallet add -wallet ./server -trusted_cert -cert ./root/b64certificate.txt -pwd yourpassword
16. Add a user_cert certificate for the server wallet.
orapki wallet add -wallet ./server -user_cert -cert ./server/cert.txt -pwd yourpassword -sign_alg sha512
17. Display contents of server wallet.
orapki wallet display -wallet ./server -pwd yourpassword