So a request comes in from a colleague…
”Hi Connor. I’ve created this new table, and I need to populate it with some data. I’ve got it in Excel – can you help me load it”
Now, at this point, my heart normally sinks, because whilst the Oracle tools to load flat file data are comprehensive, I know that I now have to
all of which is going to be a drain on my time…
What I really would like, is to do this:
1) Check that my table is ready for loading
Whenever you have the need to use the DISTINCT keyword, its worth just pausing for a second, and making sure that you are not hiding just a larger issue. It actually might represent either incorrect use of SQL or incorrect assumptions from the data model.
Consider the following example
SELECT DISTINCT d.dname FROM emp e, dept d WHERE e.ename = 'SMITH' AND e.deptno = d.deptno
The query is certainly valid, but when I see "distinct" I ask myself the following questions:
Has the DISTINCT has been added in an attempt to only return a single row ?, ie, is someone working under the assumption being that an employee name can only refer to a single department ? Unless there is a unique constraint on the ENAME column, then we can still just as easily get multiple rows back (even with the DISTINCT), so the SQL will be a "sleeping problem" in the application until the data causes it to fail.
Because the DISTINCT keyword here:
Can you make your SQL code easier to understand without adding comments. Can it be self-documenting ? Can you do your bit to make sure your code is easily comprehended by the next person who has to maintain your code ? Yes you can. Learn how at my next quick tip at https://youtu.be/bfaFT9doqCg
Is this a license to never add comments in and around your SQL code ? Of course not. There’s a great quote, and if you’re the author, I apologise for not providing proper attribution. Google didnt help.
Richard Foote did a series of informative posts on Index Compression which concludes that there is whole lot of positives about index compression, and very little negatives. But obviously one critical thing is choosing the right number of leading columns to compress. Is it just "take a guess?" . Luckily, Oracle has taken the guesswork out of it.
The ANALYZE command on an index can let you find the optimial compression count. When you do an ANALYZE INDEX command, two critical columns are populated:
If you head on over to http://docs.oracle.com/en/database/ you’ll be both amazed and dismayed by the volume of documentation you can find about the database. If you’re a seasoned Oracle professional, then you probably dont think twice about finding PIVOT examples in Chapter 18 of the Data Warehousing guide
But for the novice, whether it be DBA or Developer, it can be a bit overwhelming. One resource that you might find a useful addition to your navigation of the Oracle universe, is the interactive quick reference site.
Last month I joined Oracle after nearly 20 years of working with their technology. Some people congratulated me on my new role, others told me that going from working solo to working for a massive organisation would be disaster. In the latter case, this was often associated with an impassioned “But why?”
To be honest, I found that a little discourteous – its an assumption that to work for a multinational is to become “part of the problem”. In my career, I’ve worked for several large organisations (Fujitsu and BHP to name a couple). I’ve always been proud of the accomplishments with those companies, never regretful.
But the best way to answer the question I figured, was to speak directly to it.
So here is why I joined Oracle … I hope you enjoy and subscribe, because I’ll be publishing a lot more content (of a more technical nature) on my new channel.
I did a "desc" command on the STANDARD package today, the package that helps define PL/SQL, and saw the XOR function!
SQL> declare 2 x boolean; 3 begin 4 x := XOR(true,true); 5 dbms_output.put_line(case when x then 'TRUE' else 'FALSE' end); 6 7 x := XOR(true,false); 8 dbms_output.put_line(case when x then 'TRUE' else 'FALSE' end); 9 10 x := XOR(false,false); 11 dbms_output.put_line(case when x then 'TRUE' else 'FALSE' end); 12 13 end; 14 / FALSE TRUE FALSE PL/SQL procedure successfully completed.
I cant find any reference to it in the Oracle PL/SQL documentation anywhere, so its definitely not supported so using it is probably off limits until you see officially in the documentation.
One of the common issues faced when debugging or investigating SQL performance is that tools often do not not show the underlying objects at play when a query involves views.
Oracle 12c now provides a capability to display the underlying objects; DBMS_UTILITY.EXPAND_SQL_TEXT will provide the entire syntax of the”exploded” query as shown below:
create view dept_emps as select dname,loc,d.deptno, empno,ename,job,hiredate,mgr,sal,comm from emp e join dept d on e.deptno = d.deptno order by dname,ename;
Here’s a query using the view, unless you know the schema you might not realize multiple database objects were in use.
select * from dept_emps;
Here’s the PL/SQL you might use to “expand” the SQL text to show the objects really involved:
In my previous post, I looked at non shared latches and how the latching is done by Oracle. This post is a description on how the latching works for shared latches.
The information is quite internal, if you landed on this page it might be a good idea to start with my first post on this topic: first post.
A famous example for shared latches is the ‘cache buffers chains’ latch.
For the sake of the test I quite randomly scanned a test table, and had a little gdb script to look at the function call ksl_get_shared_latch:
#555555; text-align: right;">photo by #212124;" title="Go to Yannis's photostream" href="https://www.flickr.com/photos/yannisag/" data-track="attributionNameClick" data-rapid_p="48">Yannis (CC 2.0)