OracleBlog

Having chosen Oracle SQL Developer as your preferred Oracle database tool, do you have to install and learn a new technology for supporting your MS SQL databases? Nope! It's easy to connect SQL Developer to MS SQL databases, and I'll show you how.

Background

For years I worked in technical support for software vendors, and I never knew what client tool would be available when I accessed a customer system. In fact, in many cases they'd only give me command-line SQLPlus access. Consequently I never really chose a preferred Oracle client, nor became particularly proficient with anything other than command-line SQLPlus.

Eventually I found myself working directly with a client, and could finally choose whichever tool I liked best, and like many of you I chose Raptor - now known as Oracle SQL Developer. Explaining my choice is outside the scope of this article, but suffice it to say that it's a powerful time-saver at best, and a big step up on command-line SQLPLus at worst.

Connecting to MS SQL

Not all the database applications we support are going to be Oracle-based, and the most popular of the alternatives is Sybase's curious nephew MS-SQL. Fortunately there's no need to find another application for your MS-SQL instances, you can still use SQL Developer, and here's how.

Step 1. Get the JDBC driver for MS SQL

If you don't already have one, download a JDBC driver for MS-SQL. I use the jTDS driver, which is open-source Java. It doesn't matter where you place it, but remember where.

Step 2. Load the JDBC driver

As explained in Oracle's tutorial, under Preferences, scroll down to Database and Third Party JDBC Drivers. Once you load the jtds-1.2 jar file you should see both SQL Server and it's aging uncle Sybase added to your list of options when creating a new database connection (You get Oracle and Access by default).

Step 3. Set up your new database connection

Now you can create a new MS SQL database connection the same you would an Oracle connection. The only difference is that you need to select the SQLServer tab instead of Oracle. If you don't see that tab, then something went wrong - either you got the wrong jar file or it didn't load correctly.
If you're brand new to Oracle SQL Developer, Anders Andreasen has an example with a few extra screenshots.

Use the Test button to make sure you can connect, and if you can't the error message ought to be descriptive enough to give you a starting point to troubleshoot.

Troubleshooting

Needless to say, technologies as complex as these will occasionally present you with unexpected difficulties.

For instance, you may find that you need to copy the ntlmauth.dll file from the SSO directory to SQL Developer's jdk/jre/bin directory.

Or, you may need to check the Configuration Manager on your SQL Server database to make sure you have TCP/IP communication enabled, and over default port 1433.

If you used the driver I recommended, they have a FAQ that can help you track down some types of errors.

Recap

If you support multiple databases, there may not be a need to install multiple tools. Oracle SQL Developer can be used to connect to different flavours of databases, including MS SQL. Just download and load in the JDBC driver, and you should be able to create a new database connection as easily as you can with Oracle.

A colleague asked me some questions about FIRST_ROWS and ALL_ROWS, but I'm hesitant to blog about it because it's already been done so well by others -- the best example would probably be Sachin Arora.

Nevertheless, it never hurts to lend another voice to the Oracle choir, so here's everything I know on the topic.

FIRST_ROWS and ALL_ROWS are values for the optimizer setting OPTIMIZER_MODE. You can set it in your init.ora file, or you can set it on a session-by-session basis (using alter session). You can also set it on a query-by-query basis by using hints (although such hints may be ignored in certain cases, like where all rows must be accessed, as in a GROUP BY).

Your basic choices for OPTIMIZER_MODE include CHOOSE by default, RULE, ALL_ROWS, FIRST_ROWS and its variant FIRST_ROWS_N (where N is 1, 10, 100 or 1000). So far I'm not telling you anything that wouldn't be obvious with a glance at the documentation, but let me explain a little bit about the latter two.

ALL_ROWS and FIRST_ROWS have a lot in common. They're the only two settings that force the use of the CBO (Cost-Based Optimizer), and therefore they're collectively referred to as cost-based, since the concept of cost is directly related to the CBO.

If your statistics are up to date -- which they should be!! -- then the CHOOSE option will generally use the CBO as well. Possibly always, but I'd rather not use the word "always" unless I were certain. If you turn tracing on, you can see the optimizer setting that was used, and if you see "Cost=" then you know the CBO came into play.

The most important thing to keep in mind when using CHOOSE or a cost-based optimizer setting is that your statistics are up to date, otherwise it might default to RULE (if it's set to the default value of CHOOSE), or will use default statistics.

"The CBO without stats is like a morning without coffee. A really bad idea."
- Tom Kyte (though I'm sure Jon Emmons would agree).

The difference between FIRST_ROWS and ALL_ROWS is exactly what you'd think - the former instructs the CBO to get the first rows as quickly as possible even if the overall query takes longer, whereas the latter simply gets you all the data as quickly as possible.

As you would probably guess, FIRST_ROWS will likely be better in an interactive client/server situation where there is a benefit to getting some of the results before the rest. FIRST_ROWS is also found to use index scans instead of full table scans more often than otherwise - but for more detail on its peculiarities, check Sachin's article or search the Oracle forums.

As far as I know, the only real difference between ALL_ROWS and CHOOSE is that ALL_ROWS will use the CBO regardless of whether you have up-to-date statistics. Basically ALL_ROWS doesn't make as much sense to me, because if you care about performance to the point where you're modifying optimizer settings, then you probably care enough to have up-to-date statistics, in which case you'd leave it on the default value of CHOOSE.

That's about all I can really say about these two settings. If you've read this far and you'd like to know more, there are two obvious things you can do:

- You can Ask Tom, and/or
- You can read the Oracle Documentation

Each of those sources has pretty much everything I've said, plus more. If you've got more to ask or to offer on this topic, I invite you to leave a comment.

Today, August 4th, shortly after lunch, ThinkOracle had it's one millionth visitor. Care for a stroll down memory lane?

I started this site May 16, 2005, shortly after starting a new position with a company that made financial software. The idea was to make my own contribution to the growing Oracle community, expand my knowledge, improve my technical writing, and it never hurts to establish a good reputation.

I actually started a sister site for Sybase that same day, wrote for about a year (consistently at first, then sporadically), and it's not even at 42,000 hits. There just isn't as big a community for Sybase as there is for Oracle.

As for the Oracle site, I posted regularly for over two years, when I changed jobs again. Unfortunately my new position didn't involve nearly as much Oracle work, so my contributions started to fade off.

Surprisingly, the number of visitors I was getting didn't fade off, and Google dutifully sent a number of Oracle professionals to my archives. It was always a thrill when someone in the office would swing by my desk and tell me a story about how they were Googling a solution to a problem, and came across my site.

Unfortunately, being a good blogger is a big investment of time. It's not just the writing, you have to keep your knowledge up to date, follow other blogs, participate in forums and other user groups - it's a lot of work, especially if it's not a central part of what you do for a living. I wish I had the time to post more than a few times a year, but I don't.

A lot of my favourite fellow bloggers have come and go in this time, although I'm amazed at those that are still going strong, like Tom, Eddie, Doug, Pete, Mark, Laurent and Tim, to name just a few. I remember all the great bloggers that were there when I started and have since faded just like I have, especially David, Niall and Howard. I also see a brand new generation of Oracle bloggers, and as lucky as we were back then, I think everyone is luckier today.

Thinking back, there have been a lot of highlights over the years (all of which you can find in my archives):

1. Tom Kyte following my blog and commenting regularly to help me get going. This site might not be here without him, and oh what a spike in traffic when he first linked to it.

2. Oracle started including my blog in their aggregator, August, 2005.

3. My first milestone: 60 posts and 10,000 visitors by November, 2005.

4. Computerworld recognising ThinkOracle as one of the best IT blogs, December 2005.

5. Oracle publishing one of my articles on their site, February 2006.

6. Meeting Tom Kyte, June 2006.

7. My next milestone: 107 posts and 100,000 visitors November, 2006.

8. Almost winning Oracle Blogger of the year in 2006 (Damn you Eddie!)

9. Being asked to be Technical Editor on Alice Rischert's SQL book, February-March 2009.

10. One million!

Even before I was a blogger, I would visit Oracle discussion forums, and marvel at how people like Laurent would tirelessly assist anyone in need of Oracle expertise. Once I started blogging, I understood how addictive and contagious it can be.

I have very fond memories of my time as a regular blogger. I'm still here, I haven't given up, and you can definitely expect as much new content as I can muster.

And if you're a fan of my writing and you like hockey, keep your eyes peeled for the 2010-11 Puck Prospectus Annual, which is coming out in September and on which I'm an author. You can also check out my stuff on ESPN.com. If comedy is more your style, my personal blog, which pre-dates this one, is still going strong.

Last week I got a question about changing a user's default schema.

My colleague is supporting a typical database application which is configured to use the user/schema that was created for its database. Many queries were written for this application that use that schema owner, but my colleague would like to run those queries with his own account instead - either because he doesn't want to log in as schema owner, or can't.

To make the queries work for his account, he'd have to go through every one and add the schema name. That's tedious! What he'd like to do is change the default schema for his user account. What are his options?

Best Practices

First of all, I'm glad he's not simply using the schema owner account. The schema owner has virtually unlimited privileges, and the more people or applications that use that account, the greater the risk of accidentally making a big mistake. In fact, I prefer even configuring the applications to use their own account, rather than the schema owner.

Secondly, this is hardly a new question, in fact it was probably explained best by Tim Hall at Oracle Base years ago:

"Allowing applications to make direct connections to the schema owner is a bad idea because it gives those applications far (too) many privileges, which can easily result in damage to your data and the objects themselves. Instead, it is better to define application users and grant those users the necessary privileges on the schema owners objects."

Alter Session

When a user connects, it connects to its own schema, the one that was created when the user was created. It can certainly change to another, using alter session. Dan Morgan has a reference on altering sessions, or you check your Oracle documentation.

You can do this in at least three places:
1. When you first connect, either manually or as the first line of your SQL script.
2. As a log-in trigger (as Tim demonstrates)
3. In your glogin.sql file, which executes when you first log in.

Use Views

If we're not talking about a lot of tables, another option is to create views in your own schema of the same name as the tables. As I've written before, this is just one of the many useful purposes of views.

You can restrict access, and also include only those columns in the view as you need. The only trick is that you may need to update those views if the underlying tables ever change.

Synonyms

And again, if you're only selecting from a few tables, your other option is to use synonyms, which in a sense dictates which schema the statements are working against.

You'll have to do this on a table-by-table basis, and the big drawback is that it could confuse things if you deal with other schemas with the same table names. Oracle will use a local object first, then look for any private synonyms before using the public one. Here's Dan Morgan's reference on synonyms.

My Recommendation

As for me, my favourite option is to alter session at the top of my script. All these other options are things that are hidden from the user, and I've always disliked it when my database is set up to do things in the background of which I might not be aware. If I select from a table I don't have, I'd like to know that, rather than have it silently grab it from somewhere else instead.

This is an old problem, so there may be other solutions, too. If you've got one, please leave them in my comments, along with any opinions you have on these approaches.

Also, one other special request. Please scroll down to my stat counter, and if you're my one millionth reader (or very close to it), please leave me a comment! Thanks.

I was helping a colleague recently with some SQL. He had about a dozen SQL queries that all looked something like this:

SELECT
(some stuff),
DECODE (status, 'A', 1, 'I', 0),
(more stuff)
FROM
(wherever)

I made a few observations about his DECODE statement:
1. Sometimes he used DECODE and sometimes he used CASE
2. Sometimes he would map ' ' to 0, and sometimes it would go to the default value of NULL, like in this example.

Decode vs Case

First, he asked me whether he should use DECODE or CASE. I remember getting into discussions about that with Doug Burns back in the days when I was more active in the community. Without getting into the whole debate again, let me just say that between the two, CASE is ANSI-standard, newer, easier to maintain, can do more things, and is generally the way to go.

That being said, there's no real difference between DECODE and CASE in this example. I'd just pick one and be consistent.

Use Mapping Tables

My primary suggestion was to consider eliminating the DECODE and CASE altogether. Since several SQL queries were doing the same thing, and accidentally doing so inconsistently, I recommended replacing it with a mapping table. Here's how:

1. Create a table with two columns, one of the same type as "status", the other as a number.

CREATE TABLE mapper (status VARCHAR2(1), val NUMBER(1));

2. Populate the table with all possible "status", and to which number you want it mapped.

INSERT INTO mapper (status, val) VALUES ('A', 1);
INSERT INTO mapper (status, val) VALUES ('I', 0);
INSERT INTO mapper (status, val) VALUES (' ', 0);
commit;

3. It's not always possible to modify the original table to make its status column a foreign key on this one (it might be tied to a vendor application, as it is in this case), but if it is possible, it's worth considering.
4. In the SQL queries, add a join condition on "status" to this new table you created

FROM table1 t1 JOIN mapper m USING (status)

5. Replace the DECODE/CASE statement with the "number" column of this new table.

SELECT m.value

The Pros and Cons

The advantages are that it's very simple, easy to understand, and you know it's consistent across all your queries. Should you ever want to change the mapping, you can easily update that table, and know that all your queries have been updated.

Also, you can more easily take advantage of indexes now (function-based indexes notwithstanding). That's not really applicable in this example, but it may be in other cases.

The disadvantage is having another join. But as Tom Kyte once said "joins are not evil. Databases were born to join." There are reportedly some cases (in Oracle 10, for example) where sometimes adding another table causes the optimizer to make really bad choices, and it really slows down your query, almost as if you've hit a threshold. Chances are that you can address this concern either with patches or by talking with your DBA about some of the optimizer settings. In general, you can fear the reaper, and don't fear joins.

Wrap Up

If you've got several queries that are all using DECODE and/or CASE to map one value to another, consider creating a mapping table and joining that into your queries instead. It will make them easier to maintain, and depending on your indexes it might even speed up some queries.