Search

OakieTags

Who's online

There are currently 0 users and 30 guests online.

Recent comments

Uncategorized

Loading file data … easier than you think

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

  • go find out the table definition, column names and data types
  • write up a SQL Loader control file, or
  • write up an external table definition script
  • repeat this 10 times until I get the syntax right

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

Mr DISTINCT might not be your friend

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:

Better SQL via query block names

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.

pshyco_jiller
But I can’t agree more.  Have great self-documenting code AND have great commentary around it.

Index compression–working out the compression number

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:

Navigating the world of Oracle database documentation

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 Smile

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.

Joining Oracle

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.

https://youtu.be/PtGDlSKmsBw

Things that are there but you cannot use

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.

SQL Text / View Expansion

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:

Example EXPAND_SQL_TEXT – View Definition:

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;

Example EXPAND_SQL_TEXT Use

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:

Oracle 12 and latches, part 2

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:

Delphix Express – a free version of Delphix

 

#555555;">#2970a6;" href="http://datavirtualizer.com/wp-content/uploads/2015/03/2401421989_e05053cbf7_z.jpg">2401421989_e05053cbf7_z

 

#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)