Author Archives: rlockard
Beyond the basic select statement: Using the WITH clause inline PL/SQL
For years I used the basic select statement: SELECT A.C1, A.C2, B.C1, B.C2, <…> FROM TAB1 A, TAB2 B WHERE A.C1 = B.C1; Sometimes this basic select statement would not answer my question. You can make a select statement quite … Continue reading
Exploring the CBO
Lets start with a simple experiment on what the CBO can do with query optimization. We are going to build on the structure as we go to see if we can help out the CBO with more information. Test 1) … Continue reading
Posted in Cost Based Optimizer, Database Stuff
Leave a comment
something about the cbo was bothering me
A query similar to this was getting a full table scan. Now we know the NDR in archived, deleted and flag is 1. My initial thought was because of this we would get a full table scan. I was wrong, … Continue reading
Foreign Keys and indexes
In this test case we are going to use two tables with a foreign key. The question is, if we create a foreign key, do we need to add an index on that foreign key column? OPS$ORACLE@VETDEV > create … Continue reading
Posted in Database Stuff
Leave a comment
How do You succeed?
This does not have much to do with Oracle consulting, this is more some life lessons on how do succeed at work. I have learned many lessons in life, one of them is the freedom that comes from being independent … Continue reading
Posted in Database Stuff
Leave a comment
Encrypt those backups
April 2005 Ameratrade loses a backup tape containing information on 200,000 customers. February 2005 Bank of America loses backup tapes containing information on 1.2 million charge cards. September 2011, SAIC loses backup tapes of 4.9 Million members of the military … Continue reading
Life of an Oracle DBA – Test environment.
It’s been a few weeks since I have posted. The short story is my work load has been keeping me from posting. Today I had an interesting conversation regarding a test environment that we are building in a technology refresh. … Continue reading
Posted in Database Stuff, Uncategorized
Leave a comment
Life of an Oracle DBA – The Basics Access Paths: Index Skip Scan
Normally for Oracle to use an index, the leading column in the index must be in the predicate. There are conditions, where the leading edge of the index is not in the predicate but the remaining columns in the index … Continue reading
Posted in Uncategorized
Leave a comment
Life of an Oracle DBA – The Basics Access Paths: Index Range Scan
A index range scan gets the rowid by getting the adjacent index entries. Once Oracle has gathered the rowid’s the rows are returned in ascending order. Unless you use sort descending. From our previous example we saw that if you … Continue reading
Posted in Database Stuff, Life of a Oracle DBA
Leave a comment
Life of an Oracle DBA – The Basics Access Paths: Index Unique Scan
The index unique scan is good for getting one row from and index and returning the rowid. You will get a index unique scan if you have a unique index or primary key on a table. In this example; we … Continue reading
Posted in Database Stuff, Life of a Oracle DBA
Leave a comment
Life of an Oracle DBA – The Basics Access Paths: Table Access By Rowid.
Dang, that just sounds fast, and it is. Rowid points to the datafile:block:location in block. So, what is this good for? How do you get Oracle to access by rowid? If you want to access a specific row or set … Continue reading
Posted in Database Stuff, Life of a Oracle DBA
Leave a comment
Life of an Oracle DBA – The Basics Access Paths: Full Table Scan
For the next few weeks I am going to be going over some of the basics of what you need to know to be a good Oracle DBA. Feel Free to speak up if there is something you think I … Continue reading
Posted in Database Stuff, Life of a Oracle DBA
Leave a comment
Life of an Oracle DBA: Big Data restore
Over the years my opinion of what big data is has changed. Way back in the VAX days, a 1G database was not only big data it was fricken huge data. Now words like gig, tara, and peta are thrown … Continue reading
Posted in Database Stuff, Life of a Oracle DBA
Leave a comment
Life of a Oracle DBA. What is choking my Oracle database server?
The week was dominated by turning. The database server was pegged at 100% CPU all week. I have tuned the offending queries to within an inch of their lives. The customer wants to know, why is this happening? And I’m … Continue reading
Posted in Database Stuff, Life of a Oracle DBA
Leave a comment
Life of an Oracle DBA: Grumps, dataguard, upgrades and tuning oh my
There are very few things that will get my dander up. One is waisting my time; the other is “Not My Job.” Last week I experienced both; it took a bit but the person who told me it was not … Continue reading
Posted in Database Stuff, Life of a Oracle DBA
Leave a comment
The life of an Oracle DBA
So you want to be an Oracle DBA? What does a DBA do? What does my week look like? Frequently my wife asks me what my days are like and just what do I do at work. Well, I’m going … Continue reading
Posted in Database Stuff, Life of a Oracle DBA
Leave a comment
Encrypt those backups
April 2005 Ameratrade loses a backup tape containing information on 200,000 customers. February 2005 Bank of America loses backup tapes containing information on 1.2 million charge cards. September 2011, SAIC loses backup tapes of 4.9 Million members of the military … Continue reading
Security in the Cloud. Install #1
There are a number of different vendors providing cloud services. You can buy space and processing power from vendors like IBM, or Amazon or many other service providers. In the interest of full disclosure, I use cloud services all the … Continue reading
Fix the standby database.
This has happened in the past. A data file get corrupted, or there is a gap in the archive logs. How do you fix it? In the past I would put the tablespace into backup mode, backup of the datafile, … Continue reading
Posted in Uncategorized
Leave a comment
What about NULL?
What is NULL? Is NULL a space? Is NULL a NULL string? NULL is unknown. If null is unknown then you can not do anything with null. You can not perform any operation on null: Y := 5+UNKNOWN then Y=UNKNOWN … Continue reading
Posted in Database Stuff
Leave a comment
Social engineering does happen.
There have been a few times where social engineering was attempted with one of my customers. Sometimes I wind up taking the phone call after someone calls the help desk looking for some information we would not normally give out. … Continue reading
PII is valuable to criminals
The theft of PII is no longer relegated to petty criminals and hackers; it is now organized crime rings that are targeting PII and selling it on the back market. Some nations even tolerate criminal hacking to steel PII as … Continue reading
If you don’t measure it, you can’t protect it
have always felt safe in my home until a few days ago I had locked myself out of my house. I spent about 30 minutes looking for a spare house key my wife may have left in a car that … Continue reading
Posted in Security
Leave a comment