Slideshare won’t let you update slide decks anymore, so I just uploaded my latest version as a new offering.
I’ll be presenting this topic at UKOUG this week. if you’d like me to present this to your group of the developers and administrators, drop me a note, and we’ll setup a webinar.
A Custom Design Rule – Do I Have Foreign Keys on Columns that are NOT Indexed?
One of the features I’ll be showing in this talk is the Data Modeler. I know that many performance problems start at the very beginning of a project. The data model is bad, incomplete, or just plain wrong.
I know that many people like to make sure that all of their Foreign Key columns are indexed – you can imagine that these columns will end up in your queries quite frequently as a predicate or even in the SELECT. Here’s some nice, practical advice on the subject from Tom.
So I asked our developer to help me with a custom design rule – this allows me to quickly ask my model if I have forgotten to cover this scneario.
After I add the code, I simply open the custom design rules and hit ‘apply,’ and I get my warnings.
Here’s the code:
// define function - function parameter doesn't need to be named index function checkFKforSupportIndex(index){ //check it's foreign key index, otherwise FK is null if(index.isFK()){ columns = index.getColumns(); table = index.getTable(); var sind = getIndex(table,columns); return sind != null; } return true; } //getindex that is not used for PK, UK or FK constraint function getIndex(tab,cols){ keys = tab.getKeys(); for (var i = 0; i < keys.length; i++) { ind = keys[i]; if(!(ind.isPK() || ind.isUnique()) && !ind.isFK() && ind.isIndexForColumns(cols)){ return ind; } } return null; } // invoke the function checkFKforSupportIndex(index);
17 Comments
Hi Jeff,
I hope that the connections opened with a net route will still not hang for minutes before disconnecting when the Laptop is moved to a different location or when the laptop is suspended. There should be a way to disconnect quickly these connections.
Another problem I noticed is when I try to compile a package that is in use.
If I try to do this when using an unshared connection the editor simply hangs. When I do this when using the “main” connection then the entire interface hangs. I must open another client and kill the session otherwise I have to wait forever.
The distinction between main session and unshared ones is quite annoying. IMHO everything should be unshared and there should be the possibility to attach a loaded file to any session (btw the session selection should be a bit less automatic because in complex environment it is easy to launch a script on the wrong database).
And about the formatter, may be the USING statement in a MERGE statement should be put at the beginning on the following line, otherwise If the USING statement is complex it ends outside the editor window
Thank you
Can you provide an example of your merge scenario and how you’d like it to look?
e.g. (not tested the query but) if you format this you’ll see the SELECT placed four spaces after the parenthesis after the USING
MERGE INTO aschema.atable_with_a_long_name aa USING (
SELECT
first_col
,second_col
,third_col
,and_so_son
FROM
aschema.another_table bb
WHERE
trunc(SYSDATE) BETWEEN bb.init_date AND bb.end_date
)
ubb ON ( aa.cod_row = bb.cod_row )
WHEN NOT MATCHED THEN INSERT (
a
,b
,c
,d ) VALUES (
ubb.a
,ubb.b
,ubb.c
,ubb.d )
That’s formatter setting, Indentation – I think the default is ‘4’. I set it to 1 and it looks like this now
Thanks Jeff but IMHO it would be better if the select is moved maybe 4 spaces from the left border. Anyway OK.
Another problem the formatter has is with labels. If there is a label in the source then, after formatting, the code is broken and does not compile anymore.
Please try to format the following sample code. Thank you.
declare
lim integer := 10;
ind integer := 1;
begin
<>
dbms_output.put_line(‘ind [‘||ind||’]’);
ind := ind + 1;
if ind <= lim then
goto label_1;
end if;
dbms_output.put_line('terminated');
end;
Please notice that the blog software removed the text inside the angular brackets in the code above. Inside there should be the string “label_1” enclosed by another couple of angular brackets.
Thanks, I logged a bug for you.
Since you seems to be the SQLDeveloper magician, and I didn’t find where to ask, will do this here:
Is there a way to make the Text Editor backgroundcolor the same as the Connection Color?
I think only the borders colored didn’t draw the necessary attention…
Product manager, not a magician, but I like that 🙂
Short answer is No.
Ohh, sad history… 🙁
Being the product manager, would you be able to suggest this as further implementation? 😀
Thanks for the support! ^^
We’ve looked at it…it starts to make the tool look a bit like a christmas tree…can you think of anything less dramatic that might help?
I think more people would not like this vs like this, esp as the number of colors you used went up. I can think of only 1 or 2 that might not be offensive (white/black)
Ops, when I first asked the question, I had already read the side panel “What It Is I Do Here”.
But didn’t notice the link “Have a question? Ask me!”…
Sorry :s
Yep, I totally agree that only a few would like this.
That’s why I thought it only as configuration disabled by default, and whoever want activate…
(and I would use this only with weak/light colors, to not hurt my eyes)
What I think would be less flashy, would be the tabs with the colors as well…
Something that I thought right now:
“The ‘current line’ also could have a option to change the color as the Connection Color.”
Then I’ve realized what I really think would be nice:
The menu Tools > Preference > Text Editor > PL/SQL Sintax Colors, have the option “Use Connection Color” as the color of anything. Then, anyone who would like to put this color somewhere, would be able to… 🙂
Ya know…that’s not crazy.
Stay tuned.
Yay!
It’s nice to see that I was able to suggest something appreciated.
Thanks for hearing! 🙂